Python, Sqlite3 - How to convert a list to a BLOB cell

Question

What is the most elegant method for dumping a list in python into an sqlite3 DB as binary data (i.e., a BLOB cell)?

data = [ 0, 1, 2, 3, 4, 5 ]
# now write this to db as binary data
# 0000 0000
# 0000 0001
# ...
# 0000 0101

Answer 1

It seems that Brian's solution fits your needs, but keep in mind that with that method your just storing the data as a string.

If you want to store the raw binary data into the database (so it doesn't take up as much space), convert your data to a Binary sqlite object and then add it to your database.

query = u'''insert into testtable VALUES(?)'''
b = sqlite3.Binary(some_binarydata)
cur.execute(query,(b,))
con.commit()

(For some reason this doesn't seem to be documented in the python documentation)

Here are some notes on sqlite BLOB data restrictions:

http://effbot.org/zone/sqlite-blob.htm