Recipe7.12.Storing a BLOB in a SQLite Database

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.

Recipe7.12.Storing a BLOB in a SQLite Database