[Python中SQLAlchemy一对多关系和自关联关系示例代码] #Python #SQLAlchemy

adjacency_list.py

# coding=utf-8

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

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


class Node(Model):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))

    # SELECT ... FROM node WHERE ? = node.parent_id
    children = relationship(
        'Node',
        back_populates='parent',
        cascade='all',
        # foreign_keys=[parent_id]  # not required
    )
    # SELECT ... FROM node WHERE node.id = ?
    parent = relationship('Node', back_populates='children', remote_side=[id])

    # 等价于
    # children = relationship(
    #     "Node",
    #     backref=backref('parent', remote_side=[id]),
    #     cascade='all',
    #     # lazy='dynamic',
    # )

    def __repr__(self):
        return '<Node {}>'.format(self.id)


if __name__ == "__main__":
    Model.metadata.drop_all()
    Model.metadata.create_all()

    node1 = Node()
    node2 = Node()
    node3 = Node()
    node4 = Node()
    node5 = Node()

    node1.children = [node2, node3]
    node2.children = [node4, node5]

    session.add(node1)
    session.add(node2)

    session.commit()

    # SELECT ... FROM node LIMIT 1 OFFSET 0
    temp = session.query(Node).first()
    # SELECT ... FROM node WHERE 1 = node.parent_id
    print(temp.children)    # print [<Node 2>, <Node 3>]
    # SELECT ... FROM node WHERE 2 = node.parent_id
    print(temp.children[0].children)    # print [<Node 4>, <Node 5>]

    session.commit()  # 结束一次事务

    # SELECT ... FROM node WHERE node.id = 5 LIMIT 1 OFFSET 0
    temp_node5 = session.query(Node).filter_by(id=5).first()
    # SELECT ... FROM node WHERE node.id = 2
    print(temp_node5.parent)  # print <Node 2>

one2many.py

# coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship

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


class Child(Model):
    __tablename__ = 'child'
    id = Column(Integer(), primary_key=True)

    parent_id = Column(Integer(), ForeignKey('parent.id'))

    # SELECT ... FROM parent WHERE parent.id = ?
    parent = relationship(
        'Parent',
        back_populates='children',
        # foreign_keys=[parent_id]  # not required
    )

    def __repr__(self):
        return '<Child {}>'.format(self.id)


class Parent(Model):
    __tablename__ = 'parent'
    id = Column(Integer(), primary_key=True)

    # SELECT ... FROM child WHERE ? = child.parent_id
    children = relationship(
        'Child',
        back_populates='parent',
        cascade="all, delete-orphan",
        # foreign_keys=[Child.parent_id]  # not required
    )

    def __repr__(self):
        return '<Parent {}>'.format(self.id)


if __name__ == "__main__":
    Model.metadata.drop_all()
    Model.metadata.create_all()

    child1 = Child()
    child2 = Child()

    parent = Parent()
    parent.children = [child1, child2]

    session.add(parent)
    session.commit()

    temp = session.query(Parent).first()
    # SELECT ... FROM child WHERE 1 = child.parent_id
    print(temp.children)  # print [<Child 1>, <Child 2>]
    session.commit()    # 结束一次事务

    # SELECT ... FROM child LIMIT 1 OFFSET 0
    temp_child = session.query(Child).first()
    # SELECT ... FROM parent WHERE parent.id = 1
    print(temp_child.parent)    # <Parent 1>