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)" % placeholdersparams = [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:
String substitution (as you did).
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.
To expand on Larry and geographika's answers:
ratings = ('PG-13', 'PG', 'G')st_dt = datetime(2010, 1, 1)end_dt = datetime(2010, 12, 31)placeholders = ', '.join('?' * len(ratings))vars = (*ratings, st_dt, end_dt)query = ''' select title, director, producer from movies where rating in (%s) and release_dt between ? and ?''' % placeholderscursor.execute(query, vars)
With the placeholder, this will return a query of:
select title, director, producer from movies where rating in (?, ?, ?) and release_dt between ? and ?
If you pass in ratings
, it'll attempt to fit all of its items into one ?
. However, if we pass in *ratings
, and each item in ratings
will take its place in the in()
clause. Thus, we pass the tuple (*ratings, st_dt, end_dt)
to cursor.execute()
.