CODEWITHSUNDEEP
pythonsqlsqlite
PearsonArtPhoto
PearsonArtPhoto

Reputation: 39698

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)+"'"+")

Upvotes: 4

Views: 1964

Answers (2)

rumbarum
rumbarum

Reputation: 983

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

Upvotes: 2

Martijn Pieters
Martijn Pieters

Reputation: 1123400

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.

Upvotes: 11

Related Questions