Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

How can we convert pandas NaN to SQL NULL?

shubham_rai
Level 1
How can we convert pandas NaN to SQL NULL?

I am pushing DataFrame from Dataiku to PostgreSQL database using SQLExecutor2 library. Is there a way I can convert pandas 'NaN' to SQL NULL value?

I tried converting NaN to None also but it's not working.

Then I used psychopg2 library to convert NaN to NULL, its working fine until conversion but while writing SQL to database it fails.

I printed SQL string to check, in string instead of NULL, it is passing psychopg object <psycopg2.extensions.AsIs object at 0x7f837256dd80>  This may be the possible reason of failure.


Operating system used: Linux


Operating system used: Linux

0 Kudos
1 Reply
MiguelangelC
Dataiker
Dataiker

Hi,

Starting in version 0.15. Panda's supports writting NaN values to databases, which will be written as Null there: https://github.com/pandas-dev/pandas/pull/8208

For versions 0.14 and older, it was a known issue Panda did not handle these values very well. The workaround transforming the NaN to None is the prefered one.

Have you followed the instructions on: https://github.com/pandas-dev/pandas/issues/4199?

What error do you get when trying to convert to None?

 

0 Kudos