SQLAlchemy 自定义查询列

Question

我有一个声明性表格，定义如下：

class Transaction(Base):
    __tablename__ = "transactions"
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer)
    transfer_account_id = Column(Integer)
    amount = Column(Numeric(12, 2))
    ...

查询应该是：

SELECT id, (CASE WHEN transfer_account_id=1 THEN -amount ELSE amount) AS amount
FROM transactions
WHERE account_id = 1 OR transfer_account_id = 1

我的代码是：

query = Transaction.query.filter_by(account_id=1, transfer_account_id=1)
query = query.add_column(case(...).label("amount"))

但是它不能替换amount列。

已经试了好几个小时，不想使用原始SQL语句来完成这个任务。

- Paul Onutor

Answer 1

无论您执行何种查询，都不会替换原始的amount列。但是您可以使用以下查询加载另一列：

q = session.query(Transaction,
                  case([(Transaction.transfer_account_id==1, -1*Transaction.amount)], else_=Transaction.amount).label('special_amount')
                  )
q = q.filter(or_(Transaction.account_id==1, Transaction.transfer_account_id==1))

这不仅会返回Transaction对象，而且还会返回tuple(Transaction, Decimal)。

---

但是，如果您希望此属性成为对象的一部分，则：
由于您的case when ...函数与WHERE中的条件完全独立，因此建议您按以下方式更改代码：

1）向对象添加一个属性，该属性执行以下case when ...检查：

@property
def special_amount(self):
    return -self.amount if self.transfer_account_id == 1 else self.amount

您可以完全包装提供setter属性的金额特殊处理：

@special_amount.setter
def special_amount(self, value):
    if self.transfer_account_id is None:
        raise Exception('Cannot decide on special handling, because transfer_account_id is not set')
    self.amount = -value if self.transfer_account_id == 1 else value

2) 修复您的查询，只使用or_子句作为筛选条件（您的查询似乎根本不起作用）：

q = session.query(Transaction).filter(
    or_(Transaction.account_id==1, 
        Transaction.transfer_account_id==1)
)

# then get your results with the proper amount sign:
for t in q.all():
    print q.id, q.special_amount

- van

---

排序方面怎么样？我能加一个order_by()吗？ - Paul Onutor

使用查询 - 可以；使用@property - 不太行，但是一旦检索并存储在列表中，您可以轻松地对Transaction对象进行排序。 - van

Answer 2

你要找的结构叫做column_property。你可以使用一个次级映射器来实际替换金额列。你确定不直接在数据库中存储负值或给“更正后”的列取一个不同的名称，而让自己变得过于困难了吗？

from sqlalchemy.orm import mapper, column_property
wrongmapper = sqlalchemy.orm.mapper(Transaction, Transaction.__table,
    non_primary = True,
    properties = {'amount':
        column_property(case([(Transaction.transfer_account_id==1, -1*Transaction.amount)], 
        else_=Transaction.amount)})

Session.query(wrongmapper).filter(...)

- joeforker