sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression

[gvv]

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

[Mike Bayer]

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

        )

--

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.

Attachments:

• testhybrid.py
  

[Mike Bayer]

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:

[gvv]

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