Week 11 of 16

Read & Experiment: SQLite in Python

Get your hands on sqlite3 before building the real storage layer.

Day 52 60 minutes Experiment

Day 52 of 80

Read First

Python Docs — sqlite3 Tutorial

Read the official sqlite3 tutorial. It's short — about 10 minutes. Focus on: opening a connection, executing SQL, committing changes, using row_factory, and closing.

sqlite3 is built into Python — no pip install needed. You already have it.

Experiment in Jupyter

Open Jupyter (or a scratch .py file) and work through each cell. Don't copy-paste — type it.

Cell 1: Create a database and table Python

import sqlite3

# connect() opens (or creates) a database file.
# Think of it like open() for JSON, but for a database.
# 'test_prompts.db' is just a file on your disk.
conn = sqlite3.connect("test_prompts.db")

# A cursor is how you send SQL commands to the database.
cursor = conn.cursor()

# CREATE TABLE defines the structure — what columns exist and their types.
# IF NOT EXISTS means "only create this if it doesn't already exist."
# INTEGER PRIMARY KEY AUTOINCREMENT gives each row a unique ID automatically.
cursor.execute("""
    CREATE TABLE IF NOT EXISTS prompts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        platform TEXT NOT NULL,
        shot TEXT NOT NULL,
        prompt TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")

# commit() saves the change. Without it, nothing is actually written.
conn.commit()
print("Table created!")

The triple-quoted string lets you write multi-line SQL without awkward concatenation. The SQL goes from """ to """ — Python treats it as one long string.

TEXT NOT NULL means the column stores strings and can't be empty. This is SQL's version of validation — the database itself rejects bad data before Python even sees it.

Cell 2: Insert data Python

# The ? placeholders prevent SQL injection — a security vulnerability
# where someone types SQL code into an input field and hacks your database.
# NEVER put user input directly into SQL strings with f-strings.
# Always use ? and pass values as a tuple.
cursor.execute(
    "INSERT INTO prompts (platform, shot, prompt) VALUES (?, ?, ?)",
    ("Kling", "Golf swing follow-through", "Cinematic slow motion golf swing, golden hour...")
)

cursor.execute(
    "INSERT INTO prompts (platform, shot, prompt) VALUES (?, ?, ?)",
    ("Runway", "Ocean aerial", "Aerial tracking shot over turquoise ocean...")
)

conn.commit()
print("Inserted 2 prompts!")

SQL injection is one of the most common security bugs. If you wrote f"INSERT ... VALUES ('{platform}', ...)" and someone entered '); DROP TABLE prompts; -- as a platform name, they could delete your database. The ? pattern makes that impossible.

Cell 3: Query data Python

# fetchall() returns a list of tuples. Each tuple is one row.
cursor.execute("SELECT * FROM prompts")
rows = cursor.fetchall()

for row in rows:
    print(row)
    # row is a tuple like (1, 'Kling', 'Golf swing...', 'Cinematic...', '2026-...')
    # row[0] = id, row[1] = platform, row[2] = shot, etc.

Cell 4: Row factory — dictionaries instead of tuples Python

# Row factory makes rows behave like dictionaries.
# This is much nicer to work with than row[1], row[2], etc.
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM prompts WHERE platform = ?", ("Kling",))
rows = cursor.fetchall()

for row in rows:
    # sqlite3.Row lets you access columns by name, like a dictionary.
    print(f"[{row['platform']}] {row['shot']}")
    print(f"  {row['prompt'][:60]}...")
    print(f"  Created: {row['created_at']}\n")

Always set row_factory before creating a cursor. The row factory applies to all cursors created after it's set. Setting it once on the connection is the standard pattern — you'll do this in your Database class tomorrow.

Cell 5: Search with LIKE Python

# LIKE is SQL's fuzzy match. % is a wildcard — matches any characters.
# '%golf%' matches anything containing "golf" anywhere in the value.
cursor.execute(
    "SELECT * FROM prompts WHERE shot LIKE ? OR prompt LIKE ?",
    ("%golf%", "%golf%")
)

results = cursor.fetchall()
print(f"Found {len(results)} matches for 'golf'")
for row in results:
    print(f"  [{row['platform']}] {row['shot']}")

# Always close the connection when you're done.
conn.close()

The % wildcard lets you match anywhere in the string. '%golf%' matches "Golf swing", "slow motion golf", "Golf course aerial" — any value containing "golf." This is what your search feature will use.

Key Differences from JSON

Think about how this differs from your JSON approach:

IDs are automatic. The database assigns a unique ID to each row. With JSON, you had to manage ordering manually.
Queries are built-in. Filtering by platform, searching by keyword — SQL does this natively. With JSON, you wrote Python list comprehensions.
You don't load everything. SELECT * FROM prompts WHERE platform = 'Kling' only returns Kling prompts. With JSON, you loaded all prompts then filtered in Python.
Changes are explicit. Nothing changes until you commit(). No accidental overwrites.

End of Day Checklist

You've run all 5 cells and read the output
You can explain what cursor is and why you need one
You understand why ? placeholders matter (SQL injection)
You've set row_factory = sqlite3.Row and used column names
You know what commit() does and when to call it

Tomorrow

Days 53–54 build the Database class — a proper SQLite storage layer that replaces your JSON-based PromptLibrary. You'll use everything from today's experiments.