CODEWITHSUNDEEP
pythonpostgresql
G. Ramistella
G. Ramistella

Reputation: 1407

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

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.

Upvotes: 0

Views: 73

Answers (1)

Ivan Sveshnikov
Ivan Sveshnikov

Reputation: 384

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.

Upvotes: 2

Related Questions