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

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.

Using:

INSERT INTO members (member_id, customer_id, subscribed, customer_member_id, phone, cust_atts) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (customer_member_id) DO UPDATE SET (phone) = (EXCLUDED.phone);

I received the following error:

psycopg2.errors.FeatureNotSupported: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
LINE 1: ...ICT (customer_member_id) DO UPDATE SET (phone) = (EXCLUDED.p...

Changing to:

INSERT INTO members (member_id, customer_id, subscribed, customer_member_id, phone, cust_atts) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (customer_member_id) DO UPDATE SET (phone) = ROW(EXCLUDED.phone);

Solved the issue.

Try:

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

Here's the function that takes df, schemaname of the table, name of the table, the column that you want to use as a conflict in the name of conflict, and the engine created by create_engine of sqlalchemy. It updates the table with respect to conflict column. This is extended solution for the solution of @Ionut Ticus . Don't use pandas.to_sql() together. pandas.to_sql() destroys the primary key setting. In this case, one need to set primary key by the ALTER query, which is a suggestion by the function below. Primary key does not necessarily be destroyed by pandas, one might haven't been set it. Error would be in that case: there is no unique constraint matching given keys for referenced table? Function will suggest you to execute below.

engine.execute('ALTER TABLE {schemaname}.{tablename} ADD PRIMARY KEY ({conflictcolumn});

Function:

def update_query(df,schemaname,tablename,conflictcolumn,engine ):
"""
This function takes dataframe as df, name of schema as schemaname,name of the table to append/add/insert as tablename, 
and column name that only  other elements of rows will be changed if it's existed as conflictname,
database engine as engine.
Example to engine : engine_portfolio_pg = create_engine('postgresql://pythonuser:vmqJRZ#dPW24d@145.239.121.143/cetrm_portfolio')
Example to schemaname,tablename : weatherofcities.sanfrancisco , schemaname = weatherofcities, tablename = sanfrancisco.

"""


excluded = ""
columns = df.columns.tolist()
deleteprimary = columns.copy()
deleteprimary.remove(conflictcolumn)
excluded = ""
replacestring = '%s,'*len(df.columns.tolist())
replacestring = replacestring[:-1]

for column in deleteprimary:
    excluded += "EXCLUDED.{}".format(column)+","
excluded = excluded[:-1]

columns = ','.join(columns)

deleteprimary  = ','.join(deleteprimary)

insert_sql = """ INSERT INTO {schemaname}.{tablename} ({allcolumns})
    VALUES ({replacestring})
    ON CONFLICT ({conflictcolumn}) DO UPDATE SET
    ({deleteprimary}) = ({excluded})""".format( tablename = tablename, schemaname=schemaname,allcolumns = columns, replacestring= replacestring,
                                               conflictcolumn= conflictcolumn,deleteprimary = deleteprimary,  excluded=excluded  )



conn = engine.raw_connection()
conn.autocommit = True

#conn = engine.connect()

cursor = conn.cursor()

i = 0
print("------------------------"*5)

print("If below error happens:")
print("there is no unique constraint matching given keys for referenced table?")    
print("Primary key is not set,you can execute:")
print("engine.execute('ALTER TABLE {}.{} ADD PRIMARY KEY ({});')".format(schemaname,tablename,conflictcolumn))
print("------------------------"*5)
for index, row in df.iterrows():

    cursor.execute(insert_sql, tuple(row.values))
    conn.commit() 
    if i == 0:
        print("Order of Columns in Operated SQL Query for Rows")
        columns = df.columns.tolist()
        print(insert_sql%tuple(columns))
        print("----")
        print("Example of Operated SQL Query for Rows")
        print(insert_sql%tuple(row.values))
        print("---")
        
    i += 1 

conn.close()