Python Module — SQLAlchemy ORM

目录

SQLAlchemy

SQLAlchemy 是 Python 生态中最成熟的关系型 ORM（Object Relational Mappers，对象关系映射）工具库，可以将关系型数据库中提供的大量数据类型转译为 Python 内置的数据类型及其扩展类型，使得开发者可以像操作 Python 对象一般的操作数据库记录。

数据库连接

SQLALCHEMY_DATABASE_URI = {rdbms_type}+{driver}://{username}:{password}@{rdbms_ip}:{port}/{database_name}

MySQL

mysql+pymysql:/{username}:{password}@{rdbms_ip}:{port}/{database_name}?charset=utf8

SQLite

sqlite:///{database_file_path}.sqlite

Models

Models（模型）就是对数据进行建模，并提供一种通用的接口访问方式，区别于数据结构。

SQLAlchemy 的 Models 用于定义一张关系型数据库表结构。

Session

Session 是对 RDBMS 中 “事务” 的抽象接口，当我们 commit 一条 SQL 时，事务会帮助我们实现了一系列有效的数据库操作，以此来保护数据的一致性以及有效性。

CRUD

客户端对关系型数据库的操作类型无非 CRUD（Create / Read / Update / Delete，增查更删）。

其中的查询是最关键的一个部分，花样繁多，比较常见的有以下操作：

1. 查询所有记录
2. 根据主键查询唯一记录
3. 根据 filters（过滤器）条件查询一些记录。
4. 按照特定的排序方式返回查询到的记录。
5. 根据 Pagination（页码）分页返回查询到的记录。

另外，删除操作也有 2 种比较常见的操作：

1. 硬删除：直接删除记录。
2. 软删除：仅标记 deleted 字段，并不删除记录。

Relationship

Relationship（关系）是关系型数据库的核心竞争力，通常的存在以下 3 种类型关系：

1. One To Many
2. Many To One
3. One To One
4. Many To Many

One To Many / Many To One

在 One To Many / Many To One 关系中，我们通常可以使用父 / 子表来进行描述（注：子表的外键指向父表的主键），类比 “一父有多子，多子共一父” 的含义。当我们进行父子表级联查询（Hierarchical Queries）时，也就存在了 2 种情况：

1. 查询父表时，是 One To Many 的关系，会级联查询子表。
2. 查询子表时，不一定会级联查询父表。如果需要级联查询父表，那么此时从子表类的视角出发，就呈现出了一种 Many To One 的关系。

One To Many

One To Many 是一种 “单向” 关系，即：对父表类的查询会同时级联查询子表类，反之，对子表类的查询则不会级联查询父表类。

1. 父表类通过 relationship() 方法来引用子表类。
2. 子表类通过 ForeignKey（外键）来引用父表类的 primary_key（主键）。

class Father(Base):
    __tablename__ = 'fathers'
    id = Column(Integer, primary_key=True)
    sons = relationship('Son'， lazy='dynamic')
       
class Son(Base):
    __tablename__ = 'sons'
    id = Column(Integer, primary_key=True)
    father_id = Column(Integer, ForeignKey('fathers.id'))

值得注意的是，当你没有在父表类中通过 __tablename__ 属性来指向数据库表名时，那么 ForeignKey('Father.id') 可以直接引用父表类名。但并不推荐，因为 SQLAlchemy 在初始化所有 Models 的期间，Father 可能还没有被初始化，也就无法引用其类属性了。相对的，使用 __tablename__ 则一定会在 Father 完成初始化之后再引用。

relationship() 的形参 lazy 用于标识 SQLAlchemy 进行级联查询的方式：

1. lazy=subquery：级联查询子表类时，立即查询并返回所有关联的子表类记录，一次性查询可以减少对 DB 的 I/O 操作。但也可能就返回了大量不被代码使用的数据。建议在数据量小的关系中使用。

2. lazy=dynamic：级联查询子表类时，只有当子表类字段被代码使用时，相关的记录才会被查询并返回。例如：在代码中执行 father.sons 时，不是直接返回数据，而是返回一个 AppenderBaseQuery 实例，通过 AppenderBaseQuery 实例再调用 filter() / all() / first() 等方法进行查询。建议在数据量很大的关系中使用。

Many To One

在 One To Many 关系的基础之上，还可以为父/子表类建立 “双向” 关系。此时对子表类的查询也会同时级联查询父表类，即：从子表类的视角出发，呈现出一种 Many To One 的关系。

1. 父表类通过 relationship() 方法来引用子表类，并且传递 back_populates 参数指示建立 “双向” 关系。
2. 子表类通过 relationship() 方法来引用父表类，并且传递 back_populates 参数指示建立 “双向” 关系。
3. 子表类通过 ForeignKey（外键）来来引用父表类的 primary_key（主键）。

class Father(Base):
    __tablename__ = 'fathers'
    id = Column(Integer, primary_key=True)
    sons = relationship('Son', back_populates='fathers')
       
class Son(Base):
    __tablename__ = 'sons'
    id = Column(Integer, primary_key=True)
    father_id = Column(Integer, ForeignKey('fathers.id'))
    fathers = relationship('Father', back_populates='sons')

Many To One 关系的有 2 个比较通常的用途：

1. 查询子表的时候也把关联的父表一并查询返回。
2. 创建子表的时候也把关联的父表一并创建。

实际上，还可以在 relationship() 中使用 backref 参数来标识构建 “双向” 关系，但使用 backref 的代码可读性稍逊于 back_populates，所以建议使用 back_populates 参数。

EXAMPLE：建立双向的一对多关系

1. 定义 Models

class VirtualMachine(BASE, OctopunchBase):
    """Represents the virtual machine list."""

    __tablename__ = 'virtual_machines'
    uuid = Colmn(String(45), primary_key=True, primary_key=True)
    vm_value = Column(String(255))
    name = Column(String(255))
    datastores = relationship(
        'Datastore',
        backref='virtual_machines',
        foreign_keys='Datastore.virtual_machine_uuid',
        primaryjoin='VirtualMachine.uuid == Datastore.virtual_machine_uuid')


class Datastore(BASE, OctopunchBase):
    """Represents the datastore list."""

    __tablename__ = 'datastores'
    uuid = Colmn(String(45), primary_key=True, primary_key=True)
    ds_value = Column(String(45))
    name = Column(String(255))
    virtual_machine_uuid = Column(String(45), ForeignKey('virtual_machines.uuid'))

2. 封装 DB API

def _resource_refs(resource_dict, resource_class):
    resource_ref = resource_class()
    for k, v in resource_dict.items():
        resource_ref[k] = v
    return resource_ref

@require_context
def virtual_machine_create(context, values):
    datastores = values.pop('datastores')

    # models.Datastore(datastores)
    values['datastores'] = [_resource_refs(datastore, models.Datastore)
                            for datastore in datastores]

    if not values.get('uuid'):
        values['uuid'] = str(uuid.uuid4())

    virtual_machine_ref = models.VirtualMachine()
    virtual_machine_ref.update(values)

    session = get_session()
    with session.begin():
        session.add(virtual_machine_ref)
        return virtual_machine_ref

3. REST API request body

virtual_machine = {
    'uuid': '<virtual_machine_uuid>',
    'vm_value': '<virtual_machine_value>',
    'name': '<virtual_machine_name>',
    'datastores': '[
        {
            "name": "<datastore_1_name>",
            ...
        },
        {
            "name": "<datastore_2_name>",
            ...
        },
        ...
    ]'
}

One To One / Many To Many

在 One To One / Many To Many 关系中，我们通常就不会再使用父子表这一术语来进行描述了，因为显然的，两张表之间具有着平等的关系。

Many To Many

Many To Many 关系的构建需要引入一张额外的关联表（Association Table），通过这张关联表来记录 2 张主表彼此之间的多对多引用关系。

1. 使用 Base.metadata 父类，直接定义关联表，并拥有两个外键分别指向 2 张主表的主键。
2. 在 2 张主表中使用 relationship() 中的 secondary 参数来标识这张关联表。

teachers_students = Table('teachers_students', Base.metadata,
    Column('teacher_id', Integer, ForeignKey('teachers.id')),
    Column('student_id', Integer, ForeignKey('students.id'))
)

class Teacher(Base):
    __tablename__ = 'teachers'
    id = Column(Integer, primary_key=True)
    students = relationship("Student", back_populates='teachers', secondary=teachers_students)
        
class Student(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    teachers = relationship("Teacher", back_populates='students', secondary=teachers_students)

注意，SQLAlchemy.Table 相较于 SQLAlchemy.Model 是更加底层的封装，使用 SQLAlchemy.Table 来直接定义关联表，是因为我们通常不需要对关联表进行直接的代码操作，SQLAlchemy 会自动帮忙我们来维护其记录，实际上 RDBMS 自身会进行处理。

另外，因为 Teacher 和 Student Models 应用了关联表的命名，所以关联表的声明要在 2 个 Models 之前完成。

One To One

One To One 也是一种 “双向关系”，要做到这一点，只需要在 Many To One 关系的基础之上，在父表类的 relationship() 中传递 uselist 参数来标识。

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = 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="children")