Getting Started with Pony¶

Installing¶

To install Pony, type the following command into the command prompt:

pip install pony

Pony can be installed on Python 2.7 or Python 3. If you are going to work with SQLite database, you don’t need to install anything else. If you wish to use another database, you need to have the access to the database and have the corresponding database driver installed:

PostgreSQL: psycopg (psycopg3) (version 3.1 or newer)
MySQL: MySQL-python or PyMySQL
Oracle: cx_Oracle
CockroachDB: psycopg (psycopg3) (version 3.1 or newer)

To make sure Pony has been successfully installed, launch a Python interpreter in interactive mode and type:

>>> from pony.orm import *

This imports the entire (and not very large) set of classes and functions necessary for working with Pony. Eventually you can choose what to import, but we recommend using import * at first.

If you don’t want to import everything into global namespace, you can import the orm package only:

>>> from pony import orm

In this case you don’t load all Pony’s functions into the global namespace, but it will require you to use orm as a prefix to any Pony’s function and decorator.

Note

The examples in this tutorial are written for the asynchronous mode: queries are executed with await. Top-level await works in IPython / Jupyter; in a plain python REPL wrap the code in an async def and call it with asyncio.run(...).

The best way to become familiar with Pony is to play around with it in interactive mode. Let’s create a sample database containing the entity class Person, add three objects to it, and write a query.

Creating the database object¶

Entities in Pony are connected to a database. This is why we need to create the database object first. In the Python interpreter, type:

>>> db = Database()

Defining entities¶

Now, let’s create two entities – Person and Car. The entity Person has two attributes – name and age, and Car has attributes make and model. In the Python interpreter, type the following code:

>>> class Person(db.Entity):
...     name = Required(str)
...     age = Required(int)
...     cars = Set('Car')
...
>>> class Car(db.Entity):
...     make = Required(str)
...     model = Required(str)
...     owner = Required(Person)
...
>>>

The classes that we have created are derived from the Database.Entity attribute of the Database object. It means that they are not ordinary classes, but entities. The entity instances are stored in the database, which is bound to the db variable. With Pony you can work with several databases at the same time, but each entity belongs to one specific database.

Inside the entity Person we have created three attributes – name, age and cars. The name and age are mandatory attributes. In other words, these attributes cannot have the None value. The name is a string attribute, while age is numeric.

The cars attribute is declared as Set and has the Car type. This means that this is a relationship. It can keep a collection of instances of the Car entity. "Car" is specified as a string here because we didn’t declare the entity Car by that moment yet.

The Car entity has three mandatory attributes: make and model are strings, and the owner attribute is the other side of the one-to-many relationship. Relationships in Pony are always defined by two attributes which represent both sides of a relationship.

If we need to create a many-to-many relationship between two entities, we should declare two Set attributes at both ends. Pony creates the intermediate database table automatically.

The str type is used for representing an unicode string in Python 3. Python 2 has two types for strings - str and unicode. Starting with the Pony Release 0.6, you can use either str or unicode for string attributes, both of them mean an unicode string. We recommend using the str type for string attributes, because it looks more natural in Python 3.

If you need to check an entity definition in the interactive mode, you can use the show() function. Pass the entity class or the entity instance to this function for printing out the definition:

>>> show(Person)
class Person(Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    age = Required(int)
    cars = Set(Car)

You may notice that the entity got one extra attribute named id. Why did that happen?

Each entity must contain a primary key, which allows distinguishing one entity from the other. Since we have not set the primary key attribute manually, it was created automatically. If the primary key is created automatically, it is named as id and has a numeric format. If the primary key attribute is created manually, you can specify the name and type of your choice. Pony also supports composite primary keys.

When the primary key is created automatically, it always has the option auto set to True. It means that the value for this attribute will be assigned automatically using the database’s incremental counter or a database sequence.

Database binding¶

The database object has the Database.bind() method. It is used for attaching declared entities to a specific database. If you want to play with Pony in the interactive mode, you can use the SQLite database created in memory:

>>> db.bind(provider='sqlite', filename=':memory:')

Currently Pony supports 5 database types: 'sqlite', 'mysql', 'postgresql', 'cockroach' and 'oracle'. The subsequent parameters are specific to each database. They are the same ones that you would use if you were connecting to the database through the DB-API module.

For SQLite, either the database filename or the string ‘:memory:’ (or ‘:sharedmemory:’ if you use threads) must be specified as the parameter, depending on where the database is being created. If the database is created in-memory, it will be deleted once the interactive session in Python is over. In order to work with the database stored in a file, you can replace the previous line with the following:

>>> db.bind(provider='sqlite', filename='database.sqlite', create_db=True)

In this case, if the database file does not exist, it will be created. In our example, we can use a database created in-memory.

If you’re using another database, you need to have the specific database adapter installed. For PostgreSQL and CockroachDB Pony uses psycopg (psycopg3). For MySQL either MySQLdb or pymysql adapter. For Oracle Pony uses the cx_Oracle adapter.

Here is how you can get connected to the databases:

# SQLite
db.bind(provider='sqlite', filename=':sharedmemory:')
# or
db.bind(provider='sqlite', filename='database.sqlite', create_db=True)

# PostgreSQL
db.bind(provider='postgres', user='', password='', host='', database='')

# MySQL
db.bind(provider='mysql', host='', user='', passwd='', db='')

# Oracle
db.bind(provider='oracle', user='', password='', dsn='')

# CockroachDB
db.bind(provider='cockroach', user='', password='', host='', database='', )

Mapping entities to database tables¶

Now we need to create database tables where we will persist our data. For this purpose, we need to call the generate_mapping() method on the Database object:

>>> db.generate_mapping(create_tables=True)

The parameter create_tables=True indicates that, if the tables do not already exist, then they will be created using the CREATE TABLE command.

All entities connected to the database must be defined before calling generate_mapping() method.

Using the debug mode¶

Using the set_sql_debug() function, you can see the SQL commands that Pony sends to the database. In order to turn the debug mode on, type the following:

>>> set_sql_debug(True)

If this command is executed before calling the generate_mapping() method, then during the creation of the tables, you will see the SQL code used to generate them.

Creating entity instances¶

Now, let’s create five objects that describe three persons and two cars, and save this information in the database:

>>> p1 = Person(name='John', age=20)
>>> p2 = Person(name='Mary', age=22)
>>> p3 = Person(name='Bob', age=30)
>>> c1 = Car(make='Toyota', model='Prius', owner=p2)
>>> c2 = Car(make='Ford', model='Explorer', owner=p3)
>>> await commit()

Pony does not save objects in the database immediately. These objects will be saved only after the commit() function is called. If the debug mode is turned on, then during the commit(), you will see five INSERT commands sent to the database.

db_session¶

The code which interacts with the database has to be placed within a database session. When you work with Python’s interactive shell you don’t need to worry about the database session, because it is maintained by Pony automatically. But when you use Pony in your application, all database interactions should be done within a database session. In order to do that you need to wrap the functions that work with the database with the db_session() decorator:

@db_session
async def print_person_name(person_id):
    p = Person[person_id]
    print p.name
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

@db_session
async def add_car(person_id, make, model):
    Car(make=make, model=model, owner=Person[person_id])
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

In asynchronous mode the same session is opened with async with db_session: and the operations inside it are awaited; see Async mode below.

The db_session() decorator performs the following actions on exiting function:

Performs rollback of transaction if the function raises an exception
Commits transaction if data was changed and no exceptions occurred
Returns the database connection to the connection pool
Clears the database session cache

Even if a function just reads data and does not make any changes, it should use the db_session() in order to return the connection to the connection pool.

The entity instances are valid only within the db_session(). If you need to render an HTML template using those objects, you should do this within the db_session().

Another option for working with the database is using the db_session() as the context manager instead of the decorator:

with db_session:
    p = Person(name='Kate', age=33)
    Car(make='Audi', model='R8', owner=p)
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

Async mode¶

Besides the synchronous mode described above, Pony can work in asynchronous mode, which is useful for applications built on top of asyncio (an aiohttp service, a Responder application, and so on). Two things are required for it:

an asynchronous provider - postgres_async (PostgreSQL via psycopg3) or mariadb_async (MariaDB / MySQL via the mariadb connector);
async with db_session: instead of with db_session:, and await for every operation which reads from or writes to the database.

Database objects created with an asynchronous provider can be used in both ways: they support synchronous sessions as well:

from pony.orm import *

db = Database('postgres_async', dsn='dbname=mydb user=postgres host=localhost')

class Person(db.Entity):
    name = Required(str)
    age = Required(int)
    bio = Optional(str, lazy=True)
    cars = Set('Car')

db.generate_mapping(create_tables=True)   # schema operations are synchronous:
                                          # call them before the event loop starts

async def main():
    async with db_session:                 # flush + commit on exit
        Person(name='John', age=20, bio='...')

    async with db_session:
        persons = await select(p for p in Person if p.age > 18)
        async for p in select(p for p in Person).order_by(Person.name):
            print(p.name)
        person = persons[0]
        await person.load('bio')           # lazy attribute: explicit load
        print(person.bio)

import asyncio
asyncio.run(main())

generate_mapping() and other schema operations are synchronous - call them before the event loop starts. Inside an async session they raise TransactionError (the two modes cannot be mixed); a call inside a coroutine outside a session works, but blocks the loop.

Collections are loaded explicitly as well, and after that they behave like ordinary Python sequences (for, len(), in, indexing):

async with db_session:
    person = (await select(p for p in Person))[0]
    await person.cars                      # load the collection
    for car in person.cars:
        print(car.make)

Accessing an attribute or a collection which is not loaded raises NotLoadedError instead of sending a hidden query, so the code that reads from the database is always visible. A database session belongs to one task: two coroutines running at the same time get separate sessions (and separate connections from the pool), and objects of one task are never visible in another one.

What is available in async mode — the whole query API is usable, awaited:

async with db_session:
    page = await select(p for p in Person).order_by(Person.name)[:10]
    page = await select(p for p in Person).page(2, pagesize=10)
    total = await select(p for p in Person).count()
    oldest = await select(p.age for p in Person).max()
    found = await select(p for p in Person if p.age > 18).exists()
    person = await select(p for p in Person).first()
    person = await Person.get(name='John')
    person = await Person[1]                    # доступ по первичному ключу
    name = await get(p.name for p in Person if p.age == 30)

    persons = await select(p for p in Person).prefetch(Person.dept)   # батчами
    for p in persons:
        p.dept.name                             # прочитано без await

    await delete(p for p in Person if p.age < 18)            # удаление
    person.cars.add(car); person.cars.remove(car)            # m2m

    await flush()          # явное управление транзакцией тоже доступно
    await commit()
    await rollback()

Warning

What is still synchronous-only:

databases other than PostgreSQL and MariaDB / MySQL;
schema operations (Database.generate_mapping() and Database.create_tables()) inside an async session: they are synchronous, so call them before the event loop starts (a call inside a coroutine outside a session is allowed, but it blocks the loop);
async generators under @db_session — wrap the iteration in async with db_session: instead;
mixing synchronous and asynchronous sessions in one transaction.

Everything else works in async mode as well: queries, aggregates, slicing and pagination, prefetch(), access by primary key (await Entity[pk]), Entity.get / Entity.exists, bulk deletion, many-to-many updates, load() for reverse attributes, explicit await flush()/commit()/rollback() and @db_session on coroutines. A synchronous operation used in an async session raises TransactionError with a hint.

Note

Two rules make async sessions different from synchronous ones whenever collections are involved:

Collections return seeds. After await person.cars the elements are loaded only partially (their primary key is known), so load their attributes explicitly: for car in person.cars: await car.load().
Deleting an object which has collections requires loading them first. In sync mode Pony loads them implicitly, in async mode the code has to do it: await person.cars and then person.delete(). Bulk deletion (await delete(...)) does not need the collections.

Whenever asynchronous code tries to use a synchronous operation, Pony raises TransactionError with a hint instead of performing a blocking call inside the event loop.

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>

In asynchronous mode the same query is written as await select(...) (which returns a list) or iterated with async for; see Async mode below.

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:

>>> await 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:

>>> await 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:

>>> await 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:

>>> await 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:

>>> await 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 await 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.

Getting objects¶

To get an object by its primary key you need to specify the primary key value in the square brackets:

>>> p1 = await Person[1]
>>> print p1.name
John

You may notice that no query was sent to the database. That happened because this object is already present in the database session cache. Caching reduces the number of requests that need to be sent to the database.

For retrieving the objects by other attributes, you can use the Entity.get() method:

>>> mary = await Person.get(name='Mary')

SELECT "id", "name", "age"
FROM "Person"
WHERE "name" = ?
[u'Mary']

>>> print mary.age
22

In this case, even though the object had already been loaded to the cache, the query still had to be sent to the database because the name attribute is not a unique key. The database session cache will only be used if we lookup an object by its primary or unique key.

You can pass an entity instance to the show() function in order to display the entity class and attribute values:

>>> show(mary)
instance of Person
id|name|age
--+----+---
2 |Mary|22

Updating an object¶

>>> mary.age += 1
>>> await commit()

Pony keeps track of all changed attributes. When the commit() function is executed, all objects that were updated during the current transaction will be saved in the database. Pony saves only those attributes, that were changed during the database session.

Writing raw SQL queries¶

If you need to select entities by a raw SQL query, you can do it this way:

>>> x = 25
>>> await Person.select_by_sql('SELECT * FROM Person p WHERE p.age < $x')

SELECT * FROM Person p WHERE p.age < ?
[25]

[Person[1], Person[2]]

If you want to work with the database directly, avoiding entities, you can use the Database.select() method:

>>> x = 20
>>> await db.select('name FROM Person WHERE age > $x')
SELECT name FROM Person WHERE age > ?
[20]

[u'Mary', u'Bob']

Pony examples¶

Instead of creating models manually, you can check the examples from the Pony distribution package:

>>> from pony.orm.examples.estore import *

Here you can see the database diagram for this example: https://editor.ponyorm.com/user/pony/eStore.

During the first import, there will be created the SQLite database with all the necessary tables. In order to fill it in with the data, you need to call the following function:

>>> populate_database()

This function will create objects and place them in the database.

After the objects have been created, you can try some queries. For example, here is how you can display the country where we have most of the customers:

>>> select((customer.country, count(customer))
...        for customer in Customer).order_by(-2).first()

SELECT "customer"."country", COUNT(DISTINCT "customer"."id")
FROM "Customer" "customer"
GROUP BY "customer"."country"
ORDER BY 2 DESC
LIMIT 1

In this example, we are grouping objects by the country, sorting them by the second column (the number of customers) in the reverse order, and then extracting the first row.

You can find more query examples in the test_queries() function in the pony.orm.examples.estore module.