Filtering data out of the database

In continuation from the last post about database basics with python and sqlalchemy, we are going to learn some more query types such as filter, sum, and count.

While using the same Cars Model object and db session object, we can create our new queries accordingly.

Let's say we want to add a filter for getting all the make of cars that match Jeep. The following query would accomplish this:

db.session \ 
    .query(models.Cars) \ 
    .filter_by(make = "Jeep") \
    .all()

What if we want to filter all Jeeps by model as well, let's say find all Jeep Wranglers in the database.

Well to do that we us `filter` instead of `filter_by`.

db.session \
    .query(models.Cars) \
    .filter(models.Cars.make = "Jeep") \
    .filter(models.Cars.model = "Wrangler")
    .all()

Notice how we had to explicitly use `model.Cars.make` in the argument to `filter`. This is because, the filter method needs to have the full model and models method that represents the table and column specified respectively. Chaining these`filter` methods together create an AND expression automatically by default, so that is why we can ask for all Jeep Wranglers here.

Let's move on to only selecting certain columns from the cars table. Sometimes we don't need to select all the columns out of the table for every query against the database. Doing this helps with performance and optimization of your queries. For example to get a list of all the makes of cars we can do:

db.session \
    .query(models.Cars) \
    .with_entities(models.Cars.make) \
    .all()

This query above will only select the make column from the cars table and display all make of cars. This same query can be explained in a slightly different way that gives the same result.

db.session \
    .query(models.Cars.make) \
    .all()

Now we need to talk about how to get the count ofall the Jeeps in the database that there are. This is easy due to the nature of sqlalchemy API.

db.session \
    .query(models.Cars) \
    .filter(models.Cars.make == "Jeep")
    .count()

That is all there is to it in order to get the count of Jeep's in the database.

The last thing I want to cover here is the `sum` method. Let's just pretend that we have already run a migration to add cost column to the cars table. We will get into migrations at a later date. First we need import the func function from the sqlachemy.sql package.

from sqlalchemy.sql import func

Then a query would be:

db.session \
    .query(func.sum(models.Cars.cost)) \
    .filter(models.Cars.make == "Jeep")
    .first()

This would give you a sum of all the Jeep's cost in the database.

That concludes the lesson for now, and there will be more to come.