Insert blob data to MySQL

[Gerhard Häring]

Alex Martelli wrote in comp.lang.python:
> Steve Holden wrote:
>         ...
>>>>> s = "string data but it could be anything"
>>>>> cc.execute("""INSERT INTO blobtbl (b) VALUES(%s)""", (s,))
>> 1L
> 
> If s is 'anything' (e.g., a cPickle.dumps with binary option set),
> MySQLdb.escape_string(s) is likely to work better than plain s
> as the value to put in the tuple that's execute's second arg.

AFAIC you shouldn't need to do this. converters.Thing2Literal is the
default converter in MySQL, which calls string_literal in _mysql,
which in turn calls mysql_real_escape_string.

> BTW, with PostgreSQL, recent releases (since 7.1 I believe, i.e.,
> since 'toast' was integrated in the mainstream codebase), you
> can use basically the same approach, except you call the field
> type BYTEA rather than BLOB and psycopg.Binary(s) is the
> function you call to 'escape' the bytestring to place there.

This is also needed in pyPgSQL, but just because a BYTEA is different
from a VARCHAR/TEXT field in that it can contain null characters.
Normally, all conversions are done automatically.

pyPgSQL also has a feature that I like a lot: you can open BLOBs as
file-like objects and use read, readlines, etc. on them:

from pyPgSQL import PgSQL

#   BLOB data
blob = "".join(map(lambda x: chr(x), range(256)))
#   Open connection, get cursor
db = PgSQL.connect()
cursor = db.cursor()
#   Create a PgLargeObject
lo = db.binary(blob)
#   Create table and insert the large object
cursor.execute("create table blobtest(id serial, b oid)")
cursor.execute("insert into blobtest(b) values (%s)", lo)
cursor.execute("select * from blobtest")
result = cursor.fetchone()
#   Open the large object as a file-like object
result.b.open("r")
returned_blob = result.b.read()
assert(blob == returned_blob)

This uses several nonstandard feature, one I like is that the
PgResultSet that's returned from fetchone can be access as a tuple, as
a dictionary or just by accessing the fields as attributes, which I
did above.

I've given up on being SQL compatible on multiple databases. And on
compatibility with multiple DB-API modules, I've almost given up,
mostly because I like some of the nonstandard features of pyPgSQL and
MySQLdb a lot.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))