Python, PostgreSQL and bytea

Morten Goodwin Olsen

Python, PostgreSQL and bytea

Hi
I am having a bit of trouble inserting images into a postgres database
using python. The images should be stored in a bytea field.
I have tried both with an odbc connection and psycopg.

The python code is as following:

conn = PgSQL.connect(connectionString) # or psycopg og odb
curs = conn.cursor()
data = {}
blob = conn.binary(open('temp.jpg','rb').read())
sql = "Insert into imagetabel(image) values(%s )" print sql
curs.execute(sql,blob)

Using psycopg, the error is the follwing:
ProgrammingError: syntax error at or near "
" at character 1

Using odcb, the error is:
TypeError: argument 1 must be string without null bytes, not str

While escaping the null bytes (string.replace(..., \0, "") - just to
see if the helped), the error changed to dbi.operation-error: ODBC
escape convert error in EXEC

Creating the same application using Java went without any errors.

I would be glad if someone new how to get around this problem.

Morten Goodwin Olsen

Jul 18 '05 #1

Alex Martelli

Morten Goodwin Olsen <go*****@realgoodwin.com> wrote:
...

I am having a bit of trouble inserting images into a postgres database
using python. The images should be stored in a bytea field.
I have tried both with an odbc connection and psycopg. ... I would be glad if someone new how to get around this problem.

You need to escape the binary data with psycopg.Binary. The full recipe
is in the Python Cookbook (1st edition, but I think I'll keep it for the
2nd edition as well), but basically it goes down to:

cursor.execute("CREATE TABLE justatest (name TEXT, ablob BYTEA)")
...
sql = "INSERT INTO justatest VALUES(%s, %s)"
for name in names:
cursor.execute(sql, (name, psycopg.Binary(data[name])) )

BTW, from somewhere on O'Reilly's site I believe you can download, for
free, a zipfile with all the code from the (printed, 1st edition) Python
Cookbook; doing that, unpacking the file, and grepping for BYTEA would
have gotten you the solution (although it's clearly nicer to read the
discussion too, and by buying the book you're also contributing
something to the Python Software Foundation, but, that _is_ by the
by;-).
Alex

Jul 18 '05 #2

Alexis Roda

Morten Goodwin Olsen wrote:

Python, PostgreSQL and bytea

Hi
I am having a bit of trouble inserting images into a postgres database
using python. The images should be stored in a bytea field.
I have tried both with an odbc connection and psycopg.

The python code is as following:

conn = PgSQL.connect(connectionString) # or psycopg og odb
curs = conn.cursor()
data = {}
blob = conn.binary(open('temp.jpg','rb').read())
sql = "Insert into imagetabel(image) values(%s )" print sql
curs.execute(sql,blob)

Using psycopg, the error is the follwing:
ProgrammingError: syntax error at or near "
" at character 1

Excerpt from postgres docs:

When entering bytea values, octets of certain values must be escaped
(but all octet values may be escaped) when used as part of a string
literal in an SQL statement. In general, to escape an octet, it is
converted into the three-digit octal number equivalent of its decimal
octet value, and preceded by two backslashes. Table 8-7 contains the
characters which must be escaped, and gives the alternate escape
sequences where applicable.

http://www.postgresql.org/docs/7.4/s...pe-binary.html
HTH
--
////
(@ @)
----------------------------oOO----(_)----OOo--------------------------
<> Ojo por ojo y el mundo acabara ciego
/\ Alexis Roda - Universitat Rovira i Virgili - Reus, Tarragona (Spain)
-----------------------------------------------------------------------

Jul 18 '05 #3

Peter Maas

Morten Goodwin Olsen schrieb:

Python, PostgreSQL and bytea

Hi
I am having a bit of trouble inserting images into a postgres database
using python. The images should be stored in a bytea field.
I have tried both with an odbc connection and psycopg.

Please read

- http://www.postgresql.org/docs/curre...pe-binary.html
- http://users.bigpond.net.au/rmoonen/...en1/BLOBs.html

The second URL is about storing images as BLOBs for retrieval
via ODBC / MS apps. The bytea type doesn't work in this case.

Mit freundlichen Gruessen,

Peter Maas

--
-------------------------------------------------------------------
Peter Maas, M+R Infosysteme, D-52070 Aachen, Tel +49-241-93878-0
E-mail 'cGV0ZXIubWFhc0BtcGx1c3IuZGU=\n'.decode('base64')
-------------------------------------------------------------------

Jul 18 '05 #4

Stuart Bishop

On 09/09/2004, at 2:07 AM, Morten Goodwin Olsen wrote:

I am having a bit of trouble inserting images into a postgres database
using python. The images should be stored in a bytea field.
I have tried both with an odbc connection and psycopg.

BYTEA works with psycopg. As per the DB-API spec, you should
use psycopg.BINARY(open('temp.jpg', 'rb').read()) though. If
you have no luck, you might want to try the psycopg mailing list
at http://www.initd.org/software/initd/psycopg (or look at the
samples that come with psycopg - there is a blob example using BYTEA
in there).
--
Stuart Bishop <st****@stuartbishop.net>
http://www.stuartbishop.net/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (Darwin)

iD8DBQFBR9abAfqZj7rGN0oRAr9FAJ4oUmzPVHmaj7mnapQB69 bu1pqUfACePklK
pYF6n0NFqw9MHozr9NAs/V0=
=b9Q+
-----END PGP SIGNATURE-----

Jul 18 '05 #5