Binary Lion Studios

I code for fun and for food.

Use parameter substitution with single value

Here is how to use Python’s DB-API parameter substitution with a single value.

1
2
3
4
5
6
7
8
9
10
11

import sqlite3

def main():
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    # blows up because of single quotes around question mark
    cursor.execute("update prefs set value = '?' where key = 'testing'", ('123',))
    # blows up because missing comma after single value tuple
    cursor.execute("update prefs set value = ? where key = 'testing'", ('123'))
    # this works
    cursor.execute("update prefs set value = ? where key = 'testing'", ('123',))

It’s subtle, but if you put single quotes around the question mark or forget the comma after the single value tuple, you’ll get a sqlite3.ProgrammingError exception.