Search code examples
stringpython-3.xquotation-marks

python 3 single quotation escapes parameter marker

hi I want to reproduce the sql command

SP_HELPTEXT SP_CIW_STEP1

in cursor.execute in pyodb, using ? as parameter marker .

import pyodbc

ch = pyodbc.connect('DRIVER={SQL Server};SERVER=xxx;DATABASE=yyy;Trusted_Connection=True')
cur = ch.cursor()


cur.execute("sp_helptext '?'", 'SP_CIW_STEP1')

yields error:

ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

And (thanks @ryugie)

cur.execute("sp_helptext ?", "'SP_CIW_STEP1'")

also causes error:

[SQL Server]The object ''SP_CIW_STEP1'' does not exist in database 'xxx' or is invalid for this operation

While 

cur.execute("? 'SP_CIW_STEP1'", 'sp_helptext')

works, yielding

Out[28]: <pyodbc.Cursor at 0x9c21db0>

So it seems the single quotation ruins the parameter marker. I tried putting \ and adding r in front of the string. Doesn't work. Any help's appreciated here. Thanks-

Solution

  • Parameter markers shouldn't be quoted, and neither should parameter values. 

    cur.execute("sp_helptext ?", "SP_CIW_STEP1")

    should work just fine.