Return Pandas dataframe from PostgreSQL query with sqlalchemy

Question

I want to query a PostgreSQL database and return the output as a Pandas dataframe.

I created a connection to the database with 'SqlAlchemy':

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

I write a Pandas dataframe to a database table:

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:

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.

What's the right way to do this?

Community · Accepted Answer · 2017-05-23 11:33:24Z

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?

Preston · Accepted Answer · 2021-03-12 13:39:06Z

27

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

answered Mar 12, 2021 at 13:39

Preston

8,26511 gold badges64 silver badges96 bronze badges

1 Comment

insanely_sin Over a year ago

i got this warning: " UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql(query, con=engine)"

Chandan Kumar Singh · Accepted Answer · 2020-02-21 12:45:24Z

24

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.sstatic.net/1bege.png

edited Feb 21, 2020 at 12:45

answered Feb 21, 2020 at 12:40

Chandan Kumar Singh

3453 silver badges5 bronze badges

Comments

7stud · Accepted Answer · 2017-10-11 18:31:23Z

1

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.

edited Oct 11, 2017 at 18:31

answered Jan 11, 2015 at 5:52

7stud

48.8k14 gold badges107 silver badges136 bronze badges

4 Comments

lmart999 Over a year ago

Thanks. Checked and the table was indeed created. Like @joris said, it was a case sensitivity problem in table name: I re-wrote the table: i.to_sql('stat_table',engine,if_exists='replace') and then it works: a=pd.read_sql_query('select * from stat_table',engine)

7stud Over a year ago

@Imart999, When I wrote: see if a table by that name got created in your db --that name was referring to the name in the error message, which was stat_table. The error message name is what is relevant--with ANY error you get. And because python NEVER makes a mistake, the error meant your code NEVER created a table named stat_name. See how I posted the table name stat_name in it's own paragraph and highlighted it--that was supposed to direct your attention to it.

lmart999 Over a year ago

Right, I get it. I saw both responses at the same time (was offline). I see that your response is leading me to the same answer (e.g., ensure table named stat_table is written) that @joris stated explicitly (e.g., case sensitivity matters). Appreciate the response.

7stud Over a year ago

@Imart999, No worries. When I reread my answer, I realized that what that name referred to wasn't entirely clear. I should have left out your line of code.

Antony Hatchkins · Accepted Answer · 2025-02-26 11:04:56Z

1

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)

edited Feb 26 at 11:04

Antony Hatchkins

34.4k13 gold badges121 silver badges114 bronze badges

answered Jan 31, 2022 at 9:26

Иван Любаев

111 bronze badge

Collectives™ on Stack Overflow

Return Pandas dataframe from PostgreSQL query with sqlalchemy

5 Answers 5

Comments

1 Comment

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related