Writing queries

Now that we have the database with five objects saved in it, we can try some queries. For example, this is the query which returns a list of persons who are older than twenty years old:

>>> select(p for p in Person if p.age > 20)
<pony.orm.core.Query at 0x105e74d10>

The select() function translates the Python generator into a SQL query and returns an instance of the Query class. This SQL query will be sent to the database once we start iterating over the query. One of the ways to get the list of objects is to apply the slice operator [:] to it:

>>> select(p for p in Person if p.age > 20)[:]
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
WHERE "p"."age" > 20
[Person[2], Person[3]]

As the result you can see the text of the SQL query which was sent to the database and the list of extracted objects. When we print out the query result, the entity instance is represented by the entity name and its primary key written in square brackets, e.g. Person[2].

For ordering the resulting list you can use the Query.order_by() method. If you need only a portion of the result set, you can use the slice operator, the exact same way as you would do that on a Python list. For example, if you want to sort all people by their name and extract the first two objects, you do it this way:

>>> select(p for p in Person).order_by(Person.name)[:2]
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
ORDER BY "p"."name"
LIMIT 2
[Person[3], Person[1]]

Sometimes, when working in the interactive mode, you might want to see the values of all object attributes. For this purpose, you can use the Query.show() method:

>>> select(p for p in Person).order_by(Person.name)[:2].show()
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
ORDER BY "p"."name"
LIMIT 2
id|name|age
--+----+---
3 |Bob |30
1 |John|20

The Query.show() method doesn’t display “to-many” attributes because it would require additional query to the database and could be bulky. That is why you can see no information about the related cars above. But if an instance has a “to-one” relationship, then it will be displayed:

>>> Car.select().show()
id|make  |model   |owner
--+------+--------+---------
1 |Toyota|Prius   |Person[2]
2 |Ford  |Explorer|Person[3]

If you don’t want to get a list of objects, but need to iterate over the resulting sequence, you can use the for loop without using the slice operator:

>>> persons = select(p for p in Person if 'o' in p.name)
>>> for p in persons:
...     print p.name, p.age
...
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
WHERE "p"."name" LIKE '%o%'
John 20
Bob 30

In the example above we get all Person objects with the name attribute containing the letter ‘o’ and display the person’s name and age.

A query does not necessarily have to return entity objects. For example, you can get a list, consisting of the object attribute:

>>> select(p.name for p in Person if p.age != 30)[:]
SELECT DISTINCT "p"."name"
FROM "Person" "p"
WHERE "p"."age" <> 30
[u'John', u'Mary']

Or a list of tuples:

>>> select((p, count(p.cars)) for p in Person)[:]
SELECT "p"."id", COUNT(DISTINCT "car-1"."id")
FROM "Person" "p"
  LEFT JOIN "Car" "car-1"
    ON "p"."id" = "car-1"."owner"
GROUP BY "p"."id"
[(Person[1], 0), (Person[2], 1), (Person[3], 1)]

In the example above we get a list of tuples consisting of a Person object and the number of cars they own.

With Pony you can also run aggregate queries. Here is an example of a query which returns the maximum age of a person:

>>> print max(p.age for p in Person)
SELECT MAX("p"."age")
FROM "Person" "p"
30

In the following parts of this manual you will see how you can write more complex queries.