sqlite - Can I pickle a python dictionary into a sqlite3 text field?

Question

Any gotchas I should be aware of? Can I store it in a text field, or do I need to use a blob? (I'm not overly familiar with either pickle or sqlite, so I wanted to make sure I'm barking up the right tree with some of my high-level design ideas.)

Answer 1

I needed to achieve the same thing too.

I turns out it caused me quite a headache before I finally figured out, thanks to this post, how to actually make it work in a binary format.

To insert/update:

pdata = cPickle.dumps(data, cPickle.HIGHEST_PROTOCOL)
curr.execute("insert into table (data) values (:data)", sqlite3.Binary(pdata))

You must specify the second argument to dumps to force a binary pickling.
Also note the sqlite3.Binary to make it fit in the BLOB field.

To retrieve data:

curr.execute("select data from table limit 1")
for row in curr:
  data = cPickle.loads(str(row['data']))

When retrieving a BLOB field, sqlite3 gets a 'buffer' python type, that needs to be strinyfied using str before being passed to the loads method.