Insert blob data to MySQL

[Steve Holden]

"Alex Martelli" <aleax at aleax.it> wrote in message
news:EXKA8.2549$CN3.94262 at news2.tin.it...
> 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.
>
Granted.

> 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.
>
Not yet done more than looked at PostgreSQL, but I'll get there when the
Linux server comes back up in a fe days.

> 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.
>
Ah, the joy of a standards-based database environment. Wouldn't it be nice
if all the database producers decided to break the SQL standard in the same
way? Thanks for your useful addition to my original somewhat over-simplified
note.

you-go-get-coffee-now-i'm-off-to-bed-ly y'rs  - steve
--
Steve Holden: http://www.holdenweb.com/ ; Python Web Programming:
http://pydish.holdenweb.com/pwp/