Python Cookbook

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

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