Using cursor.execute arguments in pymssql with IN sql statement

Question

I have troubles using a simple sql statement with the operator IN through pymssql.

Here is a sample :

import pymssql
conn = pymssql.connect(server='myserver', database='mydb')
cursor = conn.cursor()
req = "SELECT * FROM t1 where id in (%s)"
cursor.execute(req,  tuple(range(1,10)))
res = cursor.fetchall()

Surprisingly only the first id is returned and I can't figure out why. Does anyone encounter the same behavior ?

Answer 1

You're trying to pass nine ID values to the query and you only have one placeholder. You can get nine placeholders by doing this:

ids = range(1,10)
placeholders = ','.join('%s' for i in ids)
req = "SELECT * FROM t1 where id in ({})".format(placeholders)
cursor.execute(req, ids)
res = cursor.fetchall()

As an aside, you don't necessarily need a tuple here. A list will work fine.