SQLAlchemy 全面理解

这个系列是学习《Flask Web开发:基于Python的Web应用开发实战》的部分笔记

对于用户提交的信息，包括 账号、文章 等，需要能够将这些数据保存下来

持久存储的三种方法：

• 文件：shelve(pickle 和 DBM 的结合)等，提供类似字典的对象接口
• 关系型数据库(SQL)
• 非关系型数据库(NoSQL)
• 其他

通常会使用数据库保存信息，并向数据库发起查询获取信息

SQL，关系型数据库

关系型数据库把数据存储在表中,表在程序中通过 Python 的类实现。例如,订单管理程序的数据库中可能有表 customers、products 和 orders。

表的列数是固定的,行数是可变的。

列定义表所表示的实体的数据属性。例如,customers表中可能有 name、address、phone 等列。表中的行定义各列对应的真实数据。

表中有个特殊的列,称为主键,其值为表中各行的唯一标识符。表中还可以有称为外键的列,引用同一个表或不同表中某行的主键。行之间的这种联系称为关系,这是关系型数据库模型的基础。

从这个例子可以看出,关系型数据库存储数据很高效,而且避免了重复。将这个数据库中的用户角色重命名也很简单,因为角色名只出现在一个地方。一旦在 roles 表中修改完角色名,所有通过 role_id 引用这个角色的用户都能立即看到更新。

但从另一方面来看,把数据分别存放在多个表中还是很复杂的。生成一个包含角色的用户列表会遇到一个小问题,因为在此之前要分别从两个表中读取用户和用户角色,再将其联结起来。关系型数据库引擎为联结操作提供了必要的支持。

将数据分开存放在多个表中，通过外键建立联结。减少数据重复量。查询比较麻烦，但修改方便。

关系型数据库有：

• MySQL

• PostgreSQL

• SQLite

比较特殊，是存储于硬盘上单个文件中的数据库。用一个文件保存每一个数据库的所有数据。Python 自带。但同一时间只能有一个连接访问。所以强烈建议不要在一个生产环境的web应用中使用。

等

NoSQL，非关系型数据库

• 键值对

键-值对数据存储是基于散列映射的数据结构。

• 面向文档的

MongoDB
Riak
Apache CouchDB

访问关系型数据库

Python 可以通过数据库接口程序(DB-API)或对象关系映射(ORM)访问关系数据库。

DB-API

Python 程序可以通过 API 连接到目标数据库, 并用 SQL 语句进行数据读取操作

connect()，创建连接

close()，关闭数据库连接

commit()，提交

rollback()，回滚/取消当前

Python 的官方规范 PEP 0249

MySQL 和 PostgreSQL 是最常见的存储 Python web 应用数据的开源数据库。

• MySQL

唯一的 MySQL API：MySQLdb

• PostgreSQL

有至少三个接口程序

• SQLite

sqlite3

基本 SQL 语句

• 创建数据库、将数据库的权限赋给某个/全部用户
  CREATT DATABASE test;
  GRANT ALL ON test.* to user;

• 选择要使用的数据库
  USE test;

• 删除数据库
  DROP DATABASE test;

• 创建表
  CREAT TABLE users;

• 删除表
  DROP TABLE users;

• 插入行
  INSERT INTO users VALUES();

• 更新行
  UPDATE users SET XXX;

• 删除行
  DELETE FROM users ;

ORM

使用DB-API访问数据库，需要懂 SQL 语言，能够写 SQL 语句，如果不想懂 SQL，又想使用关系型数据库，可以使用 ORM

对象关系映射（Object Relational Mapping，简称ORM）

一个 ORM , 它的一端连着 Database, 一端连着 Python DataObject 对象。有了 ORM，可以通过对 Python 对象的操作，实现对数据库的操作，不需要直接写 SQL 语句。ORM 会自动将 Python 代码转换成对应的 SQL 语句。其余的操作，包括数据检查，生成 SQL 语句、事务控制、回滚等交由 ORM 框架来完成。

DataObject 可以通过 Query 执行产生，也可以通过用户自己创建产生。

当然，ORM 还是可以执行原始的 SQL 语句，以便执行一些复杂的/特别的操作。

查找角色为 "User" 的所有用户:

>>> user_role = Role(name='User')
>>> User.query.filter_by(role=user_role).all()  # 
 [<User u'susan'>, <User u'david'>]

若要查看 SQLAlchemy 为查询生成的原生 SQL 查询语句,只需`把 query 对象转换成字符串` :

>>> str(User.query.filter_by(role=user_role))
'SELECT users.id AS users_id, users.username AS users_username, users.role_id AS users_role_id FROM users WHERE :param_1 = users.role_id'

数据库将很多 SQL 的功能抽象为 Python 对象，这样，不需要写 SQL 也能完成对数据库的操作。

在Flask 中通过 Python 的类定义数据库的表

from flask.ext.sqlalchemy import SQLAlchemy # 从 flask 扩展中导入 SQLAlchemy

db = SQLAlchemy()

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text) # 博客正文，不限长度
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow) # 发布博文的时间
    body_html = db.Column(db.Text) # 存放转换后的 HTML 代码
    author_id = db.Column(db.Integer, db.ForeignKey('users.id')) # 外键使用 ForeignKey，指向 User 表的 id

    comments = db.relationship('Comment', backref='post', lazy='dynamic')

ORM 类似标准的数据库接口，但很多工作由 ORM 代为处理了，不需要直接使用接口。

Python 的 ORM 模块：SQLAlchemy 等

一些大型 web 开发工具/框架 有自己的 ORM 组件。

import os
basedir = os.path.abspath(os.path.dirname(__file__)) # 项目根目录

SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'app.db') # 数据库文件的路径、文件名
    # print SQLALCHEMY_DATABASE_URI
    # sqlite:////Users/chao/Desktop/projects/flask/flask_blog/app.db
SQLALCHEMY_MIGRATE_REPO = os.path.join(basedir, 'db_repository') # 文件夹，保存`SQLAlchemy-migrate`数据文件，也就是迁移策略文件
    # print SQLALCHEMY_MIGRATE_REPO
    # /Users/chao/Desktop/projects/flask/flask_blog/db_repository

数据库引擎的配置

hello.py

from flask.ext.sqlalchemy import SQLAlchemy # 从 flask 扩展中导入 SQLAlchemy

db = SQLAlchemy()  # 创建数据库实例`db`

如何选择

你要考虑以下几个因素。

• 易用性
  如果直接比较API和ORM,显然后者取胜。对象关系映射(Object-Relational Mapper,ORM)在用户不知觉的情况下把高层的面向对象操作转换成低层的数据库指令。
• 性能
  ORM 把对象业务转换成数据库业务会有一定的损耗。大多数情况下,这种性能的降低微不足道,但也不一定都是如此。一般情况下,ORM 对生产率的提升远远超过了这一丁点儿的性能降低,所以性能降低这个理由不足以说服用户完全放弃 ORM。真正的关键点在于如何选择一个能直接操作低层数据库的抽象层,以防特定的操作需要直接使用数据库原生指令优化。
• 可移植性
  选择数据库时,必须考虑其是否能在你的开发平台和生产平台中使用。例如,如果你打算利用云平台托管程序,就要知道这个云服务提供了哪些数据库可供选择。可移植性还针对 ORM。尽管有些 ORM 只为一种数据库引擎提供抽象层,但其他 ORM 可能做了更高层的抽象,它们支持不同的数据库引擎,而且都使用相同的面向对象接口。SQLAlchemy ORM 就是一个很好的例子,它支持很多关系型数据库引擎,包 括流行的 MySQL、Postgres 和 SQLite。
• FLask集成度
  选择框架时,你不一定非得选择已经集成了 Flask 的框架,但选择这些框架可以节省你编写集成代码的时间。使用集成了 Flask 的框架可以简化配置和操作,所以专门为 Flask 开发的扩展是你的首选。

基于以上因素,本书选择使用的数据库框架是 Flask-SQLAlchemy,这个 Flask 扩展包装了SQLAlchemy框架。

数据库模型

定义模型

在 ORM 中,模型一般是一个 Python 类, 代表数据库中的一张表, 类中的属性对应数据库表中的列。

Flask-SQLAlchemy 创建的数据库实例为模型提供了一个基类db.Model以及一系列辅助类和辅助函数,可用于定义 模型/表 的结构。

下面的例子定义了两个表，一个是用户角色，一个是用户信息

hello.py

class Role(db.Model):
        __tablename__ = 'roles'
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(64), unique=True)

        def __repr__(self):
            return '<Role %r>' % self.name

class User(db.Model):
        __tablename__ = 'users'
        id = db.Column(db.Integer, primary_key=True)
        username = db.Column(db.String(64), unique=True, index=True)

        def __repr__(self):
            return '<User %r>' % self.username

类变量__tablename__定义在数据库中使用的表名。如果没有定义__tablename__,Flask-SQLAlchemy 会使用一个默认名字,但默认的表名没有遵守 使用复数形式进行命名(加 s ) 的约定, 所以最好由我们自己来指定表名。

其余的类变量都是该 模型的属性/表的列,被定义为 db.Column 类的实例。

db.Column 类构造函数的第一个参数是数据库表列/模型属性 的类型。

更多类型

db.Column 中其余的参数指定属性的配置选项。

选项名	说 明
primary_key	如果设为 True,这列就是表的主键
unique	如果设为 True,这列不允许出现重复的值
index	如果设为 True,为这列创建索引,提升查询效率
nullable	如果设为 True,这列允许使用空值;如果设为 False,这列不允许使用空值
default	为这列定义默认值

Flask-SQLAlchemy 要求每个模型都要定义主键,这一列经常命名为 id。ID 由 Flask-SQLAlchemy 控制。

其他配置选项

虽然没有强制要求,但这两个模型都定义了__repr()__方法,返回一个 具有可读性的字符串 表示 模型,可在调试和测试时使用。

数据库操作

学习如何使用模型的最好方法是在 Python shell 中实际操作。

• 创建表

首先,我们要让 Flask-SQLAlchemy 根据模型类创建数据库。方法是使用 db.create_all() 函数:

      (venv) $ python hello.py shell  # 进入 Python shell
      >>> from hello import db   # 从`hello.py`导入创建的数据库实例
      >>> db.create_all()

如果你查看程序目录,会发现新建了一个名为app.db的文件。这个 SQLite 数据库文件 的名字就是在配置中指定的。如果数据库表已经存在于数据库中,那么 db.create_all() 不会重新创建或者更新这个表。如果在模型中做了修改，想要把改动应用到现有的数据库中,这一特性会带来不便。

更新现有数据库表的粗暴方式是先删除旧表再重新创建:

      >>> db.drop_all()
      >>> db.create_all()

遗憾的是,这个方法有个我们不想看到的副作用,它把数据库中原有的数据都销毁了。末尾将会介绍一种称为数据库迁移的方式用于更新数据库。

• 插入行

>>> from hello import Role, User
     >>> admin_role = Role(name='Admin')
     >>> mod_role = Role(name='Moderator')
     >>> user_role = Role(name='User')
     >>> user_john = User(username='john', role=admin_role)
     >>> user_susan = User(username='susan', role=user_role)
     >>> user_david = User(username='david', role=user_role)

模型的构造函数接受的参数是使用关键字参数指定的模型属性初始值。注意,role 属性也可使用,虽然它不是真正的数据库列,但却是一对多关系的高级表示。这些新建对象的 id 属性并没有明确设定,因为主键是由 Flask-SQLAlchemy 管理的。现在这些对象只存在于 Python 中,还未写入数据库。因此id 尚未赋值:

     >>> print(admin_role.id)
     None
     >>> print(mod_role.id)
     None
     >>> print(user_role.id)
     None

通过数据库会话管理对数据库所做的改动,在 Flask-SQLAlchemy 中,会话由 db.session 表示。准备把对象写入数据库之前,先要将其添加到会话中:

     >>> db.session.add(admin_role)
     >>> db.session.add(mod_role)
     >>> db.session.add(user_role)
     >>> db.session.add(user_john)
     >>> db.session.add(user_susan)
     >>> db.session.add(user_david)

或者简写成:

     >>> db.session.add_all([admin_role, mod_role, user_role,
     ...     user_john, user_susan, user_david])

为了把对象写入数据库,我们要调用 commit() 方法提交会话:

>>> db.session.commit()

再次查看 id 属性,现在它们已经赋值了:

>>> print(admin_role.id)
1
>>> print(mod_role.id)
2
>>> print(user_role.id)
3

数据库会话能保证数据库的一致性。提交操作使用原子方式把会话中的对象全部写入数据 库。如果在写入会话的过程中发生了错误,整个会话都会失效。如果你始终把相关改动放 在会话中提交,就能避免因部分更新导致的数据库不一致性。 一致性：数据库中数据与实际保存的数据不一致。

数据库会话也可回滚。调用 db.session.rollback() 后,添加到数据库会话中、还未提交的所有对象都会还原到它们在数据库中的版本。

• 修改行

在数据库会话上调用 add() 方法也能更新模型。我们继续在之前的 shell 会话中进行操作

下面这个例子把 "Admin" 角色重命名为 "Administrator":

     >>> admin_role.name = 'Administrator'
     >>> db.session.add(admin_role)
     >>> db.session.commit()

• 删除行

数据库会话还有个 delete() 方法。下面这个例子把 "Moderator" 角色从数据库中删除:

     >>> db.session.delete(mod_role)
     >>> db.session.commit()

注意,删除与插入和更新一样,提交数据库会话后才会执行。

• 查询行

Flask-SQLAlchemy 为每个模型类都提供了 query 对象。最基本的模型查询是取回对应表中的所有记录:


>>> Role.query.all()
      [<Role u'Administrator'>, <Role u'User'>]
>>> User.query.all()
      [<User u'john'>, <User u'susan'>, <User u'david'>]

使用过滤器可以配置 query 对象进行更精确的数据库查询。下面这个例子查找角色为 "User" 的所有用户:

>>> User.query.filter_by(role=user_role).all()  # user_role = Role(name='User'), role=user_role
 [<User u'susan'>, <User u'david'>]

若要查看 SQLAlchemy 为查询生成的原生 SQL 查询语句,只需把 query 对象转换成字符串 :

>>> str(User.query.filter_by(role=user_role))
'SELECT users.id AS users_id, users.username AS users_username, users.role_id AS users_role_id FROM users WHERE :param_1 = users.role_id'

如果你退出了 shell 会话,前面这些例子中创建的对象就不会以 Python 对象的形式存在,而是作为各自数据库表中的行。如果你打开了一个新的 shell 会话,就要从数据库中读取行, 再重新创建 Python 对象。

下面这个例子发起了一个查询,加载名为 "User" 的用户角色:

>>> user_role = Role.query.filter_by(name='User').first()

filter_by() 等过滤器在 query 对象上调用,返回一个更精确的 query 对象。多个过滤器可以一起调用,直到获得所需结果。

可在 query 对象上调用的常用过滤器。

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

在查询上应用指定的过滤器后,通过调用 all() 执行查询,以列表的形式返回结果。除了 all() 之外,还有其他方法能触发查询执行。

常用查询执行函数

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

关系和查询的处理方式类似。

完整的列表参见SQLAlchemy query

下面这个例子分别从关系的两端查询角色和用户之间的一对 多关系:

     >>> users = user_role.users
     >>> users
     [<User u'susan'>, <User u'david'>]
     >>> users[0].role
     <Role u'User'>

这个例子中的 user_role.users 查询有个小问题。执行 user_role.users 表达式时,隐含的查询会调用 all() 返回一个用户列表。query 对象是隐藏的,因此无法指定更精确的查询 过滤器。就这个特定示例而言,返回一个按照字母顺序排序的用户列表可能更好。

在示例 5-4中,我们修改了关系的设置,加入了lazy = 'dynamic'参数,从而禁止自动执行查询。

     class Role(db.Model):
         # ...
         users = db.relationship('User', backref='role', lazy='dynamic')
         # ...

这样配置关系之后,user_role.users 会返回一个尚未执行的查询,因此可以在其上添加过 滤器:

     >>> user_role.users.order_by(User.username).all()
     [<User u'david'>, <User u'susan'>]
     >>> user_role.users.count()
     2

在视图函数中操作数据库

在 Python shell 中做过练习后，可以直接在视图函数中进行数据库的操作了。

@app.route('/', methods=['GET', 'POST'])
def index():
    form = NameForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.name.data).first()
        if user is None:
            user = User(username = form.name.data)
            db.session.add(user)   # 没有提交？？   配置对象中有一个选项,即 SQLALCHEMY_COMMIT_ON_TEARDOWN 键,将其设为 True 时,`每次请求结束后都会自动提交数据库中的变动`
            session['known'] = False
        else:
            session['known'] = True
        session['name'] = form.name.data
        form.name.data = ''
        return redirect(url_for('index'))
return render_template('index.html', form = form, name = session.get('name'), known = session.get('known', False))

提交表单后,程序会使用filter_by()查询过滤器在数据库中查找提交的名字。变量 known 被写入用户会话中,因此重定向之后,可以把数据传给模板, 用来显示自定义的欢迎消息。注意,要想让程序正常运行,你必须按照前面介绍的方法, 在 Python shell 中创建数据库表。

对应的模板新版本。这个模板使用 known 参数在欢迎消息中加入了第二行,从而对已知用户和新用户显示不同的内容。

{% extends "base.html" %}
{% import "bootstrap/wtf.html" as wtf %}
{% block title %}Flasky{% endblock %}
{% block page_content %} 

<div class="page-header">
<h1>Hello, {% if name %}{{ name }}{% else %}Stranger{% endif %}!</h1> 
{% if not known %}
<p>Pleased to meet you!</p>
{% else %}
    <p>Happy to see you again!</p>
{% endif %} 
</div>

{{ wtf.quick_form(form) }}
{% endblock %}

对象关系教程

SQLAlchemy 官方文档

建立一个关系

翻译自Building a Relationship

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % ( self.name, self.fullname, self.password)

让我们考虑第二个表与User关联,可以被映射和查询。Users 在可以存储任意数量的电子邮件地址关联的用户名。这意味着一个从users到一个存储电子邮件地址的新表Addresses的一对多关联。我们在Address中使用声明定义这张表与User的映射:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref

>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...
...     user = relationship("User", backref=backref('addresses', order_by=id))
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

上述类使用了ForeignKey函数,它是一个应用于Column的指令,表明这一列的值应该保存指定名称的远程列的值。这是关系数据库的一个核心特征,是“胶水”,将原本无关的表变为有丰富的重叠关系的集合。上面的ForeignKey表示，Addresses.user_id列的值应该等于users.id列中的值，即，users的主键。

第二个函数,称为relationship(), 它告诉 ORM ,Address类本身应该使用属性Address.user链接到User类。relationship()使用两个表之间的外键关系来确定这个链接的性质，这个例子中，确定Address.user将要成为多对一中多的一侧。relationship()的参数中有一个称为backref()的relationship()的子函数，反向提供详细的信息, 即在users中添加User对应的Address对象的集合，保存在User.addresses中。多对一关系的反向始终是一对多的关系。一个完整的可用的relationship()配置目录在基本关系模式。

两个互补关系, Address.user和User.addresses被称为一个双向关系，并且这是SQLAlchemy ORM的一个关键特性。小节Linking Relationships with Backref详细讨论了“Backref”特性。

relationship()的参数中，关联的远程类可以通过字符串指定,如果声明系统在使用。在上面的例子的User类中，一旦所有映射完成,这些字符串被认为是用于产生实际参数的 Python 表达式。允许的名字在这个评估包括,除其他方面外,所有类的名称已被创建的声明的基础。

下面我们举例说明，用User代替Address创建相同的 地址/用户 双向关系:

class User(Base):
    # ....
    addresses = relationship("Address", order_by="Address.id", backref="user")

通过relationship()获得参数风格的更多详细信息。

你知道么？

• 外键约束的大部分(尽管不是全部)关系数据库只能链接到一个主键列,或一个有独特约束的列。
• 一个外键约束,引用多个主键列,并且本身有多个列,被称为“复合外键”。它还可以引用这些列的一个子集。
• 外键列可以自动更新,以应对引用的列或行的改变。这就是所谓的级联引用行为,是一个关系数据库的内建函数。
• 外键可以引用自己的表。这是被称为“自我引用”的外键。
• 更多关于外键的信息Foreign Key - Wikipedia

使用关联对象

翻译自Working with Related Objects

现在，当我们创建一个User对象、将出现一个空白Addresses集合。集合有很多类型,如sets和词典,这里都有可能(详细信息Customizing Collection Access),但默认情况下,集合是一个Python列表。

>>> jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
>>> jack.addresses
[]

我们可以向User对象自由的添加Address对象。在这个例子中,我们直接分配一个完整列表:

>>> jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]

当使用一个双向关系时, 元素在一侧被添加后，会自动在出现在另一侧。这种行为的发生，基于属性的改变事件，并且由 Python 判断,不需要使用任何SQL语句:

>>> jack.addresses[1]
<Address(email_address='j25@yahoo.com')>

>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', password='gjffdd')>

我们将Jack Bean添加到数据库会话，并提交到数据库。jack以及相应的addresses集合中的两个Address成员都被一次性添加到会话中, 这使用了一个叫级联的处理:

>>> session.add(jack)
>>> session.commit()

INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT

查询 jack, Jack杰克回来了。SQL中没有提到Jack的地址:

>>> jack = session.query(User).filter_by(name='jack').one() 
>>> jack
<User(name='jack', fullname='Jack Bean', password='gjffdd')>

BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
('jack',)

让我们看一下addresses集合。观察SQL:

>>> jack.addresses 
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

SELECT addresses.id AS addresses_id,
        addresses.email_address AS
        addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
(5,)

当我们访问addresses集合时,SQL突然提到了。这是一个延迟加载的例子。addresses集合现在被加载，并且行为就像一个普通的列表。我们将讨论如何优化这个集合的加载。

基本关系模式

翻译自SQLAlchemy 官方文档

• 一对多

一个parent对多个child，一对多关系添加一个外键到child表，用于保存对应parent.id的值，引用parent。relationship()在parent中指定，引用/保存 一批 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'))

在一对多模式中，建立一个双向关系，ForeignKey所在的是多，在relationship中指定backref选项:

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

表child将因此获得一个parent属性, 值为对应的parent表中的条目。

• 多对一

多个parent对一个child。多到一 在parent表添加一个外键，保存child.id的值。relationship()在parent中被宣告，创建一个新的属性child，保存关联的child表的条目。

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中设置值为"parents"的backref可选参数。在Child类中产生集合，收集parent表中对应条目。

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

• 一对一

一对一本质上是一种同时在两边设置一个数量的属性的双向关系。为了达到这个目标, 设置一个限制数量的属性uselist=False替代关系的many侧的集合。

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

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

或者转换一个 一对多 引用 到 一对一，使用backref()函数为反向端提供uselist=False参数：

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)

• 多到多

多对多关系需要在两个类之间增加一个关系表。关系表通过relationship()的secondary参数标识。通常，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)

对于一个双向关系，关系两边都包含一个集合。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)

relationship()的secondary参数还接受一个可以返回最终参数的调用，只有当映射第一次使用时进行评估。使用这个，我们可以在以后定义association_table,只要在所有模块初始化完成后能够被调用：

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

通过使用扩展的声明，传统的"表的字符串名称"被接受，匹配的表名存储在Base.metadata.tables中：

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

使用 Backref 链接关系

翻译自Linking Relationships with Backref

backref关键字参数它实际在做什么?

让我们先从标准的用户和地址情境开始了解:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

以上配置在User中建立一个名为User.addresses的，关联的Address对象/条目的集合。它还在Address中建立了一个user属性，保存关联的User条目。

事实上,backref关键字只是一个常见的快捷方式, 用于将第二个relationship()放置到关系另一端的Address, 同时在两边建立一个事件侦听器，在关系两边对属性操作进行镜像复制。以上配置相当于:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", back_populates="addresses")

在上面,我们明确地向Address添加了一个名为User的关系。在关系的两边,back_populates参数将关联的对端信息告诉给每一个relationship，表明他们应该互相之间建立“双向”的行为。这个配置的主要作用是将事件处理程序添加到relationship, ”当一个添加或设置事件发生时,设置使用这个属性名称传入属性”。这种行为说明如下。从一个User和一个Address 的实例开始。addresses集合是空的, 并且user属性是None:

>>> u1 = User()
>>> a1 = Address()
>>> u1.addresses
[]
>>> print a1.user
None

无论如何,一旦Address被添加到u1.addresses集合,所有的集合和标量属性将被填充:

>>> u1.addresses.append(a1)
>>> u1.addresses
[<__main__.Address object at 0x12a6ed0>]
>>> a1.user
<__main__.User object at 0x12a6590>

这种行为在反向删除操作中当然也一样 ,同样两边等效操作。例如，当user属性再次设置为None,Address对象从反向集合中被删除:

>>> a1.user = None
>>> u1.addresses
[]

对addresses集合和user属性的操作，完全发生在 Python 中, 没有任何与SQL数据库的交互。如果不这样处理, 需要将数据更新到数据库，然后在一个提交或过期操作发生后重新加载，才能在两边看到正确的状态。backref/back_populates行为的优点是常见的双向操作可以反映正确的状态，不需要一个数据库往返。

记住,当在一个关系的一边使用backref关键字,和上面 在关系的两边单独使用back_populates是一样的。

Backref 参数

我们已经建立backref关键字只是一个快捷方式，用于构建两个独立的relationship()结构去引用对方。这个快捷方式的部分行为，是确定 应用到relationship()的配置参数 也将被应用到另一个方向——即那些参数描述模式层面的关系,不太可能在相反的方向不同。通常的情况是一个多对多关系,有一个secondary参数,或者一对多或多对一有一个primaryjoin参数。比如如果我们限制列表中的Address对象以tony开头:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address",
                    primaryjoin="and_(User.id==Address.user_id, "
                        "Address.email.startswith('tony'))",
                    backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

我们可以观察到,通过检查生成的内容,关系的两边应用jion条件:

>>> print User.addresses.property.primaryjoin
"user".id = address.user_id AND address.email LIKE :email_1 || '%%'
>>>
>>> print Address.user.property.primaryjoin
"user".id = address.user_id AND address.email LIKE :email_1 || '%%'
>>>

重用的参数都应该做“正确的事”——它只使用合适的参数,在 多对多 关系中,将对另一端反向使用primaryjoin和secondaryjoin。

最常见的情况是, 我们想在backref端指定另一端使用的参数。这包括relationship()的参数，比如lazy,remote_side、cascade、cascade_backrefs。对于这种情况,我们使用backref()函数代替字符串:

# <other imports>
from sqlalchemy.orm import backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address",
                    backref=backref("user", lazy="joined"))

上面,我们仅在Address.user放置一个lazy="joined"参数，表明当一个针对Address的查询发生,一个User实例的 join 应自动每个返回的Address的user属性填充。backref()函数格式化的参数 我们将它变成一种被relationship()解释为应用到它创建的新关系的附加参数。

数据库迁移

在开发程序的过程中,你会发现有时需要修改数据库模型, 比如 增加表、添加列 ,而且修改之后还需要更新数据库, 就需要对数据库进行迁移

更新表的更好方法是使用数据库迁移框架。源码版本控制工具可以跟踪源码文件的变化, 类似地,数据库迁移框架能跟踪数据库模式的变化,然后增量式的把变化应用到数据库中。

SQLAlchemy 的主力开发人员编写了一个迁移框架,称为 Alembic。除了直接使用 Alembic 之外,Flask 程序还可使用 Flask-Migrate扩展。这个扩展对 Alembic 做了轻量级包装,并集成到 Flask-Script 中,所有操作都通过 Flask-Script 命令完成。

• 创建迁移仓库

安装 Flask-Migrate:

(venv) $ pip install flask-migrate

初始化、配置 Flask-Migrate

from flask.ext.migrate import Migrate, MigrateCommand
# ...
migrate = Migrate(app, db)  # 初始化
manager.add_command('db', MigrateCommand) # 在命令行中，用`db`调用`MigrateCommand`

➜  flask_blog git:(master) ✗ python run.py
usage: run.py [-?] {shell,db,runserver} ...

positional arguments:
  {shell,db,runserver}
    shell               Runs a Python shell inside Flask application context.
    db                  Perform database migrations
    runserver           Runs the Flask development server i.e. app.run()

optional arguments:
  -?, --help            show this help message and exit

为了导出数据库迁移命令,Flask-Migrate 提供了一个 MigrateCommand 类,可附加到 Flask- Script 的 manager 对象上。在这个例子中,MigrateCommand 类使用 db 命令附加。

在维护数据库迁移之前,要使用 init 子命令创建迁移仓库:

(venv) $ python hello.py db init    # 将向应用添加一个`migrations`文件夹。文件夹中的文件需要和其他源文件一起进行版本控制。➜  

flask_blog git:(master) ✗ python run.py db init
  Creating directory /Users/chao/Desktop/projects/flask/flask_blog/migrations ... done
  Creating directory /Users/chao/Desktop/projects/flask/flask_blog/migrations/versions ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/alembic.ini ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/env.py ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/env.pyc ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/README ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/script.py.mako ... done
  Please edit configuration/connection/logging settings in '/Users/chao/Desktop/projects/flask/flask_blog/migrations/alembic.ini'
  before proceeding.

这个命令会创建 migrations 文件夹,所有迁移脚本都存放其中。

数据库迁移仓库中的文件要和程序的其他文件一起纳入版本控制。

• 创建迁移脚本

在 Alembic 中,数据库迁移用迁移脚本表示。脚本中有两个函数,分别是 upgrade() 和 downgrade()。upgrade() 函数把迁移中的改动应用到数据库中,downgrade() 函数则将改动删除。Alembic 具有添加和删除改动的能力,因此数据库可重设到修改历史的任意一点。

我们可以使用 revision 命令手动创建 Alembic 迁移,也可使用 migrate 命令自动创建。

手动创建的迁移只是一个骨架,upgrade() 和 downgrade() 函数都是空的,开发者要使用 Alembic 提供的 Operations 对象指令实现具体操作。

自动创建的迁移会根据模型定义和数据库当前状态之间的差异生成 upgrade() 和 downgrade() 函数的内容。

自动创建的迁移不一定总是正确的,有可能会漏掉一些细节。自动生成迁移 脚本后一定要进行检查。

migrate 子命令用来自动创建迁移脚本:

     (venv) $ python hello.py db migrate -m "initial migration" # 生成一个初始的迁移
     INFO  [alembic.migration] Context impl SQLiteImpl.
     INFO  [alembic.migration] Will assume non-transactional DDL.
     INFO  [alembic.autogenerate] Detected added table 'roles'
     INFO  [alembic.autogenerate] Detected added table 'users'
     INFO  [alembic.autogenerate.compare] Detected added index 'ix_users_username' on '['username']'
Generating /home/flask/flasky/migrations/versions/1bc 594146bb5_initial_migration.py...done

➜  flask_blog git:(master) ✗ python run.py db migrate -m 'migration'
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_email' on '['email']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_username' on '['username']'
INFO  [alembic.autogenerate.compare] Detected added table 'post'
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/versions/0fb164ef6c11_migration.py ... done

• 更新数据库

检查并修正好迁移脚本之后,我们可以使用db upgrade命令把迁移应用到数据库中:

     (venv) $ python hello.py db upgrade
     INFO  [alembic.migration] Context impl SQLiteImpl.
     INFO  [alembic.migration] Will assume non-transactional DDL.
     INFO  [alembic.migration] Running upgrade None -> 1bc594146bb5, initial migration

➜  flask_blog git:(master) ✗ python run.py db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 0fb164ef6c11, migration

对第一个迁移来说,其作用和调用 db.create_all() 方法一样。但在后续的迁移中, upgrade 命令能把改动应用到数据库中,且不影响其中保存的数据。