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?

Are you sure your error is being produced by that line, as far as I can tell (apart from having a weird query) that should be valid according to the example pages found here:https://code.google.com/p/pyodbc/wiki/GettingStarted — Lucas Kauffman, May 19 '13 at 15:55
@LucasKauffman: I've edited the question to provide the full code (and a full traceback). — Arseni Mourzenko, May 19 '13 at 16:20
I can't test it at the moment but is your query valid syntax? — Lucas Kauffman, May 19 '13 at 16:26
@LucasKauffman: a query like `select 1 where 1 = 2` has nothing wrong; it will simply return an empty result. As for the semicolon, my mistake. Removed it; the same error is still here. — Arseni Mourzenko, May 19 '13 at 16:37
I get the same thing on your "working" example with a single parameter on Python 3.3, FWIW. — bbayles, May 19 '13 at 16:54

score 3 · Accepted Answer · answered May 19 '13 at 17:31

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.

Thanks, it is a bug in pypyodbc that broken under python 3. I have fixed in github. https://github.com/jiangwen365/pypyodbc/blob/master/pypyodbc.py — pypyodbc, Jul 06 '13 at 04:43

How to do parametrized queries using pypyodbc in Python?

1 Answers1