sqlite3 "IN" clause

Question

I have a table with different columns (pageNum, value1, value2, value3).

I want to do something like that in python3.3 and sqlite3:

selection = (100, 200, 300)

cursor.execute(""" SELECT value1, value3
                  FROM myTable
                  WHERE value2>? and pageNum IN ?
                  """, (10, selection))

But seems like it is complaining:

sqlite3.OperationalError: near "?": syntax error

Note that the question is about how to use the IN clause that way, not about how to do the query, which of course in that case it could be done in a different way.

You eiter have to use more than one `?` in your `IN` clause or convert yout tuple into string first. — konart, Jul 17 '15 at 10:41
@konart you should never, ever convert your tuple into a string.... also http://stackoverflow.com/questions/1309989/parameter-substitution-for-a-sqlite-in-clause not super nice, but does get around the problem — user3012759, Jul 17 '15 at 10:57
yes I am also curious to know, and the answer in the other post does not seem to clear at least for me. — codeKiller, Jul 17 '15 at 12:39
Possible duplicate of [Parameter substitution for a SQLite "IN" clause](https://stackoverflow.com/questions/1309989/parameter-substitution-for-a-sqlite-in-clause) — rcoup, Jun 28 '19 at 11:18

score 4 · Accepted Answer · answered Jul 17 '15 at 13:54

So the answer from Alex Martelli is essentially solving your problem since python support for the IN is quite unwieldy, and in essence you have to provide as many ? as you have values in your collection.

selection = (100, 200, 300)
questionmarks = '?' * len(selection)
formatted_query = 'SELECT value1, value3 FROM myTable
              WHERE value2>? AND pageNum IN ({})'.format(','.join(questionmarks))
query_args = [10]
query_args.extend(selection)
result_set = c.execute(formatted_query, query_args)

the above code should do, and heres bit of explanation on what it does:

It essentially prepares the sql statement with as many ? as you need and then executes the query with all your arguments supplied in a list so sqlite will take care of escaping the values.

If you convert your query to a string you will have to make sure that you sanitize the values yourself which is not recommended.

sqlite3 "IN" clause

1 Answers1

Linked