4

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)+"'"+")
PearsonArtPhoto
  • 38,970
  • 17
  • 111
  • 142

2 Answers2

11

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • 1
    +1. But why not just `'?' * len(list_of_vars)` instead of the generator expression? – abarnert Jan 09 '13 at 20:17
  • @abarnet: a touch of flair? I planned to `timeit` to see if it had any other use other than a personal touch, but I got distracted for a moment. – Martijn Pieters Jan 09 '13 at 20:18
  • Well, this works for the simple test case, but not the more advanced one I was really doing... Sigh. Still, it's a step forward! – PearsonArtPhoto Jan 09 '13 at 20:24
  • 1
    @Martijn Pieters: We don't _require_ our employees to wear 37 pieces of flair. – abarnert Jan 09 '13 at 20:24
  • 1
    @abarnert: A quick `timeit` later and I switched it to `.join(['?' * len(list_of_vars)])` because using a list in `.join()` is faster than a generator. So much for flair! – Martijn Pieters Jan 09 '13 at 20:28
  • If the `list` were long enough that allocation had an impact, the opposite might be true. But if you've got a list of variables that long, sqlite is probably going to have much more serious problems than your `join` statement anyway… – abarnert Jan 09 '13 at 20:32
  • PS, I've never actually tested sqlite with huge numbers of columns. But I did once accidentally create a view with a 5-digit column count in MySQL, and it was not pretty. Oracle, by contrast, gave me a nice error message saying something like "Exceeded limits, contact Oracle contract services for further details". I guess you get what you pay for (if what you pay for is requests to pay more money). – abarnert Jan 09 '13 at 20:33
  • @abarnert: It's also `.join()`; it needs to build two lists when given a generator, apparently. It's just faster to give it a list in the first place. – Martijn Pieters Jan 09 '13 at 20:33
  • @abarnert: And I am *extremely* well aware as to what Oracle requests for such services. :-) – Martijn Pieters Jan 09 '13 at 20:34
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,))
rumbarum
  • 803
  • 7
  • 7