لطفا منتظر باشید ...
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]))
See Also
Recipe 7.11 and Recipe 7.12 for
PostgreSQL-oriented and SQLite-oriented solutions to the same
problem; the MySQL home page (http://www.mysql.org); the Python/MySQL
interface module (http://sourceforge.net/projects/mysql-python).
