Pandas to_sql Retry: Duplicate Rows on Partial Commit

Symptom

When a DataFrame write to an external SQL table is retried after a failure, the same rows appear multiple times. This occurs even when using if_exists='append' and no explicit transaction boundaries are set in the calling code.

Constraints & Uncertainty

• pandas' to_sql does not expose a built-in retry mechanism.
• The library relies on the underlying SQLAlchemy engine for transaction handling, but it is unclear whether each to_sql call starts a new transaction that is automatically committed.
• There is no flag to deduplicate rows on retry; unique constraints on the target table are the only guaranteed protection.

Unresolved Questions

• Does pandas' to_sql always initiate a transaction for each call, and is that transaction committed automatically upon successful completion?
• Is there an undocumented or future parameter that would make append idempotent without requiring a unique constraint on the destination table?
• What programmatic strategies can reliably detect an incomplete write and prevent duplicate rows on retry when the target table lacks a unique key?