python sqlite insert named parameters or null

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)

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 sqlite3from collections import defaultdict# initializationdb = 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 defaultdictf = lambda:None # use str if you preferrow1 = 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 codec.executemany(SQL, rows)db.commit()# print resultc.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)