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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/fd1b2e58-5675-48c5-aec4-604e2a48ada7n%40googlegroups.com.
