Skip to content

数据库

ORM 魔法

在 Web 应用里使用原生 SQL 语句操作数据库主要存在下面两类问题:

(1) 手动编写 SQL 语句比较乏味，而且视图函数中加入太多 SQL 语句会降低代码的易读性。
另外还会容易出现安全问题，比如 SQL 注入

(2) 常见的开发模式是在开发时使用简单的 SQLite，而在部署时切换到 MySQL 等更健壮的 DBMS。
但是对于不同的 DBMS，我们需要使用不同的 Python 接口库，这让 dBMS 的切换变得不太容易。

注意:
尽管使用 ORM 可以避免 SQL 注入问题，但你仍然需要对传入的查询参数进行验证。
另外，在执行原生 SQL 语句时也要注意避免使用字符串拼接或字符串格式化的方式传入参数

使用 ORM 可以很大程度上解决这些问题。
它会自动帮你处理查询参数的转义，尽可能地避免 SQL 注入的发生。
另外，它为不同的 DBMS 提供统一的接口，让切换工作变得非常简单。
ORM 扮演翻译的角色，能够将我们的 Python 语言转换为 DBMS 能够读懂的 SQL 指令，让我们能够使用 Python 来操控数据库

附注:
尽管 ORM 非常方便，但如果你对 SQL 相当熟悉，那么自己编写 SQL 代码可以获得更大的灵活性和性能优势。
就像是使用 IDE 一样，ORM 对初学者来说非常方便，但进阶以后你也许会想要自己掌控一切。

ORM 把底层的 SQL 数据实体转换成高层的 Python 对象，这样一来，你甚至不需要了解 SQL，只需要通过 Python 代码即可完成数据库操作，ORM 主要实现了三层映射关系:

  • 表 -> Python 类
  • 字段(列) -> 类属性
  • 记录(行) -> 类实例

比如，我们要创建一个 contacts 表来存储留言，其中包含用户名称和电话号码两个字段。

在 SQL 中，下面的代码用来创建这个表: 

1
2
3
4
CREATE TABLE contacts (
    name varchar(100) NOT NULL,
    phone_number varchar(32),
);

如果使用 ORM，我们可以使用类似下面的 Python 类来定义这个表: 

1
2
3
4
5
6
from foo_orm import Model, Column, String

class Contact(Model):
    __tablename__ = 'contacts'
    name = Column(String(100), nullable=False)
    phone_number = Column(String(32))

要向表中插入一条记录，需要使用下面的 SQL 语句: 

1
2
INSERT INTO contacts(name, phone_number)
VALUES('Gery Li', '12345678');

使用 ORM 则只需要创建一个 Contact 类的实例，传入对应的参数表示各个列的数据即可。
下面的代码和使用上面的 SQL 语句效果相同: 

1
contact = Contact(name='Grey Li', phone_number='12345678')

除了便于使用，ORM 还有下面下面这些优点:

  • 灵活性好。你既能使用高层对象来操作数据库，又支持执行原生 SQL 语句
  • 提升效率。从高层对象转换成原生 SQL 会牺牲一些性能，但这微不足道的性能牺牲换取的是巨大的效率提升
  • 可移植性好。ORM 通常支持多种 DBMS，包括 MySQL、PostgreSQL、Oracle、SQLite 等。你可以随意更换 DBMS，只需要稍微改动少量配置。

使用 Python 实现的 ORM 有 SQLAlchemy、Peewee、PonyORM 等。
其中 SQLAlchemy 是 Python 社区使用最广泛的 ORM 之一。SQLAlichemy，直译过来就是 SQL 炼金术

使用 Flask-SQLAlchemy 管理数据库

https://github.com/pallets/flask-sqlalchemy

flask-sqlalchemy.palletsprojects.com

http://www.pythondoc.com/flask-sqlalchemy/quickstart.html

扩展 Flask-SQLAlchemy 集成了 SQLAlchemy，它简化了连接数据库服务器、管理数据库操作会话等各类工作，让 Flask 中的数据处理体验变得更加轻松。

下面在示例程序中实例化 Flask-SQLAlchemy 提供的 SQLAlchemy 类，传入程序实例 app，以完成扩展的初始化: 

1
2
3
4
5
6
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

db = SQLAlchemy(app)

为了便于使用，我们把实例化扩展类的对象命名为 db。
这个 db 对象代表我们的数据库，它可以使用 Flask-SQLAlchemy 提供的所有功能。

提示：
虽然我们要使用的大部分类和函数都由 SQLAlchemy 提供，但在 Flask-SQLAlchemy 中，大多数情况下，我们不需要手动从 SQLAlchemy 导入类或函数。
在 sqlalchemy 和 sqlalchemy.orm 模块中实现的类和函数，以及其他几个常用的模块和对象都可以作为 db 对象的属性调用。
当我们创建这样的调用时，Flask-SQLAlchemy 会自动把这些调用转发到对应的类、函数或模块

连接数据库服务器

DBMS 通常会提供数据库服务器运行在操作系统中。
要连接数据库服务器，首先要为我么你的程序指定数据库 URI(Uniform Resource Identifier，统一资源标识符)。
数据库 URI 是一串包含各种属性的字符串，其中包含了各种用于连接数据库的信息

附注:
URI 代表统一资源标识符，是用来标示资源的一组字符串。URL 是它的子集。在大多数情况下，这两者可以交替使用。

下表是一些常用的 DBMS 及其数据库 URI 格式示例:

DBMS URI
PostgreSQL postgresql://username:password@host/databasename
MySQL mysql://username:password@host/databasename
Oracle oracle://username:password@host:port/sidname
SQLite(UNIX) sqlite:////absolute/path/to/foo.db
SQLite(Windows) sqlite:///absolute\\path\\to\\foo.db 或 r'sqlite:///absolute\path\to\foo.db'
SQlite(内存型) sqlite:/// 或 sqlite:///:memory:

在 Flask-SQLAlchemy 中，数据库的 URL 通过配置变量 sQLALCHEMY_DATABASE_URI 设置，默认为 SQLite 内存型数据库(sqlite:///:memory:)。
SQLite 是基于文件的 DBMS，不需要设置数据库服务器，只需要指定数据库文件的绝对路径。
我们使用 app.root_path 来定位数据库文件的路径，并将数据库文件命名为 data.db 

1
2
...
app.config['SQLALCHEY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///' + os.path.join(app.root_path, 'data.db'))

在生产环境下更换到其他类型的 DBMS 时，数据库 URL 会包含敏感信息，所以这里优先从环境变量 DATABASE_URL 获取(注意这里为了便于理解使用了 URL，而不是 URI)

注意:
SQLite 的数据库 URI 在 Linux 或 maxOS 系统下的斜线数量是 4 个；
在 Windows 系统下的 URI 中的斜线数量为 3 个。内存型数据库的斜线固定为 3 个。

提示:
SQLite 数据库文件名不限定后缀，常用的命名方式有 foo.sqlite，foo.db，或是注明 SQLite 版本的 foo.sqlite3

定义数据库模型

用来映射到数据库表的 Python 类通常被称为数据库模型(model)，一个数据库模型类对应数据库中的一个表。
定义模型即使用 Python 类定义表模式，并声明映射关系。所有的模型类都需要继承 Flask-SQLAlchemy 提供的 db.Model 基类。

示例程序是一个笔记程序，笔记保存到数据库中，你可以通过程序查询、添加、更新和删除笔记。
我们定义了一个 Note 模型类，用来存储笔记。 

1
2
3
class Note(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text)

在上面的模型类中，表的字段(列)由 db.Column 类的实例标示，字段的类型通过 Column 类构造方法的第一个参数传入。
在这个模型中，我们创建了一个类型为 db.Integer 的 id 字段和类型为 db.Text 的 body 列，分别存储整型和文本。
常用的 SQLAlchemy 字段类型如表所示:

字段 说明
Integer 整型
String 字符串，可选参数 length 可以用来设置最大长度
Text 较长的 Unicode 文本
Date 日期，存储 Python 的 datetime.date 对象
Time 时间，存储 Python 的 datetime.time 对象
DateTime 时间和日期，存储 Python 的 datetime 对象
Interval 时间间隔，存储 Python 的 datetime.timedelta 对象
Float 浮点数
Boolean 布尔值
PickleType 存储 Pickle 列化的 Python 对象
LargeBinnary 存储任意二进制数据

字段类型一般直接声明即可，如果需要传入参数，你也可以添加括号。
对于类似 String 的字符串列，有些数据库会要求限定长度，因此最好为其指定长度。
虽然使用 Text 类型可以存储相对灵活的变长文本，但从性能上考虑，我们仅在必须的情况下使用 Text 类型，比如用户发表的文章和评论等不限长度的内容。

一般情况下，字段的长度是由程序设计者自定的。
尽管如此，也有一些既定的约束标准，比如姓名(英语)的长度一般不超过 70 个字符，中文名一般不超过 20 个字母，电子邮件地址的长度不超过 254 个字符，虽然各主流浏览器支持长达 2048 个字符的 URL，但在网站中用户资料设置的限度一般为 255.
尽管如此，对于超过一定长度的 Email 和 URL，比如 20 个字符，会在显示时添加省略号的形式。
显示的用户名(username)允许重复，通常要短一些，以下不超过 26 个字符为佳。
当然，在程序中，你可以根据需要来自由设定这些限制值。

注意:
当你在数据库模型类中限制了字段的长度后，在接收对应数据的表单类字段里，也需要使用 Length 验证器来验证用户的输入数据。

默认情况下，Flask-SQLAlchemy 会根据模型类的名称生成一个表名称，生成规则如下: 

1
2
Message -> message  # 单个单词转换为小写
FooBar -> foo_bar  # 多个单词转换为小写并使用下划线分隔

Note 类对应的表名称即 note。如果你想自己指定表名称，可以通过定义 __tablename__ 属性来实现。
字段名默认为类属性名，你也可以通过字段类构造方法的第一个参数指定，或使用关键字 name。
根据我们定义的 Note 模型类，最终将生成一个 note 表，表中包含 id 和 body 字段

除了 name 参数，实例化字段类时常用的字段参数如表所示:

参数名 说明
primary_key 如果设为 True，该字段为主键
unique 如果设为 True，该字段不允许出现重复值
index 如果设为 True，为该字段创建索引，以提高查询效率
nullable 确定字段值可否为空，值为 True 或 False，默认值为 True
default 为字段设置默认值

提示:
不需要在所有列都建立索引。
一般来说，取值可能性多(比如姓名)的列，以及经常被用来作为排序参照的列(比如时间戳)更适合建立索引

在实例化字段类时，通过把参数 primary_key 设为 True 可以将其定义为主键。
在我们定义的 Note 类中，id 字段即表的主键(primary key)。
主键是每一条记录(行)独一无二的标识，也是模型类中必须定义的字段，一般命令为 id 或 pk

创建数据库和表

如果把数据库(文件)看作一个仓库，为了方便取用，我们需要把货物按照类型分别放置在不同货架上，这些货架就是数据库中的表。
创建模型类后，我们需要手动创建数据库和对应的表，也就是我们常说的建库和建表。
这通过对我们的 db 对象调用 create_all() 方法实现: 

1
2
3
flask shell
>>> from app import db
>>> db.create_all()

注意:
如果你将模型类定义在单独的模块中，那么必须在调用 db.create_all() 方法前导入相应模块，以便让 SQLAlchemy 获取模型类被创建时生成的表信息，进而正确生成数据表。

通过下面的方式可以查看模型对应的 SQL 模式(建表语句): 

1
2
3
4
5
6
7
8
9
>>> from app import Note
>>> from sqlalchemy.schema import CreateTable
>>> print(CreateTable(Note.__table__))

CREATE TABLE note (
        id INTEGER NOT NULL, 
        body TEXT, 
        PRIMARY KEY (id)
)

注意:
数据库和表一旦创建后，之后对模型的改动不会自动作用到实际的表中。
比如，在模型类中添加或删除字段，修改字段的名称和类型，这时再次调用 create_all() 也不会更新表结构。
如果要使改动生效，最简单的方式是调用 db.drop_all() 方法删除数据库和表，然后再用 db.create_all() 方法创建。

数据库操作

现在我们创建了模型，也生成了数据库和表，是时候来学习常用的数据库操作了。
数据库操作主要是 CRUD，即 Create(创建)、Read(读取/查询)、Update(更新) 和 Delete(删除)

SQLAlchemy 使用数据库会话来管理数据库操作，这里的数据库会话也称为事务(transaction)。
Flask-SQLAlchemy 自动帮我们创建会话，可以通过 db.session 属性获取。

注意:
SQLAlchemy 中的数据库会话对象和我们在前面介绍的 Flask 中的 session 无关

数据库中的会话代表一个临时存储区，你对数据库做出的改动都会存放在这里。
你可以调用 add() 方法将新创建的对象添加到数据库会话中，或是对会话中的对象进行更新。
只有当你对数据库会话对象调用 commit() 方法时，改动才被提交到数据库，这确保了数据提交的一致性。
另外，数据库会话也支持回滚操作。当你对会话调用 rollback() 方法时，添加到会话中且未提交的改动都将被撤销。

CRUD

Create

添加一条新纪录到数据库主要分为三步:

(1) 创建 Python 对象(实例化模型类)作为一条记录
(2) 添加新创建的记录到数据库会话
(3) 提交数据库会话

下面的示例向数据库中添加了三条留言: 

1
2
3
4
5
6
7
8
>>> from app import db, Note
>>> note1 = Note(body='remember Sammy Jankis')
>>> note2 = Note(body='SHAVE')
>>> note3 = Note(body='DON\'T bELIEVE HISLIES, HE IS THE ONE, KILL HIM')
>>> db.session.add(note1)
>>> db.session.add(note2)
>>> db.session.add(note3)
>>> db.session.commit()

在这个示例中，我们首先从 app 模块导入 db 对象和 Note 类，然后分别创建三个 Note 实例表示三条记录，使用关键字参数传入字段数据。
我们的 Note 类继承自 db.Model 基类，db.Model 基类会为 Note 类提供一个解构函数，接收匹配类属性名称的参数值，并赋值给对应的类属性，所以我们不需要自己在 Note 类中定义构造方法。
接着我们调用 add() 方法把这三个 Note 对象添加到会话对象 db.session 中，最后调用 commit() 方法提交会话。

提示:
除了依次调用 add() 方法添加多个记录，也可以使用 add_all() 一次添加包含所有记录对象的列表。

你可能注意到了，我们在创建模型类实例的时候并没有定义 id 字段的数据，这是因为主键由 SQLAlchemy 管理。
模型类对象创建后作为临时对象(transient)，当你提交数据库会话后，模型类对象才会转换为数据库记录写入数据库中，这时模型类对象会自动获取 id 值: 

1
2
>>> note1.id
1

Read

我们已经知道了如何向数据库里添加记录，那么如何从数据库里取回数据呢？
使用模型类提供的 query 属性附加调用各种过滤方法及查询方法可以完成这个任务。

一般来说，一个完成的查询遵循下面的模式: 

1
<模型类>.query.<过滤方法>.<查询方法>

从某个模型类出发，通过在 query 属性对应的 Query 对象上附加的过滤方法和查询函数对模型类对应的表中的记录进行各种筛选和调整，最终返回包含对应数据库记录数据的模型类实例，对返回的实例调用属性即可获取对应的字段数据。

SQLAlchemy 提供了许多查询方法用来获取记录，下表列出了常用的查询方法

查询方法 说明
all() 返回包含所有查询记录的列表
first() 返回查询的第一条记录，如果未找到，则返回 None
one() 返回第一条记录，且仅允许有一条记录。如果记录数量大于 1 或小于 1，则抛出错误
get(id) 传入主键值作为参数，返回指定主键值的记录，如果未找到，则返回 None
count() 返回查询结果的数量
one_or_one() 类似 one()，如果结果数量部位 1，返回 None
first_or_404() 返回查询的第一条记录，如果未找到，则返回 404 错误响应
get_or_404(id) 传入主键值作为参数，返回指定主键值的记录，如果未找到，则返回 404 错误响应
paginate() 返回一个 Pagination 对象，可以对记录进行分页处理
with_parent(instance) 传入模型类实例作为参数，返回和这个实例相关联的对象

下面是对 Note 类进行查询的几个示例。all() 返回所有记录: 

1
2
>>> Note.query.all()
[<Note 1>, <Note 2>, <Note 3>]

first() 返回第一条记录: 

1
2
3
4
5
6
7
>>> note1
<Note 1>
>>> note1 = Note.query.first()
>>> note1
<Note 1>
>>> note1.body
'remember Sammy Jankis'

get() 返回指定主键值(id 字段)的纪律: 

1
2
3
4
5
>>> note2
<Note 2>
>>> note2 = Note.query.get(2)
>>> note2
<Note 2>

count() 返回记录的数量: 

1
2
>>> Note.query.count()
3

SQLAlchemy 还提供了许多过滤方法，使用这些过滤方法可以获取更精确的查询，比如获取指定字段值的记录。
对模型类的 query 属性存储的 Query 对象调用过滤方法将返回一个更精确的 Query 对象.
因为每个过滤方法都会返回新的查询对象。所以过滤器可以叠加使用。
在查询对象上调用前面介绍的查询方法，即可获得一个包含过滤后的记录的列表。
常用的查询过滤方法如表所示:

过滤方法 说明
filter() 使用指定的规则过滤记录，返回新产生的查询对象
filter_by() 使用指定规则过滤记录(以关键字表达式的形式)，返回新产生的查询对象
order_by() 根据指定条件对记录进行排序，返回新产生的查询对象
limit(limit) 使用指定的值限制原查询返回的记录数量，返回新产生的查询对象
group_by() 根据指定条件对记录进行分组，返回新产生的查询对象
offset(offset) 使用指定的值偏移原查询的结果，返回新产生的查询对象

filter() 方法是最基础的查询方法。
它使用指定的规则来过滤记录，下面的示例在数据库里找出了 body 字段值为 “SHAVE” 的记录: 

1
2
>>> Note.query.filter(Note.body == 'SHAVE').first()
<Note 2>

直接打印查询对象或将其转换为字符串可以查看对应的 SQL 语句: 

1
2
3
4
>>> print(Note.query.filter(Note.body == 'SHAVE'))
SELECT note.id AS note_id, note.body AS note_body 
FROM note 
WHERE note.body = ?

在 filter() 方法中传入表达式时，除了 "==" 以及表示不等于的 "!="，其他常用的查询操作符以及使用示例如下所示:

LIKE: 

1
2
>>> Note.query.filter(Note.body.like('%HA%')).first()
<Note 2>

IN: 

1
2
>>> Note.query.filter(Note.body.in_(['foo', 'SHAVE', 'hhh'])).first()
<Note 2>

NOT IN: 

1
2
>>> Note.query.filter(~Note.body.in_(['foo', 'SHAVE', 'hhh'])).all()
[<Note 1>, <Note 3>]

AND:

使用 and_(): 

1
2
>>> Note.query.filter(and_(Note.body == 'SHAVE', Note.id != 1)).all()
[<Note 2>]

或在 filter() 中加入多个表达式，使用逗号分隔 filter(Note.body == 'foo', Note.title == 'FooBar')

或叠加调用多个 filter()/filter_by() 方法

filter(Note.body == 'foo').filter(Note.title == 'FooBar')

OR: 

1
2
from sqlalchemy import or_
filter(or_(Note.body == 'foo', Note.body == 'bar'))

和 filter() 方法相比，filter_by() 方法更易于使用。
在 filter_by() 方法中，你可以使用关键字表达式来指定过滤规则。
更方便的是，你可以在这个过滤器中直接使用字段名称。下面的示例使用 filter_by() 过滤器完成了同样的任务: 

1
2
>>> Note.query.filter_by(body='SHAVE').first()
<Note 2>

Update

更新一条记录非常简单，直接赋值给模型类的字段属性就可以改变字段值，然后调用 commit() 方法提交会话即可。
下面的示例改变了一条记录的 body 字段的值: 

1
2
3
4
5
>>> note = Note.query.get(2)
>>> note.body
'SHAVE'
>>> note.body = 'SHAVE LEFT THIGH'
>>> db.session.commit()

提示:
只有要插入新的记录或要将现有的记录添加到会话中时才需要使用 add() 方法，单纯要更新现有的记录时只需要直接为属性赋新值，然后提交会话。

Delete

删除记录和添加记录很相似，不过要把 add() 方法换成 delete() 方法，最后都需要调用 commit() 方法提交修改。
下面的示例删除了 id(主键)为 2 的记录: 

1
2
3
>>> note = Note.query.get(2)
>>> db.session.delete(note)
>>> db.session.commit()

在视图函数里操作数据库

定义关系

更新数据库表