The first example from the docs is illustrating the most simple case,
where the function happens to work at both the instance and class
level. Here's the example:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
@hybrid_property
def fullname(self):
return self.firstname + " " + self.lastname
When you access <instance>.fullname, the "self" parameter refers to
the instance, so self.firstname and self.lastname are plain python
strings.
When you access <class>.fullname, the "self" parameter is now the
*class*, so self.firstname and self.lastname are SQLAlchemy column
properties. Since SA implements the "+" operator for those properties,
the result of the expression is an SQL expression. When you write
"User.fullname == 'Jinghui Niu", that becomes an SQL expression
looking something like:
(user.firstname || ' ' || user.lastname) == 'Jinghui Niu'
...except that it will use bind parameters for the string literals,
and the database-appropriate string concatenation operators.
It wouldn't make sense to write "User.__table__.c.firstname" in this
example, because that wouldn't work in the *instance* case. However,
if you split the implementations, so that you have one function for
the instance case, and a separate function for the class case (via
hybrid_property.expression), there's no reason you couldn't use
User.__table__.c.firstname in the class case. You *usually* don't need
to, since the ORM-level property User.firstname can be used in most of
the same places as User.__table__.c.firstname.
In other words, this *should* work (but I haven't tried it):
@hybrid_property
def fullname(self):
return self.firstname + " " + self.lastname
@fullname.expression
def fullname(cls):
return cls.__table__.c.firstname + " " + cls.__table__.c.lastname
...but it is redundant, because you can use "cls.firstname" instead of
"cls.__table__.c.firstname", and once you've done that the, the
implementation is exactly the same as the instance-level version and
so you can just skip the @fullname.expression definition altogether.
In answer to your other question, I don't know exactly how to
implement the SQL expression part of your property, but you'll need to
accept that you can't simply use your currency_exchange_rate_lookup
dictionary as it is. Perhaps if you could give an example of a query
you'd like to write using this property, and the sort of SQL you'd
expect to see generated, we might be able to help with the
implementation.
Cheers,
Simon
On Mon, Oct 3, 2016 at 9:51 AM, Jinghui Niu <[email protected]> wrote:
>> 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 <[email protected]> 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 <[email protected]> wrote:
>>>
>>> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu <[email protected]> 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
>>> [email protected].
>>> To post to this group, send email to [email protected].
>>> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
