Python's Built-In Database: A Practical Deep Dive into sqlite3

Every Python installation ships with a full relational database engine. No server to install, no connection strings to configure, no Docker container to babysit — just import sqlite3 and you have transactions, indexes, joins, and SQL at your fingertips. SQLite is arguably the most widely deployed database in the world, and Python's binding to it lives in the standard library.

Yet many developers either overlook sqlite3 entirely or use it just well enough to get burned by its quirks: silent SQL injection risks, surprising transaction behavior, and tuples where you expected dictionaries. This post walks through the module properly — from first connection to production-ready patterns.

Getting Started: Connections and Cursors

A SQLite database is a single file on disk. Connecting creates the file if it doesn't exist:

import sqlite3

con = sqlite3.connect("app.db")
cur = con.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id    INTEGER PRIMARY KEY,
        name  TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")
con.commit()
con.close()

For quick experiments and tests, you can skip the file entirely and work in memory:

con = sqlite3.connect(":memory:")  # vanishes when the connection closes

The INTEGER PRIMARY KEY column deserves a note: in SQLite it becomes an alias for the internal row id, so it auto-increments without any extra keywords. You rarely need AUTOINCREMENT.

Parameterized Queries: Never Format SQL Yourself

The single most important habit with sqlite3 is passing values as parameters instead of building SQL strings. String formatting invites SQL injection and breaks on inputs containing quotes:

# DANGEROUS — never do this
name = "Robert'); DROP TABLE users;--"
cur.execute(f"INSERT INTO users (name, email) VALUES ('{name}', 'x@y.z')")

# CORRECT — let the driver handle escaping
cur.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Ada Lovelace", "ada@example.com"),
)

Besides positional ? placeholders, SQLite supports named parameters, which read better in longer statements:

cur.execute(
    "UPDATE users SET email = :email WHERE name = :name",
    {"email": "ada@newdomain.com", "name": "Ada Lovelace"},
)

Note the tuple in the first example: parameters must be a sequence, so a single value is written (value,) — forgetting that trailing comma is a classic beginner error.

Reading Data: fetchone, fetchall, and Iteration

cur.execute("SELECT id, name, email FROM users WHERE id = ?", (1,))
row = cur.fetchone()          # a single tuple, or None

cur.execute("SELECT id, name FROM users ORDER BY name")
for user_id, name in cur:     # cursors are iterators — memory-friendly
    print(user_id, name)

Prefer iterating over the cursor (or calling fetchmany()) instead of fetchall() when the result set could be large; iteration streams rows instead of loading everything into a list.

Rows That Behave Like Dictionaries

By default rows come back as plain tuples, which makes code fragile — reorder your SELECT columns and every index shifts. Set a row factory once on the connection and rows become addressable by column name:

con = sqlite3.connect("app.db")
con.row_factory = sqlite3.Row

cur = con.execute("SELECT * FROM users WHERE email = ?", ("ada@example.com",))
row = cur.fetchone()
print(row["name"], row["created_at"])   # access by name
print(dict(row))                        # convert to a real dict when needed

sqlite3.Row is implemented in C, supports both index and key access, and costs almost nothing — there is little reason not to use it.

Transactions: The Part Everyone Gets Wrong

Out of the box, sqlite3 opens a transaction implicitly before data-modifying statements (INSERT, UPDATE, DELETE) and keeps it open until you call con.commit(). Forget the commit, and your changes silently disappear when the program exits.

The cleanest pattern is using the connection as a context manager:

with con:  # commits on success, rolls back on exception
    con.execute("INSERT INTO users (name, email) VALUES (?, ?)",
                ("Grace Hopper", "grace@example.com"))
    con.execute("INSERT INTO users (name, email) VALUES (?, ?)",
                ("Alan Turing", "alan@example.com"))

If either insert fails, both are rolled back — you get atomicity for free. But here is the pitfall that surprises nearly everyone:

with sqlite3.connect("app.db") as con:
    ...
# The connection is still OPEN here!

The with block manages the transaction, not the connection. Unlike file objects, exiting the block does not close the connection. Close it explicitly, or wrap it with contextlib.closing:

from contextlib import closing

with closing(sqlite3.connect("app.db")) as con:
    with con:
        con.execute("INSERT INTO users (name, email) VALUES (?, ?)",
                    ("Guido", "guido@example.com"))
# Now the connection really is closed.

Since Python 3.12 there is also an explicit autocommit attribute on connections that gives you more predictable, PEP 249-style transaction control; if you're on a modern Python, sqlite3.connect("app.db", autocommit=False) makes the behavior explicit rather than implicit.

Bulk Inserts with executemany

Inserting rows one execute() call at a time is slow. executemany() binds a sequence of parameter sets against one prepared statement:

users = [
    ("Katherine Johnson", "katherine@example.com"),
    ("Margaret Hamilton", "margaret@example.com"),
    ("Annie Easley", "annie@example.com"),
]

with con:
    con.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)

It also accepts any iterable — including a generator — so you can stream a large CSV into a table without holding it all in memory:

import csv

def rows(path):
    with open(path, newline="") as f:
        for record in csv.DictReader(f):
            yield (record["name"], record["email"])

with con:
    con.executemany("INSERT INTO users (name, email) VALUES (?, ?)",
                    rows("users.csv"))

Because the whole batch runs inside a single transaction, this is dramatically faster than committing per row.

Practical Tips for Real Applications

Enable WAL Mode for Concurrent Reads

SQLite's default journal mode locks readers out while a write is in progress. Write-Ahead Logging lets readers and one writer work simultaneously — a one-line, persistent upgrade for web apps and background jobs:

con.execute("PRAGMA journal_mode=WAL")

Turn On Foreign Key Enforcement

For historical reasons SQLite does not enforce foreign key constraints unless you ask, and the setting is per connection:

con.execute("PRAGMA foreign_keys=ON")

Store Dates as ISO 8601 Strings

SQLite has no native date type. The most portable approach is storing datetime.isoformat() strings and parsing them with datetime.fromisoformat() on the way out. (The module's old implicit date adapters were deprecated in Python 3.12, so explicit conversion is the future-proof choice.)

Run Schema Files with executescript

execute() handles one statement at a time. To run a migration file containing several statements, use:

with open("schema.sql") as f:
    con.executescript(f.read())

Common Pitfalls Recap

To summarize the traps worth remembering: building SQL with f-strings instead of parameters; forgetting the trailing comma in single-value parameter tuples; assuming with sqlite3.connect(...) closes the connection (it only manages the transaction); forgetting commit() outside a with con: block; expecting foreign keys to be enforced by default; and sharing one connection across threads without care — by default a connection may only be used by the thread that created it, so give each thread its own connection or use a queue.

Wrap-Up and Next Steps

For configuration storage, caching, CLI tools, test fixtures, data analysis scratch space, and even modest web applications, sqlite3 is often all the database you need — zero infrastructure, real SQL, full transactions. Start with row_factory = sqlite3.Row, parameterized queries, and the with con: transaction pattern, and you'll avoid the vast majority of problems.

From here, natural next steps are exploring SQLite's UPSERT syntax (INSERT ... ON CONFLICT), full-text search with the FTS5 extension, and — when you want Python objects instead of SQL — an ORM like SQLAlchemy, which speaks fluent SQLite underneath.