Sqlite db, multiple row updates: handling text and floats

Question

Hi,

I have a large SQLite database with a mix of text and lots of other columns var1 ... var 50. Most of these are numeric, though some are text based.

I am trying to extract data from the database, process it in python and write it back - I need to do this for all rows in the db.

So far, the below sort of works:

# get row using select and process
fields = (','.join(keys)) # "var1, var2, var3 ... var50"
results = ','.join([results[key] for key in keys]) # "value for var1, ... value for var50"
cur.execute('INSERT OR REPLACE INTO results (id, %s) VALUES (%s, %s);' %(fields, id, results))

This however, nulls the columns that I don't explicitly add back. I can fix this by re-writing the code, but this feels quite messy, as I would have to surround with quotes using string concatenation and rewrite data that was there to begin with (i.e. the columns I didn't change).

Apparently the way to run updates on rows is something like this:

update table set var1 = 4, var2 = 5, var3="some text" where id = 666;

Presumably the way for me would be to run map , and add the = signs somehow (not sure how), but how would I quote all of the results appropriately (Since I would have to quote the text fields, and they might contain quotes within them too .. )?

I'm a bit confused. Any pointers would be very helpful.

Thanks!

Answer 1

You don't have to care about things like quotations etc, and in fact you shouldn't. If you do it like this, it's not only more convenient but also takes care of security issues known as sql injections:

sql = "update table set var1=%s, var2=%s, var3=%s where id=666" 
cursor.execute(sql, (4, 5, "some text"))

the key point here ist that the sql and the values in the second statement aren't separated by a "%", but by a "," - this is not a string manipulation, but instead you pass two arguments to the execute function, the actual sql and the values. Each %s is replaced by a value from the value tuple. the database driver then knows how to take care of the individual types of the values.

the insert statement can be rewritten the same way, although I'm not sure and currently can't test whether you can also replace field names that way (the first %s in your insert-sql statement)

so to come back to your overall problem, you can loop over your values and dynamically add ", var%d=%%s" % i for your i-th variable while adding the actual value to a list at the same time

Answer 2

Use parameter substitution. It's more robust (and safer I think) than string formatting.

So if you did something like

query = 'UPDATE TABLE SET ' + ', '.join(str(f) + '=?,' for f in fields) + ';'

Or alternatively

query = 'UPDATE TABLE SET %s;' % (', '.join(str(f) + '=?,' for f in fields))

Or using new style formatting:

query = 'UPDATE TABLE SET {0};'.format(', '.join(str(f) + '=?,' for f in fields))

So the complete program would look something like this:

vals = {'var1': 'foo', 'var2': 3, 'var24':999}
fields = vals.keys()
results = vals.values()
query = 'UPDATE TABLE SET {0};'.format(', '.join(str(f) + '=?,' for f in fields))
conn.execute(query, results)

And that should work - and I presume do what you want it to.

Answer 3

As others have stressed, use parametrized arguments. Here is an example of how you might construct the SQL statement when it has a variable number of keys:

sql=('UPDATE results SET '
     + ', '.join(key+' = ?' for key in keys)
     + 'WHERE id = ?')
args = [results[key] for key in keys] + [id]
cur.execute(sql,args)