比较 SQLAlchemy ORM 中两列之间的差异 [英] Compare difference between two columns in SQLAlchemy ORM

问题描述

我试图找出如何做类似这个问题的答案 但使用 SQLAlchemy.

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

很难找到如何通过两列之间的差异对查询结果进行排序，而无需求助于原始 SQL.

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

非常感谢任何帮助！！

另外，出于好奇，是否可以创建一个列来自动计算其他两列之间的差异?例如，您会有一个收入和损失列，然后是一个自动组合这些的利润列.

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.

推荐答案

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

对于自动计算列，您可以使用 events

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

这篇关于比较 SQLAlchemy ORM 中两列之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！