PostgreSQL Table exists but python says it doesn't-CodePudding

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.

CodePudding user response：

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