资源简介

单元8 数据库访问与使用
课程名称 Python程序设计任务驱动式教程 章名 数据库访问与使用
教学内容 数据库访问与使用 课时 4
项目性质 □演示性 □验证性 □设计性 √综合性
授课班级 授课日期 授课地点
教学目标 了解MySQL基础知识 掌握创建与使用SQLite数据表 掌握创建与使用MySQL数据表
教学内容 创建与使用SQLite数据表 创建与使用MySQL数据表
教学重点 创建与使用SQLite数据表、创建与使用MySQL数据表
教学难点 创建与使用SQLite数据表、创建与使用MySQL数据表
教学准备 装有Python的计算机 教学课件PPT 教材：《Python程序设计任务驱动式教程（微课版）》
作业设计
11
教学过程
教学环节 教学内容与过程 （教学内容、教学方法、组织形式、教学手段）
课前组织 做好上课前的各项准备工作（打开计算机、打开课件、打开软件、打开授课计划、教案等），吸引学生注意力。
课程说明 【课前说明】 从MySQL基础知识进行初步的了解。 【目的】 使学生从了解本节课的学习目标、学习重点、考评方式等方面明确课程学习的要求和目标。
课程内容描述 8.1 创建与使用SQLite数据表 SQLite是一种轻型数据库，它的设计目标是嵌入式的。SQLite将整个数据库，包括数据库定义、数据表、索引以及数据本身，作为单独的、可跨平台使用的文件存储在主机中。Python内置了SQLite3模块，所以在Python中使用SQLite，不需要额外安装任何模块，可以直接使用。 8.1.1 创建SQLite数据库文件与数据表 Python内置了SQLite3模块，可以直接使用import语句导入SQLite3模块。Python创建数据表的通用流程如下。 （1）使用connect()方法创建连接对象。 （2）使用cursor()方法获取游标对象。 （3）使用execute()方法执行一条SQL语句创建数据表。 （4）使用游标对象的close()方法关闭游标。 （5）使用连接对象的close()方法关闭连接对象。 【实例8-1】演示创建SQLite数据库文件dbtest.db与数据表teacher 实例8-1的代码如下所示。 import SQLite3 SQL = """create table teacher ( ID int(4) primary key , name varchar(30) , sex varchar(2) , nation varchar(30), title varchar(20) ); """ # 连接到SQLite数据库 # 数据库文件是dbtest.db，如果文件不存在，会自动在当前文件夹中创建 conn = SQLite3.connect('dbtest.db') # 创建一个Cursor cursor = conn.cursor() # 执行一条SQL语句，创建user表 cursor.execute(SQL) # 关闭游标 cursor.close() # 关闭Connection conn.close() 实例8-1中使用SQLite3.connect()方法连接SQLite数据库文件dbtest.db，由于该文件并不存在，所以会先创建好数据库文件dbtest.db，然后在该文件中创建数据表teacher，该数据表中包含ID、name、sex、nation、title 5个字段。 实例8-1的代码成功运行，则表示SQLite数据库文件dbtest.db与数据表teacher都创建成功。 8.1.2 操作SQLite数据库 1．在数据表中新增记录 向数据表中新增记录，可以使用如下SQL语句。 insert into 数据表名称(字段名1,字段名2,…,字段名n) values( 字段值1,字段值2,…,字段值n ) 向数据表中新增记录时，字段值需要根据字面的数据类型正确赋值，否则新增记录会失败。 【实例8-2】演示向数据表teacher中新增3条记录 实例8-2的代码如下所示。 import SQLite3 # 连接到SQLite数据库 # 数据库文件是dbtest.db，如果文件不存在，会自动在当前目录创建 conn = SQLite3.connect('dbtest.db') # 创建一个Cursor cursor = conn.cursor() # 执行一条SQL语句，创建user表 try: cursor.execute("insert into teacher (ID,name,sex,nation,title) \ values(1,'宁夏','男','汉族','教授')") cursor.execute("insert into teacher (ID,name,sex,nation,title) \ values(2,'郑州','男','汉族','副教授')") cursor.execute("insert into teacher (ID,name,sex,nation,title) \ values(3,'叶丽','女','苗族','讲师')") conn.commit() except: # 发生错误时回滚 conn.rollback() # 关闭游标 cursor.close() # 关闭连接对象 conn.close() 实例8-2的代码成功运行，即表示向数据表teacher中新增了3条记录。 2．查看数据表中的记录 查询SQLite数据表中的记录可以使用如下SQL语句。 select 字段名1,字段名2,…,字段名n from 数据表名称 where <查询条件> 在select查询语句中，使用问号“ ”作为占位符代替具体的字段值，然后使用一个元组来替换问号，如果元组中只有一个元素，元素后面的逗号“,”不能省略。使用占位符的方式可以避免SQL注入的风险。 例如，从数据表teacher中查询ID大于2的所有记录可以使用以下SQL语句。 SQL="select * from teacher where ID> " , (2,) 上述语句等价于如下语句。 SQL="select * from teacher where ID>2" 在Python中查询SQLite数据表时，使用fetchone()方法获取数据表中的1条记录数据，使用fetchall()方法获取数据表中全部的记录数据，使用fetchmany(size)方法获取数据表中指定数量size的记录数据。 【实例8-3】演示查询数据表teacher中的记录的多种方法 实例8-3的代码如下所示。 import SQLite3 # 连接到SQLite数据库 # 数据库文件是dbtest.db，如果文件不存在，会自动在当前目录创建 conn = SQLite3.connect('dbtest.db') # 创建一个Cursor cursor = conn.cursor() # 执行一条SQL语句，创建user表 cursor.execute("select ID,name,sex,nation,title from teacher") # 获取查询结果 result1 = cursor.fetchone() # 使用fetchone()方法查询一条数据 print(result1) result2 = cursor.fetchmany(1) # 使用fetchmany(size)方法查询多条数据 print(result2) result3 = cursor.fetchall() # 使用fetchall()方法查询多条数据 print(result3) # 关闭游标 cursor.close() # 关闭连接对象 conn.close() 实例8-3的运行结果如下。 (1, '宁夏', '男', '汉族', '教授') [(2, '郑州', '男', '汉族', '副教授')] [(3, '叶丽', '女', '苗族', '讲师')] 3．修改数据表中的记录 修改数据表中的记录可以使用如下SQL语句。 update 数据表名称 set 字段名=字段值 where <查询条件> 【实例8-4】演示将数据表teacher中ID为3的name字段值修改为“夏丽” 实例8-4的代码如下所示。 import SQLite3 # 连接到SQLite数据库 # 数据库文件是dbtest.db，如果文件不存在，会自动在当前目录创建 conn = SQLite3.connect('dbtest.db') # 创建一个Cursor cursor = conn.cursor() # 执行一条SQL语句，创建user表 try: cursor.execute("update teacher set name= where ID= ", ('夏丽', 3)) conn.commit() cursor.execute("select ID,name,sex,nation,title from teacher where ID= " , (3,)) result = cursor.fetchall() print(result) except: # 发生错误时回滚 conn.rollback() # 关闭游标 cursor.close() # 关闭连接对象 conn.close() 实例8-4的运行结果如下。 [(3, '夏丽', '女', '苗族', '讲师')] 4．删除数据表中的记录 删除数据表中的记录可以使用如下SQL语句。 delete from 数据表名称 where <查询条件> 【实例8-5】演示将数据表teacher中ID为2的记录删除 实例8-5的代码如下所示。 import SQLite3 # 连接到SQLite数据库 # 数据库文件是dbtest.db，如果文件不存在，会自动在当前目录创建 conn = SQLite3.connect('dbtest.db') # 创建一个Cursor cursor = conn.cursor() # 执行一条SQL语句，创建user表 try: cursor.execute("delete from teacher where ID= ", (2,)) conn.commit() cursor.execute("select * from teacher ") result = cursor.fetchall() print(result) except: # 发生错误时回滚 conn.rollback() # 关闭游标 cursor.close() # 关闭连接对象 conn.close() 实例8-5的运行结果如下。 [(1, '宁夏', '男', '汉族', '教授'), (3, '夏丽', '女', '苗族', '讲师')] 从实例8-5的运行结果可以看出，数据表teacher中ID为2的记录已被删除。 【任务8-1】创建、新增、查询、删除SQLite数据表 【任务描述】 （1）在PyCharm集成开发环境中创建项目Unit08。 （2）在项目Unit08中创建Python程序文件8-1.py。 （3） 自定义getInsertSQL ()函数用于返回SQL插入语句，自定义execInsert()函数用于向数据表中插入多条记录。 （4）创建SQLite数据库文件电子商务.db。 （5）在SQLite数据库文件电子商务.db中如果用户表已存在，先删除该数据表，再重新创建数据表用户表，该数据表包括用户ID、用户编号、用户名称、密码4个字段，各字段的数据类型和长度见程序文件8-1.py中的代码。 （6）向数据表用户表中插入5条记录。 （7）查询用户名为“admin”，密码为“666”的记录。 【任务实施】 1．创建PyCharm项目Unit08 成功启动PyCharm后，在指定位置“D:\PycharmProject\”创建PyCharm项目Unit08。 2．创建Python程序文件8-1.py 在PyCharm项目Unit08中新建Python程序文件8-1.py，同时PyCharm主窗口显示程序文件8-1.py的代码编辑窗口，在该程序文件的代码编辑窗口自动添加了模板内容。 3．编写Python程序代码 在新建文件8-1.py的代码编辑窗口已有模板注释内容下面输入程序代码，程序文件8-1.py的代码如电子活页8-2所示。 单击工具栏中的【保存】按钮，保存程序文件8-1.py。 4．运行Python程序 在PyCharm主窗口选择【Run】菜单，在弹出的下拉菜单中选择【Run】。在弹出的【Run】对话框中选择“8-1”选项，程序文件8-1.py开始运行。程序文件8-1.py的运行结果如下。 插入第 1 条记录，数据为：1 2020011 admin 666 插入第 2 条记录，数据为：2 2020012 better 888 插入第 3 条记录，数据为：3 2020013 向前 123456 插入第 4 条记录，数据为：4 2020014 寻找 123 插入第 5 条记录，数据为：5 2020015 向好汉 1456 数据表userData.db的记录数量：5 【任务8-2】查询、更新、删除用户数据表中的数据 【任务描述】 （1）在项目Unit08中创建Python程序文件8-2.py。 （2）在程序文件8-2.py中自定义多个函数：initDb()函数用于创建数据库文件电子商务.db和一个游标对象，getSelectSQL()函数用于返回SQL查询语句，getUserInfo()函数用于获取指定用户名称和密码的记录数，getUpdateSQL()函数用于返回SQL修改记录语句，getDeleteSQL ()函数用于返回满足指定条件的SQL删除记录语句。 （3）连接到SQLite数据库电子商务.db。 （4）从数据表用户表中查询符合指定条件的记录。 （5）将数据表用户表中用户ID为“1”对应记录的“密码”修改为“666”。 （6）删除数据表用户表中用户名称为“向前”的记录。 【任务实施】 1．创建Python程序文件8-2.py 在PyCharm项目Unit08中新建Python程序文件8-2.py，同时PyCharm主窗口显示程序文件8-2.py的代码编辑窗口，在该程序文件的代码编辑窗口自动添加了模板内容。 2．编写Python程序代码 在新建文件8-2.py的代码编辑窗口已有模板注释内容下面输入程序代码，程序文件8-2.py的代码如电子活页8-3所示。 单击工具栏中的【保存】按钮，保存程序文件8-2.py。 3．运行Python程序 在PyCharm主窗口选择【Run】菜单，在弹出的下拉菜单中选择【Run】。在弹出的【Run】对话框中选择“8-2”选项，程序文件8-2.py开始运行。程序文件8-2.py的运行结果如下。 用户表符合条件的查询结果记录数：1 用户表符合条件的查询结果记录数：1 用户表的记录数：3 用户表的记录数：4 8.2 创建与使用MySQL数据表 8.2.1 连接MySQL数据库 在Windows【命令提示符】窗口的提示符“>”后输入命令“MySQL –u root –p”，按【Enter】键后输入正确的密码，当【命令提示符】窗口的提示符变为“mySQL>”时，表示已经成功登录到MySQL服务器了。 在连接数据库前，首先创建一个数据库testdb，在提示符“mySQL>”后面输入以下创建数据库testdb的语句。 Create Database if not exists testdb; 接下来可以连接MySQL数据库。 【实例8-6】演示使用PyMySQL库的connect()方法连接MySQL数据库 实例8-6的代码如下所示。 import pymySQL # 数据库连接，参数1：主机名或IP。参数2：用户名。参数3：密码。参数4：数据库名称 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法创建一个游标对象Cursor cursor = conn.cursor() # 使用execute()方法执行SQL查询 cursor.execute("Select Version()") # 使用fetchone()方法获取单条数据 data = cursor.fetchone() print ("Database version：", data) # 关闭数据库连接 conn.close() 实例8-6的代码，首先使用connect()方法连接数据库，然后使用cursor()方法创建游标对象，接着使用execute()方法执行SQL语句查看MySQL数据库版本，再使用fetchone()方法获取数据，最后使用close()方法关闭数据库连接。 实例8-6的运行结果如下。 Database version：('8.0.19',) 8.2.2 创建MySQL数据表 数据库连接成功以后，接下来就可以在数据库中创建数据表了。创建数据表需要使用execute()方法。 【实例8-7】演示MySQL数据表student的创建过程 实例8-7的代码如下所示。 import pymySQL # 打开数据库连接 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法创建一个游标对象Cursor cursor = conn.cursor() # 使用 execute()方法执行SQL，如果表存在则删除 cursor.execute("drop table if exists student") # 使用预处理语句创建表 SQL = """Create table student ( ID int(4) Not Null, name varchar(30) Not Null, sex varchar(2) Not Null, nation varchar(30) Null ); """ cursor.execute(SQL) # 关闭数据库连接 conn.close() 实例8-7的代码成功运行，则表示数据库testdb中成功创建了数据表student。 实例8-7的代码创建了一个student数据表，该数据表的字段有序号（ID）、姓名（name）、性别（sex）、民族（nation）。创建student数据表的SQL语句如下。 Create table student ( ID int(4) Not Null, name varchar(30) Not Null, sex varchar(2) Not Null, nation varchar(30) Null ); 在创建数据表之前，如果数据表student已经存在，则先要使用以下语句删除数据表student，然后创建student数据表。 drop table if exists student 8.2.3 MySQL数据表插入操作 使用insert语句可以向数据表插入记录。 【实例8-8】演示使用insert语句向数据表student插入记录 实例8-8的代码如下所示。 import pymySQL # 打开数据库连接 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法获取操作游标 cursor = conn.cursor() # SQL插入语句 SQL = """insert into student(ID,name,sex,nation) values("1","张山", "男", "汉族") """ try: # 执行SQL语句 cursor.execute(SQL) # 提交到数据库执行 conn.commit() except: # 如果发生错误则回滚 conn.rollback() # 关闭数据库连接 conn.close() 实例8-8的代码成功运行，则表示向数据表student成功插入1条记录。 【实例8-9】演示使用带参数的insert语句向数据表student插入记录 实例8-9的代码如下所示。 import pymySQL # 打开数据库连接 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法获取操作游标 cursor = conn.cursor() # SQL插入语句 try: # 执行SQL语句 cursor.execute("insert into student(ID,name,sex,nation) \ values(%s,%s,%s,%s)" , ("2","丁好","男","汉族")) # 提交到数据库执行 conn.commit() except: # 如果发生错误则回滚 conn.rollback() # 关闭数据库连接 conn.close() 实例8-9的代码成功运行，则表示使用带参数的insert语句向数据表student中成功插入1条记录。 实例8-9中使用“%s”作为占位符，防止SQL语句注入，以参数值元组形式传递给占位符。 8.2.4 MySQL数据表查询操作 在Python中查询MySQL数据表，使用fetchone()方法获取数据表中的1条记录数据，使用fetchall()方法获取数据表中的全部记录数据。只读属性rowcount可以返回执行execute()方法后影响的行数。 【实例8-10】演示从数据表student中查询男学生的所有记录 实例8-10的代码如下所示。 import pymySQL # 打开数据库连接 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法获取操作游标 cursor = conn.cursor() # SQL 查询语句 SQL = "select ID,name,sex,nation from student where sex=%s " try: # 执行SQL语句 cursor.execute(SQL,('男')) # 获取所有记录列表 results = cursor.fetchall() print("序号 姓名 性别 民族：") for row in results: ID=row[0] name = row[1] sex = row[2] nation = row[3] # 打印结果 print(" {0} {1} {2} {3}" \ .format(ID,name,sex,nation)) except: print("error: unable to fetch data") # 关闭数据库连接 conn.close() 实例8-10的运行结果如下。 序号 姓名 性别 民族： 1 张山 男 汉族 2 丁好 男 汉族 8.2.5 MySQL数据表更新操作 更新操作用于更新数据表中的数据。 【实例8-11】演示将数据表student中“丁好”的性别修改为“女” 实例8-11的代码如下所示。 import pymySQL # 打开数据库连接 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法获取操作游标 cursor = conn.cursor() # SQL更新语句 SQL = "update student set sex = '女' where name = %s" try: # 执行SQL语句 cursor.execute(SQL, ('丁好')) # 提交到数据库执行 conn.commit() except: # 发生错误时回滚 conn.rollback() # 关闭数据库连接 conn.close() 实例8-11的代码成功运行，则表示成功修改了数据表student数据。 8.2.6 MySQL数据表删除操作 删除操作用于删除数据表中的数据。 【实例8-12】演示删除数据表student中姓名为“丁好”的记录 实例8-12的代码如下所示。 import pymySQL # 打开数据库连接 conn = pymySQL.connect("localhost", "root", "123456", "testdb") # 使用cursor()方法获取操作游标 cursor = conn.cursor() # SQL删除语句 SQL = "delete from student where 姓名= %s" try: # 执行SQL语句 cursor.execute(SQL, ('丁好')) # 提交修改 conn.commit() except: # 发生错误时回滚 conn.rollback() # 关闭数据库连接 conn.close() 实例8-12的代码成功运行，则表示成功删除了数据表student一条记录。 【任务8-3】创建books数据表并显示数据表的结构信息 【任务描述】 （1）在项目Unit08中创建Python程序文件8-3.py。 （2）连接已存在的MySQL数据库eCommerce。 （3）在MySQL数据库eCommerce中创建数据表books。 （4）输出数据表books的结构信息。 【任务实施】 在PyCharm项目Unit08中创建Python程序文件8-3.py。在程序文件8-3.py中编写程序代码，实现所需功能，程序文件8-3.py的代码如电子活页8-4所示。 程序8-3.py的运行结果如下。 数据表中记录数：0 数据的结构信息：('商品ID', 3, None, 8, 8, 0, False) 数据的结构信息：('商品编号', 253, None, 48, 48, 0, False) 数据的结构信息：('图书名称', 253, None, 200, 200, 0, False) 数据的结构信息：('价格', 246, None, 10, 10, 2, True) 数据的结构信息：('ISBN', 253, None, 52, 52, 0, False) 数据的结构信息：('作者', 253, None, 120, 120, 0, True) 数据的结构信息：('出版社', 253, None, 48, 48, 0, True) 数据的结构信息：('出版日期', 253, None, 40, 40, 0, True) 数据的结构信息：('版次', 3, None, 1, 1, 0, True) 数据表的字段名：商品ID,商品编号,图书名称,价格,ISBN,作者,出版社,出版日期,版次 【任务8-4】在books数据表中批量添加多条记录 【任务描述】 （1）在项目Unit08中创建Python程序文件8-4.py。 （2）连接已存在的MySQL数据库eCommerce。 （3）定义列表bookData，该列表的元素为元组，包含6个元素，每一个元素存放一本图书的相关数据，对应数据表中一条记录的数据。 （4）在数据表books中插入6条记录数据。 【任务实施】 在PyCharm项目Unit08中创建Python程序文件8-4.py。在程序文件8-4.py中编写程序代码，实现所需功能，程序文件8-4.py的代码如电子活页8-5所示。 程序文件8-4.py的代码成功运行，则表示向数据表books中成功插入6条记录。 【任务8-5】自定义函数实现MySQL数据表新增、修改、删除与查询的综合操作 【任务描述】 （1）在项目Unit08中创建Python程序文件8-5.py。 （2）自定义多个函数，分别实现所需功能：MySQL()函数用于连接MySQL数据库eCommerce；closeSQL ()函数用于关闭数据库；insertData()函数用于向数据表中灵活插入记录数据；selectAllData()函数用于灵活获取数据表中的全部记录；selectData()函数用于根据指定查询条件灵活获取数据表中符合条件的记录数据；selectDataOrder()函数用于灵活获取数据表中有序的记录数据；updateData()函数用于灵活更新数据表中符合指定条件的数据；deleteData()函数用于灵活删除符合指定条件的记录；deleteAllData()函数用于清空数据表。 （3）定义列表bookData，该列表的元素为元组，包含6个元素，每一个元素存放一本图书的相关数据，对应数据表中一条记录的数据。 （4）删除数据表books中原有的全部记录。 （5）向数据表books中插入6条图书记录数据。 （6）调用自定义函数获取数据表books中有序的记录。 （7）从数据表books中查询出版社名称为“人民邮电出版社”的所有记录。 （8）将数据表books中人民邮电出版社出版图书的价格修改为原价格的90%。 （9）将数据表books中价格低于40元的记录删除。 【任务实施】 在PyCharm项目Unit08中创建Python程序文件8-5.py。在程序文件8-5.py中编写程序代码，实现所需功能，程序文件8-5.py的代码如电子活页8-6所示。 程序文件8-5.py的代码成功运行，则表示实现所需的查询、新增、修改与删除多项功能。
总结评价 本单元主要学习创建与使用SQLite数据表、创建与使用MySQL数据表。

展开更多......

收起↑