Python 带参数的 Pandas read_sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24408557/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pandas read_sql with parameters
提问by tobycoleman
Are there any examples of how to pass parameters with an SQL query in Pandas?
是否有任何示例说明如何在 Pandas 中使用 SQL 查询传递参数？
In particular I'm using an SQLAlchemy engine to connect to a PostgreSQL database. So far I've found that the following works:
特别是我使用 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'])
The Pandas documentation says that params can also be passed as a dict, but I can't seem to get this to work having tried for instance:
Pandas 文档说 params 也可以作为 dict 传递，但我似乎无法让它工作，例如：
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'])
What is the recommended way of running these types of queries from Pandas?
从 Pandas 运行这些类型的查询的推荐方法是什么？
采纳答案by joris
The read_sqldocs say this paramsargument can be a list, tuple or dict (see docs).
该read_sql文件说这个params参数可以是一个列表，元组或字典（见文档）。
To pass the values in the sql query, there are different syntaxes possible: ?, :1, :name, %s, %(name)s(see PEP249).
But not all of these possibilities are supported by all database drivers, which syntax is supported depends on the driver you are using(psycopg2in your case I suppose).
要在 sql 查询中传递值，可以使用不同的语法：?, :1, :name, %s, %(name)s（请参阅PEP249）。
但并非所有数据库驱动程序都支持所有这些可能性，支持哪种语法取决于您使用的驱动程序（psycopg2我想在您的情况下）。
In your second case, when using a dict, you are using 'named arguments', and according to the psycopg2documentation, they support the %(name)sstyle (and so not the :nameI suppose), see http://initd.org/psycopg/docs/usage.html#query-parameters.
So using that style should work:
在您的第二种情况下，当使用字典时，您使用的是“命名参数”，并且根据psycopg2文档，它们支持该%(name)s样式（所以不是:name我想的），请参阅http://initd.org/psycopg/docs/用法.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'])
