Queries and functions

Below is the list of upper level functions defined in Pony:

avg(gen, distinct=None)

Return the average value for all selected attributes.

• Parameters

  • gen (generator) – Python generator expression

  • distinct (bool) – distinct option

  Return type

  numeric

avg(o.total_price for o in Order)

The equivalent query can be generated using the avg() method.

between(x, a, b)

This function will be translated into x BETWEEN a AND b. It is equal to the condition x >= a AND x <= b.

select(p for p in Person if between(p.age, 18, 65))

coalesce(\args*)

• Parameters

  args (list) – list of arguments

Returns the first non-null expression in a list.

select(coalesce(p.phone, 'UNKNOWN') for p in Person)

concat(\args*)

• Parameters

  args (list) – list of arguments

Concatenates arguments into one string.

select(concat(p.first_name, ' ', p.last_name) for p in Person)

commit()

Save all changes which were made within the current db_session() using the flush() function and commits the transaction to the database. This top level commit() function calls the commit() method of each database object which was used in current transaction.

count(gen, distinct=None)

Return the number of objects that match the query condition.

• Parameters

  • gen (generator) – Python generator expression

  • distinct (bool) – distinct option

  Return type

  numeric

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

This query will be translated to the following SQL:

SELECT COUNT(*)
FROM "Customer" "c"
LEFT JOIN "Order" "order-1"
  ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING COUNT(DISTINCT "order-1"."id") > 2

The equivalent query can be generated using the count() method.

delete(gen)

Delete objects from the database. Pony loads objects into the memory and will delete them one by one. If you have before_delete() or after_delete() defined, Pony will call each of them.

• Parameters

  gen (generator) – Python generator expression

delete(o for o in Order if o.status == 'CANCELLED')

If you need to delete objects without loading them into memory, you should use the delete() method with the parameter bulk=True. In this case no hooks will be called, even if they are defined for the entity.

desc(attr)

This function is used inside order_by() and sort_by() for ordering in descending order.

• Parameters

  attr (attribute) – Entity attribute

select(o for o in Order).order_by(desc(Order.date_shipped))

The same example, using lambda:

select(o for o in Order).order_by(lambda o: desc(o.date_shipped))

distinct(gen)

When you need to force DISTINCT in a query, it can be done using the distinct() function. But usually this is not necessary, because Pony adds DISTINCT keyword automatically in an intelligent way. See more information about it in the TODO chapter.

• Parameters

  gen (generator) – Python generator expression

distinct(o.date_shipped for o in Order)

Another usage of the distinct() function is with the sum() aggregate function - you can write:

select(sum(distinct(x.val)) for x in X)

to generate the following SQL:

SELECT SUM(DISTINCT x.val)
FROM X x

but it is rarely used in practice.

exists(gen, globals=None, locals=None)

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

• Parameters

  • gen (generator) – Python generator expression.

  • globals (dict) –

  • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

  Return type

  bool

exists(o for o in Order if o.date_delivered is None)

flush()

Save all changes from the db_session() cache to the databases, without committing them. It makes the updates made in the db_session() cache visible to all database queries which belong to the current transaction.

Usually Pony saves data from the database session cache automatically and you don’t need to call this function yourself. One of the use cases when it might be needed is when you want to get the primary keys values of newly created objects which has autoincremented primary key before commit.

This top level flush() function calls the flush() method of each database object which was used in current transaction.

This function is called automatically before executing the following functions: commit(), get(), exists(), select().

get(gen, globals=None, locals=None)

Extracts one entity instance from the database.

• Parameters

  • gen (generator) – Python generator expression.

  • globals (dict) –

  • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

  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, the function raises the MultipleObjectsFoundError: Multiple objects were found. Use select(...) to retrieve them exception.

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

The equivalent query can be generated using the get() method.

getattr(object, name[, default])

This is a standard Python built-in function, that can be used for getting the attribute value inside the query.

Example:

attr_name = 'name'
param_value = 'John'
select(c for c in Customer if getattr(c, attr_name) == param_value)

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

(new in version 0.7.4)

Returns string which is concatenation of given attribute.

group_concat(t.title for t in Tag, sep='-')

The equivalent query can be generated using the group_concat() method.

Note

Query should return only single attribute. Also in SQLite you cant use both distinct and sep arguments at a time.

JOIN(\args*)

Used for query optimization in cases when Pony doesn’t provide this optimization automatically. Serves as a hint saying Pony that we want to use SQL JOIN, instead of generating a subquery inside the SQL query.

select(g for g in Group if max(g.students.gpa) < 4)
select(g for g in Group if JOIN(max(g.students.gpa) < 4))

left_join(gen, globals=None, locals=None)

The results of a left join always contain the result from the ‘left’ table, even if the join condition doesn’t find any matching record in the ‘right’ table.

• Parameters

  • gen (generator) – Python generator expression.

  • globals (dict) –

  • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

Let’s say we need to calculate the amount of orders for each customer. Let’s use the example which comes with Pony distribution and write the following query:

from pony.orm.examples.estore import *
populate_database()
select((c, count(o)) for c in Customer for o in c.orders)[:]

It will be translated to the following SQL:

SELECT "c"."id", COUNT(DISTINCT "o"."id")
FROM "Customer" "c", "Order" "o"
WHERE "c"."id" = "o"."customer"
GROUP BY "c"."id"

And return the following result:

[(Customer[1], 2), (Customer[2], 1), (Customer[3], 1), (Customer[4], 1)]

But if there are customers that have no orders, they will not be selected by this query, because the condition WHERE "c"."id" = "o"."customer" doesn’t find any matching record in the Order table. In order to get the list of all customers, we should use the left_join() function:

left_join((c, count(o)) for c in Customer for o in c.orders)[:]

SELECT "c"."id", COUNT(DISTINCT "o"."id")
FROM "Customer" "c"
LEFT JOIN "Order" "o"
  ON "c"."id" = "o"."customer"
GROUP BY "c"."id"

Now we will get the list of all customers with the number of order equal to zero for customers which have no orders:

[(Customer[1], 2), (Customer[2], 1), (Customer[3], 1), (Customer[4], 1), (Customer[5], 0)]

We should mention that in most cases Pony can understand where LEFT JOIN is needed. For example, the same query can be written this way:

select((c, count(c.orders)) for c in Customer)[:]

SELECT "c"."id", COUNT(DISTINCT "order-1"."id")
FROM "Customer" "c"
LEFT JOIN "Order" "order-1"
  ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"

len(arg)

Return the number of objects in the collection. Can be used only within the query, similar to count().

• Parameters

  arg (generator) – a collection

  Return type

  numeric

Customer.select(lambda c: len(c.orders) > 2)

max(gen)

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

• Parameters

  gen (generator) – Python generator expression.

max(o.date_shipped for o in Order)

The equivalent query can be generated using the max() method.

min(\args, **kwargs*)

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

• Parameters

  gen (generator) – Python generator expression.

min(p.price for p in Product)

The equivalent query can be generated using the min() method.

random()

Returns a random value from 0 to 1. This functions, when encountered inside a query will be translated into RANDOM SQL query.

Example:

select(s.gpa for s in Student if s.gpa > random() * 5)

SELECT DISTINCT "s"."gpa"
FROM "student" "s"
WHERE "s"."gpa" > (random() * 5)

raw_sql(sql, result_type=None)

This function encapsulates a part of a query expressed in a raw SQL format. If the result_type is specified, Pony converts the result of raw SQL fragment to the specified format.

• Parameters

  • sql (str) – SQL statement text.

  • result_type (type) – the type of the SQL statement result.

>>> q = Person.select(lambda x: raw_sql('abs("x"."age")') > 25)
>>> print(q.get_sql())

SELECT "x"."id", "x"."name", "x"."age", "x"."dob"
FROM "Person" "x"
WHERE abs("x"."age") > 25

x = 10
y = 15
select(p for p in Person if raw_sql('p.age > $(x + y)'))
names = select(raw_sql('UPPER(p.name)') for p in Person)[:]
print(names)
['JOHN', 'MIKE', 'MARY']

See more examples here.

rollback()

Roll back the current transaction.

This top level rollback() function calls the rollback() method of each database object which was used in current transaction.

select(gen)

Translates the generator expression into SQL query and returns an instance of the Query class.

• Parameters

  • gen (generator) – Python generator expression.

  • globals (dict) –

  • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

  Return type

  Query or list

You can iterate over the result:

for p in select(p for p in Product):
    print p.name, p.price

If you need to get a list of objects you can get a full slice of the result:

prod_list = select(p for p in Product)[:]

The select() function can also return a list of single attributes or a list of tuples:

select(p.name for p in Product)
select((p1, p2) for p1 in Product
                for p2 in Product if p1.name == p2.name and p1 != p2)
select((p.name, count(p.orders)) for p in Product)

You can apply any Query method to the result, e.g. order_by() or count().

If you want to run a query over a relationship attribute, you can use the select() method of the relationship attribute.

show()

Prints out the entity definition or the value of attributes for an entity instance in the interactive mode.

• Parameters

  value – entity class or entity instance

>>> show(Person)
class Person(Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    age = Required(int)
    cars = Set(Car)
>>> show(mary)
instance of Person
id|name|age
--+----+---
2 |Mary|22

set_sql_debug(value=True, show_values=None)

Prints SQL statements being sent to the database to the console or to a log file. Previous name sql_debug is deprecated.

• Parameters

  • value (bool) – sets debugging on/off

  • show_values (bool) – when True, query parameters will be logged in addition to the SQL text (new in version 0.7.3)

Before version 0.7.3 it was a global flag. Now, in multi-threaded application, it should be set for each thread separately.

By default Pony sends debug information to stdout. If you have the standard Python logging configured, Pony will use it instead. Here is how you can store debug information in a file:

import logging
logging.basicConfig(filename='pony.log', level=logging.INFO)

Note, that we had to specify the level=logging.INFO because the default standard logging level is WARNING and Pony uses the INFO level for its messages by default. Pony uses two loggers: pony.orm.sql for SQL statements that it sends to the database and pony.orm for all other messages.

sql_debugging(value=True, show_values=None)

Context manager, use it for enabling/disabling logging SQL queries for a specific part of your code. If you need to turn on debugging for the whole db_session, use the similar parameters of db_session() decorator or context manager.

• Parameters

  • value (bool) – sets debugging on/off

  • show_values (bool) – when True, query parameters will be logged in addition to the SQL text (new in version 0.7.3)

with sql_debugging:  # turn debug on for a specific query
    result = Person.select()
with sql_debugging(show_values=True):  # log with query params
    result = Person.select()
with sql_debugging(False):  # turn debug off for a specific query
    result = Person.select()

sum(gen, distinct=None)

Return the sum of all values selected from the database.

• Parameters

  • gen (generator) – Python generator expression

  • distinct (bool) – distinct option

  Return type

  numeric

  Returns

  a number. If the query returns no items, the sum() method returns 0.

sum(o.total_price for o in Order)

The equivalent query can be generated using the sum() method.

make_proxy(obj)

Make a proxy object for given object. By proxy object we call object which can be used in different sessions. It might be useful for applications that are not request-based (for example GUI standalone application).

• Parameters

  obj (entity) – entity object

  Return type

  EntityProxy

  Returns

  proxy object.

with db_session:
    user = User[id]
    current_user = make_proxy(user)
...
with db_session:
    print(current_user.name)