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

[Matteo Boscolo]

try:
myQuery ="""your sql code """%(('foo', 'bar'))
and then
cursor.execute(myQuery)

regards,
Matteo

Il 01/02/2011 18:22, Tom Hawkins ha scritto:
>
> 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
>
>
> _______________________________________________
> python-win32 mailing list
> python-win32 at python.org
> http://mail.python.org/mailman/listinfo/python-win32
>
>
> Nessun virus nel messaggio.
> Controllato da AVG - www.avg.com <http://www.avg.com>
> Versione: 10.0.1204 / Database dei virus: 1435/3416 - Data di 
> rilascio: 01/02/2011
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110202/1a8171e5/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 791 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110202/1a8171e5/attachment-0001.jpe>