Pony ORM at EuroPython 2014

Recently we had the “How Pony ORM translates Python generators to SQL queries” session at EuroPython in Berlin.
In this talk we shared interesting implementation details of our mapper. Also there is a little bit of comparison with other ORMs. Thanks to everyone who was there and for your feedback!

Here is the video from the conference:

And here are the slides:

Pony ORM Release 0.5.1

Before this release, if a text attribute was defined without the max length specified (e.g. name = Required(unicode)), Pony set the maximum length equal to 200 and used SQL type VARCHAR(200).

Actually, PostgreSQL and SQLite do not require specifying the maximum length for strings. Starting with this release such text attributes are declared as TEXT in SQLite and PostgreSQL. In these DBMSes, the TEXT datatype has the same performance as VARCHAR(N) and doesn’t have arbitrary length restrictions.

For other DBMSes default VARCHAR limit was increased up to 255 in MySQL and to 1000 in Oracle.


  • Correct parsing of datetime values with the ‘T’ separator between date and time
  • Entity.delete() bug fixed
  • Lazy attribute loading bug fixed

Pony ORM Release 0.5

We are proud to announce the Pony ORM Release 0.5! This release brings lots of improvements and new features which are listed below.

Changes since 0.4.9

  • New transaction model (link)
  • New method Query.filter() allows step-by-step query construction (link)
  • New method Database.bind() simplifies testing and allows using different settings for development and production (link)
  • New method Query.page() simplifies pagination (link)
  • New method MyEntity.select_random(N) is effective for large tables (link)
  • New method Query.random(N) for selecting random instances (link)
  • Support of new concat() function inside declarative queries
    New before_insert(), before_update(), before_delete() entity instance hooks which can be overridden
  • Ability to specify sequence_name=’seq_name’ for PrimaryKey attributes in Oracle database
  • Ability to create new entity instances specifying the value of the primary key instead of the object
  • Ability to read entity object attributes outside of the db_session
  • Ability to use lambdas as a reference to an entity in relationship attribute declarations (link)
  • The names of tables, indexes and constraints in the database creation script now are sorted in the alphabetical order
    In MySQL and PostgreSQL Pony converts the table names to the lower case. In Oracle – to the upper case. In SQLite leaves as is.
  • The option options.MAX_FETCH_COUNT is set to None by default now
  • The support of PyGreSQL is discontinued, using psycopg2 instead
  • Added pony.__version__ attribute
  • Multiple bugs were fixed
  • Stability and performance improvements

Pony ORM Release 0.5rc1

  • Before this release Pony used the exact letter case of an entity for the MySQL database table names (unless you specify the table name explicitly using the _table_ option).
    This approach turned out to be not reliable on all platforms: “MySQL table names are case-sensitive depending on the filesystem of the server. Since Windows and Mac are a case-insensitive OS, and Linux is case-sensitive; the MySQL database treats the uppercase table name differently than lowercase table name on Linux machines.”

    The official MySQL documentation reads:
    To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use. Following this recommendation, starting with this release, Pony converts all the table names during its creation in accordance with the style of the respective database system. In MySQL and PostgreSQL Pony converts the table names to the lower case. In Oracle – to the upper case. In SQLite there is no need to convert the letter case – SQLite works with the table name letter case equally on all platforms.In order to make the new release compatible with your tables created with the previous Pony releases you have a couple of options:

    • Specify the table names for each entity explicitly:
          class MyEntity(db.Entity):
              _table_ = "MyEnity"
    • Convert the names of your tables to the lower case using SQL commands manually:
          RENAME TABLE `MyEntity` TO `myentity`
  • Starting with this release the names of tables, indexes and constraints in the database creation script are sorted in the alphabetical order.
  • Now you can use lambdas instead of parenthesis for the entities which are declared later in your code:
        class User(db.Entity):
            name = Required(unicode)
            photos = Set(lambda: Photo)
        class Photo(db.Entity):
            content = Required(buffer)
            user = Required(User)

    This can be useful if you want your IDE to check the names of declared entities and highlight typos.

  • Instances now have the before_insert, before_update, before_delete hooks:
        class MyEntity(db.Entity):
            def before_insert(self):
                print '%s is about to be inserted!' % self
            def before_update(self):
                print '%s is about to be updated!' % self
            def before_delete(self):
                print '%s is about to be deleted!' % self
  • Now options.MAX_FETCH_COUNT is set to None by default. Before this release Pony would throw the TooManyRowsFound exception if the number of instances returned by a query exceeds the MAX_FETCH_COUNT. Since this parameter can vary greatly, Pony doesn’t set it by default anymore.
  • We have added the concat() function and now you can do the following:
    select(concat(s.name, ':', s.dob.year, ':', s.scholarship) 
           for s in Student)
  • With the new Pony release you have the read-only access to the entity instances outside of the db_session. Before this release any attempt to use an instance outside of the db_session would result in a TransactionRolledBack exception. Now you can get the attribute values, but if the attribute that you’re trying to access was not loaded, then you’ll get the DatabaseSessionIsOver exception. The same exception will be raised in case you want to change attribute values.
    We hope that this option will not be misused. Most of the time you want to use entity instances within the db_session. If you find yourself using the instances outside of the db_session often, you probably doing something strange.
  • Now when you create new instances you can use the value of the primary key instead of the object. This is how you created an instance of a Student before:
        student = Student(name='Student name', group=Group[123])

    and now you can pass a primary key of the Group object instead:

        student = Student(name='Student name', group=123)

    The same approach can be used for the get() method.

  • Now you can specify the sequence name for PrimaryKey attributes for Oracle databases using the keyword argument sequence_name='seq_name'
  • The bug #41 was fixed. Now you can use the same variable name in subsequent filters and it can have different values.
  • Numerous other bugs were fixed

Pony ORM Release 0.5-beta

New transaction model

Before this release Pony supported two separate transaction modes: standard and optimistic. In this release we’ve combined both of them into one. Now user can choose which locking mechanism should be use for any specific object. By default Pony uses the optimistic locking. This means, that the database doesn’t lock any objects in the database. In order to be sure that those objects weren’t updated by a concurrent transaction, Pony checks the corresponding database rows during update.

Sometimes a user might want to go with the pessimistic locking. With this approach the database locks the corresponding rows for the exclusive access in order to prevent concurrent modification. It can be done with the help of for update() methods. In this case there is no need for Pony to do the optimistic check. You can use any of the following methods:

    prod_list = select(p for p in Product if p.price > 100).for_update()
    prod_list = Product.select(lambda p: p.price > 100).for_update()
    p = Product.get_for_update(id=123)

Pony ORM transaction processing is described in more detail in the newly created documentation chapter Transactions.


In this release we have added the ability to add comments to our online documentation, please leave your feedback, questions, comments. We need you in order to make Pony ORM documentation better.

Filtering the query results

With this release we introduce a new method of the Query object: filter(). It can be convenient for applying conditions for an existing query. You can find more information in the documentation

Database late binding

Pony ORM users were asking us to provide a way to bind the database at a later stage. This is convenient for testing. The goal of Pony ORM team is to provide a convenient tool for working with databases and in this release we’ve added such a possibility.

Before this release the process of mapping entities to the database was as following:

    db = Database('sqlite', 'database.sqlite')

    class Entity1(db.Entity):

    # other entities declaration


Now besides the approach described above, you can use the following way:

    db = Database()

    class Entity1(db.Entity):

    # other entities declaration

    db.bind('sqlite', 'database.sqlite')

Other features and bugfixes

  • Added method Query.page() for pagination
  • Added count() method for collections. E.g. Customer[123].orders.count()
  • Added ORDER BY RANDOM(): MyEntity.select_random(N), MyEntity.select(...).random(N), select(...).random(N) – N is the number of elements to be selected
  • Bugfix in exists() subquery
  • Bugfix when the same item was added and removed within the same transaction
  • Aggregations bugfix
  • The support of PyGreSQL is discontinued. Using psycopg2 instead
  • Added pony.__version__ attribute
  • Stability and performance improvements

Now you can create private ER diagrams with PonyORM Diagram Editor

We are excited to announce the launch of our new PonyORM Diagram Editor website. It is located at the same address: https://editor.ponyorm.com

The main new features include:

  • The ability to create private ER diagrams. Such diagrams can be viewed only by the owner of the diagram and users which were invited by the diagram owner.
  • Revision history. This feature allows the user to track changes and restore a diagram to a previous version.
  • Now you can share your diagram with other people sending invitations from the website.

You can see the available plans here: https://editor.ponyorm.com/plans

Pony ORM Release 0.4.9

In this release we added new functionality which was requested by Pony users. The main features include the possibility to delete tables in the database and recreate tables with all necessary foreign key constraints and indexes. Let’s see how it works in detail.

Drop tables

There are 4 methods for removing tables in the database, which you can use now:

Where db is an instance of the Database class.

This method drops all tables, which are related to the current mapping. When this method is called without parameters, Pony will remove tables only if none of them contain any data. In case at least one of them is not empty the method will raise the TableIsNotEmpty exception without dropping any table. In order to drop tables with the data you should pass the parameter with_all_data=True: drop_all_tables(with_all_data=True).

If you specify with_all_data=True, then Pony will remove all tables which are mapped to declared entities even if they have data. This parameter has the same meaning for all methods below.

db.drop_table(table_name, if_exists=False, with_all_data=False)
Where db is an instance of the Database class.

This method drops a table. If such table doesn’t exist the method raises the exception TableDoesNotExist. Note, that table_name is case sensitive. If the parameter if_exists is set to True, then it will not raise the TableDoesNotExist exception if there is no such table in the database.

Where MyEntity is a declared entity class.
This method removes a table which is mapped to the entity.

Where MyEntity is a declared entity class and my_collection is a Set attribute:

    class MyEntity(db.Entity):
        my_collection = Set(…)

This method drops a table, which is associated with the Set attribute. The primary role of this method is to drop an intermediate table, which is used for establishing many-to-many relationship between two Set attributes. But if you call this method for many-to-one relationship, it will try to remove the table used for the entity at the other side of the many-to-one relationship.

Create tables

Pony now has a separate method for creating tables and corresponding foreign keys and indexes:

Where db is an instance of the Database class.
This method checks the existing mapping and creates tables for entities if they don’t exist. Also, Pony will check if foreign keys and indexes exist and create them if they are missing.

In previous releases the method generate_mapping() always checked if the tables in the database match with the entities. Now you can switch this check off. This can be useful if you want just generate mapping and create tables later.
This gives you the following options for mapping generation:

– db.generate_mapping(create_tables=True) – create tables, foreign key references and indexes if they don’t exist
– db.generate_mapping() – assuming that tables are already exist, check if the existing tables match with the enitites
– db.generate_mapping(check_tables=False) – neither create nor check tables, useful if you plan to call db.create_tables() later.

Other new features

Starting with the release 0.4.9, Pony automatically enables foreign key support in SQLite. By default SQLite doesn’t check foreign key constraints, and in previous Pony releases foreign key constraints in SQLite were not enforced.

Where db is an instance of the Database class.
Close the database connection for the current thread if it was opened.

Comparison methods for the Entity class were added. Now entity instances can be sorted by its primary key values.

Entity.exists(…) method was added. You can check the entity attributes for equality:


or use a lambda condition:

Order.exists(lambda o: o.total_price > 500 and count(o.items) <=3)

Reported bugs were fixed, new tests were added.

Thanks to all Pony users for sending us their feedback and telling us how they are using Pony. We appreciate it!
As always we are looking forward to your comments and suggestions at our mailing list ponyorm-list.ponyorm.com

Pony ORM Release 0.4.8

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 Release 0.4.7

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:

    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

Pony ORM Release 0.4.6

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:

– 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
– 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

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