How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

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?

[Simon King]

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

[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

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[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?

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

>>> sqlalchemy+...@googlegroups.com.

>>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Jinghui Niu]

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!

>>> sqlalchemy+unsubscribe@googlegroups.com.

>>> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Simon King]

On Mon, Oct 3, 2016 at 11:43 AM, Jinghui Niu <niuji...@gmail.com> wrote:
> 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*?
>

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