Skip to content

SQLite

The zero-server, zero-config SQL database that lives in a single file. SQLite is built into Python — perfect for local analysis, prototyping, caching cleaned datasets, and teaching SQL.

Table of Contents

Installation / Setup

Zero install — the sqlite3 module ships with Python. For the CLI, install separately (apt install sqlite3, brew install sqlite, or download from sqlite.org).

import sqlite3, pandas as pd

con = sqlite3.connect("analysis.db")       # file; ":memory:" = throwaway
cur = con.cursor()

sqlite3 analysis.db                        # CLI
sqlite3 analysis.db "SELECT COUNT(*) FROM orders;"   # one-shot

Core Syntax

Python workflow

con = sqlite3.connect("data.db")

# Write
con.execute("CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total REAL)")
con.executemany("INSERT INTO orders (total) VALUES (?)", [(10.5,), (20.0,)])
con.commit()                               # writes are transactional

# Read straight into pandas — the killer feature
df = pd.read_sql_query("SELECT * FROM orders", con)
df = pd.read_sql_query("SELECT total, COUNT(*) FROM orders GROUP BY total", con)

Data types (dynamic affinity)

SQLite type Stores
INTEGER ints (up to 8 bytes)
REAL floats
TEXT strings (any encoding)
BLOB binary
NUMERIC / DECIMAL affinity: try numeric, else text

SQLite is dynamically typed — you can store a string in an INTEGER column, so validate inputs yourself.

Useful pragmas

PRAGMA table_info(orders);          -- columns
PRAGMA index_list(orders);          -- indexes
PRAGMA foreign_keys = ON;           -- IMPORTANT: enable per connection!
PRAGMA journal_mode = WAL;          -- better concurrency for file DBs

Common Operations

Dialect quick wins

-- Date/time: no native DATE type — store TEXT ISO-8601 or Unix epoch
SELECT DATE('now'), datetime('now'), strftime('%Y-%m-%d', 'now');
SELECT strftime('%Y-%m', created_at) AS ym FROM orders;    -- month group key
SELECT julianday('2024-06-01') - julianday('2024-01-01');  -- 152.0 days
SELECT datetime(created_at, '+7 days') FROM orders;

-- Strings
SELECT upper(name), length(name), substr(name, 1, 3), trim(name) FROM users;
SELECT replace(email, '@x.com', '@y.com') FROM users;

-- No RIGHT/FULL JOIN, no ILIKE (use LIKE with lower()), 
-- LIMIT works everywhere incl. UPDATE/DELETE

Window functions (supported since 3.25)

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders;

Practical Examples

1. Cache a cleaned dataset to a local DB

import pandas as pd, sqlite3

df = pd.read_csv("raw.csv")
# ... cleaning ...
df = df.drop_duplicates().dropna(subset=["amount"])

with sqlite3.connect("clean.db") as con:        # context manager commits
    df.to_sql("orders", con, if_exists="replace", index=False)

# Next run: read only what you need
with sqlite3.connect("clean.db") as con:
    monthly = pd.read_sql_query("""
        SELECT strftime('%Y-%m', created_at) AS ym,
               SUM(amount) AS revenue, COUNT(*) AS n
        FROM orders GROUP BY ym ORDER BY ym""", con)

2. Quick answers without loading to pandas

con = sqlite3.connect("clean.db")
n = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
p95 = con.execute("""
    SELECT amount FROM orders
    ORDER BY amount LIMIT 1 OFFSET (SELECT CAST(COUNT(*) * 0.95 AS INT) FROM orders)
""").fetchone()

3. Index for speed on big local files

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_created ON orders(created_at);

Common Patterns

# Parameterized queries — never f-string interpolate SQL!
con.execute("SELECT * FROM users WHERE email = ?", (email,))

# Executemany for bulk inserts (fast)
con.executemany("INSERT INTO t (a, b) VALUES (?, ?)", rows)

# pandas round-trip with types preserved via dtype argument
df = pd.read_sql_query("SELECT * FROM t", con)

# In-memory DB for tests
con = sqlite3.connect(":memory:")

-- Upsert (SQLite 3.24+)
INSERT INTO users (id, name) VALUES (1, 'Ada')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

-- Find duplicates
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Best Practices

  • Use parameterized queries (?) — the only safe way to embed values.
  • Enable PRAGMA foreign_keys = ON per connection — it's off by default!
  • Prefer WAL journal mode for file databases read while writing.
  • Index the columns you filter/join; a full scan on a 10 GB file is painful.
  • Store timestamps as ISO-8601 TEXT (YYYY-MM-DD HH:MM:SS) — sorts and strftimes cleanly.
  • One connection per thread; use check_same_thread=False only when you must.
  • Commit in batches (executemany + single commit) — thousands of tiny commits are slow.
  • For concurrent writers or servers, move to PostgreSQL; SQLite is per-process friendly, not a network server.

Common Mistakes

Mistake Consequence Fix
f-string building SQL SQL injection / quote bugs ? placeholders
WHERE col = NULL No rows IS NULL
Foreign keys silently ignored Orphan rows PRAGMA foreign_keys = ON
Expecting RIGHT/FULL JOIN Syntax error Restructure or use subquery
ALTER TABLE beyond ADD COLUMN Unsupported Rebuild table (create-new-copy-drop)
Many con.commit() in a loop Very slow Batch inserts, commit once
Reading whole table when one column needed Memory waste Select only needed columns

Troubleshooting

Symptom Cause Fix
database is locked Concurrent writers WAL mode; retry; single writer
no such table Wrong file or in-memory gone Check path; same connection
sqlite3.OperationalError: near "?": syntax error Literal ? in SQL string Use placeholders only via execute args
Date column stored as float Inserted epoch seconds as REAL Convert with datetime(ts, 'unixepoch')
pandas reads dates as strings No native date type Parse in pandas: pd.to_datetime after read

Official Documentation

Last Updated

2026-09-06 — Initial version.