Pony Object Relational Mapper

Downloads

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.

Documentation

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.

License

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

Comments
  • [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
        cache.close(rollback=True)
      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.

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

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

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

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

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

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

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

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

  • "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:

    https://gist.github.com/amireldor/3c7f1701761d6c92855b868e5df07af0

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

  • 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):
    
            db.create_all()
    
        def tearDown(self):
    
            db.session.remove()
            db.drop_all()
    

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

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

    thanks!

  • generate_mapping error

    generate_mapping error

    I use postgres schemas.

    _table_ = (schema, 'molecule')
    

    and then I run generate_mapping(create_tables=True) on existing db with tables I receive:

    DBSchemaError: Table "db_data"."molecule" cannot be created, because table "molecule" (with a different letter case) already exists in the database. Try to delete "molecule" table first.

    but without schemas this works fine.

  • Cannot use memcached? TransactionRolledBack, 'Object belongs to obsolete cache'

    Cannot use memcached? TransactionRolledBack, 'Object belongs to obsolete cache'

    When I use memcached for cache, it throw the error:

     File "/usr/local/lib/python2.7/dist-packages/pony/orm/core.py", line 871, in __get__
        if not obj._cache_.is_alive: throw(TransactionRolledBack, 'Object belongs to obsolete cache')
    

    How do I use the memcached?

  • Integer of higher length than 11

    Integer of higher length than 11

    I can't seem to find a way for pony to specify a db int field with a length > 11, so if I try and insert something longer than 11 digits, I get:

    cannot be stored in the database. DataError: 1264 Out of range value for column 'xxx' at row 1

    If I increase the column length manually of the database column, the same problem persists. Any ideas / suggestions?

  • Tuple comparison with =" is broken">

    Tuple comparison with ">=" is broken

    In the case if ">=", the translation of tuple comparison is wrong. The correct translation of (a, b) >= (c, d) is a __>__ c OR (a = c AND b > d). However, currently it is a __>=__ c OR (a = c AND b > d).

    Consider the following sample program:

    from pony import orm as pony
    
    DB_NAME = "test.sqlite"
    db = pony.Database()
    
    class Test(db.Entity):
        a = pony.Required(int)
        b = pony.Required(int)
    
    
    db.bind(provider="sqlite", filename=DB_NAME, create_db=True)
    db.generate_mapping(create_tables=True)
    pony.set_sql_debug(True)
    
    with pony.db_session as ses:
        _ = pony.get(f for f in Test if (f.a, f.b) >= (1, 2))
    

    with this output:

    GET NEW CONNECTION
    SWITCH TO AUTOCOMMIT MODE
    SELECT "f"."id", "f"."a", "f"."b"
    FROM "Test" "f"
    WHERE ("f"."a" >= 1 OR "f"."a" = 1 AND "f"."b" >= 2)
    LIMIT 2
    
    RELEASE CONNECTION
    
  • PonyOrm not work with UUID as PK

    PonyOrm not work with UUID as PK

    I have next entity:

    class SchemaEntity(db.Entity):
        id = orm.PrimaryKey(uuid.UUID, default=uuid.uuid4())
        source_name = orm.Required(str)
        target_name = orm.Required(str)
    

    I try do next query:

    entity = SchemaEntity[uuid.UUID('0e325a58-3445-4354-841b-56f586c69b1c')]
    

    result => pony.orm.core.ObjectNotFound: SchemaEntity[UUID('0e325a58-3445-4354-841b-56f586c69b1c')]

    but i have success result for SQL: select * from schema_ksunci_entity where id = '0e325a58-3445-4354-841b-56f586c69b1c'

    !wherwith

    i have success result for SchemaEntity.select()[:] query

    P.S. Python 3.10.5 Pony 0.7.16

  • Entity Attribute Documentation?

    Entity Attribute Documentation?

    In Django I could use help_text to document my models such that I could retrieve the information elsewhere in the program later.

    Is there a feature for this I am missing or is this a feature request?

  • Prevent a Session from being closed multiple times

    Prevent a Session from being closed multiple times

    Currently, the Flask API does not work correctly with the Flask.test_client() method when used multiple times.

    The reason for that is that due to the teardown hook added in pony/flask/init.py gets called after each time the test_client gets exited. However, the db_session got closed before already after the request, leading to two calls to session.exit(). This in turn will force the local context counter to go negative, so that the next call will fail with assert not context_counter...

  • ERDiagramError: Cannot define entity 'Car': database mapping has already been generated

    ERDiagramError: Cannot define entity 'Car': database mapping has already been generated

    I was following this tutorial https://docs.ponyorm.org/firststeps.html created the person entity but forgot to create the Car entity, and went on to run db.bind (to my Postgres db) and db.generate_mapping now I am stuck in a loop because I cannot create a new entity, and cannot edit the person entity(that was already created) what could be the possible way around?

Async ODM (Object Document Mapper) for MongoDB based on python type hints

ODMantic Documentation: https://art049.github.io/odmantic/ Asynchronous ODM(Object Document Mapper) for MongoDB based on standard python type hints. I

Sep 26, 2022
PubMed Mapper: A Python library that map PubMed XML to Python object

pubmed-mapper: A Python Library that map PubMed XML to Python object 中文文档 1. Philosophy view UML Programmatically access PubMed article is a common ta

Aug 2, 2022
Simplest SQL mapper in Python, probably

SQL MAPPER Basically what it does is: it executes some SQL thru a database connector you fed it, maps it to some model and gives to u. Also it can cre

Jan 6, 2022
A Relational Database Management System for a miniature version of Twitter written in MySQL with CLI in python.

Mini-Twitter-Database This was done as a database design course project at Amirkabir university of technology. This is a relational database managemen

Jun 12, 2022
A Pythonic, object-oriented interface for working with MongoDB.

PyMODM MongoDB has paused the development of PyMODM. If there are any users who want to take over and maintain this project, or if you just have quest

Sep 16, 2022
Pony Object Relational Mapper

Downloads Pony Object-Relational Mapper Pony is an advanced object-relational mapper. The most interesting feature of Pony is its ability to write que

Sep 17, 2022
Pytorch implementation of "A simple neural network module for relational reasoning" (Relational Networks)
Pytorch implementation of

Pytorch implementation of Relational Networks - A simple neural network module for relational reasoning Implemented & tested on Sort-of-CLEVR task. So

Sep 17, 2022
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:

Sep 26, 2022
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:

Sep 18, 2022
Async ODM (Object Document Mapper) for MongoDB based on python type hints

ODMantic Documentation: https://art049.github.io/odmantic/ Asynchronous ODM(Object Document Mapper) for MongoDB based on standard python type hints. I

Sep 26, 2022
PubMed Mapper: A Python library that map PubMed XML to Python object

pubmed-mapper: A Python Library that map PubMed XML to Python object 中文文档 1. Philosophy view UML Programmatically access PubMed article is a common ta

Aug 2, 2022
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.

Sep 19, 2022
Python library for serializing any arbitrary object graph into JSON. It can take almost any Python object and turn the object into JSON. Additionally, it can reconstitute the object back into Python.

jsonpickle jsonpickle is a library for the two-way conversion of complex Python objects and JSON. jsonpickle builds upon the existing JSON encoders, s

Sep 26, 2022
whm also known as wifi-heat-mapper is a Python library for benchmarking Wi-Fi networks and gather useful metrics that can be converted into meaningful easy-to-understand heatmaps.
whm also known as wifi-heat-mapper is a Python library for benchmarking Wi-Fi networks and gather useful metrics that can be converted into meaningful easy-to-understand heatmaps.

whm also known as wifi-heat-mapper is a Python library for benchmarking Wi-Fi networks and gather useful metrics that can be converted into meaningful easy-to-understand heatmaps.

Sep 18, 2022
Annotates sequences with Eggnog-mapper and hhblits against PDB70

Annotating "hypothetical" proteins with the PDB See config/ for configuration information. This workflow takes as input a set of protein sequences. It

Apr 5, 2022
✂️🕷️ Spider-Cut is a Network Mapper Framework (NMAP Framework)
✂️🕷️ Spider-Cut is a Network Mapper Framework (NMAP Framework)

Spider-Cut is a Network Mapper Framework (NMAP Framework) Installation | Usage | Creators | Donate Installation # Kali Linux | WSL

Mar 7, 2022
Simplest SQL mapper in Python, probably

SQL MAPPER Basically what it does is: it executes some SQL thru a database connector you fed it, maps it to some model and gives to u. Also it can cre

Jan 6, 2022
Temporal-Relational CrossTransformers

Temporal-Relational Cross-Transformers (TRX) This repo contains code for the method introduced in the paper: Temporal-Relational CrossTransformers for

Sep 22, 2022
The next generation relational database.

What is EdgeDB? EdgeDB is an open-source object-relational database built on top of PostgreSQL. The goal of EdgeDB is to empower its users to build sa

Sep 16, 2022
Code accompanying "Dynamic Neural Relational Inference" from CVPR 2020

Code accompanying "Dynamic Neural Relational Inference" This codebase accompanies the paper "Dynamic Neural Relational Inference" from CVPR 2020. This

Aug 14, 2022