pynash wordmark

Introductions to Hybrid Properties in SQLAlchemy

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.