Как сделать update таблицы в БД массивом?

Попробую набросать логику без SQLAlchemy

Подготовим тестовую схему

create table public.my_test_data
(
    id            integer           not null
        primary key,
    name          varchar,
    some_property varchar,
    price         numeric default 0 not null
);

create table public.test_data_price_history
(
    item_id   integer
        references public.my_test_data
            -- Так себе отсылка, но пришлось как-то обыгрывать ваше желание удалять записи
            -- при условии сохранения истории цены
            on update cascade on delete set null,
    old_value numeric,
    new_value numeric,
    datetime  timestamp default (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'::text)
);

create function price_history_update() returns trigger
as
$$
begin
    if tg_op = 'INSERT' then
        insert into test_data_price_history (item_id, new_value) values (new.id, new.price);
    elseif tg_op = 'UPDATE' then
        insert into test_data_price_history (item_id, old_value, new_value) values (new.id, old.price, new.price);
    end if;
    return new;
end;
$$ language plpgsql;


create trigger price_history_new_item
    after insert
    on public.my_test_data
    for each row
execute procedure public.price_history_update();

create trigger price_history_upd_item
    after update
    on public.my_test_data
    for each row
    when (new.price IS DISTINCT FROM old.price)
execute procedure public.price_history_update();

create type incoming_json as
(
    id            int,
    name          varchar,
    some_property varchar,
    price         numeric
);

Теперь собственно Python

import pandas as pd
import psycopg2 as pg

df1 = pd.DataFrame(
    {
        'id': [1, 2, 3, 4, 5],
        'name': ['N1', 'N2', 'N3', 'N4', 'N5'],
        'some_property': ['A', 'B', 'C', 'D', 'A\'E'],
        'price': [45.62, 41.13, 125.04, 16.08, 11.16]
     }
)


def upsert(df: pd.DataFrame):
    payload = df.to_json(orient='records')

    with pg.connect('user=me dbname=me host=192.168.150.1') as conn:
        with conn.cursor() as cur:
            try:
                cur.execute(
                    """
    with recursive data as (select * from jsonb_populate_recordset(null::incoming_json, %s::jsonb)),
                   _ as (
                       insert into my_test_data select * from data on conflict (id)
                           do update set name = excluded.name,
                               some_property = excluded.some_property,
                               price = excluded.price)
    delete
    from my_test_data mtd
    where not exists(select 1
                     from data d
                     where d.id = mtd.id)

                    """,
                    [payload]
                )
            except Exception:
                cur.connection.rollback()
                raise
            else:
                cur.connection.commit()

upsert(df1)

my_test_data

test_data_price_history

df2 = pd.DataFrame(
    {
        'id': [1, 2, 3, 4, 6],
        'name': ['N1', 'N2', 'N3', 'N4', 'N6'],
        'some_property': ['A', 'B', 'C', 'D', 'B\'E'],
        'price': [45.62, 41.13, 113.03, 16.08, 88.0]
     }
)
upsert(df2)

my_test_data

test_data_price_history