Storing files in a BLOB field via SQL

[Michael Porter]

"Juergen Gerner" <J.Gerner at GernerOnline.de> wrote in message
news:5a2071a0.0406061231.59c3bede at posting.google.com...
> Hello Python fans,
>
> The main problem is the handling of the binary data. There might be
> errors in the SQL syntax if some special chars (quotas etc.) appear,
> or the SQL statement is incorrect because of strange chars that can't
> be encoded by the current codeset. Another problem is, you can't strip
> these chars because you would change the binary data or make it bigger
> than 64k.
>
> Does anybody of you have an idea?
> Any suggestions would be very helpful.

You can either use the MySQL hex literal format (x'AABBCC'...) or use the
Python DB API which will handle the parameter conversion for you...

In the first case your query becomes somethings like:

query = "INSERT INTO table (order,data) VALUES (%i,x'%s')" % (order,
data.encode('hex'))

In the second, preferable version you use something like:

cur = conn.cursor()
cur.execute("INSERT INTO table (order,data) VALUES (?,?)", (order, data))

and the DBAPI/Database driver takes care of the rest.


> Additionally, I want to compress the data and store a checksum
> somewhere. Any hint (links, sites, ...) is welcome...!

compressed = data.encode('zip')  # Compress the data


Mike.