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.

You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:

insert_sql = '''
   INSERT INTO {t} (id,col1, col2, col3)
        VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO UPDATE SET
            (col1, col2, col3)
            = (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
'''

See examples of use in Postgres documentation about ON CONFLICT.

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)''')

Here is my code for bulk insert & insert on conflict update query for postgresql from pandas dataframe:

Lets say id is unique key for both postgresql table and pandas df and you want to insert and update based on this id.

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine(postgresql://username:pass@host:port/dbname)
query = text(f""" 
                INSERT INTO schema.table(name, title, id)
                VALUES {','.join([str(i) for i in list(df.to_records(index=False))])}
                ON CONFLICT (id)
                DO  UPDATE SET name= excluded.name,
                               title= excluded.title
         """)
engine.execute(query)

Make sure that your df columns must be same order with your table.

You need to use table EXCLUDED instead of value literals in your ON CONFLICT statement:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to the row proposed for insertion using the special excluded table.

You also don't need to re-set the conflicting values, only the rest.

INSERT INTO table (col1, col2, col3) 
VALUES 
    (value1, value2, value3), 
    (value4, value5, value6)
ON CONFLICT (col1) DO UPDATE 
SET (col2, col3) = (EXCLUDED.col2, EXCLUDED.col3);

For readability, you can format your in-line SQLs if you triple-quote your f-strings. I'm not sure if and which IDEs can detect it's an in-line SQL in Python and switch syntax highlighting, but I find indentation helpful enough.

upsert_statement = f"""
    INSERT INTO table (col1, col2, col3) 
    VALUES 
        ({value1}, {value2}, {value3}), 
        ({value4}, {value5}, {value6})
    ON CONFLICT (col1) DO UPDATE 
    SET (col2, col3) = (EXCLUDED.col2, EXCLUDED.col3)"""

Here's a test at db<>fiddle:

drop table if exists test_70066823 cascade;
create table test_70066823 (
    id integer primary key, 
    text_column_1 text, 
    text_column_2 text);
insert into test_70066823 values
  (1,'first','first')
 ,(2,'second','second') returning *;

insert into test_70066823
values  (1, 'third','first'),
        (3, 'fourth','third'),
        (4, 'fifth','fourth'),
        (2, 'sixth','second')
on conflict (id) do update 
set text_column_1=EXCLUDED.text_column_1,
    text_column_2=EXCLUDED.text_column_2
returning *;

You can refer to this for improved insert performance. Inserts with a simple string-based execute or execute_many are the top 2 slowest approaches mentioned there.

Yeah, I would vote for COPY, providing you can write a file to the server's hard drive (not the drive the app is running on) as COPY will only read off the server.

More searching and trying things and this answer on Stack Overflow got me where I needed with using 'excluded'.

Code, reworked and working:

for zip_file in os.listdir():
    if zipfile.is_zipfile(zip_file):
        logger.info("Processing %s", zip_file)
        for member in zipfile.ZipFile(zip_file).namelist():
            now = dt.datetime.now()
            prices_list = []
            zipfile.ZipFile(zip_file).extract(member, path=temp)
            local_xml = os.path.join(temp, member)
            tree = ET.parse(local_xml)
            root = tree.getroot()
            ns = root.tag[:-4]
            for finhist in root.findall("./%sFinancialHistory" % ns):
                asset_id = int(finhist.get("Id"))
                logger.debug("Processing %s", asset_id)
                for prices in finhist.findall("./%sPrices" % ns):
                    price_currency = prices.get("Currency")
                    for priceset in prices.findall("./%sPriceSet" % ns):
                        price_date = priceset.get("Date")
                        for price in priceset.findall("./%sPrice" % ns):
                            price_value = float(price.text)
                            price_type = price.get("Type")
                            prices_list.append((asset_id, price_date, price_type, price_value, price_currency, now, zip_file))
            try:
                os.remove(local_xml)
            except Exception:
                logger.error("File cannot be deleted", exc_info=True)
            cur = conn.cursor()
            try:
                execute_values(cur, 'INSERT INTO "LTSF"."Prices"("Asset_ID", "Price_Date", "Price_Type", "Price_Value", "Price_Currency", "Mod_Date", "Zip_File")   \
                           VALUES %s \
                           ON CONFLICT ("Asset_ID", "Price_Date", "Price_Type") \
                           DO UPDATE SET \
                           "Price_Value"=excluded."Price_Value", "Price_Currency"=excluded."Price_Currency", "Mod_Date"=excluded."Mod_Date", "Zip_File"=excluded."Zip_File"', prices_list)
            except Exception:
                logger.error("Problem upserting Prices", exc_info=True)
            conn.commit()
            cur.close()

Thank you "a_horse_with_no_name"!