Skip to Content
Learn
Databases in Flask - Reading, Updating and Deleting
Queries: more advanced filtering

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 .like():

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 ColumnElement).

To order books by year we use the .order_by() method on Query:

ordered_books = Book.query.order_by(Book.year).all()

We suggest checking the SQLAlchemy Core + ORM documentation to see other querying options.

Instructions

1.

Using 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 s_names.

2.

Using 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 sample_emails.

3.

Using 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 ordered_reviews.

Folder Icon

Sign up to start coding

Already have an account?