Executing queries doesn't alter Heroku postgreSQL DB

Question

I'm using psycopg2 python3 library to connection to my heroku postgreSQL database. Heroku provided me with database url, I store it in DATABASE_URL environmental variable. I'm trying to locally connect to DB and execute some queries. But it seems like executing queries doesn't affect on heroku database, example:

import psycopg2, os

DATABASE_URL = os.getenv('DATABASE_URL')
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
    
cur.execute("CREATE TABLE chat (id int, chat_name TEXT);")
cur.execute("""
            INSERT INTO chat (id, chat_name) VALUES
                (1, 'name1'),
                (2, 'name2');
            """)
cur.execute("SELECT * FROM chat;")
print(cur.fetchall())
conn.close()

It prints:

>>> [(1, 'name1'), (2, 'name2')]

Then I'm trying to execute following code:

import psycopg2, os

DATABASE_URL = os.getenv('DATABASE_URL')
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
    
cur.execute("SELECT * FROM chat")
print(cur.fetchall())
conn.close()

But it throws error:

>>> psycopg2.errors.UndefinedTable: relation "chat" does not exist
>>> LINE 1: SELECT * FROM chat;

Seems like table wasn't saved after executing creation and insertion queries. How to fix it?

Answer 1

The issue is the table was created and the values inserted into it within the session created by conn = psycopg2.connect(DATABASE_URL, sslmode='require') and conn.close(). As long as you stayed in that session(did not close()) everything would be there. The lack of conn.commit() meant the table and data did not persist past the conn.close(), nor was it observable by any other concurrent sessions. You need to commit() to persist the changes or set autocommit.