CODEWITHSUNDEEP
pythonsqlsql-serverdatabasepandas
MTANG
MTANG

Reputation: 516

pandas to_sql() with NUMERIC data type

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.

Upvotes: 4

Views: 4340

Answers (2)

Finrod Felagund
Finrod Felagund

Reputation: 1293

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.

Upvotes: 2

Alexander McFarlane
Alexander McFarlane

Reputation: 11293

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

References

Upvotes: 0

Related Questions