[Python-Dev] BLOBs in Pg
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
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="@@@",
curs = conn.cursor()
curs.execute("DELETE FROM tst")
curs.execute("INSERT INTO tst (byt) VALUES (%s)",
("".join(chr(i) for i in range(256)), ))
curs.execute("SELECT byt FROM tst")
for st, in curs.fetchall():
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
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.
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