3

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Varun Khanna
  • 31
  • 1
  • 2
  • 1
    Btw `df.where(pd.notnull(df), None)` [returns a **new** DataFrame](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.where.html) with the replacements, so that line is a no-operation as is. With `inplace=True` the operation would take place in place, but that'd then change the `dtype` to object. – Ilja Everilä Jan 17 '18 at 12:17

1 Answers1

4

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)

MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419