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:
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.
Multiple criteria may be specified as comma separated and the interpretation of a comma is a Boolean and:
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.