CRUD - Read Record

Reading records is about querying the database.

diagram the four operations, and where each one actually writes mermaid

  flowchart LR
  subgraph Create
    C1["obj = Model(...)"] --> C2["db.session.add(obj)"] --> C3["commit()"]
  end
  subgraph Read
    R1["Model.query.get / filter_by"] --> R2["objects, already in the session"]
  end
  subgraph Update
    U1["obj = query...first()"] --> U2["obj.field = new_value"] --> U3["commit()"]
  end
  subgraph Delete
    D1["obj = query...first()"] --> D2["db.session.delete(obj)"] --> D3["commit()"]
  end
  R2 -.->|"no add() needed -- it is already tracked"| U2

Only two of the four need an explicit add. Reads return objects already managed by the session, and an update is just assigning to an attribute -- SQLAlchemy notices the change and writes it on the next flush. What every write shares is the commit at the end; without it the transaction is discarded when the request finishes.

Basic lookups

python

user = User.query.get(1)

• Returns the user with primary key 1 or None

Filter by column

python

user = User.query.filter_by(username="ravi").first()

• first() returns one row or None

Get all rows

python

users = User.query.all()

Ordering

python

users = User.query.order_by(User.username.asc()).all()

Common pattern: 404 if not found

In routes, you often want “not found” to return a 404.

Flask-SQLAlchemy provides:

python

user = User.query.get_or_404(user_id)

That’s very convenient for web apps.

Performance note

all() loads everything into memory.

For very large tables, use pagination (limit/offset) or streaming approaches.