Query object

The generator expression and lambda queries return an instance of the Query class. Below is the list of methods that you can apply to it.

class Query

  • [start:end]

    [index]

    Limit the number of instances to be selected from the database. In the example below we select the first ten instances:

    1. # generator expression query
    2. select(c for c in Customer)[:10]
    4. # lambda function query
    5. Customer.select()[:10]

    Generates the following SQL:

    1. SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
    2. FROM "Customer" "c"
    3. LIMIT 10

    If we need to select instances with offset, we should use start and end values:

    1. select(c for c in Customer).order_by(Customer.name)[20:30]

    It generates the following SQL:

    1. SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
    2. FROM "Customer" "c"
    3. ORDER BY "c"."name"
    4. LIMIT 10 OFFSET 20

    Also you can use the limit() or page() methods for the same purpose.

  • __len__()

    Return the number of objects selected from the database.

    1. len(select(c for c in Customer))

  • avg(distinct=None)

    Return the average value for all selected attributes:

    1. select(o.total_price for o in Order).avg()

    The function avg() does the same thing.

  • count()

    Return the number of objects that match the query condition:

    1. select(c for c in Customer if len(c.orders) > 2).count()

    The function count() does the same thing.

  • delete(bulk=None)

    Delete instances selected by a query. When bulk=False Pony loads each instance into memory and call the Entity.delete() method on each instance (calling before_delete() and after_delete() hooks if they are defined). If bulk=True Pony doesn’t load instances, it just generates the SQL DELETE statement which deletes objects in the database.

    Note

    Be careful with the bulk delete:

  • distinct()

    Force DISTINCT in a query:

    1. select(c.name for c in Customer).distinct()

    But usually this is not necessary, because Pony adds DISTINCT keyword automatically in an intelligent way. See more information about it in the Automatic DISTINCT section.

    The function distinct() does the same thing.

  • exists()

    Returns True if at least one instance with the specified condition exists and False otherwise:

    1. select(c for c in Customer if len(c.cart_items) > 10).exists()

    This query generates the following SQL:

    1. SELECT "c"."id"
    2. FROM "Customer" "c"
    3.   LEFT JOIN "CartItem" "cartitem-1"
    4.     ON "c"."id" = "cartitem-1"."customer"
    5. GROUP BY "c"."id"
    6. HAVING COUNT(DISTINCT "cartitem-1"."id") > 20
    7. LIMIT 1

  • filter(lambda, globals=None, locals=None)

    filter(str, globals=None, locals=None)

    filter(\*kwargs*)

    Filters the result of a query. The conditions which are passed as parameters to the filter() method will be translated into the WHERE section of the resulting SQL query. The result of the filter() method is a new query object with the specified additional condition.

    Note

    This method is similar to the where() method. The difference is that the filter() condition applies to items returned from the previous query, whereas where() condition applies to the loop variables from the original generator expression. Example:

    1. q = select(o.customer for o in Order)
    3. # c refers to o.customer
    4. q2 = q.filter(lambda c: c.name.startswith('John'))
    6. # o refers to Order object
    7. q3 = q2.where(lambda o: o.total_price > 1000)

    The name of lambda function argument in filter() may be arbitrary, but in where() the name of lambda argument should exactly match the name of the loop variable.

    Note

    The where() method was added in version 0.7.3. Before that it was possible to do the same by using filter() method in which argument of lambda function was not specified:

    1. q = select(o.customer for o in Order)
    3. # using new where() method
    4. q2a = q.where(lambda o: o.customer.name == 'John Smith')
    6. # old way to do the same using filter() method
    7. q2b = q.filter(lambda: o.customer.name == 'John Smith')

    But this old way has a drawback: IDEs and linters don’t understand code and warn about “undefined global variable o”. With where() it is no longer the case. Using lambda function without argument in filter() will be deprecated in the next release.

    Specifying filter() condition using lambda function

    Usually the argument of the filter() method is a lambda function. The argument of the lambda function represents the result of the query. You can use an arbitrary name for this argument:

    1. q = select(p.name for p in Product)
    2. q2 = q.filter(lambda x: x.startswith('Apple iPad'))

    In the example above x argument corresponds to the result of the query p.name. This way you cannot access the p variable in the filter method, only p.name. When you need to access the original query loop variable, you can use the where() method instead.

    If the query returns a tuple, the number of filter() lambda function arguments should correspond to the query result:

    1. q = select((p.name, p.price) for p in Product)
    2. q2 = q.filter(lambda n, p: n.startswith('Apple iPad') and p < 500)

    Specifying filter() condition using keyword arguments

    Another way to filter the query result is to pass parameters in the form of named arguments:

    1. q = select(o.customer for o in Order if o.total_price > 1000)
    2. q2 = q.filter(name="John Smith", country="UK")

    Keyword arguments can be used only when the result of the query is an object. In the example above it is an object of the Customer type.

    Specifying filter() condition as a text string

    Also the filter() method can receive a text definition of a lambda function. It can be used when you combine the condition from text pieces:

    1. q = select(p for p in Product)
    2. x = 100
    3. q2 = q.filter("lambda p: p.price > x")

    In the example above the x variable in lambda refers to x defined before. The more secure solution is to specify the dictionary with values as a second argument of the filter() method:

    1. q = select(p for p in Product)
    2. q2 = q.filter("lambda p: p.price > x", {"x": 100})

  • first()

    Return the first element from the selected results or None if no objects were found:

    1. select(p for p in Product if p.price > 100).first()

  • for_update(nowait=False, skip_locked=False)

    Note

    nowait and skip_locked parameters are mutually exclusive.

    • Parameters

      • nowait (bool) – prevent the operation from waiting for other transactions to commit. If a selected row(s) cannot be locked immediately, the operation reports an error, rather than waiting.

      • skip_locked (bool) – add SKIP LOCKED option to FOR UPDATE clause

    Sometimes there is a need to lock objects in the database in order to prevent other transactions from modifying the same instances simultaneously. Within the database such lock should be done using the SELECT FOR UPDATE query. In order to generate such a lock using Pony you can call the for_update method:

    1. select(p for p in Product if p.picture is None).for_update()

    This query selects all instances of Product without a picture and locks the corresponding rows in the database. The lock will be released upon commit or rollback of current transaction.

  • get()

    Extract one entity instance from the database. The function returns the object if an object with the specified parameters exists, or None if there is no such object. If there are more than one objects with the specified parameters, raises the MultipleObjectsFoundError: Multiple objects were found. Use select(...) to retrieve them exception. Example:

    1. select(o for o in Order if o.id == 123).get()

    The function get() does the same thing.

  • get_sql()

    Return SQL statement as a string:

    1. sql = select(c for c in Category if c.name.startswith('a')).get_sql()
    2. print(sql)
    1. SELECT "c"."id", "c"."name"
    2. FROM "category" "c"
    3. WHERE "c"."name" LIKE 'a%%'

  • group_concat(sep=’,’, distinct=False)

    (new in version 0.7.4)

    Returns a string which is the concatenation of all non-NULL values of given column.

    The function group_concat() does the same thing.

    1. select(article.tag for article in Article).group_concat(sep=', #')

    Note

    In SQLite you can’t use group_concat() with both sep and distinct arguments at a time.

  • limit(limit=None, offset=None)

    Limit the number of instances to be selected from the database.

    1. select(c for c in Customer).limit(10, offset=30)

    Also you can use the [start:end]() or page() methods for the same purpose.

    (Since version 0.7.6 limit can be None)

  • max()

    Return the maximum value from the database. The query should return a single attribute:

    1. select(o.date_shipped for o in Order).max()

    The function max() does the same thing.

  • min()

    Return the minimum value from the database. The query should return a single attribute:

    1. select(p.price for p in Product).min()

    The function min() does the same thing.

  • order_by(attr1[, attr2, …])

    order_by(pos1[, pos2, …])

    order_by(lambda[, globals[, locals]])

    order_by(str[, globals[, locals]])

    Note

    The behavior of order_by() is going to be changed in the next release (0.8). Previous behavior supports by method sort_by() which is introduced in the release 0.7.3. In order to be fully forward-compatible with the release 0.8, you can replace all order_by() calls to sort_by() calls.

    Orders the results of a query. Currently order_by() and sort_by() methods work in the same way - they are applied to the result of the previous query.

    1. q = select(o.customer for o in Order)
    3. # The following five queries are all equivalent
    5. # Before the 0.8 release
    6. q1 = q.order_by(lambda c: c.name)
    7. q2 = q.order_by(Customer.name)
    9. # Starting from the 0.7.3 release
    10. q3 = q.sort_by(lambda c: c.name)
    11. q4 = q.sort_by(Customer.name)
    13. # After the 0.8 release
    14. q5 = q.order_by(lambda o: o.customer.name)

    Most often query returns the same object it iterates. In this case the behavior of order_by() will remains the same before and after the 0.8 release:

    1. # the query returns the loop variable
    2. q = select(c for c in Customer if c.age > 18)
    4. # the next line will work the same way
    5. # before and after the 0.8 release
    6. q2 = q.order_by(lambda c: c.name)

    There are several ways how it is possible to call order_by() method:

    Using entity attributes

    1. select(o for o in Order).order_by(Order.date_created)

    For ordering in descending order, use the function desc():

    1. select(o for o in Order).order_by(desc(Order.date_created))

    Using position of query result variables

    1. select((o.customer.name, o.total_price) for o in Order).order_by(-2, 1)

    The position numbers start with 1. Minus means sorting in the descending order. In this example we sort the result by the total price in descending order and by the customer name in ascending order.

    Using lambda

    1. select(o for o in Order).order_by(lambda o: o.customer.name)

    If the lambda has a parameter (o in our example) then o represents the result of the select and will be applied to it. Starting from the release 0.8 it will represent the iterator loop variable from the original query. If you want to continue using the result of a query for ordering, you need to use the sort_by method instead.

    Using lambda without parameters

    If you specify the lambda without parameters, then inside lambda you may access all names defined inside the query:

    1. select(o.total_price for o in Order).order_by(lambda: o.customer.id)

    It looks like o is a global variable, but Pony understand it as a loop variable name o from the generator expression. This behavior confuses IDEs and linetrs which warn about “access to undefined global variable o”. Starting with release 0.8 this way of using order_by() will be unnecessary: just add o argument to lambda function instead.

    Specifying a string expression

    This approach is similar to the previous one, but you specify the body of a lambda as a string:

    1. select(o for o in Order).order_by("o.customer.name")

  • sort_by(attr1[, attr2, …])

    sort_by(pos1[, pos2, …])

    sort_by(lambda[, globals[, locals]])

    sort_by(str[, globals[, locals]])

    New in 0.7.3

    Orders the results of a query. The expression in sort_by() method call applies to items in query result. Until the 0.8 release it works the same as order_by(), then the behavior of order_by() will change.

    There are several ways how it is possible to call sort_by() method:

    Using entity attributes

    1. select(o.customer for o in Order).sort_by(Customer.name)

    For ordering in descending order, use the function desc():

    1. select(o.customer for o in Order).sort_by(desc(Customer.name))

    Using position of query result variables

    1. select((o.customer.name, o.total_price) for o in Order).sort_by(-2, 1)

    The position numbers start with 1. Minus means sorting in the descending order. In this example we sort the result by the total price in descending order and by the customer name in ascending order.

    Using lambda

    1. select(o.customer for o in Order).sort_by(lambda c: c.name)

    Lambda inside the sort_by method receives the result of the previous query.

    Specifying a string expression

    This approach is similar to the previous one, but you specify the body of a lambda as a string:

    1. select(o for o in Order).sort_by("o.customer.name")

  • page(pagenum, pagesize=10)

    Pagination is used when you need to display results of a query divided into multiple pages. The page numbering starts with page 1. This method returns a slice [start:end] where start = (pagenum - 1) * pagesize, end = pagenum * pagesize.

  • prefetch(\args*)

    Allows specifying which related objects or attributes should be loaded from the database along with the query result.

    Usually there is no need to prefetch related objects. When you work with the query result within the @db_session, Pony gets all related objects once you need them. Pony uses the most effective way for loading related objects from the database, avoiding the N+1 Query problem.

    So, if you use Flask, the recommended approach is to use the @db_session decorator at the top level, at the same place where you put the Flask’s app.route decorator:

    1. @app.route('/index')
    2. @db_session
    3. def index():
    4.     ...
    5.     objects = select(...)
    6.     ...
    7.     return render_template('template.html', objects=objects)

    Or, even better, wrapping the wsgi application with the db_session() decorator:

    1. app.wsgi_app = db_session(app.wsgi_app)

    If for some reason you need to pass the selected instances along with related objects outside of the db_session(), then you can use this method. Otherwise, if you’ll try to access the related objects outside of the db_session(), you might get the DatabaseSessionIsOver exception, e.g. DatabaseSessionIsOver: Cannot load attribute Customer[3].name: the database session is over

    More information regarding working with the db_session() can be found here.

    You can specify entities and/or attributes as parameters. When you specify an entity, then all “to-one” and non-lazy attributes of corresponding related objects will be prefetched. The “to-many” attributes of an entity are prefetched only when specified explicitly.

    If you specify an attribute, then only this specific attribute will be prefetched. You can specify attribute chains, e.g. order.customer.address. The prefetching works recursively - it applies the specified parameters to each selected object.

    Examples:

    1. from pony.orm.examples.presentation import *

    Loading Student objects only, without prefetching:

    1. students = select(s for s in Student)[:]

    Loading students along with groups and departments:

    1. students = select(s for s in Student).prefetch(Group, Department)[:]
    3. for s in students: # no additional query to the DB will be sent
    4.     print s.name, s.group.major, s.group.dept.name

    The same as above, but specifying attributes instead of entities:

    1. students = select(s for s in Student).prefetch(Student.group, Group.dept)[:]
    3. for s in students: # no additional query to the DB will be sent
    4.     print s.name, s.group.major, s.group.dept.name

    Loading students and related courses (“many-to-many” relationship):

    1. students = select(s for s in Student).prefetch(Student.courses)
    3. for s in students:
    4.     print s.name
    5.     for c in s.courses: # no additional query to the DB will be sent
    6.         print c.name

  • random(limit)

    Select limit random objects from the database. This method will be translated using the ORDER BY RANDOM() SQL expression. The entity class method select_random() provides better performance, although doesn’t allow to specify query conditions.

    For example, select ten random persons older than 20 years old:

    1. select(p for p in Person if p.age > 20).random()[:10]

  • show(width=None)

    Prints the results of a query to the console. The result is formatted in the form of a table. This method doesn’t display “to-many” attributes because it would require additional query to the database and could be bulky. But if an instance has a “to-one” relationship, then it will be displayed.

    1. >>> select(p for p in Person).order_by(Person.name)[:2].show()
    3. SELECT "p"."id", "p"."name", "p"."age"
    4. FROM "Person" "p"
    5. ORDER BY "p"."name"
    6. LIMIT 2
    8. id|name|age
    9. --+----+---
    10. 3 |Bob |30
    12. >>> Car.select().show()
    13. id|make  |model   |owner
    14. --+------+--------+---------
    15. 1 |Toyota|Prius   |Person[2]
    16. 2 |Ford  |Explorer|Person[3]

  • sum()

    Return the sum of all selected items. Can be applied to the queries which return a single numeric expression only.

    1. select(o.total_price for o in Order).sum()

    If the query returns no items, the query result will be 0.

  • where(lambda, globals=None, locals=None)

    where(str, globals=None, locals=None)

    where(\*kwargs*)

    New in version 0.7.3

    Filters the result of a query. The conditions which are passed as parameters to the where() method will be translated into the WHERE section of the resulting SQL query. The result of the where() method is a new query object with the specified additional condition.

    Note

    This method is similar to the filter() method. The difference is that the filter() condition applies to items returned from the previous query, whereas where() condition applies to the loop variables from the original generator expression. Example:

    1. q = select(o.customer for o in Order)
    3. # c refers to o.customer
    4. q2 = q.filter(lambda c: c.name.startswith('John'))
    6. # o refers to Order object
    7. q3 = q2.where(lambda o: o.total_price > 1000)

    The name of lambda function argument in filter() may be arbitrary, but in where() the name of lambda argument should exactly match the name of the loop variable.

    Note

    Before the where() method was added it was possible to do the same by using filter() method in which argument of lambda function was not specified:

    1. q = select(o.customer for o in Order)
    3. # using new where() method
    4. q2a = q.where(lambda o: o.customer.name == 'John Smith')
    6. # old way to do the same using filter() method
    7. q2b = q.filter(lambda: o.customer.name == 'John Smith')

    But this old way has a drawback: IDEs and linters don’t understand code and warn about “undefined global variable o”. With where() it is no longer the case. Using lambda function without argument in filter() will be deprecated in the next release.

    Specifying where() condition using lambda function

    Usually the argument of the where() method is a lambda function. The arguments of the lambda function refer to the query loop variables, and should have the same names.

    1. q = select(p.name for p in Product).where(lambda p: p.price > 1000)

    In the example above p argument corresponds to the p variable of the query.

    In the where() method the lambda arguments can refer to all loop variables from the original query:

    1. q = select(c.name for c in Customer for o in c.orders)
    2. q2 = q.where(lambda c, o: c.country == 'US' and o.state == 'DELIVERED')

    When the query is written using the select() method of an entity, the query does not have any explicitly defined loop variable. In that case the argument of lambda function should be the first letter of the entity name in the lower case:

    1. q = Product.select()
    2. q2 = q.where(lambda p: p.name.startswith('A'))

    Specifying where() condition using keyword arguments

    Another way to filter the query result is to pass parameters in the form of named arguments:

    1. q = select(o.customer for o in Order if o.total_price > 1000)
    2. q2 = q.where(state == 'DELIVERED')

    The state keyword attribute refers to the state attribute of the Order object.

    Specifying where() condition as a text string

    Also the where() method can receive an expression text instead of lambda function. It can be used when you combine the condition from text pieces:

    1. q = select(p for p in Product)
    2. x = 100
    3. q2 = q.where("p.price > x")

    The more secure solution is to specify the dictionary with values as a second argument of the where() method:

    1. q = select(p for p in Product)
    2. q2 = q.where("p.price > x", {"x": 100})

  • without_distinct()

    By default Pony tries to avoid duplicates in the query result and intellectually adds the DISTINCT SQL keyword to a query where it thinks it necessary. If you don’t want Pony to add DISTINCT and get possible duplicates, you can use this method. This method returns a new instance of the Query object, so you can chain it with other query methods:

    1. select(p.name for p in Person).without_distinct().order_by(Person.name)

    Before Pony Release 0.6 the method without_distinct() returned query result and not a new query instance.