Compare difference between two columns in SQLAlchemy ORM

Question

I'm trying to find out how to do something like the answer to this question but using SQLAlchemy.

Having a hard time finding how to order query results by the difference between two columns without resorting to raw SQL.

Any help is greatly appreciated!!

Also, just out of curiosity, is it possible to create a column that automatically calculates the difference between two other columns? For example, you would have a revenue and loss column, and then a profit column that automatically combined those.

Answer 1

session.query((Table.revenue - Table.loss).label('profit')).order_by('profit desc').all()

For automatically calculate column you can use events

from sqlalchemy import event

class Table(Model):
    id = Column(Integer, primary_key=True)
    revenue = Column(Integer)
    loss = Column(Integer)
    profit = Column(Integer)

@event.listens_for(Table.revenue, 'set')
def revenue_listener(target, value, oldvalue, initiator):
    # update profit when revenue change
    target.profit = value - (target.loss or 0)

@event.listens_for(Table.loss, 'set')
def loss_listener(target, value, oldvalue, initiator):
    # update profit when loss change
    target.profit = (target.revenue or 0) - value