Credit: Luther Blissett
The MySQLdb
module does not support full-fledged
placeholders, but you can make do with its
escape_string
function:
import MySQLdb, cPickle # Connect to a DB, e.g., the test DB on your localhost, and get a cursor connection = MySQLdb.connect(db="test") cursor = connection.cursor( ) # Make a new table for experimentation cursor.execute("CREATE TABLE justatest (name TEXT, ablob BLOB)") try: # Prepare some BLOBs to insert in the table names = 'aramis', 'athos', 'porthos' data = {} for name in names: datum = list(name) datum.sort( ) data[name] = cPickle.dumps(datum, 1) # Perform the insertions sql = "INSERT INTO justatest VALUES(%s, %s)" for name in names: cursor.execute(sql, (name, MySQLdb.escape_string(data[name])) ) # Recover the data so you can check back sql = "SELECT name, ablob FROM justatest ORDER BY name" cursor.execute(sql) for name, blob in cursor.fetchall( ): print name, cPickle.loads(blob), cPickle.loads(data[name]) finally: # Done. Remove the table and close the connection. cursor.execute("DROP TABLE justatest") connection.close( )
MySQL supports binary data (BLOBs and
variations thereof), but you need to be careful when communicating
such data via SQL. Specifically, when you use a normal
INSERT
SQL statement and
need to have binary strings among the VALUES
you’re inserting, you need to escape some characters
in the binary string according to MySQL’s own rules.
Fortunately, you don’t have to figure out those
rules for yourself: MySQL supplies a function that does all the
needed escaping, and
MySQLdb
exposes it to your Python programs as the
escape_string
function. This recipe shows a typical case: the BLOBs
you’re inserting come from
cPickle.dumps
, and so they may represent almost
arbitrary Python objects (although, in this case,
we’re just using them for a few lists of
characters). The recipe is purely demonstrative and works by creating
a table and dropping it at the end (using a
try/finally
statement to ensure that finalization
is performed even if the program terminates because of an uncaught
exception). With recent versions of MySQL and MySQLdb, you
don’t need to call the
escape_string
function anymore, so you can change
the relevant statement to the simpler:
cursor.execute(sql, (name, data [name]))
An alternative is to save your binary data to a temporary file and
use MySQL’s own
server-side LOAD_FILE
SQL function. However, this
works only when your program is running on the same machine as the
MySQL database server, or the two machines at least share a
filesystem on which you can write and from which the server can read.
The user that runs the SQL including the LOAD_FILE
function must also have the FILE
privilege in
MySQL’s grant tables. If all conditions are met,
here’s how we can instead perform the insertions in
the database:
import tempfile tempname = tempfile.mktemp('.blob') sql = "INSERT INTO justatest VALUES(%%s, LOAD_FILE('%s'))"%tempname for name in names: fileobject = open(tempname,'wb') fileobject.write(data[name]) fileobject.close( ) cursor.execute(sql, (name,)) import os os.remove(tempname)
This is clearly too much of a hassle (particularly considering the
many conditions you must meet, as well as the code bloat) for BLOBs
of small to medium sizes, but it may be worthwhile if your BLOBs are
quite large. Most often, however, LOAD_FILE
comes
in handy only if you already have the BLOB data in a file, or if you
want to put the data into a file anyway for another reason.
Recipe 8.8 for a PostgreSQL-oriented solution to the same problem; the MySQL home page (http://www.mysql.org); the Python/MySQL interface module (http://sourceforge.net/projects/mysql-python).