psycopg2 cannot find any tables after connection
Your table names Users
and Companies
both start with a capitalized letter. PostgreSQL will convert all identifiers to lower case (by default), as you can see from the error message:
psycopg2.ProgrammingError: relation "users" does not exist
Where users
is written in all lower case. This is needed if you wish to strictly follow the SQL standard (as PostgreSQL is renowned for). You can solve this in two ways:
Solve it in your database:
Adhere to a common convention and rename your tables to be all lowercase.
Solve it in your code:
Quote your identifiers (your table name in this case) so PostgreSQL will leave them untouched:
cur.execute(""" SELECT * from "Users" """)
Related videos on Youtube
user21398
Updated on September 16, 2022Comments
-
user21398 over 1 year
I can connect to my database, but psycopg2 fails to find any of my tables. The following will error trying to get my users:
import psycopg2 try: conn = psycopg2.connect("dbname='pdb' user='postgres' host='localhost' password='password'") except: print 'failed to connect' cur = conn.cursor() cur.execute(""" SELECT * from Users """) rows = cur.fetchall() for row in rows: print row[0] #Error: psycopg2.ProgrammingError: relation "users" does not exist LINE 1: SELECT * from Users # This also fails cur.execute("""SELECT * from pdb.Users """)
If I do:
cur.execute(""" SELECT * from pg_database """) # Outputs template1 template0 postgres pdb
In my admin panel, pdb shows a bunch of tables, one of them being
Users
, so I'm not sure why psycopg2 can't find it.Here's a printout from psql for
pdb
:List of relations Schema | Name | Type | Owner --------+--------------------+-------+---------- public | Companies | table | postgres public | Users | table | postgres (2 rows)
-
mimic over 5 yearsYou saved my day. I didn't know that "standard" and, to be honest, it's weird. Thanks.