Pony ORM Release 0.7.17

Pony ORM 0.7.17 now supports Python 3.11 and discontinues support for Python versions earlier than 3.8.

Support for Python 3.11

Python 3.11 introduced changes to its bytecode to speed up execution (see Python 3.11 changes). As a consequence, updates were required in the Pony bytecode decompiler.

Dropping Support for Python 3.6 and 3.7

Starting with Pony ORM Release 0.7.17, we are discontinuing support for Python 3.6 and 3.7. Pony ORM now supports Python versions 3.8 through 3.11.

Bug Fixes

  • Fix #636: nullable IntArray and StrArray issues.
  • Corrected quoting of table aliases in MySQL DELETE queries.
  • Fixed handling of default argument values in hybrid methods and functions.

Pony ORM Release 0.7.16

Hi all! We released Pony ORM 0.7.16. This release includes:

  • A long-awaited support of Python 3.10.
  • A possibility to create shared-in memory SQLite databases that can be accessed from multiple threads

Support of Python 3.10

Previous versions of PonyORM use Python parser module from Python 2.x to parse Python AST. The support of this module was dropped in Python 3.10. Because of this, Pony ORM switches to new ast module.

With this change, the support of Python 2.7 and old versions of Python 3 was dropped, and the supported versions now are Python 3.6-Python 3.10

Shared in-memory SQLite databases

Previously in-memory SQLite databases are created in Pony ORM by specifying ":memory:" string as a database filename. The database created this way cannot be used in multiple threads. Now it is possible to use ":sharedmemory:" string to create a database that can be accessed from multiple threads.

Other changes

  • Do not perform optimistic checks when deleting an object (it is OK if it was already deleted by concurrent transaction)
  • Validation of int fields should take into account field size and check that the value is fit into the range
  • More tests for hybrid methods added
  • Fix incorrect assertion check `assert t is translator.`
  • Fix aggregated query sum(x.field for x in previous_query)
  • #594: Use a clearly synthetic filename when compiling dynamic code to not confuse coverage.py
  • Use DecompileError exception instead of AssertionError when a function cannot be decompiled

Pony ORM Release 0.7.12

Hi all,

Recently we have worked with the CockroachDB team and today we are excited to release Pony ORM 0.7.12 with the CockroachDB support. CockroachDB is a “cloud-native SQL database for building global, scalable cloud services that survive disasters”.

Another exciting news is that we have added a full blown continuous integration support. Now you can see the result of the tests at the Pony ORM page:

https://github.com/ponyorm/pony

Bugfixes

Also, there is a list of bugfixes:

  • Fix translation of getting array items with negative indexes
  • Fix string getitem translation for slices and negative indexes
  • PostgreSQL DISTINCT bug fixed for queries with ORDER BY clause
  • Fix date difference syntax in PostgreSQL
  • Fix casting json to dobule in PostgreSQL
  • Fix count by several columns in PostgreSQL
  • Fix PostgreSQL MIN and MAX expressions on boolean columns
  • Fix determination of interactive mode in PyCharm
  • Fix column definition when sql_default is specified: DEFAULT should be before NOT NULL
  • Relax checks on updating in-memory cache indexes (don’t throw CacheIndexError on valid cases)
  • Fix deduplication logic for attribute values

Join our Telegram group, in case you haven’t already.

Pony ORM Release 0.7.10

This release includes the following bugfixes:

  • Python3.7 and PyPy decompiling fixes
  • Fix reading NULL from Optional nullable array column
  • Fix handling of empty arrays in queries
  • #415: error message typo
  • #432: PonyFlask – request object can trigger teardown_request without real request
  • Fix GROUP CONCAT separator for MySQL

If you use Python 3.7 or PyPy we strongly recommend to upgrade to this release.

Join our Telegram group, in case you haven’t already.

Pony ORM Migrations

When we have started developing Pony ORM, we set a goal of creating the most pythonic, easy to use and powerful object-relational mapper for Python. Now, when we are getting feedback from our users, we are happy to see that we have managed to do this. We are grateful for your feedback, you are awesome! It helps us making Pony even better.

The most frequent request at the moment is database migrations. Pony ORM provides intuitive object-oriented interface to a database. We want our migrations to share the same paradigm – to be easy to use and powerful.

Pony ORM describes database model in terms of entities and its relationships. For human beings working with such a model is easier rather than with tables in a relational database. For example, it allows easily describe many-to-many relationships. In a relational database one need to add an intermediate table for this, which adds complexity. This way, Pony allows working with a database at a higher level of abstraction.

Previous attempts

By this moment, we have made a couple of attempts to implement the migrations. The result of those attempts were not officially released, although they are available at GitHub. The reason was that we’ve found some flaws in those approaches and for us it is important to provide an easy to use and powerful migrations interface. Based on that experience, we figured a better way and hope this approach is free from the drawbacks of previous versions.

Migrations workflow

The workflow of Pony ORM migrations looks as following:

The migration file describes the changes to be made in the database. In order to generate such a file, Pony compares the current models taken from the source code with the models generated by applying all previous migrations. The resulting file contains the descriptions both at the entity level and the database tables level.

Entity level

The modifications at the entity level are described using the list of operations such as AddEntity, RemoveEntity, AddAttrbute, AddRelation, RenameEntity, etc. After applying these operations, Pony gets a new state of entities.

Database level

The modifications at the database level are described using another list with the operations like AddTable, AddColumn, AddForeignKey, AddIndex, etc. These operations provide full control on database changes. At this level one can add the ExecSQL command, which is used for data migrations.

In most cases the database operations will be generated automatically, but when necessary, the developer can add any arbitrary operation to this list. It might be useful for complex updates or in early releases of the migration feature. Later we will be adding more and more well known use cases so that Pony can do those migrations automatically.

Advantages

This approach perfectly matches with a typical software development workflow when adding new features happens in separate branches and then those branches are being merged into the main branch. Operations which describe the changes to be made makes the merging process very simple.

Once we have the new migrations, developers will be able to easily switch to the new version if currently they use an old approach.

We believe the new Pony ORM migrations will help developing applications even faster.

Pony ORM Release 0.7.7

In this release we introduce new features and improvements an also fix a number of bugs.

Major features

  • Array type support for PostgreSQL and SQLite
  • isinstance() support in queries
  • Support of queries based on collections: select(x for x in y.items)

Other features

  • Support of Entity.select(**kwargs)
  • Support of SKIP LOCKED option in ‘SELECT … FOR UPDATE’
  • New function make_proxy(obj) to make cros-db_session proxy objects
  • Specify ON DELETE CASCADE/SET NULL in foreign keys
  • Support of LIMIT in SELECT FROM (SELECT …) type of queries
  • Support for negative JSON array indexes in SQLite

Improvements

  • Improved query prefetching: use fewer number of SQL queries
  • Memory optimization: deduplication of values recieved from the database in the same session
  • increase DBAPIProvider.max_params_count value

Bugfixes

  • #405: breaking change with cx_Oracle 7.0: DML RETURNING now returns a list
  • #380: db_session should work with async functions
  • #385: test fails with python3.6
  • #386: release unlocked lock error in SQLite
  • #390: TypeError: writable buffers are not hashable
  • #398: add auto coversion of numpy numeric types
  • #404: GAE local run detection
  • Fix Flask compatibility: add support of LocalProxy object
  • db_session(sql_debug=True) should log SQL commands also during db_session.__exit__()
  • Fix duplicated table join in FROM clause
  • Fix accessing global variables from hybrid methods and properties
  • Fix m2m collection loading bug
  • Fix composite index bug: stackoverflow.com/questions/53147694
  • Fix MyEntity[obj.get_pk()] if pk is composite
  • MySQL group_concat_max_len option set to max of 32bit platforms to avoid truncation
  • Show all attribute options in show(Entity) call
  • For nested db_session retry option should be ignored
  • Fix py_json_unwrap
  • Other minor fixes

Join our Telegram group, in case you haven’t already.

Pony ORM Release 0.7.6

In release 0.7.4 we introduced support of hybrid methods and properties. This change required massive refactoring which lead to some bugs. Current release fixes all new bugs revealed after 0.7.4.

Also in this release some nice features were added. The first is the support of Literal String Interpolation a.k.a f-strings, introduced in Python 3.6. It looks nicely than using of %-interpolation or format method. f-strings use two special bytecode instructions which Pony now able to decompile and translate correctly:

select(f'The age of {s.name} is {s.age}' for s in Student)

The second small feature is a possibility to skip initial rows in query by specifying offset without limit. In order to do this, you can pass None as a limit value, or just omit limit value at all.

Also now it is possible to explicitly case JSON expressions to int, str or float. It may be necessary if you want to mix JSON expressions with expressions of other types in functions like coalesce()

Finally, we introduced @db.on_connect decorator which can be used to set up some connection properties. For example, since release 0.7.4 like in SQLite works as case-sensitive for better compatibility with other database management systems. If for some reason you want to restore the old like behavior, you can set corresponding pragma on SQLite connection:

@db.on_connect(provider='sqlite')
def set_case_insensitive_like(db, connection):
    cursor = connection.cursor()
    cursor.execute('PRAGMA case_sensitive_like = OFF')

Below is the full changelog since 0.7.5:

Features

  • f-strings support in queries select(f'{s.name} - {s.age}' for s in Student)
  • #344: It is now possible to specify offset without limit: query.limit(offset=10)
  • #371: Support of explicit casting of JSON expressions to str, int or float
  • @db.on_connect decorator added

Bugfixes

  • Fix bulk delete bug introduced in 0.7.4
  • #370 Fix memory leak introduced in 0.7.4
  • Now exists() in query does not throw away condition in generator expression: exists(s.gpa > 3 for s in Student)
  • #373: 0.7.4/0.7.5 breaks queries using the in operator to test membership of another query result
  • #374: auto=True can be used with all PrimaryKey types, not only int
  • #369: Make QueryResult looks like a list object again: add concatenation with lists, .shuffle() and .to_list() methods
  • #355: Fix binary primary keys PrimaryKey(buffer) in Python2
  • Interactive mode support for PyCharm console
  • Fix wrong table aliases in complex queries
  • Fix query optimization code for complex queries
  • Fix a bug with hybrid properties that use external functions

Pony ORM Releases 0.7.4 & 0.7.5

In these releases we add support of Python 3.7 and PyPy. Also we changed PonyORM internals significantly in order to add two long-demanded features. The first is hybrid methods and properties. Now it is possible to define one-line method or property in entity class and use it inside declarative queries. This way it is possible to re-use complex expressions between different queries:

class Person(db.Entity):
    first_name = Required(str)
    last_name = Required(str)
    country = Required(str)
    age = Required(int)

    @property
    def full_name(self):
        return self.first_name + self.last_name

    @property
    def is_adult(self):
        return self.age > 18

...

query = select(p for p in Person if p.is_adult)

The second feature is the possibility to base query on a previous query. This way it is possible to construct complex queries gradually. In previous versions of Pony it was possible to add conditions by using query.filter method:

query2 = query.filter(lambda person: person.country == 'USA')

but query.filter function cannot change the result type of query. Now you have full flexibility for making your query:

query3 = select(x.full_name for x in query2 if x.last_name.startswith('A'))

Implementing this two features was not easy, because SQL and Python have different namespace rules, and mapping Python name to SQL aliases is a non-trivial task for complex queries.

Also we added a pony.flask subpackage wich provide integration with Flask, so extensions like Flask-Login can work with Pony.

From now on we will concentrate on migration tool and hopefully release it soon.

Here is the detailed changelog:

Pony ORM Release 0.7.4 (2018-07-23)

Major features

  • Hybrid methods and properties added
  • Allow to base queries on another queries: select(x.a for x in prev_query if x.b)
  • Added support of Python 3.7
  • Added support of PyPy
  • group_concat() aggregate function added
  • pony.flask subpackage added for integration with Flask

Other features

  • distinct option added to aggregate functions
  • Support of explicit casting to float and bool in queries

Improvements

  • Apply @cut_traceback decorator only when pony.MODE is ‘INTERACTIVE’

Bugfixes

  • In SQLite3 LIKE is case sensitive now
  • #249: Fix incorrect mixin used for Timedelta
  • #251: correct dealing with qualified table names
  • #301: Fix aggregation over JSON Column
  • #306: Support of frozenset constants added
  • #308: Fixed an error when assigning JSON attribute value to the same attribute: obj.json_attr = obj.json_attr
  • #313: Fix missed retry on exception raised during db_session.__exit__
  • #314: Fix AttributeError: ‘NoneType’ object has no attribute ‘seeds’
  • #315: Fix attribute lifting for JSON attributes
  • #321: Fix KeyError on obj.delete()
  • #325: duplicating percentage sign in raw SQL queries without parameters
  • #331: Overriding __len__in entity fails
  • #336: entity declaration serialization
  • #357: reconnect after PostgreSQL server closed the connection unexpectedly
  • Fix Python implementation of between() function and rename arguments: between(a, x, y) -> between(x, a, b)
  • Fix retry handling: in PostgreSQL and Oracle an error can be raised during commit
  • Fix optimistic update checks for composite foreign keys
  • Don’t raise OptimisticCheckError if db_session is not optimistic
  • Handling incorrect datetime values in MySQL
  • Improved ImportError exception messages when MySQLdb, pymysql, psycopg2 or psycopg2cffi driver was not found
  • desc() function fixed to allow reverse its effect by calling desc(desc(x))
  • __contains__ method should check if objects belong to the same db_session
  • Fix pony.MODE detection; mod_wsgi detection according to official doc
  • A lot of inner fixes

Pony ORM Release 0.7.5 (2018-07-24)

Bugfixes

  • query.where and query.filter method bug introduced in 0.7.4 was fixed

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