We are happy to announce that we have created the Pony ORM mailing list
ponyorm-list@ponyorm.com. This mailing list is the place where you can ask questions, share experiences, or discuss the Pony ORM with other users and the Pony ORM developers. Please subscribe to the list using this link: ponyorm-list.ponyorm.com

Starting with this release Pony starts transactions with the database in the standard mode by default. Previously the default mode was the optimistic mode.

Standard and optimistic transaction modes

Both modes require that all interaction with the database should happen within the db_session. In order to achieve that you should either decorate the function which interacts with the database with the  @db_session decorator or use the db_session context manager. However what happens inside the db_session differs in the standard and optimistic mode.

Standard mode

When an application makes changes in the database, those changes are accumulated in the db_session cache. In the standard mode the changes which are made in the current transaction become visible to the subsequent SELECTs in the same transaction. This happens because Pony automatically flushes accumulated changes before each SELECT.

Optimistic mode

The optimistic transactions mode is designed to provide better scalability. The main goal of the optimistic mode it to minimize the time when the database or its part is locked due to the data update. In this mode Pony splits one transaction into two. The first transaction is used only for reading data from the database. All the changes are accumulated in the db_session cache. When it is time to do commit() Pony sends rollback() to the database in order to finish the read-only transaction and then starts short write-only transaction and flushes the cache. This mode is called optimistic because Pony hopes that no other transaction changes the same objects concurrently. During the write-only transaction Pony checks that the object’s attributes which are seen by the user have the same values as during the first transaction. If any change was detected, Pony raises the exception “UnrepeatableReadError: ‘Object Person[123] was updated outside of current transaction’“

Other changes

– Select for update. Now you can add “.for_update()“ at the end of the query in order to generate “SELECT .. FOR UPDATE“ SQL statement:

    x = 'john'
    u = select(u for u in User if u.login == x).for_update()[:]

Since SQLite doesn’t support the SELECT … FOR UPDATE syntax, Pony emulates such behavior by ensuring that such SELECT is executed within the transaction.

– Inline foreign keys definitions for MySQL bug workaround.
MySQL ignores inline foreign keys definitions. Pony provides a workaround for this bug replacing the inline definitions with foreign key constraints declared separately.

– Reconnect on connection failure. If the connection to the database was lost, now Pony will try to reconnect.

– Automatic foreign key indexes generation.

– Ability to control the column indexes creation. Now you can specify the keyword parameter “index“ during the attribute declaration.

“index=False“ – skip index creation (MySQL InnoDB will still create foreign key indexes automatically)
“index=True“ – index will be created with the default name
“index=’index_name’“ – create index with the specified name

If no “index“ option is specified then Pony still creates index for foreign keys with the default name.

– UUID datatype support has been added:

    >>> from pony.orm import *
    >>> from uuid import UUID, uuid4
    >>> db = Database('sqlite', ':memory:')
    >>> class MyEntity(db.Entity):
    ...  uid = PrimaryKey(UUID, default=uuid4)
    ...  name = Required(unicode)
    ... 
    >>> db.generate_mapping(create_tables=True)
    >>> with db_session:
    ...  obj1 = MyEntity(name='First')
    ...  obj2 = MyEntity(name='Second')
    ... 
    >>> for obj in MyEntity.select():
    ...  print obj.uid, obj.name
    ...  
    c719327f-128e-45b5-8c64-5af451e70925 First
    6ede633c-63e8-4ad8-9ff8-89d56f822588 Second
    >>>

– The parameter ‘check_tables’ in the “Database.generate_mapping()“ method is now deprecated. Starting with this release Pony always checks the existence of tables and columns for each entity.

– The support of PyGreSQL module is discontinued. Please use psycopg2 for connecting to the PostgreSQL database.

– Bug fixes.

Pony ORM 0.4.7 is released.

Starting with this release Pony requires that all database interactions are done within an explicitly created session. In order to set session boundaries one needs to use the @db_session decorator:

    @db_session
    def handle_request(params):
        ...

Another option is to use db_session as a context manager:

    with db_session:
        ...

When a decorated function or the context manager exits Pony clears the transaction cache and returns the database connection to the connection pool. If data was changed within the session then Pony will commit transaction to the database. If the function raises an exception then Pony performs a rollback of the transaction.

In previous versions, Pony would start a session implicitly and sometimes it would result in an UnrepeatableReadError because an obsolete transaction cache wouldn’t be cleared in a timely manner. Now Pony eliminates this problem and requires that all database interactions be done within db_session, even if all of the operations performed are read-only. If you omit the db_session decorator or context manager, Pony will raise the TransactionError exception with the following text: ‘db_session is required when working with the database’. You can omit the db_session only if you work with Python interactive shell.

Essentially @db_session replaces the @with_transaction decorator and now @with_transaction is deprecated. You can find a couple of examples here

This release also includes a couple of other updates:

– Pickling/unpickling objects is now supported (e.g. for storing in memcached)
– Query can be built step-by-step using the .filter() method: q = select(…); q.filter(…); q.filter(…)
– Lazy collections support: Set(…, lazy=true). When user requests items from a collection one by one, Pony loads all collection items after a threshold is reached. This behavior can be switched off by setting lazy to true, so Pony will not do prefetching and make a request to the database each time a new item is needed. This feature can be useful for large collections when only small and unpredictable portions of collection are required.
– MySQL fractional seconds fix. This bug appeared as an UnrepeatableReadError while working with MySQL DATETIME columns
– Several bug fixes

First of all we would like to say thanks to our users who gave us feedback and reported bugs. We really appreciate your help!
Below is the list of updates included to the Release 0.4.6:

Features:
– PostgreSQL psycopg2 driver support. Previously Pony supported only PyGreSQL driver. Now you can use

    db = Database('postgres', ...)

for psycopg2 driver and

    db = Database('pygresql', ...)

for PyGreSQL driver

– Integration with Bottle web framework

Bug fixes:
– MySQL should CONCAT(a, b) instead of a || b for string concatenation
– MySQL TRIM/LTRIM/RTRIM bug fixed
– PostgreSQL count(distinct(column1, column2)) works incorrectly if any of columns is null
– least(a, b, c) and greatest(a, b, c) are used instead of min(a, b, c) and max(a, b, c) in all dbproviders except sqlite
– GitHub #1 – Typo in regex
– GitHub #6 – Query results cache was not cleared after commit
– Aggregation bug fixed (unnecessary joins were removed)
– Use ‘power’ function for all db providers instead of ‘**’
– Collection attributes cannot be used for ordering
– Numerous small bugs were fixed

Enhancements:
– Common Database Pool implementation added to dbapiprovider module
– Numerous optimizations and refactorings

Pony ORM 0.4.5 is released. You can install it using pip:

    pip install pony

or upgrade it:

    pip install --upgrade pony

if you had the previous version installed.
Also you can download the sources from GitHub: github.com/ponyorm/pony

Pony ORM 0.4.5 Release Notes:

Feature: Now Pony can be easily integrated with Flask. Use Pony’s with_transaction decorator this way:  app.wsgi_app = with_transaction(app.wsgi_app).

Bug fix: Nested calls of functions decorated with @with_transaction didn’t work properly.
Now only the top level function decorated with @with_transaction does commit() or rollback() and releases the database connection. All nested calls ignore @with_transaction decorator.

Each function which works with Pony ORM should be wrapped with @with_transaction decorator. This decorator automatically does commit (or rollback in case of an exception happened inside the function), clears the cache and returns the database connection to the connection pool.

Fix Git issue #4: “Ambiguous column” error when using inheritance

Bug fix: Secondary keys (unique indexes) were not stored in cache.

Enhancement: Collections now have the ‘create’ method which can create and add a new collection item in one step.

Enhancement: Improved __repr__ and __str__ functions for collections.

The following updates were included in Pony ORM Release 0.4.4:

Enhancement: Suppress warnings when table already exists for mysql

Bug fix: Many-to-many “Duplicate primary key” error

Enhancement: Some exceptions text was improved