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

I wrote a blog about this idea, except instead of a pickle, I used json, since I wanted it to be interoperable with perl and other programs.

http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/

Architecturally, this is a quick and dirty way to get persistence, transactions, and the like for arbitrary data structures. I have found this combination to be really useful when I want persistence, and don't need to do much in the sql layer with the data (or it's very complex to deal with in sql, and simple with generators).

The code itself is pretty simple:

#  register the "loader" to get the data back out.
sqlite3.register_converter("pickle", cPickle.loads) 

Then, when you want to dump it into the db,

p_string = p.dumps( dict(a=1,b=[1,2,3]))  
conn.execute(''' 
   create table snapshot( 
      id INTEGER PRIMARY KEY AUTOINCREMENT, 
        mydata pickle); 
''')  

conn.execute(''' 
    insert into snapshot values 
    (null, ?)''', (p_string,))
''')

If you want to store a pickled object, you'll need to use a blob, since it is binary data. However, you can, say, base64 encode the pickled object to get a string that can be stored in a text field.

Generally, though, doing this sort of thing is indicative of bad design, since you're storing opaque data you lose the ability to use SQL to do any useful manipulation on that data. Although without knowing what you're actually doing, I can't really make a moral call on it.

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.