From: | Gregory Arenius <gregory(at)arenius(dot)com> |
---|---|
To: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
Cc: | "psycopg(at)postgresql(dot)org" <psycopg(at)postgresql(dot)org> |
Subject: | Re: How do I convert numpy NaN objects to SQL nulls? |
Date: | 2015-08-27 19:21:31 |
Message-ID: | CAMPu6475gY=k+y0PPkQr7cNQUXKK+5vqj_PR4iVez27-fxU8qQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
That converter code works perfectly. Thank you very much for your time.
On Fri, Aug 21, 2015 at 3:41 AM, Daniele Varrazzo <
daniele(dot)varrazzo(at)gmail(dot)com> wrote:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Brooks | 2015-08-30 18:59:13 | VARCHAR versus TEXT when using psychopg2, sqlalchemy and redshift |
Previous Message | Daniele Varrazzo | 2015-08-21 10:41:00 | Re: How do I convert numpy NaN objects to SQL nulls? |