Want to Stop Rebuilding "Expensive" Parts of your Flow? Explicit Builds are the Answer!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