[python-win32] How to specify parameters in a query using odbc

[Tom Hawkins]

Hi,

 

I'm trying to get data out of a MS SQL Server 2005 database from a
Python 2.5 script, using the odbc module - I can't easily get anything
more up-to-date installed on this system.

 

If I specify the query literally, like this:

 

import dbi, odbc

 

myQuery = """SELECT EnteredValue FROM SampleTests INNER JOIN
SampleResults

ON (SampleTests.SampleCode = SampleResults.SampleCode AND 

SampleTests.AuditFlag = SampleResults.AuditFlag AND

SampleTests.TestPosition = SampleResults.TestPosition)

WHERE (SampleTests.SampleCode='foo' AND SampleTests.AuditFlag=0 AND
SampleTests.TestCode='bar' AND

SampleTests.TestStatus<900 AND SampleResults.ComponentName='IntMethod')

"""

 

conn=odbc.odbc("DSN=mydatabase;UID=myusername;PWD=mypassword")

cursor=conn.cursor()

cursor.execute(myQuery)

 

print cursor.fetchall()

 

conn.close()

 

 

...it works OK, but if I try to parameterise the query:

 

myQuery = """SELECT EnteredValue FROM SampleTests INNER JOIN
SampleResults

ON (SampleTests.SampleCode = SampleResults.SampleCode AND 

SampleTests.AuditFlag = SampleResults.AuditFlag AND

SampleTests.TestPosition = SampleResults.TestPosition)

WHERE (SampleTests.SampleCode=%s AND SampleTests.AuditFlag=0 AND
SampleTests.TestCode=%s AND

SampleTests.TestStatus<900 AND SampleResults.ComponentName='IntMethod')

"""

...

cursor.execute(myQuery, ('foo', 'bar'))

 

 

...I get:

 

dbi.program-error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near 's'. in EXEC

 

 

Any idea what I'm doing wrong please? Apologies if I've missed something
stupid. I get the same error if I only specify one of the parameters
with %s and leave the other one literal, by the way.

 

Thanks,

Tom

 

   Tom Hawkins

Principal Scientist

Innospec Inc

Tel: +44 (0)151 356 6197

Fax: +44 (0)151 356 6112

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110201/a72a6429/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 791 bytes
Desc: image001.jpg
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110201/a72a6429/attachment-0001.jpg>