Using a WHERE ___ IN ___ statement

I’m trying to figure out how to properly use a WHERE _ IN _ statement

Definition:

c.execute('''CREATE TABLE IF NOT EXISTS tab (
    _id integer PRIMARY KEY AUTOINCREMENT,
    obj text NOT NULL
    ) ;''')

I’m trying to do something like this:

list_of_vars=['foo','bar']
statement="SELECT * FROM tab WHERE obj IN (?)"
c.execute(statement,"'"+"','".join(list_of_vars)+"'")

Alternatively, I’ve also tried this, which directly evaluates to the above

statement="SELECT * FROM tab WHERE obj IN (?)"
c.execute(statement,"'foo','bar'")

The error I am getting is:

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

This is giving me an error. When I do it this way, it works, but this is not recommended as it is vulnerable to a SQL injection attack.

statement="SELECT * FROM tab WHERE obj IN ("+"'"+"','".join(statement)+"'"+")

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You need to create enough parameters to match your list of vars:

statement = "SELECT * FROM tab WHERE obj IN ({0})".format(', '.join(['?'] * len(list_of_vars)))
c.execute(statement, list_of_vars)

Note that you pass in list_of_vars as the parameter values list. Using the ', '.join() we generate a string of ? characters separated by commas, then use .format() to insert that into the statement.

For a long list of variables, it may be more efficient to use a temporary table to hold those values, then use a JOIN against the temporary table rather than an IN clause with bind parameters.

Method 2

FYI, pymysql with MySQL user.

query ="SELECT * FROM tab WHERE obj IN %s"
cursor.execute(query, (['foo','bar'],))

same as

cursor.execute(query, (list_of_var,))

I’m not sure about sqlite3,
this may work,

query ="SELECT * FROM tab WHERE obj IN ?"
cursor.execute(query, (['foo','bar'],)) 
or
cursor.execute(query, (list_of_vars,))

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0