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
Answers
-
Miguel Angel Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 118 DataikerHi,
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?