Recipe7.10.Storing a BLOB in a MySQL Database

Recipe 7.10. Storing a BLOB in a MySQL Database

Credit: Luther Blissett

Problem

You need to store a binary large object (BLOB) in a MySQL database.

Solution

The MySQLdb module does not support full-fledged placeholders, but you can make do with the module's 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, 2)     # 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( )

Discussion

MySQL supports binary data (BLOBs and variations thereof), but you should 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 have 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 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, 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 should terminate because of an uncaught exception). With recent versions of MySQL and MySQLdb, you don't even need to call the escape_string function anymore, so you can change the relevant statement to the simpler:

cursor.execute(sql, (name, data[name]))

Recipe7.10.Storing a BLOB in a MySQL Database