8.1.2 SQLite数据库的操作（课件）-《程序设计基础（Python）》同步教学（电子工业出版社）

任务8.1 SQLite数据库操作 学生信息管理系统设计 同学们，大家好，欢迎大家来到本课程第8章的学习。在学习开始前，我们先对这章的学习内容给大家作一个简要的介绍。 1 8.1 SQLite数据库操作——学生信息管理系统设计 【任务描述】 编写学生信息管理系统，需要通过Python连接数据库，来实现学生信息的存储、读取、查找、修改等功能。 8.1 SQLite数据库操作——学生信息管理系统设计 【任务分析】 根据任务需求，对数据库系统进行读写操作，实现对SQLite数据库的访问。 （1）建立数据库连接。 （2）通过执行SQL语句，完成对数据的操作。 （3）系统采用模块化设计，根据不同功能细分多个模块。 8.1 SQLite数据库操作——学生信息管理系统设计 Python应用程序 Python DB API MySQL Oracle SQL Server Python DB API：Python访问数据库的统一接口规范。 Python访问SQLite数据库，可以通过sqlite3模块实现。 Python不能直接对数据库进行连接和操作，必须遵循相应的规则，通过对应的数据库驱动程序，才能连接数据。在python中，客户端连接访问数据库，需要满足Python DB-API标准。Python DB-API是一个规范. 它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 ，有助于开发人员编写跨数据库的可移植的Python应用程序。 Python 数据库接口支持非常多的数据库： MySQL、Microsoft SQL Server、Oracle等。不同的数据库需要使用不同的DB-API模块。 Python的DB-API为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。下面我就以sqlite数据库为例，来学习python如何操作数据库。 4 8.1 SQLite数据库操作——学生信息管理系统设计 8.1.1 SQLite数据库的连接 8.1.2 SQLite数据库操作 8.1.3 任务实现——学生信息管理系统设计 8.1.2 SQLite数据库的操作 同学们，大家好，欢迎大家来到本课程第8章的学习。在学习开始前，我们先对这章的学习内容给大家作一个简要的介绍。 6 8.1 SQLite数据库操作——学生信息管理系统设计 【任务分析】 根据任务需求，对数据库系统进行读写操作，实现对SQLite数据库的访问。 （1）建立数据库连接。 （2）通过执行SQL语句，完成对数据的操作。 （3）系统采用模块化设计，根据不同功能细分多个模块。 1.游标对象 Cursor对象即游标对象，它主要负责暂时保存SQL操作所影响到的数据。游标对象通过数据库的连接对象的cursor()方法创建，方法如下： cur = conn.cursor() 其中，conn为数据库连接对象变量，cur则为游标对象变量。 8.1.1 SQLite数据库的连接 游标对象是数据库访问时的另一个重要对象，在数据库访问中起着重要作用。它负责暂时保存SQL语句操作所影响到的数据。游标对象通过数据库的连接对象的cursor()方法创建。 8 8.1.2 SQLite数据库的操作 属性/方法 说明 connection() 获取当前连接对象 close() 关闭游标 execute(sql[,parameters]) 执行SQL语句，返回受影响的行数。sql是SQL语句，parameters是为sql提供的参数 fetchone() 以元组形式返回一条记录，如果没有结果 则返回 None。 fetchall() 执行SQL查询语句，返回元组序列，即返回查询到的所有记录,如果没有结果则返回 () 游标Cursor对象有很多的属性和方法，其中常用的属性和方法如下： 游标对象的方法能够实现python对数据库数据的各种操作，这里我们先作初步的了解，具体的应用将在后面结合实例来具体讲解。 9 8.1.2 SQLite数据库的操作 在这个流程图中，我们就可以清晰地看出，python操作数据库的步骤及逻辑。这是要求大家必须掌握的内容。 10 2.SQLite数据库访问流程 python访问SQLite数据库可以分为以下几个步骤： （1）创建数据库连接：通过connect()方法创建数据库的连接对象（connection）。 （2）创建游标对象：通过连接对象的cursor()方法创建游标对象（cursor）。 （3）执行SQL操作：通过游标对象的execute()方法执行SQL语句。 （4）如果操作成功，则可通过连接对象的commit()方法提交数据库事务，否则使用rollback()方法回滚数据库事务。 （5）关闭游标：通过游标对象的close()方法关闭游标。 （6）关闭数据库连接：通过数据库连接对象的close()方法关闭数据库连接。 8.1.2 SQLite数据库的操作 python访问不同数据库的方法基本相同，可以分为6个步骤： 1、建立数据库连接：通过connect()方法创建连接数据库的连接对象（connection）。 2、创建游标对象：通过连接对象的cursor()方法创建游标对象（cursor）。 3、执行SQL操作：通过游标对象的execute()方法执行SQL语句 4、如果操作成功，则可通过连接对象的commit()方法提交数据库事务，否则，需使用rollback()方法回滚数据库事务。 5、关闭游标：通过游标对象的close()方法关闭游标。 6、关闭数据库连接：通过数据库连接对象的close()方法关闭连接。下面我们通过流程图更清楚的展示。 11 编写程序：创建students表，包含学号、姓名、性别、年龄字段，代码如下： 【例8-2】创建数据库和数据表。 import sqlite3 # 建立数据库连接 conn = sqlite3.connect('stu.db') print('数据库创建成功！') # 创建游标对象 cur = conn.cursor() 3.SQLite数据库操作 8.1.2 SQLite数据库的操作 sql = ''' create table if not exists students( stuNo varchar(10) primary key, stuName varchar(4), gender varchar(1), age int(3)) ''' # 执行SQL语句，创建表 cur.execute(sql) print('数据表创建成功！') # 提交事务 conn.commit() # 关闭游标 cur.close() # 关闭数据库连接 conn.close() 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 12 常用SQL语句 SQL语句 说明 create database 数据库名; 创建数据库 create table 表名(字段名 字段类型[约束],字段名 字段类型[约束],字段名 字段类型[约束]…); 创建数据表 insert into 表名 (字段1, 字段2,...字段N ) values (值1, 值2,...值N); 向数据表中插入数据 delete from 表名 where 条件表达式; 删除表中记录 update 表名 set 字段1 = 数值1,字段2 = 数值2,字段3 = 数值3… 修改表中数据记录 select 字段1,字段2,……,字段n from 表名; 从数据表中查询数据 8.1.2 SQLite数据库的操作 python要实现对数据库数据的操作，需要结合sql语句，因此，需要大家掌握常用的sql语句。 13 在【例8-2】创建的students表中添加5条数据记录。 【例8-3】添加数据。 import sqlite3 conn = sqlite3.connect('stu.db') cur = conn.cursor() # 在students表中插入5条记录 sql_insert = ''' insert into students values\ ('20201001','钱梅宝','男',20),\ ('20201002','张平光','男',22),\ ('20201003','许动明','男',19),\ ('20201004','张 云','女',19),\ ('20201005','唐 琳','女',20); ''' 8.1.2 SQLite数据库的操作 # 执行插入语句 cur.execute(sql_insert) conn.commit() cur.close() conn.close() 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 14 在执行SQL查询语句之后，需要通过游标对象的fetchone()或fecthall()方法获取结果。 fetchone()：以元组的形式从结果集中提取一条记录，如果没有结果则返回None。 fetchall()：以元组列表的形式返回查询到的所有记录，如果没有结果则返回()。 8.1.2 SQLite数据库的操作 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 15 查询students表中的所有数据，使用fetchone()方法获取数据。 【例8-4】查询数据。 import sqlite3 conn = sqlite3.connect('stu.db') cur = conn.cursor() sql_select = 'select * from students;' # 执行查询语句 cur.execute(sql_select) # 使用fectchone()方法获取数据查询结果，返回记录列表 row = cur.fetchone() while row: print(row) row = cur.fetchone() cur.close() conn.close() 8.1.2 SQLite数据库的操作 ('20201001', '钱梅宝', '男', 20) ('20201002', '张平光', '男', 22) ('20201003', '许动明', '男', 19) ('20201004', '张 云', '女', 19) ('20201005', '唐 琳', '女', 20) 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 16 查询students表中的性别为“女”的学生记录，使用fetchall()方法获取数据。 import sqlite3 conn = sqlite3.connect('stu.db') cur = conn.cursor() sql_select = 'select * from students where gender = "女";' # 执行查询语句 cur.execute(sql_select) # 使用fectchall()方法获取数据查询结果，返回记录构成的列表 rows = cur.fetchall() for row in rows: print(row) cur.close() conn.close() 8.1.2 SQLite数据库的操作 ('20201004', '张 云', '女', 19) ('20201005', '唐 琳', '女', 20) 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 17 对students表中的数据进行更新。 【例8-5】更新数据。 import sqlite3 conn = sqlite3.connect('stu.db') cur = conn.cursor() # 将姓名为张平光的学生的年龄修改为21 sql_update = 'update students set age=21 where stuName="张平光";' # 执行更新语句 cur.execute(sql_update) conn.commit() cur.execute('select * from students where stuName="张平光";') row = cur.fetchone() print('修改后的数据为：') print(row) cur.close() conn.close() 8.1.2 SQLite数据库的操作 修改后的数据为： [('20201002', '张平光', '男', 21)] 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 18 删除students表中的一条记录。 【例8-6】删除数据。 import sqlite3 conn = sqlite3.connect('stu.db') cur = conn.cursor() # 删除学号为20201004的学生记录 sql_del = 'delete from students where stuNo="20201004";' # 执行更新语句 cur.execute(sql_del) conn.commit() cur.execute('select * from students;') rows = cur.fetchall() print('删除后表中数据为：') for row in rows: print(row) cur.close() conn.close() 8.1.2 SQLite数据库的操作 删除后表中数据为： ('20201001', '钱梅宝', '男', 20) ('20201002', '张平光', '男', 21) ('20201003', '许动明', '男', 19) ('20201005', '唐 琳', '女', 20) 这里我们通过一个示例来演示python编程操作数据的全部流程。通过编写python程序来创建students表，表包含学号、姓名、性别、年龄等字段。 访问数据库6个步骤，第一步，通过sqlite3模块的connect()函数来创建连接对象；第二步，使用连接对象的cursor()方法创建游标对象。 19 4．占位符 在通过游标对象的execute()方法执行一条SQL语句时，如果SQL语句带有参数，则可以使用占位符来传递参数。占位符只能用于设置value，不能用于设置表名、字段等。SQLite3支持两种占位符：问号占位符和命名占位符。 （1）问号占位符采用问号作为占位符，参数为元组或列表形式。 （2）命名占位符采用冒号加key的形式作为占位符，参数为字典形式。 cur.execute('select * from students where gender=?;',('男',)) cur.execute('select * from students where age=:age and gender=:sex;', {'age':21,'sex':'男'}) 8.1.1 SQLite数据库的连接 游标对象是数据库访问时的另一个重要对象，在数据库访问中起着重要作用。它负责暂时保存SQL语句操作所影响到的数据。游标对象通过数据库的连接对象的cursor()方法创建。 20 8.1.1 SQLite数据库的连接 边学边练： 编写程序，完成以下功能： （1）创建SQLite数据库，在库中创建学生信息表，并输入部分学生信息。 （2）根据用户输入的学号，查询对应的学生信息。 � � 建立数据库连接 创建游标对象 执行SQL 操作 提取操作结果 操作成功 回滚数据库事务 查询操作 修改操作 提交数据库事务 关闭游标 是 否 关闭数据库连接 $$