Hi Mike, Thank you very much. A thousand years and I still wouldn't be able to figure that one out.
And thank you also for clarifying use of Comparators - makes coding simpler with expression instead. Thanks, George On Saturday, 20 November 2021 at 00:28:00 UTC+11 Mike Bayer wrote: > there's no need to use custom Comparator objects, these are difficult to > get right and there's not really any need to use them except in the > extremely unusual case that you need specific SQL operators to do something > different, which itself is not really a thing in modern SQLAlchemy. > > Just use @expression along with the case([]) syntax and your program runs > fine. thanks for reworking this into a succinct example. > > # calculated columns > @hybrid_property > def total_value(self): > return self.Units * self.UnitPrice > > @total_value.expression > def total_value(cls): > return cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) > > @hybrid_property > def total_cost(self): > if self.Type == "SELL": > return self.total_value - self.Brokerage > return self.total_value + self.Brokerage > > @total_cost.expression > def total_cost(cls): > return 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+...@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/fd1b2e58-5675-48c5-aec4-604e2a48ada7n%40googlegroups.com.