Working with db_session

The code which interacts with the database has to be placed within a database session. The session sets the borders of a conversation with the database. Each application thread which works with the database establishes a separate database session and uses a separate instance of an Identity Map. This Identity Map works as a cache, helping to avoid a database query when you access an object by its primary or unique key and it is already stored in the Identity Map. In order to work with the database using the database session you can use the @db_session() decorator or db_session() context manager. When the session ends it does the following actions:

• Commits transaction if data was changed and no exceptions occurred otherwise it rolls back transaction.

• Returns the database connection to the connection pool.

• Clears the Identity Map cache.

If you forget to specify the db_session() where necessary, Pony will raise the exception TransactionError: db_session is required when working with the database.

Example of using the @db_session() decorator:

@db_session
def check_user(username):
    return User.exists(username=username)

Example of using the db_session() context manager:

def process_request():
    ...
    with db_session:
        u = User.get(username=username)
        ...

Note

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.

If you’ll try to access instance’s attributes which were not loaded from the database outside of the db_session() scope, you’ll get the DatabaseSessionIsOver exception. For example:

DatabaseSessionIsOver: Cannot load attribute Customer[3].name: the database session is over

This happens because by this moment the connection to the database is already returned to the connection pool, transaction is closed and we cannot send any queries to the database.

When Pony reads objects from the database it puts those objects to the Identity Map. Later, when you update an object’s attributes, create or delete an object, the changes will be accumulated in the Identity Map first. The changes will be saved in the database on transaction commit or before calling the following methods: select(), get(), exists(), execute().

db_session and the transaction scope

Usually you will have a single transaction within the db_session(). There is no explicit command for starting a transaction. A transaction begins with the first SQL query sent to the database. Before sending the first query, Pony gets a database connection from the connection pool. Any following SQL queries will be executed in the context of the same transaction.

Note

Python driver for SQLite doesn’t start a transaction on a SELECT statement. It only begins a transaction on a statement which can modify the database: INSERT, UPDATE, DELETE. Other drivers start a transaction on any SQL statement, including SELECT.

A transaction ends when it is committed or rolled back using commit() or rollback() calls or implicitly by leaving the db_session() scope.

@db_session
def func():
    # a new transaction is started
    p = Product[123]
    p.price += 10
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

Several transactions within the same db_session

If you need to have more than one transaction within the same database session you can call commit() or rollback() at any time during the session, and then the next query will start a new transaction. The Identity Map keeps data after the manual commit(), but if you call rollback() the cache will be cleared.

@db_session
def func1():
    p1 = Product[123]
    p1.price += 10
    commit()          # the first transaction is committed
    p2 = Product[456] # a new transaction is started
    p2.price -= 10

Nested db_session

If you enter the db_session() scope recursively, for example by calling a function which is decorated with the db_session() decorator from another function which is decorated with db_session(), Pony will not create a new session, but will share the same session for both functions. The database session ends on leaving the scope of the outermost db_session() decorator or context manager.

What if inner db_session() has different settings? For example, the outer one is a default db_session() and the inner one is defined as db_session(optimistic=False)?

Currently Pony checks inner db_session() options, and does one of the following:

1. If inner db_session() uses options incompatible with the outer db_session() (ddl=True or serializable=True), Pony throws an exception.

2. For sql_debug option Pony uses new sql_debug option value inside the inner db_session() and restores it when returning to the outer db_session().

3. Other options (strict, optimistic, immediate and retry) are ignored for the inner db_session().

If rollback() is called inside inner db_session(), it will be applied to the outer db_session().

Some databases support nested transactions, but at the moment Pony doesn’t support this.

db_session cache

Pony caches data at several stages for increasing performance. It caches:

• The results of a generator expression translation. If the same generator expression query is used several times within the program, it will be translated to SQL only once. This cache is global for entire program, not only for a single database session.

• Objects which were created or loaded from the database. Pony keeps these objects in the Identity Map. This cache is cleared on leaving the db_session() scope or on transaction rollback.

• Query results. Pony returns the query result from the cache if the same query is called with the same parameters once again. This cache is cleared once any of entity instances is changed. This cache is cleared on leaving the db_session() scope or on transaction rollback.

Using db_session with generator functions or coroutines

The @db_session() decorator can be used with generator functions or coroutines too. The generator function is the function that contains the yield keyword inside it. The coroutine is a function which is defined using the async def or decorated with @asyncio.coroutine.

If inside such a generator function or coroutine you’ll try to use the db_session context manager, it will not work properly, because in Python context managers cannot intercept generator suspension. Instead, you need to wrap you generator function or coroutine with the @db_session decorator.

In other words, don’t do this:

def my_generator(x):
    with db_session: # it won't work here!
        obj = MyEntity.get(id=x)
        yield obj

Do this instead:

@db_session
def my_generator( x ):
    obj = MyEntity.get(id=x)
    yield obj

With regular functions, the @db_session() decorator works as a scope. When your program leaves the db_session() scope, Pony finishes the transaction by performing commit (or rollback) and clears the db_session cache.

In case of a generator, the program can reenter the generator code for several times. In this case, when your program leaves the generator code, the db_session is not over, but suspended and Pony doesn’t clear the cache. In the same time, we don’t know if the program will come back to this generator code again. That is why you have to explicitly commit or rollback current transaction before the program leaves the generator on yield. On regular functions Pony calls commit() or rollback() automatically on leaving the db_session() scope.

In essence, here is the difference when using db_session() with generator functions:

1. You have to call commit() or rollback() before the yield expression explicitly.

2. Pony doesn’t clear the transaction cache, so you can continue using loaded objects when coming back to the same generator.

3. With a generator function, the db_session() can be used only as a decorator, not a context manager. This is because in Python the context manager cannot understand that it was left on yield.

4. The db_session() parameters, such as retry, serializable cannot be used with generator functions. The only parameter that can be used in this case is immediate.

Parameters of db_session

As it was mentioned above db_session() can be used as a decorator or a context manager. It can receive parameters which are described in the API Reference.