heya - haven't run it yet but if you are on SQLAlchemy 1.3, the signature for case() is different (sorry, note the list ):
https://docs.sqlalchemy.org/en/13/core/sqlelement.html?highlight=case#sqlalchemy.sql.expression.case expr = case( [(cls.Type == "SELL", cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) - cls.Brokerage)], else_=cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) + cls.Brokerage ) On Fri, Nov 19, 2021, at 2:06 AM, gvv wrote: > Hi All, > > Sorry About my previous post - did not read the posting Guidelines. > > Using Sqlalchemy 1.3.23 > In this example using SQLlite memory, but is also happening in Postgresql 12.9 > > class TotalCostComparator(Comparator): > def __init__(self, cls): > expr = case( > (cls.Type == "SELL", cast(cls.Units * cls.UnitPrice, Numeric(9, > 2)) - cls.Brokerage), > else_=cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) + > cls.Brokerage > ) > def asc(self): > expr = self.__clause_element__() > return asc(expr) > def desc(self): > expr = self.__clause_element__() > return desc(expr) > > class Transaction(Base): > __tablename__ = "Transactions" > Id = Column(Integer, autoincrement=True, primary_key=True, nullable=False) > Type = Column(Enum("BUY", "SELL", name="HoldingTransactionType"), > nullable=False, default="BUY" > ) > Units = Column(Integer, nullable=False) > UnitPrice = Column(Numeric(9, 4), nullable=False) > Brokerage = Column(Numeric(9, 2)) > > # calculated columns > @hybrid_property > def total_value(self): > return self.Units * self.UnitPrice > > @total_value.comparator > def total_value(cls): > return TotalValueComparator(cls) > > @hybrid_property > def total_cost(self): > if self.Type == "SELL": > return self.total_value - self.Brokerage > return self.total_value + self.Brokerage > > @total_cost.comparator > def total_cost(cls): > return TotalCostComparator(cls) > > I am getting an error in this code: > session.query(Transaction).order_by(desc(Transaction.total_cost)).all() > > with this traceback: > Traceback (most recent call last): > File "testhybrid.py", line 122, in <module> > trans = > db_session.query(Transaction).order_by(desc(Transaction.total_cost)).all() > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/ext/hybrid.py", > line 898, in __get__ > return self._expr_comparator(owner) > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/ext/hybrid.py", > line 1105, in expr_comparator > comparator(owner), > File "testhybrid.py", line 75, in total_cost > return TotalCostComparator(cls) > File "testhybrid.py", line 31, in __init__ > expr = case( > File "<string>", line 2, in case > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", > line 2437, in __init__ > whenlist = [ > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", > line 2439, in <listcomp> > for (c, r) in whens > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", > line 432, in __getitem__ > return self.operate(getitem, index) > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", > line 762, in operate > return op(self.comparator, *other, **kwargs) > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", > line 432, in __getitem__ > return self.operate(getitem, index) > File "<string>", line 1, in <lambda> > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", > line 67, in operate > return o[0](self.expr, op, *(other + o[1:]), **kwargs) > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", > line 237, in _getitem_impl > _unsupported_impl(expr, op, other, **kw) > File > "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", > line 241, in _unsupported_impl > raise NotImplementedError( > NotImplementedError: Operator 'getitem' is not supported on this expression > > Sorry couldnt align the code. But I have attached the code to replicate it. > > Thanks in advance, > George > > > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/ddc7e6f0-5e3e-459d-9adf-e02d3220f67cn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/ddc7e6f0-5e3e-459d-9adf-e02d3220f67cn%40googlegroups.com?utm_medium=email&utm_source=footer>. > > > *Attachments:* > * testhybrid.py -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5e2a206a-26ad-4f70-a5e3-f9195cda5a03%40www.fastmail.com.
