Sqlalchemy 表的关联关系

基础知识

SQLAlchemy列类型

类型名	Python类型	说明
Integer	int	普通常数，通常是32位
SmallInteger	int	取值范围小的整数，通常是16位
BigInteger	int或long	不限制精度的整数
Float	float	浮点数
Numeric	decimal.Decimal	定点数
String	str	变长字符串
Text	str	变长字符串，对较长或不限长的字符串做了优化
Unicode	unicode	变长unicode字符串
UnicodeText	unicode	变长unicode字符串，对较长或不限长的字符串做了优化
Boolean	bool	布尔类型
Date	datetime.date	日期
Time	datetime.time	时间
DateTime	datetime.datetime	日期和时间
Interval	datetime.timedelta	时间间隔
Enum	str	一组字符串
PickleType	任何python对象	自动使用pickle序列化
LargeBinary	str	二进制blob

常用的SQLAlchemy列选项

选项名	说明
primary_key	如果设为true，列代表的主键
unique	如果设为true，列不允许出现重复值
index	如果设置true，为列创建索引，提申查询效率
nullable	如果设置true，列允许使用空值,如果设为false，列不允许使用空值
default	为列定义默认值

常用的SQLAlchemy关系选项

选项名	说明
backref	在关系的另一个模型中添加反向引用
primaryjoin	明确的指定两个模型之间使用的联结关系；只在模凌两可的关系中需要指定
lazy	指定如何加载相关记录，具体看下表
uselist	如果设为false，不使用列表，而使用标量值
order_by	指定关系中记录的排序方式
secondary	指定多对多关系中关联表的名称
secondaryjoin	SQLAlchemy 无法自行决定时，指定多对对关系中的二级链接关系

relationship 的 lazy 属性指定 sqlalchemy 数据库什么时候加载数据:

参数	说明
select	默认属性,首次访问属性时，应使用单独的SELECT语句或标识映射获取来简单地加载项目以进行简单的多对一引用。
immediate	应该在加载父项时加载项目，使用单独的SELECT语句或标识映射获取来进行简单的多对一引用。
joined	应该使用JOIN或LEFT OUTER JOIN在与父项相同的查询中“热切地”加载项目。 连接是否为“外部”由innerjoin参数确定。
subquery	在父项加载时，应“急切地”加载项目，使用一个额外的SQL语句，该语句为请求的每个集合发出JOIN到原始语句的子查询。
selectin	使用一个或多个其他SQL语句加载父项时，应“急切地”加载项目，这些语句向直接父对象发出JOIN，使用IN子句指定主键标识符。
noload	任何时候都不应该加载。 这是为了支持“只写”属性，或以特定于应用程序的某种方式填充的属性。
raise	不允许懒加载，访问该属性需要预先加载，否则会引发InvalidRequestError，当对象在加载后从其附加的会话中分离时，可以使用此策略。
raise_on_sql	如果懒加载前需要执行sql，访问该属性需要预先加载，否则会引发InvalidRequestError，如果延迟加载可以从标识映射中提取相关值或确定它应该是None，则加载该值。 当对象与附加的Session保持关联时，可以使用此策略，但应阻止其他SELECT语句。
dynamic	返回所有读取的query对象，在迭代之前可以进行进一步的过滤操作

SQLAlchemy的查询过滤器

过滤器	说明
filter()	把过滤器添加到原查询上，返回一个新查询
filter_by()	把等值过滤器添加到原查询上，返回一个新查询
limit()	使用指定的值限制原查询返回的结果数量，返回一个新查询
offset()	偏移原查询返回的结果，返回一个新查询
order_by()	根据指定条件对原查询结果进行排序，返回一个新查询
group_by()	根据指定条件对原查询结果进行分组，返回一个新查询

SQLAlchemy最常用的查询执行方法

方法	说明
all()	以列表形式返回查询的所有结果
first()	返回查询的第一个结果，没有则返回None
first_or_404()	返回查询的第一个结果，没有则返回404错误
get()	返回指定主键对应的行，没有则返回None
get_or_404()	返回指定主键对应的行，没有则返回404错误
count()	返回查询结果的数量
paginate()	返回一个paginate对象，包含指定范围内结果

详细事例

以下内容转载: SQLAlchemy_定义(一对一/一对多/多对多)关系

下列的 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

一: 单向关系的建立（父类可直接使用子类，子类无法直接使用父类）

在子表类中我们通过 foreign key (外键)引用父表类。

在父表类中通过 relationship() 方法来引用子表的类。

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


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

二: 双向关系的建立（父类可直接使用子类，子类也可直接使用父类）

在一对多的关系中建立双向的关系，只需要做如下方法的改动。

在子表类中附加一个 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)
    '''
    子表类中附加一个 relationship() 方法
    并且在(父)子表类的 relationship() 方法中使用 
    relationship.back_populates 参数
    '''
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

或者，可以在单一的 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

一对一是两张表之间本质上的双向关系。

一: 在一对多关系基础上的父表中使用 uselist 参数设置。

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

二: 在对多一关系基础上的子表中使用 uselist 参数设置。

 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)

同样以上配置可使用 backref 配置，如下所示:

from sqlalchemy.orm import backref

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


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

Many To Many

多对多关系建立需要在两个表之间增加一个关联表。 这个关联的表在 relationship() 方法中通过 secondary 参数来表示。 通常的，这个表会通过 MetaData 对象来与声明基类关联， 所以这个 ForeignKey 指令会使用链接来定位到远程的表：

一: 单向关系的建立（父类可直接使用子类，子类无法直接使用父类）

# 多对多关系中的两个表之间的一个关联表
association_table = Table('association', Base.metadata,
    Column('id', Integer, primary_key=True),
    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)
    '''在父表中的 relationship() 方法传入 secondary 参数，其值为关联表的表名'''
    childrens = relationship("Child", secondary=association_table)

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

二: 双向关系的建立（父类可直接使用子类，子类也可直接使用父类）

此时两个表类都会包含这个集合。指定使用 relationship.back_populates 参数，并且为每一个 relationship() 方法指定共用的关联表：

association_table = Table('association', Base.metadata,
    Column('id', Integer, primary_key=True),
    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)
    childrens = 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="childrens")

同样当我们在父表类的 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)
    childrens = relationship(
        "Child",
        secondary=association_table,
        backref="parents")

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

secondary 还能接收一个可调函数的最终返回值为参数使用如下所示。

'''
仅在首次使用映射器时完成。 使用这个，我们可以在以后定义association_table，
只要它在所有模块初始化完成后可用于callable：
'''
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        secondary=lambda: association_table,
        backref="parents")

中间表也可以用如下普通表的形式来表示

class Association(Base):
    __tablename__ = 'association'
    id = Column(Integer, primary_key=True)
    left_id = Column(Integer, ForeignKey('left.id'))
    right_id = Column(Integer, ForeignKey('right.id'))

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

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