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