Psycopg2: how to insert and update on conflict using psycopg2 with python?

Question

I am using psycopg2 to insert command to postgres database and when there is a confilict i just want to update the other column values.

Here is the query:

        insert_sql = '''
        INSERT INTO tablename (col1, col2, col3,col4)
        VALUES (%s, %s, %s, %s) (val1,val2,val3,val4)
        ON CONFLICT (col1)
        DO UPDATE SET
        (col2, col3, col4)
        = (val2, val3, val4) ; '''

        cur.excecute(insert_sql)

I want to find where I am doing wrong? I am using variables val1 , val2, val3 not actual values.

Answer 1

To quote from psycopg2's documentation:

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

Now, for an upsert operation you can do this:

insert_sql = '''
    INSERT INTO tablename (col1, col2, col3, col4)
    VALUES (%s, %s, %s, %s)
    ON CONFLICT (col1) DO UPDATE SET
    (col2, col3, col4) = (EXCLUDED.col2, EXCLUDED.col3, EXCLUDED.col4);
'''
cur.execute(insert_sql, (val1, val2, val3, val4))

Notice that the parameters for the query are being passed as a tuple to the execute statement (this assures psycopg2 will take care of adapting them to SQL while shielding you from injection attacks).

The EXCLUDED bit allows you to reuse the values without the need to specify them twice in the data parameter.