Using Python generator expressions

Pony allows to use generator expressions as a very natural way of writing database queries. Pony provides select() function which accepts Python generator, translates it to SQL and returns objects from the database. The process of the translation is described in this StackOverflow question.

Here is an example of a query:

query = select(c for c in Customer
               if sum(o.total_price for o in c.orders) > 1000)

or, with attribute lifting:

query = select(c for c in Customer
               if sum(c.orders.total_price) > 1000)

You can apply filter() function to query

query2 = query.filter(lambda person: person.age > 18)

Also you can make new query based on another query:

query3 = select(customer.name for customer in query2
                if customer.country == 'Canada')

select() function returns an instance of a Query class, and you can then call the Query object methods for getting the result, for example:

customer_name = query3.first()

From query you can return entity, attribute or tuple of arbitrary expressions

select((c, sum(c.orders.total_price))
       for c in Customer if sum(c.orders.total_price) > 1000)