Class inheritance in Python is fairly easy, but in SQLAlchemy it can’t be done directly with Python class inheritance and requires a little more setup.

There are a lot of things about this on the Internet. Even the official website does not post the simplest solution, instead, it has a very complex Inheritance Configuration.

First of all, the simplest solution, from StackOverflow, works perfectly and is the most consistent with Python class inheritance.

Reference: SQLAlchemy: Avoiding Multiple Inheritance and Having Abstract Base Class

Positive solution

Here we call this method the __abstract__ method:

Base = declarative_base()

class CommonRoutines(Base):
    __abstract__ = True

    id = Column(Integer, primary_key=True)

    def __init__(self):
        # ...

class Foo(CommonRoutines):
    __tablename__ = 'foo'

    name = Column(...)

    def __init__(self, name):
        super().__init__()
        self.name = name
        # ...

That is, an abstract class that uses __abstract__ = True instead of __tablename__ does everything else just as Python’s built-in class inheritance does.

Class methods and static methods in inheritance

SQLAlchemy’s ORM inheritance, classmethod and staticmethod inheritance is consistent with Python’s OOP object-oriented inheritance scheme.

In other words:

  • Be champions@staticmethodStatic methods are inherited, but when called by a child class, are called by the parent class with the same name.
  • Be champions@classmethodThe method of the subclass is inherited, and when the subclass calls it, the method of the subclass is called.

Foreign keys in inheritance

SQLAlchemy defines an ORM that cannot inherit a Foreign Key from a parent ORM, forcing it to be redefined in a subclass. Hierarchies Mapping Class Inheritance suggests simply searching for the “foreign “keyword with Ctrl-F to see how the hierarchy redefines foreign keys when it comes to Inheritance.

Also see SQLAlchemy Inheritance

class Parent(Base):
    __abstract__ = True

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String)
    age = Column('age', String)
    fk = Column('fk', Integer, ForeignKey('anotherTable.id'), primary_key=True)

class Son(Parent):
    __tablename__ = 'son'

    fk = Column('fk', Integer, ForeignKey('anotherTable.id'), primary_key=True)

Other inheritance schemes

Referring to other people’s solutions, websites’ solutions, can be confusing. To avoid referencing someone else’s stuff, post some solutions here that are not solutions.

Declarative_base (CLS = XX) methods:

class CommonBase(object):
    @classmethod
    def somecommonaction(cls):
        # body here

Base = declarative_base(cls=CommonBase)

class Table1(Base):
    # __tablename__ & Table1 specific fields here

class Table2(Base):
     # __tablename__ & Table2 specific fields here

The downside of this is that it is hard to see the inheritance relationship.

The official __mapper_args__ method:

class Person(Base):
    __tablename__ = 'people'
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __tablename__ = 'engineers'
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    primary_language = Column(String(50))

As you can see, this must define the elusive __mapper_args__ attribute in both parent and child classes. And that’s not all. The website also says that various mappings require different complex Settings. Interested can refer to the website: https://docs.sqlalchemy.org/e…