Re: [sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression
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
> 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 "", 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
> 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 "", line 1, in
> 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)
>
Re: [sqlalchemy] raise error on insert/update PK?
You ought to be able to use the "sqlalchemy.func" system:
https://docs.sqlalchemy.org/en/14/core/tutorial.html#functions
server_default=sa.func.gen_random_uuid()
Hope that helps,
Simon
On Fri, Nov 19, 2021 at 6:21 AM jens.t...@gmail.com
wrote:
>
> Tim,
>
> I wanted to offload the UUID generation for the PK to the server
> (server_default instead of just default argument). But I wasn’t able to find
> gen_random_uuid() documented in the PostgreSQL dialect, should probably be
> under Column Valued Functions?
>
> So I ended up using server_default=text("gen_random_uuid()")
>
> Is that the (currently) recommended way? It seems to work…
>
> Thanks!
> Jens
>
> On Thursday, January 4, 2018 at 7:50:21 AM UTC+10 timc...@gmail.com wrote:
>>
>> Thanks so much! Really appreciate the example.
>>
>>
>>
>>
>> On Wednesday, January 3, 2018 at 3:46:47 PM UTC-5, Mike Bayer wrote:
>>>
>>> On Wed, Jan 3, 2018 at 1:18 PM, Tim Chen wrote:
>>> > Let's say I'm using a uuid PK for my models with a ` server_default` set
>>> > to
>>> > `gen_random_uuid()` (in PostgreSQL). Is there a way to ensure the
>>> > server_default value? I would like to catch any INSERT or UPDATE
>>> > statements
>>> > that set the PK value and raise an error if possible.
>>>
>>> Sure, I would use a before_cursor_execute() handler that does this.
>>> You can see the parameters and the statement coming in and raise an
>>> error if they have column values you don't want to see.
>>>
>>> http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
>>>
>>> this is cleanest using some of the internal state of the context so
>>> here is a demo:
>>>
>>> from sqlalchemy import *
>>> from sqlalchemy.orm import *
>>> from sqlalchemy import event
>>> from sqlalchemy.ext.declarative import declarative_base
>>>
>>> Base = declarative_base()
>>>
>>>
>>> class A(Base):
>>> __tablename__ = 'a'
>>> id = Column(Integer, primary_key=True)
>>> x = Column(Integer)
>>>
>>> e = create_engine("sqlite://", echo=True)
>>>
>>>
>>> @event.listens_for(e, "before_cursor_execute")
>>> def receive_before_cursor_execute(conn, cursor, statement, parameters,
>>> context, executemany):
>>> if context.isinsert:
>>> table = context.compiled.statement.table
>>> for col in table.primary_key:
>>> if col.key in context.compiled.binds:
>>> raise TypeError("no pk allowed!")
>>>
>>>
>>> Base.metadata.create_all(e)
>>>
>>> s = Session(e)
>>> s.add(A(x=5))
>>> s.commit() # OK
>>>
>>> s.add(A(id=2, x=7))
>>> s.commit() # not OK
>>>
>>>
>>>
>>>
>>>
>>>
>>> >
>>> > --
>>> > 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 post to this group, send email to sqlal...@googlegroups.com.
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> 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/96f5ba79-1dd5-487f-b493-32f0144289f4n%40googlegroups.com.
--
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/CAFHwexfO7aCbih4%2BWOPdScCsm5t99gVR1yXUJ-iCz65yurdCCg%40mail.gmail.com.
Re: [sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression
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
> 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 "", 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
> 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 "", line 1, in
> 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
>
>
>
Re: [sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression
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
)
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
> 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 "", 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
> 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 "", line 1, in
> 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
>
>
