Postgres `INSERT INTO` replacing entry instead of adding a new one

Question

So I have this code (literally the whole script):

import psycopg2

DATABASE_URL = "URL"

sql = """INSERT INTO Investments(Data)
             VALUES(%s);"""

conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
var = 'asssd'

cur.execute('CREATE TABLE Investments (ID SERIAL, Data varchar(256), PRIMARY KEY (ID)); ')

cur.execute(sql, (var,))

cur.execute("""SELECT * from Investments""")

rows = cur.fetchall()
for row in rows:
    print (row)

Now, if I execute this code as is, it prints exactly what I want. For some reason though, if I execute the script again, everything remains as is (it should add a new entry, but it doesn't, it replaces the old one).

Also, if I comment the CREATE TABLE.. part, the script stops working and tells me that the table doesn't exist.

I am using heroku for the postgres db, so if I understand it correctly everything should persist, which is not happening in my case.

Ivan Sveshnikov · Accepted Answer · 2019-06-24 22:33:33Z

2

Seems like you forgot conn.commit() after second execute. Uncommitted changes are discarded when connection is closed.

psycopg2 docs:

By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.

answered Jun 24, 2019 at 22:33

Ivan Sveshnikov

3964 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres `INSERT INTO` replacing entry instead of adding a new one

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related