A Database in Your Standard Library: A Practical Deep Dive into Python's sqlite3

You do not need Postgres, a Docker container, or an ORM to start storing structured data in Python. The standard library already ships a full-featured, transactional SQL database engine: sqlite3. It writes to a single file (or an in-memory buffer), needs no server, and is battle-tested enough to power browsers, phones, and aircraft software. For local tools, prototypes, test fixtures, caches, and small-to-medium applications, it is often the right answer.

The catch is that the module is thin. It hands you raw SQL and a DB-API 2.0 cursor, and it makes a few surprising choices about transactions and types. This guide walks through the parts that matter in day-to-day code, and the pitfalls that quietly cause bugs.

Connecting, creating, and inserting

A connection is your handle to the database. Pass a filename to persist to disk, or ":memory:" for a throwaway database that lives only for the session. You can run SQL directly on the connection object — it creates a cursor for you behind the scenes.

import sqlite3

con = sqlite3.connect("library.db")
con.execute("""
    CREATE TABLE IF NOT EXISTS book (
        id      INTEGER PRIMARY KEY,
        title   TEXT NOT NULL,
        author  TEXT NOT NULL,
        year    INTEGER,
        rating  REAL
    )
""")
con.commit()

Declaring the primary key as INTEGER PRIMARY KEY makes it an alias for SQLite's built-in rowid, so it auto-increments without extra keywords.

Always parameterize — never format strings

The single most important habit with any SQL library is to pass values as parameters, not by building the query with f-strings or +. String interpolation opens you to SQL injection and breaks on values containing quotes. Use ? placeholders and a tuple of values:

con.execute(
    "INSERT INTO book (title, author, year, rating) VALUES (?, ?, ?, ?)",
    ("Fluent Python", "Luciano Ramalho", 2022, 4.8),
)
con.commit()

To insert many rows in one call, use executemany with an iterable of tuples. It is both cleaner and faster than a Python loop of individual inserts:

books = [
    ("The Pragmatic Programmer", "Hunt & Thomas", 1999, 4.7),
    ("Clean Code", "Robert C. Martin", 2008, 4.2),
    ("Effective Python", "Brett Slatkin", 2019, 4.6),
]
con.executemany(
    "INSERT INTO book (title, author, year, rating) VALUES (?, ?, ?, ?)",
    books,
)
con.commit()

If you prefer self-documenting queries, SQLite also supports named placeholders bound from a dictionary:

con.execute(
    "INSERT INTO book (title, author, year, rating) "
    "VALUES (:title, :author, :year, :rating)",
    {"title": "Serious Python", "author": "Julien Danjou",
     "year": 2018, "rating": 4.3},
)
con.commit()

Reading data back

execute returns a cursor, which is an iterator over result rows. You can loop over it directly (memory-friendly for large results), grab a single row with fetchone(), or pull everything with fetchall().

cur = con.execute(
    "SELECT title, year FROM book WHERE year >= ? ORDER BY year",
    (2018,),
)
print(cur.fetchone())        # ('Serious Python', 2018)

for title, year in con.execute("SELECT title, year FROM book ORDER BY title"):
    print(title, year)

Row factories: stop counting tuple positions

By default every row is a plain tuple, so you access columns by index. That gets unreadable fast. Set con.row_factory = sqlite3.Row and rows become mapping-like objects you can index by column name (case-insensitively) or position, and inspect with .keys():

con.row_factory = sqlite3.Row

row = con.execute(
    "SELECT title, author, rating FROM book WHERE title = ?",
    ("Clean Code",),
).fetchone()

print(row["title"], row["author"], row["rating"])
print(row.keys())    # ['title', 'author', 'rating']

sqlite3.Row is lightweight and almost always worth turning on. If you want real dictionaries, assign a small custom factory instead: con.row_factory = lambda c, r: {d[0]: v for d, v in zip(c.description, r)}.

Transactions and the connection as a context manager

This is the part that trips people up. The connection's context manager does not close the connection — it manages a transaction. On a clean exit it commits; if an exception propagates out of the block, it rolls back. It leaves the connection open for reuse.

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

try:
    with con:                                  # commit on success, rollback on error
        con.execute("UPDATE book SET rating = rating + 0.1 WHERE year < 2000")
        # NOT NULL violation raises IntegrityError -> whole block rolls back
        con.execute("INSERT INTO book (title, author) VALUES (?, ?)", (None, "x"))
except sqlite3.IntegrityError as e:
    print("rolled back:", e)

# The earlier UPDATE was undone too, because both ran in one transaction.

Because with con: does not close anything, you still call con.close() yourself when you are done. A common clean pattern is to nest: use with sqlite3.connect(path) as con: for the transaction, but remember you are responsible for closing on older versions. The safest habit is an explicit close() in a finally, or wrapping the connection with contextlib.closing.

One more subtlety worth knowing: on the default settings, sqlite3 opens a transaction implicitly before data-modifying statements but leaves you to commit. If you forget commit() and just close the connection, your changes are lost. Since Python 3.12 you can opt into clearer behavior with the autocommit attribute (set it to False for explicit control, or True to commit every statement). On earlier versions the behavior is governed by isolation_level.

Storing richer types with adapters and converters

SQLite natively stores only NULL, integers, floats, text, and blobs. Python's int, float, str, bytes, and None map across automatically, but a datetime.date does not. You can teach the module to translate by registering an adapter (Python → SQLite) and a converter (SQLite → Python), then opening the connection with detect_types:

import datetime

sqlite3.register_adapter(datetime.date, lambda d: d.isoformat())
sqlite3.register_converter("date", lambda b: datetime.date.fromisoformat(b.decode()))

con = sqlite3.connect("events.db", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("CREATE TABLE event (name TEXT, day date)")   # column typed 'date'
con.execute("INSERT INTO event VALUES (?, ?)", ("launch", datetime.date(2026, 8, 23)))
con.commit()

value = con.execute("SELECT day FROM event").fetchone()[0]
print(type(value).__name__, value)     # date 2026-08-23

With PARSE_DECLTYPES, the module looks at the column's declared type (day date) and applies the matching converter. Note that Python 3.12 deprecated the built-in date/time adapters precisely to encourage you to register your own, so this pattern is the future-proof approach.

Two PRAGMAs you should almost always set

SQLite has knobs called PRAGMAs. Two are worth setting on nearly every connection. Foreign key enforcement is off by default for historical reasons, so enable it per connection. And Write-Ahead Logging (WAL) mode dramatically improves concurrency by letting readers and a writer work at the same time:

con = sqlite3.connect("app.db")
con.execute("PRAGMA foreign_keys = ON")
con.execute("PRAGMA journal_mode = WAL")

WAL is a persistent, database-level setting (it survives reconnects and creates -wal and -shm side files), while foreign_keys is a per-connection flag you must set every time you connect.

Backing up a live database

Copying the file with shutil while it is open can produce a corrupt copy. The connection's backup() method does a safe, online snapshot — perfect for flushing an in-memory database to disk, or vice versa:

src = sqlite3.connect("library.db")
dst = sqlite3.connect(":memory:")
src.backup(dst)                # copy the whole database, safely
print(dst.execute("SELECT count(*) FROM book").fetchone()[0])
src.close()
dst.close()

Common pitfalls

A few traps catch almost everyone. Building queries with f-strings instead of placeholders is the big one — it is both a security hole and a correctness bug. Forgetting to commit() (or to wrap writes in with con:) silently discards data. Assuming with con: closes the connection leaks handles. Sharing a single connection across threads throws by default; either give each thread its own connection or pass check_same_thread=False and add your own locking. And relying on SQLite to reject a string in an INTEGER column will disappoint you: by default columns use flexible "type affinity" and will happily store the wrong type unless you add CHECK constraints or use a STRICT table.

Wrap-up and next steps

The sqlite3 module gives you a real, transactional database with zero setup: parameterized queries keep you safe, sqlite3.Row keeps result handling readable, the connection context manager gives you atomic transactions, and adapters plus a couple of PRAGMAs cover the rough edges. From here, explore user-defined functions with con.create_function, full-text search via the FTS5 extension, and STRICT tables for tighter typing. When a project genuinely outgrows a single file, the SQL you have been writing will port cleanly to Postgres or an ORM like SQLAlchemy — but you may be surprised how long SQLite is all you need.