CODEWITHSUNDEEP
pythonpandassqlalchemynullsql-insert
Varun Khanna
Varun Khanna

Reputation: 31

Converting NaN to Oracle Nulls within Python Pandas

I get this error on executing the below function & It is because NaN is tried to be inserted into numeric column. Replacing NaN with 0 throughout does work but replacement with None does not. Any ideas would be appreciated

def insertData(table_name, schema_name, df):
    if not df.empty:

        #input_data[file].fillna(0, inplace=True) # This works but replacing with 0's is not ideal
        df.where(pd.notnull(df), None) #This does not work
        values = df.to_dict(orient='records')
        table = sa.Table(table_name , conndict['meta'], autoload=True, schema=schema_name)
        result_proxy = conndict['conn'].execute(table.insert(), values)
        return result_proxy
    else:
        return None

(cx_Oracle.DatabaseError) DPI-1055: value is not a number (NaN) and cannot be used in Oracle numbers

Upvotes: 3

Views: 8220

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

well, Pandas has it's own beautiful DataFrame.to_sql() method, which takes care of NaN --> NULL conversion automatically:

df.to_sql('tab_name', sql_alchemy_conn, if_exists='append', index=False)

PS in case your DF has string (object) columns - use dtype parameter (see an example in this answer)

Upvotes: 4

Related Questions