Databases

Your First Database with Python

A database is a super-organised place to store information permanently. Python has a built-in module called sqlite3 that lets you create and use a database with just a few lines of code — no installation needed! Think of it like a super-powered spreadsheet that lives inside your program.

CREATE TABLE — Building Your Blueprint

Before you store any data, you need to create a table — a blueprint with column names and types. Think of a table like a spreadsheet sheet: you define the columns (name, age, score) and then fill in the rows. The main types are TEXT, INTEGER, REAL (decimal), and BLOB (files).

INSERT — Adding Rows to Your Table

Now that you have a table blueprint, you can fill it with data using INSERT INTO. You can insert one row or many rows at once. Always use placeholders (?) instead of putting values directly in the string — this protects against bugs and security issues.

SELECT — Reading Your Data

Use SELECT to read data from a table. fetchall() returns every matching row as a list of tuples. fetchone() returns just the first match. You can use * to get all columns, or list specific column names.

WHERE — Filtering Rows

The WHERE clause lets you filter rows — only show rows that match a condition. You can compare with =, >, <, >=, <=, and != (not equal). Combine conditions with AND / OR.

ORDER BY — Sorting Your Results

ORDER BY sorts the rows returned by SELECT. Use ASC (ascending, low to high) or DESC (descending, high to low). You can sort by any column — numbers, text (alphabetically), or dates.

UPDATE & DELETE — Changing Your Data

UPDATE changes existing rows. DELETE removes rows. ALWAYS use a WHERE clause with both — otherwise you'll change or delete EVERY row! It's a good habit to check which rows match your WHERE first with a SELECT, then run the UPDATE or DELETE.

COUNT, SUM, AVG — Crunching Numbers

Aggregate functions calculate a single result from many rows. COUNT counts rows, SUM adds numbers up, AVG finds the average, MAX finds the biggest, MIN finds the smallest. They're used in the SELECT part of your query.

GROUP BY — Summarising Groups

GROUP BY groups rows that share a value, then you can run aggregate functions on each group. For example: total points per team, or average score per class. HAVING filters groups (like WHERE, but for groups).

JOIN — Connecting Two Tables

Real apps have multiple tables that relate to each other. JOIN lets you combine rows from two tables based on a matching column. INNER JOIN returns rows that have a match in both tables. You'll use this pattern constantly in real projects.

Mini Project — Score Tracker App

Let's build a complete score-tracking database app! It will store players and their game scores, let you add new scores, find the leaderboard, and show personal bests. This is a real mini-app using everything you've learned.