How to change datetime.date type in outputtypehandler?

user_12802756 0
Viewed 443

Is it possbie to change datetime data type returned from outputtypehandler using Python's Cx_Oracle into string type (format yyyymmdd)?

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType in (cx_Oracle.DB_TYPE_TIMESTAMP, cx_Oracle.DB_TYPE_DATE):
        return cursor.var(datetime.date,cursor.arraysize)
cx-oracle python-3.x oracle python
1 Answers

The code:

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type in (cx_Oracle.DB_TYPE_TIMESTAMP_TZ, cx_Oracle.DB_TYPE_DATE):
        return cursor.var(cx_Oracle.STRING, arraysize=cursor.arraysize)

connection.outputtypehandler = output_type_handler

with connection.cursor() as cursor:
    sql = """alter session set nls_date_format = 'YYYYMMDD' nls_timestamp_tz_format = 'YYYYMMDD'"""
    cursor.execute(sql)

    sql = """select systimestamp, sysdate from dual"""
    for r in cursor.execute(sql):
        print(r)

Has output:

('20201221', '20201221')

If you rely on the client-side NLS date format settings as above, don't forget to use a session callback to execute the ALTER statement with a session pool. But it's probably wiser to do the formatting to YYYYMMDD in the SQL query itself.

If you have other timezone types, you'll need to update the output type handler if test, and the ALTER statement.

user_47990350
Related