HW6: Build a Database App

We recommend working through these steps in order. Each step builds on the previous one, and this order will help you make steady progress rather than getting stuck debugging too many things at once. If you find that one of these steps is taking significantly longer than the time we specified here, or if you aren't confident on how to start, please ask on Ed or come to office hours!

Step 1: Choose What to Track (15 minutes)

Pick something you'd genuinely find useful or fun to track. Here are some ideas, but feel free to come up with your own:

• Reading log: title, author, genre, rating, date finished, notes
• Workout journal: exercise, duration, intensity, date, notes
• Recipe collection: name, cuisine, prep time, difficulty, ingredients, URL
• Budget tracker: item, amount, category, date, notes
• Music diary: song, artist, album, genre, rating, date listened
• Course planner: course name, department, units, semester, grade, status
• Movie/TV log: title, genre, rating, date watched, where you watched it
• Habit tracker: habit name, date, completed (yes/no), streak, notes

Once you've picked a topic, write down 4–6 pieces of information (columns) you'd want to store for each entry. One of them should be an id that uniquely identifies each record.

Step 2: Design Your Database Schema (15 minutes)

Before writing any code, sketch out your table on paper, in a notes app, or by asking AI for help. For example, a reading log might look like:

Table: books
-----------------------------------------
| id | title | author | rating | date_finished | notes |
-----------------------------------------

Decide on the data type for each column. The most common SQLite types are:

• INTEGER — whole numbers (good for id, rating, quantity)
• TEXT — strings (good for names, descriptions, dates as text)
• REAL — decimal numbers (good for prices, distances)

Step 3: Set Up Your Project and Create the Database (20 minutes)

Create a new folder and Python file for your project. Use Python's built-in sqlite3 module (no installation needed!) to create your database and table. Here's the general pattern:

import sqlite3

# Connect to (or create) a database file
con = sqlite3.connect('my_tracker.db')
cur = con.cursor()

# Create your table (if it doesn't already exist)
cur.execute('''
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author TEXT,
        rating INTEGER,
        date_finished TEXT,
        notes TEXT
    )
''')
con.commit()

Run your script and verify that the .db file is created. You can also ask AI to show you how to verify the table was created correctly.

Step 4: Implement Create (INSERT) (20 minutes)

Write a function that takes user input and inserts a new record into your table. For a CLI, this might look like asking the user for each field and then running an INSERT INTO statement. Test it by adding a few records.

Step 5: Implement Read (SELECT) (20 minutes)

Write a function that retrieves records from your table and displays them nicely. Start with showing all records, then add at least one way to filter or sort. For example:

• Show all entries
• Show entries matching a search term (e.g., books by a specific author)
• Show entries sorted by a column (e.g., by rating, by date)
• Show entries matching a condition (e.g., only 5-star ratings, only this month)

Step 6: Implement Update (UPDATE) and Delete (DELETE) (30 minutes)

Update: Let the user pick a record (by id or by selecting from a list) and change one or more of its fields. For example, updating a book's rating after re-reading it.

Delete: Let the user remove a record by id. Consider asking for confirmation before deleting ("Are you sure?").

Step 7: Make the Interface (30 minutes)

If you're building a CLI (which we highly recommend), create a main menu loop that lets the user choose between operations, something like this:

--- My Reading Log ---
1. Add a book
2. View all books
3. Search books
4. Update a book
5. Delete a book
6. Quit

Choose an option:

If you're building a web interface, make sure each operation has a corresponding page or form.

Test the full flow: add several records, view them, update one, delete one, and verify everything works.

Step 8: Write Your README and Prompt Log (15 minutes)

Document your work as described in the requirements above (see "What you must submit").

Step 9: Record Your Video, Push to GitHub, Submit (15 minutes)

Make a short screencast (a minute or two is fine) showing yourself using the app: add an entry, view entries, search/filter, update one, and delete one. Push everything to GitHub, add it to your portfolio, and fill out the Google submission form.

Here's a quick reference for the SQL and database concepts you'll need. We'll cover these in lecture, but this is here for you to refer back to.

• SQLite: A lightweight database that stores everything in a single file (like my_tracker.db). No server setup required—it's built right into Python.
• SQL (Structured Query Language): The language used to talk to databases. The four main operations are:
  • INSERT INTO table (columns) VALUES (values) — adds a new record
  • SELECT columns FROM table WHERE condition — retrieves records
  • UPDATE table SET column = value WHERE condition — modifies records
  • DELETE FROM table WHERE condition — removes records
• Schema: The structure of your database—what tables exist and what columns they have.
• Primary key: A unique identifier for each row (usually id INTEGER PRIMARY KEY).
• Parameterized queries: Using ? placeholders instead of string formatting to prevent SQL injection attacks. Example: cursor.execute("SELECT * FROM books WHERE author = ?", (author_name,))
• CRUD: Create, Read, Update, Delete—the four basic database operations.

• Start with the database, not the interface. Get your CREATE TABLE, INSERT, and SELECT working in a simple test script before building out a menu or web page. You can even test queries interactively in the Python REPL.
• Keep it simple first, then add flair if you wish. A clean, working CLI with all four CRUD operations is worth more than a fancy web interface that's only half-working. Get the basics solid before adding extra features.
• A warning for dates: SQLite has no native date type. Dates are stored as plain text. This can be ok as long as you're consistent about format. We strongly recommend storing all dates as YYYY-MM-DD (e.g. "2026-03-09"). This format sorts and compares correctly as text, so queries like WHERE date_finished > '2026-01-01' will behave as you'd expect. If you use a different format (e.g. "March 8, 2026"), date comparisons and sorting will silently produce wrong results.
• NULL surprises: If a column is optional (e.g. book_notes), some rows may have no value, stored as NULL. Be aware that WHERE notes != 'boring' will not return rows where notes is NULL, because any comparison with NULL yields NULL, not TRUE or FALSE. Use WHERE notes IS NULL or WHERE notes IS NOT NULL to check for missing values explicitly.
• Ask AI to explain the SQL. After AI generates a query for you, ask it to break down what each part does. Understanding your queries is required—you should be able to explain any SQL in your code.
• Use parameterized queries. Always use ? placeholders rather than f-strings or string concatenation when putting user input into SQL. This prevents SQL injection and avoids bugs with special characters. AI might sometimes generate code that uses f-strings in SQL—watch out for this and fix it.
• Don't forget conn.commit(). After INSERT, UPDATE, or DELETE operations, you need to call conn.commit() to save changes to the database file. SELECT queries don't need a commit.
• Inspect your database. You can use a free tool like DB Browser for SQLite to open your .db file and see your tables and data visually. This is great for debugging.
• Close your connection. When your app exits, call conn.close() to cleanly close the database connection. (Note: There are ways to use Python's with statement for automatic cleanup, and you may see this in AI-generated code. If done correctly, this approach is fine too; just be consistent.)
• Add your .db file to .gitignore (unless you have a good reason not to, but be careful in this case). Your database file contains your personal test data and will be recreated when someone runs your app. Add *.db to your .gitignore so it isn't pushed to GitHub. (Include a note in your README about how the database is created on first run.)

Useful Resources

• Python sqlite3 documentation — the official docs for Python's built-in SQLite module
• SQLite Tutorial — beginner-friendly SQL tutorial with examples
• DB Browser for SQLite — free visual tool for inspecting your database