Re: How do I convert numpy NaN objects to SQL nulls?

On Thu, Aug 20, 2015 at 8:34 PM, Gregory Arenius <gregory(at)arenius(dot)com> wrote:
> So, I'm trying to make a custom adapter to convert np.NaN to SQL null but
> everything I've tried results in the same NaN strings being inserted in the
> database.
>
> The code I'm currently trying is:
>
> def adapt_nans(null):
> a = adapt(None).getquoted()
> return AsIs(a)
>
> register_adapter(np.NaN, adapt_nans)
>
> I've tried a number of variations along this theme but haven't had any luck.

register_adapter takes a type as argument. np.NaN is a value of type
float instead:

In [3]: type(np.NaN)
Out[3]: float

so you should really customize the float adapter to do what you want.

def nan_to_null(f):
if f is np.NaN:
return psycopg2.extensions.AsIs('NULL')
else:
return psycopg2.extensions.Float(f)

psycopg2.extensions.register_adapter(float, nan_to_null)

>>> print cur.mogrify("select %s, %s", [10.0, np.NaN])
select 10.0, NULL

Note: the above is for clarity. For sake of
premature-micro-optimization I would actually write the adapter as:

def nan_to_null(f,
_NULL=psycopg2.extensions.AsIs('NULL'),
_NaN=np.NaN,
_Float=psycopg2.extensions.Float):
if f is not _NaN:
return _Float(f)
return _NULL

-- Daniele