Correct usage of %s when inserting/updateing into a psql database

Question

I want to insert values (a,b,c,d) into a database and if the triple (a,b,c) already exists, I want to update that row with the new value d. For the sake of speed I want to bulk insert. I am using the following query:

    query = """INSERT INTO table
                 (a, b, c, d)
                 VALUES (%(a)s, %(b)s, %(c)s, %(d)s)
                 ON CONFLICT (a, b, c)
                 DO UPDATE SET d = %(d)s"""            
    psycopg2.extras.execute_values(cursor, query, ((
            test_dict[line]['a'],
            test_dict[line]['b'],
            test_dict[line]['c'],
            test_dict[line]['d'])
            for line in test_dict))

Here cur is a cursor to the database and test_dict a dictionary. However, I get the following error:

ValueError: unsupported format character: '('

So if I alter the query to

query = """INSERT INTO table
                 (a, b, c, d)
                 VALUES %s""" 

i don't get this error anymore, but I also don't know how to tell the code to update the value d in case of a conflict.

Answer 1

How do I then alter the line DO UPDATE SET d = %(d)s?

Tell PostgreSQL, not python, the value to update to:

DO UPDATE SET d = EXCLUDED.d

EXCLUDED is how you reference the row whose insertion was conflicted.