Python 数据库编程：提升技能的最佳实践

第1关：数据库表创建※

任务描述

本关任务：编写一个 python 程序完成数据库建表操作。

相关知识

pymysql 模块介绍

pymysql 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中是使用 mysqldb 。

pymysql 基本使用

1. # 导入pymysql模块
2. import pymysql
4. # 连接database
5. conn = pymysql.connect(host='localhost', port=3306,
6. user='root', passwd='root',
7. charset='utf8', db = 'mydb')
9. # 得到一个可以执行SQL语句的游标对象
10. cursor = conn.cursor()
12. # 定义要执行的SQL语句
13. sql = """
14. CREATE TABLE USER1 (
15. id INT auto_increment PRIMARY KEY ,
16. name CHAR(10) NOT NULL UNIQUE,
17. age TINYINT NOT NULL
18. )ENGINE=innodb DEFAULT CHARSET=utf8; #注意：charset='utf8' 不能写成utf-8
19. """
21. # 执行SQL语句
22. cursor.execute(sql)
24. # 关闭游标对象
25. cursor.close()
27. # 关闭数据库连接
28. conn.close()

上面的代码就是 pymysql 模块操作 mysql 数据库的基本“套路”，接下来我们来总结下这“套路”吧。

第一步，获取数据库连接对象。

第二步，从数据库连接对象获取执行 SQL 的游标对象。

第三步，编写 SQL 语句并使用游标对象执行 SQL

第四步，关闭游标、关闭连接库连接。

编程要求

根据提示，在右侧Begin-End区域补充代码，完成以下需求:

在名为 nudt 的数据库下，创建课程表（Course）和教师表（Teacher）,表结构如下。

课程表（Course）

字段名	字段类型	说明
Cno	CHAR(10)	课程编号（主键）
Cname	CHAR(100)	课程名称
Tno	CHAR(10)	教师编号

教师表（Teacher）

字段名	字段类型	说明
Tno	CHAR(10)	教师编号（主键）
Tname	CHAR(100)	教师名称
Tsex	CHAR(10)	教师性别

mysql连接参数如下：

host='localhost',port=3306,user='root',passwd='123123'

---

测试说明

平台会对您的代码进行运行测试，如果实际输出与预期输出相同，则算通关。

---

开始你的任务吧，祝你成功！

import pymysql

if __name__ == '__main__':
    # **********begin********** #

    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='123123',db='nudt')
    cursor=conn.cursor()
    
    sql='create table Course(Cno char(10) primary key,Cname CHAR(100),Tno CHAR(10))'
    cursor.execute(sql)
    sql='create table Teacher(Tno CHAR(10) primary key,Tname CHAR(100),Tsex CHAR(10))'
    cursor.execute(sql)
    cursor.close()
    conn.close()
   


    # **********end********** #
   
   

第2关：数据更新※

任务描述

本关任务：编写一个 python 程序完成数据更新操作。

相关知识

pymsql 新增数据

案例1：添加多条数据。

1. import pymysql
3. conn = pymysql.connect(
4. host='localhost',
5. port=3306,
6. user='root',
7. password='root',
8. database='mydb',
9. charset='utf8'
10. )
11. # 获取一个游标
12. cursor = conn.cursor()
14. # 定义要执行的sql语句
15. sql = 'insert into userinfo(user,pwd) values(%s,%s);'
16. data = [
17. ('july', '147'),
18. ('june', '258'),
19. ('marin', '369')
20. ]
21. # 拼接并执行sql语句
22. cursor.executemany(sql, data)
24. # 涉及写操作要注意提交
25. conn.commit()
27. # 关闭连接
28. cursor.close()
29. conn.close()

效果如下：

案例2：单条数据插入。

1. import pymysql
2. conn = pymysql.connect(
3. host='localhost',
4. port=3306,
5. user='root',
6. password='root',
7. database='mydb',
8. charset='utf8'
9. )
10. cursor =conn.cursor() #获取一个游标
11. sql ='insert into userinfo (user,pwd) values (%s,%s);'
13. name = 'wuli'
14. pwd = '123456789'
15. cursor.execute(sql, [name, pwd])
16. conn.commit()
17. cursor.close()
18. conn.close()

效果如下：

数据库表说明

课程表（Course）

字段名	字段类型	说明
Cno	CHAR(10)	课程编号（主键）
Cname	CHAR(100)	课程名称
Tno	CHAR(10)	教师编号

教师表（Teacher）

字段名	字段类型	说明
Tno	CHAR(10)	教师编号（主键）
Tname	CHAR(100)	教师名称
Tsex	CHAR(10)	教师性别

编程要求

根据提示，在右侧Begin-End区域补充代码，完成以下需求：

将教师数据插入教师表（Teacher）

教师编号	教师名称	教师性别
T1	周海芳	女
T2	周竞文	男
T3	谭春娇	女
T4	陈立前	男
T5	李暾	男
T6	毛晓光	男

将课程数据插入课程表（Course）

课程编号	课程名称	教师编号
C1	大学计算机基础	T1
C2	大学计算机基础	T2
C3	数据库原理与技术	T2
C4	大学计算机基础	T5
C5	程序设计	T1
C6	程序设计	T4
C7	数字图像处理	T1
C8	抽象代数	T6
C9	离散数学	T5
C10	软件可靠性	T6

数据库连接参数如下：

host='localhost', port=3306,user='root', passwd='123123', db="nudt",charset='utf8'

---

测试说明

平台会对您的代码进行运行测试，如果实际输出与预期输出相同，则算通关。

---

开始你的任务吧，祝你成功！

import pymysql

if __name__ == '__main__':
    # **********begin********** #
    conn=pymysql.connect(host='localhost', port=3306,user='root', passwd='123123', db="nudt",charset='utf8')
    cursor=conn.cursor()
    sql='insert into Teacher(Tno,Tname,Tsex)values(%s,%s,%s);'
    data=[('T1','周海芳','女'),('T2','周竞文','男'),('T3','谭春娇',    '女'),('T4','陈立前','男'),('T5','李暾','男'),('T6','毛晓光','男')]
    cursor.executemany(sql,data)
    conn.commit()
    sql='insert into Course(Cno,Cname,Tno)values(%s,%s,%s);'
    data=[('C1','大学计算机基础','T1'),('C2','大学计算机基础','T2'),('C3','数据库原理与技术','T2'),('C4','大学计算机基础','T5'),('C5','程序设计','T1'),('C6','程序设计','T4'),('C7','数字图像处理','T1'),('C8','抽象代数','T6'),('C9','离散数学','T5'),('C10','软件可靠性','T6')]
    cursor.executemany(sql,data)
    conn.commit()

    cursor.close()  
    conn.close()  

    
    
    # **********end********** #

第3关：单表查询※

任务描述

本关任务：使用 pymysql 模块统计男女教师的数量。

相关知识

为了完成本关任务，你需要掌握：如何使用 pymysql 查询数据。

pymysql 查询之 fetchone

fetchone 查看一条符合条件的数据，可以连续使用，查询的是上一个fetchone 的后面一条。

表数据如下：

案例如下:

1. import pymysql
2. if __name__ == '__main__':
3. conn = pymysql.connect(host='localhost', port=3306,
4. user='root', passwd='root', db="educoder",charset='utf8')
5. cs = conn.cursor()
6. cs.execute("select * from user ")
7. print(cs.fetchone())
8. print(cs.fetchone())
9. print(cs.fetchone())
10. cs.close()
11. conn.close()

结果如下：

1. (1, 'alice', '女', 21)
2. (2, 'planet ', '男', 20)
3. (3, 'cage', '男', 22)

pymysql 查询之 fetchall

fetchall : 查询所有符合条件的数据。

案例如下：

1. import pymysql
2. if __name__ == '__main__':
3. conn = pymysql.connect(host='localhost', port=3306,
4. user='root', passwd='root', db="educoder",charset='utf8')
5. cs = conn.cursor()
6. cs.execute("select * from user ")
7. print(cs.fetchall())
8. cs.close()
9. conn.close()

结果如下：

1. ((1, 'alice', '女', 21), (2, 'planet ', '男', 20), (3, 'cage', '男', 22), (4, 'zero', '男', 20), (5, 'mary', '女', 18), (10, 'Isabella', '女', 23), (11, 'Madison', '女', 25), (12, 'Abigail', '男', 24))

pymysql 查询之 fetchmany

fetchmany : 获取指定的条数数据。

案例如下：

1. import pymysql
3. if __name__ == '__main__':
4. conn = pymysql.connect(host='localhost', port=3306,
5. user='root', passwd='root', db="educoder", charset='utf8')
6. cs = conn.cursor()
7. cs.execute("select * from user ")
8. print(cs.fetchmany(3))
9. print(cs.fetchmany(1))
10. print(cs.fetchmany(1))
11. cs.close()
12. conn.close()

1. ((1, 'alice', '女', 21), (2, 'planet ', '男', 20), (3, 'cage', '男', 22))
2. ((4, 'zero', '男', 20),)
3. ((5, 'mary', '女', 18),)

说明：fetchmany（count） ： count 是获取的数量。

为什么第一次取 cs.fetchmany(3) 是 ((1, 'alice', '女', 21), (2, 'planet ', '男', 20), (3, 'cage', '男', 22)),而第二次取 cs.fetchmany(1) 是 ((4, 'zero', '男', 20),)呢？为什么不是 ((1, 'alice', '女',21),)?

原因是第一取 cs.fetchmany(3) 的时候，游标已经到 3 了，第二次取的时候，游标还是之前的的游标（指向 3 的游标），所以再一次取数据的时候，数据就是从 4 开始。

编程要求

根据提示，在右侧Begin-End区域补充代码，完成以下需求：

查询所用表：Teacher；

查询男老师数量并将数值赋予给变量 maleNum；

查询女老师数量将数值赋予给变量 femaleNum；

将变量 maleNum 与变量 femaleNum 输出到控制台，格式如 –> 变量名:数值（拼接时使用逗号拼接，不要使用加号）。

预期图片：

连接参数如下：

1. host='localhost', port=3306,
2. user='root', passwd='123123',
3. charset='utf8', db="nudt"

---

测试说明

平台会对您的代码进行运行测试，如果实际输出与预期输出相同，则算通关。

---

开始你的任务吧，祝你成功！

import pymysql
import matplotlib

matplotlib.use('Agg')
import matplotlib.pyplot as plt
from pylab import mpl

mpl.rcParams['font.sans-serif'] = ['SimHei']

if __name__ == '__main__':
    # **********begin********** #

    # 获取连接对象
    conn= pymysql.connect(host='localhost', port=3306,user='root', passwd='123123',charset='utf8', db="nudt" )
    # 获取光标
    cs = conn.cursor()


    # 查询男老师数量并将数值赋予给变量 maleNum
    sql='select count(Tsex) from Teacher where Tsex="男"'
    cs.execute(sql)
    maleNum=cs.fetchall()[0][0]



    # 查询女老师数量将数值赋予给变量 femaleNum
    sql='select count(Tsex) from Teacher where Tsex="女"'
    cs.execute(sql)
    femaleNum=cs.fetchall()[0][0]


    # 将变量 maleNum 与变量 femaleNum 输出到控制台，格式如 -->  变量名:数值
    # 例如： maleNum:100
    # 注意：一行输出一个变量,请先输出变量 maleNum ，其次是变量 femaleNum
    print('maleNum: '+str(maleNum))
    print('femaleNum: '+str(femaleNum))


    # 绘制柱状图
    plt.xticks(fontsize=12)
    plt.yticks([])  # 不显示y轴刻度
    plt.text(0, maleNum * 0.5, maleNum, ha='center', fontsize=30)
    plt.text(1, femaleNum * 0.5, femaleNum, ha='center', fontsize=30)
    plt.bar(range(2), (maleNum, femaleNum), color='gy', tick_label=('男', '女'))
    plt.savefig("img/1.png")
    cs.close()
    conn.close()
    # 关闭资源

    # **********end********** #

第4关：多表查询※

任务描述

本关任务：使用 pymysql 模块统计教师的课程数量。

相关知识

相关知识略

编程要求

根据提示，在右侧Begin-End区域补充代码，完成以下需求：

统计教师的课程数量并按照教师名称倒序。

获取结果集，将其赋予给变量 results 并遍历结果集，按照格式 –> 教师:课程数量 ，输出到控制台。

连接参数如下：

1. host='localhost', port=3306,
2. user='root', passwd='123123',
3. charset='utf8', db="nudt"

课程表（Course）

字段名	字段类型	说明
Cno	CHAR(10)	课程编号（主键）
Cname	CHAR(100)	课程名称
Tno	CHAR(10)	教师编号

表数据如下:

教师表（Teacher）

字段名	字段类型	说明
Tno	CHAR(10)	教师编号（主键）
Tname	CHAR(100)	教师名称
Tsex	CHAR(10)	教师性别

表数据如下:

测试说明

平台会对您的代码进行运行测试，如果实际输出与预期输出相同，则算通关。

---

开始你的任务吧，祝你成功！

import pymysql  
import matplotlib
matplotlib.use('Agg')  
import matplotlib.pyplot as plt  
from pylab import mpl
mpl.rcParams['font.sans-serif'] = ['SimHei']
if __name__ == '__main__':  
    # **********begin***********#
    # 获取连接对象  
    conn = pymysql.connect(host='localhost', port=3306,  
                       user='root', passwd='123123',  
                       charset='utf8',db='nudt')
    # 获取光标  
    cs = conn.cursor()
    # 执行SQL，统计教师的课程数量并按照教师名称倒序  
    cs.execute("SELECT Tname, count(Cno) FROM Teacher LEFT JOIN Course  ON Teacher.Tno=Course.Tno  GROUP BY Tname order by Tname desc")
    # 获取结果集，将其赋予给变量 results  
    results = cs.fetchall()
    # 遍历结果集，按照格式 --> 教师:课程数量 ，输出到控制台  
    for x in results:  
        print(x[0],":",x[1])  
    # 绘制柱状图  
    names, courseNum = zip(*results)  
    plt.xticks(fontsize=16)  
    plt.yticks([])  # 不显示y轴刻度  
    for i in range(len(courseNum)):  
        plt.text(i, courseNum[i] * 0.5, courseNum[i], ha='center', fontsize=20)  
    plt.bar(range(len(courseNum)), courseNum, color='y', tick_label=names)  
    plt.savefig("step4_img/1.png")
    # 关闭资源  
    cs.close()  
    conn.close()
    # **********end**********#

第5关：信息管理系统※

任务描述

本关任务：使用 python 实现一个有增删改查功能的信息管理系统（简易版）

相关知识

相关知识略

编程要求

根据提示，在右侧Begin-End区域补充代码，完成以下需求：

补全 addCourse(cs) 函数，完成添加课程信息功能。

补全 updateCourse(cs) 函数，完成修改课程信息功能。

补全 findCourseByCno(cs) 函数，完成查询课程信息功能。

补全 deleteCourse(cs) 函数，完成删除课程信息功能。

补全 findCourseByTeacherName(cs) 函数，完成通过教师名称查询课程名称功能。

补全 selectTeacherByCname(cs) 函数，完成通过课程名称查询教师名称功能。

课程表（Course）

字段名	字段类型	说明
Cno	CHAR(10)	课程编号（主键）
Cname	CHAR(100)	课程名称
Tno	CHAR(10)	教师编号

表数据如下:

教师表（Teacher）

字段名	字段类型	说明
Tno	CHAR(10)	教师编号（主键）
Tname	CHAR(100)	教师名称
Tsex	CHAR(10)	教师性别

表数据如下:

测试说明

平台会对您的代码进行运行测试，如果实际输出与预期输出相同，则算通关。

---

开始你的任务吧，祝你成功！

import pymysql  
# 添加课程信息，输入课程信息格式为：Cno,Cname,Tno  
def addCourse(cs):  
    courseInfo = input()  
    # **********begin********** #  
    arr = courseInfo.split(",")  
    sql = "INSERT INTO Course VALUES ('%s', '%s', '%s')" % (arr[0], arr[1], arr[2])  
    try:  
        cs.execute(sql)  
    except:  
        return  
    # **********end********** #  
# 修改课程信息（通过课程编号修改课程名称），输入新课程信息格式为：Cno,Cname  
def updateCourse(cs):  
    courseInfo = input()  
    # **********begin********** #  
    arr = courseInfo.split(",")  
    sql = "UPDATE Course SET Cname='%s' WHERE Cno='%s'" % (arr[1], arr[0])  
    try:  
        cs.execute(sql)  
    except:  
        return  
    # **********end********** #  
# 查询课程信息（通过课程编号查询课程信息），输入课程编号 Cno  
# 将课程信息打印到控制台  
def findCourseByCno(cs):  
    courseId = input()  
    # **********begin********** #  
    sql = "select * from Course where Cno = '%s'" % (courseId)  
    try:  
        cs.execute(sql)  
        courseInfo = cs.fetchall()  
        print(courseInfo)  
    except:  
        return  
    # **********end********** #  
# 删除课程信息（通过课程编号删除课程信息），输入课程编号 Cno  
def deleteCourse(cs):  
    courseId = input()  
    # **********begin********** #  
    sql = "delete  from Course where Cno = '%s'" % (courseId)  
    try:  
        cs.execute(sql)  
    except:  
        return  
    # **********end********** #  
# 通过教师名称查询课程名称并将其打印到控制台  
# 打印格式为：课程名  （一个课程名一行，不含其它字符）  
def findCourseByTeacherName(cs):  
    tname = input()  
    # **********begin********** #  
    sql = "SELECT Cname From Course, Teacher WHERE Course.Tno=Teacher.Tno AND Tname='%s'" % (tname)  
    try:  
        cs.execute(sql)  
        courseInfo = cs.fetchall()  
        for x in courseInfo:  
            print(x[0])  
    except:  
        return  
    # **********end********** #  
# 通过课程名称查询教师名称并将其打印到控制台  
# 打印格式为：教师名  （一个教师名一行，不含其它字符）  
def selectTeacherByCname(cs):  
    cname = input()  
    # **********begin********** #  
    sql = "SELECT Tname From Course, Teacher WHERE Course.Tno=Teacher.Tno AND Cname='%s'" % (cname)  
    try:  
        cs.execute(sql)  
        teachers = cs.fetchall()  
        for x in teachers:  
            print(x[0])  
    except:  
        return  
    # **********end********** #  
def Test(cs):  
    sql = "select * from Course"  
    cs.execute(sql)  
    courseInfo = cs.fetchall()  
    print(courseInfo)  
if __name__ == '__main__':  
    conn = pymysql.connect(host='localhost', port=3306,  
                           user='root', passwd='123123', db="nudt", charset='utf8')  
    command = input()  
    cs = conn.cursor()  
    if command == '01':  
        addCourse(cs)  
    elif command == '02':  
        updateCourse(cs)  
    elif command == '03':  
        findCourseByCno(cs)  
    elif command == '04':  
        deleteCourse(cs)  
    elif command == '05':  
        findCourseByTeacherName(cs)  
    elif command == '06':  
        selectTeacherByCname(cs)  
    conn.commit()  
    Test(cs)  
    cs.close()  
    conn.close()