Pandas to_sql Retry: Duplicate Rows on Partial Commit

Answer to the Core Question

Yes – when you call pandas.DataFrame.to_sql with a SQLAlchemy engine, the library starts a new transaction for that call and commits it automatically on success (or rolls it back on exception). There is no built‑in retry or idempotent flag in pandas, so the only guaranteed way to avoid duplicate rows on retry is to enforce uniqueness on the target table (primary key or unique constraint) or to use a staging‑and‑deduplication strategy.

Why duplicates appear on retry

• Each to_sql call runs inside its own transaction; a failed write does not leave a partially committed state, but the next call will insert the same rows again.
• Without a unique constraint, the database cannot detect that the rows already exist, so the second insert succeeds and duplicates appear.
• pandas does not expose a parameter to make append idempotent; the only reliable protection is the database’s own uniqueness enforcement or custom dedup logic.

Confirmed Behavior

Running a small experiment (DataFrame with duplicate rows, to_sql(..., if_exists='append') called twice) shows that the table ends up with double the rows unless a unique constraint is defined. Enabling engine.echo=True confirms that each call emits a BEGIN and COMMIT pair.

Practical Steps to Avoid Duplicates When No Unique Key Exists

1. Use a staging table
   Create a temporary table that mirrors the target schema but is not subject to the same business rules.
2. Load the DataFrame into the staging table
   Call to_sql(..., if_exists='replace', index=False) on the staging table. Because the staging table is fresh each run, you avoid partial data from earlier failures.
3. Deduplicate in SQL
   Run a single INSERT … SELECT DISTINCT … (or INSERT … ON CONFLICT DO NOTHING if your backend supports it) that pulls unique rows from the staging table into the final table.
4. Wrap in a transaction (optional)
   If you want to guarantee that the deduplication itself is atomic, wrap the INSERT SELECT in a session.begin() block of SQLAlchemy.
5. Clean up
   Drop the staging table or truncate it for the next run.

Alternative: Upsert Semantics

When your database supports upsert (PostgreSQL’s ON CONFLICT DO NOTHING, MySQL’s INSERT … ON DUPLICATE KEY UPDATE, SQL Server’s MERGE), you can pass a custom insert statement to to_sql via the method parameter or use SQLAlchemy’s core insert(...).prefix_with(...). This makes the operation idempotent without a unique key, but you must still define a unique index that the upsert can reference.

Diagnostic Question

Which database backend are you using (PostgreSQL, MySQL, SQLite, SQL Server, etc.)? The exact upsert or deduplication syntax depends on the backend, and knowing it will let us fine‑tune the SQL snippets above.