Thread: bytea extraction
How do I grab a bytea object stored in a postgres database and write it to the filesystem? Say I have a .jpeg stored in a table called pictures. I want to extra that object (select picture from pictures), then writeit to a file called picture.jpg through the use of a python script. I have tried several ways but the end file is always messed up and not the original picture. Any pointers? Thanks Rich On 8 Jan 2014, at 13:20, psycopg-owner@postgresql.org wrote: > Message Digest > Volume 1 : Issue 248 : "index" Format > > Messages in this Issue: > 201401/2 : Psycopg 2.5.2 released
On 02/03/2014 09:50 AM, Rich Harley wrote: > How do I grab a bytea object stored in a postgres database and write it to the filesystem? > > Say I have a .jpeg stored in a table called pictures. I want to extra that object (select picture from pictures), thenwrite it to a file called picture.jpg through the use of a python script. > I have tried several ways but the end file is always messed up and not the original picture. > Any pointers? Well the default format for bytea has changed with Postgres versions and how Psycopg handles same has changed over time. So to begin to answer this we will need to know what versions of Postgres and psycopg2 you are using? > > Thanks > Rich > -- Adrian Klaver adrian.klaver@gmail.com
Ah, I see. It’s postgres 9.1 and psycopg1 (through Zope 2.11) - an old one but we have found no need to upgrade as yet becausewe only use very simple data types. When the data goes in it goes through psycopg.Binary(photo). So it’s really the reverse of that I need. On 3 Feb 2014, at 17:54, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 02/03/2014 09:50 AM, Rich Harley wrote: >> How do I grab a bytea object stored in a postgres database and write it to the filesystem? >> >> Say I have a .jpeg stored in a table called pictures. I want to extra that object (select picture from pictures), thenwrite it to a file called picture.jpg through the use of a python script. >> I have tried several ways but the end file is always messed up and not the original picture. >> Any pointers? > > Well the default format for bytea has changed with Postgres versions and how Psycopg handles same has changed over time.So to begin to answer this we will need to know what versions of Postgres and psycopg2 you are using? > >> >> Thanks >> Rich >> > > > > -- > Adrian Klaver > adrian.klaver@gmail.com
On 02/03/2014 09:59 AM, Rich Harley wrote: > Ah, I see. It’s postgres 9.1 and psycopg1 (through Zope 2.11) - an old one but we have found no need to upgrade as yetbecause we only use very simple data types. > > When the data goes in it goes through psycopg.Binary(photo). > So it’s really the reverse of that I need. I can't even find docs for psycopg1, so from the current documentation: http://initd.org/psycopg/docs/faq.html?highlight=bytea Transferring binary data from PostgreSQL 9.0 doesn’t work. PostgreSQL 9.0 uses by default the “hex” format to transfer bytea data: the format can’t be parsed by the libpq 8.4 and earlier. The problem is solved in Psycopg 2.4.1, that uses its own parser for the bytea format. For previous Psycopg releases, three options to solve the problem are: set the bytea_output parameter to escape in the server; execute the database command SET bytea_output TO escape; in the session before reading binary data; upgrade the libpq library on the client to at least 9.0. -- Adrian Klaver adrian.klaver@gmail.com
Great, thanks Adrian. On 3 Feb 2014, at 18:07, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 02/03/2014 09:59 AM, Rich Harley wrote: >> Ah, I see. It’s postgres 9.1 and psycopg1 (through Zope 2.11) - an old one but we have found no need to upgrade as yetbecause we only use very simple data types. >> >> When the data goes in it goes through psycopg.Binary(photo). >> So it’s really the reverse of that I need. > > I can't even find docs for psycopg1, so from the current documentation: > > http://initd.org/psycopg/docs/faq.html?highlight=bytea > > > Transferring binary data from PostgreSQL 9.0 doesn’t work. > > PostgreSQL 9.0 uses by default the “hex” format to transfer bytea data: the format can’t be parsed by the libpq 8.4and earlier. The problem is solved in Psycopg 2.4.1, that uses its own parser for the bytea format. For previous Psycopgreleases, three options to solve the problem are: > > set the bytea_output parameter to escape in the server; > execute the database command SET bytea_output TO escape; in the session before reading binary data; > upgrade the libpq library on the client to at least 9.0. > > > > > > > -- > Adrian Klaver > adrian.klaver@gmail.com