Databases

SQLite ships with Python and works well for local tools and smaller applications.

import sqlite3

connection = sqlite3.connect("app.db")
connection.row_factory = sqlite3.Row
connection.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
    )
""")
connection.commit()

Parameterize queriesCopy Anchor Link

with connection:
    connection.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("Ada", "ada@example.com"),
    )

Never insert input into a SQL string. Placeholders prevent SQL injection and handle escaping correctly.

Transactions make a group of changes succeed or fail together. Keep SQL inside a repository and business rules inside services so both can be tested independently. Version schema changes as migrations.

ExerciseCopy Anchor Link

Create a SQLite task manager with create, list, complete and delete operations. Test it against a separate temporary database.