SQLAlchemy is the bridge between two worlds you already know separately: Python objects on one side, MySQL tables on the other. You describe your data once, as a Python class, and SQLAlchemy keeps the two worlds in sync — turning your method calls into SQL and the database's rows into objects. But it is a bridge, not a wall: the SQL is still there underneath, and everything you do in SQLAlchemy should be something you could translate back into the SQL you wrote by hand. When you can move fluently in both directions, the ORM stops being magic and becomes a tool.
This article is the working reference for SQLAlchemy in this course. It covers the three things every SQLAlchemy program is made of: models (the Python classes that define your tables), the session (the workspace where changes are staged and committed), and queries (reading, adding, changing, and deleting data). It assumes you have read the databases article and the ORM article — you should already be able to write the raw SQL version of everything here.
| Level | What it covers | When to read it |
|---|---|---|
| Basic idea | Defining a model; how the session works; the core patterns for reading, adding, updating, and deleting | Right now, with your editor open. |
| At a deeper level | What each part of the syntax actually does; the mistakes that cost the most time; how to debug an ORM query | Once you have written your first few queries and something has gone wrong. |
| At the deepest level | What SQLAlchemy is doing behind the scenes; transactions; where this leads in the rest of the stack | When the patterns feel routine and you want to understand the machinery. |
1 Models: A Python Class That Is a Table
In SQLAlchemy, you describe a database table by writing a Python class. This class is called a model. Here is the model for the students table you have been querying all along:
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), nullable=False)
grade = db.Column(db.Integer)Read it line by line:
<strong>class Student(db.Model)</strong>— this class describes a table. By default the table is named after the class.<strong>id = db.Column(db.Integer, primary_key=True)</strong>— an integer column that uniquely identifies each row. The database fills it in automatically.<strong>name = db.Column(db.String(80), nullable=False)</strong>— a text column holding up to 80 characters, which must never be empty.<strong>grade = db.Column(db.Integer)</strong>— an integer column, allowed to be empty.
One class, one table. One attribute, one column. One object, one row. That three-part correspondence is the entire idea of the ORM.
The model is where the constraints you met in SQL live in Python form. primary_key=True is the PRIMARY KEY constraint. nullable=False is NOT NULL. db.String(80) is the column's type and maximum size — which, as you know from the binary article, is ultimately a statement about how many bytes each value may use. If your code tries to save a Student with no name, the database refuses, exactly as it refused a bad INSERT in December. The model did not create new rules; it wrote down the same rules in a place your Python code can see.
Notice what this buys you. The class Student is now a real Python type: your editor can autocomplete student.grade, and a typo like student.grdae fails loudly instead of silently. Compare the raw-SQL world, where a misspelled column name inside a query string is just characters until the database rejects it.
In a Flask project, every question about the data — what fields exist, what types they are, what is required — is answered by reading the models file. When you plan a project, writing the models is the first coding step, because everything else (queries, forms, pages) depends on the shape declared there. A vague model produces a vague program.
How does defining a class create a table? Through a pattern called declarative mapping. db.Model is a base class with machinery that inspects every class inheriting from it, collects the db.Column attributes, and builds an internal description of the corresponding table. When you ask SQLAlchemy to create the database tables, it walks these descriptions and generates the CREATE TABLE statements — DDL you could have written yourself.
This raises a real professional problem the class-based approach must eventually face: what happens when the model changes after the table already holds data? Adding a column to the Python class does not add it to the existing MySQL table — the two are now out of sync. Professionals solve this with migrations: small, versioned scripts that alter the live table to match the new model, without losing data. You will not write migrations in this course, but you should recognise the problem, because "I changed my model and now everything is broken" is this problem wearing a mask.
2 The Session: Your Workspace
Every interaction with the database goes through db.session. The session is a workspace between your Python code and MySQL. Changes you make are staged in the session first; nothing reaches the database until you commit.
db.session.add(Student(name='Ana', grade=91)) # staged, not saved db.session.add(Student(name='Tomek', grade=78)) # staged, not saved db.session.commit() # NOW both are saved
Think of it like a shopping basket. add() puts items in the basket. commit() is the checkout — everything in the basket is saved at once. Until checkout, you can keep adding, or abandon the basket entirely with db.session.rollback(), and the database is untouched.
The forgotten commit() is the single most common SQLAlchemy bug in student projects, and it is worth understanding why it is so sneaky. The code runs. No error appears. If you inspect the object in the same program, it even looks right — the session shows you your own staged changes. Then the program ends, the session disappears, and the database was never told anything. The symptom appears later and elsewhere: data that "saved" yesterday is missing today.
The rule: any operation that changes data — add, update, delete — is incomplete until <strong>db.session.commit()</strong> runs. Reading data needs no commit, because nothing changed.
After committing, prove the change reached the database: run a fresh query for the row and print it, or check in the MySQL terminal directly. "My code ran" is not evidence; a row you can query back is. This is the same standard as the mini data project's requirement that both query versions print their results — the output is the evidence, not the absence of errors.
The session exists because of transactions. A transaction is a group of changes that the database applies atomically — all of them, or none of them. commit() ends the current transaction by making its changes permanent; rollback() ends it by discarding them. The classic illustration is a bank transfer: subtract from one account, add to another. If the program crashes between the two steps, atomicity guarantees the database never shows the halfway state where money has vanished.
The session also does bookkeeping you cannot see: it tracks every object it has loaded (the identity map, which guarantees that querying the same row twice gives you the same Python object) and it notices when you modify a loaded object's attributes (change tracking). That second ability explains something in the next sections that would otherwise look like magic: why updating a row requires no explicit "update" call at all.
3 Reading Data: select, where, order_by
Reading data follows one pattern: build a select(), execute it through the session, unwrap the results.
from sqlalchemy import select
# All students
students = db.session.execute(select(Student)).scalars().all()
# Students with grade above 80
high = db.session.execute(
select(Student).where(Student.grade > 80)
).scalars().all()
# Sorted by name
by_name = db.session.execute(
select(Student).order_by(Student.name)
).scalars().all()
for s in high:
print(s.name, s.grade)The mapping to SQL is direct: select(Student) is SELECT * FROM students, .where(…) is WHERE, .order_by(…) is ORDER BY. The result is a list of Student objects — not tuples — so you read fields by name: s.grade, not s[1].
Three details in this pattern deserve a closer look:
<strong>Student.grade > 80</strong>is not a Python comparison. Normally,>immediately produces True or False. Here,Student.gradeis a column object, and comparing it builds a description of a condition — the WHERE clause — to be evaluated later, by the database, against every row. Nothing is compared when this line runs; a query fragment is constructed.- Clauses chain, exactly like SQL clauses combine.
select(Student).where(Student.grade > 80).order_by(Student.name)is one query with a WHERE and an ORDER BY. Conditions can also stack: two.where()calls, or.where(and_(…)), mirror SQL's AND. - The endings differ by what you want back.
.scalars().all()gives a list of objects..scalars().first()gives the first match orNone— the usual choice when you expect one row. Fetching one specific row by primary key has a shortcut:db.session.get(Student, 5).
Before executing an ORM query, say what SQL it will generate and what will come back: how many objects, in what order. If the actual result differs, either your mental translation to SQL is wrong or your expectation of the data is — and finding which is exactly the paper-first discipline from December, one layer up.
select(Student).where(Student.grade > 80) is an example of a query builder: Python objects that assemble a data structure representing a SQL statement, which is only rendered into SQL text and sent when execute() runs. You can see this directly — printing the select object displays the SQL it would generate, with the value 80 replaced by a parameter placeholder.
That placeholder is the injection protection mentioned in the ORM article, visible up close. The SQL text and the values are sent to MySQL separately: the statement with placeholders, then the values to fill them. A value can never be misread as SQL, because it never travels inside the SQL. This is the parameterised-query pattern, and the ORM applies it to every query automatically — which is why building queries with the ORM is structurally safer than gluing strings, not just more convenient.
4 Writing Data: Add, Update, Delete
The three writing operations, each followed by the commit that makes it real:
# ADD — create an object, add it, commit
db.session.add(Student(name='Kasia', grade=88))
db.session.commit()
# UPDATE — fetch the object, change the attribute, commit
s = db.session.execute(
select(Student).where(Student.name == 'Kasia')
).scalars().first()
s.grade = 95
db.session.commit()
# DELETE — fetch the object, delete it, commit
db.session.delete(s)
db.session.commit()Note what UPDATE looks like: there is no update method. You fetch the object, assign to its attribute with ordinary Python, and commit. The session noticed the change and generated the UPDATE statement itself.
Translate each pattern back to the SQL you know, and check the safety properties along the way:
| ORM pattern | SQL generated (approximately) | Where is the WHERE? |
|---|---|---|
db.session.add(obj) + commit | INSERT INTO students (name, grade) VALUES (…); | Not needed — INSERT creates a row. |
| Fetch object, change attribute, commit | UPDATE students SET grade = 95 WHERE id = …; | Generated from the object's primary key — it targets exactly that row. |
db.session.delete(obj) + commit | DELETE FROM students WHERE id = …; | Same — the primary key pins the DELETE to one row. |
This is the ORM answer to December's most important warning. In raw SQL, forgetting a WHERE on a DELETE destroys the table. In the fetch-then-delete pattern, the WHERE is generated from the object's identity — you cannot forget it, because you never wrote it. Deleting everything is still possible in SQLAlchemy, but it requires deliberately writing a bulk operation; it cannot happen by omission.
The no-update-method design is the session's change tracking at work. When a query loads a Student, the session records the values it arrived with. At commit time, the session compares each loaded object's current attributes against that record, and for every difference, generates an UPDATE for exactly the changed columns of exactly that row. This is called the unit of work pattern: you declare what the final state should be by mutating objects; the session computes the minimal set of SQL statements to make the database match.
It follows that which object you mutate matters. Changing an object the session is tracking schedules an UPDATE; creating a fresh Student(name='Kasia') with the same name and changing that one schedules nothing — the session has never seen it, and add()-ing it would INSERT a duplicate rather than update the original. When an "update" mysteriously creates a second row instead, this is almost always the cause: the code modified a new object instead of the fetched one.
5 When It Goes Wrong: Debugging ORM Code
When a SQLAlchemy query misbehaves, the failures fall into three groups, and the first debugging move is deciding which group you are in:
- Python errors before any SQL runs. A misspelled attribute (
Student.grdae), a missing import, wrong syntax. The traceback points at your Python. The database was never contacted. - Database errors after SQL runs. The query executed and MySQL refused it — a constraint violation, a NULL in a NOT NULL column. The error message names the constraint. Your SQL knowledge reads these directly.
- No error, wrong result. Zero rows when you expected some; data that did not save; a duplicate row. Nothing failed loudly, so you must compare what happened with what you predicted.
Groups one and two come with a message that names the problem. Group three is where real debugging happens.
For group three, work through the usual suspects in order — these cover nearly every silent failure in student projects:
- Did you commit? The classic. Data changed in the session, never in the database.
- Are you mutating the tracked object? Fetched one object but modified a freshly created one — see section 4. Symptom: duplicates, or updates that never appear.
- Is the query the one you meant? Print the select object and read its SQL. Is the WHERE what you intended?
==where you wrote=by SQL habit is a Python syntax error; a condition on the wrong column is not. - Is the data what you assume? Bypass Python entirely: run the equivalent query in the MySQL terminal. If SQL agrees with the ORM, the "bug" is in your expectation of the data, not in the code.
The most powerful ORM debugging technique is one you already own: drop below the abstraction. Whenever ORM behaviour is confusing, run the raw SQL version by hand and compare. Two possible outcomes, both useful — the results match (your data model of the world is wrong; investigate the data) or they differ (your ORM code does not say what you think it says; investigate the translation). This move is only available because you learned the layer underneath. Use it.
Professionals formalise the second-opinion technique with echo logging: SQLAlchemy can be configured to print every SQL statement it sends, as it sends it. Reading the echo log of a running program is a small revelation the first time — you watch your Python turn into SQL statement by statement, commits become COMMIT, and the shape of the unit of work becomes visible. When a program is slow, the echo log also exposes a famous ORM trap: code that innocently loops over objects while each iteration triggers its own query, turning one intended query into hundreds (the "N+1 problem").
The larger lesson is about debugging layered systems in general, and it will reappear for the rest of your time in computing: when a layer misbehaves, observe the boundary between it and the layer below. What did this layer actually emit? Is that what it should have emitted? The same move debugs an ORM (inspect the generated SQL), a web page (inspect the HTTP requests), and a compiler (inspect the generated code). Learning SQLAlchemy properly is partly learning this move in its first natural habitat.
- Write a model class
Bookwith an auto-assigned id, a required title of up to 120 characters, and an optional integer year. Explain what each argument in yourdb.Columncalls does. - State the three-part correspondence at the heart of the ORM: what does a class map to? An attribute? An object?
- A classmate's program adds a student, prints "saved!", and exits — but the row is not in the database the next day. No error ever appeared. What is the most likely cause, and why did it produce no error?
- Explain the shopping-basket model of the session. What do
add(),commit(), androllback()each correspond to? - Write the SQLAlchemy query for: all students with a grade of at least 60, sorted by grade. Then write the raw SQL it generates. Label which method corresponds to which clause.
- In
select(Student).where(Student.grade > 80), the expressionStudent.grade > 80does not produce True or False. What does it produce, and when is the comparison actually performed? - Updating a row in SQLAlchemy involves no update method. Describe the three-step pattern, and explain how the session knows to generate an UPDATE statement.
- In December you learned that DELETE without a WHERE clause is catastrophic. Explain why the fetch-then-
db.session.delete(obj)pattern makes this specific mistake impossible by omission. - A program meant to update Kasia's grade instead creates a second Kasia row. Using the idea of tracked objects, explain what the code probably did wrong.
- Your ORM query returns zero rows and you are sure matching data exists. Describe two independent checks you would perform — one above the abstraction and one below it — and what each outcome would tell you.