Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 790
Electrons_Ahoy Avatar asked Oct 13 '08 19:10

Electrons_Ahoy


People also ask

Can you pickle a dictionary Python?

We can use the method pickle. dump() to serialise the dictionary and write it into a file. Then, we can read the file and load it back to a variable. After that, we have the exact dictionary back.

Can SQLite store Python objects?

As others have mentioned, the answer is yes... but the object needs to be serialized first. I'm the author of a package called klepto that is built to seamlessly store python objects in SQL databases, HDF archives, and other types of key-value stores.


2 Answers

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.

like image 116
Benoît Vidis Avatar answered Sep 21 '22 03:09

Benoît Vidis


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.

like image 29
SpoonMeiser Avatar answered Sep 17 '22 03:09

SpoonMeiser