使用 pyodbc 和 SQL Server 的 SQL IN 运算符 [英] SQL IN operator using pyodbc and SQL Server
问题描述
我正在使用 pyodbc 查询 SQL Server 数据库
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)")
更新:
我能够使用字符串格式化操作符让这个查询工作,这并不理想,因为它引入了安全问题.
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))
推荐答案
不能使用单个字符串参数在 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).
以 IN (?, ?, . . ., ?)
形式构建参数化查询,然后为每个占位符传入一个 单独 参数.我不是 Python 到 ODBC 的专家,但我想这在像 Python 这样的语言中特别容易做到.这样更安全,因为您可以获得参数化的全部价值.
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.
这篇关于使用 pyodbc 和 SQL Server 的 SQL IN 运算符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!