One of the most often requests that we are getting from Pony users is adding support for Python 3. And today we are happy to announce that we’ve just finished development and have prepared Pony 0.6 Release Candidate with Python 3 support!

In order to install this release candidate version you need to execute the following command:

    pip install pony==0.6rc1

If you don’t specify the version explicitly, pip will install the latest stable release 0.5.4

The same Pony code runs both on Python 2 (≥ 2.6) and Python 3 (≥ 3.3). If you clone Pony git repository from GitHub you can use it on Python 2 and Python 3 without any modifications.

Now let’s see what changes we had to make along the way.

Unicode strings

As you know, Python 3 has some differences from Python 2 when it comes to strings. Python 2 provides two string types – str (byte string) and unicode (unicode string), whereas in Python 3 the str type represents unicode strings and the unicode type has gone.

When we were working on adding Python 3 support to Pony, one of our goals was to have the same Pony entity declarations working on both Python 2 and 3 in the same manner. In order to achieve this we had to make one non-backward compatible change – treat both str and unicode types as they are unicode strings in both Python 2 and 3. Pony just adds unicode as an alias to str in Python 3. When you importing * from pony.orm you will get this alias along with other stuff.

    attr1 = Required(str)
    #   is now the same as
    attr2 = Required(unicode)

    attr3 = Required(LongStr)
    #   is now the same as
    attr4 = Required(LongUnicode)

Before this release, Pony stored values of str and unicode attributes as unicode in the database, but for str attributes it had to convert unicode to byte string on reading from the database. Starting with the Pony Release 0.6 the attributes of str type in Python 2 behave as if they were declared as unicode attributes. There is no difference now if you specify str or unicode as the attribute type – you will have unicode string in Python and in the database.

The same thing is with the LongUnicode and LongStr. LongStr now is an alias to LongUnicode. This type uses unicode in Python and in the database.

Byte sequences

If you need to represent a byte sequence in Python 2, you can use the buffer type. In Python 3 the buffer type has gone, and Pony uses the bytes type which was added in Python 3 to represent binary data. But for the sake of backward compatibility we still keep buffer as an alias to the bytes type in Python 3. If you’re importing * from pony.orm you will get this alias too.

If you want to write code which can run both on Python 2 and Python 3, you should use the buffer type for binary attributes. If your code is for Python 3 only, you can use bytes instead:

    attr1 = Required(buffer) # Python 2 and 3

    attr2 = Required(bytes) # Python 3 only

It would be cool if we could use the bytes type as an alias to buffer in Python 2, but unfortunately it is impossible, because Python 2.6 adds bytes as a synonym for the str type (Btw, there is a good read regarding this decision)

Porting from previous Pony releases to 0.6

In most cases, you don’t need to do anything in order to port your code to Pony ORM 0.6. Pony entity declarations and queries should work correctly by default both in Python 2 and Python 3.

If you declare string attribute as unicode, it will work correctly in Python 2 and Python 3, because Pony adds unicode as an alias to str in Python 3.

If you declare string attribute as str and keep only ASCII data there, it will not require any change and will work both on Python 2 and Python 3. But now that attribute will accept and return unicode values. In Python 2, if you assign an ASCII byte string to such attribute, the value will be automatically converted to unicode.

If you use str attribute type for storing byte strings, you should use buffer or bytes attribute type instead. The bytes type can be used in Python 3 only, it cannot be used correctly in Python 2. The buffer type can be used both in Python 2 and Python 3 with the same meaning. In Python 3 Pony just adds buffer as an alias to bytes.

Declaring Pony entities which work in both Python 2 and 3

It does not matter if you use type str or unicode in you string attributes – both will do the same, because Pony adds unicode as an alias to str in Python 3. But for aesthetical reasons we recommend to keep consistency and assign the same type for all of you string attributes. Starting with this release we prefer to use the str as a string type, because it looks more naturally in Python 3.

For BLOB attributes the preferred type is buffer. In Python 3 Pony adds buffer as an alias to bytes. Don’t use bytes if you want to run your code both in Python 2 and Python 3, because in Python 2 bytes is an alias to str, and has different meaning.

pymysql adapter for MySQL database added

Now Pony can use pymysql adapter in Python 2 and 3. By default, in Python 2, Pony uses MySQLdb for accessing MySQL databases. Now it falls back to pymysql if MySQLdb is not available.

Since MySQLdb doesn’t work in Python 3, here you can use only pymysql adapter.

Other changes

In this release we’ve fixed the following bugs:
Fixes #74: Wrong FK column type when using sql_type on foreign ID column
Fixes #75: MappingError for self-referenced entities in a many-to-many relationship
Fixes #80: “Entity NoneType does not belong to database” when using to_dict

As always, we appreciate your feedback. Try to use Pony in Python 3 and share your experience with us in our email list or by email at team [at] ponyorm.com

New functions and methods:

• pony.orm.serialization module with the to_dict() and to_json() functions was added. Before this release you could use the to_dict() method of an entity instance in order to get a key-value dictionary structure for a specific entity instance. Sometimes you might need to serialize not only the instance itself, but also the instance’s related objects. In this case you can use the to_dict() function from the pony.orm.serialization module.
  • to_dict() – receives an entity instance or a list of instances and returns a dictionary structure which keeps the passed object(s) and immediate related objects
  • to_json() – uses `to_dict()` and returns JSON representation of the to_dict() result
• Query.prefetch() – allows to specify which related objects or attributes should be loaded from the database along with the query result . Example:

        select(s for s in Student)\
            .prefetch(Group, Department, Student.courses)
  

• obj.flush() – allows flush a specific entity to the database
• obj.get_pk() – return the primary key value for an entity instance
• py_check parameter for attributes added. This parameter allows you to specify a function which will be used for checking the value before it is assigned to the attribute. The function should return True/False or can raise ValueError exception if the check failed. Example:

      class Student(db.Entity):
          name = Required(unicode)
          gpa = Required(float, py_check=lambda v: v >= 0 and v <= 5)
  

  New types:

  • time and timedelta – now you can use these types for attribute declaration. Also you can use timedelta and a combination of datetime + timedelta types inside queries.

  New hooks:

  • after_insert, after_update, after_delete - these hooks are called when an object was inserted, updated or deleted in the database respectively (link)
  • Added support for pymysql – pure Python MySQL client. Currently it is used as a fallback for MySQLdb interface

  Other changes and bug fixes

  • obj.order_by() method is deprecated, use Entity.select().order_by() instead
  • obj.describe() now displays composite primary keys
  • Fixes #50: PonyORM does not escape _ and % in LIKE queries
  • Fixes #51: Handling of one-to-one relations in declarative queries
  • Fixes #52: An attribute without a column should not have rbits & wbits
  • Fixes #53: Column generated at the wrong side of "one-to-one" relationship
  • Fixes #55: obj.to_dict() should do flush at first if the session cache is modified
  • Fixes #57: Error in to_dict() when to-one attribute value is None
  • Fixes #70: EntitySet allows to add and remove None
  • Check that the entity name starts with a capital letter and throw exception if it is not then raise the ERDiagramError: Entity class name should start with a capital letter exception

This release fixes the setup.py problem that was found after the previous release was uploaded to PyPI.

You can install the latest Pony ORM version using pip:

    pip install pony

Or, if you already have the previous version of Pony ORM installed, upgrade it:

    pip install --upgrade pony

This release is a step forward to Python 3 support. While the external API wasn’t changed, the internals were significantly refactored to provide forward compatibility with Python 3.

Changes since 0.5.1

• New Entity instance method
  to_dict(only=None, exclude=None, with_collections=False, with_lazy=False, related_objects=False)

  Returns a dictionary with attribute names and its values. This method can be used when you need to serialize an object to JSON or other format.

  By default this method doesn’t include collections (relationships to-many) and lazy attributes. If an attribute’s values is an entity instance then only the primary key of this object will be added to the dictionary.

  only – use this parameter if you want to get only the specified attributes. This argument can be used as a first positional argument. You can specify a list of attribute names obj.to_dict(['id', 'name']), a string separated by spaces: obj.to_dict('id name'), or a string separated by spaces with commas: obj.to_dict('id, name').

  exclude – this parameter allows to exclude specified attributes. Attribute names can be specified the same way as for the only parameter.

  related_objects – by default, all related objects represented as a primary key. If related_objects=True, then objects which have relationships with the current object will be added to the resulting dict as objects, not their primary keys. It can be useful if you want to walk the related objects and call the to_dict() method recursively.

  with_collectionsTrue, then the relationships to-many will be represented as lists. If related_objects=False (which is by default), then those lists will consist of primary keys of related instances. If related_objects=True then to-many collections will be represented as lists of objects.

  with_lazy – if True, then lazy attributes (such as BLOBs or attributes which are declared with lazy=True) will be included to the resulting dict.

  For illustrating the usage of this method we will use the eStore example which comes with Pony distribution. Let’s get a customer object with the id=1 and convert it to a dictionary:

         >>> from pony.orm.examples.estore import *
         >>> c1 = Customer[1]
         >>> c1.to_dict()
  
         {'address': u'address 1',
         'country': u'USA',
         'email': u'john@example.com',
         'id': 1,
         'name': u'John Smith',
         'password': u'***'}
  

  If we don’t want to serialize the password attribute, we can exclude it this way:

         >>> c1.to_dict(exclude='password')
  
         {'address': u'address 1',
         'country': u'USA',
         'email': u'john@example.com',
         'id': 1,
         'name': u'John Smith'}
  

  If you want to exclude more than one attribute, you can specify them as a list: exclude=['id', 'password'] or as a string: exclude='id, password' which is the same as exclude='id password'.

  Also you can specify only the attributes, which you want to serialize using the parameter only:

         >>> c1.to_dict(only=['id', 'name'])
  
         {'id': 1, 'name': u'John Smith'}
  
         >>> c1.to_dict('name email') # 'only' parameter as a positional argument
  
         {'email': u'john@example.com', 'name': u'John Smith'}
  

  By default the collections are not included to the resulting dict. If you want to include them, you can specify with_collections=True. Also you can specify the collection attribute in the only parameter:

         >>> c1.to_dict(with_collections=True)
  
        {'address': u'address 1',
         'cart_items': [1, 2],
         'country': u'USA',
         'email': u'john@example.com',
         'id': 1,
         'name': u'John Smith',
         'orders': [1, 2],
         'password': u'***'}
  

  By default all related objects (cart_items, orders) are represented as a list with their primary keys. If you want to see the related objects instances, you can specify related_objects=True:

         >>> c1.to_dict(with_collections=True, related_objects=True)
  
         {'address': u'address 1',
         'cart_items': [CartItem[1], CartItem[2]],
         'country': u'USA',
         'email': u'john@example.com',
         'id': 1,
         'name': u'John Smith',
         'orders': [Order[1], Order[2]],
         'password': u'***'}
  

Bugfixes

• Now select() function and filter() method of the Query object can accept lambdas with closures
• Some minor bugs were fixed

You can install the latest Pony ORM version using pip:

    pip install pony

Or, if you already have the previous version of Pony ORM installed, upgrade it:

    pip install --upgrade pony

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:

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.

Bugfixes:

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

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

• 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

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.

Documentation

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

    db.generate_mapping(create_tables=True)

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')
    db.generate_mapping(create_tables=True)

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

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:

db.drop_all_tables(with_all_data=False)
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.

MyEntity.drop_table(with_all_data=False)
Where MyEntity is a declared entity class.
This method removes a table which is mapped to the entity.

MyEntity.my_collection.drop_table(with_all_data=False)
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:

db.create_tables()
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.

db.disconnect()
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:

User.exists(username="Jon")

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