D. Querying Techniques

In our previous posts, we went through setting up Flask-SQLAlchemy, creating models, and performing basic CRUD operations. Today, we’re going to dive deeper into the querying capabilities provided by Flask-SQLAlchemy.

Table of Contents

Querying Techniques

To interact with our database and retrieve specific data, we use queries. A query is a request for data or information from a database table or combination of tables.

Queries can be as simple as retrieving all records from a table, or as complex as combining data from multiple tables based on sophisticated conditions.

In Flask-SQLAlchemy, we use the query object for making queries.

#all
suppliers = Supplier.query.all()  
# Retrieve all suppliers from the table as a list 

#first & filter_by
first_supplier = Supplier.query.filter_by(name = 'BestSupplier').first() 
# Retrieve the first supplier from the query or None that matches the name 'BestSupplier'

#get
supplier = Supplier.query.get(1) # Retrieves the supplier with id 1 or None

#count
no_of_products = Products.query.count() #Returns the number of products

We already looked at Supplier.query.first() which returns the first instance of the results. The methods all() and first() are part of SQLAlchemy’s ORM querying interface and are used to execute a query.

all(): This method executes the query and returns a list of results. Remember that all() returns a list. If no results were found, it returns an empty list. This is typically used when you expect multiple results.

first(): This method returns the first result of the query or None if no results were found. This is typically used when you expect one or zero results.

Here are a few other useful query methods:

get(): This method returns the instance with the specified primary key value, or None if not found. This method only works for queries on a single model class.

count(): This method returns the number of rows the query would return. This is typically used when you want to count the number of results without actually fetching them.

filter_by(): This method filters the query based on keyword arguments. The keyword arguments correspond to field names of the model class.

filter(): This method is a more flexible way to filter the query, accepting SQL-like expressions.

Remember, until you call a method like all(), first(), or count(), no SQL query is sent to the database, so you can build up your query in stages, and nothing will happen until you actually need the results. This is a feature called lazy evaluation.

Filtering techniques

Let’s look at filter and filter_by in detail

Flask-SQLAlchemy provides a variety of ways to filter your queries to find specific items. The filter() method is used to filter the records as per the arguments passed.

Filter_by method

Let’s extend our eCommerce application where we manage different products. We want to find all products in the category “Electronics”.

electronic_products = Product.query.filter_by(category='Electronics').all()

In this example, filter_by(category='Electronics') generates SQL equivalent to WHERE category = 'electronics'. The all() method then retrieves all matching records as a list.

Or, we might want to find a specific product by its unique ID:

specific_product = Product.query.filter_by(id=123).first()

filter_by() is a convenient method offered by SQLAlchemy’s ORM for simple filter conditions.

You can think of filter_by() as a Pythonic way to write SQL’s WHERE clause. The filter_by() method is used to filter the records as per the arguments passed. You provide it with keyword arguments, each representing a field and a value that field should equal.

You can also use filter_by() with multiple criteria: Assuming we have a boolean column called “same_day_delivery” with suppliers offers the service denoted as True and False otherwise. And a rating and email columns for our supplier. Our updated model would look like this.

class Supplier(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(100),nullable=False)
    products = db.relationship('Product', backref='supplier', lazy=True)
    same_day_delivery = db.Column(db.Boolean) # True or False
    rating = db.Column(db.Integer) # Rating from 1 (Low) to 5 (High)

And you wanted to find out the suppliers who offers same day delivery and are rated 3 stars

supplier = Supplier.query.filter_by(rating = 3, same_day_delivery=True).all()

In this example, filter_by(rating = 3, same_day_delivery=True) generates SQL equivalent to WHERE rating = 4 AND same_day_delivery = TRUE.

It’s important to note that filter_by() is best suited for straightforward equality checks. If you need to do more complex queries, like field comparisons, non-equality checks, or using SQL functions, you should use the filter() method instead.

The filter() method uses SQL-like syntax to create more complex queries. Here’s an equivalent filter() for the above filter_by()

Filter() method

The filter() method uses SQL-like syntax to create more complex queries. Here’s an equivalent filter() for the above filter_by():

supplier = Supplier.query.filter(Supplier.rating = 3, Supplier.same_day_delivery=True).all()

As you can see, filter() uses expressions rather than keyword arguments. This makes it more flexible than filter_by(), but also a bit more verbose for simple equality checks. Ensure that for filter methods you add the class names while accessing the columns as shown above Supplier.rating=3. I frequently missed adding the class names while alternating between the filter and filter_by. Atleast you can save you some frustration along the way.

filter() allows you to use comparison operators, such as !=, <, <=, >, and >=.

The same example above you can query for suppliers rated 4 and above, with same day delivery like this

supplier = Supplier.query.filter(Supplier.rating >= 3, Supplier.same_day_delivery=True).all()

or for eg. querying products with price greater than 20 dollars

products = Product.query.filter(Product.price > 20.00).all()

This code generates SQL equivalent to WHERE price > 20.00.

like(), ilike(), in_(), and contains()

filter() can also handle functions like like(), ilike(), in_(), and contains():

# Find users whose email addresses are from gmail
suppliers = Supplier.query.filter(Supplier.email.like('%@gmail.com')).all()

# Case insensitive version of like()
suppliers = Supplier.query.filter(Supplier.email.ilike('%@gmail.com')).all()

# Find users whose username is either 'BestSupplier', 'NextBestSupplier', or 'OkSupplier'
suppliers = Supplier.query.filter(Supplier.name.in_(['BestSupplier', 'NextBestSupplier', 'OkSupplier'])).all()

# Find products whose description contains 'iPhone'
products = Product.query.filter(Product.description.contains('iPhone')).all()

and_(), or_(), and not_()

For more complex logical conditions, you can use and_(), or_(), and not_() functions from SQLAlchemy:

from sqlalchemy import and_, or_, not_

# Find suppliers who offered same day delivery & with names BestSupplier or NextBestSupplier
suppliers = Supplier.query.filter(and_(Supplier.same_day_delivery == True, or_(Supplier.username == 'BestSupplier', User.username == 'NextBestSupplier'))).all()

# Find products that are not in the 'Electronics' category
products = Product.query.filter(not_(Product.category == 'Electronics')).all()

Sorting

Sorting results is achieved with the order_by() method. Let’s sort our electronic products by price:

sorted_products = Product.query.filter_by(category='Electronics').order_by(Product.price).all()

In the above example, the products will be sorted in ascending order. If you want to sort in descending order, you can use the desc() function:

from sqlalchemy import desc

sorted_products_desc = Product.query.filter_by(category='Electronics').order_by(desc(Product.price)).all()

Limiting and Offset

The limit() and offset() methods are used for controlling how many results to return and where to start returning results from, respectively.

# Get the first 5 electronic products
first_five_products = Product.query.filter_by(category='Electronics').limit(5).all()

# Skip the first 5 products and get the next 5
next_five_products = Product.query.filter_by(category='Electronics').offset(5).limit(5).all()

Chaining Queries

One of the powerful features of SQLAlchemy queries is that they can be chained together. This means you can start with a base query, then add additional filters, orderings, or other modifications before finally executing the query.

For example, suppose you want to find the three cheapest electronic products. You could build and execute this query in one line:

cheap_products = Product.query.filter_by(category='Electronics').order_by(Product.price).limit(3).all()

This is equivalent to building the query in separate steps:

query = Product.query  # Start with a base query
query = query.filter_by(category='Electronics')  # Add a filter
query = query.order_by(Product.price)  # Sort the results
query = query.limit(3)  # Only get the first 3 results
cheap_products = query.all()  # Execute the query

This flexibility allows you to build complex queries in a readable and efficient manner.

In the next post, we will discuss more advanced querying techniques, such as joining tables, aggregating data, and subqueries. Stay tuned!

That’s it for this post. You should now have a solid understanding of how to use SQLAlchemy’s querying capabilities to interact with your data. Practice these techniques and experiment with chaining to create more complex queries.