Dictionary inserts into MySQL (each key in its own field)

Fredrik Lundh fredrik at pythonware.com
Fri Jan 27 11:07:10 CET 2006

Derick van Niekerk wrote:

> I have found many posts that deal with writing a dictionary to MySQL in
> a blob field - which I can't imagine why anybody would want to do it.

it might be useful if you have a bunch of unknown properties (e.g. configuration
parameters for some external parameters), and no need to access the properties
as individual parameters via the database.

> I want to write each element of a dictionary onto a db table. The keys
> would match the fieldnames. Is there something that would make this job
> easier? i.e. how do I write an entire dictionary into a db table as a
> row? I have an idea that it is something very simple - possibly even
> one line of code...

some database drivers have API:s for this, but if you're using a generic
DB-API driver, it's pretty straightforward to generate an appropriate SQL
statement on the fly; e.g.

d = {"spam": "1", "egg": "2"}

cols = d.keys()
vals = d.values()

stmt = "INSERT INTO table (%s) VALUES(%s)" % (
    ",".join(cols), ",".join(["?"]*len(vals))

cursor.execute(stmt, tuple(vals))


More information about the Python-list mailing list