Experience creating a web application with Pony ORM

Hello, Habr!

Recently, I had the task of writing a Python web application in Python for sharing a restaurant bill between participants in a meal. Since we need a DB to store data about orders and users, the question arose of choosing ORM for working with the database. Development was carried out on Flask, so Django ORM was immediately swept away and the choice initially fell in the direction of SQLAlchemy. On the one hand, this ORM is almost omnipotent, but due to this, it is quite difficult to master. After suffering with alchemy for a while, I decided to find a simpler option so that development would go faster. As a result, Pony ORM was chosen for the project.

, , . .

, . , , .

PonyORM:

• ( lambda )

( )

, , Pony , , .

Pony , .

pip Pony:

pip install pony

DB API :

• PostgreSQL: psycopg psycopg2cffi
• Oracle: cx_Oracle
• MySQL: PyMySQL

Pony:

from pony.orm import *

— . .

db = Database()

. Database db.bind() . . SQLite ( ) PostgreSQL (). , Database. :

settings = dict(
    sqlite=dict(provider='sqlite', filename='pony.db', create_db=True),
    postgres=dict(provider='postgres', user='pony', password='pony', host='localhost', 
        database='pony')
)

:

db = Database(**settings['postgres'])

ER . , Python . , .

:

from datetime import datetime
from pony.orm import *

db = Database()

class User(db.Entity):
    id = PrimaryKey(int, auto=True)
    fullname = Optional(str)
    password = Optional(str)
    nickname = Optional(str)
    lended = Set('Credit', reverse='lender')
    borrowed = Set('Credit', reverse='borrower')
    credit_editions = Set('CreditEdition', reverse='user')
    sessions = Set('UserInSession')
    affected_editions = Set('CreditEdition', reverse='affected_user')

class Session(db.Entity):
    id = PrimaryKey(int, auto=True)
    title = Optional(str)
    start = Optional(datetime)
    end = Optional(datetime)
    ordereders = Set('OrderedItem')
    users = Set('UserInSession')

class OrderedItem(db.Entity):
    id = PrimaryKey(int, auto=True)
    session = Required(Session)
    title = Optional(str)
    price = Optional(int)
    user_in_sessions = Set('UserInSession')

class Credit(db.Entity):
    id = PrimaryKey(int, auto=True)
    lender = Required(User, reverse='lended')
    borrower = Required(User, reverse='borrowed')
    value = Optional(int)
    credit_editions = Set('CreditEdition')

class UserInSession(db.Entity):
    id = PrimaryKey(int, auto=True)
    user = Required(User)
    session = Required(Session)
    orders = Set(OrderedItem)
    value = Optional(int)

class CreditEdition(db.Entity):
    id = PrimaryKey(int, auto=True)
    user = Required(User, reverse='credit_editions')
    affected_user = Required(User, reverse='affected_editions')
    credit = Required(Credit)
    old_value = Optional(int)
    new_value = Optional(int)

db.generate_mapping()

, , .

( ). :

session = Session[sid]

Session , id == sid.

Also, as I said above, queries in Pony can be written in generator expressions, which makes writing them much more convenient than in alchemy.
Below is an example in which the database is sent a request to select users who are participants in this session and are not virtual users.

users = select(u for u in User if u not in session.users.user and not u.virtual)

Conclusion

I hope my article has been helpful to someone. If you are also interested in Pony, below I will leave links to the documentation and to the telegram channel of the community.

Thank you for the attention!

Pony Telegram Community Documentation