---

show: step
version: 1.0
enable_checker: true

---

SQLAlchemy 简介和基本用法

知识点

• SQL 和 ORM
• SQLAlchemy 简介与安装
• 连接数据库的引擎和创建映射类的声明基类
• 创建映射类
• 生成数据表
• 使用 faker 库生成测试数据

课程准备

本课程基于 MySQL 数据库向大家介绍 SQLAlchemy 的使用。学习课程之前，需要掌握 Python3 基础语法，MySQL 创建数据库、表和对数据的增删改查等操作以及各种约束的作用和用法。

SQL 和 ORM

数据库 DataBase 是按照数据结构来组织、存储和管理数据的仓库，它的产生距今已有六十多年。随着信息技术和市场的发展，数据库变得无处不在：它在电子商务、银行系统等众多领域都被广泛使用，且成为其系统的重要组成部分。

SQL 是英文 Structured Query Language 的缩写，意为结构化查询语言。SQL 的主要功能就是同各种数据库建立联系，进行沟通。按照美国国家标准协会的规定，SQL 被作为关系型数据库管理系统的标准语言。

MySQL 数据库中的表有行和列的概念，例如某个用户表的数据如下：

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Javy      |
|  2 | Nash      |
|  3 | Yanxue    |
|  4 | Wujing    |
|  5 | Thmos     |
+----+-----------+

ORM 是英文 Object Relational Mapping 的缩写，意为对象关系映射。对应到 Python 中，我们可以设计一个类把上文关系数据库的表结构映射到该类上：

class User:
    def __init__(self, id, name):
        self.id = id
        self.name = name

[
    User(1, 'Javy'),
    User(2, 'Nash'),
    User(3, 'Yanxue'),
    User(4, 'Wujing'),
    User(5, 'Thmos')
]

要实现 MySQL 数据表到 Python 类的映射，就需要 ORM 框架的帮助。在 Python 中有多个选择，例如轻量的 SQLobject 框架、Storm 框架、Django 内置的 ORM、著名的 SQLAlchemy 以及基于前者的 flask 插件 flask-SQLAlchemy 等。

SQLAlchemy 简介与安装

SQLAlchemy 是针对 Python 编程语言经过 MIT 许可的开源 ORM 框架，它采用数据库映射模式提供了企业级的持久化功能，专为高效率和高性能的数据库访问设计，是一个简单的针对 Python 语言的完整套件”。它是 Python 中最有名的 ORM 框架。

SQLAlchemy 作为 Python 工具包，可以使用包管理工具 pip 来安装，终端执行以下命令即可：

$ sudo pip3 install sqlalchemy

启动 MySQL 服务，打开 MySQL 客户端，实验环境中默认用户为 shiyanlou，未对 MySQL 设置密码：

102.png

使用 SQLAlchemy 创建映射类

数据库的默认编码为 latin1，修改数据表的默认编码是 MySQL 的一个基本操作，这是需要预先掌握的。不过学习本课程时并不需要这么做，在创建数据库的同时添加 CHARACTER SET = UTF8 指定编码格式即可。我们要创建课程相关的映射类以及对应的数据表，现在先创建所需数据库 study ，编码格式为 UTF-8 ：

103.png

上图所示，三个 MySQL 语句作用分别为：

• 查看 MySQL 数据库的默认编码格式
• 新建数据库 study 并设置编码格式为 UTF-8
• 查看数据库的编码是否正确

引擎和声明基类

使用 SQLAlchemy 连接数据库需要引擎，创建引擎使用 create_engine 方法：

from sqlalchemy import create_engine

# 参数字符串说明：数据库类型+驱动://用户名:密码@主机:端口号/数据库名字?charset=编码格式
# mysql 自带驱动，密码未设定，端口号可省略
engine = create_engine('mysql://root@localhost/study?charset=utf8')

创建映射类需要继承声明基类，使用 declarative_base ：

from sqlalchemy.ext.declarative import declarative_base

# 创建声明基类时传入引擎
Base = declarative_base(engine)

创建第一个映射类

创建映射类须继承声明基类。首先创建 user 数据表的映射类，此表存放用户数据，也就是课程作者的数据：

# Column 定义字段，Integer、String 分别为整数和字符串数据类型
from sqlalchemy import Column, Integer, String


class User(Base):           # 继承声明基类
    __tablename__ = 'user'  # 设置数据表名字，不可省略
    id = Column(Integer, primary_key=True)   # 设置该字段为主键
    # unique 设置唯一约束，nullable 设置非空约束
    name = Column(String(64), unique=True, nullable=False)
    email = Column(String(64), unique=True)

    # 此特殊方法定义实例的打印样式
    def __repr__(self):
        return '<User: {}>'.format(self.name)

一对多关系

现在创建第二个映射类 Course，它对应的数据表 course 存放课程数据。一个课程作者可以创建多个课程，一个课程对应唯一的课程作者，这种关系被称为一对多或者多对一关系，这是最常用的数据表关系类型：

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


class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    # ForeignKey 设置外键关联，第一个参数为字符串，user 为数据表名，id 为字段名
    # 第二个参数 ondelete 设置删除 User 实例后对关联的 Course 实例的处理规则
    # 'CASCADE' 表示级联删除，删除用户实例后，对应的课程实例也会被连带删除
    user_id = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))
    # relationship 设置查询接口，以便后期进行数据库查询操作
    # 第一个参数为位置参数，参数值为外键关联的映射类名，数据类型为字符串
    # 第二个参数 backref 设置反向查询接口
    # backref 的第一个参数 'course' 为查询属性，User 实例使用该属性可以获得相关课程实例的列表
    # backref 的第二个参数 cascade 如此设置即可实现 Python 语句删除用户数据时级联删除课程数据
    user = relationship('User', 
            backref=backref('course', cascade='all, delete-orphan'))

    def __repr__(self):
        return '<Course: {}>'.format(self.name)

创建数据表

声明基类 Base 在创建之后并不会主动连接数据库，因为它的默认设置为惰性模式。Base 的 metadata 有个 create_all 方法，执行此方法会主动连接数据库并创建全部数据表，完成之后自动断开与数据库的连接：

Base.metadata.create_all()

完整代码

总结以上内容，写入 db.py 文件：

# File Name：db.py

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

engine = create_engine('mysql://root@localhost/study?charset=utf8')
Base = declarative_base(engine)


class User(Base):  
    __tablename__ = 'user'  
    id = Column(Integer, primary_key=True) 
    name = Column(String(64), unique=True, nullable=False)
    email = Column(String(64), unique=True)

    def __repr__(self):
        return '<User: {}>'.format(self.name)


class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    user_id = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))
    user = relationship('User',
            backref=backref('course', cascade='all, delete-orphan'))

    def __repr__(self):
        return '<Course: {}>'.format(self.name)


if __name__ == '__main__':
    # 使用声明基类的 metadata 对象的 create_all 方法创建数据表：
    Base.metadata.create_all()

运行程序

在终端使用 Python 解释器运行文件，在此之前先安装一个必要的依赖包 mysqlclient，该依赖包的作用是连接数据库：

$ sudo pip3 install mysqlclient  # 安装依赖包
$ python3 db.py                  # 运行文件，使用映射类创建对应的数据表

在 MySQL 客户端可以查看到新建的两张表：

mysql> USE study
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_study |
+-----------------+
| course          |
| user            |
+-----------------+
2 rows in set (0.00 sec)

添加测试数据

测试数据的创建需要用到 Python 的 faker 库，使用 pip3 安装先：

sudo pip3 install faker

这是一个用法简单功能强大的伪造数据的库，大家可以在命令行交互解释器 ipython 中进行测试：

In [38]: from faker import Faker  # 引入 Faker 类

# 创建实例，添加参数 'zh-cn' 是为了伪造中文数据
# 该实例叫做工厂对象，它可以使用各种各样的方法伪造数据
In [39]: fake = Faker('zh-cn')       

In [40]: fake.name()     # 伪造姓名
Out[40]: '房明'

In [41]: fake.address()  # 伪造地址
Out[41]: '山西省梅市上街董路Q座 238175'

In [42]: fake.email()    # 伪造邮箱
Out[42]: 'oqiu@rn.net'

In [43]: fake.url()      # 伪造 URL
Out[43]: 'https://www.yuzhu.cn/'

In [44]: fake.date()     # 伪造日期
Out[44]: '2012-07-03'

session 处理数据

上文已经介绍了使用映射类创建数据表要用声明基类 Base，那么处理数据用什么呢？要用到 session，它是 sessionmaker 类的实例，该实例实现了 __call__ 方法，本身可以作为函数来执行，返回值就是能够处理数据的 session：

from sqlalchemy.orm import sessionmaker

# 从 db.py 文件中引入下列对象备用
from db import Base, engine, User, Course

# 将 engine 引擎作为参数创建 session 实例
session = sessionmaker(engine)()

当我们创建了 session 实例，就启动了一个操作 MySQL 数据库的会话。

生成测试数据

接下来创建 5 个课程作者，也就是 5 个 User 类的实例，每个作者对应两个课程，共 10 个 Course 类实例。将以下代码写入测试数据的 Python 文件中，文件名为 create_data.py ：

# File Name: create_data.py

from sqlalchemy.orm import sessionmaker
from faker import Faker
from db import Base, engine, User, Course

session = sessionmaker(engine)()
fake = Faker('zh-cn')

def create_users():
    for i in range(10):
        # 创建 10 个 User 类实例，伪造 name 和 email
        user = User(name=fake.name(), email=fake.email())
        # 将实例添加到 session 会话中，以备提交到数据库
        # 注意，此时的 user 对象没有 id 属性值
        # 映射类的主键字段默认从 1 开始自增，在传入 session 时自动添加该属性值
        session.add(user)

def create_courses():
    # session 有个 query 方法用来查询数据，参数为映射类的类名
    # all 方法表示查询全部，这里也可以省略不写
    # user 就是上一个函数 create_users 中的 user 对象
    for user in session.query(User).all():
        # 两次循环，对每个作者创建两个课程
        for i in range(2):
            # 创建课程实例，name 的值为 8 个随机汉字
            course = Course(name=''.join(fake.words(4)), user_id=user.id)
            session.add(course)

def main():
    # 执行两个创建实例的函数，session 会话内就有了这些实例
    create_users()
    create_courses()
    # 执行 session 的 commit 方法将全部数据提交到对应的数据表中
    session.commit()

if __name__ == '__main__':
    main()

完成后，可以在终端执行 python3 create_data.py 来创建数据。为了便于查看代码的执行情况，不这样做，我们在 ipython 中引入这些函数，依次执行它们来查看细节：

# 引入相关对象
In [1]: from create_data import User, Course, session, create_users, create_courses

# 执行创建 User 实例的函数
In [2]: create_users()

# session 查询结果为列表，每个元素就是一个 User 实例
In [3]: session.query(User).all()
Out[3]:
[<User: 安颖>,
 <User: 赵琴>,
 <User: 李英>,
 <User: 邢想>,
 <User: 高玲>,
 <User: 戴晶>,
 <User: 卢建平>,
 <User: 陈强>,
 <User: 姜帆>,
 <User: 包柳>]

# 将某个 User 实例赋值给 user 变量
In [4]: user = session.query(User).all()[3]

# 查看属性
In [5]: user.name
Out[5]: '邢想'

In [6]: user.id
Out[6]: 4

# 执行创建 Course 实例的函数
In [7]: create_courses()

# 查看前 4 个 Course 实例的 name 属性
In [8]: for course in session.query(Course)[:4]:
   ...:     print(course.name)
   ...:
开发中文电子新闻
怎么发布结果详细
你的只要非常如果
次数通过评论等级

# User 实例的 course 属性为查询接口，通过 relationship 设置
# 属性值为列表，里面是两个课程实例
In [9]: user.course
Out[9]: [<Course: 上海这么国际时候>, <Course: 对于技术两个你们>]

# 将某个课程实例赋值给 course 变量
In [10]: course = session.query(Course)[12]

# 课程实例的 user 属性为查询接口，通过 relationship 设置
In [11]: course.user
Out[11]: <User: 卢建平>

# 将全部实例提交到对应的数据表
In [12]: session.commit()

In [13]:

查看 MySQL 数据库中各表的数据：

mysql> SELECT * FROM user;
+----+-----------+----------------------+
| id | name      | email                |
+----+-----------+----------------------+
|  1 | 安颖       | fanxiuying@weiyin.cn |
|  2 | 赵琴       | tao10@hotmail.com    |
|  3 | 李英       | xia68@gangqiang.cn   |
|  4 | 邢想       | minyi@yahoo.com      |
|  5 | 高玲       | maoqiang@pa.cn       |
|  6 | 戴晶       | xlin@yahoo.com       |
|  7 | 卢建平     | dqian@yahoo.com      |
|  8 | 陈强       | yuanjun@guiying.cn   |
|  9 | 姜帆       | xiayan@tx.cn         |
| 10 | 包柳       | qiang78@hotmail.com  |
+----+-----------+----------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM course;
+----+--------------------------+---------+
| id | name                     | user_id |
+----+--------------------------+---------+
|  1 | 开发中文电子新闻            |       1 |
|  2 | 怎么发布结果详细            |       1 |
|  3 | 你的只要非常如果            |       2 |
|  4 | 次数通过评论等级            |       2 |
|  5 | 经验方式文件根据            |       3 |
|  6 | 资料你们各种类别            |       3 |
|  7 | 上海这么国际时候            |       4 |
... ...
+----+--------------------------+---------+
20 rows in set (0.00 sec)

接下来我们在 ipython 中删除 user 实例，验证级联删除功能是否生效：

In [13]: session.delete(user)

In [14]: session.commit()

In [15]:

查看数据表的情况，一如预期，user 表中 id 为 4 的行被删除，course 表中 user_id 为 4 的行也被删除：

mysql> SELECT * FROM user;
+----+-----------+----------------------+
| id | name      | email                |
+----+-----------+----------------------+
|  1 | 安颖      | fanxiuying@weiyin.cn |
|  2 | 赵琴      | tao10@hotmail.com    |
|  3 | 李英      | xia68@gangqiang.cn   |
|  5 | 高玲      | maoqiang@pa.cn       |
|  6 | 戴晶      | xlin@yahoo.com       |
|  7 | 卢建平    | dqian@yahoo.com      |
|  8 | 陈强      | yuanjun@guiying.cn   |
|  9 | 姜帆      | xiayan@tx.cn         |
| 10 | 包柳      | qiang78@hotmail.com  |
+----+-----------+----------------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM course WHERE user_id = 4;
Empty set (0.00 sec)

mysql>

总结

本节所讲知识点：

• SQL 和 ORM
• SQLAlchemy 简介与安装
• 连接数据库的引擎和创建映射类的声明基类
• 创建映射类
• 生成数据表
• 使用 faker 库生成测试数据

本节课程介绍了 SQLAlchemy 的作用，演示了创建映射类、数据表、相关数据的完整流程。下一节课我们来学习创建一对一和多对多关系的数据表。