>
> User.firstname is not the value from any particular row - it's the
> (ORM-level) column object.
It's a little abstruse here that a ORM-level instrumented column object,
such as User.firstname works, but a true Column object, such as
User.__table__.c.firstname doesn't. Maybe I misunderstood your comment here?
On Mon, Oct 3, 2016 at 1:40 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> 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_lo
>> okup[(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_loo
>> kup[(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/to
>> pic/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.
