Psycopg2 upsert multiple rows with one query

Question

I have table in postgresql with fields id (unique) and val. I want to execute something like this:

INSERT INTO my_table (id, val) 
VALUES (%(id)s, %(val)s)
ON CONFLICT(id) DO UPDATE
SET val = val + %(val)s

Data to insert is like [{"id": 123, "val": 5}, {"id": 456, "val": 8}, ...]. Is there any way to upsert all of these with one query?

cursor.executemany won't do, it's the same as to make queries with all of these dicts in a loop one after another. Without ON CONFLICT DO UPDATE I could just do something like "insert into mytable (id, val) values " + ', '.join(['(%s, %s)'] * len(data)) and transform data to the list [id1, val1, id2, val2, ...]. But I've no idea how to combine multiple values to insert and update statement.

Answer 1

I have the same problem. After a while searching, I found two post:

1. Posgresql - upsert: https://dba.stackexchange.com/questions/167591/postgresql-psycopg2-upsert-syntax-to-update-columns
2. Psycopg2 - insert many rows: psycopg2: insert multiple rows with one query

=> So the answer for you is:

    # my table: cas(address, id, description) - address is primary key
data = [('0x18f9f00a432F50c6E2429d31776724d3cB873BEF', '1000', 'mot ngan'),
        ('0x06471C53CE649Eb4dA88b792D500544A7E5C9635', '2000', 'hai ngan')]
args = [cur.mogrify('(%s, %s, %s)', x).decode('utf-8')
        for x in data]
args_str = ', '.join(args)
cur.execute('''INSERT INTO cas (address, id, description) VALUES'''
            + args_str +
            '''ON CONFLICT (address) DO UPDATE SET     
        (address, id, description) = (EXCLUDED.address, EXCLUDED.id, EXCLUDED.description)''')