Inserting records into Sqlite using Python parameter substitution where some fields are blank

Question

I am running this sort of query:

insert into mytable (id, col1, col2)
values (:ID, :COL1, :COL2)

In Python, a dictionary of this form can be used in conjuction with the query above for parameter substitution:

d = { 'ID' : 0, 'COL1' : 'hi', 'COL2' : 'there' }
cursor.execute(sql_insert, d)

But in the real problem, there are lots of columns and lots of rows. Sometimes the data source that populates the dictionary doesn't have an entry. However, if the dictionary is short, Sqlite will complain that an incorrect number of bindings was supplied, rather than giving me a way to add empty strings or NULLs in the columns which are not populated in this case.

I'm being a lazy, or a bit perfectionist. I could write some code to add any missing fields to the dictionary. I'm just looking for an elegant solution that doesn't require triplicating the list of fields.

I tried overloading the dictionary with a modified dictionary that returns an empty string if the field is missing.

Answer 1

I haven't checked that this works, but I think it should:

from collections import defaultdict
d = { 'ID' : 0, 'COL1' : 'hi' }
cursor.execute(sql_insert, defaultdict(str, d))

defaultdict is a specialised dictionary where any missing keys generate a new value instead of throwing a KeyError.

Of course this only works if all the values need the same default such as an empty string or None. If you need different defaults then you'll need a dictionary containing the defaults and you can do:

DEFAULTS = { ... whatever ... }
d = { 'ID' : 0, 'COL1' : 'hi' }
cursor.execute(sql_insert, dict(DEFAULTS).update(d))

Note that you must copy DEFAULTS each time so you can update the copy with the actual values.

Answer 2

To be honest, I'm not entirely sure what you're asking. It seems you're saying that you want to build an insert statement for a table row where you have some but not all of the field values ready.

You could build your query like this (taking advantage of dict.get returning None for missing keys):

>>> columns = ['id','col1','col2','col_missing']
>>> myData = {'id': 0, 'col1': 'hi', 'col2':'there'}
>>> myQuery = 'insert into mytable (%s) values (%s)' % (",".join(columns),",".join(['%s']*len(columns)))
>>> myArgs = [ myData.get(x) for x in columns ]
>>> myQuery
'insert into mytable (id,col1,col2,col_missing) values (%s,%s,%s,%s)'
>>> myArgs
[0, 'hi', 'there', None]
>>> cursor.execute(myQuery,myArgs)

Answer 3

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses

quoted from here.

so use CREATE like this:

CREATE mytable {
    id INTEGER PRIMARY KEY,
    col1 TEXT DEFAULT 'Hello, World!',
    col2 TEXT DEFAULT 'The cake in a lie'
}