[Python-Dev] BLOBs in Pg

[Steve Holden]

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/