import sqlite3 as sqlite

# Create a new in-memory DB and a cursor
#
con = sqlite.connect(':memory:')
cur = con.cursor()

# The table is named 'frames'
# The columns are: a running ID, and a data blob
#
cur.execute('''
    create table frames (
        id integer primary key,
        data blob)''')

# Shove some data into the table. The data stored
# using the sqlite.Binary type, which means a BLOB.
#
cur.execute('''
    insert into frames values (null, ?)''',
    (sqlite.Binary('\0' * 10 + '\x12'),))
cur.execute('''
    insert into frames values (null, ?)''',
    (sqlite.Binary('\x01\x42\x55'),))

# Now read it back. When BLOBs are read, they're
# converted to Python buffers of type 'buffer'
#
for row in cur.execute("select * from frames"):
    print row[0], str(row[1]).encode('hex')

cur.close()
con.close()

10 Responses to “Storing BLOBs in a SQLite DB with Python/pysqlite”

1. Alexander Artemenko Says:
   May 29th, 2009 at 11:53

   Why do you want to store blobs in the sqlite? Isn’t it better to use anydbm module from standart python library?

2. eliben Says:
   May 29th, 2009 at 15:35

   anydbm is too simplistic for my needs. I need to be able to fetch the rows in the order of their insertion (I use the ID column for that), and also to do indexing by some fields. BLOB is just part of the data.

3. Doug Says:
   May 30th, 2009 at 02:57

   Here’s one i modified from a recipe in an older version of Python Cookbook (i’ve used in 2.5 & 2.6)

   import os
     import sys
     import cPickle
    from sqlite3 import dbapi2 as sqlite
   
   class Blob(object):
       ''' automatic converter for binary strings '''
       def __init__(self, s): self.s = s
       def _quote(self): return "'%s'" % sqlite.encode(self.s)
   connection = sqlite.connect(':memory:')
   cursor = connection.cursor()
   cursor.execute("CREATE TABLE fullofblobs (itm TEXT, ablob BLOB)")
   itms = "one two three four five six seven eight nine".split()
   my_data = {}
   for itm in itms:
       datum = list(itm)
       datum.sort()
       my_data[itm] = cPickle.dumps(datum, 2)
   sql = 'INSERT INTO fullofblobs VALUES(%s, %s)'
   for itm in itms:
       cursor.execute(sql, (itm, Blob(my_data[itm])) )
   sql = 'SELECT name, ablob FROM fullofblobs ORDER BY itm'
   cursor.execute(sql)
   for itm, blob in cursor.fetchall():
       print itm, cPickle.loads(blob), cPickle.loads(my_data[itm])
   connection.close()

4. eliben Says:
   May 30th, 2009 at 06:37

   @Doug,
   This doesn’t work for me (the original Cookbook example didn’t either):

   Traceback (most recent call last):
   File “z.py”, line 21, in
   cursor.execute(sql, (itm, Blob(my_data[itm])) )
   sqlite3.OperationalError: near “%”: syntax error

5. Iain Says:
   June 1st, 2009 at 07:16

   Nice! But why include the triplequotes (”’)? This seems to work:

   import sqlite3 as sqlite
   
   # Create a new in-memory DB and a cursor
   con = sqlite.connect(':memory:')
   cur = con.cursor()
   
   # The table is named 'frames'
   # The columns are: a running ID, and a data blob
   cur.execute('create table frames(id integer primary key,data blob)')
   
   # Shove some data into the table. The data stored
   # using the sqlite.Binary type, which means a BLOB.
   cur.execute('insert into frames values (null, ?)',
       (sqlite.Binary('\0' * 10 + '\x12'),))
   cur.execute('insert into frames values (null, ?)',
       (sqlite.Binary('\x01\x42\x55'),))
   
   # Now read it back. When BLOBs are read, they're
   # converted to Python buffers of type 'buffer'
   for row in cur.execute("select * from frames"):
       print row[0], str(row[1]).encode('hex')
   
   cur.close()
   con.close()

6. Iain Says:
   June 1st, 2009 at 09:05

   In relation to Doug’s code, try this:

   import os
   import sys
   import cPickle
   import sqlite3 as sqlite
   
   # Create a new in-memory DB and a cursor
   con = sqlite.connect(':memory:')
   cur = con.cursor()
   
   # The table is named 'frames'
   # The columns are: a running ID, a data blob, the type (for decoding)
   cur.execute('create table frames(id integer primary key,data blob, type)')
   
   # Pickle an entry (of type string or hex) and add it to the database
   def dbadd(entry,type):
       mypickle = cPickle.dumps(entry, 1)
       cur.execute('insert into frames values (null, ?, ?)',
           (sqlite.Binary(mypickle),type))
   
   # Add some strings
   strlist = "one two three four five six seven eight nine".split()
   for itm in strlist: dbadd(itm,'string')
   
   # Add some hex data
   dbadd('\0' * 10 + '\x12','hex')
   dbadd('\x01\x42\x55','hex')
   
   # Now read the data back.
   for id,data,type in cur.execute("select id,data,type from frames"):
       if type == 'string':
           print id, 'string:', cPickle.loads(str(data))
       if type == 'hex':
           print id, 'hex:', cPickle.loads(str(data)).encode('hex')
   
   cur.close()
   con.close()

7. alxx Says:
   June 22nd, 2009 at 14:46

   What about for image storage in a db ?
   Use BLOBS ? How would you format them, just as hex string ?

   Generating plots with matplotlib and would like to put them straight into a db and also doing image capture.Both will have some associated xml data.

   Old application used cache/ensemble for db, want new version to be completely open source based and not tied to vendor tools.

   I realise sqlite isn’t a good long term solution but for getting things up and running quickly its perfect.

8. eliben Says:
   June 22nd, 2009 at 17:49

   @alexx,
   I don’t think there’s a problem with storing images as BLOBs in a DB.

9. code43 Says:
   December 3rd, 2009 at 07:54

   The Python Cookbook recipe is broken… but it’s been substantially updated in the y_serial module at http://yserial.sourceforge.net — which handles any arbitrary Python object as a compressed BLOB in SQLite. It has indexed primary keys which eliben requires, plus an automatic timestamp — and it supports regex on user notes which can be optionally append to the object. And yes, that object could be an image file or any binary/text file (see the infile method). The interface is so simple, one can start coding in less than 10 minutes.

10. roger Says:
    January 26th, 2012 at 12:55

    Thanks!! It’s exactly what i’m looking for.

Leave a Reply