SQL IN operator using pyodbc and SQL Server

To expand on Larry's second option - dynamically creating a parameterized string, I used the following successfully:

placeholders = ",".join("?" * len(code_list))
sql = "delete from dbo.Results where RESULT_ID = ? AND CODE IN (%s)" % placeholders
params = [result_id]
params.extend(code_list)
cursor.execute(sql, params)

Gives the following SQL with the appropriate parameters:

delete from dbo.Results where RESULT_ID = ? AND CODE IN (?,?,?)

You cannot parameterize multiple values in an IN () clause using a single string parameter. The only way to accomplish that is:

  1. String substitution (as you did).

  2. Build a parameterized query in the form IN (?, ?, . . ., ?) and then pass in a separate parameter for each place holder. I'm not an expert at Python to ODBC but I imagine that this is particularly easy to do in a language like Python. This is safer because you get the full value of parameterization.

Tags:

Python

Sql

Pyodbc