Creating & Reading Data
In the previous lesson, you defined your models as Python classes. Tables exist, columns have types, constraints are in place. But a schema without data is like a filing cabinet with labeled drawers and nothing inside. Now you'll put data in and get it back out -- reliably.
You might be thinking: "Why can't I just session.add() and move on?" Because adding without committing is like writing a check without signing it -- the bank won't process it. And if something goes wrong mid-write, you need a way to tear up that check cleanly. That's what this lesson is about: the session lifecycle that makes database writes trustworthy.
- CRUD: Create, Read, Update, Delete -- the four basic operations every database application needs
- Session: A workspace for database operations -- think of it as a shopping cart where you add items before checking out (committing)
- Commit vs Flush:
flush()sends your changes to the database temporarily (like previewing your cart).commit()makes them permanent (like clicking "Place Order"). If anything fails,rollback()empties the cart. - Rollback: Undo all uncommitted changes in the current session -- your safety net when writes go wrong
The Session Lifecycle
Before you write a single row, understand the machine you're working with. A session moves through a predictable sequence, and knowing where you are in that sequence prevents most beginner CRUD bugs.
Session Lifecycle:
open add/modify flush commit
| | | |
v v v v
+------+ +-----------+ +----------+ +----------+
| New |--->| Pending |--->| Flushed |--->| Committed|
|Session| | Changes | | (in DB | | (durable)|
+------+ +-----------+ | but not | +----------+
| permanent)|
+-----+----+
| error?
v
+----------+
| Rollback |
| (undo |
| all) |
+----------+
Here's what each stage means in plain language:
- Open: You create a session. Nothing has happened yet.
- Pending: You call
session.add(). The object is tracked in memory, but the database hasn't seen it. - Flushed: You call
session.flush(). SQLAlchemy sends the SQL to the database and the database assigns IDs -- but this is a preview, not a commitment. If the session ends without a commit, the changes vanish. - Committed: You call
session.commit(). The transaction finalizes. The data is durable -- it survives restarts, crashes, power outages. - Rollback: Something went wrong. You call
session.rollback(), and every pending or flushed change in that session is undone. Clean slate.
Why does flush exist separately from commit? Because sometimes you need the database to assign an ID (like an auto-incrementing primary key) before you can create related rows -- but you don't want to commit until everything succeeds. That's exactly what happens in the code below.
The Happy Path: Insert and Read Back
Here's the pattern you'll use hundreds of times. Create some rows, commit them, then prove they exist by reading them in a fresh session.
from datetime import date
from sqlalchemy import Column, Date, ForeignKey, Integer, Numeric, String, create_engine, event, select
from sqlalchemy.orm import Session, declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
email = Column(String(100), unique=True, nullable=False)
name = Column(String(100), nullable=False)
class Category(Base):
__tablename__ = "categories"
id = Column(Integer, primary_key=True)
name = Column(String(50), unique=True, nullable=False)
class Expense(Base):
__tablename__ = "expenses"
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
category_id = Column(Integer, ForeignKey("categories.id"), nullable=False)
description = Column(String(200), nullable=False)
amount = Column(Numeric(10, 2), nullable=False)
date = Column(Date, nullable=False, default=date.today)
engine = create_engine("sqlite:///:memory:")
@event.listens_for(engine, "connect")
def _enable_sqlite_fk(dbapi_connection, _connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
Base.metadata.create_all(engine)
with Session(engine) as session:
user = User(email="alice@example.com", name="Alice")
food = Category(name="Food")
session.add_all([user, food])
session.flush()
session.add(
Expense(
user_id=user.id,
category_id=food.id,
description="Groceries",
amount=52.50,
date=date(2024, 1, 15),
)
)
session.commit()
with Session(engine) as session:
rows = session.execute(select(Expense).where(Expense.amount >= 50)).scalars().all()
print([(r.description, str(r.amount)) for r in rows])
Output:
[('Groceries', '52.50')]
Walk through the key moments:
session.add_all([user, food])stages both objects. Nothing hits the database yet.session.flush()sends the INSERT statements to the database. Nowuser.idandfood.idhave real values (the database assigned them). But the transaction is still open -- this is the "preview your cart" step.session.add(Expense(...))uses those freshly assigned IDs. Without the flush,user.idwould still beNone.session.commit()finalizes everything. Now the data is permanent.- The second
with Session(...)block opens an entirely new session. If the data shows up here, it's genuinely committed -- not just cached in memory.
The .scalars() call deserves a quick explanation: when you run session.execute(select(Expense)), SQLAlchemy returns rows wrapped in a result object. The .scalars() call unwraps them -- think of it as opening the envelope to get the letter inside. Without it, you'd get Row objects instead of Expense objects.
(A note about that PRAGMA foreign_keys=ON listener: SQLite disables foreign key enforcement by default. The listener turns it on for every connection so your local development catches the same FK violations that PostgreSQL would catch in production. Without it, you could insert an expense pointing to a nonexistent user and SQLite would happily accept it -- a nasty surprise when you deploy.)
Read-Path Verification Checklist
Don't trust that your writes worked just because no error appeared. Trust the read:
- Write one known row with deterministic values
- Read that row in a new session
- Assert expected field values, not only row count
- Run one failing FK insert and verify the rollback path
- Repeat the read to prove the failed write left no residue
This simple loop prevents most beginner CRUD illusions -- the cases where you think data was saved but it was only in memory, or where a failed write silently corrupted your session state.
The Failure Path: Rollback in Action
You might be thinking: "When would commit actually fail?" More often than you'd hope -- duplicate emails, invalid foreign keys, network hiccups. The question isn't IF it fails, it's WHEN.
Here's what a controlled failure looks like:
with Session(engine) as session:
try:
bad_row = Expense(
user_id=9999, # invalid
category_id=1,
description="Invalid FK demo",
amount=10.00,
)
session.add(bad_row)
session.commit()
except Exception as exc:
session.rollback()
print(type(exc).__name__)
Output:
IntegrityError
The database rejects the row because user_id=9999 doesn't exist in the users table (that's your foreign key constraint doing its job). The session.rollback() call undoes everything in this session's transaction, leaving the database exactly as it was before.
Running one controlled failure now saves hours of confusing downstream debugging later. session.add() without commit is the database equivalent of "I'll save my file later." We all know how that story ends.
Use this pattern whenever a write might fail:
try:
session.add(expense)
session.commit()
except Exception:
session.rollback()
raise
Output:
(Re-raises the original exception after rolling back cleanly.)
Also avoid reusing failed session state after exception paths. Rollback first, then continue or open a new session.
You've seen both a successful insert and a failed one. Why is the failure case just as important to practice? In production, which scenario do you think happens more often -- and what happens if you don't handle it?
Query Patterns for Reading
Once data is committed, you need flexible ways to get it back. Here are the patterns you'll reach for most often:
# one row or None
one = session.execute(select(Category).where(Category.name == "Food")).scalars().first()
# many rows
many = session.execute(select(Category)).scalars().all()
# range query
march_rows = session.execute(
select(Expense).where(Expense.date >= date(2024, 3, 1), Expense.date < date(2024, 4, 1))
).scalars().all()
Output:
# one: <Category name='Food'> (or None if not found)
# many: [<Category name='Food'>]
# march_rows: [] (no March expenses in our data)
Use .first() when you expect zero or one result. Use .all() when you expect a list. If you use .first() on a query that could match many rows, you'll silently get just the first one -- which might hide bugs where your filter isn't specific enough.
Think of it this way: imagine building a user registration system. When checking if an email already exists, .first() is exactly right -- you only need to know if at least one match exists. But when listing all users in an admin panel, .all() is what you want. Picking the wrong one doesn't crash your program; it just gives you misleading results, which is worse.
Or consider a project tracker where tasks get created, assigned, completed, and archived. Every one of those operations is a CRUD operation with this same session pattern: open, add or query, commit or rollback, close.
Debugging Patterns
When things don't work as expected, check these in order:
- If reads are empty, verify commit happened. A missing
session.commit()means your data exists only in the session's memory. - If FK errors don't appear locally, verify the SQLite FK pragma is enabled. Without
PRAGMA foreign_keys=ON, SQLite silently accepts invalid foreign keys. - If one query "sometimes" works, check whether
.first()is hiding multiple matches that should have been constrained by a unique index.
Good beginner habit: print small deterministic result sets while learning. Switch to assertions once behavior is stable. This keeps debugging concrete and avoids "it looked right" mistakes.
Micro-Check Before Moving On
Before you continue to the next lesson, verify these three things:
- Can you insert one valid row and read it back in a new session?
- Can you trigger one invalid row (bad FK) and handle it with rollback?
- Can you explain why both outcomes are useful to practice?
What breaks next? Single-table CRUD is stable now. The next risk is cross-table truth: relationships and query shape.
Try With AI
Prompt 1: CRUD Sanity Check
Given this SQLAlchemy CRUD code, find correctness risks:
- missing commit
- missing rollback
- FK misuse
- wrong assumptions about .first() vs .all()
Provide corrected code.
What you're learning: You're developing an eye for the subtle bugs that slip past "it runs without errors." AI is good at spotting patterns like missing rollbacks or commit-before-flush issues -- this is AI as code reviewer, catching what human eyes skip during the "it works on my machine" phase.
Prompt 2: Add a Safe Read Pattern
Add a function list_expenses_over(amount) that returns
[(description, amount)] using SQLAlchemy 2.0 select() style.
Include one test query example and expected output.
What you're learning: Writing query functions that return clean data structures (not raw SQLAlchemy objects) is how production code stays testable. You're practicing the pattern of wrapping database access in functions with predictable inputs and outputs.
Prompt 3: Apply to Your Domain
Pick a project you're building. Write the CRUD operations for your main entity:
1. A create function that inserts one row with validation
2. A read function that queries by one filter condition
3. A failure case that tests what happens with invalid data
Show the expected output for each operation.
What you're learning: CRUD is the bread and butter of every database app. The session, add, commit/rollback pattern you're learning here is the same pattern used in production systems serving millions of users. Master it once, use it everywhere.