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
    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.
    insert into frames values (null, ?)''',
    (sqlite.Binary('\0' * 10 + '\x12'),))
    insert into frames values (null, ?)''',

# 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')



comments powered by Disqus