执行查询不会改变Heroku的postgreSQL数据库。

huangapple go评论59阅读模式
英文:

Executing queries doesn't alter Heroku postgreSQL DB

问题

I'm using the "psycopg2" Python3 library to connect to my Heroku PostgreSQL database. Heroku provided me with a database URL, which I stored in the "DATABASE_URL" environment variable. I'm trying to locally connect to the database and execute some queries. However, it seems that executing queries doesn't affect the Heroku database. For 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 the 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 an error:

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

It seems like the table wasn't saved after executing the creation and insertion queries. How can I fix this?

英文:

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?

答案1

得分: 1

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

英文:

Table wasn't creating and values weren't 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

得分: 1

问题出在表格是在由conn = psycopg2.connect(DATABASE_URL, sslmode='require')创建的会话中创建并插入值,然后通过conn.close()关闭该会话。只要您保持在该会话中(不要close()),所有内容都会存在。由于缺少conn.commit(),表格和数据在conn.close()之后不会持久存在,也不会被任何其他并发会话观察到。您需要使用commit()来持久保存更改,或者设置autocommit

英文:

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.

huangapple
  • 本文由 发表于 2023年5月17日 20:26:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76272098.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定