Load Excel into SQLite with a Defined Schema

df.to_sql("orders", conn) gets Excel data into SQLite in one line, and for a quick look that is fine. For a database other code will rely on, the table it creates is weak: it has no primary key, so loading the same file twice doubles the data; dates become text in whatever form pandas chose; an integer column with one blank becomes a column of floats; and nothing stops a negative quantity or an order for a customer that does not exist. A database is only as useful as the rules it enforces, so the better approach is to define the tables yourself — types, keys, constraints — and load the spreadsheet into them, letting the database reject what does not fit. This page compares the table to_sql invents with a designed schema, creates SQLite STRICT tables with primary keys, NOT NULL, CHECK and foreign-key constraints, converts Excel's values deliberately, loads everything in one transaction, and reports each rejected row with the rule it broke. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas openpyxl

sqlite3 is in the standard library; STRICT tables need SQLite 3.37 or later, which current Python builds include. The workbook has a Customers sheet and an Orders sheet with three deliberate problems: an order for a customer not on the customer list, an order with a blank quantity and one with a negative quantity.

Step 1 — What to_sql invents

import sqlite3
import pandas as pd

orders = pd.read_excel("sales.xlsx", sheet_name="Orders")
quick = sqlite3.connect(":memory:")
orders.to_sql("orders", quick, index=False)
print(quick.execute("SELECT sql FROM sqlite_master WHERE name='orders'").fetchone()[0])
print(quick.execute("SELECT OrderDate, Qty FROM orders LIMIT 3").fetchall())
orders.to_sql("orders", quick, index=False, if_exists="append")
print("rows after loading twice:", quick.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
quick.close()

The generated table has a type per column and nothing else. OrderDate is stored as text with a time of midnight attached; Qty is REAL because the blank made pandas read the column as float, so 12 is stored as 12.0; there is no primary key, so loading the file a second time silently doubles the table. None of the three data problems is noticed. The database accepted everything because it was given no rules.

Step 2 — Design the schema first

SCHEMA = """
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
    customer_id TEXT PRIMARY KEY CHECK (length(customer_id) = 4),
    name        TEXT NOT NULL,
    country     TEXT NOT NULL CHECK (length(country) = 2)
) STRICT;
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id TEXT NOT NULL REFERENCES customers(customer_id),
    order_date  TEXT NOT NULL CHECK (order_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),
    qty         INTEGER NOT NULL CHECK (qty > 0),
    amount      REAL NOT NULL CHECK (amount >= 0)
) STRICT;
"""
db = sqlite3.connect("sales.db")
db.executescript("DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS customers;" + SCHEMA)
for (name,) in db.execute("SELECT name FROM sqlite_master WHERE type='table'"):
    print(name, [(c[1], c[2], "NOT NULL" if c[3] else "", "PK" if c[5] else "") for c in db.execute(f"PRAGMA table_info({name})")])

Every rule the data must follow is now part of the table: primary keys stop duplicate loads, NOT NULL rejects missing values, CHECK constraints reject impossible ones — a non-positive quantity, a date that is not in ISO form — and the foreign key ties each order to an existing customer. STRICT makes SQLite enforce the column types, which it otherwise treats as suggestions: a STRICT integer column refuses 'twelve' instead of storing it. PRAGMA foreign_keys = ON has to be set on every connection, because SQLite leaves foreign keys unenforced by default. Dates are stored as ISO text, which SQLite's date functions understand and which sorts correctly.

Step 3 — Convert values deliberately and load in one transaction

Convert each column to the type the schema expects, then insert inside a transaction. Insert row by row inside it, collecting the rows the database rejects, and commit only if there are none:

customers = pd.read_excel("sales.xlsx", sheet_name="Customers")

def to_rows(df, columns, convert):
    return [tuple(convert[c](v) for c, v in zip(columns, row)) for row in df[columns].itertuples(index=False)]

def nullable(f):
    return lambda v: None if pd.isna(v) else f(v)

cust_rows = to_rows(customers, ["CustomerID", "Name", "Country"], {"CustomerID": str, "Name": str, "Country": str})
order_rows = to_rows(orders, ["OrderID", "CustomerID", "OrderDate", "Qty", "Amount"],
                     {"OrderID": int, "CustomerID": str, "OrderDate": nullable(lambda d: d.date().isoformat()),
                      "Qty": nullable(int), "Amount": nullable(lambda a: round(float(a), 2))})

def load(db, table, rows):
    placeholders = ",".join("?" * len(rows[0]))
    rejected = []
    for row in rows:
        try:
            db.execute(f"INSERT INTO {table} VALUES ({placeholders})", row)
        except sqlite3.IntegrityError as e:
            rejected.append((row, str(e)))
    return rejected

with db:
    bad = load(db, "customers", cust_rows) + load(db, "orders", order_rows)
    if bad:
        db.rollback()
for row, err in bad:
    print(f"rejected {row[0]}: {err}")
print("orders in database:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])

Every value is converted by an explicit function: dates to ISO strings with the midnight time dropped, quantities to int with blanks turned into None so the NOT NULL rule can catch them, amounts rounded to pennies. Each insert either succeeds or raises IntegrityError naming the rule — FOREIGN KEY constraint failed for the order from customer C004, NOT NULL constraint failed: orders.qty for the blank, CHECK constraint failed: qty > 0 for the negative. Because rejected rows were found, the whole load is rolled back and the database is left exactly as it was: no half-loaded day of data. Choosing all-or-nothing makes reruns simple — fix the sheet and load again.

Step 4 — Fix, reload and verify

fixed = [r for r in order_rows if r[0] not in {row[0] for row, _ in bad}]
with db:
    assert not load(db, "customers", cust_rows) and not load(db, "orders", fixed)
print(db.execute("""SELECT c.name, COUNT(*), SUM(o.amount), MIN(o.order_date)
                    FROM orders o JOIN customers c USING (customer_id) GROUP BY c.name ORDER BY c.name""").fetchall())
try:
    with db:
        load_again = load(db, "orders", fixed)
        if load_again:
            raise sqlite3.IntegrityError(load_again[0][1])
except sqlite3.IntegrityError as e:
    print("loading the same orders again:", e)
db.close()

In a real process the three rejected orders would go back to whoever maintains the sheet; here they are simply left out, and the remaining three load cleanly. A join confirms the result — each customer's order count, total and first order date, with dates sorting correctly because they are ISO strings. Loading the same orders a second time now fails on the primary key, which is exactly the protection the inferred table lacked. Loads that should update existing rows rather than refuse them use an upsert, covered in Upsert Excel Rows into a Database Table with Python.

Keeping the schema with the code

Put the CREATE TABLE statements in a .sql file next to the loading script and in version control, rather than letting the first load decide the structure. Reviewers can then see the rules the data must meet, changes to them show up as diffs, and a fresh database can be built from the file in a test. When the spreadsheet gains a column, the load fails loudly until the schema is updated — an inconvenience that is far preferable to new data quietly not arriving. The same discipline carries over unchanged to PostgreSQL or SQL Server, where the constraint syntax is almost identical; SQLite is simply the quickest place to start and test.

Common pitfalls

• Letting to_sql create the table. You get guessed types and no rules.
• Forgetting PRAGMA foreign_keys = ON. SQLite ignores foreign keys without it.
• Non-STRICT tables. SQLite stores wrong types happily unless the table is STRICT.
• Blanks as NaN in integer columns. Convert to None so NOT NULL can catch them.
• Partial loads. Insert in one transaction and roll back on any rejected row.

Conclusion

Define SQLite tables yourself — STRICT, with primary keys, NOT NULL, CHECK and foreign-key constraints, and foreign keys switched on — then convert each Excel column explicitly and insert inside one transaction, collecting IntegrityErrors and committing only when no row was rejected. The database then refuses duplicates, blanks, impossible values and orphan records, and every rejection comes with the rule that caught it.

Frequently asked questions

How do I load an Excel sheet into SQLite with my own table schema? Create the table with CREATE TABLE first, then insert the rows with executemany or row by row, or use DataFrame.to_sql with if_exists="append" into the existing table.

Why does pandas to_sql store my integers as REAL? A blank cell makes pandas read the column as float; convert the column explicitly and turn blanks into None before inserting.

How do I make SQLite enforce column types? Declare the table as STRICT (SQLite 3.37 or later), so values of the wrong type are rejected.

How do I see which Excel rows broke a constraint? Insert rows individually inside one transaction, catch sqlite3.IntegrityError for each, record the row and message, and roll back if any were rejected.

Related

• Up one level: Moving Data Between Excel and Databases — Excel and SQL.
• Load an Excel File into a SQL Database with pandas — the quick approach.
• Compare an Excel File with a Database Table — checking what was loaded.
• Validate Excel Data with pandera Schemas — validating before the database.