to_sql append mode and write idempotency limits

Direct answer

The most efficient way to make to_sql(if_exists='append') idempotent is to let the database reject or ignore duplicate rows via a unique constraint and an UPSERT (ON CONFLICT DO NOTHING / INSERT … ON DUPLICATE KEY UPDATE / MERGE). pandas itself does not provide this logic, so you must use a staging table and a single upsert statement.

Confirmed facts

• if_exists='append' issues plain INSERT statements; retries after a partial commit insert rows that were already committed, producing duplicates.
• The method='multi' flag only changes how executemany batches the inserts; it does not affect transaction boundaries or add deduplication.
• if_exists='replace' drops the table, losing indexes, constraints and data, so it cannot be used for retry‑safe incremental loads.

Steps for this case (PostgreSQL example)

1. Ensure the target table has a unique constraint (or primary key) on the columns that define a logical row identifier.
2. Write the incoming DataFrame to a temporary table:

   df.to_sql('temp_load', con=engine, if_exists='replace', index=False, method='multi')
   

3. Execute an upsert that inserts only rows not already present:

   upsert = text("""
       INSERT INTO target_table AS t
       SELECT * FROM temp_load
       ON CONFLICT (pk_col) DO NOTHING;
   """)
   with engine.begin() as conn:
       conn.execute(upsert)
   

4. Drop the temporary table:

   engine.execute(text("DROP TABLE temp_load"))
   

Dialect note

For MySQL replace the upsert with INSERT INTO target_table SELECT * FROM temp_load ON DUPLICATE KEY UPDATE pk_col=pk_col;. For SQL Server use a MERGE statement. Provide your dialect if you need the exact syntax.

Verification

After a simulated interruption, rerun the upsert block and query SELECT COUNT(*) FROM target_table;. The count should remain unchanged, confirming no duplicate rows were added.