SQLAlchemy自定义查询列 [英] SQLAlchemy custom query column
问题描述
我有一个这样定义的声明性表:
I have a declarative table defined like this:
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.
Been trying to do this with for hours and I don't want to use raw SQL.
推荐答案
您执行的任何查询都不会替换原始的amount
列.但是您可以使用以下查询加载另一列:
Any query you do will not replace original amount
column. But you can load another column using following query:
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)
This will not return only Transaction
objects, but rather tuple(Transaction, Decimal)
但是,如果您希望此属性成为对象的一部分,则:
由于您的case when ...
函数完全独立于WHERE
中的条件,因此我建议您按以下方式更改代码:
But if you want this property be part of your object, then:
Since your case when ...
function is completely independent from the condition in WHERE
, I would suggest that you change your code in following way:
1)向您的对象添加一个属性,该属性按照以下步骤进行case when ...
检查:
1) add a property to you object, which does the case when ...
check as following:
@property
def special_amount(self):
return -self.amount if self.transfer_account_id == 1 else self.amount
您还可以完全包装提供setter属性的金额的这种特殊处理:
You can completely wrap this special handling of the amount providing a setter property as well:
@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_
子句的filter子句(看起来您的查询根本不起作用):
2) fix your query to only have a filter clause with or_
clause (it looks like your query does not work at all):
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
这篇关于SQLAlchemy自定义查询列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!