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.

What's wrong with None? Why aren't you using that for these "missing" fields? — S.Lott
– S.Lott, Commented Jun 30, 2010 at 10:28
The issue is that I need to populate the dict with each missing field, even if the value is None (or an empty string, or whatever), for Sqlite to be happy, and I'm too lazy to populate the keys in dict. e.g. I would need d = { 'COL_MISSING' : None }, etc. — jbarlow
– jbarlow, Commented Jun 30, 2010 at 10:30
Can't you create the dictionary with all the bindings and empty values, then fill it with the data from your source. Thus any binding value not present in your datasource will default to its empty value. I do not know what your source looks like, but assuming it's dictionary you could just for over the key-value pairs and set it on the final bindings. — Skurmedel
– Skurmedel, Commented Jun 30, 2010 at 10:34
@MattH: Hah. No, that doesn't very good in combination, does it? I was trying to steer people away from the obvious answer. Because there are 50 columns, keeping a duplicated list in order is really undesirable. — jbarlow
– jbarlow, Commented Jul 1, 2010 at 9:16

Duncan · Accepted Answer · 2010-06-30 10:43:48Z

7

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.

answered Jun 30, 2010 at 10:43

Duncan

96.4k15 gold badges129 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jbarlow Over a year ago

I was definitely looking for defaultdict by didn't know of it. Thanks for the pointer.

MattH · Accepted Answer · 2010-06-30 10:59:41Z

0

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)

answered Jun 30, 2010 at 10:59

MattH

38.4k11 gold badges85 silver badges84 bronze badges

3 Comments

jbarlow Over a year ago

That solves one problem (duplicating the column name) in exchange for a worse problem -- opening the door for Sqlite injection attacks via the insert query. xkcd.com/327

MattH Over a year ago

The parameter substitution in cursor.execute is supposed to be safe. Though apparently it should be '?' instead of '%s' for sqlite3. Where is the injection vector? The column names? They are defined on the schema.

MattH Over a year ago

@jbarlow: Mind telling me what you think the "open door to injection attacks" is in this answer?

Daniel Hill · Accepted Answer · 2010-06-30 11:25:21Z

0

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'
}

answered Jun 30, 2010 at 11:25

Daniel Hill

3102 silver badges14 bronze badges

1 Comment

jbarlow Over a year ago

That doesn't solve it. The issue is that Python's Sqlite library requires all of the bindings too be set for the insert query to be valid -- this is a real a Python problem.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related