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

On 08/20/2015 12:34 PM, Gregory Arenius wrote:
> have a Pandas dataframe that I'm inserting into an SQL database. I'm
> using Psycopg2 directly to talk to the database, not SQLAlchemy, so I
> can't use Pandas built in to_sql functions. Almost everything works as
> expected except for the fact that numpy np.NaN values get converted to
> text as NaN and inserted into the database. They really should be
> treated as SQL null values.
>
> 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:
>
> |defadapt_nans(null):a
> =adapt(None).getquoted()returnAsIs(a)register_adapter(np.NaN,adapt_nans)|
>
> I've tried a number of variations along this theme but haven't had any luck.

How about having Pandas do the work? From here:

https://github.com/pydata/pandas/issues/1972

import pandas as pd

df = pd.DataFrame([
     dict(a=1, p=0),
     dict(a=2, m=10),
     dict(a=3, m=11, p=20),
     dict(a=4, m=12, p=21)
     ], columns=('a', 'm', 'p', 'x'))

In [4]: df
Out[4]:
    a   m   p   x
0  1 NaN   0 NaN
1  2  10 NaN NaN
2  3  11  20 NaN
3  4  12  21 NaN

In [5]:  df.where(pd.notnull(df), None)
Out[5]:
    a     m     p     x
0  1  None     0  None
1  2    10  None  None
2  3    11    20  None
3  4    12    21  None

>
> Cheers,
>
> Greg
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Aug 20, 2015 at 8:34 PM, Gregory Arenius <gregory@arenius.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