带参数的 Pandas read_sql

问题描述：

有没有关于如何在 Pandas 中使用 SQL 查询传递参数的例子？

具体来说，我使用 SQLAlchemy 引擎连接到 PostgreSQL 数据库。到目前为止，我发现以下方法有效：

df = psql.read_sql(('select "Timestamp","Value" from "MyTable" '
                     'where "Timestamp" BETWEEN %s AND %s'),
                   db,params=[datetime(2014,6,24,16,0),datetime(2014,6,24,17,0)],
                   index_col=['Timestamp'])

Pandas 文档说这params也可以作为字典传递，但我似乎无法让它工作，例如尝试过：

df = psql.read_sql(('select "Timestamp","Value" from "MyTable" '
                     'where "Timestamp" BETWEEN :dstart AND :dfinish'),
                   db,params={"dstart":datetime(2014,6,24,16,0),"dfinish":datetime(2014,6,24,17,0)},
                   index_col=['Timestamp'])

从 Pandas 运行这些类型的查询的推荐方法是什么？

解决方案 1：

文档read_sql说这个params参数可以是列表，元组或字典（参见文档）。

要传递 SQL 查询中的值，可以使用不同的语法：?，，，，（参见PEP249）。
但并非所有数据库驱动程序都支持所有这些可能性，支持哪种语法取决于您使用的驱动程序（我想在您的情况下:1）。:name`%s`%(name)s
psycopg2

在第二种情况下，当使用字典时，您使用的是“命名参数”，并且根据文档psycopg2，它们支持该%(name)s样式（因此:name我认为不是），请参阅http://initd.org/psycopg/docs/usage.html#query-parameters。

因此，使用该样式应该可行：

df = psql.read_sql(('select "Timestamp","Value" from "MyTable" '
                     'where "Timestamp" BETWEEN %(dstart)s AND %(dfinish)s'),
                   db,params={"dstart":datetime(2014,6,24,16,0),"dfinish":datetime(2014,6,24,17,0)},
                   index_col=['Timestamp'])

解决方案 2：

在从 SQLite 表读取时，我遇到了传递大量参数的问题。后来发现，由于您将字符串传递给read_sql，因此只需使用 f-string 即可。尝试使用 MSSQL pyodbc 进行同样的操作，效果也很好。

对于 SQLite，它看起来像这样：

# write a sample table into memory
from sqlalchemy import create_engine
df = pd.DataFrame({'Timestamp': pd.date_range('2020-01-17', '2020-04-24', 10), 'Value1': range(10)})
engine = create_engine('sqlite://', echo=False)
df.to_sql('MyTable', engine);

# query the table using a query
tpl = (1, 3, 5, 8, 9)
query = f"""SELECT Timestamp, Value1 FROM MyTable WHERE Value1 IN {tpl}"""
df = pd.read_sql(query, engine)

如果参数是日期时间，则会更复杂一些，但调用您使用的 SQL 方言的日期时间转换函数应该可以完成这项工作。

start, end = '2020-01-01', '2020-04-01'
query = f"""SELECT Timestamp, Value1 FROM MyTable WHERE Timestamp BETWEEN STRFTIME("{start}") AND STRFTIME("{end}")"""
df = pd.read_sql(query, engine)