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

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