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))
    ...

The query should be:

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

My code is:

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

But it doesn't replace the amount column.

Been trying to do this with for hours and I don't want to use raw SQL.