Python, store a dict in a database

Question

What's the best way to store and retrieve a python dict in a database?

Answer 1

Fairly vague question, depends a lot on the use cases.

Typically you'd just serialize it and insert it wherever it was needed, but if you need the dict itself to be "database-like", then you can use one of many of the available key/value stores for Python

Answer 2

If the dict directly corresponds to a database table, then you should set up a table with a schema containing two columns - key and value. Then for each element of the dict, just insert it into the database, or update it if the key is already in place, etc.

Otherwise you could use pickle to serialize the dict to a string, and then you could just save the string to the DB. But in general I would not recommend this solution since you cannot query the serialized data.

Answer 3

If you are not specifically interested into using a traditionally SQL database, such as MySQL, you could look into unstructured document databases where documents naturally map to python dictionaries, for example MongoDB. The MongoDB python bindings allow you to just insert dicts in the DB, and query them based on the values in the dict. See for example the code below from the tutorial:

>>> from pymongo import Connection
>>> connection = Connection()
>>> db = connection['test-database']
>>> import datetime
>>> post = {"author": "Mike",
...         "text": "My first blog post!",
...         "tags": ["mongodb", "python", "pymongo"],
...         "date": datetime.datetime.utcnow()}
>>> posts = db.posts
>>> posts.insert(post)
ObjectId('...')
>>> posts.find_one({"author": "Mike"})
{u'date': datetime.datetime(...), u'text': u'My first blog post!', u'_id': ObjectId('...'), u'author': u'Mike', u'tags': [u'mongodb', u'python', u'pymongo']}

Answer 4

"Best" is debatable.
If you need a DBMS, sqlite is built in; so it might be considered an easier method than some of the other ways mentioned.

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("create table kv (key text, value integer);")
# <sqlite3.Cursor object at 0x00C62CE0>
d = {'a':1,'b':2}
c.executemany("insert into kv values (?,?);", d.iteritems())
# <sqlite3.Cursor object at 0x00C62CE0>
c.execute("select * from kv;").fetchall()
# [(u'a', 1), (u'b', 2)]