第五章 数据库

序：什么是数据库

数据库按规则保存程序数据，
程序发起查询取回数据。
Web 程序最常使用基于关系模型的数据库，
这种数据库也称为 SQL 数据库，
因为它们使用结构化查询语言。
不过最近几年文档数据库和键值对数据库成了流行的替代选择，
这两种数据库合称 NoSQL数据库。

5.1 SQL数据库

关系型数据库把数据存储在表中，
表模拟程序中不同的实体。
例如，订单管理程序的数据库中可能有表 customers、 products 和 orders。

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

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

5.2 NoSQL数据库

所有不遵循上节所述的关系模型的数据库统称为 NoSQL 数据库。
NoSQL 数据库一般使用集合代替表，
使用文档代替记录。
NoSQL 数据库采用的设计方式使联结变得困难，
所以大多数数据库根本不支持这种操作。

5.3 使用SQL还是NoSQL

SQL 数据库擅于用高效且紧凑的形式存储结构化数据。
这种数据库需要花费大量精力保证数据的一致性。
NoSQL 数据库放宽了对这种一致性的要求，
从而获得性能上的优势。

对不同类型数据库的全面分析、 对比超出了本书范畴。
对中小型程序来说，
SQL 和 NoSQL数据库都是很好的选择，
而且性能相当。

5.4 python数据库框架

可选择的数据库框架

大多数的数据库引擎都有对应的 Python 包，
包括开源包和商业包。
Flask 并不限制你使用何种类型的数据库包，
因此可以根据自己的喜好选择使用
MySQL、 Postgres、 SQLite、
Redis、 MongoDB 或者 CouchDB。

如果这些都无法满足需求，
还有一些数据库抽象层代码包供选择，
例如 SQLAlchemy 和MongoEngine。
你可以使用这些抽象包直接处理高等级的 Python 对象，
而不用处理如表、文档或查询语言此类的数据库实体。

选择时需要考虑的因素

易用性

如果直接比较数据库引擎和数据库抽象层，显然后者取胜。

性能

一般情况下， ORM 和 ODM 对生产率的提
升远远超过把对象业务转换成数据库业务的损耗。
真正的关键点在于如何选择一个能直接操作低层数据库的抽象层，
以防特定的操作需要直接使用数据库原生指令优化。

可移稙性

选择数据库时，必须考虑其是否能在你的开发平台和生产平台中使用。
SQLAlchemy ORM 就是一个很好的例子，
它支持很多关系型数据库引擎，
包括流行的 MySQL、 Postgres 和 SQLite。

Flask集成度

专门为Flask 开发的扩展是你的首选，
选择这些框架可以节省你编写集成代码的时间。

5.5 使用Flask-SQLAlchemy管理数据库

什么是Flask-SQLAlchemy

Flask-SQLAlchemy 是一个 Flask 扩展，
简化了在 Flask 程序中使用 SQLAlchemy 的操作。
SQLAlchemy 是一个很强大的关系型数据库框架，
支持多种数据库后台。
SQLAlchemy 提供了高层 ORM，
也提供了使用数据库原生 SQL 的低层功能。

如何安装

$ pip install flask-sqlalchemy

数据库URL

表5-1 FLask-SQLAlchemy数据库URL

数据库引擎	URL
MySQL	mysql://username:password@hostname/database
Postgres	postgresql://username:password@hostname/database
SQLite（ Unix）	sqlite:////absolute/path/to/database
SQLite（ Windows）	sqlite:///c:/absolute/path/to/database

配置数据库

from flask_sqlalchemy import SQLAlchemy
 
basedir = os.path.abspath(os.path.dirname(__file__))
 
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = \
    'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
  
db = SQLAlchemy(app)

__file__是当前文件的文件路径（包含文件名），
dirname可以提取出不包含文件名的路径，
abspath可以把路径转换为绝对路径，
join可以把两个路径合并为一个路径。

db对象是 SQLAlchemy类的实例，
表示程序使用的数据库，
同时还获得了 Flask-SQLAlchemy提供的所有功能。

配置对象中还有一个很有用的选项，
即SQLALCHEMY_COMMIT_ON_TEARDOWN键，
将其设为 True时，
每次请求结束后都会自动提交数据库中的变动。

5.6 定义模型

什么是模型

模型这个术语表示程序使用的持久化实体。
在 ORM 中，模型一般是一个 Python 类，
类中的属性对应数据库表中的列。

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

示例5-2 hello.py：定义Role和User模型

class Role(db.Model):
    __tabname__ = '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):
    __tabname__ = '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 会使用一个默认名字，
但默认的表名没有遵守使用复数形式进行命名的约定，
所以最好由我们自己来指定表名。
其余的类变量都是该模型的属性，
被定义为 db.Column类的实例。

表5-2 最常用的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	二进制文件

5-3 最常用的SQLAlchemy列选项

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

5.7 关系

一对多的关系

角色到用户是一对多关系，
因为一个角色可属于多个用户，
而每个用户都只能有一个角色。

示例5-3：一对多的代码

class User(db.Model):
    # ...
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
 
class Role(db.Model):
    # ...
    users = db.relationship('User', backref='role')

User中的 role_id 列被定义为外键(db.ForeignKey)，
它的参数 'roles.id' 表明，
这列等于__tabname__为roles的id列。

对于一个 Role 类的实例，
其 users 属性将返回与角色相关联的用户组成的列表。
也就是说，如果当前类A的实例是a，
另一个和A相关的的类是B，
则返回所有和a相关的B的实例b。

db.relationship() 的第一个参数表明这个关系的另一端是哪个模型，
也就是B。
如果模型类尚未定义，
可使用字符串形式指定。
backref 参数向 B类中添加一个属性，
从而定义反向关系。
这一属性可替代外键访问B类，
此时获取的是B的实例列表，而不是外键的值。

大多数情况下，
db.relationship() 都能自行找到关系中的外键，
但有时却无法决定把哪一列作为外键。 例如，
如果 User 模型中有两个或以上的列定义为 Role 模型的外键，
SQLAlchemy 就不知道该使用哪列。
如果无法决定外键，
你就要为 db.relationship() 提供额外参数，
从而确定所用外键。

表5-4 常用的SQLAlchemy关系选项

选项名	说明
backref	在关系的另一个模型中添加反向引用
primaryjoin	明确指定两个模型之间使用的联结条件。只在模棱两可的关系中需要指定
lazy	指定如何加载相关记录。可选值有 select（首次访问时按需加载）、 immediate（源对象加载后就加载）、 joined（加载记录，但使用联结）、 subquery（立即加载，但使用子查询），noload（永不加载）和 dynamic（不加载记录，但提供加载记录的查询）
uselist	如果设为 Fales，不使用列表，而使用标量值
order_by	指定关系中记录的排序方式
secondary	指定多对多关系中关系表的名字
secondaryjoin	SQLAlchemy 无法自行决定时，指定多对多关系中的二级联结条件

一对一关系可以用前面介绍的一对多关系表示，
但调用 db.relationship() 时要把 uselist 设为 False，
把“多”变成“一”。
多对一关系也可使用一对多表示，
对调两个表即可，
或者把外键和 db.relationship() 都放在“多”这一侧。
最复杂的关系类型是多对多，
需要用到第三张表， 这个表称为关系表。
你将在第 12 章学习多对多关系。

5.8 数据库操作

5.8.1 创建表

>>> from hello import db
>>> db.create_all()
>>> db.drop_all()

5.8.2 插入行

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

这些新建对象的 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 和第 4 章介绍的 Flasksession 对象没有关系。
数据库会话也称为事务。

>>> 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() 后，
添加到数据库会话中的所有对象
都会还原到它们在数据库时的状态。

5.8.3 修改行

在数据库会话上调用 add() 方法也能更新模型。
下面这个例子把 "Admin" 角色重命名为 "Administrator"：

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

5.8.4 删除行

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

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

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

5.8.5 查询行

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 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 对象的形式存在，
而是作为各自数据库表中的行。
下面这个例子发起了一个查询，
加载名为 "User" 的用户角色：

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

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

表5-5 常用的SQLAlchemy查询过滤器

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

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

表5-6 常用的SQLAlchemy查询执行函数

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

>>> 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' 参数，
从而禁止自动执行查询。

示例5-4 hello.py：动态关系

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

5.9 在视图函数中操作数据库

数据库操作可以直接在视图函数中进行，
示例 5-5 展示了首页路由的新版本，
已经把用户输入的名字写入了数据库。

@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)
            session['known'] = False
        else:
            session['known'] = True
        session['name'] = form.name.data
        form.name.data = ''
        return redirect(url_for('index'))
    return reder_template('index.html',
        form = form, name = session.get('name'),
        known = session.get('known', False))

在这个修改后的版本中，
提交表单后，
程序会使用 filter_by() 查询过滤器在数据库中查找提交的名字。
变量 known 被写入用户会话中，
因此重定向之后，可以把数据传给模板，
用来显示自定义的欢迎消息。

示例5-6 templates/index.html

{% 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 %}

5.10 集成Python shell

次启动 shell 会话都要导入数据库实例和模型，
这真是份枯燥的工作。
为了避免一直重复导入，
我们可以做些配置，
让 Flask-Script 的 shell 命令自动导入特定的对象。

若想把对象添加到导入列表中，
我们要为 shell 命令注册一个 make_context 回调函数，
如示例 5-7 所示。

示例 5-7 hello.py： 为 shell 命令添加一个上下文

from flask_script import Shell
 
def make_shell_context():
    return dict(app=app, db=db, User=User, Role=Role)
manager.add_command("shell", Shell(make_context=make_shell_context))

make_shell_context() 函数注册了程序、数据库实例以及模型，
因此这些对象能直接导入 shell：

$ python hello.py shell
>>> app
<Flask 'app'>
>>> db
<SQLAlchemy engine='sqlite:////home/flask/flasky/data.sqlite'>
>>> User
<class 'app.User'>

5.11 使用alembic数据库迁移

安装

Alembic是SQLAlchemy作者编写的Python数据库迁移工具。
通过pip安装，会自动安装依赖包SQLAlchemy、Mako和MarkupSafe。

pip install alembic

安装完成后就可以使用alembic命令，
所有Alembic操作均由该命令实现（类似Git）。

初始化

cd到你的应用程序的路径，
然后在命令行输入：

alembic init alembic

第一个alembic是声明要使用alembic的命令，
第二个alembic可以随便命名，是用来存放数据库环境配置文件的。
输入命令后，数据库环境创建完成。
这时在目录下出现了一个alembic的文件夹。

在进行初始化时，
根据使用多数据库、单数据库或者pylons，
选择不同的选项，默认是单数据库。
如果要使用pylons项目，可以使用下列命令：

alembic init --template pylons ./scripts

配置数据库地址

在alembic的同级目录下，
同样生成了一个alembic.ini配置文件。
当alembic脚本激活时，
会查找这个配置文件的选项并应用。
也可以利用--config选项来指定.ini文件的位置。

在初始化结束后，需要配置数据库连接URL，
在alembic.ini中设置：

sqlalchemy.url = sqlite:///data.sqlite

sqlite:////是绝对地址，sqlite:///是相对地址。

配置自动迁移

在alembic文件夹的env.py中，
有一个选项是target_metadata = None。
修改这个选项可以自动生成迁移脚本，
我们将它修改为：

from app import db
target_metadata = db.metadata

但运行脚本时会提示还不知道app模块的位置。
我们在这两行代码前，把app的位置添加到系统环境变量。

import os
import sys
 
root = os.path.dirname(__file__) + '/../'
print(root)
 
sys.path.append(root)

现在就可以使用自动迁移选项了。

自动迁移检查哪些数据库变化

自动迁移会检查

• Table的增加、删除
• Column的增加、删除
• Column的空状态变化
• Index的基本变化和明确命名的UniqueConstraint 0.6.1增加
• ForeignKey constraint的基本变化 0.7.1增加

可选择性的检查

• Column类型的变化
• Server default的更改

要检测Column类型的变化，

需要把python包中的alembic/runtime/evironment.py中的
configure函数的compare_type设为True。

不会检查

• Tablename的改变
• Column name的改变
• 匿名constraints
• 特殊的SQLAlchemy类型，例如Enum在不支持的引擎中使用

创建版本

用alembic revision --autogenerate -m "注释"来创建数据库版本。
自动迁移和手动迁移的区别在于，
是否自动在迁移脚本中提供upgrade和downgrade函数的功能，
这两个函数用来升级或者回滚数据库版本。
加上--autogenerate就是自动迁移，否则是手动迁移。

迁移脚本位于alembic文件夹下的revision文件夹中，
以header信息加注释的形式命名。

升级和回滚

使用alembic upgrade进行升级，
使用alembic downgrade进行回滚。
例如：

$ alembic upgrade head

删除历史记录

Alembic在数据库中仅保存当前版本号，
其余信息均从文件读取。
删除历史记录，
只需要将数据库表删除，
并删除versions下的所有文件。
而alembic.ini和env.py中的设置无需更改，
可以再次使用。