[Python-Dev] BLOBs in Pg (original) (raw)

Steve Holden steve at holdenweb.com
Thu Apr 9 22:42:21 CEST 2009

Tony Nelson wrote:

At 21:24 +0400 04/09/2009, Oleg Broytmann wrote:

On Thu, Apr 09, 2009 at 01:14:21PM -0400, Tony Nelson wrote:

I use MySQL, but sort of intend to learn PostgreSQL. I didn't know that PostgreSQL has no real support for BLOBs. I think it has - BYTEA data type. So it does; I see that now that I've opened up the PostgreSQL docs. I don't find escaping data to be a problem -- I do it for all untrusted data. You shouldn't have to when you are using parameterized queries.

So, after all, there isn't an example of a database that makes onerous the storing of email and other such byte-oriented data, and Python's email package has no need for workarounds in that area.

Create a table:

CREATE TABLE tst ( id serial, byt bytea, PRIMARY KEY (id) ) WITH (OIDS=FALSE) ; ALTER TABLE tst OWNER TO steve;

The following program prints "0":

import psycopg2 as db conn = db.connect(database="maildb", user="@@@", password="@@@", host="localhost", port=5432) curs = conn.cursor() curs.execute("DELETE FROM tst") curs.execute("INSERT INTO tst (byt) VALUES (%s)", ("".join(chr(i) for i in range(256)), )) conn.commit() curs.execute("SELECT byt FROM tst") for st, in curs.fetchall(): print len(st)

If I change the date to use range(1, 256) I get a ProgrammingError fron PostgreSQL "invalid input syntax for type bytea".

If I can't pass a 256-byte string into a BLOB and get it back without anything like this happening then there's something in the chain that makes the database useless. My current belief is that this something is fairly deeply embedded in the PostgreSQL engine. No "syntax" should be necessary.

I suppose if we have to go round again on this we should take it to email as we have gotten pretty far off-topic for python-dev.

regards Steve

Steve Holden +1 571 484 6266 +1 800 494 3119 Holden Web LLC http://www.holdenweb.com/ Watch PyCon on video now! http://pycon.blip.tv/

More information about the Python-Dev mailing list