Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 37 ยท ORM Mapping

Mixins, Inheritance and Hybrid Properties

Share columns with mixins and abstract bases, map class hierarchies with single-table and joined-table inheritance, and write hybrid properties that work in Python and SQL.

Real models repeat themselves. Almost every table has an id and timestamps. Some entities are variations of a common thing: employees and contractors, credit cards and bank transfers. Python has tools for sharing behaviour, and SQLAlchemy lets you use them for mapped classes without fighting the database.

This lesson covers four techniques: mixins and abstract bases for sharing columns, inheritance mapping for class hierarchies (two main strategies), and hybrid properties for values you want to use both on objects and inside queries.

Mixins and abstract bases

A mixin is an ordinary Python class that declares columns using Mapped/mapped_column. Mapped classes that inherit from it get copies of those columns. For a base that should not become a table itself, mark it __abstract__ = True.

Python A timestamp mixin and an abstract base

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both tables received id, created_at and updated_at, and Entity itself produced no table. The mixin does not inherit from Base; it is just a bag of columns.

Inheritance mapping: three strategies

What if a Manager is an Employee? Databases have no inheritance, so SQLAlchemy offers three layouts:

Strategy Tables Pros Cons
Single table one table for the whole hierarchy, a type column simple, fast queries many nullable columns
Joined table one table per class, joined by primary key normalized, no wasted columns joins on every query
Concrete table one independent table per class no joins harder to query across classes

Single and joined are the two you will use. Both need a discriminator column that says which class a row belongs to, and polymorphic_identity values that identify each subclass.

Single-table inheritance

Python Single-table inheritance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Querying the base class returns instances of the right subclass. Querying a subclass adds a filter on the discriminator, so you only get that type.

Joined-table inheritance

Here each subclass has its own table holding only its extra columns, with a foreign key to the parent table.

Python Joined-table inheritance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Loading a Payment list does not fetch the subclass columns immediately, so the first access to p.last4 would cost another query. with_polymorphic loads everything in one statement.

flowchart TB
    subgraph single["Single table"]
      E["employee<br/>id, name, kind,<br/>team_size, language"]
    end
    subgraph joined["Joined tables"]
      P["payment<br/>id, amount_cents, type"]
      C["card_payment<br/>id FK, last4"]
      T["transfer<br/>id FK, iban"]
      P --- C
      P --- T
    end

Single-table inheritance stores everything in one table; joined-table inheritance splits subclass columns into child tables.

Hybrid properties

Suppose a Rectangle has width and height and you want an area. On an instance, width * height is plain Python. In a query you want WHERE area > 50 to run in the database. A hybrid property gives you both with a single definition.

Python hybrid_property and hybrid_method

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Because the string concatenation uses +, the same code means "join two strings" in Python and first || ' ' || last in SQL. When the Python and SQL versions must differ (for example Python's len() versus SQL's length()), declare a second function with @full_name.expression.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does abstract = True do on a mapped base class?

    An abstract class is not mapped to a table, but subclasses inherit its mapped columns.
  2. 2. Which inheritance strategy uses a single table with a discriminator column?

    Single-table inheritance keeps all subclasses in one table, with a column such as kind telling them apart.
  3. 3. What does polymorphic_identity define?

    Each subclass sets a polymorphic_identity so the ORM can pick the right class for each row.
  4. 4. Why use hybrid_property instead of a plain Python property?

    A hybrid evaluates in Python on instances and as SQL on the class, so filters can use the same logic.

Summary

  • Share columns with plain mixins and __abstract__ bases.
  • Map hierarchies with single-table (one table, discriminator) or joined-table (one table per class) inheritance, using polymorphic_on and polymorphic_identity; use with_polymorphic to load subclass columns in one query.
  • hybrid_property and hybrid_method let the same logic run in Python and in SQL.

Next: One-to-Many and One-to-One Relationships