Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 72 ยท Databases

Queries and CRUD Views

Query a database from Flask with SQLAlchemy 2 select(), then build create, read, update and delete views with get_or_404, redirects and pagination.

Once your models exist, an application spends most of its life doing four things: creating rows, reading them, updating them and deleting them. These four operations, known as CRUD, sit behind almost every form, list page and API endpoint you have ever used. This lesson shows how to express them with Flask-SQLAlchemy and how to turn them into views.

You will first learn to ask questions of the database with db.select(). Then you will wire those queries into routes that follow the Post/Redirect/Get pattern, and finally you will split long lists into pages with db.paginate(). If you have not met models yet, read Flask-SQLAlchemy Models first.

From routes to SQL

Every CRUD view is a thin layer between an HTTP request and one SQL statement. The mapping is almost mechanical:

flowchart LR
    A["GET /notes"] --> B["SELECT ..."]
    C["POST /notes"] --> D["INSERT ..."]
    E["POST /notes/1/update"] --> F["UPDATE ..."]
    G["POST /notes/1/delete"] --> H["DELETE ..."]
    B --> I[("notes table")]
    D --> I
    F --> I
    H --> I

Each CRUD view runs one kind of SQL statement against the table.

HTML forms can only send GET and POST, so the examples here use POST for updates and deletes. In a JSON API you would use PUT, PATCH and DELETE instead (see Designing a REST API).

Reading data with select()

In SQLAlchemy 2 style you build a statement with db.select(Model), add clauses by chaining, and then hand the statement to the session. Nothing touches the database until you execute it.

The most convenient way to execute is db.session.scalars(stmt). It returns the first column of each row, which for select(Book) is the Book object itself. You can loop over the result or call .all() and .first() on it. When you need one value rather than objects, use db.session.scalar(stmt).

Python Selecting, filtering, ordering and counting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Study the pieces:

  • where() and filter_by(). where(Book.year < 1985) takes any SQL expression built from column attributes. filter_by(read=False) is a shortcut for simple equality tests using keyword arguments. Several where() calls are joined with AND; use or_() and and_() from sqlalchemy for explicit logic.
  • session.get(Model, pk) looks up by primary key and returns None when nothing matches. It also checks the session's identity map first, so a repeated lookup may not hit the database at all.
  • .first() returns the first object or None. If you expect exactly one row, .one() raises an error when there are zero or several, and .one_or_none() allows zero.
  • order_by(), limit() and offset() map directly to the SQL clauses of the same name. Use .desc() on a column for descending order.
  • Selecting columns. select(Book.title, Book.year) returns tuple-like rows, which is cheaper than building full objects when you only need a couple of fields.

You will still see the older Book.query.filter_by(...).all() style in many tutorials. Flask-SQLAlchemy keeps it working, but it is marked legacy. Use db.select() in new code: it is the same API SQLAlchemy itself documents, and it works the same outside Flask.

Never build SQL from strings

Filters accept Python values and send them to the database as bound parameters, so user input can never change the shape of the query. This is your protection against SQL injection. Do not assemble a WHERE clause with an f-string. If you ever need raw SQL, wrap it in sqlalchemy.text() and pass values as named parameters. The same idea appears in Using sqlite3 with Flask.

Creating, updating and deleting

Changes go through the session, which collects them like a shopping basket and writes them in a single transaction when you call commit().

Goal Code
Create db.session.add(obj) then db.session.commit()
Update Change attributes on a loaded object, then commit()
Delete db.session.delete(obj) then commit()

Updating needs no special call. The session notices that a loaded object changed ("dirty") and emits an UPDATE at commit time. Only the changed columns are sent.

If something fails halfway, call db.session.rollback() so the session returns to a usable state. Flask-SQLAlchemy also removes the session at the end of each request, so unfinished work from one request never leaks into the next.

A complete CRUD set of views

Here is the full set for a small Note model. Notice three habits that every CRUD view should share:

  1. db.get_or_404(Model, id) loads a row or aborts with a 404 page, so you never write if note is None by hand.
  2. Validate before you write. An empty title is rejected with abort(400). Proper form validation is covered in Validating Input Without Libraries.
  3. Redirect after a successful POST (Post/Redirect/Get). The browser then shows a normal GET page, so pressing refresh does not submit the form again. Status 303 See Other is the precise code for this. See Handling HTML Forms for the full pattern.
Python Create, read, update and delete through the test client

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The update view only changes the fields that arrived in the form: the second call sends just body, so the title stays "Buy milk". The final line shows that the deleted note now answers with 404, and so does an id that never existed.

Never delete or change data from a GET route. Browsers, link previewers and search crawlers follow GET links freely, so a link such as /notes/2/delete could wipe your data. Use POST (or DELETE in an API), and protect forms against CSRF as shown in Web Security Essentials.

Other 404 helpers

db.get_or_404() covers lookups by primary key. For anything else there are siblings that take a full statement: db.first_or_404(stmt) returns the first match, and db.one_or_404(stmt) insists on exactly one. A page found by slug is a typical use:

Python
post = db.first_or_404(db.select(Post).filter_by(slug=slug))

Pagination

A list page that loads ten thousand rows is slow for the database, the server and the reader. db.paginate() runs two queries for you, a COUNT for the total and a LIMIT/OFFSET for the current slice, and returns a Pagination object.

It reads the page query parameter from the current request, so it must run inside a request. A missing page means page 1. A value that is not a number, page 0, or a page past the end all produce a 404 (that is the error_out=True default). If you prefer an empty list over a 404, pass error_out=False.

If you leave out per_page, it also reads per_page from the query string. That is convenient but lets visitors ask for huge pages, so either pass a fixed per_page as below or cap it with max_per_page.

Python Paginating a list with db.paginate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Always add an order_by() to a paginated statement. Without a stable order the database may return rows in a different sequence on each page, so items can appear twice or not at all.

The useful attributes of the Pagination object are items, page, pages, total, per_page, has_prev, has_next, prev_num and next_num. The iter_pages() method yields page numbers for a pager, with None marking a gap that you render as an ellipsis. In a template you loop over it and call url_for(request.endpoint, page=p) for each link.

OFFSET pagination gets slower on very deep pages because the database still has to skip every earlier row. For infinite feeds over millions of rows, "keyset" pagination (WHERE id > last_seen_id ORDER BY id LIMIT n) scales better. Designing a REST API compares the two.

Filtering, sorting and paginating one list

Real list pages combine all three: /books?genre=sf&q=dune&sort=-year&page=2. Each part reads the query string, and each needs a rule that keeps visitors from asking for something dangerous.

  • Filters narrow the rows. Add a where() only for parameters that were actually sent, and always pass values as arguments (SQLAlchemy binds them), never glue them into SQL.
  • Sorting names a column, so you cannot hand the raw text to order_by. Map each allowed sort key to a real column in a dictionary (an allowlist). Anything else falls back to a default. A leading - is a common convention for descending.
  • Pagination needs a stable order: add the primary key as a final tie-breaker so rows with equal values do not swap places between pages.
  • Page links must keep the other parameters. url_for(..., **args) with a copy of request.args minus page does that.
Python A filtered, sorted, paginated list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output: the second request is page 2 of the same filtered, sorted list, and the next -> link carries genre and sort along. The sort=password request does not crash and does not leak anything: unknown keys simply fall back to the default.

If the filters come from a form, Flask-WTF with a GET form and meta csrf = False gives you validated filter values. For JSON APIs, return the pagination metadata (page, pages, total, a next URL) in the response body, as discussed in Designing a REST API.

Common mistakes

  • Forgetting commit(). add() and attribute changes live only in the session until you commit. The data seems to work inside the request and then vanishes.
  • Querying outside an application context. db.session needs an app context. Views have one automatically; scripts and the shell need with app.app_context():.
  • Loading everything, then filtering in Python. [b for b in all_books if b.year < 1985] moves every row over the wire. Put the condition in where().
  • Using get() where you need a 404. session.get() returns None; db.get_or_404() aborts. Pick the one that matches what the user should see.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which call returns a single Book object (or None) looked up by its primary key?

    session.get(Model, pk) is the primary-key lookup. It returns None when the row does not exist, unlike db.get_or_404() which aborts with a 404.
  2. 2. You change note.title = 'New' on a loaded object and the request ends without calling commit(). What happens to the change?

    Changes are only made permanent by db.session.commit(). Without it, the pending transaction is discarded when the session is removed at the end of the request.
  3. 3. Why should a delete action be a POST (or DELETE) route rather than a GET route?

    GET is meant to be safe: it should not change data. Anything that follows a link, such as a crawler or a link preview, would otherwise delete rows.
  4. 4. A paginated list uses db.paginate(stmt, per_page=5) and a visitor requests ?page=9 when there are only 3 pages. With the default settings, what is the response?

    error_out defaults to True, so a page outside the valid range aborts with 404. Pass error_out=False to get an empty result instead.
  5. 5. Why should a paginated statement always include order_by()?

    SQL does not promise any row order unless you ask for one. A deterministic ordering makes each page a consistent slice of the same sequence.

Summary

  • Build queries with db.select(Model), chain where(), filter_by(), order_by(), limit() and offset(), and execute them with db.session.scalars() or db.session.scalar().
  • session.get() fetches by primary key; db.get_or_404(), db.first_or_404() and db.one_or_404() abort with a 404 when nothing matches.
  • Create with add(), update by changing attributes, delete with delete(), and make every change permanent with commit().
  • Never change data from GET routes, and redirect after a successful POST (status 303).
  • db.paginate() returns a Pagination object with the items, page numbers and navigation helpers; always sort the statement first.
  • Combine filters, sorting and pages by reading query parameters, allowlisting sort columns, adding a unique tie-breaker to order_by, and carrying the other parameters in page links.
  • Pass values as parameters, never format them into SQL strings.

Next: Relationships, connecting tables with foreign keys and loading related rows efficiently.