Question 170710 in PostgreSQL Question posted in
The official documentation can be found here.

Does executing queries change Heroku postgreSQL DB?

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Table wasn't creating and values were not inserted because there was no conn.commit() before conn.close(). There's an paragraph with example in psycopg documentation:
    https://www.psycopg.org/docs/usage.html#basic-module-usage


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search