If you’re targeting PostgreSQL, you can do an UPSERT (merge) on two unique keys by using a custom insert method with pandas’

to_sql

that issues an

INSERT ... ON CONFLICT (...) DO UPDATE

. High-level steps: - Ensure your table has a composite unique constraint on the two keys. - Pass a callable via

method=

to

to_sql

that builds a PostgreSQL insert with

on_conflict_do_update

. Example:

# 1) Make sure your table has a composite unique constraint on the two keys
# Example (run once):
# ALTER TABLE marketplaces.amazon_transactions_breakdowns
#   ADD CONSTRAINT amazon_transactions_breakdowns_uq UNIQUE (key1, key2);

from sqlalchemy import create_engine
from sqlalchemy.dialects.postgresql import insert
import pandas as pd

engine = create_engine("<postgresql+psycopg2://user:pass@host:5432/dbname>")

def upsert_on_two_keys(table, conn, keys, data_iter):
    # table is a pandas SQLTable wrapper; table.table is the SQLAlchemy Table
    target = table.table
    rows = [dict(zip(keys, row)) for row in data_iter]

    if not rows:
        return

    stmt = insert(target).values(rows)

    # Replace these with your actual unique key column names
    conflict_cols = ['key1', 'key2']

    # Update all columns except the conflict keys
    update_cols = {
        col.name: getattr(stmt.excluded, col.name)
        for col in target.columns
        if col.name not in conflict_cols
    }

    upsert_stmt = stmt.on_conflict_do_update(
        index_elements=conflict_cols,
        set_=update_cols
    )
    conn.execute(upsert_stmt)

# Your dataframe
# df = pd.DataFrame([transaction_breakdowns])

df.to_sql(
    name="amazon_transactions_breakdowns",
    con=engine,               # use an SQLAlchemy Engine/Connection
    schema="marketplaces",
    index=False,
    if_exists="append",
    method=upsert_on_two_keys
)

Notes: - Replace

key1

and

key2

with your actual unique key column names (e.g.,

order_id

,

line_id

). - The composite unique constraint must exist on the table or the

ON CONFLICT (key1, key2)

will fail. - This approach works with pandas’

to_sql(method=callable)

plus SQLAlchemy’s PostgreSQL dialect. References: - pandas.DataFrame.to_sql - SQLAlchemy: INSERT…ON CONFLICT (upsert) If you share your two unique key column names, I can tailor the snippet exactly to your schema.