python sqlite insert named parameters or null

Question

I'm trying to insert data from a dictionary into a database using named parameters. I have this working with a simple SQL statement e.g.

SQL = "INSERT INTO status (location, arrival, departure) VALUES (:location, :arrival,:departure)"
dict = {'location': 'somewhere', 'arrival': '1000', 'departure': '1001'}
c.execute(SQL,dict)

Inserts somewhere into location, 1000 into the arrival column, and 1001 into departure column.

The data that I will actually have will contain location but may contain either arrival, or departure but might not have both (in which case either nothing or NULL can go into the table). In this case, I get sqlite3.ProgrammingError: You did not supply a value for binding 2.

I can fix this by using defaultdict:

c.execute(SQL,defaultdict(str,dict))

To make things slightly more complicated, I will actually have a list of dictionaries containing multiple locations with either an arrival or departure.

    ({'location': 'place1', 'departure': '1000'},
    {'location': 'palce2', 'arrival': '1010'},
    {'location': 'place2', 'departure': '1001'})

and I want to be able to run this with c.executemany however I now can't use defaultdict.

I could loop through each dictionary in the list and run many c.execute statements, but executemany seems a tidier way to do it.

I've simplified this example for convenience, the actual data has many more entries in the dictionary, and I build it from a JSON text file.

Anyone have any suggestions for how I could do this?

Answer 1

Use None to insert a NULL:

dict = {'location': 'somewhere', 'arrival': '1000', 'departure': None}

You can use a default dictionary and a generator to use this with executemany():

defaults = {'location': '', 'arrival': None, 'departure': None}

c.executemany(SQL, ({k: d.get(k, defaults[k]) for k in defaults} for d in your_list_of_dictionaries)

Answer 2

There is a simpler solution to this problem that should be feasible in most cases; just pass to executemany a list of defaultdict instead of a list of dict.

In other words, if you build from scratch your rows as defaultdict you can pass the list of defaultdict rows directly to the command executemany, instead of building them as dictionaries and later patch the situation before using executemany.

The following working example (Python 3.4.3) shows the point:

import sqlite3
from collections import defaultdict
# initialization
db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute("CREATE TABLE status(location TEXT, arrival TEXT, departure TEXT)")
SQL = "INSERT INTO status VALUES (:location, :arrival, :departure)"
# build each row as a defaultdict
f = lambda:None # use str if you prefer
row1 = defaultdict(f,{'location':'place1', 'departure':'1000'})
row2 = defaultdict(f,{'location':'place2', 'arrival':'1010'})
rows = (row1, row2)
# insert rows, executemany can be safely used without additional code
c.executemany(SQL, rows)
db.commit()
# print result
c.execute("SELECT * FROM status")
print(list(zip(*c.description))[0])
for r in c.fetchall():
    print(r)
db.close()

If you run it, it prints:

('location', 'arrival', 'departure')
('place1', None, '1000') # None in Python maps to NULL in sqlite3
('place2', '1010', None)