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 .filter()
method.
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 .all()
method. .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 and
:
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.
Instructions
Use the filter
method to fetch all the readers from the Reader
table with Adams
surname, and assign the result in the variable called adams
.
Use the filter
method to fetch all the books from the year 2019 or earlier, but then assign only the first result to the variable called book_pre2019
.