Insert blob data to MySQL

[Alex Martelli]

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.

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.

In both cases there is an alternative that hinges on "loading a
data file" (server-side) via a nonstandard SQL function (LOAD_FILE
in MySQL, LO_IMPORT in PostgreSQL) but almost always there
are too many pitfalls with that (you do need to get the data to the
DB server machine, which hampers architectures based on client
and server DB processes on separate machines; you need special
privileges in the database; with LO_IMPORT, you need the data
field to be of special type OID; ...) so I wouldn't follow that route.


Alex