使用pandas.read_sql 使用参数查询数据库 [英] Query database with parameter using pandas.read_sql
问题描述
询问了一个类似问题.目标是创建此处描述的函数:
A similar question was asked. The goal is to create the function described here:
def DB_Query(d1, d2):
conn = pyodbc.connect('DSN=DATASOURCE')
tbl = "SELECT TableA.Field_1 \
FROM TableA \
WHERE TableA.Date>=? AND TableA.Date<=?"
df_tbl= pd.read_sql_query(tbl, conn, params = (d1,d2))
conn.close
return df_tbl
这适用于带有 SQL Server 驱动程序的数据库,但不适用于 Microsoft ODBC for Oracle 驱动程序.
This worked on database with SQL Server driver, but it won't work on Microsoft ODBC for Oracle driver.
当我给出,例如 d1 = '2020-02-20'
和 d2 = '2020-02-25'
时,我得到错误('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL 数据类型超出范围 (0) (SQLBindParameter)')
When I give, for e.g., d1 = '2020-02-20'
and d2 = '2020-02-25'
, I get error
('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) (SQLBindParameter)')
我知道在 Oracle 中,您需要 DATE 'YYYY-MM-DD'
来表示日期,这与 SQL Server 不同,您可以使用 'YYYY-MM-DD'
.
I understand in Oracle, you need DATE 'YYYY-MM-DD'
to express a date, which is different from SQL server where you can just use 'YYYY-MM-DD'
.
我试过在 ?
前面添加 DATE
但不起作用.有什么想法吗?
I've tried add DATE
in front of ?
but doesn't work. Any ideas?
推荐答案
在此处找到解决方案.只需将 d1
定义为 date(2020,02,20)
.
Found solution here. Just define d1
as date(2020,02,20)
.
这篇关于使用pandas.read_sql 使用参数查询数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!