I'm interfacing with SQL Server from Django using django-pyodbc. I'm trying to build a query with optional parameters around a stored procedure which I'll pass to cursor.execute.
I'm constructing the query manually so that I only provide SP variables if they are sent via POST, which essentially leads me to this point:
I end up with the following query and params tuple:
query = "EXEC spapi_patient_match @PCode = '?', @FName = '%?%', @LName = '%?%'"
params = (u'PC', u'FN', u'LN')
Passing these via execute to the cursor:
cursor.execute(query, params)
I get the error not enough arguments for format string. I'm wondering whether this is related to % usage in python strings, so I instead double-up on them with the following query:
query = "EXEC spapi_patient_match @PCode = '?', @FName = '%%?%%', @LName = '%%?%%'"
And end up with this error: not all arguments converted during string formatting
I've also looked into trying to move the wildcards into the Stored Procedure definiton but that did not work and I've now read on SO is also ill-advised.
EDIT: As suggested by Martijn Pieters below I have moved the wildcards into the values tuple, which I agree is probably the correct approach, however this hasn't solved the problem and still results in the error not all arguments converted during string formatting.
Add the % wildcards to the values, and don't quote the parameters. You'll also have to use the %s or %(name)s parameter styles for Django raw queries:
query = "EXEC spapi_patient_match @PCode = %s, @FName = %s, @LName = %s"
params = (u'PC', u'%FN%', u'%LN%')
The Django-ODBC plugin used translates the SQL parameter style to the appropriate ? style for the pyodbc adapter.