python - 有向图-SQLAlchemy ORM

我试图通过SQLAlchemy创建一个有向图（带有向后引用）；如何获得下面列出的所需输出？ [请参阅标题为“所需输出”的部分]



示例代码：

example_db.py

from sqlalchemy import Column, Integer, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

node_connector = Table('node_connector', Base.metadata,
    Column('from_node_handle', Integer, ForeignKey('node.handle')),
    Column('to_node_handle', Integer, ForeignKey('node.handle')))


class Node(Base):
    __tablename__ = 'node'
    handle = Column(Integer, nullable=False, primary_key=True)
    next_nodes = relationship('Node',
        secondary=node_connector,
        foreign_keys=[node_connector.c.to_node_handle],
        backref=backref("previous_nodes",
            foreign_keys=[node_connector.c.from_node_handle]))

测试/example_db_test.py

正在执行的模块

from sqlalchemy import create_engine, event
from sqlalchemy.engine import Engine
from sqlalchemy.orm import sessionmaker
import unittest

from ..example_db import Base, Node

class TestNodeDB(unittest.TestCase):

    def setUp(self):
        engine = create_engine('sqlite:///:memory:', echo=False)
        SessionMaker = sessionmaker(bind=engine)
        self.__session = SessionMaker()
        Base.metadata.create_all(engine)

    def test_node_creation(self):
        node_a = Node()
        node_b = Node()
        node_c = Node()
        self.__session.add(node_a)
        self.__session.add(node_b)
        self.__session.add(node_c)
        node_a.next_nodes.append(node_b)
        node_b.next_nodes.append(node_c)
        self.__session.commit()

        nodes = self.__session.query(Node)

        for node in nodes:
            print "Node handle " + str(node.handle) + ":"
            for next_node in node.next_nodes:
                print "\t next node handle " + str(next_node.handle)
            for previous_node in node.previous_nodes:
                print "\t previous node handle " + str(previous_node.handle)



if __name__ == "__main__":
    unittest.main()

输出量

实际产量

Node handle 1:
Node handle 2:
     next node handle 2
     previous node handle 2
Node handle 3:
     next node handle 3
     previous node handle 3

期望的输出

Node handle 1:
     next node handle 2
Node handle 2:
     next node handle 3
     previous node handle 1
Node handle 3:
     previous node handle 2

如何获得所需的输出？

谢谢！

最佳答案

SQLAlchemy甚至对此有文档：Self-Referential Many-to-Many Relationship，涵盖了您想要的内容。基本上，您几乎完全正确，只是使用foreign_keys和primaryjoin而不是使用secondaryjoin设置：

next_nodes = relationship('Node',
    secondary=node_connector,
    primaryjoin=handle==node_connector.c.to_node_handle,
    secondaryjoin=handle==node_connector.c.from_node_handle,
    backref="previous_nodes",
    )

现在您的测试工作了。一个额外的细节：您不需要添加node_b和node_c：它们是通过casacde添加的：如果会话中有node_a，并且您向配置的关系中添加了内容，那么它们也将被添加。同样，您也不需要commit：查询会话数字时，必须先刷新然后查询。

另外请注意，您需要在这里注意加载策略。以Configuring Self-Referential Eager Loading上的示例为例，找出适合您的方法。

关于python - 有向图-SQLAlchemy ORM，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18566888/