In this lab you will build a complete todo management system using Python's built-in sqlite3 module. All data will live in an in-memory database โ no file is written to disk.
Step 1 โ Connect and create the schema
Use sqlite3.connect(":memory:") to create an ephemeral database. Then create a todos table:
Column
Type
Constraint
id
INTEGER
PRIMARY KEY AUTOINCREMENT
title
TEXT
NOT NULL
done
INTEGER
DEFAULT 0
done = 0 means pending; done = 1 means completed. SQLite has no native boolean type โ integers are the convention.
Step 2 โ Insert at least 5 todos
Use executemany() to insert a batch of todos. Include a mix of done and pending items so later queries have data to filter.
Always use parameterized queries (? placeholders). Never use f-strings or string concatenation with SQL โ that opens the door to SQL injection.
Step 3 โ Print all todos
Fetch every row and print it in a readable format. Use conn.row_factory = sqlite3.Row so columns are accessible by name instead of position index.
Tip: Set row_factory immediately after connect(), before any queries, so every cursor you open from that connection inherits it.
Step 4 โ Print only pending todos
Filter with WHERE done = 0:
Step 5 โ Mark a todo as done
Write a function that updates the done flag for a given id and reports whether the row actually existed:
Step 6 โ Print the done / pending count
Use a single aggregation query with GROUP BY:
Putting it all together
Your final script should produce output similar to: