Sqlalchemy中relationship的理解

"指向"

即定义一张表中的数据条目指向另一张表中的条目，建立这种有向的"指向"可以让表以字段的方式查询到被指向的条目(们)，所以，如果要双向查询，就需要双向指向。

One To Many

在"多"方表中添加"一"方的id作为ForeignKey约束，为查询方便双方均需要定义relationship()字段；

class Parent(Base):__tablename__ = 'parent'id = Column(Integer, primary_key=True)children = relationship("Child", back_populates="parent")class Child(Base):__tablename__ = 'child'id = Column(Integer, primary_key=True)parent_id = Column(Integer, ForeignKey('parent.id'))parent = relationship("Parent", back_populates="children")

One To One

与One To Many区别不大，只要"一"方的relationship方法中添加一个"uselist=False"参数即可，uselist是一个标量属性(a scalar attribute)，其含义是"一"方对应另一张表的条目不使用列表。

class Parent(Base):__tablename__ = 'parent'id = Column(Integer, primary_key=True)child = relationship("Child", uselist=False, back_populates="parent")class Child(Base):__tablename__ = 'child'id = Column(Integer, primary_key=True)parent_id = Column(Integer, ForeignKey('parent.id'))  # 多方才有这个字段parent = relationship("parent", back_populates="child") # 通常也会把backref用函数表示天机uselist=False参数，用以显示指出。 # parent = rlationship("parent", backref=backref("child", uselist=False))

Many To Many

借助中间表完成，利用relationship支持的secondary参数，

association_table = Table('association', Base.metadata,    Column('left_id', Integer, ForeignKey('left.id')),    Column('right_id', Integer, ForeignKey('right.id'))    )    class Parent(Base):    __tablename__ = 'left'    id = Column(Integer, primary_key=True)    children = relationship("Child",                            secondary=association_table,                            back_populates="parents")class Child(Base):    __tablename__ = 'right'    id = Column(Integer, primary_key=True)    parents = relationship("Parent",                            secondary=association_table,                            back_populates="children")

注意，secondary可以接受'a callable that returns the ultimate argument,which is evaluated only when mappers are first used."即接受可执行参数，可以让association_table 在稍晚的时候定义，甚至可以在所有模块都初始化完成后，直到它可调用为止。

class parent(Base):    __tablename__ = "left"    id = Column(Integer, primary_key=True)    children = relationship("Child",                             secondary=lambda: assciation_table,                            backref="parents")

以上relationship参数的表明均可以用类名字符串代替。

删除Mang To Many记录，不必手动删除secondary的中间表数据，数据库会根据"cascade rule"级联规则自动删除。

如果中间表对象需要被调用

class Association(Base):    __tablename__ = 'association'    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)    extra_data = Column(String(50))    child = relationship("Child", back_populates="parents")    parent = relationship("Parent", back_populates="children")class Parent(Base):    __tablename__ = 'left'    id = Column(Integer, primary_key=True)    children = relationship("Association", back_populates="parent")class Child(Base):    __tablename__ = 'right'    id = Column(Integer, primary_key=True)    parents = relationship("Association", back_populates="child")    # create parent, append a child via associationp = Parent()a = Association(extra_data="some data")a.child = Child()p.children.append(a)# iterate through child objects via association, including association# attributesfor assoc in p.children:    print(assoc.extra_data)    print(assoc.child)

# 寻找答案的路途上要保持耐心和专心！

需要注意：back_populates参数赋值参数一定不能是relationship第一个参数的字段，那样相当于被对应关系表中有了重复字段。