9-数据库操作
实现数据持久化：数据库操作
在前面的章节中，已经介绍了Python编程的基础知识，包括变量、数据类型、选择结构、循环结构、函数、列表、字典、元组、面向对象编程、文件操作和模块等内容。能够编写程序来处理各种数据，但是有一个问题：程序运行结束后，数据就消失了。比如编写了一个学生成绩管理程序，运行时可以录入成绩、查询成绩，但是一旦关闭程序，所有录入的成绩数据就全部丢失了，下次运行还得重新录入。这显然不是想要的结果。
要解决这个问题，就需要把数据"永久保存"起来，这就是"数据持久化"的思想。在第八章学习了文件操作，可以把数据保存到文本文件中，这是一种简单的数据持久化方式。但是当数据量很大、数据之间的关系比较复杂时，文件操作就显得力不从心了。比如，一个学校有几万名学生的信息，还要记录每个学生选了哪些课程、每门课程的成绩是多少，如果用文件来管理这些数据，查找和修改都会非常困难。
数据库技术就是为解决大规模数据存储和管理问题而诞生的。数据库就像一个超级"文件柜"，可以高效地存储、组织、查询和管理大量数据。MySQL是目前最流行的开源关系型数据库之一，而Python通过pymysql模块可以方便地连接和操作MySQL数据库。本章将介绍数据库的基本概念，学习如何安装和配置MySQL，掌握使用Python操作MySQL数据库的方法，最终能够独立开发一个完整的数据库应用程序。
【学习目标】
知识目标
理解数据库、数据库管理系统、关系型数据库等基本概念。
了解MySQL数据库的安装、配置和基本操作方法。
掌握pymysql模块的安装和使用方法。
掌握SQL语言的基本语法，包括CREATE、INSERT、SELECT、UPDATE、DELETE等语句。
能力目标
能够使用Python连接MySQL数据库，完成基本的数据库操作。
能够设计简单的数据库表结构，并使用SQL语句进行数据的增删改查。
能够结合列表、字典等数据结构，处理数据库查询结果。
能够开发基于数据库的简单信息管理系统。
素养目标
培养数据安全意识和信息保密意识，养成良好的数据管理习惯。
树立依法依规使用数据的观念，尊重数据隐私和知识产权。
培养严谨细致的工作作风，注重数据操作的准确性和安全性。
MySQL数据库概述
什么是数据库
在日常生活中，会和各种各样的数据打交道。去超市购物，收银台会扫描商品条码、计算总价、打印小票；去图书馆借书，管理员会查询你的借阅记录、办理借还手续；在学校里，教务系统会记录每个学生的选课信息、考试成绩。这些场景背后，都需要一个系统来高效地存储和管理大量的数据，这个系统就是数据库。
数据库（Database，简称DB）是按照一定的数据模型组织、存储和管理数据的仓库。简单来说，数据库就像一个有组织的"电子文件柜"，它把相关的数据按照一定的规则存放在一起，方便随时查找、添加、修改和删除。与普通的文件相比，数据库有以下几个显著的优势：
第一，存储容量大。一个文本文件通常只能存储几千到几万条数据，而数据库可以轻松存储数百万甚至数十亿条数据。比如，淘宝网每天要处理数十亿条交易数据，这些数据都存储在数据库中。
第二，查询速度快。如果要在文件中查找某个特定数据，可能需要从头到尾遍历整个文件，效率很低。而数据库使用了索引等技术，可以在海量数据中快速定位到目标数据，查询速度非常快。
第三，支持并发访问。文件一般一次只能被一个程序读写，而数据库允许多个用户同时访问和操作数据。比如，多个收银员可以同时使用同一个商品数据库进行结账操作，互不影响。
第四，数据安全性高。数据库提供了用户权限管理、数据备份、事务处理等安全机制，可以有效保护数据不被丢失或篡改。而文件一旦被误删或损坏，恢复起来非常困难。
数据库管理系统（Database Management System，简称DBMS）是管理数据库的软件系统，它提供了创建、查询、更新、删除数据等功能。常见的数据库管理系统有MySQL、Oracle、SQL Server、SQLite等。本书选择MySQL作为教学数据库，因为它是免费开源的、功能强大、使用广泛，非常适合初学者学习。
认识MySQL
MySQL是全球最流行的开源关系型数据库管理系统之一。它最早由瑞典MySQL AB公司开发，目前属于Oracle公司旗下产品。MySQL以其体积小、速度快、总体拥有成本低等特点，被广泛应用于各类Web应用程序中，包括淘宝、百度、新浪等知名网站都在使用MySQL。
所谓"关系型数据库"，是指数据以表格（Table）的形式组织存储，表格之间可以建立关联关系。就像日常使用的Excel表格一样，数据按照行和列排列。比如，一个学生信息表可能包含学号、姓名、性别、年龄、专业等列，每一行代表一个学生的信息。关系型数据库的核心概念包括以下几个：
数据库（Database）：数据的容器，一个数据库管理系统可以管理多个数据库。比如，一个学校可以创建一个"学生管理系统"数据库，里面包含学生表、课程表、成绩表等。
数据表（Table）：数据的组织形式，由行（Row）和列（Column）组成。每一行称为一条"记录"，每一列称为一个"字段"。比如，学生表的每一行是一个学生的完整信息，每一列是学生的某个属性（如姓名、年龄等）。
主键（Primary Key）：唯一标识表中每一行记录的字段。比如，学生表中的"学号"字段可以作为主键，因为每个学生的学号都是唯一的，不会重复。主键的作用就像身份证号一样，通过它可以准确地找到某一条特定的记录。
外键（Foreign Key）：用于建立表与表之间关联关系的字段。比如，成绩表中可以包含一个"学号"字段作为外键，指向学生表中的"学号"主键，这样就可以通过成绩表找到对应的学生信息。
安装与配置MySQL
要使用MySQL数据库，首先需要在电脑上安装MySQL软件。MySQL的安装过程比较简单，下面以Windows系统为例，介绍MySQL的安装步骤。
第一步，下载MySQL安装包。打开MySQL官方网站（https://dev.mysql.com/downloads/），选择"MySQL Installer for Windows"。建议下载MySQL 8.0版本，这是目前最稳定的版本。
第二步，运行安装程序。双击下载的安装包，按照安装向导的提示一步步操作。在安装过程中，安装向导会要求设置root用户的密码，这个密码非常重要，请务必记住。root用户是MySQL的最高权限用户，类似于Windows系统的Administrator账户。建议设置一个简单易记的密码，方便后续学习使用。
第三步，配置环境变量。安装完成后，需要将MySQL的bin目录添加到系统的环境变量PATH中，这样就可以在命令行窗口中直接使用mysql命令。具体方法是：右键点击"此电脑"→"属性"→"高级系统设置"→"环境变量"，在"系统变量"中找到Path，点击"编辑"，添加MySQL的bin目录路径（如C:\Program Files\MySQL\MySQL Server 8.0\bin），如图9-1环境变量设置。
{width="3.5770833333333334in" height="3.426388888888889in"}
图9-1环境变量设置
第四步，验证安装。打开命令行窗口（按Win+R，输入cmd，回车），输入以下命令：
代码 9-1：验证MySQL安装
mysql --version
如果安装成功，会显示MySQL的版本号信息。然后输入以下命令登录MySQL：
mysql -u root -p
系统会提示你输入密码，输入安装时设置的root密码后，如果看到"mysql>"提示符，说明已经成功登录MySQL数据库。此时可以输入"exit"退出MySQL。如图9-2数据库MySQL安装验证。
{width="4.551388888888889in" height="2.797222222222222in"}
图9-2数据库MySQL安装验证
MySQL基本操作
MySQL提供了两种操作方式：命令行方式和图形化工具方式。命令行方式需要输入SQL语句来操作数据库，适合有一定基础的用户；图形化工具方式通过可视化界面操作数据库，更加直观方便，适合初学者。本节介绍使用图形化工具Navicat来操作MySQL数据库。
Navicat是一款流行的数据库管理工具，提供了直观的图形界面，可以方便地创建数据库、创建表、插入数据、查询数据等。Navicat有免费的教育版，可以在官网申请使用。安装Navicat后，按照以下步骤操作：
第一步，新建连接。打开Navicat，点击左上角的"连接"按钮，选择"MySQL"，在弹出的对话框中输入连接信息：连接名可以随意填写（如"本地MySQL"），主机名填写"localhost"（表示本机），端口填写"3306"（MySQL默认端口），用户名填写"root"，密码填写安装时设置的root密码。点击"测试连接"，如果提示连接成功，点击"确定"保存连接。如图9-3用Navicat连接数据库。
{width="4.358333333333333in" height="3.53125in"}
图9-3用Navicat连接数据库
第二步，创建数据库。在左侧的连接列表中双击刚才创建的连接，展开后可以看到已有的数据库列表。右键点击"本地MySQL"，选择"新建数据库"，在弹出的对话框中输入数据库名称（如"student_db"），字符集选择"utf8mb4"，排序规则选择"utf8mb4_general_ci"，点击"确定"完成创建。如图9-4用Navicat创建数据库。
{width="3.995833333333333in" height="3.0722222222222224in"}
图9-4用Navicat创建数据库
第三步，创建数据表。双击打开刚创建的"student_db"数据库，点击上方的"新建表"按钮，开始设计表结构。在表设计器中，可以添加字段、设置字段名、数据类型、是否为主键等。比如，创建一个学生表（students）。
表 9-1 学生表（students）结构设计
字段名 数据类型 说明 约束
id INT 学号 主键，自动增长
name VARCHAR(50) 姓名 不能为空
gender VARCHAR(10) 性别 无
age INT 年龄 无
major VARCHAR(50) 专业 无
设置好字段后，点击"保存"，输入表名"students"，完成表的创建，如图9-5用Navicat创建表。接下来，可以双击打开students表，直接在表格中输入数据，就像使用Excel一样方便。
{width="3.592361111111111in" height="3.7118055555555554in"}
图9-5用Navicat创建表
MySQL数据库连接
安装pymysql模块
Python本身不能直接操作MySQL数据库，需要通过专门的"驱动模块"来与数据库通信。pymysql就是这样一个驱动模块，它是纯Python实现的MySQL客户端库，安装简单、使用方便，非常适合教学和学习使用。安装pymysql模块的方法和安装其他第三方模块一样，在命令行窗口中输入以下命令：
代码 9-2：安装pymysql模块
pip install pymysql
安装过程中，命令行会显示下载进度和安装信息。安装完成后，可以通过以下命令验证是否安装成功：
代码 9-3：验证pymysql安装
import pymysql
print(pymysql.__version__)
如果没有报错并且打印出了版本号，说明pymysql已经安装成功。如果提示"ModuleNotFoundError: No module named 'pymysql'"，说明安装失败，请检查网络连接或尝试使用国内镜像源安装：pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple。
建立数据库连接
安装好pymysql模块后，就可以在Python程序中连接MySQL数据库了。连接数据库需要提供以下信息：数据库主机地址（host）、端口号（port）、用户名（user）、密码（password）和数据库名称（database）。下面是建立数据库连接的完整代码：
代码 9-4：建立数据库连接
import pymysql
# 建立数据库连接
conn = pymysql.connect(
host="localhost", # 数据库主机地址
port=3306, # 端口号
user="root", # 用户名
password="123456", # 密码
database="student_db" # 数据库名称
)
print("数据库连接成功！")
# 关闭连接
conn.close()
print("数据库连接已关闭。")
运行结果如图9-6用pymysql连接数据库。
图9-6用pymysql连接数据库
如果输出"数据库连接成功！"，说明Python已经成功连接到了MySQL数据库。pymysql.connect()函数的各个参数说明如下：host参数指定数据库服务器的地址，如果是本机数据库则填写"localhost"；port参数指定端口号，MySQL默认端口是3306；user参数指定登录数据库的用户名；password参数指定登录密码；database参数指定要连接的数据库名称。
在实际开发中，通常会将数据库连接信息集中管理，方便维护和修改。可以把这些信息存储在一个字典中，然后通过字典来传递参数。
代码 9-5：使用字典管理数据库连接配置
import pymysql
# 数据库连接配置（使用字典存储）
db_config = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "123456",
"database": "student_db"
}
# 使用 ** 解包字典作为连接参数
conn = pymysql.connect(**db_config)
print("数据库连接成功！")
conn.close()
这里用到了字典来存储数据库连接配置，使用时通过"**"运算符将字典解包为关键字参数传递给connect()函数。这样做的好处是，当需要修改连接信息时，只需要修改字典中的值即可，不需要改动连接代码。
关闭数据库连接
数据库连接是一种宝贵的资源，每次连接都会占用数据库服务器的内存和连接数。如果程序中打开了数据库连接却不关闭，会导致资源浪费，严重时甚至会使数据库服务器因为连接数过多而无法响应。因此，使用完数据库后，一定要及时关闭连接。
关闭连接的方法很简单，调用连接对象的close()方法即可。但是，在实际编程中，经常会遇到这样的情况：在操作数据库的过程中发生了异常（比如SQL语句写错了），程序直接崩溃退出，close()方法根本没有被执行到，连接就没有被关闭。为了解决这个问题，Python提供了with语句来自动管理资源的释放：
代码 9-6：使用with语句自动管理数据库连接
import pymysql
# 使用 with 语句自动管理连接
db_config = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "123456",
"database": "student_db"
}
try:
with pymysql.connect(**db_config) as conn:
print("数据库连接成功！")
# 在这里进行数据库操作...
print("数据库操作完成。")
# with 块结束后，连接会自动关闭
print("连接已自动关闭。")
except Exception as e:
print("数据库操作出错：", e)
使用with语句后，无论数据库操作是否成功，无论是否发生异常，连接都会在with代码块结束后自动关闭。这种方式更加安全可靠，是推荐的做法。同时，还使用了try...except语句来捕获可能出现的异常，避免程序因为数据库错误而崩溃。
MySQL数据库读写
连接上数据库之后，就可以对数据库进行各种操作了。数据库的基本操作可以概括为四个字------"增删改查"，英文缩写为CRUD（Create、Read、Update、Delete）。本节将详细介绍如何使用Python和SQL语句来完成这四种操作。
创建数据库和表
在进行数据操作之前，首先需要确保数据库和表已经创建好。虽然可以使用Navicat等图形化工具来创建，但也可以使用Python代码来创建，这样更加灵活，也更便于程序的自动化部署。创建数据库和表需要使用SQL（Structured Query Language，结构化查询语言）语句。SQL是操作关系型数据库的标准语言，几乎所有的关系型数据库都支持SQL。
下面是使用Python创建数据库和表的代码：
代码 9-7：创建数据库和表
import pymysql
# 先连接MySQL服务器（不指定数据库）
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="123456"
)
cursor = conn.cursor()
# 创建数据库（如果不存在）
cursor.execute("CREATE DATABASE IF NOT EXISTS student_db CHARACTER SET utf8mb4")
print("数据库创建成功！")
# 切换到该数据库
cursor.execute("USE student_db")
# 创建学生表
create_table_sql = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(10),
age INT,
major VARCHAR(50)
) CHARACTER SET utf8mb4
"""
cursor.execute(create_table_sql)
print("学生表创建成功！")
cursor.close()
conn.close()
这段代码中有几个重要的知识点需要理解。首先，cursor（游标）是数据库操作的核心对象，所有的SQL语句都需要通过游标来执行。可以把游标理解为一个"指挥棒"，通过它向数据库发送指令。创建游标的方法是调用连接对象的cursor()方法。
其次，执行SQL语句使用的是游标的execute()方法。注意，CREATE DATABASE和CREATE TABLE语句只需要执行一次，如果数据库或表已经存在，使用"IF NOT EXISTS"关键字可以避免重复创建导致的错误。
最后，"AUTO_INCREMENT"关键字表示该字段是自动增长的，每次插入新记录时不需要手动指定id的值，数据库会自动为其分配一个递增的编号。"PRIMARY KEY"关键字表示该字段是主键。"NOT NULL"关键字表示该字段的值不能为空。"VARCHAR(50)"表示该字段是可变长度字符串，最大长度为50个字符。
插入数据
表创建好之后，就可以向表中插入数据了。插入数据使用SQL的INSERT语句。下面是插入单条数据和批量插入多条数据的代码示例：
代码 9-8：插入数据
import pymysql
db_config = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "123456",
"database": "student_db"
}
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
# ---- 插入单条数据 ----
insert_sql = "INSERT INTO students (name, gender, age, major) VALUES (%s, %s, %s, %s)"
cursor.execute(insert_sql, ("张三", "男", 19, "计算机科学"))
conn.commit() # 提交事务
print("插入成功：张三")
# ---- 插入多条数据（使用元组列表） ----
students_data = [
("李四", "女", 20, "软件工程"),
("王五", "男", 18, "人工智能"),
("赵六", "女", 19, "数据科学"),
("钱七", "男", 20, "计算机科学"),
("孙八", "女", 19, "软件工程")
]
cursor.executemany(insert_sql, students_data)
conn.commit()
print("批量插入成功，共", cursor.rowcount, "条")
cursor.close()
conn.close()
这段代码中有两个非常重要的知识点。第一，SQL语句中的"%s"是参数占位符，不是字符串格式化。使用参数占位符可以防止SQL注入攻击，这是一种常见的安全漏洞。千万不要使用字符串拼接的方式来构造SQL语句，比如"INSERT INTO students VALUES ('" + name + "', ...)"，这样做非常不安全。
第二，conn.commit()方法用于提交事务。在MySQL中，对数据的修改操作（插入、更新、删除）默认不会立即生效，而是需要手动提交事务。这就好比编辑一个文档，修改的内容不会自动保存，需要点击"保存"按钮才会写入磁盘。如果忘记提交事务，数据修改将不会生效。
第三，executemany()方法可以一次性执行多条INSERT语句，比循环调用execute()效率更高。注意，传入的参数是一个包含多个元组的列表，每个元组代表一条记录的数据。
查询数据
查询数据是数据库操作中最常用的功能。查询数据使用SQL的SELECT语句。查询结果通过游标获取，可以使用fetchone()获取一条记录、fetchall()获取所有记录、fetchmany(n)获取n条记录。下面是查询数据的代码示例：
代码 9-9：查询数据
import pymysql
db_config = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "123456",
"database": "student_db"
}
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
# ---- 查询所有数据 ----
cursor.execute("SELECT * FROM students")
results = cursor.fetchall()
print("共查询到", len(results), "条记录")
print("学号\t姓名\t性别\t年龄\t专业")
for row in results:
print(row[0], "\t", row[1], "\t", row[2], "\t", row[3], "\t", row[4])
# ---- 条件查询 ----
cursor.execute("SELECT * FROM students WHERE major = %s", ("计算机科学",))
results = cursor.fetchall()
print("\n计算机科学专业的学生：")
for row in results:
print(row[1], "-", row[4])
cursor.close()
conn.close()
fetchall()方法返回的是一个包含元组的列表，每个元组代表一条记录。比如，results可能是这样的结构：[(1, '张三', '男', 19, '计算机科学'), (2, '李四', '女', 20, '软件工程'), ...]。可以通过索引来访问每个字段的值，比如row[0]是学号，row[1]是姓名，以此类推。
但是，通过索引访问字段值不够直观，容易出错（比如记不清第几个字段是姓名）。更好的做法是使用DictCursor（字典游标），它返回的每条记录是一个字典，可以通过字段名来访问值：
代码 9-10：使用字典游标查询数据
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="123456",
database="student_db"
)
# 创建字典游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM students")
results = cursor.fetchall()
print("使用字典游标查询结果：")
for row in results:
print("姓名：" + row["name"]
+ "，性别：" + row["gender"]
+ "，专业：" + row["major"])
cursor.close()
conn.close()
使用字典游标后，每条记录变成了一个字典，比如{"id": 1, "name": "张三", "gender": "男", "age": 19, "major": "计算机科学"}。通过row["name"]来访问姓名字段，代码更加清晰易读，也不容易出错。在实际开发中，推荐使用字典游标。
更新数据
更新数据使用SQL的UPDATE语句。更新操作可以修改表中已有记录的字段值。下面是更新数据的代码示例：
代码 9-11：更新数据
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="123456",
database="student_db"
)
cursor = conn.cursor()
# ---- 更新单条数据 ----
# 将张三的年龄更新为20岁
update_sql = "UPDATE students SET age = %s WHERE name = %s"
cursor.execute(update_sql, (20, "张三"))
conn.commit()
print("更新成功，影响", cursor.rowcount, "行")
# ---- 批量更新数据 ----
# 将所有软件工程专业学生的年龄加1
cursor.execute(
"UPDATE students SET age = age + 1 WHERE major = %s",
("软件工程",)
)
conn.commit()
print("批量更新成功，影响", cursor.rowcount, "行")
cursor.close()
conn.close()
UPDATE语句的语法是"UPDATE 表名 SET 字段名 = 新值 WHERE 条件"。其中，WHERE子句用于指定要更新的记录，如果不加WHERE子句，将会更新表中的所有记录。cursor.rowcount属性返回受影响的行数，可以用来判断更新操作是否成功。
删除数据
删除数据使用SQL的DELETE语句。删除操作会从表中移除符合条件的记录。下面是删除数据的代码示例：
代码 9-12：删除数据
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="123456",
database="student_db"
)
cursor = conn.cursor()
# ---- 删除单条数据 ----
# 删除名字为"钱七"的学生
delete_sql = "DELETE FROM students WHERE name = %s"
cursor.execute(delete_sql, ("钱七",))
conn.commit()
print("删除成功，影响", cursor.rowcount, "行")
# ---- 删除满足条件的所有数据 ----
# 删除年龄小于19岁的学生
cursor.execute(
"DELETE FROM students WHERE age < %s",
(19,)
)
conn.commit()
print("批量删除成功，影响", cursor.rowcount, "行")
cursor.close()
conn.close()
DELETE语句的语法是"DELETE FROM 表名 WHERE 条件"。同样，WHERE子句非常重要，如果不加WHERE子句，将会删除表中的所有数据，这是一个非常危险的操作！在实际开发中，执行删除操作前最好先查询确认一下要删除的数据，避免误删。常用SQL语句如表9-2.
表 9-2 SQL常用语句一览
操作 SQL语句 说明
创建表 CREATE TABLE 表名 (字段定义...) 定义表结构
插入数据 INSERT INTO 表名 (字段) VALUES (值) 添加新记录
查询数据 SELECT 字段 FROM 表名 WHERE 条件 检索记录
更新数据 UPDATE 表名 SET 字段=值 WHERE 条件 修改记录
删除数据 DELETE FROM 表名 WHERE 条件 删除记录