使用 sql 炼金术从 PostgreSQL 查询返回熊猫数据框

我想查询 PostgreSQL 数据库并将输出作为熊猫数据框返回。

我用“ SqlAlchemy”创建了一个到数据库的连接:

from sqlalchemy import create_engine
engine = create_engine('postgresql://user@localhost:5432/mydb')

我写了一个熊猫数据框到一个数据库表:

i=pd.read_csv(path)
i.to_sql('Stat_Table',engine,if_exists='replace')

基于 医生，似乎 pd.read _ sql _ query ()应该接受一个 SQLAlchemy 引擎:

a=pd.read_sql_query('select * from Stat_Table',con=engine)

但它抛出了一个错误:

ProgrammingError: (ProgrammingError) relation "stat_table" does not exist

我用的是熊猫版本0.14.1。

怎么做才是正确的？

117794 次浏览
小开

The error message is telling you that a table named:

stat_table

does not exist( a relation is a table in postgres speak). So, of course you can't select rows from it. Check your db after executing:

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:

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.

小开
最佳答案

You are bitten by the case (in)sensitivity issues with PostgreSQL. If you quote the table name in the query, it will work:

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

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_Table to 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.

See eg also Are PostgreSQL column names case-sensitive?

小开

Read postgres sql data in pandas in given below and image link

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)

https://i.stack.imgur.com/1bege.png

小开

Late to the party here, but to give you a full example of this:

import pandas as pd
import psycopg2 as pg


engine = pg.connect("dbname='my_db_name' user='pguser' host='127.0.0.1' port='15432' password='pgpassword'")
df = pd.read_sql('select * from Stat_Table', con=engine)

You need to run the following to install the dependencies for ubuntu:

pip install pandas psycopg2-binary SQLAlchemy

Pandas docs on the subject here

小开

import sqlalchemy
import psycopg2

engine = sqlalchemy.create_engine('postgresql://user@localhost:5432/mydb')

You must specify schema and table
df = pd.read_sql_query("""select * from "dvd-rental".film""", con=engine)


提交答案