SQLAlchemy_定义(一对一/一对多/多对多)关系

Basic Relationship Patterns

基本关系模式 
The imports used for each of the following sections is as follows: 
下列的 import 语句，应用到接下来所有的代章节中：

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

Base = declarative_base()

One To Many

A one to many relationship places a foreign key on the child table referencing the parent. 
表示一对多的关系时，在子表类中通过 foreign key (外键)引用父表类。 
relationship() is then specified on the parent, as referencing a collection of items represented by the child: 
然后，在父表类中通过 relationship() 方法来引用子表的类：

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")
   # 在父表类中通过 relationship() 方法来引用子表的类集合

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    # 在子表类中通过 foreign key (外键)引用父表的参考字段

 

To establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one, 
在一对多的关系中建立双向的关系，这样的话在对方看来这就是一个多对一的关系， 
specify an additional relationship() and connect the two using the relationship.back_populates parameter: 
在子表类中附加一个 relationship() 方法，并且在双方的 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")
    # 子表类中附加一个 relationship() 方法
    # 并且在(父)子表类的 relationship() 方法中使用 relationship.back_populates 参数

 

Child will get a parent attribute with many-to-one semantics. 
这样的话子表将会在多对一的关系中获得父表的属性

Alternatively, the backref option may be used on a single relationship() instead of using back_populates: 
或者，可以在单一的 relationship() 方法中使用 backref 参数来代替 back_populates 参数：

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

 

One To One

One To One is essentially a bidirectional relationship with a scalar attribute on both sides. 
一对一是两张表之间本质上的双向关系。 
To achieve this, the uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship. 
要做到这一点，只需要在一对多关系基础上的父表中使用 uselist 参数来表示。 
To convert one-to-many into one-to-one:

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

 

To convert many-to-one into one-to-one:

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

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

 

Many To Many

Many to Many adds an association table between two classes. 
多对多关系会在两个类之间增加一个关联的表。 
The association table is indicated by the secondary argument to relationship(). 
这个关联的表在 relationship() 方法中通过 secondary 参数来表示。 
Usually, the Table uses the MetaData object associated with the declarative base class, 
通常的，这个表会通过 MetaData 对象来与声明基类关联， 
so that the ForeignKey directives can locate the remote tables with which to link: 
所以这个 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)
    # 在父表中的 relationship() 方法传入 secondary 参数，其值为关联表的表名

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

 

For a bidirectional relationship, both sides of the relationship contain a collection. 
双向关系中，两个表类都会包含这个集合。 
Specify using relationship.back_populates, and for each relationship() specify the common association table: 
指定使用 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")

 

When using the backref parameter instead of relationship.back_populates, the backref will automatically use the same secondary argument for the reverse relationship: 
当在父表类的 relationship() 方法中使用 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)

 

The secondary argument of relationship() also accepts a callable that returns the ultimate argument, 
secondary 参数还能够接收一个可调函数的最终返回值， 
which is evaluated only when mappers are first used. Using this, we can define the association_table at a later point, as long as it’s available to the callable after all module initialization is complete:

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

 

With the declarative extension in use, the traditional “string name of the table” is accepted as well, matching the name of the table as stored in Base.metadata.tables:

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