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@python.org
> http://mail.python.org/mailman/listinfo/python-win32
_______________________________________________
python-win32 mailing list
python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
