具有多对象层次结构的SQLalchemy

Question

我正试图在Pyramid框架中使用SQLAlchemy构建对象的层次结构。我已经设置了一个工作层次结构——目前，一个C对象的父对象是B，而它的父对象则是a。

但我需要对其进行更改，以便模型B可以将A、B或C作为其父级，等等。我尝试使用关联表，但其中使用的外键也只链接到一种类型的对象。我还想保留当前的"子对象"one_answers"父对象"关系属性。

这是我当前的models.py文件：

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
class Root(Base):
    __tablename__ = 'Root'
    ID = Column(Integer, primary_key=True)
    Children = relationship("A",
                        backref='Parent',
                        cascade="all, delete, delete-orphan")

class A(Base):
    def getID():
        return uuid.uuid1().hex
    __tablename__ = 'A'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Integer, ForeignKey('Root.ID'))
    Children = relationship("B",
                            backref='Parent',
                            cascade="all, delete, delete-orphan")
class B(Base):
    def getID():
        return uuid.uuid1().hex
    __tablename__ = 'B'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Integer, ForeignKey('A.ID'))
                      cascade="all, delete, delete-orphan")
    Children = relationship("C",
                            backref='Parent',
                            cascade="all, delete, delete-orphan")

class C(Base):
    def getID():
        return uuid.uuid1().hex
    __tablename__ = 'C'
    ID = Column(Text, primary_key=True, default=getID)
    Name = Column(Text)
    ParentID = Column(Integer, ForeignKey('B.ID'))
    Children = []

因此，我的最终目标是建立一个层次结构，在这个层次结构中，任何节点都可以有任何数量的a、B、C类型的子节点。

注意：我使用uuid作为主键ID，这样整个层次结构中的每个ID都是唯一的。

Answer 1

感谢van和使用继承，如果它能帮助其他人，这里是我的解决方案：

from sqlalchemy import (
    Column,
    Integer,
    Text,
    ForeignKey,
)
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    relationship,
    backref,
)
import uuid
from sqlalchemy.ext.declarative import declarative_base
from zope.sqlalchemy import ZopeTransactionExtension
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
class Node(Base):
    """
    An object representing a node in the hierarchy.
    All the other objects inherit from Node.
    """
    def getID():
        return uuid.uuid1().hex
    __tablename__ = 'Node'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Text, ForeignKey('Node.ID'))
    type = Column(Text(50))
    Children = relationship("Node",
                backref=backref('Parent', remote_side=[ID], uselist=False)
            )
    __mapper_args__ = {
        'polymorphic_identity':'Node',
        'polymorphic_on':type
    }

 class A(Node):
    __tablename__ = 'A'
    ID = Column(Text, ForeignKey('Node.ID'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'A',
    }
class B(Node):
    __tablename__ = 'B'
    ID = Column(Text, ForeignKey('Node.ID'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'B',
    }
class C(Node):
    __tablename__ = 'C'
    ID = Column(Text, ForeignKey('Node.ID'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'C',
    }