In the article “Objectifying our database with SQLAlchemy ORM” we’ve touched very briefly on the Hybrid Declarative approach, as the primary focus of this article series is on the Declarative Approach, and one of the main use-cases for using the Hybrid Declarative Approach is actually to use the __table__ property to assign an Imperative model to a Declarative model.

Ill show the same example we’ve used in that article here to refresh your memory

from sqlalchemy import MetaData, create_engine, Table, Column, Integer, String
from sqlalchemy.orm import DeclarativeBase

engine = create_engine('sqlite:///:memory:', echo=True)

metadata_obj = MetaData()

flasks_table = Table(
	"flasks",
  metadata_obj,
  Column('id', Integer, primary_key=True),
  Column('type', String(100)),
  Column('volume', Integer),
)

class Base(DeclarativeBase):
  pass

class Flask(Base):
  __table__ = flasks_table

metadata_obj.create_all(engine)

This straightforward example is a great starting point. It might already be satisfactory for many use cases, such as migrating imperative models or just creating wrappers to be used by systems that expect declarative models to be used in specific places.

But that's not all, folks.

The hybrid declarative approach is actually much more powerful, since it can also accept any FromClause as a __table__ property, which means that technically, you can create declarative models from select statements, which provides a way of creating programmatic “views” directly from SQLAlchemy.

Let's take a look at a basic example. We will begin by creating Declarative tables for the subquery that we will be using

from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, Session

class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50))
  academy: Mapped[str] = mapped_column(String(50))

class Transmutation(Base):
  __tablename__ = 'transmutations'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  alchemist_id: Mapped[int] = mapped_column(ForeignKey('alchemists.id'), nullable=False)
  technique_name: Mapped[str] = mapped_column(String(50))
  potency_level: Mapped[int]

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

# Add sample data
with Session(engine) as session:
  alchemists = [
      Alchemist(name="Aurelius Flameheart", academy="Chrysopoeia Academy"),
      Alchemist(name="Mercuria Quicksilver", academy="Chrysopoeia Academy"), 
      Alchemist(name="Sulfurion the Wise", academy="Azoth Collegium")
  ]
  session.add_all(alchemists)
  session.flush()
  
  transmutations = [
      Transmutation(alchemist_id=1, technique_name="Solve et Coagula", potency_level=9),
      Transmutation(alchemist_id=1, technique_name="Nigredo Process", potency_level=6), 
      Transmutation(alchemist_id=2, technique_name="Aqua Vitae", potency_level=10),
      Transmutation(alchemist_id=2, technique_name="Sublimation Wave", potency_level=8),
      Transmutation(alchemist_id=3, technique_name="Calcination Burst", potency_level=7)
  ]
  session.add_all(transmutations)
  session.commit()

Moving on to Hybrid Declarative

Now that we have two database tables and some data pushed to our database, we can construct a subquery to be used as a __table__ property.

from sqlalchemy import select, func
from typing import Optional

academy_mastery_query = select(
  Alchemist.academy.label('academy'),
  func.count(Alchemist.id).label('adept_count'),
  func.group_concat(Alchemist.name).label('master_names')
).group_by(Alchemist.academy).subquery()

class AcademyMastery(Base):
  __table__ = academy_mastery_query
  
  # Type hints
  academy: Mapped[str]
  adept_count: Mapped[int] 
  master_names: Mapped[Optional[str]]
  
  # Specify primary key
  __mapper_args__ = {
      'primary_key': [academy_mastery_query.c.academy]
  }

with Session(engine) as session:
  print("--- Academy Mastery Overview ---")
  for mastery in session.execute(select(AcademyMastery)).scalars().all():
      print(f"{mastery.academy}:")
      print(f"\tMaster Alchemists: {mastery.master_names}\n")

Since __table__ expects either a Table or a FromClause we need to mark our SELECT as a SUBQUERY using the .subquery() chained function, which will make it possible for the query constructor to construct a FROM statement that SELECTs from a SUBQUERY.

We also include all of the fields from the statement as Mapped annotated properties, which is actually all we can do for these column as far as type hinting goes, note that it is not required, and in the case that you do not define these column properties you can still access the fields using the imperative table_name.c.column_name syntax.

Since the __table__ property is used, we cannot assign mapped_column to any of our fields, this is a limitation of SQLAlchemy and you will actually get an error if you try to do so.

This limitation is actually the reason we use the __mapper_args__ property to define our primary_key for the table, which is also required by SQLAlchemy, in the case that your tables do not define a primary_key for an SQLAlchemy model, you will get an error saying that it’s missing.

What about relationships?

Since we’re basically using the __table__ property as the from clause, technically everything else about declarative tables should be working as if we had an actual table behind our model.

This means that we can create things like relationships, as we will see in the following example

from sqlalchemy.orm import relationship, selectinload

alchemist_prowess_query = select(
  Alchemist.id,
  Alchemist.name,
  Alchemist.academy,
  func.count(Transmutation.id).label('total_techniques'),
  func.avg(Transmutation.potency_level).label('average_potency'),
  func.max(Transmutation.potency_level).label('peak_mastery')
).join(Transmutation).group_by(Alchemist.id, Alchemist.name).subquery()

class AlchemistProwess(Base):
  __table__ = alchemist_prowess_query
  
  id: Mapped[int]
  name: Mapped[str]
  academy: Mapped[str] 
  total_techniques: Mapped[int]
  average_potency: Mapped[Optional[float]]
  peak_mastery: Mapped[Optional[int]]
  
  transmutations: Mapped[list[Transmutation]] = relationship()
  
  __mapper_args__ = {
      'primary_key': [alchemist_prowess_query.c.id]
  }

with Session(engine) as session:
  print("--- Alchemist Prowess ---")
  prowess_query = select(AlchemistProwess).options(
      selectinload(AlchemistProwess.transmutations)
  )
  for prowess in session.execute(prowess_query).scalars().all():
      print(f"Alchemist: {prowess.name} (Academy: {prowess.academy})")
      print(f"\tTechniques Mastered: {prowess.total_techniques}")
      print(f"\tPeak Mastery Level: {prowess.peak_mastery}")
      print("\tAll Transmutations:")
      for transmutation in prowess.transmutations:
          print(f"\t\t- {transmutation.technique_name} (Potency: {transmutation.potency_level})")

In this example we begin by selecting Alchemist rows and joining the Transmutation table on them, since our declarative tables have a ForeignKey defined on the Transmutation object we do not need to define an on clause on the join.

When defining the relationship for the Transmutation table, since our primary_key is the same as for the Alchemist table, SQLAlchemy is able to automatically determine the conditions for this relationship.

This is actually how I would actually recommend working with Hybrid Declarative Models, find the root element that you would like to work with, like in our example, the Alchemist table, and use its primary key as the primary_key in your __mapper_args__ .

This method can save time when defining relationships, but the more important reason is that it maintains a behavior closer to native for this model. You can continue to use this model and actually use its primary key to define relationships from and to it, as long as your other models maintain a reference in the form of a FOREIGN KEY to the Alchemist table, creating relationships to this model is a breeze.

Summary

The hybrid declarative approach is a particular feature that is suitable in some situations. The reason it’s so interesting is specifically for cases where your database might have complex queries that you would like to make either reusable or extendable/manipulatable per use case, without having to maintain a query construction function or class to manipulate your queries.

Instead, using SQLAlchemy’s native tools allows us to define and maintain a simple-to-use interface for accessing even the most complex queries. To me, this seems like a perfect example of how powerful SQLAlchemy can be when it comes to database abstraction.

The hybrid declarative approach also partially replaces your need for database views (unmaterialized) if they are solely used in your application layer and not required in the database layer.