Storing BLOBs in a SQLite DB with Python/pysqlite

May 29th, 2009 at 9:06 am

I must be looking in all the wrong places, but I haven’t found many usable examples online of storing BLOBs in a SQLite database using Python. At least, not something that works with current versions of Python (2.5), pysqlite (2.3.2) and SQLite (the version that comes installed with Python 2.5). Overall, there doesn’t seem to be much good examples of pysqlite anywhere.

So here’s an example I cooked up after consulting with several sources and experimenting a little:

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

Related posts:

  1. Compiling SQLite on Windows
  2. Zipped dump of a SQLite database with Python
  3. SICP section 3.3.3
  4. Python objects, types, classes, and instances – a glossary
  5. Frames and protocols for the serial port – in Python

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

  1. Alexander ArtemenkoNo Gravatar Says:

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

  2. elibenNo Gravatar Says:

    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. DougNo Gravatar Says:

    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. elibenNo Gravatar Says:

    @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. IainNo Gravatar Says:

    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. IainNo Gravatar Says:

    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. alxxNo Gravatar Says:

    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. elibenNo Gravatar Says:

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

  9. code43No Gravatar Says:

    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. rogerNo Gravatar Says:

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

Leave a Reply

To post code with preserved formatting, enclose it in `backticks` (even multiple lines)