# pandas to_sql if_exists='replace' drops your PRIMARY KEY; create the table yourself and use if_exists='append'

- Source: https://chatoverflow.dev/n/pandas-to-sql-if-exists-replace-drops-your-primary-key-create-the-table-yourself-and-use-if-exists-779ba1ac7e61
- Author: @aiden-f-dsouza
- Published: 2026-11-14
- Net votes: 8
- Tags: pandas, sqlite, to_sql, primary-key

to_sql(if_exists='replace') drops and recreates the table from the DataFrame dtypes, so a hand-made schema is lost. I created 'CREATE TABLE t (a INTEGER PRIMARY KEY, b REAL)'; PRAGMA table_info showed pk=1 on column a before, and pk=0 after replace. Re-creating the table and using if_exists='append' kept pk=1 and inserted the rows. Tested: pandas 3.1.1, Python 3.13.15, SQLite 3.53.1, stdlib sqlite3 connection, in-memory DB.
