Postgresql tables exists, but getting "relation does not exist" in Python

Question

I am unable to query using psycopg2. DB connection is successful as DB version is printed.

import psycopg2
import pandas.io.sql as psql
cursor = connection.cursor()  
        logger.info('PostgreSQL database version:')
        cursor.execute("SELECT version()")
        logger.info(cursor.fetchone())
        my_table = psql.read_sql("SELECT table_name FROM information_schema.tables limit 2", connection)
        print(my_table)
        my_table1 = psql.read_sql('select * from ada.assessmentversion limit 1', connection)
        print(my_table1)

I am getting error as "Execution failed on sql 'select * from ada.assessmentversion limit 1': relation "ada.assessmentversion" does not exist LINE 1: select * from ada.assessmentversion limit 1"

Query works when I execute from dbeaver application manually.

Below query works :

 my_table = psql.read_sql("SELECT table_name FROM information_schema.tables limit 2", connection)

but when I add where clause it gives me no records (works when executed manually)

 my_table = psql.read_sql("""SELECT table_name FROM information_schema.tables WHERE table_schema = 'adacrawling'""", connection)
        print(my_table)

Tried multiple options but no luck:

my_table1 = psql.read_sql('select * from "ada".assessmentversion limit 1', connection)
my_table1 = psql.read_sql('select * from ada."assessmentversion" limit 1', connection)

Please help me with this.

Thanks in advance.

Most likely you’re connected to a different database (server). — Frank Heikens, May 26 '22 at 06:48
I am connected to correct database because it displays correct table schemas. — Megha Dalvi, May 26 '22 at 07:18
And you can successfully query every other table in your database and it also gives you the correct results? — Frank Heikens, May 26 '22 at 07:24
Yes, manually it works fine from dbeaver application but from python script I cant execute anything — Megha Dalvi, May 26 '22 at 07:39
Again, to what database is your python script connected? And does any other query to any other table work correctly? — Frank Heikens, May 26 '22 at 08:04
Till now only this query works and provides list of table_name present in DB. psql.read_sql("SELECT table_name FROM information_schema.tables limit 2", connection) — Megha Dalvi, May 26 '22 at 09:09
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/245068/discussion-between-frank-heikens-and-megha-dalvi). — Frank Heikens, May 26 '22 at 09:24

Postgresql tables exists, but getting "relation does not exist" in Python

0 Answers0