لطفا منتظر باشید ...
Recipe 7.12. Storing a BLOB in a SQLite Database
Credit: John Barham
Problem
You need to
store a BLOB in an SQLite database.
Solution
The PySQLite Python extension offers function
sqlite.encode to let you insert binary strings in
SQLite databases. You can also build a small adapter class based on
that function:
import sqlite, cPickle
class Blob(object):
''' automatic converter for binary strings '''
def _ _init_ _(self, s): self.s = s
def _quote(self): return "'%s'" % sqlite.encode(self.s)
# make a test database in memory, get a cursor on it, and make a table
connection = sqlite.connect(':memory:')
cursor = connection.cursor( )
cursor.execute("CREATE TABLE justatest (name TEXT, ablob BLOB)")
# 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, Blob(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])
# Done, close the connection (would be no big deal if you didn't, but...)
connection.close( )
Discussion
SQLite does not directly support binary data, but it still lets you
declare such types for fields in a CREATE TABLE
DDL statement. The PySQLite Python extension uses the declared types
of fields to convert field values appropriately to Python values when
you fetch data after an SQL SELECT from an SQLite
database. However, you still need to be careful when communicating
binary string data via SQL.Specifically, when you use INSERT or
UPDATE SQL statements, and need to have binary
strings among the VALUES you're
passing, you need to escape some characters in the binary string
according to SQLite's own rules. Fortunately, you
don't have to figure out those rules for yourself:
SQLite supplies the function to do the needed escaping, and PySQLite
exposes that function to your Python programs as the
sqlite.encode 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 database in memory, so that the database is implicitly lost at the
end of the script.While you could perfectly well call sqlite.encode
directly on your binary strings at the time you pass them as
parameters to a cursor's execute
method, this recipe takes a slightly different tack, defining a
Blob class to wrap binary strings and passing
instances of that. When PySQLite receives as arguments instances of
any class, the class must define a method named
_quote, and PySQLite calls that method on each
instance, expecting the method to return a string fully ready for
insertion into an SQL statement. When you use this approach for more
complicated classes of your own, you'll probably
want to pass a decoders keyword argument to the
connect method, to associate appropriate decoding
functions to specific SQL types. By default, however, the
BLOB SQL type is associated with the decoding
function sqlite.decode, which is exactly the
inverse of sqlite.encode; for the simple
Blob class in this recipe, therefore, we do not need
to specify any custom decoder, since the default one suits us
perfectly well.
See Also
Recipe 7.10 and Recipe 7.11 for MySQL-oriented and
PostgreSQL-oriented solutions to the same problem;
SQLite's home page (http://www.sqlite.org/); the PySQLite manual
(http://pysqlite.sourceforge.net/manuall);
the SQLite FAQ ("Does SQLite support a BLOB
type?") at http://www.hwaci.com/sw/sqlite/faql#q12.
