Python SQLite3: Complete Guide with Real Examples

SQLite3 is Python's built-in relational database — zero installation, zero configuration, works everywhere. It's perfect for local apps, prototypes, small-to-medium datasets, and CLI tools.

Installation

No installation needed. sqlite3 is part of Python's standard library:

import sqlite3

Create a Database and Table

import sqlite3
from pathlib import Path

DB_PATH = Path("library.db")

def get_connection(path: Path = DB_PATH) -> sqlite3.Connection:
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row  # access columns by name
    conn.execute("PRAGMA journal_mode=WAL")  # better concurrent read performance
    conn.execute("PRAGMA foreign_keys=ON")
    return conn

def create_tables(conn: sqlite3.Connection) -> None:
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS authors (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    NOT NULL UNIQUE,
            email   TEXT
        );

        CREATE TABLE IF NOT EXISTS books (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            title       TEXT    NOT NULL,
            author_id   INTEGER NOT NULL REFERENCES authors(id),
            year        INTEGER,
            pages       INTEGER,
            created_at  TEXT    DEFAULT (datetime('now'))
        );

        CREATE INDEX IF NOT EXISTS idx_books_author ON books(author_id);
        CREATE INDEX IF NOT EXISTS idx_books_year   ON books(year);
    """)
    conn.commit()
    print("Tables created.")

conn = get_connection()
create_tables(conn)

INSERT — Adding Records

from sqlite3 import IntegrityError

def add_author(conn: sqlite3.Connection, name: str, email: str = None) -> int:
    try:
        cur = conn.execute(
            "INSERT INTO authors (name, email) VALUES (?, ?)",
            (name, email),
        )
        conn.commit()
        return cur.lastrowid
    except IntegrityError:
        row = conn.execute("SELECT id FROM authors WHERE name = ?", (name,)).fetchone()
        return row["id"]

def add_book(
    conn: sqlite3.Connection,
    title: str,
    author_id: int,
    year: int = None,
    pages: int = None,
) -> int:
    cur = conn.execute(
        "INSERT INTO books (title, author_id, year, pages) VALUES (?, ?, ?, ?)",
        (title, author_id, year, pages),
    )
    conn.commit()
    return cur.lastrowid

# Bulk insert with executemany
def bulk_add_books(conn: sqlite3.Connection, books: list[dict]) -> int:
    rows = [(b["title"], b["author_id"], b.get("year"), b.get("pages")) for b in books]
    conn.executemany(
        "INSERT INTO books (title, author_id, year, pages) VALUES (?, ?, ?, ?)",
        rows,
    )
    conn.commit()
    return len(rows)

aid1 = add_author(conn, "Luciano Ramalho", "[e-mail]")
aid2 = add_author(conn, "Brett Slatkin")
add_book(conn, "Fluent Python", aid1, 2022, 1012)
add_book(conn, "Effective Python", aid2, 2019, 448)
print(f"Inserted authors: {aid1}, {aid2}")

SELECT — Querying Records

def get_all_books(conn: sqlite3.Connection) -> list[sqlite3.Row]:
    return conn.execute("""
        SELECT b.id, b.title, b.year, b.pages, a.name AS author
        FROM   books b
        JOIN   authors a ON a.id = b.author_id
        ORDER  BY b.year DESC
    """).fetchall()

def search_books(conn: sqlite3.Connection, keyword: str) -> list[sqlite3.Row]:
    return conn.execute(
        """
        SELECT b.title, a.name AS author, b.year
        FROM   books b
        JOIN   authors a ON a.id = b.author_id
        WHERE  b.title LIKE ?
        ORDER  BY b.title
        """,
        (f"%{keyword}%",),
    ).fetchall()

def get_author_stats(conn: sqlite3.Connection) -> list[sqlite3.Row]:
    return conn.execute("""
        SELECT a.name, COUNT(b.id) AS book_count, AVG(b.pages) AS avg_pages
        FROM   authors a
        LEFT JOIN books b ON b.author_id = a.id
        GROUP BY a.id
        ORDER BY book_count DESC
    """).fetchall()

for book in get_all_books(conn):
    print(f"  [{book['year']}] {book['title']} — {book['author']} ({book['pages']} pages)")

for stat in get_author_stats(conn):
    print(f"  {stat['name']}: {stat['book_count']} books, avg {stat['avg_pages']:.0f} pages")

UPDATE and DELETE

def update_book_pages(conn: sqlite3.Connection, book_id: int, pages: int) -> bool:
    cur = conn.execute(
        "UPDATE books SET pages = ? WHERE id = ?",
        (pages, book_id),
    )
    conn.commit()
    return cur.rowcount > 0

def delete_book(conn: sqlite3.Connection, book_id: int) -> bool:
    cur = conn.execute("DELETE FROM books WHERE id = ?", (book_id,))
    conn.commit()
    return cur.rowcount > 0

# Safe upsert pattern
def upsert_author(conn: sqlite3.Connection, name: str, email: str) -> int:
    conn.execute(
        """
        INSERT INTO authors (name, email) VALUES (?, ?)
        ON CONFLICT(name) DO UPDATE SET email = excluded.email
        """,
        (name, email),
    )
    conn.commit()
    row = conn.execute("SELECT id FROM authors WHERE name = ?", (name,)).fetchone()
    return row["id"]

Transactions and Context Manager

import contextlib

@contextlib.contextmanager
def transaction(conn: sqlite3.Connection):
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise

with transaction(conn):
    aid = add_author(conn, "David Beazley")
    add_book(conn, "Python Cookbook", aid, 2013, 706)
    add_book(conn, "Python Essential Reference", aid, 2009, 717)
print("Transaction committed.")

Real-World Use Cases

• CLI tools: store config, history, and state locally without a server
• Data pipelines: accumulate and query intermediate results before final export
• Testing: spin up an in-memory DB (":memory:") per test for full isolation
• Desktop apps: Tkinter / PyQt apps often bundle SQLite for local persistence
• Prototyping: validate your schema before migrating to PostgreSQL

---

Follow me for more Python tips! 🐍

---

🛠️ Recommended Tool

If you found this useful, check out Content Creator Ultimate Bundle (Save 33%) — $29.99 and designed for developers like you.

Get instant access to our best-selling AI Dev Boost, HTML Landing Page Templates, AI Prompts for Developers, and Python Automation Scripts Pack, perfect for content creators and marketers looking to elevate their game. This bundle is a must-have for anyone looking to create stunning content, build high-converting landing pages, and drive real results. With these tools, you'll be able to create engaging content, build beautiful landing pages, and boost your online presence.