Upsert Excel Rows into a Database Table with Python

A product list, price table or cost-centre mapping often lives in an Excel sheet that someone maintains, while applications read the same data from a database table. Loading the sheet once with to_sql is easy; loading it again next week is where it goes wrong. if_exists="append" duplicates every row or fails on the primary key, and if_exists="replace" drops the table — along with its indexes, constraints and any rows added by other systems. What you want is an upsert: insert rows with new keys, update rows whose values changed, leave the rest alone. This guide, part of Moving Data Between Excel and Databases, shows two ways to do it with pandas and SQLAlchemy, using SQLite so every example runs as written.

Prerequisites

pip install pandas openpyxl sqlalchemy

An existing table with a primary key, and the maintained sheet:

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("sqlite:///products.db")
with engine.begin() as con:
    con.exec_driver_sql("DROP TABLE IF EXISTS products")
    con.exec_driver_sql("""CREATE TABLE products (
        sku TEXT PRIMARY KEY, name TEXT NOT NULL, price REAL, updated_at TEXT)""")
    con.exec_driver_sql("""INSERT INTO products VALUES
        ('A-1', 'Tea', 2.5, '2026-09-01'), ('B-2', 'Coffee', 4.0, '2026-09-01'),
        ('C-3', 'Cocoa', 3.0, '2026-09-01')""")

pd.DataFrame({"sku": ["A-1", "B-2", "D-4"], "name": ["Tea", "Coffee beans", "Mate"],
              "price": [2.5, 4.2, 3.8]}).to_excel("products.xlsx", index=False)
sheet = pd.read_excel("products.xlsx", dtype={"sku": str})

The table must have a primary key or unique constraint on the key column; the database uses it to decide whether a row is new. Creating tables from Excel data in the first place is covered in Load an Excel File into a SQL Database with pandas.

Step 1 — Check the sheet before touching the table

An upsert trusts the key. Duplicate or blank keys in the sheet either make the statement fail halfway or let the last duplicate silently win, depending on the database. Check first:

problems = []
if sheet["sku"].isna().any():
    problems.append(f"{sheet['sku'].isna().sum()} rows without a sku")
dupes = sheet.loc[sheet["sku"].duplicated(keep=False), "sku"].unique().tolist()
if dupes:
    problems.append(f"duplicate skus: {dupes}")
if problems:
    raise ValueError("; ".join(problems))
sheet["sku"] = sheet["sku"].str.strip()
print(len(sheet), "rows ready")          # 3 rows ready

PostgreSQL rejects a statement that would update the same row twice ("ON CONFLICT DO UPDATE command cannot affect row a second time"); SQLite applies the duplicates one after another, so the last one wins without a warning. Catching duplicates in Python gives the same, clear failure on every database.

Step 2 — Stage the sheet and upsert in one statement

Write the sheet into a staging table, then merge it into the real table with a single INSERT … ON CONFLICT DO UPDATE. Everything runs in one transaction, so a failure leaves the table as it was:

UPSERT = """
INSERT INTO products (sku, name, price, updated_at)
SELECT sku, name, price, :today FROM stage_products WHERE true
ON CONFLICT (sku) DO UPDATE SET
    name = excluded.name,
    price = excluded.price,
    updated_at = excluded.updated_at
WHERE products.name IS NOT excluded.name OR products.price IS NOT excluded.price
"""

with engine.begin() as con:
    sheet.to_sql("stage_products", con, if_exists="replace", index=False)
    changed = con.execute(sa.text(UPSERT), {"today": "2026-09-23"}).rowcount
    con.exec_driver_sql("DROP TABLE stage_products")
print(changed)                                   # 2
print(pd.read_sql("SELECT * FROM products ORDER BY sku", engine))

excluded refers to the incoming row. The WHERE on the update compares old and new values with IS NOT, which treats two NULLs as equal, so A-1 — unchanged — is skipped and keeps its original updated_at. B-2 is updated and D-4 inserted: two rows changed. C-3, which is not in the sheet, is untouched. The odd-looking WHERE true after the SELECT is required by SQLite to tell its parser that ON CONFLICT belongs to the insert, not to a join; PostgreSQL accepts it too.

Step 3 — The same SQL on other databases

The staging pattern works everywhere; only the merge statement changes. PostgreSQL uses the same ON CONFLICT … DO UPDATE syntax as SQLite, with one difference in the change check: SQLite's null-safe IS NOT is written IS DISTINCT FROM in PostgreSQL, as in WHERE products.price IS DISTINCT FROM excluded.price. MySQL and MariaDB use INSERT … ON DUPLICATE KEY UPDATE, and SQL Server uses MERGE:

-- MySQL / MariaDB
INSERT INTO products (sku, name, price)
SELECT sku, name, price FROM stage_products
ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price);

-- SQL Server
MERGE products AS t
USING stage_products AS s ON t.sku = s.sku
WHEN MATCHED AND (t.name <> s.name OR t.price <> s.price)
    THEN UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

On server databases, create the staging table as a temporary table so concurrent jobs cannot collide, and give it the same column types as the target: letting to_sql guess types can turn a NUMERIC(10,2) price into a float.

Step 4 — A to_sql method for small loads

For a few hundred rows, you can skip the staging table and pass an upsert function to to_sql's method argument. pandas calls it with batches of rows; the function builds a dialect-specific insert:

from sqlalchemy.dialects.sqlite import insert

def upsert_rows(table, conn, keys, data_iter):
    rows = [dict(zip(keys, row)) for row in data_iter]
    stmt = insert(table.table).values(rows)
    stmt = stmt.on_conflict_do_update(
        index_elements=["sku"],
        set_={col: stmt.excluded[col] for col in keys if col != "sku"},
    )
    return conn.execute(stmt).rowcount

update = pd.DataFrame({"sku": ["C-3"], "name": ["Cocoa"], "price": [3.2]})
with engine.begin() as con:
    update.to_sql("products", con, if_exists="append", index=False, method=upsert_rows)
print(pd.read_sql("SELECT price FROM products WHERE sku = 'C-3'", engine)["price"].item())   # 3.2

For PostgreSQL, import insert from sqlalchemy.dialects.postgresql instead; the rest is identical. This version updates every incoming row, changed or not, so prefer the staging statement when you care about accurate updated_at values or large volumes.

Step 5 — Decide what happens to rows removed from the sheet

An upsert never deletes. If someone removes C-3 from the sheet, it stays in the table. Whether that is right is a policy decision; either way, find those keys explicitly:

in_table = pd.read_sql("SELECT sku FROM products", engine)["sku"]
removed = sorted(set(in_table) - set(sheet["sku"]))
print(removed)                    # ['C-3']

Deleting them makes the table an exact mirror of the sheet, which is dangerous if other systems reference those rows or add their own. Marking them inactive — an active flag or a retired_at date — keeps history and references intact. Most teams choose the flag and report the removed keys so the sheet's owner can confirm the change.

Common pitfalls

• Counting rows as success. rowcount reports inserted plus updated rows. Log it alongside the sheet's row count and the number of removed keys, so a run that changed nothing — or everything — stands out in the log.
• Blank cells overwriting data. An empty price in the sheet becomes NULL and replaces a real price in the table. Decide whether blanks mean "unknown" or "keep the current value"; for the latter, use COALESCE(excluded.price, products.price) in the update.
• Using if_exists="replace" on the real table. It drops the table and recreates it with guessed types, losing keys, indexes and permissions.
• Keys that change format. "A-1 " with a trailing space is a new key to the database. Strip and normalise keys before staging.
• Numbers read as text or floats. IDs such as 00123 lose their zeros unless read with dtype=str; see Merge Excel Files with Different Column Names in pandas.

Conclusion

Loading a maintained Excel sheet into a database repeatedly needs an upsert, which pandas' to_sql does not provide. Validate the keys, write the sheet to a staging table, and merge it with one INSERT … ON CONFLICT DO UPDATE that skips unchanged rows — or MERGE and ON DUPLICATE KEY UPDATE on other databases. Use a to_sql method callable for small loads, and decide explicitly whether keys removed from the sheet are deleted or marked inactive.

Frequently asked questions

Does pandas to_sql support upserts? Not directly. if_exists="append" inserts every row and fails on primary key conflicts, and "replace" drops and recreates the table. Use a custom method callable or load into a staging table and run an INSERT … ON CONFLICT statement.

What is a staging table? A temporary table that receives the Excel rows unchanged. One SQL statement then merges it into the real table, which is fast, runs inside a transaction and keeps the upsert logic in SQL where the database can enforce it.

How do I update only rows that actually changed? Add a WHERE clause to the DO UPDATE part that compares the existing and incoming values — IS NOT in SQLite, IS DISTINCT FROM in PostgreSQL — so unchanged rows are skipped and their updated_at timestamps stay put.

What about rows deleted from the Excel sheet? An upsert never deletes. Find keys that are in the table but not in the sheet with a left join from the table to the staging table, then delete them or mark them inactive, depending on your policy.

Related

• Up one level: Moving Data Between Excel and Databases — Excel and SQL in both directions.
• Export SQL Query Results to Excel with Python — the reverse direction.
• Refresh an Excel Report from a Database on a Schedule — automating the round trip.
• Validate a pandas Merge to Avoid Duplicate Rows — the same key checks for joins.