Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Simon King]

On Mon, Oct 3, 2016 at 11:43 AM, Jinghui Niu  wrote:
> This really helps. Thank you Simon! I still have a couple of smaller
> questions.
>
>> When you access .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*?
>

In this case they are in fact InstrumentedAttributes. (Actually, I
think properties created using column_property are *also*
InstrumentedAttributes).

>
>> 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:-)
>

In your case, I guess you need to construct some sort of case
statement based on the values in the dictionary. Here's some untested
code:

@amountInCAD.expression
def amountInCAD(cls):
conditions = []
rates = cls.currency_exchange_rate_lookup.items()
for ((from_currency, to_currency), exchange_rate) in rates:
assert to_currency == 'CAD'
conditions.append((cls.currency == from_currency), cls.amount
* exchange_rate))
# You'll need to implement the "ELSE" case yourself
return sa.case(conditions)

Hope that helps,

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.

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

This really helps. Thank you Simon! I still have a couple of smaller
questions.

When you access .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  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 .fullname, the "self" parameter refers to
> the instance, so self.firstname and self.lastname are plain python
> strings.
>
> When you access .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  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 
> 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 
> wrote:
> >>>
> >>> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu 
> wrote:
> >>> > I have 

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Simon King]

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 .fullname, the "self" parameter refers to
the instance, so self.firstname and self.lastname are plain python
strings.

When you access .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  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  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  wrote:
>>>
>>> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu  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 * 

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

>
> 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  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  wrote:
>
>> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu  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=, 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 

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

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  wrote:

> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu  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=, 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.

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Simon King]

On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu  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=, 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.