PostgreSQL Table exists but python says it doesn't

Question

I am new to Python and although it seems like some silly mistake but I am not able to figure out what am I doing wrong. Any suggestions or hints will be very helpful. And also if this question has been answered before, please link me to that.

I am writing a simple python script which will connect to the database. Now in that database I am checking that test_run table exists. If it exists just print that it is exists, if not, create the table.

# Connect to the Database Server, now with the created DB
try:
    connection = psycopg2.connect(user="postgres", password="postgres", host="127.0.0.1", port="5432",
                                  database="dashboard")
except (Exception, psycopg2.Error) as error:
    print("Connection not established", error)


# Check if test_run Table Exists
cursor = connection.cursor()
if bool(cursor.execute("SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name='test_run')")):
    print('test_run table exists. Moving On.')
else:
    print('test_run does not exist. Creating the Table now.')
    cursor.execute("CREATE TABLE test_run (run_id serial PRIMARY KEY, date date, status varchar(255), "
                   "total_time integer, project_name varchar(255));")

Now in the DB the table test_run is present and when I run the SELECT EXISTS command I get a true.

Now when I run the above script else condition is executed indicating that the test_run table doesn't exist. But ideally the if condition should be executed as the table do exist.

Oleksii Tambovtsev · Accepted Answer · 2021-12-08 19:53:28Z

1

You should try this.

import psycopg2
# Connect to the Database Server, now with the created DB
try:
    connection = psycopg2.connect(user="postgres", password="postgres", host="127.0.0.1", port="5432", database="dashboard")
except (Exception, psycopg2.Error) as error:
    print("Connection not established", error)


# Check if test_run Table Exists
cursor = connection.cursor()
cursor.execute("SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name='test_run')")
if bool(cursor.fetchone()[0]):
    print('test_run table exists. Moving On.')
else:
    print('test_run does not exist. Creating the Table now.')
    cursor.execute("CREATE TABLE test_run (run_id serial PRIMARY KEY, date date, status varchar(255), "
               "total_time integer, project_name varchar(255));")
    connection.commit()

edited Dec 8, 2021 at 19:53

answered Dec 8, 2021 at 19:47

Oleksii Tambovtsev

2,9041 gold badge6 silver badges24 bronze badges

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

1 Comment

Alapan Das Over a year ago

cursor.fetchone()[0] did the trick. Thanks a lot for your quick help.

Collectives™ on Stack Overflow

PostgreSQL Table exists but python says it doesn't

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related