Insert or update Posgresql via Python

Asked

Viewed 578 times

1

Today I am using the following code to call via API and store my data in the Postgresql database:

  conn = psycopg2.connect("dbname='DBNAME' user='USER' host='HOST' 
password='PASSWORD'")

insert = "INSERT INTO TABLE (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5) 
VALUES"

.... Here is a list where I extract the data in json and insert it into the database .....

if len(gravar) >0 :
    cur = conn.cursor()
    y = b','.join(cur.mogrify(" 
(%s,%s,%s,%s,%s)", x) for x in gravar)
    comando = insert + y.decode()
    try:
        cur.execute("TRUNCATE TABLE TABLE")
        cur.execute(comando)
        conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)

cur.close()
print('Carga Completa')
else:
conn.close()
print('Nada a Inserir')

To not overwrite the data I am doing a trucate before running Insert. How I could do an "Insert or update" in this command, thus avoiding having to truncate the table?

sql python postgresql

  • What version of yours PostgreSQL?

  • 1

    Perhaps the usual ON CONFLICT DO UPDATE can help you. https://www.postgresql.org/docs/current/sql-insert.html

  • 1

    As mentioned you can use the INSERT with the ON CONFLICT. There are some observations: postgres must be version >=9.5; the ON CONFLICT validates the restriction corresponding to only one field or Constraint.

  • @Camilosantos Thanks a lot for the help, I put the ON CONFLICT is worked normally!

  • Cool @Maursb!! As a suggestion, post your solution in the answer to the question. That way if someone else is in the same situation you can use your solution.

1 answer

0


I used the On Conflict Update clause (documentation link) and removed the truncate before entering the data.

My code stayed that way:

conn = psycopg2.connect("dbname='DBNAME' user='USER' host='HOST' 
password='PASSWORD'")

insert = "INSERT INTO TABLE (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5) 
VALUES"

.... Here is a list where I extract the data in json and insert it into the database .....

if len(gravar) >0 :
    cur = conn.cursor()
    y = b','.join(cur.mogrify(" 
(%s,%s,%s,%s,%s)", x) for x in gravar)
     comando = insert + y.decode()
    try:
        cur.execute(comando + "ON CONFLICT (COLUMN1) DO UPDATE;")
        conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)

cur.close()
print('Carga Completa')
else:
conn.close()
print('Nada a Inserir')

Thanks so much for your help, guys!

Browser other questions tagged sql python postgresql

You are not signed in. Login or sign up in order to post.