在 Python Pandas 中将 NaN 转换为 Oracle Null [英] Converting NaN to Oracle Nulls within Python Pandas

问题描述

我在执行以下函数时遇到此错误 &这是因为尝试将 NaN 插入到数字列中.始终用 0 替换 NaN 有效，但用 None 替换则无效.任何想法将不胜感激

def insertData(table_name, schema_name, df):如果不是 df.empty:#input_data[file].fillna(0, inplace=True) # 这有效，但替换为 0 并不理想df.where(pd.notnull(df), None) #这不起作用值 = 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)返回 result_proxy别的:返回无

<块引用>

(cx_Oracle.DatabaseError) DPI-1055:值不是数字 (NaN)，不能用于 Oracle 数字

解决方案

好吧，Pandas 有它自己的美丽 DataFrame.to_sql() 方法，自动处理 NaN --> NULL 转换:

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

PS 如果您的 DF 有字符串 (object) 列 - 使用 dtype 参数 (请参阅此答案中的示例)

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

解决方案

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)

这篇关于在 Python Pandas 中将 NaN 转换为 Oracle Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！