SQL IN statement using pyodbc and SQL Server

Question

I am using pyodbc to query SQL Server database

import datetime import pyodbc conn = pyodbc.connect("Driver={SQL Server};Server='dbserver',Database='db', TrustedConnection=Yes") cursor = conn.cursor() ratings = ("PG-13", "PG", "G") st_dt = datetime(2010, 1, 1) end_dt = datetime(2010, 12, 31) cursor.execute("""Select title, director, producer From movies Where rating In ? And release_dt Between ? And ?""", ratings, str(st_dt), str(end_dt)) 

but I get the error below. Does the tuple parameter need to be handled differently? Is there a better way to structure this query?

 ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 9: Incorrect syntax near '@P1'. (170) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s) could not be prepared. (8180)") 

UPDATE:

I managed to get this request to work using the string formatting operator, which is not ideal as it presents security issues.

 import datetime import pyodbc conn = pyodbc.connect("Driver={SQL Server};Server='dbserver',Database='db', TrustedConnection=Yes") cursor = conn.cursor() ratings = ("PG-13", "PG", "G") st_dt = datetime(2010, 1, 1) end_dt = datetime(2010, 12, 31) cursor.execute("""Select title, director, producer From movies Where rating In %s And release_dt Between '%s' And '%s'""" % (ratings, st_dt, end_dt)) 

Answer 1

You cannot parameterize multiple values ​​in an IN () clause using a single string parameter. The only way to achieve this:

• String substitution (like you).

• Create a parameterized query in the form IN (?, ?, . . ., ?) , And then pass a separate parameter for each place owner. I am not an expert in Python for ODBC, but I assume this is especially easy to do in Python. This is safer because you get the full parameterization value.

Answer 2

To extend the second Larry variant - dynamically creating a parameterized string, I successfully used the following:

 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 (?,?,?) 

Answer 3

The problem is your tuple. The ODBC connection expects the string to build the request and you send the python tuple. And remember that you need to get the correct string quote. I assume that the number of ratings you will be looking for varies. There is probably a better way, but my pyodbc tends to be simple and straightforward.

Try the following:

 import datetime import pyodbc conn = pyodbc.connect("Driver={SQL Server};Server='dbserver',Database='db', TrustedConnection=Yes") def List2SQLList(items): sqllist = "%s" % "\",\"".join(items) return sqllist cursor = conn.cursor() ratings = ("PG-13", "PG", "G") st_dt = datetime(2010, 1, 1) end_dt = datetime(2010, 12, 31) cursor.execute("""Select title, director, producer From movies Where rating In (?) And release_dt Between ? And ?""", List2SQLList(ratings), str(st_dt), str(end_dt)) 

Answer 4

More on Larry and the geography of 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 ? ''' % placeholders cursor.execute(query, vars) 

With a placeholder, this will return a query:

  select title, director, producer from movies where rating in (?, ?, ?) and release_dt between ? and ? 

If you go to ratings , will he try to fit all his subjects into one ? . However, if we pass *ratings , and each element in the ratings will take its place in the in() sentence. Thus, we pass the tuple (*ratings, st_dt, end_dt) to cursor.execute() .