Often times you don’t want to retrieve all the entries from a table but select only those that satisfy some criterion. Criteria are usually based on the values of the table’s columns. To filter a query, SQLAlchemy provides the
For example, to select books from a specific year from the
Book table we use the following command:
Book.query.filter(Book.year == 2020).all()
Notice the additional
.filter() returns a
Query object that needs to be further refined. This can be done by using several additional methods like
.all() that returns a list of all results,
.count() that counts the number of fetched entries, or
.first() that returns only one result, namely the first one.
Book.query.filter(Book.year == 2020).first()
Multiple criteria may be specified as comma separated and the interpretation of a comma is a Boolean
Review.query.filter(Review.stars <= 3, Review.book_id == 1).all()
This query will return all entries in the
Review table that have fewer than 3 stars for the book with
id = 1.
Note: there is also the
.filter_by() method that uses only a simple attribute-value test for filtering.
filter method to fetch all the readers from the
Reader table with
Adams surname, and assign the result in the variable called
filter method to fetch all the books from the year 2019 or earlier, but then assign only the first result to the variable called