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.
from datetime import date
from sqlalchemy import asc, desc, cast, case, create_engine
from sqlalchemy import Column, Integer, Numeric, Enum, Date
from sqlalchemy.orm import configure_mappers, sessionmaker
from sqlalchemy.ext.hybrid import hybrid_property, Comparator
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base()
class TotalValueComparator(Comparator):
def __init__(self, cls):
# need to cast to 2 decimals - display is 2 decimals
# cls.UnitPrice is 4 decimals
expr = cast(cls.Units * cls.UnitPrice, Numeric(9, 2))
super().__init__(expr)
def asc(self):
expr = self.__clause_element__()
return asc(expr)
def desc(self):
expr = self.__clause_element__()
return desc(expr)
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)
if __name__ == '__main__':
configure_mappers()
Base.metadata.create_all(engine)
db_session = sessionmaker(bind=engine)()
trans = Transaction()
trans.Type = "BUY"
trans.Units = 250000
trans.UnitPrice = 0.0200
trans.Brokerage = 19.95
db_session.add(trans)
trans = Transaction()
trans.Type = "SELL"
trans.Units = 250000
trans.UnitPrice = 0.0210
trans.Brokerage = 19.95
db_session.add(trans)
db_session.commit()
print("\nTransactions")
trans = db_session.query(Transaction).all()
format_string = "{:<4} {:>7} {:>9} {:>10} {:>9} {:>10}"
print(format_string.format("Type","Units","UnitPrice","TotalValue",
"Brokerage","TotalCost"))
for tran in trans:
print(format_string.format(tran.Type, str(tran.Units),
str(tran.UnitPrice),
"{:.2f}".format(tran.total_value),
str(tran.Brokerage),
"{:.2f}".format(tran.total_cost)))
print("\nTransactions - Sort by TotalValue desc - OK")
trans = db_session.query(Transaction).order_by(desc(Transaction.total_value)).all()
print(format_string.format("Type","Units","UnitPrice","TotalValue",
"Brokerage","TotalCost"))
for tran in trans:
print(format_string.format(tran.Type, str(tran.Units),
str(tran.UnitPrice),
"{:.2f}".format(tran.total_value),
str(tran.Brokerage),
"{:.2f}".format(tran.total_cost)))
print("\nTransactions - Sort by TotalCost desc - NOT OK")
trans = db_session.query(Transaction).order_by(desc(Transaction.total_cost)).all()
# trans = db_session.query(Transaction).order_by(Transaction.total_cost.desc()).all()
print(format_string.format("Type","Units","UnitPrice","TotalValue",
"Brokerage","TotalCost"))
for tran in trans:
print(format_string.format(tran.Type, str(tran.Units),
str(tran.UnitPrice),
"{:.2f}".format(tran.total_value),
str(tran.Brokerage),
"{:.2f}".format(tran.total_cost)))
