Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 16 down vote favorite
3

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

I use sqlalchemy to create a connection the the database:

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 upon 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?

share|improve this question
up vote 17 down vote accepted

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?

share|improve this answer
up vote 2 down vote

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.

share|improve this answer
1  
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) – lmart999 Jan 11 '15 at 17:46
    
@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. – 7stud Jan 11 '15 at 21:18
    
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. – lmart999 Jan 11 '15 at 21:51
    
@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. – 7stud Jan 11 '15 at 21:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.