How to handle None values in list which fetched from Oracle Database?-CodePudding

Scenario: I am fetching data from oracle data using python code and once the data is fetched from tables it stores in a list and all the null values on the database come as None in the list while inserting data from this list to different databases these None is being treated as a string and leading to failure in the insertion process.

Table Data:

INSERT INTO table1 VALUES(1,'ABCD',NULL);
INSERT INTO table1 VALUES(2,'XYZ',NULL);
INSERT INTO table1 VALUES(3,'AB',NULL);

Python Code:

cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_9")
dsn = cx_Oracle.makedsn(host='XX.XX.XX.XX', port=XX, sid='XX')

oracle_conn = cx_Oracle.connect(user="XX", password="XX", dsn=dsn)
# print(conn.version)

oracle_cursor = oracle_conn.cursor()

oracle_cursor.execute("SELECT col1, col2, col3 FROM table1 WHERE rownum < 10000")
rows = oracle_cursor.fetchall()
print(rows)

# below code is getting executed for snowflake
sql = 'INSERT INTO table2( col1, col2, col3 ) VALUES '
for rec in rows:
    sql = sql   str(rec)   ","

snowflake_cursor.execute(sql)

Error: invalid identifier 'NONE' (line 10)

CodePudding user response：

Since None values are generated for each NULL values of the database while fetching them. You can spontaneously get rid of them during the execution of the INSERT statement by preparing the parametrized form of it such as

oracle_conn = cx_Oracle.connect(user, password, host ':' port '/' dbname)
oracle_cursor = oracle_conn.cursor()

oracle_cursor.execute("SELECT * FROM table1 WHERE rownum < 10000") 
# the columns of the table is not listed individually in the line above, because their count already implicitly presented to be three by the INSERT statements to populate the table
rows = oracle_cursor.fetchall()

# below code is getting executed for snowflake
#prepare concateneted list of bind variables as "?"
vrs=""
for j in range(0,len(rows[0])):
    vrs = '?,' 

sql='INSERT INTO table2( col1, col2, col3 ) VALUES(' vrs.rstrip(",") ')'
snowflake_cursor.executemany(sql,rows)
snowflake_conn.commit()

CodePudding user response：

Although your problem seems to be with lack of binding on insert, I wanted to post a related example for other people who land on this issue.

The general solution for changing fetched data is to use a typehandler and outconverter. For example, if you want to change NULLs to an empty string you would use:

def out_converter(value):
    if value is None:
        return ''
    return value

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type in (cx_Oracle.DB_TYPE_VARCHAR, cx_Oracle.DB_TYPE_CHAR):
        return cursor.var(str, size, arraysize=cur.arraysize,
                          outconverter=out_converter)

connection.outputtypehandler = output_type_handler

This is in the cx_Oracle documentation Changing Query Results with Outconverters