German Yamil

Posted on Jun 3

Python sqlite3: A Database in One File, No Server Needed

#python #database #beginners #tutorial

What SQLite Is (and Isn't)

SQLite is a full SQL database stored in a single file. No server, no install, no configuration. It ships with Python.

Good for:

Local data persistence (replacing JSON for complex data)
Development databases before switching to PostgreSQL
Embedded databases in desktop apps, CLI tools, scripts
Read-heavy workloads up to ~100GB

Not for:

High concurrent writes (>10 writers at once)
Multi-server setups
Web apps with heavy traffic

Creating a Database

import sqlite3

# Creates file if it doesn't exist; opens it if it does
conn = sqlite3.connect("myapp.db")

# In-memory database — gone when connection closes
conn = sqlite3.connect(":memory:")

Always close connections (or use context managers):

with sqlite3.connect("myapp.db") as conn:
    # conn auto-commits on success, rolls back on exception
    pass

Creating Tables

import sqlite3

with sqlite3.connect("users.db") as conn:
    conn.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    NOT NULL,
            email   TEXT    UNIQUE NOT NULL,
            created TEXT    DEFAULT (datetime('now'))
        )
    """)

IF NOT EXISTS prevents errors on repeated runs.

CRUD Operations

import sqlite3
from datetime import datetime

def add_user(conn: sqlite3.Connection, name: str, email: str) -> int:
    cursor = conn.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        (name, email)
    )
    conn.commit()
    return cursor.lastrowid  # New row's ID

def get_user(conn: sqlite3.Connection, user_id: int) -> dict | None:
    row = conn.execute(
        "SELECT id, name, email, created FROM users WHERE id = ?",
        (user_id,)
    ).fetchone()
    if row is None:
        return None
    return {"id": row[0], "name": row[1], "email": row[2], "created": row[3]}

def update_user(conn: sqlite3.Connection, user_id: int, name: str) -> bool:
    cursor = conn.execute(
        "UPDATE users SET name = ? WHERE id = ?",
        (name, user_id)
    )
    conn.commit()
    return cursor.rowcount > 0  # True if a row was updated

def delete_user(conn: sqlite3.Connection, user_id: int) -> bool:
    cursor = conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
    conn.commit()
    return cursor.rowcount > 0

# Usage
with sqlite3.connect("users.db") as conn:
    conn.execute("""CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created TEXT DEFAULT (datetime('now'))
    )""")

    uid = add_user(conn, "Alice", "alice@example.com")
    print(add_user(conn, "Bob", "bob@example.com"))    # 2

    print(get_user(conn, 1))  # {'id': 1, 'name': 'Alice', ...}
    update_user(conn, 1, "Alicia")
    print(get_user(conn, 1))  # {'id': 1, 'name': 'Alicia', ...}

NEVER Use String Formatting for Queries

# ❌ SQL INJECTION VULNERABILITY
name = "'; DROP TABLE users; --"
conn.execute(f"SELECT * FROM users WHERE name = '{name}'")

# ✅ Always use parameterized queries
conn.execute("SELECT * FROM users WHERE name = ?", (name,))

The ? placeholder safely escapes the value. Always use it.

Fetching Multiple Rows

def get_all_users(conn: sqlite3.Connection) -> list[dict]:
    rows = conn.execute("SELECT id, name, email FROM users ORDER BY id").fetchall()
    return [{"id": r[0], "name": r[1], "email": r[2]} for r in rows]

def search_users(conn: sqlite3.Connection, query: str) -> list[dict]:
    rows = conn.execute(
        "SELECT id, name, email FROM users WHERE name LIKE ?",
        (f"%{query}%",)
    ).fetchall()
    return [{"id": r[0], "name": r[1], "email": r[2]} for r in rows]

# For large results, iterate instead of fetchall()
for row in conn.execute("SELECT * FROM large_table"):
    process(row)

Row Factory: Get Dicts Instead of Tuples

import sqlite3

def dict_factory(cursor, row):
    return {col[0]: row[idx] for idx, col in enumerate(cursor.description)}

conn = sqlite3.connect("users.db")
conn.row_factory = dict_factory

row = conn.execute("SELECT * FROM users WHERE id = 1").fetchone()
print(row)  # {'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}

Or use the built-in:

conn.row_factory = sqlite3.Row
row = conn.execute("SELECT * FROM users WHERE id = 1").fetchone()
print(row["name"])   # Alice (access by column name)
print(dict(row))     # {'id': 1, 'name': 'Alice', ...}

Transactions

def transfer_points(conn, from_id, to_id, amount):
    try:
        conn.execute("BEGIN")
        conn.execute("UPDATE users SET points = points - ? WHERE id = ?", (amount, from_id))
        conn.execute("UPDATE users SET points = points + ? WHERE id = ?", (amount, to_id))
        conn.commit()
    except Exception:
        conn.rollback()
        raise

The context manager handles this automatically:

with sqlite3.connect("db.db") as conn:
    conn.execute("UPDATE ...")  # Auto-committed or rolled back

Schema Migrations

def init_db(conn: sqlite3.Connection):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        )
    """)

    # Add column if it doesn't exist (migration)
    columns = [row[1] for row in conn.execute("PRAGMA table_info(users)")]
    if "created" not in columns:
        conn.execute("ALTER TABLE users ADD COLUMN created TEXT DEFAULT (datetime('now'))")

    conn.commit()

When to Switch to PostgreSQL

SQLite	PostgreSQL
Single writer	Multiple concurrent writers
< 100GB	Any size
Local/embedded	Server, networked
No setup needed	Requires server
`sqlite3` (stdlib)	`psycopg2` or `asyncpg`

SQLite first, switch when you need to.

Get the Full Pipeline

This article is part of the Python AI Publishing Pipeline series — a complete system to write, validate, and publish technical ebooks with Python and Claude.

📋 Free checklist: 7 steps to ship a Python ebook — PDF, no email required.

🚀 Full pipeline + source code: germy5.gumroad.com/l/xhxkzz — $9.99, 30-day money-back guarantee.

If this was useful, the ❤️ button helps other developers find it.

Building a Python content pipeline? I sell the complete automation system as a one-time download — Dev.to API, Claude API, launchd, Gumroad. Check it out ($9.99)

DEV Community