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+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/355ee68b-aacc-4a88-9ec1-a82140acc49f%40www.fastmail.com.
