Skip to content

Week 10: SQL and databases with SQLite

~9 min

This week's goal

This week you store data in a database instead of a JSON file. You use SQLite, a database that lives in one file and needs no server. You learn the basic SQL statements for creating a table, inserting, reading, changing, and deleting rows, and the aggregate queries that count and total values. You then connect them to Python with the standard sqlite3 module.

The main text is the databases page and lesson 2 of the backend part of the Python course, SQLite CRUD.

Day-by-day (about 1.5-2 hours per day)

Day 1. Read the first half of the databases page. Focus on what a table, a row, and a column are, and why a database is useful when many parts of a program need the same data. Then read the SQLite introduction and install or open the sqlite3 command line tool. Check that you can type .help and see a list of commands.

Day 2. Create a database file expenses.db with a table. In the command line tool, run CREATE TABLE expenses (id INTEGER PRIMARY KEY, amount REAL, category TEXT, note TEXT);. Read the CREATE TABLE reference to see what each part means. Insert two rows with INSERT INTO, and read them back with SELECT * FROM expenses;.

Day 3. Read the SQLite datatypes page. SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Then read the SELECT reference and try WHERE, ORDER BY, and LIMIT. Write three queries that show only the food expenses, the five largest expenses, and the expenses from one note.

Day 4. Practise changing and removing rows. Use UPDATE to fix one amount, and DELETE to remove one row. Always write a WHERE clause first, and run the SELECT with the same WHERE before you run the UPDATE or the DELETE. Forgetting the WHERE changes or removes every row.

Day 5. Read aggregate functions and GROUP BY. Write queries that return the number of expenses, their total, and the total for each category. Check the totals against your own sum. Then add an index with CREATE INDEX on the category column, and note that it helps queries that search by category.

Day 6. Connect the database to Python. Read the sqlite3 module documentation, especially the part on placeholders. Write expense_db.py with a function that inserts an expense using a ? placeholder, and a function that returns the total for a category. Commit the file to your repository from week 6. Do not put user input directly into an SQL string. Use placeholders.

Day 7 is a rest day.

Deliverable

expense_db.py creates an expenses table in expenses.db, inserts rows with placeholders, and prints three results: the total of all expenses, the total for each category, and the five largest expenses. Save the three SQL queries in a file queries.sql, and commit both files.

Check yourself

  1. What does a primary key do?
  2. Why does a DELETE with no WHERE clause remove every row?
  3. What are SQLite's five storage classes?
  4. What does SUM do, and what does GROUP BY add to it?
  5. Why should you use a ? placeholder instead of building the SQL string with an f-string?
Answers
  1. A primary key gives each row a unique identifier. SQLite uses it to find that row quickly, and it stops two rows from sharing the same value.
  2. Without a WHERE clause, the DELETE applies to all rows in the table. That is why you should test the same condition with SELECT first.
  3. NULL, INTEGER, REAL, TEXT, and BLOB.
  4. SUM adds the values in a column. GROUP BY splits the rows into groups, so you get one total per group, such as one total per category.
  5. A placeholder passes the value separately from the SQL text, so the value cannot change the structure of the query. An f-string puts the value into the text, which can allow SQL injection if the value comes from a user.

Sources