A database is a program whose entire job is to store data and answer questions about it — quickly, permanently, and for any amount of data. When your records live in a Python list, they disappear the moment your program ends, and every search means writing another loop. A database keeps the data safe on disk and gives you a language, SQL, for asking questions directly: find these rows, add this row, change that one, remove this one. Learning to write SQL by hand — before any framework writes it for you — is how you build the mental model that everything later in this course depends on.
This article covers your first week with databases: what a database is and why a file or a list is not enough, how to connect to the school MySQL server from the Python terminal, and the four fundamental operations — SELECT to read data, INSERT INTO to add it, UPDATE to change it, and DELETE to remove it. No Flask, no HTML, no ORM yet. Just you, Python, and the database.
| Level | What it covers | When to read it |
|---|---|---|
| Basic idea | Why databases exist; what each SQL keyword does; how to read, add, change, and delete data | Right now, before or during our first database class. |
| At a deeper level | How the pieces of a query fit together; common mistakes and edge cases; what makes a query safe or dangerous | Once you have run a few queries yourself. |
| At the deepest level | What the database is doing behind the scenes; why SQL looks the way it does; where these ideas lead | When the basics feel solid and you want to know why. |
1 Why Not Just Use a List?
Imagine 1,000 student records stored in a Python list. You can search it with a loop, add records with .append(), update them by index, and delete them with .remove(). It works. So why do databases exist?
Three reasons:
- Persistence. When your program ends, the list is gone. A database stores data on disk — it is still there tomorrow, next week, and after a crash.
- Speed at scale. Searching 1,000 records with a loop is fine. Searching 10 million is not. Databases are engineered to answer questions about huge amounts of data in milliseconds.
- Structure. A database enforces the shape of your data. Every student record has the same fields, with the right types. In a list, nothing stops you from appending garbage.
A database is not a fancy list. It is a separate program — a server — that manages the data, and your Python code talks to it by sending questions written in SQL.
You might ask: why not just save the list to a file? Files solve persistence, but nothing else. To find one record in a file, your program still has to read the whole file and loop through it. To update one record, you typically rewrite the entire file. And if two programs write to the same file at the same time, the data can be corrupted.
A database solves all three at once. It stores data on disk (persistence), builds internal structures called indexes so it can find records without scanning everything (speed), and coordinates access so multiple users can read and write safely at the same time (which is exactly what a web application needs — many users, one database).
Everything you learned about lists and dictionaries still matters. When your Python program receives results from a database, those results arrive as Python data structures you already know how to work with. The database handles storage and searching; your Python code handles logic and presentation. Knowing where that boundary sits is one of the most important ideas in this course.
MySQL is a relational database management system (RDBMS). "Relational" refers to a mathematical model proposed by Edgar Codd in 1970: data is organised into tables (relations) of rows and columns, and questions about the data are expressed in a declarative language. Declarative means you describe what you want — "students with a grade above 80" — and the database decides how to find it. Compare this to your Python loop, which is imperative: you spell out every step of the search yourself.
This separation between what and how is why databases can be fast. The database engine contains a query optimiser that chooses the best strategy for each query — use an index, scan the table, reorder the conditions — without you changing a line of SQL. The same query that works on 1,000 rows works on 100 million; the engine adapts the strategy, not you.
2 Talking to MySQL from Python
In this course you connect to the school MySQL server directly from the Python terminal. There is no web page, no Flask, no framework — just Python sending SQL to the database and printing what comes back.
import mysql.connector
conn = mysql.connector.connect(
host="school-db-server",
user="your_username",
password="your_password",
database="school"
)
cursor = conn.cursor()
cursor.execute("SELECT name FROM students WHERE grade > 80;")
for row in cursor.fetchall():
print(row)Four things happen here: you connect to the server, you get a cursor (the object that sends queries and receives results), you execute a SQL query as a string, and you fetch the results back into Python.
Notice that the SQL query is just a Python string. Python does not understand SQL — it passes the string to the MySQL server, and the server understands it. This is why a typo in your SQL does not produce a Python syntax error. Python is perfectly happy with the string; the error comes back from the database, after the query has been sent. Learning to read those database error messages is a skill in itself.
The results come back as a list of tuples. A query for one column returns tuples with one item each: ('Ana',). A query for three columns returns three-item tuples. Everything you know about looping over lists and unpacking tuples applies directly.
The connection between your Python program and the MySQL server is a network conversation, even when both run on the same machine. Your query travels to the server as a message over a protocol; the server parses it, plans it, executes it, and streams the result rows back. This client-server architecture is why one database can serve an entire school at once — every user's program is just another client holding a connection.
In January you will meet SQLAlchemy, an ORM (object-relational mapper) that generates SQL for you from Python code. The reason we do not start there is simple: an ORM is a translator, and you cannot debug a translator if you do not speak the language it is translating into. Everything you hand-write this week is the language that SQLAlchemy will be writing on your behalf later.
3 Reading Data: SELECT, FROM, WHERE
The most important query in SQL reads data. Take it apart word by word:
SELECT name FROM students WHERE grade > 80;
- SELECT name — which columns you want back. Here, just the name.
- FROM students — which table to look in. A table is like one spreadsheet inside the database.
- WHERE grade > 80 — which rows to include. Only rows where this condition is true.
- ; — the semicolon marks the end of the statement.
Read it as a sentence: "Give me the name of every row in the students table where the grade is above 80." The WHERE clause is optional — leave it out and you get every row. SELECT * means "all columns."
The WHERE clause works like the conditions you already know from Python — but note the differences in syntax:
| Meaning | Python | SQL |
|---|---|---|
| Equal to | == | = |
| Not equal to | != | != or <> |
| Text value | "Ana" | 'Ana' (single quotes) |
| Combine conditions | and, or | AND, OR |
The single = for equality is the most common early mistake for Python programmers. In SQL, WHERE grade = 80 is a comparison, not an assignment.
SELECT * is convenient for exploring, but name your columns in real queries. When a table has 50 columns, SELECT * drags all of them across the network when you needed two. Naming columns also makes your intent readable: the query says what you actually wanted.
Although you write SELECT first, the database does not process the query in the order you write it. Logically, it starts with FROM (find the table), then applies WHERE (filter the rows), and only then applies SELECT (pick the columns from the surviving rows). This explains behaviour that otherwise seems odd — for example, why the WHERE clause can filter on a column that you did not SELECT: the filtering happens before the column-picking.
The WHERE clause is an example of predicate filtering: the condition is a predicate — a statement that is true or false for each row — and the database keeps exactly the rows where it is true. The equivalent Python is a loop with an if, or a list comprehension with a condition. Same idea, expressed declaratively instead of imperatively.
4 Adding Data: INSERT INTO
INSERT INTO adds a new row to a table:
INSERT INTO students (name, grade) VALUES ('Ana', 91);- INSERT INTO students — which table receives the row.
- (name, grade) — which columns you are providing values for.
- VALUES ('Ana', 91) — the values, in the same order as the columns.
The columns and the values match by position: the first value goes into the first named column, the second into the second. Text values go in single quotes; numbers do not.
What about columns you did not mention? If the table has an id column and you did not provide one, the database fills it in — most tables use an auto-increment id, meaning the database assigns the next number automatically. This is one of the ways the database enforces structure: you cannot accidentally create two rows with the same id.
If you provide a value of the wrong type — text where a number belongs, or a value that violates a rule the table defines — the database rejects the entire INSERT with an error, and no row is added. Compare this to a Python list, which happily appends anything. The database's strictness is a feature: bad data is refused at the door instead of discovered weeks later.
The rules a table enforces are called constraints. A primary key constraint guarantees every row has a unique identifier. A NOT NULL constraint means a column must always have a value. Type constraints ensure a grade column only holds numbers. Together, constraints are the database's version of the validation code you would otherwise have to write by hand — except they can never be forgotten or bypassed, because they live in the database itself, not in any one program.
One caution for later: when a query includes values typed by a user, never build the SQL string by gluing the user's text into it. That pattern creates a vulnerability called SQL injection — a user can type SQL of their own and have your database run it. The safe pattern (parameterised queries) is built into the connector, and it is one of the reasons ORMs like SQLAlchemy exist. For this week, with your own values typed by you, string queries are fine — but remember this boundary.
5 Changing and Deleting Data: UPDATE and DELETE
UPDATE changes existing rows; DELETE removes them. Both use a WHERE clause to say which rows.
UPDATE students SET grade = 95 WHERE name = 'Ana'; DELETE FROM students WHERE name = 'Ana';
- UPDATE students SET grade = 95 — in the students table, set the grade column to 95…
- WHERE name = 'Ana' — …but only in rows where the name is Ana.
- DELETE FROM students WHERE … — remove the rows that match the condition.
In both statements, the WHERE clause is what limits the damage. That leads to the most important warning in this article.
DELETE FROM students; — with no WHERE clause — deletes every row in the table. Not a warning, not a confirmation dialog. Gone. The same is true of UPDATE: UPDATE students SET grade = 0; sets every student's grade to zero. The WHERE clause is not decoration. It is the only thing standing between "change this one row" and "change all of them."
Professional developers protect themselves from destructive queries with a simple habit: SELECT before you UPDATE or DELETE. Before running a destructive statement, run a SELECT with the identical WHERE clause:
-- Step 1: check which rows will be affected SELECT * FROM students WHERE name = 'Ana'; -- Step 2: only if the result is what you expected DELETE FROM students WHERE name = 'Ana';
If the SELECT returns one row and you expected one row, proceed. If it returns forty rows — or every row — you have just discovered a problem with your WHERE clause at zero cost. The SELECT is free; the DELETE is not.
Also worth knowing: UPDATE and DELETE report how many rows they affected. If you expected to change one row and the database says "3 rows affected," stop and investigate. Row counts are evidence, exactly like expected outputs in testing.
Real database systems have deeper safety nets. A transaction lets you group statements so that they either all take effect or none do — and until you commit, the changes can be rolled back. Regular backups mean that even a catastrophic mistake can be undone by restoring yesterday's data. And access permissions can be configured so that most users simply cannot run DELETE on important tables at all.
None of these replaces the WHERE clause discipline — they are layers behind it. This layered approach is called defence in depth: no single safeguard is trusted to be perfect, so several independent ones stand between a mistake and disaster. You will see the same principle again when we discuss why ORMs make some mistakes structurally harder to make.
6 Write the Query on Paper First
In class, you will write every query on paper before you type it. This is not busywork. Before running a query, you write down:
- The complete query, every clause, every keyword
- What you expect back: how many rows, roughly? Which columns?
Then you run it and compare. If the actual result differs from your prediction, something in your mental model is wrong — and finding out exactly where is the whole point.
This is the same predict-before-run discipline you used with Python code earlier in the course, applied to data. Typing queries directly from examples produces a student who can copy SQL but cannot construct it — and, worse, cannot debug it, because debugging requires knowing what the query should have returned.
It also matters for what comes in January. When you meet SQLAlchemy, you will be shown an ORM query and asked: which part of this maps to SELECT? To WHERE? To ORDER BY? That recognition is only possible if the SQL is already in your hands, not just in your notes.
A query that returns the wrong result is debugged the same way as Python code: reduce it. Remove the WHERE clause — do you get all the rows you expect? Add one condition back at a time. The clause where the result goes wrong is where the bug lives. This is the same isolate-the-problem strategy from the debugging articles, applied to SQL.
The paper-first habit is a form of what learning scientists call generation: producing an answer from memory before checking it builds far stronger retention than reading or copying the same material. It is also why the formative quiz asks you to write queries from plain-English descriptions with AI off — translation from intent to SQL is the actual skill, and it is precisely the step that copying (from an example, or from an AI) skips.
Professionals do a version of this too. Before running a query against a production database, an experienced developer states what they expect — "this should touch about 200 rows" — and treats any large deviation as a stop signal. The habit you are building this week is not a classroom exercise scaled down; it is professional practice scaled to fit.
- Give three reasons why a database is a better place for 1,000 student records than a Python list. Which of these problems would saving the list to a file solve, and which would it not?
- Take apart the query
SELECT name FROM students WHERE grade > 80;word by word. What does each keyword do? - Write a query that returns the name and grade of every student whose grade is exactly 100. What is the difference between
=in SQL and==in Python? - Write an INSERT INTO statement that adds yourself to the students table with a grade of your choosing. What happens if you provide text where the table expects a number?
- A student runs
DELETE FROM students;intending to delete one test record. What actually happens, and what single habit would have caught the mistake before any data was lost? - You run an UPDATE expecting to change one row, and the database reports "12 rows affected." What went wrong, and what should you do next?
- Explain in your own words why we write queries on paper and predict the result before running them. What does a wrong prediction tell you that a correct one does not?
- Why does this course teach raw SQL before introducing SQLAlchemy? What would be lost if the order were reversed?