How do I convert numpy NaN objects to SQL nulls?
This answer is an alternate version of Gregory Arenius's Answer. I have replaced the conditional statement to work on any Nan value by simply checking if the value is equal to itself.
def nan_to_null(f,
_NULL=psycopg2.extensions.AsIs('NULL')
_Float=psycopg2.extensions.Float)):
if f != f:
return _NULL
else:
return _Float(f)
psycopg2.extensions.register_adapter(float, nan_to_null)
If you check if a nan value is equal to itself you will get False. The rational behind why this works is explained in detail in Stephen Canon's answer.
I believe the easiest way is:
df.where(pd.notnull(df), None)
Then None
is "translated": to NULL
when imported to Postgres.
The code I was trying previously fails because it assumes that np.Nan is its own type when it is actually a float. The following code, courtesy of Daniele Varrazzo on the psycopg2 mailing list, does the job correctly.
def nan_to_null(f,
_NULL=psycopg2.extensions.AsIs('NULL'),
_Float=psycopg2.extensions.Float):
if not np.isnan(f):
return _Float(f)
return _NULL
psycopg2.extensions.register_adapter(float, nan_to_null)