Python程序设计与应用课件 第9章 Python数据库设计与应用

Python程序设计与应用第9章Python数据库设计与应用学习目标知识目标理解和掌握数据库基本概念、关系数据库和Python数据库访问模块；掌握常用的SQL数据库操作；理解和掌握SQLite数据类型、数据库的访问和SQLite数据库函数；掌握SQLite数据库应用，包括SQLite数据库的查看与维护、数据库与表的创建、数据库表的插入、修改与删除、数据库表的查询；理解和掌握SQLite可视化工具SQLiteStudio的使用；理解数据库操作的错误和异常。技能目标能够利用SQL实现数据库操作；能够利用SQLite数据库技术实现数据库操作；具备一定的程序优化技能。素质目标与课程思政熟悉数据库设计与应用方法；培养善于观察、勤于思考的习惯，提高解决问题的分析与归纳能力，树立解决问题的工程化思想；培养学生积极向上、奋发图强的精神。知识导图9.1数据库概述9.1.1基本概念9.1.2关系数据库9.1.3Python数据库访问模块9.1.1基本概念工作中常常需要处理大量数据。利用数据库技术，能将大量数据按照一定的方式组织并存储起来，便于管理和维护。数据库技术涉及的基本术语有数据库(DataBase,DB)、数据库管理系统(DataBaseManagementSystem,DBMS)、数据库系统(DataBaseSystem,DBS)。1.数据库数据库可以理解为存储数据的仓库，是按照一定的组织方式将相关数据组织在一起并存储在计算机外存储器上所形成的、可动态变化的、与应用程序彼此独立的、不同用户可以共享的一组相关数据的集合。例如，学校相关部门常常需要把学生的基本情况（学号、姓名、性别等）存放在表中，这张表就是一个数据库。2.数据库管理系统数据库管理系统是用户建立、使用和管理数据库的软件系统。它使用户能方便地定义数据和操纵数据，以保证数据库的安全性和完整性，支持多用户对数据的并发使用。2026/7/2569.1.1基本概念数据库管理系统具有以下功能：1)数据定义。提供了数据定义语言(DataDefinitionLanguage,DDL)，供用户定义数据库结构，建立所需的数据库和表。2)数据存取。提供了数据操纵语言(DataManipulationLanguage,DML)，实现对数据库和表中的数据进行基本的存取操作：检索、插入、修改和删除。3)数据库运行管理。提供了核心控制程序，实现对数据库运行操作的统一管理，包括安全性、完整性和并发控制等。4)数据库的建立和维护。提供了一组实用程序，完成数据库初始数据的载入、转换，数据库的转储、恢复、重组织、性能监视和分析等。5)数据库的传输。提供处理数据的传输，实现用户程序与DBMS之间的通信。常用的数据库管理系统有MySQL、MSSQL、ORACLE等。2026/7/2579.1.1基本概念3.数据库系统数据库系统是以计算机系统为基础，用数据库技术管理大量共享数据的综合系统。数据库系统一般由计算机硬件系统、操作系统、数据库、数据库管理系统、应用程序、数据库管理员、应用程序员和用户构成。2026/7/2589.1.2关系数据库使用计算机来处理现实世界的具体事物时，必须先把具体事物转化为抽象的模型，再转化为计算机可以处理的数据模型。数据模型描述了客观事物及其联系的数据化描述。数据库系统中的基本数据模型有4种：层次模型、网状模型、关系模型和面向对象模型。目前，使用最广泛的数据模型是关系模型。1.关系模型关系模型把世界看作是实体(Entity)和联系(Relationship)构成的。实体是指现实世界中可以相互区别、具有一定特征或属性的对象。联系是指实体之间的关系，可以分为3种：一对一；一对多；多对多。2026/7/2599.1.2关系数据库1)一对一(1:1)：一个实体集A中的每个实体至多只与另一个实体集B中的一个实体联系，反之亦然。例如，一个班级只要一位班长，并且一个班长只能管理一个班级，所以班级实体集和班长实体集之间就是一对一联系。2)一对多(1:n)：一个实体集A中的每个实体与另一个实体集B中的多个实体有联系，反之，实体集B中的每个实体至多与实体集A中的一个实体相联系。例如，一个班级有许多学生，但每个学生只能在一个班级学习，所以班级和学生是一对多的联系。3)多对多(m:n)：一个实体集A中的每个实体与另一个实体集B中的多个实体联系，反之，对于实体集B中的每个实体，也能在实体集A中找到多个与它相对应的实体。例如，一个学生可以选修多门课程，而每一门课程可以被多个学生选修，所以学生实体集和课程实体集之间就是多对多联系。2026/7/25109.1.2关系数据库实体-联系方法(Entity-RelationshipApproach)，简称E-R方法，是常用的描述关系数据库模型的方法。E-R方法的规则是：用长方形表示实体，并在长方形内写上实体名称；用椭圆表示实体属性，椭圆内写上属性名称，并用无向边（即直线）将实体与其属性连接起来；用菱形表示实体间的联系，菱形框内写上联系名称，并用无向边将菱形与相关实体相连接，在无向边旁标上联系的类型(1:1,1:n,m:n)。若实体之间的联系也具有属性，则把属性和菱形也用无向边连接上。2026/7/2511一对一联系一对多联系多对多联系9.1.2关系数据库关系数据库使用表（又称数据表，可简称“表”）来存储数据。表由行(Row)和列(Column)组成。每一行称为一个元组(tuple)，相当于一个记录值。每一列是一个属性(attribute)值集，属性的取值范围称为域(domain)，相当于数据项或字段(Field)，列的标题称为字段名。例如，下表中有7个属性（学号，姓名，性别，专业，出生日期，籍贯，奖学金）。数据表中通常都有一个主关键字(PrimaryKey)，又称主码或主键，通过主关键字可以唯一确定一条记录。主关键字可以是一个属性，也可以是几个属性的组合。例如，表中“学号”字段即为学生表的主关键字。2026/7/2512学号姓名性别专业出生日期籍贯奖学金202459225101林文伟男计算机科学与技术2005-8-20四川成都1200202459225203方晓娜女计算机科学与技术2005-7-16浙江台州

202452125101肖青可女工商管理2006-6-15重庆9.1.2关系数据库2.关系操作关系模型的数据操作主要包括查询、插入、修改和删除数据。这些数据操作是基于集合的操作，操作对象和操作结果都是集合（或关系）。关系操作必须要满足关系的数据完整性约束条件。3.关系模型的数据完整性约束数据完整性是指数据库中存储的数据是有意义或是正确的。关系数据库模型中的数据完整性规则是对关系的某种约束条件，主要包括3大类：实体完整性、参照完整性和用户定义完整性。2026/7/25139.1.2关系数据库1)实体完整性实体完整性要求基本关系的所有主键对应的属性不能为空，且主键取值具有唯一性，即通过主键就可以区别不同的记录。2)参照完整性参照完整性也称为引用完整性。参照完整性指明多个表之间的关联关系，它不允许引用不存在的记录。例如，在选课数据表中的学生必须受限于学生数据表中存在的学生。3)用户自定义完整性用户自定义完整性又称为域完整性或应用语义完整性，是针对某一具体应用定义的数据必须满足的取值类型及取值范围。例如，学生考试成绩的取值范围为0~100。2026/7/25149.1.3Python数据库访问模块Python数据库访问模块有2类：通用数据库访问模块和专用数据库访问模块。1.通用数据库访问模块1)ODBCODBC（OpenDatabaseConnectivity，开放数据库互连）提供了一种标准的应用程序编程接口(ApplicationProgrammingInterface,API)方法来访问数据库管理系统。ODBCPython接口连接数据库需要ODBC驱动，包括mxODBC、pyodbc、turbodbc、ceODBC、mxODBCConnect等。2)JDBCJDBC（JavaDatabaseConnectivity，Java数据库连接）是基于Java的面向对象的应用编程接口，描述了一套访问关系数据库的Java类库标准。在Python2.1以后的发行版中，包括通过JDBC访问数据的模块zxJDBC，建立在底层的JDBC接口之上，支持DB-API2.0接口。2026/7/25159.1.3Python数据库访问模块2.专用数据库访问模块Python针对各种流行的数据库，提供了各种专用的数据库访问模块，如表所示。2026/7/2516数据库Python模块网址MySQLMySQL-python/projects/mysql-python/PostgreSQLPyGreSQL/Oraclepython-oracledb/project/oracledb/IBMDB2PyDB2/projects/pydb2/SQLServerpymssql/project/pymssql/9.2SQL结构化查询语言9.2.1数据表的建立与删除9.2.2数据查询9.2.3数据表记录操作SQL(StructuredQueryLanguage，结构化查询语言)是通用的关系数据库操作语言，具有数据定义、数据操纵等多种功能。SQL是一种非过程化语言，使用SQL时，只需要告诉计算机“做什么”，而不需要告诉它“怎么做”。SQL不是独立的编程语言，没有for、while等循环语句。使用SQL的方式有2种：一是独立应用于终端，以命令方式交互使用；二是嵌入到其它程序语言（如Python、C、C++等）中使用。SQL对关键字大小写不敏感，为方便区分SQL语句和Python语句，本教材中的SQL语句采用大写形式。SQL共分为4大类，即数据定义语言、数据查询语言、数据操纵语言和数据控制语言，分别完成数据定义、数据查询、数据操纵和数据控制的功能，且总共只用了9个动词：CREATE、DROP、ALTER、SELECT、INSERT、DELETE、UPDATE、GRANT、REVOKE。9.2.1数据表的建立与删除利用SQL可以进行数据表的建立与删除。1.创建表SQL使用CREATETABLE语句创建数据库的表，其基本语法格式如下：CREATETABLE表名称(

列名1数据类型[约束],

列名2数据类型[约束],

列名3数据类型[约束], ……)2026/7/25189.2.1数据表的建立与删除说明：1)数据类型包括：整数类型(tinyint,smallint,mediumint,int,bigint)，实数类型(float,double,decimal)，位类型(bit(m))，日期和时间类型(date,datetime,timestamp,time,year)，字符串类型(char,varchar,tinytext,text,mediumtext,longtext,tinyblob,blob,mediumblob,longblob)。2)约束，常用的约束主要有以下几种：(1)主键(primarykey)：设置某个或某几个字段为主键，通过主键唯一标识一条记录。主键不能重复，其值不能为空。(2)非空(notnull)：字段值不能有空值，否则出错。(3)唯一(unique)：字段值唯一。(4)默认(default)：当为字段设置值时，将自动采用默认值。(5)外键(foreignkey)：假设A表的B字段是唯一的，而C表的D字段的值引用了B字段的值，则这个引用关系称为外键。D字段的值只能来自于B字段已存在的值。2026/7/25199.2.1数据表的建立与删除创建students表，该表包含6个字段：stuNumber，stuName，sex，major，score，city。参考语句代码如下：CREATETABLEstudents(stuNumbervarchar(12),primarykey,stuNamevarchar(20),sexvarchar(2),majorvarchar(20),scoreint(4),cityvarchar(20))2026/7/25209.2.1数据表的建立与删除2.删除表SQL使用DROPTABLE语句删除表，表的结构、属性以及索引也会被删除，其基本语法格式如下：DROPTABLE表名称例如，删除students表。参考语句代码如下：DROPTABLEstudents2026/7/25219.2.2数据查询数据查询是指从数据库查询需要的数据记录，是最常用的数据库操作。SQL使用SELECT语句实现查询功能。语法格式如下：SELECT字段1,字段2,字段3,……FROM表名WHERE查询条件GROUPBY分

组字段ORDERBY字段[ASC|DESC]查询语句SELECT包括字段表、FROM子句、WHERE子句、GROUPBY子句和ORDERBY子句。它们分别表明所查询的列、查询的表或视图、搜索条件、分组字段和排序字段。2026/7/25229.2.2数据查询1.字段表和FROM子句字段表指出要查询的列，由一组字段名（用逗号间隔）、星号（表示所有列）、表达式、变量等构成。FROM子句指出要查询的表。使用FROM子句的数据查询示例如下：1)查询students表中所有记录的stuNumber、stuName字段内容。参考语句代码如下：SELECTstuNumber,stuNameFROMstudents2)查询students表中所有记录的所有字段内容。参考语句代码如下：SELECT*FROMstudents2026/7/25239.2.2数据查询2.WHERE子句WHERE子句用来设置查询条件，过滤掉不满足条件的记录。查询条件可包括各种运算符。这些运算符包括：1)比较运算符（大小比较）：>、>=、=、<、<=、<>、!>、!<例如，查询students表中学生名为“张三”的学号。参考语句代码如下：SELECTstuNumberFROMstudentsWHEREstuName='张三'2)范围运算符（表达式的值是否在指定范围内）：BETWEEN...AND...、NOTBETWEEN...AND...例如，查询students表中成绩在90~100分的学生姓名。参考语句代码如下：SELECTstuNameFROMstudentsWHEREscoreBETWEEN90AND1003)列表运算符（判断表达式是否为指定项）：IN(项1，项2，...)、NOTIN(项1，项2，...)例如，查询students表中籍贯(city)是“北京”或“上海”的学生姓名。参考语句代码如下：SELECTstuNameFROMstudentsWHEREcityIN('北京','上海')2026/7/25249.2.2数据查询4)逻辑运算符（用于多个条件的逻辑连接）：NOT、AND、OR例如，查询students表中年龄大于18岁的男生姓名。参考语句代码如下：SELECTstuNameFROMstudentsWHEREage>18ANDsex='男'5)模式匹配符（判断是否与指定的字符统配格式相符）：LIKE、NOTLIKE“LIKE”和“NOTLIKE”这2个模式匹配符常用于模糊查询。例如，查询students表中姓李的所有学生信息。参考语句代码如下：SELECT*FROMstudentsWHEREstuNameLIKE'李%%'其中，%可匹配任意类型和长度的字符串，如果是匹配中文，则用两个百分号，即%%。例如，查询students表中成绩在60~70之间的所有学生信息。参考语句代码如下：SELECT*FROMstudentsWHEREscoreLIKE[60-70]其中，[]用来指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。[^]则表示所匹配对象为指定字符以外的任一字符。2026/7/25259.2.2数据查询3.GROUPBY子句GROUPBY子句用于依据一个或多个字段对结果集进行分组，通常结合聚合函数进行使用。常用的聚合函数及其功能如表所示。例如，统计students表中所有男生的平均成绩。参考语句代码如下：SELECTavg(score)asavgScoreFROMstudentsGROUPBYsexWHEREsex='男'2026/7/2526函数功能函数功能avg(字段名)求平均值min(字段名)求最小值count(字段名)统计记录数sum(字段名)求和max(字段名)求最大值

9.2.2数据查询4.ORDERBY子句SQL使用ORDERBY子句对查询返回的结果按指定的单个或多个字段进行升序（用“ASC”标记）或降序（用“DESC”标记）排序，默认为升序。例如，查询students表的stuName、stuNumber字段，查询结果按照成绩降序排列。参考语句代码如下：SELECTstuName,stuNumberFROMstudentORDERBYscoreDESC2026/7/25279.2.3数据表记录操作SQL数据表记录操作有：添加记录、修改记录和删除记录。1.添加记录SQL通过命令INSERTINTO语句向数据表中插入新的行，语法格式如下：INSERTINTO表名称(字段1,字段2,字段3,…)VALUES(值1,值2,值3,…)当INSERTINTO语句中的字段列表中的字段的顺序与数据表中字段的顺序一致时，可以省略INSERTINTO语句中的字段列表，即语法格式为：INSERTINTO表名称VALUES(值1,值2,值3,…)例如，向students表添加一条记录。参考语句代码如下：INSERTINTOstudents(stuNumber,stuName,sex,major,score,city)VALUES('202459225116','张三','男','计算机科学与技术',87,'浙江嘉兴')或INSERTINTOstudentsVALUES('202459225116','张三','男','计算机科学与技术',87,'浙江嘉兴')2026/7/25289.2.3数据表记录操作2.修改记录SQL通过UPDATE语句修改表中的数据，语法格式如下：UPDATE表名称SET字段1=新值1,字段2=新值2,…WHERE条件修改记录的示例如下：1)将students表中sex为“女”的学生的年龄增加1岁。参考语句代码如下：UPDATEstudentsSETage=age+1WHEREsex='女'2)将students表中学生张三的专业及分数分别修改为“软件工程”、90。参考语句代码如下：UPDATEstudentsSETmajor='软件工程',score=90WHEREstuName='张三'2026/7/25299.2.3数据表记录操作3.删除记录SQL通过DELETEFROM语句删除表中的记录，语法格式如下：DELETEFROM表WHERE条件例如，删除students表中的“张三”的记录。参考语句代码如下：DELETEFROMstudentsWHEREstuName='张三'例如，删除students表中所有记录。参考语句代码如下：DELETEFROMstudents2026/7/25309.3SQLite数据库技术9.3.1SQLite数据类型9.3.2SQLite数据库的访问9.3.3SQLite数据库函数9.3.1SQLite数据类型SQLite采用动态的数据类型，即根据存入值自动判断数据类型。这使得SQLite能兼容其他数据库普遍使用的静态类型，也就意味着使用静态数据类型的数据库上使用的数据表，也可以在SQLite上使用。每个存放在SQLite数据库中的值，归属于下表中的数据类型之一。SQLite采用弱数据类型，除了被声明为主键的INTEGER类型的列外，任何表的任何列可以保存任何类型的数据，与列的类型声明无关，也完全可以不声明列的类型。2026/7/2532数据类型说明BLOB无类型，保存二进制数据INTEGER带符号整数，根据存入的数值的大小占据1、2、3、4、6或者8个字节REAL浮点数TEXT字符串文本，采用数据库的编码（UTF-8、UTF-16BE或UTF-16LE）NULL空值9.3.2SQLite数据库的访问访问和操作SQLite数据库时，首先需要导入sqlite3模块；然后创建一个关联数据库的Connection对象；再创建一个游标Cursor对象，通过调用Cursor对象的execute()方法来执行SQL语句创建数据表以及查询、插入、修改或删除数据库中的数据；最后关闭Cursor对象和Connection对象。下图为访问和操作SQLite数据库的流程图。2026/7/25339.3.2SQLite数据库的访问1.导入SQLite模块Python标准库中带有SQLite3模块，可以直接导入该模块，其语法格式如下：importsqlite32.建立数据库连接，返回Connection对象导入数据库模块后，通过使用数据模块的connect()函数返回连接数据库的Connection对象，语法格式如下：conn=sqlite3.connect(connectstring)#连接到数据库，返回sqlite3.Connection对象其中，connectstring是连接字符串。sqlite的连接字符串为数据库的文件名。如果文件存在，则打开数据库，否则创建并打开数据库。如果指定连接字符串为“:memory:”，则可以创建一个内存数据库。2026/7/25349.3.2SQLite数据库的访问Connection是sqlite3模块中最基本也是最重要的一个类，其主要方法及其说明如表所示。2026/7/2535方法说明execute(sql[,parameters])执行一条SQL语句executemany(sql[,parameters])执行多条SQL语句cursor()返回连接的游标commit()提交当前事务，如果不提交，那么自上次调用commit()方法之后的所有修改都不会真正保存到数据库中rollback()撤销当前事务，将数据库恢复至上次调用commit()方法后的状态close()关闭数据库连接create_function(name,num_params,func)创建可在SQL语句中调用的函数，其中name为函数名，num_params表示该函数可以接收的参数个数，func表示Python可调用对象9.3.2SQLite数据库的访问例如，以下代码创建了连接D盘目录下数据库名称为test.db的对象conn。如果D盘目录下不存在test.db数据库，则建立一个空数据库test.db。conn=sqlite3.connect('D:/test.db')创建数据库连接对象（Connection对象）后，可以设置其属性。例如：conn.isolation_level=None #设置事务隔离级别，默认为自动提交conn.row_factory=sqlite3.Row #设置连接对象使用的行工厂对象3.创建游标对象除了使用where子句来限制只有一条记录被选中，SQLite没有其它描述数据表中单一记录的表达形式。如果要处理单条记录，需要借助具有定位功能的游标(Cursor)。例如，以下代码通过数据库连接对象的curse()方法创建了游标对象cur。cur=con.cursor()2026/7/25369.3.2SQLite数据库的访问4.使用游标对象的方法执行SQL命令返回结果集游标Cursor也是sqlite3模块中比较重要的一个类，下面简单介绍Cursor对象的常用方法。1)execute(sql)：执行一条SQL语句。2)execute(sql,parameters)：执行带参数的SQL语句。3)executemany(sql,seq_of_parameters)：对于所有给定参数执行同一个SQL语句，参数序列可以使用不同的方式产生。4)executescript(sql_script)：执行SQL脚本。2026/7/25379.3.2SQLite数据库的访问例如，创建一个表Course。参考语句代码如下：cur.execute("CREATETABLECourse(Course_coding,Course_name,Credits,Hours)")上面代码将创建一个包含4个字段Course_coding、Course_name、Credits和Hours的表Course。下面向表中插入记录：cur.execute("INSERTINTOCourseVALUES(2402536005,"大学计算机",3,56)")SQL语句字符串中可以使用占位符“?”表示参数，传递的参数使用元组。例如：cur.execute("INSERTINTOCourseVALUES(?,?,?,?)",(2402536001,"马克思主义原理概述",2.5,40))一般地，建议直接使用Connection对象的execute/executemany/executescript方法。事实上，它们是Cursor对象对应方法的快捷方式，系统创建一个临时Cursor对象，然后调用对应的方法，并返回Cursor对象。例如：conn.execute("CREATETABLEIFNOTEXISTSCourse(Course_coding,Course_name,Credits,Hours)")2026/7/25389.3.2SQLite数据库的访问5.获取游标的查询结果集通过调用游标对象的方法返回查询结果，方法及其说明如下：1)fetchone()：返回结果集的下一行（Row对象）；无数据时，返回None。2)fetchall()：返回结果集的剩余行（Row对象列表）；无数据时，返回空列表。3)fetchmany()：返回结果集的多行（Row对象列表）；无数据时，返回空列表。4)rowcount()：返回影响的行数、结果集的行数。2026/7/25399.3.2SQLite数据库的访问例如，运行以下代码：cur.execute("SELECT*FROMCourse")printcur.fetchall() #提取查询到的数据返回结果如下：[(2402536005,"大学计算机",3,56),2402536001,"马克思主义原理概述",2.5,40)]如果使用cu.fetchone()，则首先返回列表中的第1个元素，再次使用cu.fetchone()，返回第2个元素，依次进行。也可以直接使用循环语句输出查询结果，参考语句代码如下：forrowincur.execute("SELECT*FROMCourse")print(row[0],row[1])2026/7/25409.3.2SQLite数据库的访问6.数据库的提交和回滚根据数据库事务隔离级别的不同，可以提交或回滚，命令形式如下：mit() #事务提交conn.rollback() #事务回滚7.关闭Cursor对象和Connection对象最后，需要关闭打开的Cursor对象和Connection对象，命令形式如下：cur.close() #关闭Cursor对象coon.close() #关闭Connection对象2026/7/25419.3.3SQLite数据库函数SQLite数据库中的函数主要包括数值计算函数、字符串处理函数、日期时间函数、数据库函数及其他函数，核心函数如表所示。2026/7/2542类别函数说明数值计算函数abs(x)返回x的绝对值。如果x为NULL，则返回NULL；如果x为不能转换成数值的字符串，则返回0；如果x值超出整型数据的上限，则抛出异常

max(x,y,...)返回参数中的最大值，如果参数中有NULL，则返回NULL

min(x,y,...)返回参数中的最小值，如果参数中有NULL，则返回NULL

random()返回整型的伪随机数

round(x[,y])返回参数x的四舍五入，y为小数点位数，y的默认值为09.3.3SQLite数据库函数2026/7/2543字符串处理函数length(stra)返回字符串字符个数，如果stra为数值，则返回stra的字符串表示形式的长度，如果stra为NULL，则返回NULL

lower(stra)返回stra的小写形式

ltrim(stra[,strb])删除stra左侧的任意在strb中出现的字符；如果不指定strb，则删除stra两侧的所有空格。最后返回删除后的字符串

replace(stra,strb,strc)将字符串类型的stra中所有子字符串strb替换为字符串strc，并返回替换后的字符串，原字符串stra保持不变

rtrim(stra[,strb])删除stra右侧的任意在strb中出现的字符；如果不指定strb，则删除stra两侧的所有空格。最后返回删除后的字符串

substr(stra,x[,y])返回字符串stra中第x位（位置序号从1开始）开始、长度为y的子字符串；如果不指定参数y，则取第x位开始的后面所有字符；如果y为负数，则从第x位开始，向左截取abs(y)个字符；如果x为负数，则从字符串stra的尾部开始第abs(x)的位置开始截取

trim(stra[,strb])删除stra两侧的任意在strb中出现的字符；如果不指定strb，则删除stra两侧的所有空格。最后返回删除后的字符串

upper(stra)返回stra的大写形式9.3.3SQLite数据库函数2026/7/2544日期时间函数date()设置日期

time()设置时间

datetime()设置日期时间数据库函数changes()统计最近执行的INSERTINTO、UPDATE和DELETEFROM等SQL语句所影响的记录条数

total_changes()统计从连接表起，INSERTINTO、UPDATE和DELETEFROM等SQL语句所影响的记录条数其它函数typeof(x)返回x的数据类型

ifnull(x,y)返回第一个不是NULL的参数，如果两个参数均为NULL，则返回NULL

nullif(x,y)x,y相同时返回NULL，否则返回x

coalesce(x,y,…)返回第一个非NULL的参数，如果参数全都是NULL，则返回NULL9.3.3SQLite数据库函数SQLite还支持聚合函数。聚合函数也称为组函数，是在行的集合上进行操作。聚合函数常用于对数据进行分类汇总和统计分析，如表所示。2026/7/2545函数说明avg(field)求平均值。不能转换为数值的字符串和布尔值被视为0。所有字段值均为NULL时，返回NULL，否则返回浮点型数据count(field或*)统计字段field中值不等于NULL的行数，并返回数据行数max(field)求字段field中的最大值，字段中所有值均为NULL时，返回NULLmin(field)求字段field中的最小值，字段中所有值均为NULL时，返回NULLsum(field)求字段field中所有值的和，如果字段field中所有值均为NULL，返回NULL；如果字段field中所有值均为整型数据，该函数返回整型值，否则返回浮点型值total(field)功能和sum(field)相似，函数始终返回浮点型数值，字段field中所有值均为NULL时，返回0.09.4SQLite数据库应用9.4.1SQLite数据库的查看与维护9.4.2数据库与表的创建9.4.3数据库表的插入、修改与删除9.4.4数据库表的查询9.4.5错误处理9.4.1SQLite数据库的查看与维护使用SQLite可视化工具SQLiteStudio可以方便地查看程序运行后更新数据库的结果。下载、安装和使用SQLiteStudio.exe的详情介绍如下。1.下载SQLiteStudio.exe在浏览器中输入SQLiteStudio官网地址“https://sqlitestudio.pl/”，下载最新版本SQLiteStudio-3.4.4。2.安装SQLiteStudio运行SQLiteStudio.exe，根据提示安装SQLiteStudio。3.打开数据库打开SQLiteStudio，使用“添加数据库”命令，选择数据库文件（例如student.db）；使用“连接到数据库”命令建立数据库连接；选中数据表（例如StudentInfo），点击“编辑表”命令，查看表结构，如图所示。2026/7/25479.4.1SQLite数据库的查看与维护2026/7/25489.4.2数据库与表的创建使用sqlite3.connect("数据库文件名")可以创建或打开SQLite数据库，并返回连接对象conn；使用conn.execute("CREATETABLE…")可以创建表。【例9.1】创建一个数据库stuinfo，并在其中创建数据库表student，表中包含5个字段：stuid、stuname、birthday、sex、rxrq，分别表示学号、姓名、出生日期、性别和入学日期，其中stuid设为主键。参考程序代码如下：importsqlite3 #导入PythonSQLite数据库模块conn=sqlite3.connect("D:/stuinfo.db") #创建SQLite数据库#创建数据库表studentconn.execute("CREATETABLEstudent(stuidprimarykey,stuname,birthday,sex,rxrq)")2026/7/25499.4.2数据库与表的创建创建数据库和数据表student后，可以使用SQLiteStudio进行查看，如图所示。2026/7/25509.4.3数据库表的插入、修改与删除在数据库表中插入、更新和删除记录的一般步骤如下：1.建立数据库连接；2.创建游标对象cur，使用cur.execute(sql)执行SQL的INSERT、UPDATE、DELETE等语句完成数据库记录的插入、更新、删除等操作，并根据返回值判断操作结果；3.提交操作；4.关闭数据库。【例9.2】数据库表记录的插入、修改和删除操作。参考程序代码如下：importsqlite3 #导入PythonSQLite数据库模块conn=sqlite3.connect("D:/stuinfo.db") #打开数据库cur=conn.cursor() #创建游标对象2026/7/25519.4.3数据库表的插入、修改与删除#插入一行数据cur.execute('''INSERTINTOstudent(stuid,stuname,birthday,sex,rxrq)VALUES('202459225101','林文伟','2005-8-20','男','2024-8-26')''')cur.execute('''INSERTINTOstudent(stuid,stuname,birthday,sex,rxrq)VALUES(?,?,?,?,?)''',('202459225203','方晓娜','2005-7-16','女','2024-8-26'))stulist=[('202452125101','肖青可','2006-6-15','女','2024-8-26'),('202459225109','杨林峰','2005-7-4','男','2024-8-26'),('202459455101','王嘉明','2006-3-7','男','2024-8-26')] cur.executemany("INSERTINTOstudent(stuid,stuname,birthday,sex,rxrq)VALUES(?,?,?,?,?)",stulist) #插入多行记录2026/7/25529.4.3数据库表的插入、修改与删除#修改一行记录cur.execute("UPDATEstudentSETbirthday=?WHEREstuname=?",('2005-9-20','林文伟'))#删除一行记录m=cur.execute("DELETEFROMstudentWHEREstuname=?",('方晓娜',))print("删除了",m.rowcount,"行记录")mit()cur.close()conn.close()运行结果如下：删除了1行记录2026/7/25539.4.3数据库表的插入、修改与删除程序运行之后，使用SQLiteStudio查看数据表student，如图所示。2026/7/25549.4.4数据库表的查询查询数据库表的步骤如下：1.建立数据库连接；2.创建游标对象cur，使用cur.execute(sql)执行SQL的select语句；3.循环输出结果。【例9.3】数据库表的查询。参考程序代码如下：importsqlite3 #导入PythonSQLite数据库模块conn=sqlite3.connect("D:/stuinfo.db") #打开数据库cur=conn.cursor() #创建游标对象cur.execute("SELECT*FROMstudent") #查询数据库表forrowincur:print(row)2026/7/25559.4.4数据库表的查询运行程序，结果如下：('202459225101','林文伟','2005-9-20','男','2024-8-26')('202452125101','肖青可','2006-6-15','女','2024-8-26')('202459225109','杨林峰','2005-7-4','男','2024-8-26')('202459455101','王嘉明','2006-3-7','男','2024-8-26')2026/7/25569.4.5错误处理2026/7/2557异常说明Warning严重警告，如插入数据时被截断等。Error一般错误，警告以外所有其他错误类。InterfaceError接口错误，当数据接口模块发生错误时触发（不是数据库的错误）。DatabaseError数据库错误，发生和数据库相关的错误时触发。DataError数据错误，数据处理发生错误时触发，如除零错误、数据超范围等。OperationError操作错误，操作数据库时发生的错误，如连接意外断开、事务处理失败、内存分配错误等操作。IntegrityError完整性错误，如外键检查失败等。InternalError数据库内部错误，如游标失效、事务同步失败等。ProgrammingError程序错误，如数据表没找到、SQL语句语法错误、参数数量错误等。NotSupportedError不支持错误，使用了数据库不支持的函数或API等，如连接对象使用.rollback()函数，而数据库并不支持事务或者事务已关闭。9.5应用实例通过一个综合应用实例，根据SQLite数据库技术提供的功能，实现SQLite数据库的添加、删除、修改与查看等操作。9.5应用实例【例9.4】梅花不畏严寒，在隆冬美丽绽放，是中国文人墨客经常吟咏的主题。设计一个数据库来记录与梅花相关的诗词，实现数据库信息的添加、删除、修改和查看。分析：设计数据库数据表包含4个字段：title、author、verse、notes，分别表示题目、作者、诗/词句和注释。使用PRIMARYKEY声明title和author共同组成主键。根据分析，参考程序代码如下：importsqlite3defopendb(): #创建/打开数据库conn=sqlite3.connect("D:/mytest.db")cur=conn.execute('''CREATETABLEIFNOTEXISTSmeihua(title,author,verse,notes,PRIMARYKEY(title,author))''')returncur,conn2026/7/25599.5应用实例defshowallitems(): #查询全部记录print("--------处理后的数据记录--------")ope=opendb()cur=ope[1].cursor()cur.execute("SELECT*FROMmeihua")res=cur.fetchall()forrecordinres:foriinrecord:print(i)print()cur.close()2026/7/25609.5应用实例defadd(): #输入信息title=input("请输入作品名称：")author=input("请输入作者名：")verse=input("请输入诗/词句：")notes=input("请输入注释：")returntitle,author,verse,notes2026/7/25619.5应用