Can I pickle a python dictionary into a sqlite3 te

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

If a dictionary can be pickled, it can be stored in text/blob field as well.

Just be aware of the dictionaries that can't be pickled (aka that contain unpickable objects).

Answer 2

Yes, you can store a pickled object in a TEXT or BLOB field in an SQLite3 database, as others have explained.

Just be aware that some object cannot be pickled. The built-in container types can (dict, set, list, tuple, etc.). But some objects, such as file handles, refer to state that is external to their own data structures, and other extension types have similar problems.

Since a dictionary can contain arbitrary nested data structures, it might not be pickle-able.

Answer 3

Depending on what you're working on, you might want to look into the shove module. It does something similar, where it auto-stores Python objects inside a sqlite database (and all sorts of other options) and pretends to be a dictionary (just like the shelve module).

Answer 4

Since Pickle can dump your object graph to a string it should be possible.

Be aware though that TEXT fields in SQLite uses database encoding so you might need to convert it to a simple string before you un-pickle.

Answer 5

I have to agree with some of the comments here. Be careful and make sure you really want to save pickle data in a db, there's probably a better way.

In any case I had trouble in the past trying to save binary data in the sqlite db. Apparently you have to use the sqlite3.Binary() to prep the data for sqlite.

Here's some sample code:

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

Answer 6

See this solution at SourceForge:

y_serial.py module :: warehouse Python objects with SQLite

"Serialization + persistance :: in a few lines of code, compress and annotate Python objects into SQLite; then later retrieve them chronologically by keywords without any SQL. Most useful "standard" module for a database to store schema-less data."

http://yserial.sourceforge.net