Retrieving Data

The real strength of our database is in how it allows us to retrieve data through queries. Relational databases are excellent for making ad-hoc queries.

Getting single records

Let’s retrieve Grandma’s record from the database. To get a single record from the database, use Select.get():

  1. grandma = Person.select().where(Person.name == 'Grandma L.').get()

We can also use the equivalent shorthand Model.get():

  1. grandma = Person.get(Person.name == 'Grandma L.')

Lists of records

Let’s list all the people in the database:

  1. for person in Person.select():
  2.     print(person.name)
  4. # prints:
  5. # Bob
  6. # Grandma L.
  7. # Herb

Let’s list all the cats and their owner’s name:

  1. query = Pet.select().where(Pet.animal_type == 'cat')
  2. for pet in query:
  3.     print(pet.name, pet.owner.name)
  5. # prints:
  6. # Kitty Bob
  7. # Mittens Jr Herb

Attention

There is a big problem with the previous query: because we are accessing pet.owner.name and we did not select this relation in our original query, peewee will have to perform an additional query to retrieve the pet’s owner. This behavior is referred to as N+1 and it should generally be avoided.

For an in-depth guide to working with relationships and joins, refer to the Relationships and Joins documentation.

We can avoid the extra queries by selecting both Pet and Person, and adding a join.

  1. query = (Pet
  2.          .select(Pet, Person)
  3.          .join(Person)
  4.          .where(Pet.animal_type == 'cat'))
  6. for pet in query:
  7.     print(pet.name, pet.owner.name)
  9. # prints:
  10. # Kitty Bob
  11. # Mittens Jr Herb

Let’s get all the pets owned by Bob:

  1. for pet in Pet.select().join(Person).where(Person.name == 'Bob'):
  2.     print(pet.name)
  4. # prints:
  5. # Kitty
  6. # Fido

We can do another cool thing here to get bob’s pets. Since we already have an object to represent Bob, we can do this instead:

  1. for pet in Pet.select().where(Pet.owner == uncle_bob):
  2.     print(pet.name)

Sorting

Let’s make sure these are sorted alphabetically by adding an order_by() clause:

  1. for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
  2.     print(pet.name)
  4. # prints:
  5. # Fido
  6. # Kitty

Let’s list all the people now, youngest to oldest:

  1. for person in Person.select().order_by(Person.birthday.desc()):
  2.     print(person.name, person.birthday)
  4. # prints:
  5. # Bob 1960-01-15
  6. # Herb 1950-05-05
  7. # Grandma L. 1935-03-01

Combining filter expressions

Peewee supports arbitrarily-nested expressions. Let’s get all the people whose birthday was either:

  • before 1940 (grandma)
  • after 1959 (bob)
  1. d1940 = date(1940, 1, 1)
  2. d1960 = date(1960, 1, 1)
  3. query = (Person
  4.          .select()
  5.          .where((Person.birthday < d1940) | (Person.birthday > d1960)))
  7. for person in query:
  8.     print(person.name, person.birthday)
  10. # prints:
  11. # Bob 1960-01-15
  12. # Grandma L. 1935-03-01

Now let’s do the opposite. People whose birthday is between 1940 and 1960:

  1. query = (Person
  2.          .select()
  3.          .where(Person.birthday.between(d1940, d1960)))
  5. for person in query:
  6.     print(person.name, person.birthday)
  8. # prints:
  9. # Herb 1950-05-05

Aggregates and Prefetch

Now let’s list all the people and how many pets they have:

  1. for person in Person.select():
  2.     print(person.name, person.pets.count(), 'pets')
  4. # prints:
  5. # Bob 2 pets
  6. # Grandma L. 0 pets
  7. # Herb 1 pets

Once again we’ve run into a classic example of N+1 query behavior. In this case, we’re executing an additional query for every Person returned by the original SELECT! We can avoid this by performing a JOIN and using a SQL function to aggregate the results.

  1. query = (Person
  2.          .select(Person, fn.COUNT(Pet.id).alias('pet_count'))
  3.          .join(Pet, JOIN.LEFT_OUTER)  # include people without pets.
  4.          .group_by(Person)
  5.          .order_by(Person.name))
  7. for person in query:
  8.     # "pet_count" becomes an attribute on the returned model instances.
  9.     print(person.name, person.pet_count, 'pets')
  11. # prints:
  12. # Bob 2 pets
  13. # Grandma L. 0 pets
  14. # Herb 1 pets

Note

Peewee provides a magical helper fn(), which can be used to call any SQL function. In the above example, fn.COUNT(Pet.id).alias('pet_count') would be translated into COUNT(pet.id) AS pet_count.

Now let’s list all the people and the names of all their pets. As you may have guessed, this could easily turn into another N+1 situation if we’re not careful.

Before diving into the code, consider how this example is different from the earlier example where we listed all the pets and their owner’s name. A pet can only have one owner, so when we performed the join from Pet to Person, there was always going to be a single match. The situation is different when we are joining from Person to Pet because a person may have zero pets or they may have several pets. Because we’re using a relational databases, if we were to do a join from Person to Pet then every person with multiple pets would be repeated, once for each pet.

It would look like this:

  1. query = (Person
  2.          .select(Person, Pet)
  3.          .join(Pet, JOIN.LEFT_OUTER)
  4.          .order_by(Person.name, Pet.name))
  5. for person in query:
  6.     # We need to check if they have a pet instance attached, since not all
  7.     # people have pets.
  8.     if hasattr(person, 'pet'):
  9.         print(person.name, person.pet.name)
  10.     else:
  11.         print(person.name, 'no pets')
  13. # prints:
  14. # Bob Fido
  15. # Bob Kitty
  16. # Grandma L. no pets
  17. # Herb Mittens Jr

Usually this type of duplication is undesirable. To accommodate the more common (and intuitive) workflow of listing a person and attaching a list of that person’s pets, we can use a special method called prefetch():

  1. query = Person.select().order_by(Person.name).prefetch(Pet)
  2. for person in query:
  3.     print(person.name)
  4.     for pet in person.pets:
  5.         print('  *', pet.name)
  7. # prints:
  8. # Bob
  9. #   * Kitty
  10. #   * Fido
  11. # Grandma L.
  12. # Herb
  13. #   * Mittens Jr

SQL Functions

One last query. This will use a SQL function to find all people whose names start with either an upper or lower-case G:

  1. expression = fn.Lower(fn.Substr(Person.name, 1, 1)) == 'g'
  2. for person in Person.select().where(expression):
  3.     print(person.name)
  5. # prints:
  6. # Grandma L.

This is just the basics! You can make your queries as complex as you like. Check the documentation on Querying for more info.