Thank you Simon. Your explanation helps me understand this quite a lot. Sometimes the documentation is so terse that only when you fully understand the subject then you can understand it by reading it:) But still if I want to implement this hybrid property from the query level, how would you suggest modify my current code? Or maybe you could please point out a link to where I can explore further on the python to SQL transition? Thank you so much.
Jinghui On Mon, Oct 3, 2016 at 1:27 AM, Simon King <si...@simonking.org.uk> wrote: > On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu <niujing...@gmail.com> wrote: > > I have a ledger table and a corresponding python class. I defined the > model > > using SQLAlchemy, as follows, > > > > class Ledger(Base): > > __tablename__ = 'ledger' > > > > currency_exchange_rate_lookup = {('CNY', 'CAD'): 0.2} > > > > amount = Column(Numeric(10, 2), nullable=False) > > currency = Column(String, nullable=False) > > payment_method = Column(String) > > notes = Column(UnicodeText) > > > > @hybrid_property > > def amountInCAD(self): > > if self.currency == 'CAD': > > return self.amount > > exchange_rate = self.currency_exchange_rate_ > lookup[(self.currency, > > 'CAD')] > > CAD_value = self.amount * Decimal(exchange_rate) > > CAD_value = round(CAD_value, 2) > > return CAD_value > > > > @amountInCAD.expression > > def amountInCAD(cls): > > amount = cls.__table__.c.amount > > currency_name = cls.__table__.c.currency > > exchange_rate = cls.currency_exchange_rate_ > lookup[(currency_name, > > 'CAD')] > > return case([ > > (cls.currency == 'CAD', amount), > > ], else_ = round((amount * Decimal(exchange_rate)),2)) > > > > Now as you can see, I want to create a hybrid property called > "amountInCAD". > > The Python level getter seems to be working fine. However the SQL > expression > > doesn't work. > > > > Now if I run a query like this: > > > >>>>db_session.query(Ledger).filter(Ledger.amountInCAD > 1000) > > > > SQLAlchemy gives me this error: > > > > File "ledger_db.py", line 43, in amountInCAD > > exchange_rate = cls.currency_exchange_rate_lookup[(currency_name, > > 'CAD')] > > KeyError: (Column('currency', String(), table=<ledger>, nullable=False), > > 'CAD') > > > > I've researched SQLAlchemy's online documentation regarding hybrid > > property.http://docs.sqlalchemy.org/en/latest/orm/ > mapped_sql_expr.html#using-a-hybrid > > Comparing my code to the example code, I don't understand why mine > doesn't > > work. If in the official example, cls.firstname can refer to a column of > > value, why in my code the cls.__table__.c.currencyonly returns a Column > not > > its value? > > Forget about using this in a query for a second. For example, open a > python shell, import your class, and type "Ledger.amountInCAD". This > will trigger the same exception. There's no way that > cls.__table__.c.currency can *ever* return a value from a specific > row, because you are accessing it from the *class*, which isn't > related to any specific row. > > I assume the example in the docs you are referring to is this one: > > @hybrid_property > def fullname(self): > return self.firstname + " " + self.lastname > > In this example, "User.fullname" is precisely equivalent to: > > User.firstname + " " + User.lastname > > User.firstname is not the value from any particular row - it's the > (ORM-level) column object. The result of that expression is another > SQL expression. > > Sorry, that's probably not a very good explanation. Has it made it any > clearer? > > Simon > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/7AsxiTT3Dtc/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.