英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论