pandas to_sql() with NUMERIC data type

Question

I loaded a Pandas DataFrame by reading from a file and doing some pre-processing - that has a few columns of numbers. such as

            value
1     13654654328.4567895
2     NULL
3     54643215587.6875455

In order not to lose accuracy I plan to store it as NUMERIC in SQL Server. Since I do not want Pandas to convert my data into float, I load it as string and then use df.to_sql() to insert into SQL.

It worked fine if no NULL exists. However if it contains null, no matter I put "" or np.nan for null, it reported the error as "Error converting data type nvarchar to numeric." Seems that it automatically converts it into empty string which could not be cast into NUMERIC in SQL Server.

Is there any way that I could handle this problem. Hopefully done everything in Python and no further SQL script is needed.

user_7423100 · Answer

I haven't used .to_sql method ever, but I would suppose that you need to replace your NULL values with None values. For example:

df.replace([np.nan], [None], inplace=True)

Btw np.nan is type float. In SQL, NULL is type "nothing", equivalent of this in Python is None. Also "" and "NULL" would be considered as string.

user_4013571 · Answer

I thought I'd add more detail to complement to answer below ...

As per PEP 249 -- Python Database API Specification v2.0

SQL NULL values are represented by the Python None singleton on input and output.

You are having the problem because you are trying to send mixed types to the db. You need to replace all intended blank values with None

pandas to_sql() with NUMERIC data type

2 Answers

References