How to implement SQL level expression for this hybrid property in SQLAlchemy?

Question

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.currency only returns a Column not its value?

Ilja Everilä · Accepted Answer · 2016-10-03 21:43:11Z

1

cls.firstname does not "refer to value", but the Column. cls.firstname + " " + cls.lastname in the example produces a string concatenation SQL expression along the lines of:

firstname || ' ' || lastname

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.

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

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),
    ])

This way you effectively pass your exchange rate lookup as a CASE expression to SQL.

edited Oct 3, 2016 at 21:43

answered Oct 3, 2016 at 21:34

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jinghui Niu Over a year ago

@llja Everilä thanks. By the way, how can I use Decimal inside a SQL expression? I need to ensure that it is a decimal not a float.

Ilja Everilä Over a year ago

Well of course the value should be Decimal to begin with. If you had floats before, then you might already have an incorrect value: Decimal(0.3) -> Decimal('0.299999999...'). Your DB column should also use a fixed precision type or the like. Your DB-API should handle the python Decimal type correctly as is, if you just give one as a bind param, but do check. DB-API is what SQLAlchemy uses underneath, for example psycopg2 (common) for Postgresql.

Jinghui Niu Over a year ago

@llja Everilä I'm using SQLite, which unfortunately does not support Decimal in the database backend. Could you please suggest a workaround? Only in SQL level. I've already used a decorated type for the python level. Thanks.

Ilja Everilä Over a year ago

Ah sorry, entirely already forgot you're using SQLite. As you've noted SQLite does not support fixed precision numeric values and as such any mathematical operations on them. Even though you're passing a Decimal as the bound param, depending on your type affinity and order of conversion preference it'll use either INTEGER or REAL as the storage class.

Ilja Everilä Over a year ago

In order to retain "fixed precision" you could perhaps use integer cents or such and do the required math and conversions on them yourself, but that will be a daunting task, but not impossible.

Collectives™ on Stack Overflow

How to implement SQL level expression for this hybrid property in SQLAlchemy?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related