Week 11 of 16

Watch: SQL & SQLite Basics

Replace JSON files with a real database — no server required, built into Python, scales to millions of rows

Day 51 75 minutes Watch

Day 51 of 80

Why Databases?

JSON Files Work for Learning — Not for Scale

Your Prompt Vault currently stores data in a JSON file. That works fine when you have 50 prompts and one user. But consider what breaks at scale:

10,000 prompts — your app loads all 10,000 into memory every time it starts, just to search for one
Two users simultaneously — User A reads the file, User B reads the file, User A writes their version back. User B's changes are gone. This is called a write conflict, and JSON files have no protection against it.
Complex queries — "show me all Kling prompts from last week sorted by usage" requires Python loops over every record. A database does this in one SQL line.
Data integrity — JSON will happily let you store {"platform": null, "shot": ""}. A database enforces constraints: NOT NULL, data types, unique keys.

Databases solve all of this. And SQLite is the easiest possible database to start with.

SQLite Is Different from Other Databases

Most databases you've heard of — MySQL, PostgreSQL, MongoDB — run as separate servers. You install them, configure them, start them, and connect to them over a network. SQLite is different:

It lives in a single file on disk (e.g., prompt_vault.db)
No server to install or start — the database engine is part of your Python program
Built into Python — import sqlite3 is all you need, no pip install
Portable — move the .db file and all your data moves with it
Used by iOS, Android, Firefox, Chrome, and millions of other applications

It's not a toy. SQLite is the most widely deployed database in the world. For single-user applications and apps with moderate traffic, it's often all you need.

Today's Videos

#	Video	Length	Focus
1	Corey Schafer — SQLite Tutorial with Python	~30 min	sqlite3 module: connect, cursor, execute, fetchall, commit, close
2	freeCodeCamp — SQL Tutorial for Beginners	First 45 min	SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY — stop at the 45-minute mark

How to Watch

Watch the Corey Schafer video first — it shows you how SQLite works from Python's perspective, which is the angle you'll use. Then watch the first 45 minutes of the freeCodeCamp SQL video to solidify the SQL language syntax. Don't try to code along today — just absorb the concepts. Tomorrow's Jupyter session is your hands-on practice.

The Four SQL Commands You Need

SQL has dozens of commands. You need four for 90% of what you'll ever do:

Command	What It Does	Example
`SELECT`	Get data from the database	`SELECT * FROM prompts`
`INSERT`	Add a new row	`INSERT INTO prompts (platform, shot, prompt) VALUES (?, ?, ?)`
`DELETE`	Remove rows	`DELETE FROM prompts WHERE id = 5`
`WHERE`	Filter which rows are affected	`WHERE platform = 'Kling'`

You'll also use a few modifiers:

Modifier	What It Does	Example
`ORDER BY`	Sort results	`ORDER BY created_at DESC`
`LIKE`	Partial text match (% is wildcard)	`WHERE shot LIKE '%golf%'`
`GROUP BY`	Aggregate rows by a column	`GROUP BY platform`
`COUNT(*)`	Count matching rows	`SELECT COUNT(*) FROM prompts`

SQL is Not Python

A Different Language for a Different Job

SQL is a separate language from Python. You write SQL strings inside your Python code and pass them to the database engine to execute. The database engine is not Python — it speaks SQL. This is normal and fine.

Think of it like this: when you build a Flask app, Python handles the logic and HTML handles the presentation. When you build a database-backed app, Python handles the logic and SQL handles the data questions.

SQL reads almost like English: SELECT platform, COUNT(*) FROM prompts GROUP BY platform ORDER BY COUNT(*) DESC — "select the platform and count of rows, grouped by platform, sorted by count descending." Once you see the pattern, it clicks quickly.

Never Put Variables Directly in SQL Strings

This is the most important rule in database programming. Do NOT write: f"SELECT * FROM prompts WHERE platform = '{user_input}'". If user_input is '; DROP TABLE prompts; --, you just deleted your database. This is called SQL injection. Instead, always use ? placeholders: "SELECT * FROM prompts WHERE platform = ?" with (user_input,) as the second argument. The database driver handles escaping safely. You'll practice this tomorrow.

What's Coming This Week

Day 51 (today) — Watch: understand SQLite and basic SQL
Day 52 — Read + Jupyter: hands-on with sqlite3 in Python, five cells
Day 53 — Build: create a Database class that wraps SQLite for the Prompt Vault
Day 54 — Build: write a migration script to move your JSON data into SQLite, update vault.py
Day 55 — Review: SQL knowledge check, JSON vs SQLite comparison, preview of Week 12 (FastAPI)

End of Day Checklist

Watched the Corey Schafer SQLite tutorial (~30 min)
Watched the first 45 minutes of the freeCodeCamp SQL tutorial
Can write a basic SELECT query with a WHERE clause from memory
Know what conn.commit() does and why it's needed
Understand why SQLite requires no server installation
Know the SQL injection danger and why ? placeholders prevent it

Tomorrow

Day 52 is Read + Jupyter. You'll read the official Python sqlite3 tutorial, then work through five cells: creating a table, inserting with ? placeholders, querying all rows, using row_factory for dict-like access, and fuzzy LIKE search. Hands-on practice with everything from today's videos.