Pandas to_sql(): Write and Verify SQLite Tables

Write Pandas DataFrames to SQLite with table policies, SQL types, batches and constraints. Practise the student Excel workflow in Colab.

Pandas DataFrame to SQLite table in Colab

to_sql(): Data from Pandas DataFrame to SQLite database table #B05

Show Table of Contents ↓

Create the student DataFrame and SQLAlchemy engine 🔝

Use the original 35-student sample, embedded in the notebook. The examples need Pandas and SQLAlchemy. Each student_engine() call creates an independent in-memory database, writes student records and returns the engine. Dispose it when finished.

import pandas as pd
import tempfile
from pathlib import Path
from io import StringIO
from sqlalchemy import create_engine, text, inspect
from sqlalchemy.types import Integer, String, DateTime
student_csv = 'id,name,class,mark,gender\n1,John Deo,Four,75,female\n2,Max Ruin,Three,85,male\n3,Arnold,Three,55,male\n4,Krish Star,Four,60,female\n5,John Mike,Four,60,female\n6,Alex John,Four,55,male\n7,My John Rob,Fifth,78,male\n8,Asruid,Five,85,male\n9,Tes Qry,Six,78,male\n10,Big John,Four,55,female\n11,Ronald,Six,89,female\n12,Recky,Six,94,female\n13,Kty,Seven,88,female\n14,Bigy,Seven,88,female\n15,Tade Row,Four,88,male\n16,Gimmy,Four,88,male\n17,Tumyu,Six,54,male\n18,Honny,Five,75,male\n19,Tinny,Nine,18,male\n20,Jackly,Nine,65,female\n21,Babby John,Four,69,female\n22,Reggid,Seven,55,female\n23,Herod,Eight,79,male\n24,Tiddy Now,Seven,78,male\n25,Giff Tow,Seven,88,male\n26,Crelea,Seven,79,male\n27,Big Nose,Three,81,female\n28,Rojj Base,Seven,86,female\n29,Tess Played,Seven,55,male\n30,Reppy Red,Six,79,female\n31,Marry Toeey,Four,88,male\n32,Binn Rott,Seven,90,female\n33,Kenn Rein,Six,96,female\n34,Gain Toe,Seven,69,male\n35,Rows Noump,Six,88,female\n'
students = pd.read_csv(StringIO(student_csv))
def student_engine():
    engine = create_engine("sqlite:///:memory:")
    with engine.begin() as connection:
        students.to_sql("student", connection, index=False, if_exists="fail")
    return engine
print(students.head())
print("Student rows:", len(students))
assert len(students) == 35

Expected output

   id        name  class  mark  gender
0   1    John Deo   Four    75  female
1   2    Max Ruin  Three    85    male
2   3      Arnold  Three    55    male
3   4  Krish Star   Four    60  female
4   5   John Mike   Four    60  female
Student rows: 35

Follow the original Excel and Colab workflow 🔝

To follow the video, upload student.xlsx through the Colab Files panel and use pd.read_excel("student.xlsx"). The portable example below creates a workbook and reads it before writing to SQLite. openpyxl is required. Session files are temporary, so download or save database files you need to retain. See read_excel().

with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
    workbook = Path(folder) / "student.xlsx"
    students.to_excel(workbook, index=False, engine="openpyxl")
    imported = pd.read_excel(workbook, engine="openpyxl")
    engine = create_engine("sqlite:///:memory:")
    try:
        with engine.begin() as connection:
            imported.to_sql("student", connection, index=False)
        with engine.connect() as connection:
            checked = pd.read_sql(text("SELECT * FROM student ORDER BY id"), connection)
        pd.testing.assert_frame_equal(checked, students)
        print("Excel to database rows:", len(checked))
    finally:
        engine.dispose()

Expected output

Excel to database rows: 35

List tables and inspect written records 🔝

Retain table discovery, row display and the mark > 80 plus class Five query. Use SQLAlchemy text() for SQL statements and bind changing data values. See read_sql().

engine = student_engine()
try:
    print("Tables:", inspect(engine).get_table_names())
    with engine.connect() as connection:
        print(connection.execute(text("SELECT id, name, mark FROM student ORDER BY id LIMIT 3")).fetchall())
        result = pd.read_sql(text("SELECT * FROM student WHERE mark>:mark AND class=:class ORDER BY id"),
            connection, params={"mark": 80, "class": "Five"})
    print(result)
    assert result["id"].tolist() == [8]
finally:
    engine.dispose()

Expected output

Tables: ['student']
[(1, 'John Deo', 75), (2, 'Max Ruin', 85), (3, 'Arnold', 55)]
   id    name class  mark gender
0   8  Asruid  Five    85   male

Write a SQLite database file and reopen it 🔝

An in-memory database ends with the engine. For a file use a sqlite:/// URL with the destination path. The temporary example demonstrates persistence after disposal without overwriting a real database.

with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
    destination = Path(folder) / "my_data.db"
    engine = create_engine("sqlite:///" + destination.as_posix())
    try:
        with engine.begin() as connection:
            students.to_sql("student", connection, index=False)
    finally:
        engine.dispose()
    reopened = create_engine("sqlite:///" + destination.as_posix())
    try:
        with reopened.connect() as connection:
            count = connection.execute(text("SELECT COUNT(*) FROM student")).scalar_one()
        print("Reopened rows:", count)
        assert count == 35
    finally:
        reopened.dispose()

Expected output

Reopened rows: 35

Choose fail, append or replace deliberately 🔝

if_exists defaults to fail. append inserts records into an existing table; it is not an upsert. replace drops and recreates the table, which can remove constraints and indexes. These operations run only on new practice databases. Pandas 3.0 also offers delete_rows, which deletes records while retaining a table; verify version support before using it.

engine = create_engine("sqlite:///:memory:")
try:
    with engine.begin() as connection:
        students.head(2).to_sql("student", connection, index=False)
    try:
        with engine.begin() as connection:
            students.head(1).to_sql("student", connection, index=False)
    except ValueError:
        print("Default fail prevented a second table creation")
    else:
        raise AssertionError("Expected the existing-table error")
    with engine.begin() as connection:
        students.iloc[2:3].to_sql("student", connection, index=False, if_exists="append")
        assert connection.execute(text("SELECT COUNT(*) FROM student")).scalar_one() == 3
    with engine.begin() as connection:
        students.head(1).to_sql("student", connection, index=False, if_exists="replace")
        assert connection.execute(text("SELECT COUNT(*) FROM student")).scalar_one() == 1
    print("Append and replace row counts checked")
finally:
    engine.dispose()

Expected output

Default fail prevented a second table creation
Append and replace row counts checked

Control index and index_label 🔝

index=True writes row labels as a column and creates a SQL index; it does not establish a primary key. Keep index=False for disposable row numbering. Use index_label when labels carry useful meaning.

engine = create_engine("sqlite:///:memory:")
try:
    labeled = students.head(2).set_index("id")
    with engine.begin() as connection:
        labeled.to_sql("labeled", connection, index=True, index_label="student_id")
    with engine.connect() as connection:
        restored = pd.read_sql(text("SELECT * FROM labeled ORDER BY student_id"), connection, index_col="student_id")
    print(restored)
    pd.testing.assert_frame_equal(restored, labeled.rename_axis("student_id"))
finally:
    engine.dispose()

Expected output

                name  class  mark  gender
student_id                               
1           John Deo   Four    75  female
2           Max Ruin  Three    85    male

Specify SQL types for missing and text values 🔝

Use SQLAlchemy type objects with an engine. The synthetic order IDs are text, units are nullable integers and dates are timestamps. SQL types are not a complete Pandas dtype schema; declare appropriate types when reading back.

orders = pd.DataFrame({"id": ["001", "002"],
    "units": pd.array([2, None], dtype="Int64"),
    "ordered": pd.to_datetime(["2026-01-02", "2026-01-03"])})
engine = create_engine("sqlite:///:memory:")
try:
    with engine.begin() as connection:
        orders.to_sql("orders", connection, index=False,
            dtype={"id": String(3), "units": Integer(), "ordered": DateTime()})
    with engine.connect() as connection:
        restored = pd.read_sql(text("SELECT * FROM orders ORDER BY id"), connection,
            dtype={"id": "string", "units": "Int64"}, parse_dates=["ordered"])
    print(restored)
    assert restored["id"].tolist() == ["001", "002"]
    assert restored["units"].isna().sum() == 1
finally:
    engine.dispose()

Expected output

    id  units    ordered
0  001      2 2026-01-02
1  002   <NA> 2026-01-03

Insert in batches and verify the database 🔝

chunksize sets the write batch size; method="multi" uses multi-value inserts where supported. Driver parameter limits and database behavior affect suitable sizes. Returned row counts are driver-dependent, so verify the saved data with SELECT.

engine = create_engine("sqlite:///:memory:")
try:
    with engine.begin() as connection:
        students.to_sql("student", connection, index=False, chunksize=10, method="multi")
    with engine.connect() as connection:
        summary = pd.read_sql(text("SELECT COUNT(*) AS rows, SUM(mark) AS total_mark FROM student"), connection)
    print(summary)
    assert summary.loc[0, "rows"] == 35
    assert summary.loc[0, "total_mark"] == students["mark"].sum()
finally:
    engine.dispose()

Expected output

   rows  total_mark
0    35        2613

Create primary keys explicitly and handle duplicate inserts 🔝

Create a constrained table separately, then append through to_sql(). The managed transaction rolls back a failed insertion in this SQLite/SQLAlchemy example. Raw sqlite3 to_sql writes have different transaction limitations. Use a database-specific upsert when that is your intended behavior.

from sqlalchemy.exc import IntegrityError
engine = create_engine("sqlite:///:memory:")
try:
    with engine.begin() as connection:
        connection.execute(text("CREATE TABLE marks (id INTEGER PRIMARY KEY, name TEXT NOT NULL)"))
        students.head(2)[["id", "name"]].to_sql("marks", connection, index=False, if_exists="append")
    try:
        with engine.begin() as connection:
            students.head(1)[["id", "name"]].to_sql("marks", connection, index=False, if_exists="append")
    except (IntegrityError, pd.errors.DatabaseError) as error:
        assert isinstance(error, IntegrityError) or isinstance(error.__cause__, IntegrityError)
        print("Duplicate primary key rejected")
    else:
        raise AssertionError("Expected duplicate-key rejection")
    with engine.connect() as connection:
        assert connection.execute(text("SELECT COUNT(*) FROM marks")).scalar_one() == 2
finally:
    engine.dispose()

Expected output

Duplicate primary key rejected

Questions and practical choices 🔝

How to save? df.to_sql(name, con) writes records.
Required arguments? A table name and compatible connection/engine.
Table name? Supply name explicitly; choose identifiers from a controlled list.
Default existing-table behavior? fail raises ValueError.
Types? Pass SQLAlchemy types through dtype; legacy sqlite3 uses type strings.
Append? Use if_exists="append" with matching columns.
Keys? Pre-create a schema with constraints; index=True is not a primary-key declaration.
Duplicate columns? Make column names unique before writing.
Batch size? chunksize and method control insertion strategy, subject to driver limits.
Errors? Use managed transactions and catch the relevant exception, then verify table state.

schema chooses a supported database schema, not a SQLite filename. Manage engine disposal and connection lifetimes. Treat replace as a schema-changing operation. Check version support for newer options. For MySQL export, see to_sql() with MySQL.

Exercise: save and check high-scoring Four students 🔝

Select class Four records with marks at least 80. Write them to a new table without an index and verify the IDs.

Exercise solution 🔝

Filter the DataFrame before writing, then inspect the saved records.

selected = students.loc[students["class"].eq("Four") & students["mark"].ge(80)].copy()
engine = create_engine("sqlite:///:memory:")
try:
    with engine.begin() as connection:
        selected.to_sql("selected", connection, index=False)
    with engine.connect() as connection:
        result = pd.read_sql(text("SELECT id, name, mark FROM selected ORDER BY id"), connection)
    print(result)
    assert result["id"].tolist() == [15, 16, 31]
finally:
    engine.dispose()

Expected output

   id         name  mark
0  15     Tade Row    88
1  16        Gimmy    88
2  31  Marry Toeey    88

Practice in Google Colab 🔝

Open in Google Colab View on GitHub

Run examples in order, inspect database records and try the exercise. Save a copy to keep edits.

Reference: Pandas to_sql() documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here