Sqlalchemy使用关联代理和对象映射表自引用多个

Question

我试图在Flask中构建一个自引用多对多模型。我已经在internet上检查了很多示例，但是在我的例子中，我想使用对象映射表来处理关系。另外，因为我将Flask与棉花糖一起使用，所以我希望在表中包含关联代理，以简化模型的序列化。我发现的每个示例都使用了backref，但为了可读性，我想使用back_populates。目前我不确定这是否可能。 请在下面找到我的最小示例来演示这个问题

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class NodeRelation(Base):
  __tablename__ = "node_relation"
  parent_id = Column(Integer, ForeignKey('node.id'), primary_key=True)
  parent = relationship('Node',
                        primaryjoin="NodeRelation.parent_id == node.c.id",
                        back_populates='parent_childs',
                        #foreign_keys=parent_id
                        )

  child_id = Column(Integer, ForeignKey('node.id'), primary_key=True)
  child = relationship('Node',
                        primaryjoin="NodeRelation.child_id == node.c.id",
                        back_populates='child_parents',
                        #foreign_keys=child_id
                        )

  def __init__(self, parent=None, child=None, **kwargs):
      super().__init__(**kwargs)
      if child:
          self.child = child
      if parent:
          self.parent = parent

  def __repr__(self):
      return "(parent_id: %s, child_id: %s)" % (self.parent_id, self.child_id)


class Node(Base):
  __tablename__ = "node"
  id = Column(Integer, primary_key=True, autoincrement=True)
  title = Column(String())
  parent_childs = relationship('NodeRelation',
                                primaryjoin="Node.id==node_relation.c.parent_id",
                                back_populates='parent',
                                cascade='all, delete',
                                #foreign_keys=NodeRelation.parent_id
                                )
  parents = association_proxy('parent_childs', 'parent',
                            creator=lambda parent: NodeRelation(parent=parent))

  child_parents = relationship('NodeRelation',
                                primaryjoin="Node.id==node_relation.c.child_id",
                                back_populates='child',
                                cascade='all, delete',
                                #foreign_keys=NodeRelation.child_id
                                )
  childs = association_proxy('child_parents', 'child',
                            creator=lambda child: NodeRelation(child=child))

  def __init__(self, title, **kwargs):
    super().__init__(**kwargs)
    self.title = title

  def __repr__(self):
      return "(id: %s, title: %s, childs: %s)" % (self.id, self.title, self.childs)


Base.metadata.create_all(engine)

n1 = Node("First")
n2 = Node("Second")
"""
# This is failing with: NOT NULL constraint failed: node_relation.parent_id
n1.childs.append(n2)

session.add(n1)
session.add(n2)
session.commit()
"""
# This one is working
c = NodeRelation(n1, n2)
session.add(n1)
session.add(n2)
session.add(c)

# Node 1 and Node 2 exists
q = session.query(NodeRelation).all()
print(q)

# This is failing with infinite recursion when childs property is displayed.
q2 = session.query(Node).all()
print(q2)

如果我使用n1.childs.append()，我会得到一个空contsraint错误。如果我直接用n1和n2构造映射器对象，它工作得很好，但是一旦我访问了childs属性，我就得到了无限递归

编辑：

我发现是自定义创建者lambda导致了附加错误。因此，更新后的代码如下所示：

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class NodeRelation(Base):
  __tablename__ = "node_relation"
  parent_id = Column(Integer, ForeignKey('node.id'), primary_key=True)
  parent = relationship('Node',
                        primaryjoin="NodeRelation.parent_id == node.c.id",
                        back_populates='parent_childs',
                        #foreign_keys=parent_id
                        )

  child_id = Column(Integer, ForeignKey('node.id'), primary_key=True)
  child = relationship('Node',
                        primaryjoin="NodeRelation.child_id == node.c.id",
                        back_populates='child_parents',
                        #foreign_keys=child_id
                        )

  def __init__(self, parent=None, child=None, **kwargs):
      super().__init__(**kwargs)
      if child:
          self.child = child
      if parent:
          self.parent = parent

  def __repr__(self):
      return "(parent_id: %s, child_id: %s)" % (self.parent_id, self.child_id)


class Node(Base):
  __tablename__ = "node"
  id = Column(Integer, primary_key=True, autoincrement=True)
  title = Column(String())
  parent_childs = relationship('NodeRelation',
                                primaryjoin="Node.id==node_relation.c.parent_id",
                                back_populates='parent',
                                cascade='all, delete',
                                #foreign_keys=NodeRelation.parent_id
                                )
  parents = association_proxy('parent_childs', 'parent')

  child_parents = relationship('NodeRelation',
                                primaryjoin="Node.id==node_relation.c.child_id",
                                back_populates='child',
                                cascade='all, delete',
                                #foreign_keys=NodeRelation.child_id
                                )
  childs = association_proxy('child_parents', 'child')

  def __init__(self, title, **kwargs):
    super().__init__(**kwargs)
    self.title = title

  def __repr__(self):
      return "(id: %s, title: %s, childs: %s)" % (self.id, self.title, self.childs)


Base.metadata.create_all(engine)

n1 = Node("First")
n2 = Node("Second")

# This is failing with: NOT NULL constraint failed: node_relation.parent_id
n1.childs.append(n2)

session.add(n1)
session.add(n2)
session.commit()

# Node 1 and Node 2 exists
q = session.query(NodeRelation).all()
print(q)

# This is failing with infinite recursion when childs property is displayed.
q2 = session.query(Node).all()
print(q2)

所以唯一的问题是，当我尝试访问childs属性时，我得到了一个无限递归。我想我把关系搞砸了