How to use placeholders for the column and table names in sqlite3 statements, when '?' does not work?

Question

For some reason I am getting errors when using placeholders in select statements.

def get_id(table_name, id_name):
    db = sqlite3.connect('test_db')
    max_id = db.execute('''SELECT max(?) FROM ?''', (id_name, table_name)).fetchone()[0]
    if not max_id:
        primary_id = 0
    else:
        primary_id = max_id + 1

This functions returns this error:

File "test.py", line 77, in get_id
max_id = db.execute('''SELECT max(?) FROM ?''', (id_name, table_name)).fetchone()[0]
sqlite3.OperationalError: near "?": syntax error

Answer 1

You aren't able to use placeholders for column or table names. The placeholders are for values used to insert or retrieve data from the database. The library properly sanitizes them.

To do what you want, try something like this:

db.execute('''SELECT max({}) FROM {}'''.format(id_name, table_name)).fetchone()[0]

This will use string formatting to build your query. If you need to add a WHERE condition to this, you can still do that using parameters:

db.execute('''SELECT max({}) FROM {} WHERE ID = ?'''.format(id_name, table_name), id_variable).fetchone()[0]

Answer 2

You're seeing this error because placeholders can only be used to substitute values, not column or table names.

In this case, you will have to use Python's string formatting, being very careful that the values don't contain SQL or special characters:

max_id = db.execute(
    'SELECT max(%s) FROM %s where foo > ?' %(id_name, table_name),
    (max_foo_value, ),
)