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)