在 SQLAlchemy 中定义（或模拟）包含不同类型的集合

在使用 SQLAlchemy 的项目中，尝试使用一个我认为是组合模式的东西。有一个 “owner” 对象的类；将一些功能封装在组件类中，并通过为所有者分配组件来为所有者提供不同的功能。所有者和组件都具有需要序列化的状态，因此它们都是 SQLAlchemy 对象。这里有一个简单的示例：

class Employee(DeclarativeBase):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)

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


class SalesRole(DeclarativeBase):
    __tablename__ = 'sales_role'
    id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'))
    employee = relationship(
        'Employee',
        backref=backref('sales_role', uselist=False)
    )

    def __init__(self, employee):
        self.employee = employee
        self.total_sales = 0

    def __repr__(self):
        return "<SalesRole(employee='%s')>" % self.employee.name

    # Sales-specific data and behavior
    total_sales = Column(Float)


class CustomerSupportRole(DeclarativeBase):
    __tablename__ = 'support_role'
    id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'))
    employee = relationship(
        'Employee',
        backref=backref('support_role', uselist=False)
    )

    def __init__(self, employee):
        self.employee = employee
        self.tickets_resolved = 0

    def __repr__(self):
        return "<CustomerSupportRole(employee='%s')>" % self.employee.name

    # Support-specific data and behavior
    tickets_resolved = Column(Integer)

希望能够在 owner 类上定义一个属性，该属性返回一个集合（任何类型），其中包含已分配给 owner 的所有此类组件，即：

# Define an Employee object bob and assign it some roles
>>> bob.roles
[<SalesRole(employee='Bob')>, <CustomerSupportRole(employee='Bob')>]

希望完成此操作，而不硬编码任何对组件类型存在的引用的代码——希望在不更改任何其他地方的代码的情况下定义新组件。

2、解决方案

class Employee(DeclarativeBase):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    components = relationship(
        'Component',
        secondary='employee_component_association',
        back_populates='employees'
    )

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


class Component(DeclarativeBase):
    __tablename__ = 'component'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    employees = relationship(
        'Employee',
        secondary='employee_component_association',
        back_populates='components'
    )

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


class EmployeeComponentAssociation(DeclarativeBase):
    __tablename__ = 'employee_component_association'
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    component_id = Column(Integer, ForeignKey('component.id'), primary_key=True)

    def __init__(self, employee, component):
        self.employee = employee
        self.component = component


# Define an Employee object bob and assign it some roles
bob = Employee('Bob')
sales_role = Component('Sales')
support_role = Component('Support')
bob.components.append(sales_role)
bob.components.append(support_role)

# Print the components associated with bob
for component in bob.components:
    print(component.name)