CODEWITHSUNDEEP
pythonsqlite
user2327814
user2327814

Reputation: 543

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

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

Upvotes: 11

Views: 18389

Answers (2)

Andy
Andy

Reputation: 50600

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]

Upvotes: 18

David Wolever
David Wolever

Reputation: 154594

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, ),
)

Upvotes: 6

Related Questions