Pony ORM Release 0.7.3

In this release we have added the sort_by method of the Query object. At this point it is an alias of the order_by method. In the release 0.8 we are going to make a backward incompatible change and slightly modify the behavior of the order_by method. If you replace the order_by with sort_by it will continue working without any problems in this and future releases. There is no urgency to make this change – it is a preparation for the 0.8 release.

Here is the list of changes in this release:

New features

  • where() method added to the Query object
  • coalesce() function added
  • between(x, a, b) function added
  • #295: Add _table_options_ for entity class to specify engine, tablespace, etc.
  • Make debug flag thread-local
  • sql_debugging context manager added
  • sql_debug and show_values arguments were added to db_session
  • set_sql_debug function added as alias to (to be deprecated) sql_debug function
  • Allow db_session to accept ddl parameter when used as context manager
  • Add optimistic=True option to db_session
  • Skip optimistic checks for queries in db_session with serializable=True
  • fk_name option added for attributes in order to specify foreign key name
  • #280: Now it’s possible to specify timeout option in db.bind() method for SQLite, as well as pass other keyword arguments for sqlite3.connect function
  • Add support of explicit casting to int in queries using int() function
  • Added modulo division % native support in queries

Bugfixes

  • Fix bugs with composite table names
  • Fix invalid foreign key & index names for tables which names include schema name
  • For queries like select(x for x in MyObject if not x.description) add “OR x.info IS NULL” for nullable string columns
  • Add optimistic checking for delete() method
  • Show updated attributes when OptimisticCheckError is being raised
  • Fix incorrect aliases in nested queries
  • Correctly pass exception from user-defined functions in SQLite
  • More clear error messages for UnrepeatableReadError
  • Fix db_session(strict=True) which was broken in 2d3afb24
  • Fixes #170: Problem with a primary key column used as a part of another key
  • Fixes #223: incorrect result of getattr(entity, attrname) when the same lambda applies to different entities
  • Fixes #266: Add handler to "pony.orm" logger does not work
  • Fixes #278: Cascade delete error: FOREIGN KEY constraint failed, with complex entity relationships
  • Fixes #283: Lost Json update immediately after object creation
  • Fixes #284: query.order_by() orders Json numbers like strings
  • Fixes #288: Expression text parsing issue in Python 3
  • Fixes #293: translation of if-expressions in expression
  • Fixes #294: Real stack traces swallowed within IPython shell
  • Collection.count() method now checks if session is alive
  • Set obj._session_cache_ to None after exiting from db session for better garbage collection
  • Unload collections which are not fully loaded after exiting from db session for better garbage collection
  • Raise on unknown options for attributes that are part of relationship

Pony ORM Release 0.7.2

Here is the list of changes:

  • All arguments of db.bind() can be specified as keyword arguments. Previously Pony required the first positional argument which specified the database provider. Now you can pass all the database parameters using the dict: db.bind(**db_params). See Pony reference for more information
  • The optimistic attribute option is added – reference
  • Fixed #219: when a database driver raises an error, sometimes this error was masked by the ‘RollbackException: InterfaceError: connection already closed’ exception. This happened because on error, Pony tried to rollback transaction, but the connection to the database was already closed and it masked the initial error. Now Pony displays the original error which helps to understand the cause of the problem.
  • Fixed #276: Memory leak
  • Fixes the __all__ declaration. Previously IDEs, such as PyCharm, could not understand what is going to be imported by from pony.orm import *. Now it works fine.
  • Fixed #232: negate check for numeric expressions now checks if value is zero or NULL
  • Fixed #238, #133: raise TransactionIntegrityError exception instead of AssertionError if obj.collection.create(**kwargs) creates a duplicate object
  • Fixed #221: issue with unicode json path keys
  • Fixed bug when discriminator column is used as a part of a primary key
  • Handle situation when SQLite blob column contains non-binary value

To install the updated version use the following command:

pip install --upgrade pony

Pony ORM Release 0.7.1

This release brings Python 3.6 support and has some bugfixes.

Here is the list of changes:

  • Fixed #216: decompiler fixes for Python 3.6
  • Fixed #203: subtranslator should use argnames from parent translator
  • Change a way aliases in SQL query are generated in order to fix a problem when a subquery alias masks a base query alias
  • Volatile attribute bug fixed
  • Fix creation of self-referenced foreign keys – before this Pony didn’t create the foreign key for self-referenced attributes
  • Bug fixed: when required attribute is empty the loading from the database shouldn’t raise the validation error. Now Pony raises the warning DatabaseContainsIncorrectEmptyValue
  • New warning DatabaseContainsIncorrectEmptyValue added, it is raised when the required attribute is empty during loading an entity from the database
  • Throw an error with more clear explanation when a list comprehension is used inside a query instead of a generator expression: “Use generator expression (… for … in …) instead of list comprehension [… for … in …] inside query”

To install the updated version use the following command:

pip install --upgrade pony

Pony ORM Release 0.7

We are excited to announce that

Starting with this release Pony ORM is release under the Apache License, Version 2.0!

Previously we were using the AGPL and commercial licenses and found that it was driving away contributors and “open source zealots”. Pony is a mature library which is used by many companies in a production mode. We’d like to grow a community around Pony ORM and that is why we decided to release it under a permissive license, such as Apache 2.0. The Apache License allows you to freely use, modify, and distribute any Apache licensed product. You can use Pony ORM in your commercial products for free.

In this article you can find more information about the Apache license.

Besides this, here is the list of changes, improvements and bug fixes included into this release:

  • Added getattr() support in queries
  • Fixed 159: exceptions happened during flush() should not be wrapped with the CommitException
    Before this release an exception that happened in a hook , could be raised in two ways – either wrapped into the CommitException or without wrapping. It depended if the exception happened during the execution of flush() or commit() function on the db_session exit. Now the exception happened inside the hook never will be wrapped into the CommitException.
  • Fixed 190: Timedelta is not supported when using pymysql

To install the updated version use the following command:

pip install --upgrade pony

Pony ORM Release 0.6.6

The main feature of this release is native JSON data type support in databases. We have added a new chapter to the documentation, which describes how it works.

Here is the list of changes, improvements and bug fixes:

  • Starting with this release Pony drops Python 2.6 support
  • Added an experimental @db_session strict parameter
  • Merged #179: Added the compatibility with PYPY using psycopg2cffi
  • Fixed #182 – LEFT JOIN doesn’t work as expected for inherited entities when foreign key is None
  • Fixed some small bugs

To install the updated version use the following command:

pip install --upgrade pony

Pony ORM Release 0.6.5

This release contains a number of bug fixes and one enhancement.

  • Fixed #168: Incorrect caching when slicing the same query multiple times
    The bug appeared when the same query instance was sliced more than once. The result of first slicing was cached and used for all subsequent slices. The correct behavior is to generate new SQL query with different LIMIT…OFFSET parameters.
  • Fixed #169: When py_check() returns False, Pony should truncate too large values in resulting ValueError message
    Before this fix, when you tried to assign a long str or bytes value and if didn’t pass the validation, the whole string was printed in the stacktrace. That could be inconvenient if the string was pretty long. Now we show only first 100 symbols and truncate the rest.
  • Fixed #171: AssertionError when saving changes of multiple objects
    The bug appeared when there was a one-to-one relationship between two entities and both ends of this relationship were declared as Optional. When you tried to create objects and link them in the same transaction, it could cause the AssertionError: _save_() called for object ... with incorrect status 'inserted'
  • Fixed #172: Query prefetch() method should load specified lazy attributes right in the main query if possible
    Before this release, the result of prefetch() method on a lazy attribute was a separate SQL query. That’s because the goal was not in minimizing SQL queries, but in loading all necessary data before leaving the db_session scope. Now Pony does prefetch smarter – it tries to minimize the number of SQL queries.

    Let’s consider an example:

        from pony.orm import *
    
        db = Database()
    
        class Post(db.Entity):
            title = Required(str, 128)
            text = Required(LongStr)
    
        db.bind('sqlite', ':memory:')
        db.generate_mapping(create_tables=True)
        sql_debug(True)
    
        with db_session:
            posts = Post.select().prefetch(Post.text)[:]
    

    In this example the text attribute is declared as the lazy string type. Before this release Pony always loaded lazy attributes using a separate SQL query. Now, if you pass such a lazy attribute to the prefetch() method, Pony will retrieve this field in the main SQL query:

        SELECT "p"."id", "p"."title", "p"."text"
        FROM "Post" "p"
    
  • Fixed #176: Autostripped strings are not validated correctly for Required attributes
    The bug was that Pony automatically striped attribute value after validation, not before it. So, if we had a Required attribute and tried to assign some value which contained only spaces, Pony would through the ValueError: Attribute ... is required. Now Pony first strips the value and then does the validation.

To install the updated version use the following command:

pip install --upgrade pony

Pony ORM Release 0.6.4

This release brings no new features, has no backward incompatible changes, only bug fixes.
If you are using obj.flush() method in your code we recommend you to upgrade to 0.6.4 release.

  • Fixed #161: 0.6.3 + obj.flush(): after_insert, after_update & after_delete hooks do not work

To install the updated version use the following command:

pip install --upgrade pony

Pony ORM Release 0.6.3

This release doesn’t bring any new features, only bug fixes. The release brings no backward-incompatible changes, so you can upgrade from PonyORM 0.6.2 without any modifications in your code.

  • Fixed #138: Incorrect behavior of obj.flush(): assertion failed after exception
  • Fixed #157: Incorrect transaction state after obj.flush() caused “release unlocked lock” error in SQLite
  • Fixed #151: SQLite + upper() or lower() does not work as expected

#138: Incorrect behavior of obj.flush(): assertion failed after exception,
#157: Incorrect transaction state after obj.flush() caused “release unlocked lock” error in SQLite

These are long-standing bugs, but they were discovered just recently. The bugs were caused by incorrect implementation of obj.flush() method. In the same time the global flush() function worked correctly. Before this fix the method obj.flush() didn’t call before_xxx and after_xxx hooks. In some cases it led to assertion error or some other errors when using SQLite.

Now both bugs are fixed and the method obj.flush() works properly.

#151: SQLite + upper() or lower() does not work as expected

Since SQLite does not support Unicode operations, the upper() and lower() SQL functions do not work for non-ascii symbols.

Starting with this release Pony registers two additional unicode-aware functions in SQLite: py_upper() and py_lower(), and uses these functions instead of the standard upper and lower functions:

>>> select(p.id for p in Person if p.name.upper() == 'John')[:]

SQLite query:

SELECT "p"."id"
FROM "Person" "p"
WHERE py_upper("p"."name") = 'John'

For other databases Pony still uses standard upper() and lower() functions.

Pony ORM Release 0.6.2

Python 3.5 support

Pony now supports Python 3.5

Better raw SQL support

Before this release Pony provided an ability to write raw SQL queries using the Entity.select_by_sql() method. But in this case you had to write the whole SQL query by itself.

Starting with this release Pony allows embedding raw SQL fragments into a lambda or generator query using the raw_sql() function.

Here is an example of using raw_sql() function:

s = 'J%'
select(p for p in Person if p.age > 20 and raw_sql('"p"."name" LIKE $s'))

The string '"p"."name" LIKE <param>' will be embedded into generated SQL query. The value of the parameter will be taken from the s variable. p is an alias of SQL table used in this query, not Python generator variable. Pony uses the variable from the generator as an alias for SQL table.

You also can embed more complex Python expressions into raw SQL fragment:

import datetime
Task.select(lambda t: raw_sql("t.due_date < date($datetime.date.today(), '+1 day')"))

Here datetime.date.today() is a Python expression which will be evaluated and replaced with a single parameter.

You can find more examples of using raw_sql() function in Pony docs.

Using @db_session with generator functions

Previously the @db_session decorator was used for decorating functions that work with the database. But it didn't work correctly when was applied to generators (functions that return value using the yield expression). Starting with this release you can use the @db_session decorator for generators too.

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

This fixes the issue #126.

Getting SQL statement as a string

Now you can use Query.get_sql() method in order to get SQL statement that will be sent to the database:

sql = select(c for c in Category if c.name.startswith('a')).get_sql()
print sql

SELECT "c"."id", "c"."name"
FROM "category" "c"
WHERE "c"."name" LIKE 'a%%'

Deleting objects

Before Pony release 0.6.2 you could delete objects only by calling the delete() method on an entity instance. Now you can use the delete() query:

delete(p for p in Product if p.picture is None)

Another option is calling the delete() method on a query:

select(p for p in Product if p.picture is None).delete()

The Query.delete() method has the bulk parameter, which is False by default. When bulk=False Pony loads each instance into memory and calls the delete() method on each instance (calling before_delete and after_delete hooks if they were defined). If bulk=True Pony doesn't load instances, it just generates the SQL DELETE statement which deletes objects in the database.

Ability to override entity constructor and add methods to an entity

Now you can override the __init__() method of an entity, and also monkeypatch your own methods in an entity.

Backward incompatibilities

Normalizing table names for symmetric relationships

Pony can automatically generate database table names when it creates tables for entities and many-to-many relationships. Depending on the database, it normalizes the table name using either upper or lower case letters. In previous releases this normalization was not applied to symmetric relationships (where both ends of a relationships are specified using the same attribute). Here is an example of a symmetric relationship:

class Person(db.Entity):
    friends = Set("Person", reverse="friends")

Now, when this bug is fixed, Pony applies the same normalization rules to all table names. So, you might need to change the database table name or use the table option of a symmetric attribute for specifying your current table name:

class Person(db.Entity):
    friends = Set("Person", reverse="friends", table="current_table_name")

Autostrip

Now Pony automatically removes leading and trailing whitespace characters in a string attribute. You can control it by using the autostrip option. By default autostrip=True. The behavior is similar to Python string.strip() function. If you want to keep leading and trailing characters, you have to set autostrip parameter to False:

class Person(db.Entity):
    name = Required(str, autostrip=False)

Documentation

We have moved Pony ORM documentation to a separate repo at https://github.com/ponyorm/pony-doc. Also we have changed the license of documentation to Apache 2.0. This way it will be easier to receive pull request for docs and it makes collaboration easier. Please create new documentation related issues here.
The compiled version of docs still can be found at https://docs.ponyorm.com

Other changes and bug fixes

  • Fixed #87: Pony fails with pymysql installed as MySQLdb
  • Fixed #116: Add support to select by UUID
  • Fixed #118: Pony should reconnect if previous connection was created before process was forked
  • Fixed #121: Unable to update value of unique attribute
  • Fixed #122: AssertionError when changing part of a composite key
  • Fixed #127: a workaround for incorrect pysqlite locking behavior
  • Fixed #136: Cascade delete does not work correctly for one-to-one relationships
  • Fixed #141, #143: remove restriction on adding new methods to entities
  • Fixed #142: Entity.select_random() AssertionError
  • Fixed #147: Add 'atom_expr' symbol handling for Python 3.5 grammar

Pony ORM Release 0.6.1

With Pony release 0.6.1 we take into account our users feedback and introduce some new cool features.

Using queries for collections (relationship attributes)

Now you can apply Entity.select, Query.order_by, Query.page, Query.limit, Query.random methods to the relationships to-many.

Below you can find several examples of using these methods. We’ll use the University schema for showing these queries, here are python entity definitions and Entity-Relationship diagram

The example below selects all students with the gpa greater than 3 within the group 101:

    g = Group[101]
    g.students.select(lambda student: student.gpa > 3)[:]

This query can be used for displaying the second page of group 101 student’s list ordered by the name attribute:

    g.students.order_by(Student.name).page(2, pagesize=3)

The same query can be also written in the following way:

    g.students.order_by(lambda s: s.name).limit(3, offset=3)

The following query returns two random students from the group 101:

    g.students.random(2)

And one more example. This query returns the first page of courses which were taken by Student[1] in the second semester, ordered by the course name:

    s = Student[1]
    s.courses.select(lambda c: c.semester == 2).order_by(Course.name).page(1)

Now you can pass globals and locals to queries

It allows you to create a context for executing your queries. Example:

    Student.select(lambda s: s.gpa > x, {'x' : 3})
    # or
    select("s for s in Student if s.gpa > x", {'x' : 3})

Even if the variable x has another value within the outer scope, the value from the dictionary that we pass to the query overrides it.

It behaves the same way as for the standard Python eval expression.

Here is the list of methods and functions that can receive globals and locals starting with this release:
select, left_join, get, exists, Entity.select, Entity.exists, Entity.get, Entity.get_for_update, Query.order_by, Query.filter

Improved inheritance support in queries

Now you can iterate over the base entity and use attributes of subclasses in query conditions:

    select(x for x in BaseClass if x.subclass_attr == y)

As an example, let’s say you want to extract all employee and manager objects which have their salary or bonus greater then some amount x. We have classes Employee and Manager inherited from the class Person:

    class Person(db.Entity):
        name = Required(str)

    class Employee(Person):
        salary = Required(Decimal)
        ...

    class Manager(Employee):
        bonus = Required(Decimal)
        ...

Here is the query:

    select(p for p in Person if p.salary > x or p.bonus > x)

The entity Person doesn’t have the attributes salary and bonus, but the fact that the descendant classes have it, allows us to use this attribute in the query conditions section.

db.insert() can receive both table name or entity name

Object of the Database class can be used for direct access to the database. Now you can specify either the table name or the entity name when usingh the insert() method:

    db.insert(SomeEntity, column1=x, column2=y)
    # is equal to
    db.insert(SomeEntity._table_, column1=x, column2=y)

When you specify an entity, Pony will use the table which is mapped to this entity.

Discriminator attribute can be part of the composite index

    class Person(db.Entity):
        cls_id = Discriminator(int)
        _discriminator_ = 1
        name = Required(str)
        ...
        composite_key(cls_id, name)

Now it is possible to specify the attribute name instead of the attribute itself in composite index

It can be used if you want to use an automatically generated attribute (e.g. classtype) as a part of a composite key.
Let’s use the same example again. Now, in composite_key instead of the name attribute, you can use the string representation of it:

    class Person(db.Entity):
        cls_id = Discriminator(int)
        _discriminator_ = 1
        name = Required(str)
        ...
        composite_key(cls_id, 'name')

Query statistics: global_stats_lock is deprecated, just use global_stats property without any locking

Pony gathers statistics on executed queries. Previously you had to acqire lock on global_stats_lock attribute before accessing the global_stats property. Now Pony does it itself and you don’t need to bother about it yourself.

New load() method for entity instances

The new load() method loads all lazy and non-lazy attributes, but not collection attributes, which were not retrieved from the database yet. If an attribute was already loaded, it won’t be loaded again. You can specify the list of the attributes which need to be loaded, or it’s names. In this case Pony will load only them:

    p = Person[123]
    p.load(Person.biography, Person.some_other_field)

You can use the string attribute names too:

    p = Person[123]
    p.load('biography', 'some_other_field')

Usually, when Pony loads lazy attributes only when you access them. But if your object has a lot of lazy attributes and you know that you’re going to need all of them, it is more optimal to load all of them at once using the load() method.

New load() method for collections

This method loads all related objects from the database. It is useful when you intend to work with a collection and want to load all objects from the database at once. Example:

    customer = Customer[123]
    customer.orders.load()

Enhanced error message when descendant classes declare attributes with the same name

Now if you try to declare the following entities:

    class Person(db.Entity):
        name = Required(str)

    class Employee(Person):
        dob = Required(date)
        ...

    class Manager(Person):
        dob = Required(date)
        ...

    db.generate_mapping(create_tables=True) # raises exception

When Pony will try to generate mapping for the entities above, it will raise the ERDiagramError exception with the following text:

    Attribute Manager.dob conflicts with attribute Employee.dob because both entities inherit from Person. To fix this, move attribute definition to base class

To fix this you need to declare the dob attribute in the entity Person:

    class Person(db.Entity):
        name = Required(str)
        dob = Required(date)

    class Employee(Person):
       ...

    class Manager(Person):
        ...

Other changes and bug fixes

  • Fixed #98: Composite index can include attributes of base entity
  • Fixed #106: incorrect loading of object which consists of primary key only
  • Fixed pony.converting.check_email()
  • Prefetching bug fixed: if collection is already fully loaded it shouldn’t be loaded again
  • Deprecated Entity.order_by(..) method was removed. Use Entity.select().order_by(…) instead
  • Various performance enhancements
  • Multiple bugs were fixed

As always, we appreciate your feedback which you can send us to our email list or by email at team [at] ponyorm.com