Combining polymorphic inheritance with many-to-many with SQLAlchemy

background

There is a table with a similar structure, and I made it a DRY structure by utilizing polymorphism = polymorphism. From each table, it became necessary to have a many-to-many relationship with the same separate table.

Review 1

SQLAlchemy can use three types of polymorphism depending on the table structure. The number of tables assuming that there is one base class and two derived classes is also described.

--Single Table Inheritance Put all derived tables in one table. The number of tables is 1. --Joint Table Inheritance The base table and the derived table are separate. The number of tables is 3. --Concrete Table Inheritance Do not have a base class in the table, but have a derived class in each table. The number of tables is 2.

This time, we adopted Concrete Table Inheritance because of the external database.

Review 2

SQLAlchemy can handle many-to-many without being aware of intermediate tables. Quoted from the sample in the official manual. A pattern in which a blog article has multiple keywords.

post.keywords.append(Keyword('wendy'))

So how do you do it

Suppose each form with similar configuration has multiple tags. There are four tables: tags, each form x 2, and an intermediate table.

# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.ext.declarative import *
from sqlalchemy.orm import *
from sqlalchemy_utils import *

base = declarative_base()

#The intermediate table does not have to be a class.
assoc = Table('assoc', base.metadata,
  Column('aform_id', Integer, ForeignKey('AForm.id')),
  Column('bform_id', Integer, ForeignKey('BForm.id')),
  Column('tag_id', Integer, ForeignKey('Tag.id'))
)

class Tag(base):
  __tablename__ = 'Tag'
  id = Column(Integer, primary_key = True)
  name = Column(String)
  #Even if you do not use it directly, an error will occur without this definition.
  #By specifying backref, the opposite[AB]The relationship definition of Form can be omitted.
  aform = relationship('AForm', secondary = assoc, backref = 'atag')
  bform = relationship('BForm', secondary = assoc, backref = 'btag')

class Form(AbstractConcreteBase, base):
  id = Column(Integer, primary_key = True)
  amount = Column(Integer)

  @declared_attr
  def __tablename__(cls):
    return cls.__name__
  
  @declared_attr
  def __mapper_args__(cls):
    return {
      'polymorphic_identity': cls.__name__,
      'concrete':True
  }

  
class AForm(Form, base):
  pass

class BForm(Form, base):
  b_only = Column(String(10))


db_uri = 'sqlite:////tmp/m2m.sqlite'
engine = create_engine(db_uri, echo =True)

if database_exists(engine.url):
  drop_database(db_uri)
  create_database(engine.url)
base.metadata.create_all(bind = engine)

Session = sessionmaker(bind = engine)
session = Session()

#Since atag is defined in AForm and btag is defined in BForm, it is necessary to call them separately.
a = AForm(amount = 100)
atag = Tag(name = 'booked')
a.atag.append(atag)
session.add(a)

f = BForm(amount = 200)
tag = Tag(name = 'canceled')
f.btag.append(tag)
session.add(f)
session.commit()

#It is also possible to call them dynamically below.
getattr(btag, f.__class__.__name__.lower()).append(f)

forms=session.query(AForm).all()
for f in forms:
  print(f)
  print(f.atag[0].name)

It is true that you can operate without being aware of the intermediate table, but since atag is defined for AForm and btag is defined for BForm, it is necessary to call them separately. However, it is possible to use getattr to generate a method reference from the object's class name and join it in the method chain.

Repost

getattr(tag, b.__class__.__name__.lower()).append(b)

Confusing! There may be an opinion that it should be written like this. It will depend on your coding style and the number of derived classes.

if isinstance(f, AForm):
  tag.aform.append(f)