使用pyodbc和SQL Server的SQL IN运算符

Question

我正在使用pyodbc来查询一个SQL Server数据库。

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

但是我收到了下面的错误。这个元组参数需要以不同的方式处理吗？有没有更好的方法来构建这个查询？

('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)")

更新：

我通过使用字符串格式化操作符让这个查询成功了，但这并不是最理想的做法，因为这样会引入安全隐患。

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

为了更好地理解Larry和geographika的回答，我们来详细说说：

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)

使用这个占位符，查询的结果会是：

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

如果你传入ratings，它会试图把所有的项目放进一个?里。但如果我们传入*ratings，那么ratings中的每个项目都会在in()这个部分占据自己的位置。因此，我们会把元组(*ratings, st_dt, end_dt)传给cursor.execute()。

Answer 2

为了进一步解释Larry提到的第二种选择——动态创建一个带参数的字符串，我成功地使用了以下代码：

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)

这段代码生成了以下SQL语句，并且带上了合适的参数：

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

Answer 3

你不能用一个字符串参数来给 IN () 语句传多个值。要做到这一点，只有以下两种方法：

1. 字符串替换（就像你做的那样）。

2. 构建一个参数化的查询，格式是 IN (?, ?, . . ., ?)，然后为每个占位符传入一个单独的参数。我对Python和ODBC不是很专业，但我想在像Python这样的语言中，这个过程会特别简单。这种方法更安全，因为你能充分利用参数化的好处。