Year is Out of Range Cx_Oracle Python

Question

The below code gives me usually results to query data, except this time...

def oracle(user, pwd, dsn, sql, columns):

    # Connection to databases
    con = cx_Oracle.connect(user=user, password=pwd, dsn=dsn, encoding="UTF-8")
    cur = con.cursor()

    # Check Connection
    print('Connected')

    # Create DF
    df = pd.DataFrame(cur.execute(sql).fetchall(), columns= columns , dtype = 'str') 

    print('Shape:', df.shape)

    return df

Below is the error.

ValueError                                Traceback (most recent call last)
<timed exec> in <module>

<timed exec> in oracle_aml(user, pwd, dsn, sql)

<timed exec> in oracle(user, pwd, dsn, sql, columns)

ValueError: year -7 is out of range

Question: How can I overpass this Warning? It's says that for some date columns, the value = -7. This is due to a misspelling in DB.

I thought to add the below expression in order to ignore columns types but not really helpful.

dtype = 'str'

Thanks to anyone helping!

Answer 1

Thanks to this link, I have been able to solve my problem

Below is the full code used (worked for me)

import cx_Oracle
import datetime
import os
os.environ['NLS_DATE_FORMAT'] = 'YYYY-MM-DD HH24:MI:SS'


def DateTimeConverter(value):
    if value.startswith('9999'):
        return None
    return datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S')


def OutputHandler(cursor, name, defaulttype, length, precision, scale):
    if defaulttype == cx_Oracle.DATETIME:
        return cursor.var(cx_Oracle.STRING, arraysize=cursor.arraysize, outconverter=DateTimeConverter)


def oracle(user, pwd, dsn, sql, columns):

    # Connection to databases
    con = cx_Oracle.connect(user=user, password=pwd, dsn=dsn, encoding="UTF-8")
    con.outputtypehandler = OutputHandler

    # Cursor allows Python code to execute PostgreSQL command in a database session
    cur = con.cursor()

    # Check Connection
    print('Connected')

    # Create DF
    df = pd.DataFrame(cur.execute(sql).fetchall(), columns= columns, dtype='object')[:]

    print('Shape:', df.shape)

    return df