Python---Flask--07--SQLAlchemy基本关系

1、一对一

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")

2、一对多

一对多关系将外键放在引用父对象的子表上。 relationship()然后在父项上指定，作为引用子项表示的项集合：

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

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

　　

要在一对多中建立双向关系，其中“反向”侧是多对一，请指定一个附加relationship()并使用relationship.back_populates参数连接两者：

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")

Child将获得parent具有多对一语义的属性。

或者，该backref选项可用于单个relationship()而不是使用 back_populates：

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent")

3、多对一

多对一将外键放在引用该子对象的父表中。 relationship()在父级上声明，将创建一个新的标量持有属性：

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

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

　　通过添加relationship() 并relationship.back_populates在两个方向上应用参数来实现双向行为：

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

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parents = relationship("Parent", back_populates="child")

　或者，backref参数可以应用于单个relationship()，例如Parent.child：

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref="parents")　

4、多对多

Many to Many在两个类之间添加了一个关联表。关联表由secondary参数表示 relationship()。通常，Table使用MetaData 与声明性基类关联的对象，以便ForeignKey 指令可以定位要与之链接的远程表：

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)

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

对于双向关系，关系的两侧都包含一个集合。指定using relationship.back_populates，并为每个relationship()指定公共关联表：

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")

　　

当使用backref参数代替时 relationship.back_populates，backref会自动使用相同的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,
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

 

5、自联表

自关联最常见就是评论盖楼和地址的三级联动了，下面一评论盖楼为例：
一个评论下面有很多追加的评论，这是可以通过记录父评论的id就可以一环扣一环形成完整了评论楼层

class Category(db.Model):
    """商品分类表"""
    __tablename__ = 'category'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    parent = db.relationship('Category', remote_side=[id]) #自关联
    leavel = db.Column(db.SmallInteger, default=1, comment='级别')
    state = db.Column(db.SmallInteger, default=1, comment='状态 1--有效 0--无效')

    def __repr__(self):
        return "<Category %r>" % self.name