Peewee is a lightweight and expressive ORM (Object Relational Mapping) library for Python. It provides a simple yet powerful way to interact with databases and perform various operations, including field lookups and expressions. In this blog post, we will explore how to use field lookups and expressions in Peewee.

Field Lookups

Field lookups in Peewee allow us to filter and query data based on specific conditions. They provide a convenient way to retrieve data that meets certain criteria. Peewee supports a wide range of field lookups, such as equal to, not equal to, greater than, less than, and many more. Here are a few examples:

Equal to lookup:

from peewee import *

class Person(Model):
    name = CharField()
    age = IntegerField()

# Get all persons with age equal to 25
persons = Person.select().where(Person.age == 25)

Greater than lookup:

# Get all persons older than 30
persons = Person.select().where(Person.age > 30)

Less than or equal to lookup:

# Get all persons with age less than or equal to 40
persons = Person.select().where(Person.age <= 40)

Expressions

Expressions in Peewee allow us to perform complex calculations and operations on fields. They can be used in various scenarios, such as sorting, aggregations, and conditional expressions. Peewee provides a wide range of built-in expressions and also allows us to create custom expressions. Let’s take a look at some examples:

Sorting using expressions:

from peewee import fn

# Get all persons sorted by age in descending order
persons = Person.select().order_by(Person.age.desc())

# Get the youngest person
youngest_person = Person.select().order_by(Person.age.asc()).get()

Aggregations using expressions:

# Get the average age of all persons
average_age = Person.select(fn.AVG(Person.age)).scalar()

# Get the total number of persons
total_persons = Person.select(fn.COUNT()).scalar()

Conditional expressions:

# Get all persons with age greater than 30 and assign a category based on their age
persons = Person.select(Person.name, Person.age, 
                Case(None, ((Person.age < 40, 'Young'), (Person.age < 60, 'Middle-aged'),
                            (Person.age >= 60, 'Elderly'))).alias('category'))

Peewee’s field lookups and expressions provide great flexibility and functionality when working with databases. They make it easy to filter, sort, and perform various calculations on data. By leveraging these features, you can build powerful and efficient database queries using Peewee.

In this blog post, we have only scratched the surface of what Peewee has to offer in terms of field lookups and expressions. I encourage you to explore the Peewee documentation to learn more about the different types of lookups and expressions available and how to use them effectively in your projects.

Happy coding with Peewee!