Return Pandas dataframe from PostgreSQL query with sqlalchemy
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 pgimport pandas.io.sql as psqlconnection = 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)
Late to the party here, but to give you a full example of this:
import pandas as pdimport psycopg2 as pgengine = 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