带参数的 Pandas read_sql

问题描述

有没有关于如何在 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'])

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

What is the recommended way of running these types of queries from Pandas?

推荐答案

read_sql 文档说这个 params 参数可以是列表、元组或字典(请参阅 docs).

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

在第二种情况下，当使用 dict 时，您使用的是命名参数"，并且根据 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！