Skip site navigation (1) Skip section navigation (2)

Re: Using a psycopg2 converter to retrieve bytea data from PostgreSQL

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: Andy Casey <andycasey(at)gmail(dot)com>
Cc: psycopg(at)postgresql(dot)org
Subject: Re: Using a psycopg2 converter to retrieve bytea data from PostgreSQL
Date: 2012-05-21 08:22:56
Message-ID: CA+mi_8b_ObSun-S-7iV1x+JFGC4A16+itM+CZRUU5JjVm=LAcw@mail.gmail.com (view raw or flat)
Thread:
Lists: psycopg
On Mon, May 21, 2012 at 5:32 AM, Andy Casey <andycasey(at)gmail(dot)com> wrote:
> Hi,
>
> I'm just re-raising this problem to anyone on the mailing list, because I
> haven't had any luck on StackOverflow, or any suggested answers from the
> mailing list:
>
>  http://stackoverflow.com/questions/10529351/using-a-psycopg2-converter-to-retrieve-bytea-data-from-postgresql

Sorry, I forgot to get back on the question. The default bytea
typecaster (which is the object that can parse the postgres binary
representation and return a buffer object out of it) is
psycopg2.BINARY. We can use it to create a typecaster converting to
array instead:


In [1]: import psycopg2

In [2]: import numpy as np

In [3]: a = np.eye(3)

In [4]: a
Out[4]:
array([[ 1.,  0.,  0.],
       [ 0.,  1.,  0.],
       [ 0.,  0.,  1.]])

In [5]: cnn = psycopg2.connect('')

In [6]: cur = cnn.cursor()


# The adapter: converts from python to postgres
# note: this only works on numpy version whose array support the
buffer protocol,
# e.g. it works on 1.5.1 but not on 1.0.4 on my tests.

In [12]: def adapt_array(a):
   ....:     return psycopg2.Binary(a)
   ....:

In [13]: psycopg2.extensions.register_adapter(np.ndarray, adapt_array)


# The typecaster: from postgres to python

In [21]: def typecast_array(data, cur):
   ....:     if data is None: return None
   ....:     buf = psycopg2.BINARY(data, cur)
   ....:     return np.frombuffer(buf)
   ....:

In [24]: ARRAY = psycopg2.extensions.new_type(psycopg2.BINARY.values,
'ARRAY', typecast_array)

In [25]: psycopg2.extensions.register_type(ARRAY)


# Now it works "as expected"

In [26]: cur = cnn.cursor()

In [27]: cur.execute("select %s", (a,))

In [28]: cur.fetchone()[0]
Out[28]: array([ 1.,  0.,  0.,  0.,  1.,  0.,  0.,  0.,  1.])


As you know, np.frombuffer(a) loses the array shape, so you will have
to figure out a way to preserve it.

Cheers,

-- Daniele

In response to

Responses

psycopg by date

Next:From: Andy CaseyDate: 2012-05-21 08:49:44
Subject: Re: Using a psycopg2 converter to retrieve bytea data from PostgreSQL
Previous:From: Andy CaseyDate: 2012-05-21 04:32:55
Subject: Re: Using a psycopg2 converter to retrieve bytea data from PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group