How to parameterize datestamp in pyODBC query?

Question

I'm using pyodbc together with QODBC to construct an ODBC query.

I'm having trouble inserting datestamp parameters. Here you can see the escalation starting from the literal version (1) to string-format version (2) to error-state versions. (Note DateFrom & DateTo):

sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateFrom = {d'2018-02-12'}, DateTo = {d'2018-02-18'}, SummarizeColumnsBy='TotalOnly', ReturnRows='All'"
sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateFrom = %s, DateTo = %s, SummarizeColumnsBy='TotalOnly', ReturnRows='All'" % (q_startdate, q_enddate)

Subsequent attempts with the insertion syntax ?, cursor.execute(sql, (q_startdate), (q_enddate)) and the variables:

q_startdate = ("{d'%s'}" % dates[0])
q_enddate = ("{d'%s'}" % dates[1])

sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateFrom = ?, DateTo = ?, SummarizeColumnsBy='TotalOnly', ReturnRows='All'"

>>> ('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) (SQLBindParameter)')

q_startdate = (dates[0])
q_enddate = (dates[1])

sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateFrom = {d'?'}, DateTo = {d'?'}, SummarizeColumnsBy='TotalOnly', ReturnRows='All'"

>>> ('42000', "[42000] [QODBC] [sql syntax error] Expected lexical element not found: = {d'?'} (11015) (SQLPrepare)")

Reading the pyodbc Wiki page on inserting data, I don't read about any speed bumps with insertion strings. This must have something to do with how pyodbc processes (escapes) the datestamp.

How do you parameterize datestamp--Especially with the qodbc flavor of datestamp.

Does your query work if your parameter values are true Python [date](https://docs.python.org/3/library/datetime.html#date-objects) values (e.g., `q_startdate = date(2018, 2, 12)`) and you simply use `... DateFrom = ?` ...? — Gord Thompson, Jul 24 '18 at 13:02
That works. Thanks. If you want, make that an answer and I'll give you the correct answer points. — xtian, Jul 25 '18 at 02:08

score 1 · Accepted Answer · answered Jul 25 '18 at 16:36

It is almost never necessary to use ODBC escape sequences like {d'2018-02-12'} in a pyodbc parameterized query. If the parameter value is a true Python date object

q_startdate = date(2018, 2, 12)

then pyodbc will inform the ODBC driver that the parameter value is a SQL_TYPE_DATE as shown in the ODBC trace log

[ODBC][2984][1532535987.825823][SQLBindParameter.c][217]
        Entry:
            Statement = 0x1f1a6b0
            Param Number = 1
            Param Type = 1
            C Type = 91 SQL_C_TYPE_DATE
            SQL Type = 91 SQL_TYPE_DATE
            Col Def = 10
            Scale = 0
            Rgb Value = 0x1f3ac78
            Value Max = 0
            StrLen Or Ind = 0x1f3ac58

and we can just use a bare parameter placeholder in our SQL command text

... parameters DateFrom = ?, ...

How to parameterize datestamp in pyODBC query?

1 Answers1

Linked