When pandas DataFrame.to_sql loads a nightly result into an existing database table, what do if_exists and index default to, and what goes wrong?
answer
- the default refuses existing tables
- replace means drop and recreate
- an extra column you did not ask for
- new 3.0 option keeps the table
basics
~10 sif_exists defaults to 'fail', so an existing table raises ValueError; index defaults to True, so the DataFrame index is written as an extra column. Use 'append', or in pandas 3.0 'delete_rows', with index=False.
solid answer
~40 s`to_sql` defaults to `if_exists='fail'`, which raises `ValueError` when the table exists, and to `index=True`, which writes the DataFrame index as a column (named `index` when unnamed). `'append'` adds rows; `'replace'` drops the table and recreates it from pandas dtypes, losing the table's own column types, keys and constraints; and in pandas 3.0 `'delete_rows'` empties the table but keeps its definition. The index default is the other trap: appending to a table without an `index` column fails, and `reset_index(drop=True)` does not fix it because a `RangeIndex` is still written. So a nightly load is `df.to_sql('daily_totals', engine, if_exists='append', index=False)`, or `'delete_rows'` for a snapshot table.
code
python · 17 linesimport sqlite3
import pandas as pd
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE daily_totals (day TEXT PRIMARY KEY, revenue REAL)")
df = pd.DataFrame({"day": ["2026-10-09", "2026-10-10"], "revenue": [910.0, 1040.5]})
try:
df.to_sql("daily_totals", conn, if_exists="append") # index=True by default
except pd.errors.DatabaseError as exc:
print("failed:", exc) # the table has no column named index
print(df.to_sql("daily_totals", conn, if_exists="append", index=False)) # 2
print(df.to_sql("daily_totals", conn, if_exists="delete_rows", index=False)) # 2
print(pd.read_sql("SELECT COUNT(*) AS n FROM daily_totals", conn)) # n = 2go deeper
Recall that if_exists defaults to 'fail' and that index=False keeps the row labels out of the table.
Explain all four if_exists values, including pandas 3.0's 'delete_rows', and why index=True breaks appends to a table without an index column.
Argue for creating the table with explicit DDL and never letting 'replace' rebuild it from dtypes, and say what the returned rowcount can and cannot prove.
Set the team rule: the database owns table schemas, pandas only appends or deletes rows, and every to_sql call states if_exists and index explicitly.
## What `to_sql` does `DataFrame.to_sql(name, con, *, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)` writes the rows of a DataFrame into a database table and returns the number of rows affected (or `None` when a custom `method` callable returns nothing). `con` is a SQLAlchemy engine or connection, an ADBC connection, or, as a legacy path, a `sqlite3.Connection`. If the table does not exist, pandas **creates** it, choosing column types from the DataFrame's dtypes; if it does exist, `if_exists` decides what happens. ## The four `if_exists` values | value | table exists | table missing | |---|---|---| | `'fail'` (default) | raises `ValueError` ("Table ... already exists") | creates it and inserts | | `'append'` | inserts the new rows after the old ones | creates it and inserts | | `'replace'` | **drops** the table, recreates it from the DataFrame's dtypes, inserts | creates it and inserts | | `'delete_rows'` | deletes all existing rows, keeps the table, inserts | creates it and inserts | `'delete_rows'` is **new in pandas 3.0**. Before it, the only `if_exists` value that refreshed a table's contents was `'replace'`, and that is the trap in a nightly load: - A table someone created deliberately carries column types, a primary key, constraints, defaults and permissions. - `'replace'` drops it and lets pandas create a new one from dtypes alone, so a `VARCHAR(10)` can become a generic text type, the primary key disappears, and dependent grants or views may break. - `'delete_rows'` empties the table and keeps its definition, so the database still enforces its own schema on the new rows. For an append-only fact table, `'append'` is the right choice; for a "latest snapshot" table, `'delete_rows'` on pandas 3.0. ## The `index=True` default `index` defaults to **`True`**: the DataFrame's index is written as a column, named after the index (or `index` when the index has no name, unless `index_label` is given), and the docstring adds that pandas **creates a database index** on it when it creates the table. Consequences: 1. For a frame with a meaningless `RangeIndex`, the table gains a column of row numbers `0..n-1` that nobody asked for. 2. When appending to a table that was created without that column, the insert fails because the target has no column named `index`. With the `sqlite3` fallback pandas raises `pandas.errors.DatabaseError`; through SQLAlchemy you see the driver's error. 3. `reset_index(drop=True)` does not help: the new `RangeIndex` is still an index and is still written. Only `index=False` stops it. When the index is meaningful (a `store_id` set with `set_index`), keep `index=True` and name it with `index_label`, or `reset_index()` so it is an ordinary column. ## A nightly load done safely ```python df.to_sql( "daily_totals", engine, if_exists="append", # or "delete_rows" for a snapshot table index=False, ) ``` Practical habits that go with it: - Create the target table with explicit DDL once, then only ever `append` or `delete_rows` into it, so the database, not pandas' dtype guesses, owns the schema. - Use `dtype={...}` with SQLAlchemy types when pandas must create a table and its default mapping (for example text for string columns) is not what you want. - Remember that a column of `category` dtype is written as its plain values, so reading the table back does not give a categorical. - With a SQLAlchemy engine, pandas opens a connection and wraps the write in a transaction; with a connection already inside a transaction, pandas does not commit for you. ## How pandas picks column types when it creates the table When `to_sql` has to create the table (first run of `'append'`, or any `'replace'`), it maps dtypes to SQL types: integers to an integer type, floats to a floating type, booleans to a boolean where the database has one, datetimes to a timestamp type, and text to a text type. A few consequences worth knowing: - Time-zone-aware datetimes are written as a timestamp with time zone where the database supports it; otherwise they are stored as naive local timestamps. - `timedelta64` columns are written as integer nanoseconds with a warning; the user guide notes one ADBC driver as the exception, writing an interval type instead. - The `dtype` argument takes a dict of column name to SQLAlchemy type (or a string type name for the `sqlite3` fallback) and overrides any of these choices. ## Reading the return value `to_sql` returns the sum of the driver's `rowcount` values. The docstring warns that this may not reflect the exact number of written rows for every driver, so use it as a log line, not as a reconciliation check; count the table if the number matters.
- When does if_exists='replace' still make sense?When pandas owns the table entirely: a scratch or staging table that is rebuilt from the DataFrame each run, with no keys, grants or views depending on it. Even then, pass `dtype=` with SQLAlchemy types if the default mapping from pandas dtypes is not what readers expect.
- The DataFrame's index is a meaningful store_id. How do you write it?Keep `index=True` and pass `index_label='store_id'` so the column gets the right name, or call `reset_index()` so `store_id` becomes an ordinary column and write with `index=False`. Either way the target table needs a matching column when you append.
- What happens with if_exists='append' when the table does not exist yet?pandas creates it, choosing column types from the DataFrame's dtypes, and then inserts. That is convenient on the first run but means the table's schema came from pandas' guesses, so teams usually create the table with explicit DDL first.
- Says to_sql appends to an existing table by default
- Uses if_exists='replace' as a harmless refresh for a table with keys and grants
- Calls reset_index(drop=True) to stop the index being written as a column
- Believes index defaults to False in to_sql as it would in a CSV export
- Thinks delete_rows drops and recreates the table like replace
Assumes pandas 3.0: if_exists='delete_rows' was added in 3.0; on 2.x the choices are 'fail', 'replace' and 'append'.