Python 使用 sqlalchemy 从 PostgreSQL 查询返回 Pandas 数据帧
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27884268/
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
Return Pandas dataframe from PostgreSQL query with sqlalchemy
提问by lmart999
I want to query a PostgreSQL database and return the output as a Pandas dataframe.
我想查询 PostgreSQL 数据库并将输出作为 Pandas 数据框返回。
I created a connection to the database with 'SqlAlchemy':
我使用“SqlAlchemy”创建了到数据库的连接：
from sqlalchemy import create_engine
engine = create_engine('postgresql://user@localhost:5432/mydb')
I write a Pandas dataframe to a database table:
我将 Pandas 数据框写入数据库表：
i=pd.read_csv(path)
i.to_sql('Stat_Table',engine,if_exists='replace')
Based on the docs, looks like pd.read_sql_query() should accept a SQLAlchemy engine:
根据docs，看起来 pd.read_sql_query() 应该接受 SQLAlchemy 引擎：
a=pd.read_sql_query('select * from Stat_Table',con=engine)
But it throws an error:
但它抛出一个错误：
ProgrammingError: (ProgrammingError) relation "stat_table" does not exist
I'm using Pandas version 0.14.1.
我正在使用 Pandas 0.14.1 版。
What's the right way to do this?
这样做的正确方法是什么？
采纳答案by joris
You are bitten by the case (in)sensitivity issues with PostgreSQL. If you quote the table name in the query, it will work:
您被 PostgreSQL 的大小写（不）敏感性问题所困扰。如果您在查询中引用表名，它将起作用：
df = pd.read_sql_query('select * from "Stat_Table"',con=engine)
But personally, I would advise to just always use lower case table names (and column names), also when writing the table to the database to prevent such issues.
但就我个人而言，我建议始终使用小写的表名（和列名），在将表写入数据库时也是如此，以防止出现此类问题。
From the PostgreSQL docs (http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS):
来自 PostgreSQL 文档 ( http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS)：
Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case
引用标识符也使其区分大小写，而未引用的名称始终折叠为小写
To explain a bit more: you have written a table with the name Stat_Tableto the database (and sqlalchemy will quote this name, so it will be written as "Stat_Table" in the postgres database). When doing the query 'select * from Stat_Table'the unquoted table name will be converted to lower case stat_table, and so you get the message that this table is not found.
再解释一下：您已经将一个带有该名称的表写入Stat_Table数据库（并且 sqlalchemy 将引用此名称，因此它将在 postgres 数据库中写为“Stat_Table”）。执行查询时'select * from Stat_Table'，未加引号的表名将转换为小写stat_table，因此您会收到找不到此表的消息。
See eg also Are PostgreSQL column names case-sensitive?
回答by 7stud
The error message is telling you that a table named:
错误消息告诉您一个名为的表：
stat_table
does not exist( a relationis a table in postgres speak). So, of course you can't select rows from it. Check your db after executing:
不存在（关系是 postgres 中的表）。所以，当然你不能从中选择行。执行后检查您的数据库：
i.to_sql('Stat_Table',engine,if_exists='replace')
and see if a table by that name got created in your db.
并查看是否在您的数据库中创建了具有该名称的表。
When I use your read statement:
当我使用您的 read 语句时：
df = pd.read_sql_query('select * from Stat_Table',con=engine)
I get the data back from a postgres db, so there's nothing wrong with it.
我从 postgres 数据库取回数据，所以它没有任何问题。
回答by Chandan Kumar Singh
Read postgres sql data in pandas in given below and image link
在下面给出的熊猫中读取 postgres sql 数据和图像链接
import psycopg2 as pg
import pandas.io.sql as psql
connection = pg.connect("host=localhost dbname=kinder user=your_username password=your_password")
dataframe = psql.read_sql('SELECT * FROM product_product', connection)
product_category = psql.read_sql_query('select * from product_category', connection)
