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

On May 10, 2012 11:18 PM, "Andy Casey" <andycasey@gmail.com> wrote:
>
> Hi there,
>
> I have used psycopg2's adapters and converters in the past with numeric[] arrays in PostgreSQL, and now I'm trying to use them with binary data types (bytea). I've outlined my problem in detail with some test code which illustrates what works (without adapters/converters), and how it fails when I build my working code into using an adapter and converter:
>
> http://stackoverflow.com/questions/10529351/using-a-psycopg2-converter-to-retrieve-bytea-data-from-postgresql
>
> Would you mind taking a look and providing some advice?
>
> Thanks,
> Andy

As answered on SO:

"""
The format you see in the debugger is easy to parse: it is PostgreSQL hex binary format (http://www.postgresql.org/docs/9.1/static/datatype-binary.html). psycopg can parse that format and return a buffer containing the data; you can use that buffer to obtain an array. Instead of writing a typecaster from scratch, write one invoking the original func and postprocess its result. Sorry but I can't remember its name now and I'm writing from a mobile: you may get further help from the mailing list.
"""

I think you are close to the solution of your case: please keep us informed!

-- Daniele

On Mon, May 21, 2012 at 5:32 AM, Andy Casey <andycasey@gmail.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

Brilliant!

Thank you immensely Daniele!

Cheers,
Andy

Sent from my magical and revolutionary device

On 21/05/2012, at 6:22 PM, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:

> On Mon, May 21, 2012 at 5:32 AM, Andy Casey <andycasey@gmail.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