How to do parametrized queries using pypyodbc in Python?

Question

I'm discovering Python and is stuck at a error I don't understand.

When querying a SQL Server database with parameters, as I understood from the examples, the way to do it is:

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

As soon as I execute the following code, I receive the following error:

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

What doesn't support indexing? How should I write the execute statement correctly?

Answer 1

I've played with this a bit and I think there must be a bug in pypyodbc that make it not behave as the documentation suggests:

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

For example, I ran this code with pyodbc and everything works fine:

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

But switching the import line to pypyodbc breaks the second example:

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

So I don't think this is anything you're doing wrong; either pypyodbc is broken for this use case or its documentation is wrong. If I can figure out which I'll file a bug.