---

show: step
version: 1.0
enable_checker: true

---

SQLAlchemy 查询语句

知识点

• query 查询语句
• like 方法模糊查询
• and_ 、or_ 多条件查询
• order_by 排序
• limit 限制查询数量
• join 联结查询

课程简介

本节实验主要讲解 SQLAlchemy 的查询方法，讲解主要基于前两节的数据。通常我们对数据库的使用主要有三步：创建数据表，添加数据，查询数据。前两节实验完成了前两步的介绍，接下来我们针对查询尤其是过滤查询进行深入探讨。

首先在 MySQL 客户端中删除数据库 study 并重建：

mysql> DROP SCHEMA IF EXISTS study;  # SCHEMA 等同于 DATABASE
Query OK, 5 rows affected (0.23 sec)

mysql> CREATE SCHEMA study CHARACTER SET = UTF8;
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql>

然后在终端命令行执行 Python 脚本创建数据表、添加数据，脚本代码参见上一节实验文档：

$ python3 db.py
$ python3 create_data.py

基本查询语句

我们使用命令行交互解释器 ipython 来学习查询语句：

# 首先从 create_data.py 中引入所需对象
In [1]: from create_data import session, User, Course, Lab, Tag

# session 的 query 方法接收类作为参数，.all 表示获取全部实例
# 不论查询结果是一个还是多个，返回值都是列表，查不到的话返回值是空列表
# 等同于 MySQL 语句：SELECT * FROM user;
In [2]: session.query(User).all()
Out[2]:
[<User: 王雷>,
 <User: 朱华>,
 <User: 汪林>,
 <User: 夏晶>,
 <User: 蒋桂珍>,
 <User: 宁兵>,
 <User: 梁龙>,
 <User: 李博>,
 <User: 段欣>,
 <User: 刘文>]

# 查询第一条数据
In [3]: session.query(User).first()
Out[3]: <User: 王雷>

# filter 方法过滤查询
In [4]: session.query(User).filter(User.name=='王雷').first()
Out[4]: <User: 王雷>

# filter_by 方法也是常用的过滤方法，且写法更为简洁
In [5]: session.query(User).filter_by(name='王雷').first()
Out[5]: <User: 王雷>

# filter 方法支持 >、>=、<、<=、==、!= 等比较符号
In [6]: session.query(User).filter(User.name!='王雷').all()
Out[6]:
[<User: 朱华>,
 <User: 汪林>,
 <User: 夏晶>,
 <User: 蒋桂珍>,
 <User: 宁兵>,
 <User: 梁龙>,
 <User: 李博>,
 <User: 段欣>,
 <User: 刘文>]

In [7]: session.query(User).filter(User.id>=6).all()
Out[7]: [<User: 宁兵>, <User: 梁龙>, <User: 李博>, <User: 段欣>, <User: 刘文>]

# filter_by 方法支持多条件查询
In [8]: session.query(User).filter_by(name='王雷', id=1).all()
Out[8]: [<User: 王雷>]

# 查询 User 表中全部数据的 name 值
In [9]: session.query(User.name).all()
Out[9]:
[('刘文'),
 ('夏晶'),
 ('宁兵'),
 ('朱华'),
 ('李博'),
 ('梁龙'),
 ('段欣'),
 ('汪林'),
 ('王雷'),
 ('蒋桂珍')]

# 注意上一个命令的查询结果列表中每个元素的数据类型是 namedtuple
# 当然也可以理解为元组，取下标为 0 的元素就是 name 值
In [10]: for i in session.query(User.name).all():
    ...:     print(i)
    ...:     print(i.name)
    ...:
('刘文',)
刘文
('夏晶',)
夏晶
('宁兵',)
宁兵
('朱华',)
朱华
('李博',)
李博
('梁龙',)
梁龙
('段欣',)
段欣
('汪林',)
汪林
('王雷',)
王雷
('蒋桂珍',)
蒋桂珍

like 方法进行模糊查询，% 匹配任意数量的任意字符，_ 匹配单个任意字符：

# 查询邮箱为谷歌邮箱的 User 实例
In [12]: session.query(User).filter(User.email.like('%gmail%')).all()
Out[12]: [<User: 王雷>, <User: 朱华>, <User: 汪林>, <User: 段欣>, <User: 刘文>]

# 查询邮箱第二个字符为 i 的 User 实例
In [13]: session.query(User).filter(User.email.like('_i%')).all()
Out[13]: [<User: 王雷>, <User: 夏晶>, <User: 宁兵>, <User: 李博>]

in_ 方法查询某个字段的值数据某个列表的数据：

# 如果列表中的字段在 name 列中查不到，并不会报错
In [14]: session.query(User).filter(User.name.in_(['王雷', '夏晶', 'xxx'])).all()
Out[14]: [<User: 夏晶>, <User: 王雷>]

and_ 方法进行多条件查询，需要引入，等同于上面 filter_by 方法：

In [15]: from sqlalchemy import and_

# 查询符合全部条件的数据
In [16]: session.query(User).filter(and_(User.name=='王雷', User.id==1)).all()
Out[16]: [<User: 王雷>]

or_ 也是多条件查询，符合任一条件即可，也是需要引入：

In [24]: from sqlalchemy import or_

# 查询符合任一条件的数据
In [25]: session.query(User).filter(or_(
    ...:     User.email=='na55@zhouwan.cn',
    ...:     User.id==1
    ...: )).all()
Out[25]: [<User: 王雷>, <User: 梁龙>]

高级查询语句

下面介绍 SQLAlchemy 的排序、设置查询数量、联结查询等操作。

排序

使用 order_by 方法根据某一字段值进行排序：

# 查询全部 User 实例，按实例的 email 值排序
In [27]: session.query(User).order_by(User.email).all()
Out[27]:
[<User: 汪林>,
 <User: 刘文>,
 <User: 段欣>,
 <User: 夏晶>,
 <User: 梁龙>,
 <User: 王雷>,
 <User: 李博>,
 <User: 宁兵>,
 <User: 蒋桂珍>,
 <User: 朱华>]
In [28]: for user in session.query(User).order_by(User.email).all():
    ...:     print(user.email)
    ...:
caoli@gmail.com
fang72@gmail.com
heyang@gmail.com
minzhao@meng.cn
na55@zhouwan.cn
pingmao@gmail.com
xiaping@hotmail.com
xiuying77@hotmail.com
yang59@hotmail.com
ytao@gmail.com

# desc 方法进行降序排序，默认为升序
In [29]: session.query(User).order_by(User.email.desc()).all()
Out[29]:
[<User: 朱华>,
 <User: 蒋桂珍>,
 <User: 宁兵>,
 <User: 李博>,
 <User: 王雷>,
 <User: 梁龙>,
 <User: 夏晶>,
 <User: 段欣>,
 <User: 刘文>,
 <User: 汪林>]

等同于 MySQL 中的 ORDER BY 语句：

mysql> SELECT * FROM user ORDER BY email;
+----+-----------+-----------------------+
| id | name      | email                 |
+----+-----------+-----------------------+
|  3 | 汪林      | caoli@gmail.com       |
| 10 | 刘文      | fang72@gmail.com      |
|  9 | 段欣      | heyang@gmail.com      |
|  4 | 夏晶      | minzhao@meng.cn       |
|  7 | 梁龙      | na55@zhouwan.cn       |
|  1 | 王雷      | pingmao@gmail.com     |
|  8 | 李博      | xiaping@hotmail.com   |
|  6 | 宁兵      | xiuying77@hotmail.com |
|  5 | 蒋桂珍    | yang59@hotmail.com    |
|  2 | 朱华      | ytao@gmail.com        |
+----+-----------+-----------------------+
10 rows in set (0.01 sec)

mysql> SELECT email FROM user ORDER BY email;
+-----------------------+
| email                 |
+-----------------------+
| caoli@gmail.com       |
| fang72@gmail.com      |
| heyang@gmail.com      |
| minzhao@meng.cn       |
| na55@zhouwan.cn       |
| pingmao@gmail.com     |
| xiaping@hotmail.com   |
| xiuying77@hotmail.com |
| yang59@hotmail.com    |
| ytao@gmail.com        |
+-----------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM user ORDER BY email DESC;
+----+-----------+-----------------------+
| id | name      | email                 |
+----+-----------+-----------------------+
|  2 | 朱华      | ytao@gmail.com        |
|  5 | 蒋桂珍    | yang59@hotmail.com    |
|  6 | 宁兵      | xiuying77@hotmail.com |
|  8 | 李博      | xiaping@hotmail.com   |
|  1 | 王雷      | pingmao@gmail.com     |
|  7 | 梁龙      | na55@zhouwan.cn       |
|  4 | 夏晶      | minzhao@meng.cn       |
|  9 | 段欣      | heyang@gmail.com      |
| 10 | 刘文      | fang72@gmail.com      |
|  3 | 汪林      | caoli@gmail.com       |
+----+-----------+-----------------------+
10 rows in set (0.00 sec)

设置查询数量

使用 limit 方法限制查询数量：

# 按 id 字段降序排序，取前 4 条数据
In [30]: session.query(User).order_by(User.id.desc()).limit(4).all()
Out[30]: [<User: 刘文>, <User: 段欣>, <User: 李博>, <User: 梁龙>]

# 因为查询结果为有序可迭代对象，所以使用列表的切片也是可以的
# 但 limit 方法更可取，因为后者只查询前 4 条数据，对内存的压力要小很多
In [31]: session.query(User).order_by(User.id.desc()).all()[:4]
Out[31]: [<User: 刘文>, <User: 段欣>, <User: 李博>, <User: 梁龙>]

对应的 MySQL 语句如下：

mysql> SELECT * FROM user ORDER BY id DESC LIMIT 4;
+----+--------+---------------------+
| id | name   | email               |
+----+--------+---------------------+
| 10 | 刘文   | fang72@gmail.com    |
|  9 | 段欣   | heyang@gmail.com    |
|  8 | 李博   | xiaping@hotmail.com |
|  7 | 梁龙   | na55@zhouwan.cn     |
+----+--------+---------------------+
4 rows in set (0.00 sec)

count 方法统计查询数量：

In [33]: session.query(User).filter(User.email.like('%gmail%')).all()
Out[33]: [<User: 王雷>, <User: 朱华>, <User: 汪林>, <User: 段欣>, <User: 刘文>]

# 注意结尾没有 .all() 了
In [34]: session.query(User).filter(User.email.like('%gmail%')).count()
Out[34]: 5

对应的 MySQL 语句：

mysql> SELECT COUNT(*) AS COUNT FROM user WHERE email LIKE '%gmail%';
+-------+
| COUNT |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

联结查询

使用 join 方法进行联结查询：

# 查询王雷老师的全部课程，即查询 course 表中的数据
# 条件是 name 值为 '王雷' 的 user 表外键关联的 course 表中的数据
In [26]: session.query(Course).join(User).filter(User.name=='王雷').all()
Out[26]: [<Course: 不能产品如何经营>, <Course: 责任标题表示女人>]

等同于 MySQL 中的 JOIN 语句：

mysql> SELECT course.id, course.name, user_id FROM course
    -> JOIN user ON user.id = course.user_id 
    -> WHERE user.name = '王雷';   
+----+--------------------------+---------+
| id | name                     | user_id |
+----+--------------------------+---------+
|  1 | 不能产品如何经营            |       1 |
|  2 | 责任标题表示女人            |       1 |
+----+--------------------------+---------+
2 rows in set (0.00 sec)

总结

本节课程主要包括以下知识点：

• query 查询语句
• like 方法模糊查询
• and_ 、or_ 多条件查询
• order_by 排序
• limit 限制查询数量
• join 联结查询

本篇教程带领大家使用 SQLAlchemy 创建了一个与课程相关的数据库及四张不同关系的数据表，在此过程中，介绍了使用 SQLAlchemy 定义一对一、一对多、多对多关系，数据的增删改查以及使用 Faker 生成测试数据，还有对数据库进行各种查询的方法。对课程有任何疑问欢迎到讨论组里提出。