How to implement SQL level expression for this hybrid property in SQLAlchemy?
<p>I have a ledger table and a corresponding python class.
I defined the model using SQLAlchemy, as follows,</p>

<pre><code>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))
</code></pre>

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

<p>Now if I run a query like this:</p>

<pre><code>&gt;&gt;&gt;db_session.query(Ledger).filter(Ledger.amountInCAD &gt; 1000)
</code></pre>

<p>SQLAlchemy gives me this error:</p>

<pre><code>  File "ledger_db.py", line 43, in amountInCAD
    exchange_rate = cls.currency_exchange_rate_lookup[(currency_name, 'CAD')]
KeyError: (Column('currency', String(), table=&lt;ledger&gt;, nullable=False), 'CAD')
</code></pre>

<p>I've researched SQLAlchemy's online documentation regarding hybrid property. <a href="http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-a-hybrid" rel="nofollow">http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-a-hybrid</a>
Comparing my code to the example code, I don't understand why mine doesn't work. If in the official example, <code>cls.firstname</code> can refer to a column of value, why in my code the <code>cls.__table__.c.currency</code> only returns a <code>Column</code> not its value?</p>

<p><code>cls.firstname</code> does not "refer to value", but the <code>Column</code>. <code>cls.firstname + " " + cls.lastname</code> in the <a href="http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-a-hybrid" rel="nofollow">example</a> produces a string concatenation SQL expression along the lines of:
</p>

<pre><code>firstname || ' ' || lastname
</code></pre>

<p>That is part of the magic of hybrid properties: they make it relatively easy to write simple expressions that can work in both domains, but you still have to understand when you're handling a python instance and when building an SQL expression.</p>

<p>You could rethink your own hybrid a bit and actually pass the conversion options to the DB in your <code>case</code> expression:</p>

<pre><code>from sqlalchemy import func

...

@amountInCAD.expression
def amountInCAD(cls):
    # This builds a list of (predicate, expression) tuples for case. The
    # predicates compare each row's `currency` column against the bound
    # `from_` currencies in SQL.
    exchange_rates = [(cls.currency == from_,
                       # Note that this does not call python's round, but
                       # creates an SQL function expression. It also does not
                       # perform a multiplication, but produces an SQL expression
                       # `amount * :rate`. Not quite sure
                       # why you had the Decimal conversion, so kept it.
                       func.round(cls.amount * Decimal(rate), 2))
                      for (from_, to_), rate in
                      cls.currency_exchange_rate_lookup.items()
                      # Include only conversions to 'CAD'
                      if to_ == 'CAD']
    return case(exchange_rates +  [
        # The default for 'CAD'
        (cls.currency == 'CAD', cls.amount),
    ])
</code></pre>

<p>This way you effectively pass your exchange rate lookup as a <code>CASE</code> expression to SQL.</p>

python sqlalchemy descriptor