Database IO with sqlite3

SQLite is a full relational database engine in a single file — no server, no configuration, no separate install. Python ships sqlite3 in the standard library, making it the go-to choice for local data storage, small analytical pipelines, and prototyping database schemas before moving to Postgres or MySQL. The API follows the Python DB-API 2.0 standard, so the patterns you learn here transfer directly.

Connecting and the connection as a context manager

sqlite3.connect takes a file path (or ":memory:" for a transient in-memory database):

import sqlite3

# on-disk database — created if it does not exist
conn = sqlite3.connect("data.db")

# in-memory — great for tests and throwaway pipelines
conn = sqlite3.connect(":memory:")

Use the connection as a context manager for automatic transaction handling: the with block commits on success and rolls back on any exception.

with sqlite3.connect("data.db") as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS items (name TEXT, qty INTEGER)")
    conn.execute("INSERT INTO items VALUES (?, ?)", ("widget", 5))
# conn is still open here; the with block committed the transaction
conn.close()

Parameterised queries — always use ? placeholders

Never build SQL strings with f-strings or % formatting. A rogue value like "'; DROP TABLE items; --" becomes an injection attack. Use ? placeholders instead — the driver handles quoting and escaping:

# WRONG — vulnerable to SQL injection
name = input("name: ")
conn.execute(f"INSERT INTO items VALUES ('{name}', 1)")   # never do this

# RIGHT — parameterised, safe
conn.execute("INSERT INTO items VALUES (?, ?)", (name, 1))

The same rule applies to every query that takes user input or external data — SELECT, UPDATE, DELETE, all of them.

Fetching results

A cursor (returned by execute) holds the result set. Three methods cover all use cases:

cursor = conn.execute("SELECT name, qty FROM items WHERE qty > ?", (0,))

row   = cursor.fetchone()    # next row as a tuple, or None
rows  = cursor.fetchall()    # all remaining rows as a list of tuples
chunk = cursor.fetchmany(50) # next 50 rows — good for large result sets

For one-liner reads, conn.execute(...).fetchall() skips naming the cursor.

executemany for bulk inserts

Loading many rows one execute at a time means many round-trips through the driver. executemany batches them into a single operation:

records = [("Alice", 92.5), ("Bob", 78.0), ("Carol", 88.3)]
conn.execute("CREATE TABLE scores (name TEXT, score REAL)")
conn.executemany("INSERT INTO scores VALUES (?, ?)", records)
conn.commit()

row_factory for dict-like rows

By default, rows come back as plain tuples. Set row_factory to sqlite3.Row and each row behaves like a dict keyed by column name — much easier to work with in real code:

conn.row_factory = sqlite3.Row

row = conn.execute("SELECT name, score FROM scores LIMIT 1").fetchone()
print(row["name"])    # 'Alice'  — column access by name
print(row["score"])   # 92.5
print(dict(row))      # {'name': 'Alice', 'score': 92.5}

Set row_factory before running any queries. It applies to every cursor created from that connection.

Where to go next

You can now persist and query structured data. The next lesson covers the bitwise and boolean tools that help you validate, filter, and flag that data efficiently.