Pandas · Module

df.to_sql

Writes DataFrame records to a SQL database, creating, appending to, or replacing a table. It can control index storage, batching, column types, and insertion strategy.

How it works

Call this DataFrame method with a table name and a supported database connection. It creates, appends to, or replaces a table according to if_exists; index controls whether the DataFrame index becomes a column, while chunksize, dtype, and method tune batching, SQL types, and insertion behavior.

Syntax

DataFrame.to_sql(name, con, *, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Parameters

Name	Type	Description
`name`	`str`	Target table name.
`con`	`sqlalchemy.engine.Connection, Engine, or sqlite3.Connection`	Database connection or SQLAlchemy connectable.
`schema`	`str, optional`	Database schema, when supported; defaults to the backend schema.
`if_exists`	`{'fail', 'replace', 'append'}, default 'fail'`	Behavior if the table exists: 'fail', 'replace', or 'append'.
`index`	`bool, default True`	Whether to write the DataFrame index as a column.
`index_label`	`str or sequence, optional`	Column label for a written index.
`chunksize`	`int, optional`	Rows per database batch.
`dtype`	`dict or scalar, optional`	SQL type for columns, either one type or a column-to-type mapping.
`method`	`{None, 'multi', callable}, optional`	Insertion strategy: None, 'multi', or a callable.

Returns

int or None: the number of rows affected, if available; otherwise None.

Key points

The method can create a table, append records, or overwrite an existing table through if_exists.
schema selects the database schema where the table is created or accessed when the backend supports schemas.
When the index is written, index_label supplies its database column name.
dtype accepts a scalar type or per-column mapping to control SQL column types.
method='multi' groups multiple row values into one INSERT clause.

Common mistakes

The default fail mode raises ValueError when the target table already exists.
The default index=True writes the DataFrame index into the database, so pass index=False when it is not data.
Without chunksize, the method writes all rows in one batch, which may be unsuitable for large DataFrames.
replace is destructive: it drops the existing table before inserting the new records.

Example: Write a DataFrame to SQLite

import sqlite3
import pandas as pd

with sqlite3.connect(":memory:") as con:
    frame = pd.DataFrame({"name": ["Ada", "Linus"], "score": [10, 9]})
    written = frame.to_sql("scores", con, if_exists="replace", index=False)
    result = pd.read_sql_query("SELECT * FROM scores", con)
    print(written)
    print(result)

Creates a temporary SQLite table from the DataFrame, omits the DataFrame index, and reads the inserted rows back.

When to use it

Persist tabular results

Export a processed DataFrame into a relational database table for downstream applications.

Append incremental data

Load new records into an existing table without dropping prior data.

Refresh a table

Replace a staging table during a repeatable ETL or refresh workflow.

Tune database loading

Use explicit SQL types and batches when the database schema or data volume requires control.

Interview questions

QWhat does DataFrame.to_sql do, and what does it return?

It writes DataFrame records to a SQL database and returns the number of rows affected when available. SQLAlchemy-supported databases can be used, as can sqlite3 connections.

Follow-up: How would you avoid persisting the index? Pass index=False, or provide index_label when the index should be stored under an explicit column name.

QHow does if_exists affect an existing table?

if_exists controls an existing table: fail raises ValueError, replace drops and recreates it, and append inserts into it. The default is fail.

Follow-up: How would you preserve an existing table while adding records? Use if_exists='append' and ensure the DataFrame columns match the target schema.

QHow can insertion performance and SQL typing be controlled?

chunksize limits the number of rows sent per batch. method controls insertion: None uses standard INSERT statements, 'multi' sends multiple values in one INSERT clause, and a callable can customize insertion.

Follow-up: When might chunksize and method='multi' help? They can reduce per-row insertion overhead and bound batch size, subject to database and driver support.

pandas.read_sql
pandas.read_sql_query
pandas.read_sql_table
pandas.DataFrame.to_records

Source: pandas.DataFrame.to_sql. Checked 2026-10-15.