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