0

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.

DanH
  • 5,498
  • 4
  • 49
  • 72

1 Answers1

1

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • I realised my boobery right after posting, so I gave it a go as you suggested, however this still gives `not all arguments converted during string formatting` :( – DanH Dec 16 '13 at 10:22
  • Hrm, and what is the traceback? – Martijn Pieters Dec 16 '13 at 10:27
  • The traceback actually points to code within django_pyodbc: `sql = sql % tuple('?' * n_params)`, whilst `sql = 'EXEC spapi_patient_match @PCode = ?, @FName = ?, @LName = ?'` and `n_params = 3` – DanH Dec 16 '13 at 10:32
  • 1
    Ah! But Django insists you use `%s` for the parameter style. :-) – Martijn Pieters Dec 16 '13 at 10:36
  • Sorry I totally did not read your answer fully, and only paid attention to the `params` changes. Thanks very much! – DanH Dec 17 '13 at 01:46
  • I think the problem is that I failed to differentiate between pyodbc and django-pyodbc and thought I should be striving for the pyodbc method as stated in the wiki https://code.google.com/p/pyodbc/wiki/Cursor which encourages "?" usage. – DanH Dec 17 '13 at 01:47