python - 如何在Python中使用pyodbc进行参数化查询？

Question

我正在探索 Python，但遇到了一个我不明白的错误。

当使用参数查询SQL Server数据库时，据我从示例中了解到，执行方法是:

import pypyodbc

connectionString = 'DRIVER={SQL Server};SERVER=VSQL001;DATABASE=Tests;Trusted_Connection=yes'

connection = pypyodbc.connect(connectionString)
cursor = connection.cursor()
cursor.execute('select 1 where ? = ?', [1, 2]);
cursor.close()
connection.close()

当我执行以下代码时，我收到以下错误:

Traceback (most recent call last):
File "C:\Program Files (x86)\Python\lib\site-packages\pypyodbc.py", line 1171, in prepare
check_success(self, ret)
File "C:\Program Files (x86)\Python\lib\site-packages\pypyodbc.py", line 937, in check_success
ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
File "C:\Program Files (x86)\Python\lib\site-packages\pypyodbc.py", line 919, in ctrl_err
raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: ('07009', '[07009] [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "\MFRAME\Data\Profiles\Arsene\Desktop\query.py", line 7, in
cursor.execute('select 1 where ? = ?', ['1', '2']);
File "C:\Program Files (x86)\Python\lib\site-packages\pypyodbc.py", line 1398, in execute
self.prepare(query_string)
File "C:\Program Files (x86)\Python\lib\site-packages\pypyodbc.py", line 1174, in prepare
if sys.exc_info()[1][0] == '07009':
TypeError: 'DatabaseError' object does not support indexing

什么不支持索引？我应该如何正确编写execute语句？

Answer 1

我已经玩过这个了，我认为 pypyodbc 中一定有一个错误，使它不像 documentation 那样表现。建议:

In most cases, you can simply try pypyodbc in your existing pyodbc powered script with the following changes

例如，我使用 pyodbc 运行此代码，一切正常:

... import pyodbc as db
... conn = db.connect(connect_string)
... cursor = conn.cursor()
>>> # One parameter
... res = cursor.execute("SELECT 1 WHERE 1=?", [1,])
... print(cursor.fetchall())
[(1, )]
>>> # Two parameters
... res = cursor.execute("SELECT 2 WHERE ?=?", [1, 1])
... print(cursor.fetchall())
[(2, )]
>>> conn.close()

但是将导入行切换到 pypyodbc 会破坏第二个示例:

... import pypyodbc as db
... conn = db.connect(connect_string)
... cursor = conn.cursor()
>>> # One parameter
... res = cursor.execute("SELECT 1 WHERE 1=?", [1,])
... print(cursor.fetchall())
[(1,)]
>>> # Two parameters
... res = cursor.execute("SELECT 2 WHERE ?=?", [1, 1])
... print(cursor.fetchall())
Traceback (most recent call last):
...
TypeError: 'DatabaseError' object does not support indexing
>>> conn.close()

所以我不认为你做错了什么；要么 pypyodbc 在这个用例中被破坏，要么它的文档是错误的。如果我能找出哪个我会提交一个错误。