带有参数的 pandas read_sql [英] Pandas read_sql with parameters
问题描述
是否有示例说明如何在Pandas中通过SQL查询传递参数?
Are there any examples of how to pass parameters with an SQL query in Pandas?
特别是我正在使用SQLAlchemy引擎连接到PostgreSQL数据库.到目前为止,我发现以下工作有效:
In particular I'm using an SQLAlchemy engine to connect to a PostgreSQL database. So far I've found that the following works:
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也可以作为dict传递,但是例如,我似乎无法通过尝试来使它起作用:
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:
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?
推荐答案
read_sql
文档说此params
参数可以是列表,元组或字典(请参见
The read_sql
docs say this params
argument can be a list, tuple or dict (see docs).
要在sql查询中传递值,可以使用不同的语法:?
,:1
,:name
,%s
,%(name)s
(请参见PEP249 ).
但是并非所有数据库驱动程序都支持所有这些可能性,支持哪种语法取决于您使用的驱动程序(我想您使用的是psycopg2
).
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 (psycopg2
in your case I suppose).
在第二种情况下,使用字典时,您使用的是命名参数",并且根据psycopg2
文档,它们支持%(name)s
样式(因此不支持我认为的:name
),请参见 http://initd.org/psycopg/docs/usage.html#query-parameters .
因此,使用该样式应该可以:
In your second case, when using a dict, you are using 'named arguments', and according to the psycopg2
documentation, they support the %(name)s
style (and so not the :name
I suppose), see http://initd.org/psycopg/docs/usage.html#query-parameters.
So using that style should work:
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'])
这篇关于带有参数的 pandas read_sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!