This really helps. Thank you Simon! I still have a couple of smaller
questions.
When you access <class>.fullname, the "self" parameter is now the
> *class*, so self.firstname and self.lastname are SQLAlchemy column
> properties.
Here by *column properties* do you mean the object returned by
column_property() function? Are they used interchangeably with
*InstrumentedAttribute object*?
but you'll need to accept that you can't simply use your
> currency_exchange_rate_lookup
> dictionary as it is.
I have a dream, that one day SQL side and Python side can truly mingle in
such a way that when you query SQL you can directly refer to variables
defined in the Python model class:-)
I will read on the sql expression part on the docs. I've finished reading
Essential Sqlalchemy 2nd edition, but there seems to be a big gap between
beginner level and pro level in terms of available reading materials.
Really looking forward to some new books releasing soon.
Thanks Simon, cheers!
On Mon, Oct 3, 2016 at 2:32 AM, Simon King <si...@simonking.org.uk> wrote:
> 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 <niujing...@gmail.com> 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 <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_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.
>
> --
> 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.
