I am trying to insert data into an oracle table, while in the process I am getting this error:

cx_Oracle.NotSupportedError: Python value of type NAType not supported

My script:

data = data_df.values.tolist()
sql = "insert into %s(%s) values(%s)" %(table_name, cols, values)
cursor.executemany(sql, data)

I have tried the solution given by the cx-Oracle documentation(https://cx-oracle.readthedocs.io/en/7.2.3/user_guide/sql_execution.html#inserting-nulls). But it returned this error:

cx_Oracle.DatabaseError: ORA-04043: object SDO_GEOMETRY does not exist

Because it's in production environment, that I can't do anything to oracle's settings. Is there any solution that can I insert null value into the oracle table?

CodePudding user response：

Here is a minimal example reproducing your problem

d  = {'id': [1, pd.NA], 'col': [ pd.NA,'x' ]}
df = pd.DataFrame(d)

print(df.values.tolist())
cur.executemany("insert into tab(id, col) values (:1, :2)", df.values.tolist()) 

[[1, <NA>], [<NA>, 'x']]
...
cx_Oracle.NotSupportedError: Python value of type NAType not supported.

Note that the table definition is as follows

 create table tab(id int, col varchar2(10));
 

The error appears while insering the NA in a VARCHAR2 column - would it be a number column, you'll observe TypeError: expecting number

The solution is to remove the NA and to replace them with None

df.loc[0,'col'] = None
df.loc[1,'id'] = None   

The data now looks as follows and the insert works fine

[[1, None], [None, 'x']]