如何在pyODBC查询中参数化日期戳?

问题描述

我正在使用 pyodbc 和 QODBC 构建 ODBC 查询.

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

我在插入日期戳参数时遇到问题.在这里您可以看到从文字版本 (1) 到字符串格式版本 (2) 到错误状态版本的升级.(注意DateFrom & DateTo):

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):

1. sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount 参数 DateFrom = {d'2018-02-12'}, DateTo = {d'2018-02-18'}, SummarizeColumnsBy='TotalOnly', ReturnRows='All'"

sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount 参数 DateFrom = %s, DateTo = %s, SummarizeColumnsBy='TotalOnly', ReturnRows='All'" % (q_startdate, q_enddate)

使用插入语法 ?、cursor.execute(sql, (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])

1. sql = "sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount 参数 DateFrom = ?, DateTo = ?, SummarizeColumnsBy='TotalOnly', ReturnRows='All'"

>>>('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL 数据类型超出范围 (0) (SQLBindParameter)')

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

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

>>>('42000', "[42000] [QODBC] [sql 语法错误] 未找到预期的词法元素: = {d'?'} (11015) (SQLPrepare)")

阅读 pyodbc 维基页面插入数据，我没有读到任何关于插入字符串的减速带.这一定与 pyodbc 如何处理(转义)日期戳有关.

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.

你如何参数化日期戳——尤其是使用 qodbc 日期戳的味道.>

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

推荐答案

在 pyodbc 参数化查询中几乎不需要像 {d'2018-02-12'} 这样的 ODBC 转义序列.如果参数值是真正的 Python date 对象>

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)

然后pyodbc 将通知ODBC 驱动程序参数值为SQL_TYPE_DATE，如ODBC 跟踪日志中所示

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

我们可以在我们的 SQL 命令文本中使用一个裸参数占位符

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

... parameters DateFrom = ?, ...

这篇关于如何在pyODBC查询中参数化日期戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！