Question

Warning

The query interface is considered legacy in SQLAlchemy. Prefer using session.execute(select(...)) instead.

Flask-SQLAlchemy adds a query object to each model. This can be used to query instances of a given model. User.query is a shortcut for db.session.query(User).

# get the user with id 5
user = User.query.get(5)

# get a user by username
user = User.query.filter_by(username=username).one()

Queries for Views

If you write a Flask view function it’s often useful to return a 404 Not Found error for missing entries. Flask-SQLAlchemy provides some extra query methods.

• Query.get_or_404() will raise a 404 if the row with the given id doesn’t exist, otherwise it will return the instance.

• Query.first_or_404() will raise a 404 if the query does not return any results, otherwise it will return the first result.

• Query.one_or_404() will raise a 404 if the query does not return exactly one result, otherwise it will return the result.

@app.route("/user/<username>")
def show_user(username):
    user = User.query.filter_by(username=username).one_or_404()
    return render_template("show_user.html", user=user)

You can add a custom message to the 404 error:

user = User.query.filter_by(username=username).one_or_404(
    description=f"No user named '{username}'."
)

Pagination

If you have a lot of results, you may only want to show a certain number at a time, allowing the user to click next and previous links to see pages of data.

Call paginate() on a query to get a Pagination object. See Paging Query Results for more information about the pagination object.

During a request, this will take page and per_page arguments from the query string request.args. Pass max_per_page to prevent users from requesting too many results on a single page. If not given, the default values will be page 1 with 20 items per page.

page = User.query.order_by(User.join_date).paginate()
return render_template("user/list.html", page=page)