课程 › 第 17 章 › 实验一
第 17 章 · 实验一 · 在第三节之后

用 Python 操作数据库

安装 MySQL 的 Python 驱动，连上 teaching 库查询版本和学生信息，再插入、删除和修改学生数据，体会 commit 和 rollback。

环　境

MySQL 8.0 和 Workbench，Python 3.9 或更高版本，PyCharm 社区版即可。数据准备在 Workbench 中执行，Python 程序在 PyCharm 中运行。

运　行

在 PyCharm 中新建 .py 文件，复制参考答案，右击编辑区，选 Run。也可以在命令提示符中执行 python 文件名.py。

注　意

把代码中的 password='123456' 改为自己的 root 口令。Python 区分大小写，缩进也不能改。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 1-1安装驱动
  2. 实验 1-2查询 MySQL 的版本
  3. 实验 1-3取出查询结果
  4. 实验 1-4插入四位同学
  5. 实验 1-5出错时回滚
  6. 实验 1-6删除和修改
第一步

数据准备（必做）

在 Workbench 中执行。重建 teaching 库的五张表和教材中的数据，学生表 s 有 s1 到 s8 共 8 人。

Workbench　整段复制，全部执行下载 .sql
-- 第 17 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 在 Workbench 中整段执行一次即可。想从头再做时重新执行。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, classroom, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');
1-1

安装驱动

讲义 · 安装 Python、PyCharm 和驱动
目的
在命令提示符中安装 MySQL 官方的 Python 驱动 mysql-connector-python，再确认能够导入。
前提
已安装 Python，并在安装时勾选了 Add Python to PATH。
  1. 用 pip 安装 mysql-connector-python
  2. 用 python -c 导入并显示版本号

参考答案

命令提示符　一次复制一行
pip install mysql-connector-python
python -c "import mysql.connector; print(mysql.connector.__version__)"

语句解释

pip install从网上下载并安装 Python 的第三方模块
python -c直接执行引号中的 Python 语句
在 PyCharm 中安装File → Settings → Python Interpreter，单击 +，搜索 mysql-connector-python，单击 Install Package
提示找不到 python安装 Python 时没有加入 PATH，重新安装并勾选 Add Python to PATH
执行后看：安装完成后显示 Successfully installed。第二行显示驱动的版本号，没有报错就说明可以使用。
1-2

查询 MySQL 的版本

讲义 · 连接数据库与查询版本
目的
连接 teaching 库，执行 SELECT VERSION()，输出 MySQL 的版本。
前提
已完成实验 1-1，已在 Workbench 中执行数据准备。
  1. 新建 connect_01.py
  2. 连接、建立游标、执行查询、取出一行、输出
  3. 关闭游标和连接

参考答案

Python　connect_01.py
import mysql.connector
db = mysql.connector.connect(
    host='localhost', user='root', password='123456',
    database='teaching')
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print("Database version : %s " % data)
cursor.close()
db.close()

语句解释

import mysql.connector导入驱动，import 全部小写
connect(…)建立连接，返回连接对象 db
db.cursor()建立游标对象，SQL 语句通过游标执行
fetchone()取出结果的一行，是一个元组，例如 ('8.0.36',)
执行后看：输出 Database version : 8.0.xx，xx 是本机 MySQL 的小版本号。
1-3

取出查询结果

讲义 · Connection 与 Cursor
目的
查询信息学院的学生，用 fetchall 取出全部结果，再用 for 循环逐行输出学号、姓名和院系。
前提
已完成实验 1-2。
  1. 新建 fetch_01.py
  2. 用 fetchall 取出结果，输出行数
  3. 用 for 循环逐行输出

参考答案

Python　fetch_01.py
import mysql.connector
db = mysql.connector.connect(
    host='localhost', user='root', password='123456',
    database='teaching')
cursor = db.cursor()
cursor.execute("SELECT sno, sn, dept FROM s WHERE dept = '信息学院'")
rows = cursor.fetchall()
print('共', len(rows), '行')
for row in rows:
    print(row[0], row[1], row[2])
cursor.close()
db.close()

语句解释

fetchall()取出所有行，得到元组的列表
len(rows)列表的长度，即行数
row[0]这一行的第 1 列，下标从 0 开始
缩进print 前面的 4 个空格表示它属于 for 循环
执行后看：先输出 共 4 行，再输出 s1 王彤 信息学院、s2 苏乐 信息学院、s3 林欣怡 信息学院、s6 何欣荣 信息学院。
1-4

插入四位同学

讲义 · 插入数据
目的
完成示例 1：把王天一、苏红霞、林勇、李玉四位同学插入学生表 s，提交后统计人数。
前提
已完成实验 1-3。
  1. 新建 insert_s_01.py
  2. 四条 INSERT 放在列表中，逐条执行
  3. 全部成功后 commit，出错时 rollback

参考答案

Python　insert_s_01.py
import mysql.connector
db = mysql.connector.connect(
    host='localhost', user='root', password='123456',
    database='teaching')
cursor = db.cursor()
sqls = [
    "INSERT INTO s VALUES ('s9', '王天一', '女', 18, '计算机', '信息学院')",
    "INSERT INTO s VALUES ('s10', '苏红霞', '女', 20, '信息', '信息学院')",
    "INSERT INTO s VALUES ('s11', '林勇', '男', 19, '信息', '信息学院')",
    "INSERT INTO s VALUES ('s12', '李玉', '女', 21, '自动化', '工学院')",
]
try:
    for sql in sqls:
        cursor.execute(sql)
    db.commit()
    cursor.execute("SELECT COUNT(*) FROM s")
    print('插入成功，学生人数：', cursor.fetchone()[0])
except mysql.connector.Error as e:
    db.rollback()
    print('插入失败，已回滚：', e.errno, e.msg)
finally:
    cursor.close()
    db.close()

语句解释

sqls = […]把四条语句放在列表中，用 for 循环逐条执行
db.commit()提交事务，插入的数据才真正保存
excepttry 中的语句出错时转到这里，db.rollback() 撤销已经执行的语句
finally不论是否出错都执行，关闭游标和连接写在这里
执行后看：输出 插入成功，学生人数： 12。在 Workbench 中查询 s，能看到 s9 到 s12。
1-5

出错时回滚

讲义 · 插入数据讲义 · 查询数据与执行事务
目的
先插入一个新同学 s13，再插入已经存在的 s12。第二条出错时回滚，看 s13 有没有插入。
前提
已完成实验 1-4。
  1. 新建 insert_s_02.py
  2. 两条 INSERT 在同一个事务中
  3. 在 finally 中查询 s13 的行数

参考答案

Python　insert_s_02.py
import mysql.connector
db = mysql.connector.connect(
    host='localhost', user='root', password='123456',
    database='teaching')
cursor = db.cursor()
sqls = [
    "INSERT INTO s VALUES ('s13', '张明', '男', 19, '数学', '理学院')",
    "INSERT INTO s VALUES ('s12', '李玉', '女', 21, '自动化', '工学院')",
]
try:
    for sql in sqls:
        cursor.execute(sql)
    db.commit()
    print('插入成功')
except mysql.connector.Error as e:
    db.rollback()
    print('插入失败，已回滚：', e.errno, e.msg)
finally:
    cursor.execute("SELECT COUNT(*) FROM s WHERE sno = 's13'")
    print('s13 的行数：', cursor.fetchone()[0])
    cursor.close()
    db.close()

语句解释

1062Duplicate entry，s12 已经存在
rollback 撤销了什么第一条插入 s13 已经执行，但还没有提交，回滚时一起撤销
事务两条语句要么都成功，要么都不生效
执行后看：输出 插入失败，已回滚： 1062 Duplicate entry 's12' for key 's.PRIMARY'，然后输出 s13 的行数： 0。
1-6

删除和修改

讲义 · 删除与修改数据
目的
完成示例 2、示例 3：删除学号为 s12 的学生，把林欣怡的专业改为计算机，提交后查询林欣怡的记录。
前提
已完成实验 1-5。
  1. 新建 update_s_01.py
  2. 执行 DELETE 和 UPDATE，分别输出影响的行数
  3. 提交后查询林欣怡

参考答案

Python　update_s_01.py
import mysql.connector
db = mysql.connector.connect(
    host='localhost', user='root', password='123456',
    database='teaching')
cursor = db.cursor()
try:
    cursor.execute("DELETE FROM s WHERE sno = 's12'")
    print('删除的行数：', cursor.rowcount)
    cursor.execute("UPDATE s SET maj = '计算机' WHERE sn = '林欣怡'")
    print('修改的行数：', cursor.rowcount)
    db.commit()
    cursor.execute("SELECT * FROM s WHERE sn = '林欣怡'")
    print(cursor.fetchone())
except mysql.connector.Error as e:
    db.rollback()
    print('操作失败：', e.errno, e.msg)
finally:
    cursor.close()
    db.close()

语句解释

DELETE 和 UPDATE与在 Workbench 中执行的语句完全相同
rowcountDELETE、UPDATE 影响的行数
db.commit()两条修改在同一个事务中，一起提交
再运行一次s12 已经不存在，删除的行数为 0，不会报错
执行后看：输出 删除的行数： 1 和 修改的行数： 1，然后输出 ('s3', '林欣怡', '女', 19, '计算机', '信息学院')。
保山学院·人工智能教研室·曹鼎鼎