Pony Object Relational Mapper



Downloads Downloads Downloads

Pony Object-Relational Mapper

Pony is an advanced object-relational mapper. The most interesting feature of Pony is its ability to write queries to the database using Python generator expressions and lambdas. Pony analyzes the abstract syntax tree of the expression and translates it into a SQL query.

Here is an example query in Pony:

select(p for p in Product if p.name.startswith('A') and p.cost <= 1000)

Pony translates queries to SQL using a specific database dialect. Currently Pony works with SQLite, MySQL, PostgreSQL and Oracle databases.

By providing a Pythonic API, Pony facilitates fast app development. Pony is an easy-to-learn and easy-to-use library. It makes your work more productive and helps to save resources. Pony achieves this ease of use through the following:

  • Compact entity definitions
  • The concise query language
  • Ability to work with Pony interactively in a Python interpreter
  • Comprehensive error messages, showing the exact part where an error occurred in the query
  • Displaying of the generated SQL in a readable format with indentation

All this helps the developer to focus on implementing the business logic of an application, instead of struggling with a mapper trying to understand how to get the data from the database.

See the example here

Support Pony ORM Development

Pony ORM is Apache 2.0 licensed open source project. If you would like to support Pony ORM development, please consider:

Become a backer or sponsor

Online tool for database design

Pony ORM also has the Entity-Relationship Diagram Editor which is a great tool for prototyping. You can create your database diagram online at https://editor.ponyorm.com, generate the database schema based on the diagram and start working with the database using declarative queries in seconds.


Documenation is available at https://docs.ponyorm.org The documentation source is avaliable at https://github.com/ponyorm/pony-doc. Please create new documentation related issues here or make a pull request with your improvements.


Pony ORM is released under the Apache 2.0 license.

PonyORM community

Please post your questions on Stack Overflow. Meet the PonyORM team, chat with the community members, and get your questions answered on our community Telegram group. Join our newsletter at ponyorm.org. Reach us on Twitter.

Copyright (c) 2013-2019 Pony ORM. All rights reserved. info (at) ponyorm.org

  • SQL Views

    SQL Views

    I have a db of patients and would like to use an sql view for patient search, is it possible to extend Pony to do this in my code and how(that's if pony can't do this already).

    opened by noxecane 17
  • Python 3 support!

    Python 3 support!

    opened by scipetr 17
  • [BUG] InterfaceError: (0, '')

    [BUG] InterfaceError: (0, '')

    Traceback (most recent call last):
      File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1982, in wsgi_app
        response = self.full_dispatch_request()
      File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1614, in full_dispatch_request
        rv = self.handle_user_exception(e)
      File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1517, in handle_user_exception
        reraise(exc_type, exc_value, tb)
      File "/usr/local/lib/python3.6/dist-packages/flask/_compat.py", line 33, in reraise
        raise value
      File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1612, in full_dispatch_request
        rv = self.dispatch_request()
      File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1598, in dispatch_request
        return self.view_functions[rule.endpoint](**req.view_args)
      File "<string>", line 2, in application_list
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 423, in new_func
        finally: db_session.__exit__(exc_type, exc, tb)
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 397, in __exit__
        else: rollback()
      File "<string>", line 2, in rollback
      File "/usr/local/lib/python3.6/dist-packages/pony/utils/utils.py", line 58, in cut_traceback
        return func(*args, **kwargs)
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 326, in rollback
        transact_reraise(RollbackException, exceptions)
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 283, in transact_reraise
        reraise(exc_class, new_exc, tb)
      File "/usr/local/lib/python3.6/dist-packages/pony/utils/utils.py", line 85, in reraise
        try: raise exc.with_traceback(tb)
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 323, in rollback
        try: cache.rollback()
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 1559, in rollback
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/core.py", line 1573, in close
        try: provider.rollback(connection, cache)
      File "<string>", line 2, in rollback
      File "/usr/local/lib/python3.6/dist-packages/pony/orm/dbapiprovider.py", line 59, in wrap_dbapi_exceptions
        raise InterfaceError(e)
    pony.orm.core.RollbackException: InterfaceError: (0, '')

    Looks like it happen when you do nothing to db for some time and then do something. For example, when script waits for http request and when it get it, pony throws the exception. If you do another request after that, everything will be ok.

    I had this issue with aiohttp (but i thought that it's related to threadpool that i used) and flask.

    opened by xunto 17
  • Add support for calculated fields

    Add support for calculated fields

    As per this SO question, I'd like to add calculated/derived fields to my entities and then use those fields in calculations, aggregations, etc. I wasn't able to find anything in the docs or examples that indicate that this is supported. Could this kind of thing be supported in the future?

    opened by tonycpsu 16
  • How to filter query inside group statement

    How to filter query inside group statement

    Title probably doesn't describe this, wasn't sure how to explain it.

    Basically, I want to filter my DB columns via url parameters. With a normal select statement, I have this working fine. However I now need to run the filters on a select statement within another select statement and then call .count() on it, the following example will probably explain more.

    def model_with_filters(model, aid, filters):
        query = select(x for x in model)
        for column_name, value in { k: v for k, v in filters.iteritems() if k in model._columns_ and v != ""}.iteritems():
            query = query.filter("lambda x: x.%s == '%s'" % (column_name, value))
        return query.filter("lambda x: x.aff_id== aid")
    # This is what I want to do
    select((a, model_with_filters(SendPin, a.aff_id, request.args).count) for a in Affiliate)
    TypeError: Function 'model_with_filters' cannot be used inside query

    However I am not allowed to use a function within the select query. What's the recommended way of being able to apply filters to the count statement if I can't call a function in there?

    Also, using the lambdas like above seems really funky, but it's the only way I could get it to work.

    opened by JakeAustwick 15
  • Если вызвать `.flush()` то теряется транзакционная целостность.

    Если вызвать `.flush()` то теряется транзакционная целостность.

    from pony.orm import Database, db_session
    db = Database()
    class TestTable(db.Entity):
    def init_database():
        db.bind("sqlite", ":memory:")
    def clear_database():
    if __name__ == "__main__":
            with db_session:
                entity = TestTable()
                raise ValueError()
        except ValueError as exc:
        with db_session:
            # в транзакции ошибка, а в таблице запись уже есть!
            if len(TestTable.select()) != 0:
                print("Таблица не пуста")
            with db_session:
                entity = TestTable()
                raise ValueError()
        except ValueError as exc:
        with db_session:
            # в транзакции ошибка, а в таблице записи нет, все ок
            if len(TestTable.select()) == 0:
                print("Все в порядке!")

    Что как бы очень критичный баг.

    Ну и менее важная проблема - как мне получить id только что созданного элемента?

    opened by vladlutkov 15
  • "db_session required" exception when working with aync def coroutines

    Hey there. I'm having an issue that reminds #126 which should've been resolved. I'm also using tornado. I've managed to reproduce it with this minimal code:


    I wrap a coroutine (async def) with db_session but an exception is thrown when doing a database action inside the coroutine. Regular wrapped functions or with blocks are fine.

    This is Python 3.6.5 and pony 0.7.6; getting "pony.orm.core.TransactionError: db_session is required when working with the database".

    opened by amireldor 14
  • Efficient lookup in many-many relationship

    Efficient lookup in many-many relationship

    I have the following schema, where the relationship between Executable and Symbol is many-to-many.

        class File(db.Entity):
            loc = Required(str, unique=True)
            tim = Optional(datetime)
        class Executable(File):
            sym = Set("Symbol")
        class Symbol(db.Entity):
            sig = Required(str, 5000, encoding='utf-8')
            exe = Set(Executable)

    A foreign-key table called Executable_Symbol would be created by Pony to store this relationship, but there seems to be no way to check whether a particular relationship exists via the ORM unless I drop down to raw SQL, i.e.

    eid,sid = exe.id,sym.id
    db.select('* from Executable_Symbol where executable=$eid and symbol=$sid ')

    I figured the best way of doing this is that if I have a Symbol called sym, and an Executable called exe, I can use the expression:

    exe in sym.exe

    But this seems to be very slow. In comparison, accessing the Executable_Symbol table using raw SQL is much faster, but dropping to raw SQL is not very desirable. My application would check this a few hundred thousand times, so every bit of efficiency would be useful.

    Is there a better way to do this?


    opened by chnrxn 13
  • Support Python 3.9

    Support Python 3.9

    Expand the list of supported versions of Python to include newly released Python 3.9.

    Resolves #514. Resolves #530. Resolves #562.

    opened by tbabej 13
  • How to disconnect from db and drop all tables?

    How to disconnect from db and drop all tables?

    Hi, currently I am covering flask app with unit tests and I need to initialize database before each test starts and drop database after test is finished. But seems like there is no "easy" way to do it with PonyORM.

    What I want is something like how it works with sqlalchemy http://pythonhosted.org/Flask-Testing/:

    from flask.ext.testing import TestCase
    from myapp import create_app, db
    class MyTest(TestCase):
        SQLALCHEMY_DATABASE_URI = "sqlite://"
        TESTING = True
        def create_app(self):
            # pass in test configuration
            return create_app(self)
        def setUp(self):
        def tearDown(self):

    Please advice: how can I disconnect from db and delete it? Is it possible?

    opened by t4ec 13
  • Use order_by on one-to-one or many-to-one relationships

    Use order_by on one-to-one or many-to-one relationships

    class One(Entity):
      id = PrimaryKey(int, auto=True)
      other = Required(lambda: Other)
      def other_id(self):
        return self.other.id
    class Other(Entity):
      id = PrimaryKey(int, auto=True)
      one = Optional(lambda: One)

    with the entities defined, I expected something like one of the following would work:

    One.select().order_by(One.id, lambda one: one.other.id)
    One.select().order_by(One.id, One.other.id)
    One.select().order_by(One.id, One.other_id) # using hybrid property

    sadly I always seem to run into the error: TypeError: order_by() method receive an argument of invalid type: <property object at 0x7f9972effa90>

    What I want is to get the entities sorted by an attribute of a specific related entity. Using the primary key is only for a short example, it would be great if it would work on any attributes managed by Pony.

    opened by Zocker1999NET 0
  • how to generate geometry attribute?

    how to generate geometry attribute?

    I have a database that has a geometry type field. how do I generate this attribute?

    table postgres:


    class Segmentos_ruas_log(db.Entity):
        id_segmentos_ruas = PrimaryKey(int, auto=True)
        nome = Required(str)
        nome_rua = Required(str)
        geom = Required(**??????**)
    opened by VagnerBelfort 2
  • IntArray size=64 support

    IntArray size=64 support

    I couldn't find in the documentation what is the size of the integers when using IntArray data type. How could I tell pony to use size=64 (bigint) ?

    Using postgresql.

    opened by polmonso 2
  • pytest-cov report

    pytest-cov report

    Hallo, Thanks for this great orm in python. In my opinion it is way better then sqlalchemy or django! I have an issue regarding pytest-cov https://github.com/pytest-dev/pytest-cov and pony. If I try to cover the line of the select or get statements I get a weird message: image

    This also harms the coverage, because pytest thinks they are not covered.

    Thanks for your help!

    opened by tweigel-dev 0
  • Update ignore

    Update ignore

    opened by 180909 0
  • Update year to 2021

    Update year to 2021

    Update Copyright year to 2021.

    opened by 180909 0
  • How to create custom fields?

    How to create custom fields?

    Hi! Thanks for the great work! I would like to create a custom JSON field that is stored compressed (using gzip and msgpack) in the database, and the compression / decompression phase happens under the hood.

    I've read pony documentation but I've not seen documented how one would go do to this. Would you be so kind to provide me with some clues?

    opened by trenta3 1
  • AttributeError: _session_cache_

    AttributeError: _session_cache_

    I have a problem with how my relationships work (instant rimshot). Maybe I'm not supposed to init the classes in quite this way? I'm confused by the error.

    from pony.orm import *
    db = Database()
    class Menu(db.Entity):
        name = Required(str)
        price = Required(float)
        id = PrimaryKey(str)
        description = Required(str)
        category = Required("MenuCategory")
        def __init__(self, category, pr, *args, **kwargs):
            if type(pr) is str:
                raise Exception('got a string but expected a dict :( -> ' + pr)
            name = pr['nm']
            price = float(pr['pc'])
            id = pr['id']
            description = pr['ds']
            super().__init__(category=category, name=name, price=price, id=id, description=description, *args, **kwargs)
    class MenuCategory(db.Entity):
        image_url = Required(str)
        id = PrimaryKey(str)
        name = Required(str)
        menus = Set(Menu)
        restaurant = Required("Restaurant")
        def __init__(self, restaurant, ct, *args, **kwargs):
            menus = []
            image_url = ct['cti']
            id = ct['id']
            name = ct['nm']
            if '0' not in ct['ps']['pr']:
                print('no 0')
                menus += [Menu(self, d) for d in ct['ps']['pr']]
                print('has 0')
                menus += [Menu(self, d) for d in ct['ps']['pr'].values()]
            super().__init__(restaurant=restaurant, image_url=image_url, id=id, name=name, menus=menus, *args, **kwargs)
    class Restaurant(db.Entity):
        name = Required(str)
        address = Required(str)
        lat = Required(float)
        lon = Required(float)
        menu = Set(MenuCategory)
        def __init__(self, json: dict, *args, **kwargs):
            rd = json['rd']
            ad = rd['ad']
            address = f"{ad['st']} {ad['pc']}, {ad['tn']}"
            menus = [MenuCategory(self, m) for m in rd['mc']['cs']['ct']]
                address=address, lat=ad['lt'],
            *args, **kwargs)
    db.bind(provider='sqlite', filename='database.sqlite', create_db=True)

    Using it like this from a different file:

    with db_session:
        res = Restaurant(r)

    r = json dictionary http response

    The error I get:

    $ python fetch.py
    no 0
    Traceback (most recent call last):
      File "C:\Users\Sharky\Desktop\lieferando\fetch.py", line 17, in <module>
        res = Restaurant(r)
      File "<string>", line 2, in __init__
      File "C:\Python39\lib\site-packages\pony\orm\core.py", line 520, in new_func
        return func(*args, **kwargs)
      File "C:\Users\Sharky\Desktop\lieferando\db.py", line 72, in __init__
        menus = [MenuCategory(self, m) for m in rd['mc']['cs']['ct']]
      File "C:\Users\Sharky\Desktop\lieferando\db.py", line 72, in <listcomp>
        menus = [MenuCategory(self, m) for m in rd['mc']['cs']['ct']]
      File "C:\Users\Sharky\Desktop\lieferando\db.py", line 45, in __init__
        menus += [Menu(self, d) for d in ct['ps']['pr']]
      File "C:\Users\Sharky\Desktop\lieferando\db.py", line 45, in <listcomp>
        menus += [Menu(self, d) for d in ct['ps']['pr']]
      File "C:\Users\Sharky\Desktop\lieferando\db.py", line 20, in __init__
        super().__init__(category=category, name=name, price=price, id=id, description=description, *args, **kwargs)
      File "C:\Python39\lib\site-packages\pony\orm\core.py", line 4712, in __init__
        avdict[attr] = attr.validate(val, obj, from_db=False)
      File "C:\Python39\lib\site-packages\pony\orm\core.py", line 2544, in validate
        val = Attribute.validate(attr, val, obj, entity, from_db)
      File "C:\Python39\lib\site-packages\pony\orm\core.py", line 2232, in validate
        if cache is not val._session_cache_:
    AttributeError: _session_cache_

    I hope someone can make sense of this. 😕

    opened by SharkyRawr 1
  • TypeError in `issubclass` call in Index._init_ if component of composite key or index is redefined

    TypeError in `issubclass` call in Index._init_ if component of composite key or index is redefined

    Malformed schema:

    # test.py
    class A(db.Entity):
        foo = Required(int)
        bar = Required(int)
        PrimaryKey(foo, bar) # Also true for composite_key and composite_index
        bar = Required(int)


    Traceback (most recent call last):
      File "test.py", line 16, in <module>
        class A(db.Entity):
      File "<string>", line 2, in __init__
      File "pony/utils/utils.py", line 78, in cut_traceback
        reraise(exc_type, exc, full_tb)
      File "pony/utils/utils.py", line 95, in reraise
        try: raise exc.with_traceback(tb)
      File "pony/utils/utils.py", line 61, in cut_traceback
        try: return func(*args, **kwargs)
      File "pony/orm/core.py", line 3792, in __init__
        for index in indexes: index._init_(entity)
      File "pony/orm/core.py", line 2644, in _init_
        if not issubclass(entity, attr.entity): throw(ERDiagramError,
    TypeError: issubclass() arg 2 must be a class or tuple of classes

    Adding a print before that line:

    core.py L2644:
    	entity: <class 'test.A'>
            type(attr): <class `pony.orm.core.Required`>
    	attr: ?.?
    	attr.entity: None
    opened by shintoo 0
  • cannot generate mapping after deconnection with subclass

    cannot generate mapping after deconnection with subclass

    Hi I have in my db déclaration these two table

    class Customer(db.Entity):
        id = PrimaryKey(int, auto=True)
        first_name = Required(str)
        last_name = Required(str)
        email = Required(str, unique=True)
        localisation = Optional(str)
        mobile_number = Optional(str)
        phone_number = Optional(str)
        tickets = Set("Ticket")
        appointments = Set(Appointment)
        notes = Optional(str)
    class Planner(Customer):
        plannings = Set(Planning)
        password = Required(str)
        is_admin = Optional(bool)

    I use this two function to initialise and release my db

    def init_database(database_uri):
        settings = parse_uri(database_uri)
        except DatabaseError:
    def release_database():
        db.provider = None
        db.schema = None

    during building of my unit test I would like initialise on each test but when I try to initialize second time I have this exception

    >>> import ITPlanning.db as db
    >>> db.init_database("sqlite://")
    >>> db.release_database()
    >>> db.init_database("sqlite://")
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "/home/vincent/Documents/ITPlanning/ITPlanning/db.py", line 133, in init_database
      File "<string>", line 2, in generate_mapping
      File "/home/vincent/Documents/ITPlanning/env/lib/python3.9/site-packages/pony/utils/utils.py", line 77, in cut_traceback
        reraise(exc_type, exc, last_pony_tb)
      File "/home/vincent/Documents/ITPlanning/env/lib/python3.9/site-packages/pony/utils/utils.py", line 95, in reraise
        try: raise exc.with_traceback(tb)
      File "/home/vincent/Documents/ITPlanning/env/lib/python3.9/site-packages/pony/orm/core.py", line 990, in generate_mapping
        if table_name is not None: throw(NotImplementedError,
      File "/home/vincent/Documents/ITPlanning/env/lib/python3.9/site-packages/pony/utils/utils.py", line 108, in throw
        raise exc  # Set "pony.options.CUT_TRACEBACK = False" to see full traceback
    NotImplementedError: Cannot specify table name for entity 'Planner' which is subclass of 'Customer'
    opened by vincentDcmps 0
  • v0.7.14(Nov 23, 2020)


    • Add Python 3.9 support
    • Allow to use kwargs in select: Entity.select(**kwargs) and obj.collection.select(**kwargs), a feature that was announced but actually missed from 0.7.7
    • Add support for volatile collection attributes that don't throw "Phantom object appeared/disappeared" exceptions


    • Fix negative timedelta conversions
    • Pony should reconnect to PostgreSQL when receiving 57P01 error (AdminShutdown)
    • Allow mixing compatible types (like int and float) in coalesce() arguments
    • Support of subqueries in coalesce() arguments
    • Fix using aggregated subqueries in ORDER BY section
    • Fix queries with expressions like (x, y) in ((a, b), (c, d))
    • #451: KeyError for seeds with unique attributes in SessionCache.update_simple_index()
    Source code(tar.gz)
    Source code(zip)
  • v0.7.13(Mar 3, 2020)

  • v0.7.12(Feb 4, 2020)


    • CockroachDB support added
    • CI testing for SQLite, PostgreSQL & CockroachDB


    • 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
    Source code(tar.gz)
    Source code(zip)
  • v0.7.11(Oct 23, 2019)


    • #472: Python 3.8 support
    • Support of hybrid functions (inlining simple Python functions into query)
    • #438: support datetime-datetime, datetime-timedelta, datetime+timedelta in queries


    • #430: add ON DELETE CASCADE for many-to-many relationships
    • #465: Should reconnect to MySQL on OperationalError 2013 'Lost connection to MySQL server during query'
    • #468: Tuple-value comparisons generate incorrect queries
    • #470 fix PendingDeprecationWarning of imp module
    • Fix incorrect unpickling of objects with Json attributes
    • Check value of discriminator column on object creation if set explicitly
    • Correctly handle Flask current_user proxy when adding new items to collections
    • Some bugs in syntax of aggregated queries were fixed
    • Fix syntax of bulk delete queries
    • Bulk delete queries should clear query results cache so next select will get correct result from the database
    • Fix error message when hybrid method is too complex to decompile
    Source code(tar.gz)
    Source code(zip)
  • v0.7.10(Apr 20, 2019)


    • 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
    Source code(tar.gz)
    Source code(zip)
  • v0.7.9(Jan 21, 2019)

  • v0.7.8(Jan 19, 2019)

    This is a bug fix release


    • #414: prefetching Optional relationships fails on 0.7.7
    • Fix a bug caused by incorrect deduplication of column values
    Source code(tar.gz)
    Source code(zip)
  • 0.7.7(Jan 17, 2019)

    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


    • 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


    • #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
    Source code(tar.gz)
    Source code(zip)
  • 0.7.6(Aug 10, 2018)

    Features since 0.7.5:

    • 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


    • 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
    Source code(tar.gz)
    Source code(zip)
  • 0.7.6rc1(Aug 8, 2018)

    This is mostly bugfix release to fix bugs introduced in 0.7.4

    New 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
    • #367: @db.on_connect decorator added


    • 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
    Source code(tar.gz)
    Source code(zip)
  • 0.7.5(Jul 24, 2018)

  • 0.7.4(Jul 23, 2018)

    Major features

    • Hybrid methods and properties added: https://docs.ponyorm.com/entities.html#hybrid-methods-and-properties
    • 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


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


    • 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
    Source code(tar.gz)
    Source code(zip)
  • 0.7.3(Oct 23, 2017)

    New features

    • where() method added to query
    • 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 to db_session added
    • 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, 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


    • 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 should check 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
    Source code(tar.gz)
    Source code(zip)
  • 0.7.2(Jul 17, 2017)

    New features

    • 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 https://docs.ponyorm.com/api_reference.html#Database.bind
    • The optimistic attribute option is added https://docs.ponyorm.com/api_reference.html#cmdoption-arg-optimistic


    • Fixes #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.
    • Fixes #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.
    • Fixes #232: negate check for numeric expressions now checks if value is zero or NULL
    • Fixes #238, fixes #133: raise TransactionIntegrityError exception instead of AssertionError if obj.collection.create(**kwargs) creates a duplicate object
    • Fixes #221: issue with unicode json path keys
    • Fixes bug when discriminator column is used as a part of a primary key
    • Handle situation when SQLite blob column contains non-binary value
    Source code(tar.gz)
    Source code(zip)
  • 0.7.1(Jan 10, 2017)

    New features

    • New warning DatabaseContainsIncorrectEmptyValue added, it is raised when the required attribute is empty during loading an entity from the database


    • Fixes #216: Added Python 3.6 support
    • Fixes #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
    • 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"
    Source code(tar.gz)
    Source code(zip)
  • 0.7(Oct 11, 2016)

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

    New features

    • Added getattr() support in queries: https://docs.ponyorm.com/api_reference.html#getattr

    Backward incompatible changes

    • #159: exceptions happened during flush() should not be wrapped with CommitException

    Before this release an exception that happened in a hook(https://docs.ponyorm.com/api_reference.html#entity-hooks), 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.


    • #190: Timedelta is not supported when using pymysql
    Source code(tar.gz)
    Source code(zip)
  • 0.6.6(Aug 22, 2016)

    New features

    • Added native JSON data type support in all supported databases: https://docs.ponyorm.com/json.html

    Backward incompatible changes

    • Dropped Python 2.6 support


    • #179 Added the compatibility with PYPY using psycopg2cffi
    • Added an experimental @db_session strict parameter: https://docs.ponyorm.com/transactions.html#strict


    • #182 - LEFT JOIN doesn't work as expected for inherited entities when foreign key is None
    • Some small bugs were fixed
    Source code(tar.gz)
    Source code(zip)
  • 0.6.5(Apr 4, 2016)


    • Fixes #172: Query prefetch() method should load specified lazy attributes right in the main query if possible


    • Fixes #168: Incorrect caching when slicing the same query multiple times
    • Fixes #169: When py_check() returns False, Pony should truncate too large values in resulting ValueError message
    • Fixes #171: AssertionError when saving changes of multiple objects
    • Fixes #176: Autostripped strings are not validated correctly for Required attributes

    See blog post for more detailed information: https://blog.ponyorm.com/2016/04/04/pony-orm-release-0-6-5/

    Source code(tar.gz)
    Source code(zip)
  • 0.6.4(Feb 10, 2016)

    Pony ORM Release 0.6.4 (2016-02-10)

    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
    Source code(tar.gz)
    Source code(zip)
  • 0.6.3(Feb 5, 2016)

    This release was intended to fix the behavior of obj.flush(), but failed to do it in a proper way. Please skip this release and update to 0.6.4 if you are using obj.flush() method.

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

    Source code(tar.gz)
    Source code(zip)
  • 0.6.2(Jan 11, 2016)

    Pony ORM Release 0.6.2 (2016-01-11)

    Blogpost: https://blog.ponyorm.com/2016/01/11/pony-orm-release-0-6-2/

    The documentation was moved from this repo to a separate one at https://github.com/ponyorm/pony-doc The compiled version can be found at https://docs.ponyorm.com

    New features

    • Python 3.5 support
    • #132, #145: raw_sql() function was added
    • #126: Ability to use @db_session with generator functions
    • #116: Add support to select by UUID
    • Ability to get string SQL statement using the Query.get_sql() method
    • New function delete(gen) and Query.delete(bulk=False)
    • Now it is possible to override Entity.init() and declare custom entity methods

    Backward incompatible changes

    • Normalizing table names for symmetric relationships
    • Autostrip - automatically remove leading and trailing characters


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

    • Closed #65: Now the select(), filter(), order_by(), page(), limit(), random() methods can be applied to collection attributes
    • Closed #105: Now you can pass globals and locals to the select() function
    • Improved inheritance support in queries: select(x for x in BaseClass if x.subclass_attr == y)
    • Now it is possible to do db.insert(SomeEntity, column1=x, column2=y) instead of db.insert(SomeEntity._table_, column1=x, column2=y)
    • Discriminator attribute can be part of the composite index
    • Now it is possible to specify the attribute name instead of the attribute itself in composite index
    • Query statistics: global_stats_lock is deprecated, just use global_stats property without any locking
    • New load() method for entity instances which retrieves all unloaded attributes except collections
    • New load() method for collections, e.g. customer.orders.load()
    • Enhanced error message when descendant classes declare attributes with the same name
    • 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
    Source code(tar.gz)
    Source code(zip)
  • 0.6(Nov 5, 2014)

    Changes since Pony ORM 0.6rc3:

    • Fixed #94: Aggregated subquery bug fixed

    Short description

    • Official support of Python 3 added!
    • As a string attribute type, use str instead of unicode for compatibility with Python 3
    • Use int with size=64 option instead of long for compatibility with Python 3
    • In Python 3, use pure-python pymysql adapter instead of MySQLdb
    • Multiple bugfixes and optimizations since 0.5.4 release

    Full description:


    Source code(tar.gz)
    Source code(zip)
  • 0.6rc3(Oct 30, 2014)


    • Fixed #18: Allow to specify size and unsigned for int type
    • Fixed #77: Discriminate Pony-generated fields in entities: Attribute.is_implicit field added
    • Fixed #83: Entity.get() should issue LIMIT 2 when non-unique criteria used for search
    • Fixed #84: executing db.insert() should turn off autocommit and begin transaction
    • Fixed #88: composite_index(*attrs) added to support non-unique composite indexes
    • Fixed #89: IN / NOT IN clauses works different with empty sequence
    • Fixed #90: Do not automatically add "distinct" if query.first() used
    • Fixed #91: document automatic "distinct" behaviour and also .without_distinct()
    • Fixed #92: without_distinct() and first() do not work together correctly

    Deprecation of attribute long type

    • size and unsigned options for int attributes link

    Since the long type has gone in Python 3, the long type is deprecated in Pony now. Instead of long you should use the int type and specify the size option:

        class MyEntity(db.Entity):
            attr1 = Required(long) # deprecated
            attr2 = Required(int, size=64) # new way for using BIGINT type in the database
    Source code(tar.gz)
    Source code(zip)
  • 0.6rc2(Oct 10, 2014)

  • 0.6rc1(Oct 8, 2014)

    Blog post: http://blog.ponyorm.com/2014/10/08/pony-orm-0-6-release-candidate-1

    New features:

    • Python 3 support
    • pymysql adapter support for MySQL databases

    Backward incompatible changes

    Now Pony treats both str and unicode attribute types as they are unicode strings in both Python 2 and 3. So, the attribute declaration attr = Required(str) is equal to attr = Required(unicode) in Python 2 and 3. The same thing is with LongStr and LongUnicode - both of them are represented as unicode strings now.

    For the sake of backward compatibility Pony adds unicode as an alias to str and buffer as an alias to bytes in Python 3.

    Other changes and bug fixes

    • 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
    Source code(tar.gz)
    Source code(zip)
  • 0.5.4(Sep 22, 2014)

    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 val: val >= 0 and val <= 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)

    New features:

    • 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
    Source code(tar.gz)
    Source code(zip)
  • 0.5.3(Sep 22, 2014)

  • 0.5.2(Aug 11, 2014)


    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.


    • New to_dict() method can be used to convert entity instance to dictionary. This method can be useful when you need to serialize an object to JSON or other format


    • Now select() function and filter() method of the query object can accept lambdas with closures
    • Some minor bugs were fixed
    Source code(tar.gz)
    Source code(zip)
  • 0.5.1(Jul 11, 2014)


    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 T separator between date and time
    • Entity.delete() bug fixed
    • Lazy attribute loading bug fixed
    Source code(tar.gz)
    Source code(zip)
A Python Object-Document-Mapper for working with MongoDB

MongoEngine Info: MongoEngine is an ORM-like layer on top of PyMongo. Repository: https://github.com/MongoEngine/mongoengine Author: Harry Marr (http:

MongoEngine 3.6k Oct 24, 2021
Beanie - is an Asynchronous Python object-document mapper (ODM) for MongoDB

Beanie - is an Asynchronous Python object-document mapper (ODM) for MongoDB, based on Motor and Pydantic.

Roman 277 Oct 18, 2021
A curated list of awesome tools for SQLAlchemy

Awesome SQLAlchemy A curated list of awesome extra libraries and resources for SQLAlchemy. Inspired by awesome-python. (See also other awesome lists!)

Hong Minhee (洪 民憙) 2.3k Oct 21, 2021
A Python Library for Simple Models and Containers Persisted in Redis

Redisco Python Containers and Simple Models for Redis Description Redisco allows you to store objects in Redis. It is inspired by the Ruby library Ohm

sebastien requiem 434 Oct 1, 2021
Rich Python data types for Redis

Created by Stephen McDonald Introduction HOT Redis is a wrapper library for the redis-py client. Rather than calling the Redis commands directly from

Stephen McDonald 273 Sep 2, 2021
Piccolo - A fast, user friendly ORM and query builder which supports asyncio.

A fast, user friendly ORM and query builder which supports asyncio.

null 566 Oct 20, 2021
The ormar package is an async mini ORM for Python, with support for Postgres, MySQL, and SQLite.

python async mini orm with fastapi in mind and pydantic validation

null 674 Oct 23, 2021
The Orator ORM provides a simple yet beautiful ActiveRecord implementation.

Orator The Orator ORM provides a simple yet beautiful ActiveRecord implementation. It is inspired by the database part of the Laravel framework, but l

Sébastien Eustace 1.3k Oct 17, 2021
Pydantic model support for Django ORM

Pydantic model support for Django ORM

Jordan Eremieff 189 Oct 23, 2021