Paul Calnan
Published August 12, 2012

In Python, I needed a way to insert a list of dictionaries into a SQLite database. The sqlite3 package's Cursor class provides an executemany method that takes a parameterized SQL statement and a sequence of parameters.

An example of this, taken from the Python documentation:

import sqlite3

person_tuples = [
    ('Hugo', 'Boss'),
    ('Calvin', 'Klein')
]

con = sqlite3.connect(':memory:')

# Create the table
con.execute('create table person(firstname, lastname)')

# Fill the table
con.executemany('insert into person(firstname, lastname) values (?, ?)',
                person_tuples)

Now, that works fine for the case when you have a list of tuples already. But what can be done when you have a list of dictionaries? You could call dict.values(), but the order of the values can't be guaranteed and they likely won't match the order required by the SQL statement.

>>> person_dicts = [
...     { 'firstname': 'Hugo', 'lastname': 'Boss' },
...     { 'firstname': 'Calvin', 'lastname': 'Klein' },
... ]
...
>>> for d in person_dicts:
...     print d.values()
...
['Boss', 'Hugo']
['Klein', 'Calvin']

To solve this, I wrote generate_tuples(). It takes a list of dictionaries and a list of keys. It generates a sequence of tuples, one for each dictionary in the list. The elements of the tuple are determined by the values for the specified keys.

Here's the function:

def generate_tuples(list_of_dicts, keys):
    """Generates tuples, one for each dict in the specified list, with
    elements corresponding to the values for the specified keys.

    """
    from operator import itemgetter
    getter = itemgetter(*keys)
    return (getter(d) for d in list_of_dicts)

And, here it is in action:

>>> for t in generate_tuples(person_dicts, ['firstname', 'lastname']):
...     print t
...
('Hugo', 'Boss')
('Calvin', 'Klein')

Back to the original example, this would allow me to insert the list of dictionaries as follows:

con.executemany('insert into person(firstname, lastname) values (?, ?)',
                generate_tuples(person_dicts, ['firstname', 'lastname']))