Many times when building applications, it would be nice to have our models return back already computed data. This is especially handy if a subquery is required to get that value. Let’s start by using the same setup we did in last weeks article.

pip install sqlalchemy
pip install pysqlite

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory', echo=True)
from sqlalchemy.ext.declarative import declarative_base

So here is where we need to import the hybrid_property from the SQLAlchemy hybrid extension. This is included by default when a pip install is done. This extension does far more than I’m showing here, and is a great way to move more logic into your models.

from sqlalchemy.ext.hybrid import hybrid_property

Let’s continue with the rest of the basics and setup our model.

Base = declarative_base()
from sqlalchemy import Column, Integer, String, Float
class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True)
    order_reference_code = Column(String)
    merchandise_cost = Column(Float)
    tax = Column(Float)
    shipping = Column(Float)
    
    def __init__(self, order_reference_code, merchandise_cost, tax, shipping):
        self.order_reference_code = order_reference_code
        self.merchandise_cost = merchandise_cost
        self.tax = tax
        self.shipping = shipping

Now we’re ready to define our hybrid property. In this example, I want the property to return a grand total of the cost components in the order.

    @hybrid_property
    def grand_total(self):
        rollup_fields = [
        'merchandise_cost',
        'tax',
        'shipping',
        ]
        
        total = sum([self.__getattribute__(x) for x in rollup_fields])
        return round(total, 2)

Once we’ve defined our hybrid property, let’s create the database and play with it a bit.

Base.metadata.create_all(engine)

order1 = Order('ABC', 57.50, 11.75, 6.00)
order2 = Order('BCD', 21.42, 4.72, 3.00)
order3 = Order('CDE', 1000.50, 80.34, 0.00)
order4 = Order('DEF', 500.50, 45.34, 100.00)

We’ve got some test data setup so let’s make a database session, and add our data to the database.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
db = Session()

db.add(order1)
db.add(order1)
db.add(order1)
db.add(order1)
db.commit()

Okay finally, let’s use our hybrid property in a simple query. Noticed that the property name defined above is exactly how I access that data.

for order in db.query(Order).all():
    print order.order_reference_code, order.grand_total

Here is our result.

ABC 75.25
BCD 29.14
CDE 1080.84
DEF 645.84

A full gist of the code is available here.


comments powered by Disqus