psycopg2 cannot find any tables after connection

Question

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)

Timusan · Accepted Answer · 2016-10-05 23:51:38Z

15

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

edited Oct 5, 2016 at 23:51

answered Dec 12, 2014 at 2:17

Timusan

3,4551 gold badge24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mimic Over a year ago

You saved my day. I didn't know that "standard" and, to be honest, it's weird. Thanks.

Mohd Shoaib Sayyed · Accepted Answer · 2018-11-28 09:31:32Z

This issue also usually happens when you are connected to wrong database. In my case i was connected to wrong database Below is my code to connect using psycopg2 library

import psycopg2
try:
            src_conn = psycopg2.connect("dbname={} user={} host=localhost password={}".format(self.src_db, self.src_db, self.src_url ,self.src_pwd))
            if src_conn:
                src_curr = src_conn.cursor()
                print("Cursor found",src_curr)
                src_curr.execute("select * from product_template")
                recs = src_curr.fetchall()
                print(recs)
            else:
                print("Cursor not found")
        except Exception as ex:
            print(ex)

Collectives™ on Stack Overflow

psycopg2 cannot find any tables after connection

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related