Select TIMESTAMP(6) WITH TIME ZONE using Pandas, SQLAlchemy and cx

I am trying to use pandas to select some data from an Oracle database. The column in question has the data type TIMESTAMP(6) WITH TIME ZONE. I am in the same time zone as the database, but it contains data that is recorded from a different time zone.

Oracle version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Python 3.8.13
SQLAlchemy 1.4.39
cx_Oracle 8.3.0

In PL/SQL Developer, the query works:

SELECT col
FROM table

Returns

18-JAN-21 09.54.58.000000000 PM ASIA/BANGKOK

In Python, I get this error:

import sqlalchemy
import cx_Oracle

server = server
port = port
sid = sid
username = username
password = password
dsn_tns = cx_Oracle.makedsn(server, port, sid)
cnxn = cx_oracle.connect(username, password, dsn_tns)

query = """
    SELECT col
    FROM table
"""
df = pd.read_sql_query(query, cnxn)

Output:

DatabaseError: ORA-01805: possible error in date/time operation

After some SO searching, I tried this:

query = """
    SELECT CAST(TO_TIMESTAMP_TZ(
        col, 
        'DD-MMM-YY HH.MI.SS.FF6 TZH TZR')
) AT TIME ZONE 'ASIA/BANGKOK' AS col
    FROM table
"""

df = pd.read_sql_query(query, cnxn_tds_dev)

Which returns a different error message:

ORA-00905: missing keyword

How can I just select this timestamp column (and several others) using Python/SQLAlchemy/cx_Oracle? Because the query works in PL/SQL Developer, I am assuming it is an issue with cx_Oracle. I will try creating a new Python environment with an older version of cx_Oracle, per this post.

CodePudding user response：

For the record, the code I mentioned in the original comment thread is:

# create table t (c TIMESTAMP(6) WITH TIME ZONE);
# insert into t (c) values (systimestamp);
# commit;
#
# Name: pandas
# Version: 1.5.2
# Name: SQLAlchemy
# Version: 1.4.44
# Name: cx-Oracle
# Version: 8.3.0
#
# Output is like:
#  0 2022-11-24 11:49:25.505773

import os
import platform

from sqlalchemy import create_engine
import pandas as pd

import cx_Oracle

if platform.system() == "Darwin":
    cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME") "/Downloads/instantclient_19_8")

username = os.environ.get("PYTHON_USERNAME")
password = os.environ.get("PYTHON_PASSWORD")
connect_string = os.environ.get("PYTHON_CONNECTSTRING")
hostname, service_name = connect_string.split("/")

engine = create_engine(f'oracle://{username}:{password}@{hostname}/?service_name={service_name}')

query = """select * from t"""
df = pd.read_sql_query(query, engine)
print(df)

CodePudding user response：

One solution is to cast the problematic columns as string, then convert in pandas.

query = "SELECT TO_CHAR(col) AS col FROM table"
df = pd.read_sql_query(query, cnxn)
df[col] = df[col].apply(pd.to_datetime, format="%d-%b-%y %I.%M.%S.%f %p %Z")