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

You do not need Postgres, a Docker container, or a running server to persist data in Python. Sitting quietly in the standard library is sqlite3, a full SQL database engine that stores everything in a single file (or entirely in memory). It is perfect for local tools, prototypes, test fixtures, desktop apps, caches, and any situation where "a real database" would be overkill but a pile of JSON files would be a mess.

The catch is that the sqlite3 API is thin and low-level, and a lot of code in the wild misuses it in ways that are slow, unsafe, or subtly buggy. This guide walks through the idioms that actually matter: parameterized queries, row factories, transactions, bulk inserts, and type handling. Every example below was tested on Python 3.10 and the concepts apply to any modern interpreter.

Connecting and creating a table

A connection is your handle to the database. Pass a filename to persist to disk, or the special string ":memory:" for a throwaway database that vanishes when you close it — ideal for tests.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE book (
        id     INTEGER PRIMARY KEY,
        title  TEXT NOT NULL,
        author TEXT NOT NULL,
        year   INTEGER
    )
""")

Notice that id INTEGER PRIMARY KEY gives you an auto-incrementing row id for free — SQLite fills it in automatically when you omit it on insert. You can call execute() directly on the connection object; under the hood it creates a cursor and returns it, which is a convenient shortcut for one-off statements.

Never build SQL with string formatting

This is the single most important rule in the whole article. Do not interpolate values into your SQL with f-strings or +. Use placeholders and pass the values separately. This protects you from SQL injection and from quoting bugs (an apostrophe in a name, for example).

title = "Dune"

# WRONG — vulnerable to injection and breaks on quotes
conn.execute(f"SELECT * FROM book WHERE title = '{title}'")

# RIGHT — the value is bound safely by the driver
conn.execute("SELECT * FROM book WHERE title = ?", (title,))

The ? is a positional placeholder; the second argument is a tuple of values. A very common mistake is forgetting that it must be a sequence: ("Dune",) with the trailing comma is a one-element tuple, whereas ("Dune") is just a string and will raise an error.

SQLite also supports named placeholders, which are easier to read when a query has many parameters:

conn.execute(
    "INSERT INTO book (title, author, year) VALUES (:title, :author, :year)",
    {"title": "Neuromancer", "author": "William Gibson", "year": 1984},
)

Inserting many rows at once

When you have a batch of rows, do not loop and call execute() once per row. Use executemany(), which is both cleaner and significantly faster because it reuses the prepared statement.

rows = [
    ("Snow Crash", "Neal Stephenson", 1992),
    ("The Left Hand of Darkness", "Ursula K. Le Guin", 1969),
]
conn.executemany(
    "INSERT INTO book (title, author, year) VALUES (?, ?, ?)", rows
)
conn.commit()

That conn.commit() matters. By default, sqlite3 opens an implicit transaction for you and your changes are not saved until you commit. Forgetting to commit is the number-one reason people complain that "my data disappeared" — it was rolled back when the connection closed.

Reading data back

A query returns a cursor. You can pull results with fetchone(), fetchall(), or — best for large result sets — by iterating over the cursor directly, which streams rows without loading everything into memory at once.

cur = conn.execute(
    "SELECT title, year FROM book WHERE year > ? ORDER BY year",
    (1970,),
)
print(cur.fetchall())
# [('Neuromancer', 1984), ('Snow Crash', 1992)]

# Streaming, memory-friendly:
for title, year in conn.execute("SELECT title, year FROM book ORDER BY title"):
    print(title, year)

By default each row is a plain tuple, so you access columns by position. That gets unreadable fast. The fix is a row factory.

Rows you can read: sqlite3.Row

Set conn.row_factory = sqlite3.Row and every row becomes an object you can index by column name (and still by position). This makes downstream code far more robust — reordering columns in your SELECT no longer breaks anything.

conn.row_factory = sqlite3.Row

row = conn.execute("SELECT * FROM book WHERE title = ?", ("Dune",)).fetchone()
print(row["title"], row["author"], row["year"])  # Dune Frank Herbert 1965
print(row.keys())                                 # ['id', 'title', 'author', 'year']

sqlite3.Row is lightweight and supports keyword access, integer indexing, and iteration. If you want plain dictionaries instead, you can assign a small custom factory: conn.row_factory = lambda c, r: {col[0]: r[i] for i, col in enumerate(c.description)}.

Transactions the clean way

A transaction groups several statements so they either all succeed or all get undone. The sqlite3 connection is itself a context manager for exactly this: if the with block finishes normally the transaction is committed, and if an exception propagates out, it is rolled back automatically.

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE acct (name TEXT, balance INTEGER)")
db.execute("INSERT INTO acct VALUES ('alice', 100)")
db.commit()

try:
    with db:  # commits on success, rolls back on exception
        db.execute("UPDATE acct SET balance = balance - 50 WHERE name = 'alice'")
        raise ValueError("something went wrong mid-transaction")
except ValueError:
    pass

# The update was undone because the block raised:
print(db.execute("SELECT balance FROM acct").fetchone()[0])  # 100

One subtlety worth remembering: with db: manages the transaction, not the connection. It does not close the database. You still call db.close() yourself when you are done, ideally from a try/finally or by wrapping the connection in contextlib.closing().

lastrowid and rowcount

After an INSERT, the cursor's lastrowid tells you the auto-generated primary key — invaluable when you need the new id to insert related rows. After an UPDATE or DELETE, rowcount reports how many rows were affected.

cur = db.execute("INSERT INTO acct VALUES ('bob', 200)")
print(cur.lastrowid)   # the rowid of the new record

cur = db.execute("UPDATE acct SET balance = 0 WHERE name = 'bob'")
print(cur.rowcount)    # 1

Storing dates and custom types

SQLite natively understands only a handful of types (INTEGER, REAL, TEXT, BLOB, NULL). To store something like a datetime, you register an adapter (Python value to storable form) and a converter (stored bytes back to a Python object), and open the connection with detect_types so the converter fires based on the column's declared type.

import sqlite3
import datetime

sqlite3.register_adapter(datetime.datetime, lambda dt: dt.isoformat())
sqlite3.register_converter(
    "DATETIME", lambda b: datetime.datetime.fromisoformat(b.decode())
)

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute("CREATE TABLE ev (ts DATETIME)")
conn.execute("INSERT INTO ev VALUES (?)", (datetime.datetime(2026, 7, 28, 9, 30),))

got = conn.execute("SELECT ts FROM ev").fetchone()[0]
print(repr(got))  # datetime.datetime(2026, 7, 28, 9, 30)

Older tutorials rely on the built-in date/time adapters that ship with sqlite3, but those were deprecated in Python 3.12. Registering your own adapter and converter, as above, is the forward-compatible approach and makes the storage format explicit.

Common pitfalls

A few mistakes recur constantly. Forgetting to commit() silently loses writes on disk-backed databases. Passing a bare string instead of a one-tuple to execute() (execute(sql, "Dune") instead of execute(sql, ("Dune",))) raises a confusing error, because a string is a sequence of single characters. Reusing one connection across threads without care will raise ProgrammingError — by default a connection may only be used in the thread that created it, so give each thread its own connection or use a connection pool. And building SQL with f-strings is always wrong for values; placeholders exist precisely so you never have to.

One more performance note: for write-heavy local workloads, enabling write-ahead logging with conn.execute("PRAGMA journal_mode=WAL") often improves concurrency and throughput noticeably, at the cost of a couple of extra files next to your database.

Wrap-up and next steps

The mental model is small. Open a connection, use ? placeholders for every value, batch inserts with executemany(), wrap groups of writes in with conn: for atomic transactions, and turn on sqlite3.Row so your result rows read like records instead of anonymous tuples. Remember to commit, and remember that a connection is single-threaded by default.

From here, worthwhile next steps include reading about SQLite's PRAGMA statements for tuning, exploring full-text search with the FTS5 extension, and — once your schema outgrows hand-written SQL — graduating to a query builder or an ORM like SQLAlchemy, which sits comfortably on top of the very same sqlite3 driver you have just learned. But for a huge range of real tasks, a single file and the standard library are all you need.