如何在SQLAlchemy中声明对称的自引用多对多关系来存储有向图

Question

from sqlalchemy import DateTime, Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
__all__ = [ "GraphNode" ]
Base = declarative_base()
graph_edges = Table(
'graph_edges', Base.metadata,
Column('from_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True),
Column('to_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True)
)

class GraphNode(Base):
__tablename__ = 'graph_nodes'
id = Column(Integer, primary_key=True, autoincrement=False)
node_data = Column(String(50), nullable=False)
from_me = relationship("GraphNode", secondary=graph_edges,
primaryjoin=id==graph_edges.c.from_node_id,
secondaryjoin=id==graph_edges.c.to_node_id)
to_me = relationship("GraphNode", secondary=graph_edges,
primaryjoin=id==graph_edges.c.to_node_id,
secondaryjoin=id==graph_edges.c.from_node_id)

当我这样做的时候，我从SQLAlchemy:得到了这个错误

SAWarning: relationship 'GraphNode.to_me' will copy column graph_nodes.id to column graph_edges.from_node_id, which conflicts with relationship(s): 'GraphNode.from_me' (copies graph_nodes.id to graph_edges.from_node_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="from_me"' to the 'GraphNode.to_me' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)

听起来SQLAlchemy注意到，将某些内容放入一个节点的from_me列表将导致其他节点的to_me列表发生更改。这是所需的行为。我希望能够前后遍历链接。

有没有什么方法可以在不让SQLAlchemy抱怨我的情况下正确地做到这一点？

Answer 1

以下是解决问题的方法。解决方案在错误消息中，我不完全确定它为什么有效。但是，它是：

from sqlalchemy import DateTime, Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
__all__ = [ "GraphNode" ]
Base = declarative_base()
graph_edges = Table(
'graph_edges', Base.metadata,
Column('from_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True),
Column('to_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True)
)

class GraphNode(Base):
__tablename__ = 'graph_nodes'
id = Column(Integer, primary_key=True, autoincrement=False)
node_data = Column(String(50), nullable=False)
from_me = relationship("GraphNode", secondary=graph_edges,
primaryjoin=id==graph_edges.c.from_node_id,
secondaryjoin=id==graph_edges.c.to_node_id,
back_populates="to_me")
to_me = relationship("GraphNode", secondary=graph_edges,
primaryjoin=id==graph_edges.c.to_node_id,
secondaryjoin=id==graph_edges.c.from_node_id,
back_populates="from_me")

基本上，我为每个关系添加了back_populates参数。它达到了我想要的效果。

我真希望我能更好地理解发生了什么。我觉得我更像是在遵循食谱，而不是从食材开始，决定用它们做什么样的饭。当我编程的时候，我总是讨厌这样做，因为我不明白什么是关键的，什么不是关键的，我会做出一个非常重要的微小改变。