使用pyodbc和Server的SQL运算符

Question

我使用pyodbc查询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)")

更新：

我能够使用string格式化操作符来实现这个查询，这并不理想，因为它引入了安全性问题。

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

不能使用单个字符串参数将IN ()子句中的多个值参数化。要做到这一点，唯一的办法是：

1. 字符串替换(正如您所做的那样)。
2. 在表单IN (?, ?, . . ., ?)中构建一个参数化查询，然后为每个位置持有者传递一个单独的参数。我不是Python to ODBC的专家，但我认为在Python这样的语言中这样做特别容易。这更安全，因为您获得了parameterization.

的全部值

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

为了进一步了解拉里和地理学家的答案：

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