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 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.