一对多

以父子关系为例，在Child中新增一个ForeignKey外键，在Parent表中新增relationship，通过Parent.children得到多个Child的结果

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

SqlAlchemy会自动识别Parent.children为集合，Child.parent为属性。 使用relationship.backref可以简化上诉写法，只用写在Parent中，Child会动态增加parent属性

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

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

一对一

与多对一的场景相似，主要通过relationship.uselist=False来实现

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

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

有ForeiggnKey的类，relationship默认为类添加属性，不需要设置uselist relationship.backref的写法如下：

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

多对多

多对多需要使用中间表进行关联

association_table = Table('parent_child_association', Base.metadata,
    Column('parent_id', ForeignKey('parent.id'), primary_key=True),
    Column('child_id', ForeignKey('child.id'), primary_key=True)
)

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

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

relationship.backref的写法如下：

association_table = Table('parent_child_association', Base.metadata,
    Column('parent_id', ForeignKey('parent.id'), primary_key=True),
    Column('child_id', ForeignKey('child.id'), primary_key=True)
)

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

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