Flask-SQLALchemy allows more complex queries and operations such as checking whether a column starts, or ends, with some string. One can also order retrieved queries by some criterion. There are many more possible queries, but here we cover only some of them.
For example, to retrieve e-mails that end with
edu we do:
education = Reader.query.filter(Reader.email.endswith('edu')).all()
To retrieve all the readers with e-mails that contain a ‘.’ before the ‘@’ symbol we use
emails = Reader.query.filter(Reader.email.like('%.%@%')).all()
You might recognize the
like operator from SQL. It is used to search for a specified pattern in a column. The wildcard
% represents zero, one, or multiple characters.
In the two examples above, we used methods on the column of the table (SQLAlchemy’s
To order books by year we use the
.order_by() method on
ordered_books = Book.query.order_by(Book.year).all()
We suggest checking the SQLAlchemy Core + ORM documentation to see other querying options.
filter and column-based criterion
endswith, write a query that retrieves all the readers with the surname that ends with an ‘s’ and assign the result to a variable called
like, write a query that retrieves all the readers with e-mails having “sample” instead of “example” just after the “@” symbol, and assign the result to a variable called
order_by, write a query that orders the reviews by the number of stars they have in ascending order, and assign the result to a variable called