How to store API data in Python: SQLite in a few honest lines

The default fate of API data in a script is a CSV that grows until it stops being useful. Python's standard library carries a better answer: sqlite3, a real database in a single file — queryable, indexed, safe under interruptions, zero setup. For anything a personal project will collect more than once, SQLite is the honest default.

The working pattern

import sqlite3

conn = sqlite3.connect("inventory.db")
conn.execute(
    "CREATE TABLE IF NOT EXISTS items ("
    " id INTEGER PRIMARY KEY,"
    " name TEXT NOT NULL,"
    " price REAL,"
    " updated_at TEXT)"
)
sample = [
    ("desk lamp", 19.99, "2026-09-10"),
    ("usb-c cable", 8.5, "2026-09-10"),
]
conn.executemany(
    "INSERT OR REPLACE INTO items (name, price, updated_at) VALUES (?, ?, ?)",
    sample,
)
conn.commit()

for row in conn.execute(
    "SELECT name, price FROM items WHERE price > ? ORDER BY price", (9.0,)
):
    print(row)

conn.close()

Six details carry the whole skill. CREATE TABLE IF NOT EXISTS makes the script re-runnable — the first run creates, every later run reuses. INSERT OR REPLACE is the simplest upsert: re-fetching the same item updates instead of duplicating (as long as a unique key — here name could be one via a UNIQUE constraint — defines "the same"). Question-mark placeholders, always: values go in as parameters (?, (9.0,)), never formatted into the SQL string — string-formatting SQL is how injection bugs and quote-crashes are born, and the parameterised form is both safe and faster. executemany for batches: one round-trip for a hundred rows. commit() makes writes real; without it, closing the connection discards them. And dates as ISO-8601 text (2026-09-10) sort correctly as strings — which is the same convention Unix timestamps exist to simplify on the numerical side.

What you get for the ceremony

Once the data is in SQLite, the queries that were reasons to open the file become one-liners: latest price per item (ORDER BY updated_at DESC), weekly totals (SUM with a WHERE on the date), deduplication (SELECT DISTINCT). A CSV gives you none of that without rewriting the program each time. SQLite also writes atomically — a laptop dying mid-commit() leaves the file valid, which no hand-rolled file format promises.

When to graduate

The honest ceilings: SQLite is one file on one machine — perfect for a personal data pipeline, wrong for a multi-server application (that's PostgreSQL's job, and the SQL you wrote transfers almost unchanged), and wrong for data volumes where the file outgrows your disk or backup habits. For everything between "a list" and "a datacentre", it is the sweet spot — including as the storage layer under the paper-trading bot, which uses it for exactly these reasons.

Sources

• Python docs — sqlite3
• SQLite — the official documentation