Loading objects from the database

Getting an object by primary key

The simplest case is when we want to retrieve an object by its primary key. To accomplish this in Pony, the user simply needs to put the primary key in square brackets, after the class name. For example, to extract a customer with the primary key value of 123, we can write:

customer1 = Customer[123]

The same syntax also works for objects with composite keys; we just need to list the elements of the composite primary key, separated by commas, in the same order that the attributes were defined in the entity class description:

order_item = OrderItem[order1, product1]

Pony raises the ObjectNotFound exception if object with such primary key doesn’t exist.

Getting one object by unique combination of attributes

If you want to retrieve one object not by its primary key, but by another combination of attributes, you can use the get() method of an entity. In most cases, it is used for getting an object by the secondary unique key, but it can also be used to search by any other combination of attributes. As a parameter of the get() method, you need to specify the names of the attributes and their values. For example, if you want to receive a product under the name “Product 1”, and you believe that database has only one product under this name, you can write:

product1 = Product.get(name='Product1')

If no object is found, get() returns None. If multiple objects are found, MultipleObjectsFoundError exception is raised.

You may want to use the get() method with primary key when we want to get None instead of ObjectNotFound exception if the object does not exists in database.

Method get() can also receive a lambda function as a single positioning argument. This method returns an instance of an entity, and not an object of the Query class.

Getting several objects

In order to retrieve several objects from a database, you should use the select() method of an entity. Its argument is a lambda function, which has a single parameter, symbolizing an instance of an object in the database. Inside this function, you can write conditions, by which you want to select objects. For example, if you want to find all products with the price higher than 100, you can write:

products = Product.select(lambda p: p.price > 100)

This lambda function will not be executed in Python. Instead, it will be translated into the following SQL query:

SELECT "p"."id", "p"."name", "p"."description",
       "p"."picture", "p"."price", "p"."quantity"
FROM "Product" "p"
WHERE "p"."price" > 100

The select() method returns an instance of the Query class. If you start iterating over this object, the SQL query will be sent to the database and you will get the sequence of entity instances. For example, this is how you can print out all product names and it’s price:

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

If you don’t want to iterate over a query, but need just to get a list of objects, you can do so this way:

product_list = Product.select(lambda p: p.price > 100)[:]

Here we get a full slice [:] from the query. This is an equivalent of converting a query to a list:

product_list = list(Product.select(lambda p: p.price > 100))

Using parameters in queries

You can use variables in queries. Pony will pass those variables as parameters to the SQL query. One important advantage of declarative query syntax in Pony is that it offers full protection from SQL-injections, as all external parameters will be properly escaped.

Here is the example:

x = 100
products = Product.select(lambda p: p.price > x)

The SQL query which will be generated will look this way:

SELECT "p"."id", "p"."name", "p"."description",
       "p"."picture", "p"."price", "p"."quantity"
FROM "Product" "p"
WHERE "p"."price" > ?

This way the value of x will be passed as the SQL query parameter, which completely eliminates the risk of SQL-injection.

Sorting query results

If you need to sort objects in a certain order, you can use the Query.order_by() method.

Product.select(lambda p: p.price > 100).order_by(desc(Product.price))

In this example, we display names and prices of all products with price higher than 100 in a descending order.

The methods of the Query object modify the SQL query which will be sent to the database. Here is the SQL generated for the previous example:

SELECT "p"."id", "p"."name", "p"."description",
       "p"."picture", "p"."price", "p"."quantity"
FROM "Product" "p"
WHERE "p"."price" > 100
ORDER BY "p"."price" DESC

The Query.order_by() method can also receive a lambda function as a parameter:

Product.select(lambda p: p.price > 100).order_by(lambda p: desc(p.price))

Using the lambda function inside the .. code-block:: python method allows using advanced sorting expressions. For example, this is how you can sort our customers by the total price of their orders in the descending order:

Customer.select().order_by(lambda c: desc(sum(c.orders.total_price)))

In order to sort the result by several attributes, you need to separate them by a comma. For example, if you want to sort products by price in descending order, while displaying products with similar prices in alphabetical order, you can do it this way:

Product.select(lambda p: p.price > 100).order_by(desc(Product.price), Product.name)

The same query, but using lambda function will look this way:

Product.select(lambda p: p.price > 100).order_by(lambda p: (desc(p.price), p.name))

Note that according to Python syntax, if you return more than one element from lambda, you need to put them into parenthesis.

Limiting the number of selected objects

It is possible to limit the number of objects returned by a query by using the Query.limit() method, or by more compact Python slice notation. For example, this is how you can get the ten most expensive products:

Product.select().order_by(lambda p: desc(p.price))[:10]

The result of a slice is not a query object, but a final list of entity instances.

You can also use the Query.page() method as a convenient way of pagination the query results:

Product.select().order_by(lambda p: desc(p.price)).page(1)

Traversing relationships

In Pony you can traverse object relationships:

order = Order[123]
customer = order.customer
print customer.name

Pony tries to minimize the number of queries sent to the database. In the example above, if the requested Customer object was already loaded to the cache, Pony will return the object from the cache without sending a query to the database. But, if an object was not loaded yet, Pony still will not send a query immediately. Instead, it will create a “seed” object first. The seed is an object which has only the primary key initialized. Pony does not know how this object will be used, and there is always the possibility that only the primary key is needed.

In the example above, Pony get the object from database in the third line in, when accessing the name attribute. By using the “seed” concept, Pony achieves high efficiency and solves the “N+1” problem, which is a weakness of many other mappers.

Traversing is possible in the “to-many” direction as well. For example, if you have a Customer object and you loop through its orders attribute, you can do it this way:

c = Customer[123]
for order in c.orders:
    print order.state, order.price