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

[Paul Koning]

My impression (but I admit the documentation could be better) is that parameters on the execute call only apply to data values of INSERT statements.  For any other variable bits, format them into the command string (with the % operator and appropriate %s/%d/%x etc. elements in the string).

So I'd get:

myVarQuery = """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’)
“”” 
...
myQuery = myVarQuery % ('foo', 'bar')
cursor.execute(myQuery)
 
The syntax gets slightly messier if you want to have a variable INSERT statement that also takes data parameters.  For example

myVarInsert = "INSERT INTO myTable (%s) VALUES (%%s)"
...
myInsert = myVarInsert % 'testvar'
cursor.execute (myInsert, (value1, value2, value3))

Note the %%s in the VALUES clause, that becomes a %s in myInsert, and then gets the data values stuck into it by the execute.

	paul

On Feb 1, 2011, at 12:22 PM, Tom Hawkins wrote:

> 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
>  
> <image001.jpg>  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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110201/5b21d2dc/attachment-0001.html>