Python SQLite Query Parameter Substitution

28 Dec 2015

Contructing queries on-the-fly is a common task in database applications. Some parts of a query just cannot be known in advance and have to calculated at run-time. That means your SQL queries/operations will need to use values from Python variables. One example would be parameter substitution.

Parameter substitution in Python is done by using ? as a placeholder in a query whenever you want to use a value, then provide a tuple of values as the second argument to the cursor’s execute() method. This approach is suggested in the Python sqlite3 documentation to avoid SQL injection which can occur more easily if using plain python string substitution (by using % operator). Here is an example from the python documentation:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

If you don’t know how many ? marks to put into a query in advance, what do you do? One approach is to put all the data you want the query to bind with into a sequence data structure and construct the query dynamically; here is an example of my own code:

ids_to_be_deleted = []

# collect primary keys to be deleted
for row in con.execute("select id, location \
                          from inputs \
                         where fresh = 0;"):
    key, path = row['id'], row['location']
    if not os.path.exists(path):
        ids_to_be_deleted.append(key)

# delete obsolete records in all tables
con.execute("delete from inputs where id in (%s)" % \
            ','.join('?'*len(ids_to_be_deleted)),
            ids_to_be_deleted)