Every script I write starts the same way: a list in memory, or a JSON file I load, change, and dump back out. That works right up until I want to ask the data a question — everything I haven't read yet, sorted by author — and the list code turns into nested loops I don't want to look at anymore.
The fix has been in the standard library the whole time: sqlite3. It's a real SQL database that lives in a single file. No server to install, no user accounts, no configuration. The entire setup is import sqlite3.
Nearly everything I do with sqlite3 comes down to four calls. connect() opens the database file — and creates it if it isn't there yet. execute() sends one SQL statement. commit() makes your inserts and updates permanent; forget it and every change is gone when the script exits. fetchall() collects the rows a SELECT returned, as a list of tuples. Once the table has some rows in it, that looks like:
import sqlite3
conn = sqlite3.connect("books.db") # the file is created if missing
cur = conn.cursor()
cur.execute("SELECT title, author FROM books WHERE read = 0")
rows = cur.fetchall()
for title, author in rows:
print(title, "—", author)My first instinct was to assemble the SQL string myself. Here is that instinct failing on a real book title:
title = "You Don't Know JS"
cur.execute(f"INSERT INTO books (title) VALUES ('{title}')")sqlite3.OperationalError: near "t": syntax errorThe apostrophe in Don't closes the SQL string early, and the rest of the title becomes garbage the parser trips over. Worse: if that string ever comes from another person — a form, a CSV someone else filled in — building SQL with f-strings lets them type their own SQL into your database. Not a theoretical worry; it's the oldest bug in database programming.
The fix is a ? placeholder and a tuple of values:
cur.execute("INSERT INTO books (title, author) VALUES (?, ?)",
(title, author))sqlite3 swaps each ? for a properly quoted, escaped value, and there is nothing left to break. One small gotcha: the parameters go in a tuple, so a single value needs the trailing comma — (1,), because (1) is just the number 1 and sqlite3 will refuse it.
Here is my actual reading list as one script. Create, insert, select, update, delete — the whole of CRUD:
import sqlite3
conn = sqlite3.connect("books.db")
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
title TEXT,
author TEXT,
read INTEGER DEFAULT 0
)""")
cur.executemany(
"INSERT INTO books (title, author) VALUES (?, ?)",
[
("You Don't Know JS", "Kyle Simpson"),
("Fluent Python", "Luciano Ramalho"),
],
)
cur.execute("SELECT id, title FROM books WHERE read = 0")
print("unread:", cur.fetchall())
cur.execute("UPDATE books SET read = 1 WHERE title = ?", ("Fluent Python",))
cur.execute("DELETE FROM books WHERE title = ?", ("You Don't Know JS",))
conn.commit()Run it and it prints the two unread rows as tuples, then marks one book read and removes the other. The entire database is one file, books.db, sitting next to the script — back it up by copying it, move it between machines the same way. That single-file fact quietly changes how databases feel: they stop being infrastructure and become just another file in the folder.
More often than I expected. One user, or a small team that isn't writing at the same minute? Data that fits on one machine? Real queries — filtering, sorting, counting — without standing up a server? That's sqlite territory, and it covers most of what a hobby project ever asks for.
The honest limits: many programs writing at the same time, several machines sharing one file over the network, or data in the hundreds of gigabytes. Those genuinely need Postgres.
I have now built "temporary" JSON-file storage three times and rewritten it as sqlite twice. The third time I started with sqlite. It turns out sqlite was never the boring choice — rebuilding a bad imitation of it was.