SQL IN operator using pyodbc and SQL Server

Question

I'm using pyodbc to query to an 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 am receiving the error below. Does the tuple parameter need to be handled in a different way? 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 was able to get this query to work using the string formatting operator, which isn't ideal as it introduces security concerns.

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))

score 25 · Answer 1 · answered May 24 '13 at 10:07

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

score 22 · Accepted Answer · answered Jan 28 '11 at 03:37

22

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.

answered Jan 28 '11 at 03:37

Larry Lustig

49,320
14
110
160

1

For your second option, would one need to dynamically create the SQL statement based on the size of the tuple one wished to pass to the IN statement? – user338714 Feb 03 '11 at 00:58
3

You can do something like: cursor.execute("SELECT * FROM movies WHERE rating IN ({})".format(','.join('?' * len(ratings))), ratings) – rleelr Sep 09 '16 at 15:38

M_Vu_ · Answer 3 · 2019-09-19T14:41:13.243

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 ?
''' % placeholders

cursor.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().

WombatPM · Answer 4 · 2011-01-27T22:38:42.740

The problem is your tuple. The ODBC connection is expecting a string to construct the query and you are sending a python tuple. And remember that you have to get the string quoting correct. I'm assuming 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))

I'm still receiving an "Incorrect syntax" error for the 1st parameter. Could pyodbc be treating the list like a string instead of a list? — user338714, Jan 27 '11 at 21:54
I'm still receiving a parameter error. I did find a way to get the query to work using the string format operator (see my update above), and since this code will only be run locally, the security concerns can probably be overlooked. I think cursor.executemany may be another option. — user338714, Jan 28 '11 at 02:41

SQL IN operator using pyodbc and SQL Server

4 Answers4

Linked