skip to content

Parquet, SQL and Excel

to_parquet and read_parquet keep dtypes and read selected columns, read_sql and to_sql talk to databases, and Excel and JSON need options. Interviewers ask which format fits.

on this pageshow

questions

6

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?

level: middleimportance: must knowfreq 60%

answer

  1. the default refuses existing tables
  2. replace means drop and recreate
  3. an extra column you did not ask for
  4. new 3.0 option keeps the table

basics

~10 s

if_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 lines
python
import 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 = 2

go deeper

for a junior

Recall that if_exists defaults to 'fail' and that index=False keeps the row labels out of the table.

for a middle

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.

for a senior

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.

for a principal

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'.

open as a page

In pandas, how do you read every sheet of an Excel workbook, what does read_excel return, and how do you write several sheets back?

level: middleimportance: should knowfreq 50%

basics

~10 s

pd.read_excel(path, sheet_name=None) returns a dict of DataFrames keyed by sheet name; the default sheet_name=0 reads only the first sheet. To write several sheets, open one pd.ExcelWriter and call to_excel(writer, sheet_name=...) per frame.

open as a page

In pandas, how do you load a newline-delimited JSON event log with nested user objects and item arrays into flat DataFrames?

level: middleimportance: should knowfreq 55%

basics

~10 s

Read it with pd.read_json(path, lines=True), which keeps nested objects as dicts and arrays as lists, then flatten with pd.json_normalize: nested keys become dotted columns, and record_path with meta gives one row per array element.

open as a page

A nightly pandas job writes Parquet with to_parquet(partition_cols=['run_date']): how do you read one week of three columns with read_parquet?

level: seniorimportance: should knowfreq 45%

basics

~10 s

Call read_parquet on the dataset directory with columns= naming the three columns and filters=[('run_date', 'in', week)]: unread columns are never decoded, other days' directories are skipped, and the pyarrow engine also drops non-matching rows.

open as a page

A pandas to_sql load of two million rows through a SQLAlchemy engine is slow or hits packet limits: what do chunksize, method='multi' and an ADBC connection change?

level: seniorimportance: should knowfreq 40%

basics

~20 s

chunksize sets rows per batch (default: all at once); method='multi' packs each chunk into one multi-row INSERT, so chunksize must keep rows times columns under the database's parameter limit. An ADBC connection bulk-ingests through Arrow and rejects chunksize and method.

open as a page

A lead must standardise how a team's pandas jobs hand DataFrames to each other on disk: which writer do you choose, and what does that choice cost?

level: principalimportance: should knowfreq 35%

basics

~20 s

Standardise on to_parquet with the pyarrow engine and index=False for handoffs: it round-trips most pandas dtypes and supports column and filter pushdown. The cost is pinning PyArrow, enforcing string column names and no Python-object columns, and keeping CSV and Excel as edge exports.

open as a page