? not being seen in SQLite query via Python

Question

I am currently using Python 2.7's SQLite 3 database API. When I go to execute a query like such:

c.execute('''select ? from music where ? like "%?%"''', (attr, attr, query))

I get the following error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied.

I think that the %?% is what's throwing it off.

Larry Lustig · Accepted Answer · 2011-12-07 03:00:30Z

3

You misunderstand the use of parameter substitution. You can use ? in place a whole, single variable value in the SQL statement. You cannot use it in place of column or table names, nor can you use it as part of a larger string value.

In this case you could do this:

c.execute('SELECT colname FROM music WHERE other_colname LIKE ?', ('%' + query + '%'))

If you want to sub in the column names you must do it using string formatting. If you do so and the values came from user input, you need to be careful to avoid SQL injection. You're protected for the substituted parameters, but not the string formatting.

answered Dec 7, 2011 at 3:00

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Larry Lustig Over a year ago

No problem. It's a bummer to not be able to substitute the database entity names, but the engine would not be able to prepare and pre-compile the query without that information available.

Collectives™ on Stack Overflow

? not being seen in SQLite query via Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related