8.7 Storing a BLOB in a MySQL Database

Credit: Luther Blissett

8.7.1 Problem

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

8.7.2 Solution

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(  )

8.7.3 Discussion

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.

8.7.4 See Also

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).