How to resolve cx_Oracle.DatabaseError: DPI-1043: invalid number error?

Question

I have a pandas df like this:

      A         B       C             D            E       F        G        H
0  1033548.0   0.0  20190324.0   2019-04-13       NaT   10176.0    NaN  40360001.0
1  1033549.0   0.0  20190324.0   2019-04-13 2019-04-13   10182.0    NaN  41360562.0
2  1033550.0   0.0  20190324.0   2019-04-13       NaT   10433.0    NaN  45180156.0
3  1033551.0   0.0  20190324.0   2019-04-13       NaT   10019.0    123         NaN
4  1033552.0   0.0  20190324.0   2019-04-13       NaT   10333.0    NaN  40120165.0
5  1033553.0   0.0  20190324.0   2019-04-13       NaT   10246.0    NaN  40820596.0 
6  1033554.0   0.0  20190324.0          NaT       NaT   10107.0    NaN         NaN
7  1033555.0   0.0  20190324.0   2019-04-13       NaT   10131.0    NaN  41190549.0
8  1033556.0   0.0  20190324.0   2019-04-13       NaT   10057.0    NaN  40430581.0
9  1033557.0   0.0  20190324.0   2019-04-13       NaT   10009.0    NaN  40360012.0

I want to write this df to my Oracle Database Table using SQL Insert Statement. But since my df contains NaNs and NaTs, so its giving me error: cx_Oracle.DatabaseError: DPI-1043: invalid number. How can I write this df to the oracle table using SQL which will not give me any error? Any help would be appreciated.

Similar question: https://stackoverflow.com/questions/48299282/converting-nan-to-oracle-nulls-within-python-pandas — kfinity, Feb 05 '20 at 14:05

score 1 · Answer 1 · answered Jul 29 '20 at 14:21

1

Prachi.

Your column G is of type number in Oracle, right? Thus, I believe you need to set your NaNs with 0 (zeros), if possible.

Or make 2 types of inserts:

With column G, if it is exists on DF;
Without column G, if it doesn't exists on DF.

answered Jul 29 '20 at 14:21

GRCorso

11
1

khalySeb · Answer 2 · 2021-06-11T05:38:21.747

0

You have to replace your {float} nan by None in the binding list.

import math

bind_insert = lines_to_insert_df.values.tolist()

for b in bind_insert :

    for index, value in enumerate(b):
        if isinstance(value, float) and math.isnan(value):
            b[index] = None
        elif isinstance(value, type(pd.NaT)):
            b[index] = None

con.cursor().executemany(query, bind_insert)

edited Jun 11 '21 at 05:38

answered Jun 09 '21 at 15:19

khalySeb

1
1

Thanks elena.kim. There is the same issue for not a time data (Nat). – khalySeb Jun 11 '21 at 05:38

score 0 · Answer 3 · answered Oct 07 '21 at 20:23

0

I had the same issue. I do not think it is a NaN issue since it works ok for me with NaN values.

In my case there was a float column that had inf values due to divide by 0. Once I took care of this, the write to Oracle issue went away.

answered Oct 07 '21 at 20:23

Nikhil Gupta

1,436
12
15

How to resolve cx_Oracle.DatabaseError: DPI-1043: invalid number error?

3 Answers3