Python程序设计案例教程第2版第12章数据库开发

SSM框架应用于沈工高职考试系统的设计与实践第12章数据库开发数据库基础知识12.12关系型数据库1数据库的概念12.1.1数据库的概念数据库（Database）是数据的集合，数据库能将大量数据按照一定的方式组织并存储起来，方便地进行管理和维护。数据库管理系统（DatabaseManagementSystem，DBMS）是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库。它所提供的功能有以下几项：（1）数据定义功能。定义数据库结构，用于创建数据库框架，并保存在数据字典中。（2）数据存取功能。实现对数据库的增加、删除、修改、查询和检索等基本操作。

（3）数据库运行管理功能。提供数据控制功能，即数据的安全性、完整性等。（4）数据库建立和维护功能。包括初始数据的装入，数据库的存储、恢复、重组织、系统性能监视和分析等功能。（5）数据库的传输。实现用户程序与DBMS之间的通信。12.1.2构建简单的GUI数据库可以分为关系型数据库和非关系型数据库。关系型数据库使用二维表来存储数据，非关系型数据库通常以对象的形式存储数据。SQLite就是关系型的、轻量级的数据库管理系统。它由数据表之间的关系组成，如学生管理系统中的学生信息表如图所示。模块12：SQLite库12.22下载和运行SQLite数据库SQLite库的概念13SQLite3常用命令5SQLite3的常用函数SQLite3的数据类型46SQLite库的对象SQLite是用C语言编写的开源、嵌入式数据库，它的体积很小，经常被集成到各种应用程序中，可在Windows、UNIX、Linux、Android、IOS等操作系统中运行。SQLite不需要一个单独的服务器进程或操作系统（无服务器的），也不需要配置。SQLite支持SQL92（SQL2）标准的大多数查询语言的功能，并提供了简单和易于使用的API。12.2.1SQLite库的概念SQLite官网下载地址：/download.html不需要安装，直接运行sqlite3．exe，即可打开SQLite数据库的命令行窗口，在此界面中可以建立和管理SQLite数据库，建立表和查询等。12.2.2下载和运行SQLite数据库命令分类：SQLite3交互模式常用的命令操作数据库的SQL命令交互命令使用：在命令提示符后输入.help命令，将列出交互命令的提示信息输入.exit可退出命令行窗口12.2.3SQLite3常用命令SQLite3常用交互命令：12.2.3SQLite3常用命令类型说明sqlite3.exe[dbname]启动SQLite3的交互模式，并创建dbname数据库.open

dbname若数据库不存在，就创建数据库；若数据库存在，则打开数据库.databases显示当前打开的数据库文件.tables查看当前数据库下的所有表.schema[tbname]查看表结构信息.help列出命令的提示信息.exit退出交互模式SQLite3使用动态数据类型，系统会根据列值自动判断列的数据类型。SQLite3使用弱数据类型，除了主键的integer类型的列，允许保存任何类型的数据到表的任何列中。12.2.4SQLite3的数据类型类型说明类型说明smallint16位整数varchar(n)不固定长度的字符串，n不能大于4000integer32位整数graphic(n)和char(n)一样，单位是两个字节。n不能大于127decimal(p,s)小数，p是数字的位数，s是小数位数vargraphic(n)长度可变且最大长度不能大于4000的双字节字符串float32位浮点数date日期，包含年、月、日double64位浮点数time时间，包含时、分、秒char(n)固定长度的字符串，n不能大于254datetime日期和时间SQLite数据库提供了算术、字符串、日期、时间等操作函数，如下表所示名称说明名称说明abs(x)返回绝对值upper(x)小写转大写max(x,y,··.)返回最大值substr(x,y,z)截取子串min(x,y,··.)返回最小值like(A,B)确定给定的字符串与指定的模式是否匹配random(*)返回随机数date()产生日期round(x[,y])四舍五入datetime()产生日期和时间length(x)返回字符串中字符的个数time()产生时间lower(x)大写转小写strftime()把YYYY-MM-DD

HH:MM:SS格式的日期字符串转换成其他形式的字符串12.2.5SQLite3的常用函数Python内置了SQLite数据库，通过内置的SQLite3模块可以直接访问数据库。PythonDB-API是为不同的数据库提供的访问接口规范，常见对象如下表所示：12.2.6SQLite库的对象名称说明sqlite3.version常量，返回SQLite3模块的版本号sqlite3.sqlite_version常量，返回SQLite数据库的版本号sqlite3.Connection数据库连接对象sqlite3.Cursor游标对象sqlite3.Row行对象sqlite3.connect（dbname）函数，链接到数据库，返回Connection对象结构化查询语言SQL12.32插入数据创建和删除表13修改数据5查询数据删除数据46SQLite命令行执行SQL语句结构化查询语言（StructuredQueryLanguage，SQL）是关系型数据库操作语言，可以实现数据定义、数据操纵和数据控制等功能。关于SQL命令的执行，需要注意以下几个问题。（1）SQL命令需要在数据库管理系统中运行。（2）在SQLite窗口运行SQL命令，需要在SQL语句后加英文的分号后回车执行。（3）SQL命令不区分大小写。12.3结构化查询语言SQL12.3.1创建和删除表创建表1在SQL中，使用createtable语句创建表，其语法格式如下：createtable<表名>(列名1数据类型

列属性，列名2数据类型

列属性，列名n数据类型

列属性)1举个例子Forexamplecreatetablestudent(stu_numbervarchar(9)primarykey,stu_namevarchar(255)NOTNULL,stu_ageinteger,stu_sexvarchar(2)default('男'),stu_addressvarchar(255),stu_classvarchar(100))primarykey：主键。NOTNULL：不为空，默认设置。default：指定默认值。12.3.1创建和删除表创建表12.3.1创建和删除表删除表2droptable

语句用于删除表（表的结构、属性以及索引也会被删除），其语法格式如下：droptable<表名>如删除student表，示例如下：droptableemployee可以使用insert语句向表中插入数据，其语法格式如下：12.3.2插入数据insertinto<表名>[<字段名表]values(<表达式表>)举个例子Forexampleinsert

into

student(stu_number,stu_name,stu_age,stu_sex,stu_address,stu_class)values('231011101','张三',19,'男','辽宁省沈阳市和平区','23计科1班')insert

into

student(stu_number,stu_name,stu_age,stu_sex,stu_address,stu_class)values('231011102','李四',19,'女','辽宁省大连市中山区','23计科1班')12.3.2插入数据学号姓名年龄性别家庭住址所在城市231011101张三19男辽宁省沈阳市和平区23计科1班231011102李四19女辽宁省大连市中山区23计科1班将下面的数据插入到student表中。12.3.3修改数据可以使用update语句修改表中的数据，update语句的语法格式如下：update<表名>set<字段名1>=<表达式1>[,<字段名2>=<表达式2>···][where<条件表达式>］将student表中，张三的年龄改为20，示例如下：updatestudentsetstu_age=20wherestu_name="张三＂12.3.4删除数据使用delete语句删除表中的数据，其语法格式如下：deletefrom<表名>[where<条件表达式>]删除student表中性别为“女”的记录，示例如下：delete

from

studentwhere

stu_sex='女'SQL语句创建查询使用的是select命令，基本形式是由

select···fom···where···

子句组成，具体的命令格式如下。12.3.5查询数据select<字段名表>|*from<表名>[join<表名>on<连接条件>][where<条件表达式>][groupby<分组字段名>[having<条件表达式>]][orderby<排序选项>[asc|desc]]说明：（1）select子句说明要查询的字段名，如果是*，表示查询所有字段。（2）from子句说明查询的表，如果查询多个表，需要通过join选项指明连接条件。（3）where子句说明查询的条件。多个条件之间用逻辑运算符and、or或not连接。（4）groupby子句将查询结果按字段名分组。having子句与groupby一起使用，它用来限定分组必须满足的条件。（5）orderby子句对查询结果进行排序。举个例子Forexample12.3.5查询数据查找student表中姓名为“李四”的学生学号。查找student表中年龄在18～20岁的学生姓名。查找student表中年龄大于18岁的女生姓名。查找student表中姓王的所有学生信息。查找student表中所有男生信息并按年龄升序排列。学号姓名年龄性别家庭住址所在城市231011101张三19男辽宁省沈阳市和平区23计科1班231011102李四19女辽宁省大连市中山区23计科1班231011103王五21女辽宁省沈阳市沈河区23计科1班231011104赵六17男辽宁省沈阳市沈北区23计科1班231011105孙七20女辽宁省沈阳市皇姑区23计科1班selectstu_numberfromstudentwherestu_name='李四'selectstu_namefromstudentwherestr_agebetween18and20selectstu_namefromstudentwherestu_age>18andstu_sex='女'select*fromstudentwherestu_namelike"王%%"select*fromstudentwherestu_sex='男'orderbystu_age在命令行中输入sqlite3.exe，打开SQLite的命令行窗口.openstudent_ms.db打开数据库注意在SQL语句后面加入英文分号后回车才能正确执行SQL语句，如下图所示：12.3.6SQLite命令行执行SQL语句SQLite3数据库编程12.42数据库常用操作1数据库访问流程3数据库可视化操作12.4.1数据库访问流程导入SQLite3数据库模块建立数据库连接的Connection对象创建游标对象Cursor使用Cursor对象的execute方法执行SQL命令返回结果集获取游标的查询结果集12.4.1数据库访问流程1导入SQLite3数据库模块importsqlite3建立数据库连接的Connection对象创建游标对象23con=sqlite3.connect(connectstring)＃连接数据库，返回sqlite3.connection对象connectstring是数据库的文件名，案例如下：importsqlite3con=sqlite3.connect("D:\\student.db")游标(Cursor)是行的集合，使用游标对象能够灵活地操纵检索出的数据。游标实际上是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。cur=con.cursor()12.4.1数据库访问流程使用Cursor对象的execute方法执行SQL命令返回结果集4Cursor对象对数据库的操作分为以下4种类型：（1）cur.execute(sql)：执行SQL语句。（2）cur.execute(sql,parameters)：执行带参数的SQL语句。（3）cur.executemany(sql,seg_of_parameters)：根据参数执行多次SQL语句。（4）cur.executescript(sql_script)：执行SQL脚本。案例：向表中插入记录的代码如下。在SQL语句中可以使用占位符“?”表示参数，传递的参数使用元组，示例如下：cur.execute("insertintostudentvalues('231011101','张三',19,'男','辽宁省沈阳市和平区','23计科1班')")cur.execute("insertintostudentvalues(?,?,?,?,?,?)",('231011101','张三',19,'男','辽宁省沈阳市和平区','23计科1班')12.4.1数据库访问流程获取游标的查询结果集5调用cur.fetchall()、cur.fetchone()、cur.fetchmany()等方法返回查询结果。（1）cur.fetchall()：返回结果集的剩余行（Row对象列表），无数据时，返回空List。（2）cur.fetchone()：返回结果集的下一行（Row对象），无数据时，返回None。（3）cur.fetchmany()：返回结果集的多行（Row对象列表），无数据时，返回空List。将以下数据插入到student表中，并查询输出到控制台：举个例子Forexample12.4.1数据库访问流程学号姓名年龄性别家庭住址所在城市231011110张晓明19男辽宁省沈阳市和平区23计科1班231011111李思佳19女辽宁省大连市中山区23计科1班例：Case12_1.py在数据库中插入、更新、删除记录的步骤如下：（1）建立数据库连接。（2）创建游标对象cur，使用cur.execute（sql）方法执行SQL的insert、updatet和delete等语句，完成数据库记录的插入、更新和删除操作，并根据返回值判断操作结果。（3）提交操作。（4）关闭数据库。12.4.2数据库常用操作在案例Case12_1.py基础上操作数据插入下列数据：将王刚的年龄改为21岁删除姓名为李闯的学生记录查询所有记录，在控制台输出。举个例子Forexample学号姓名年龄性别家庭住址所在城市231011112王刚20男上海市黄浦区23计科2班231011113李闯20男陕西省西安市新城区23计科2班12.4.2数据库常用操作例：Case12_2.pyDatabaseNavigator插件是适用于PyCharm的一款数据库管理工具，主要功能如下：数据库连接管理。数据库对象导航。可以浏览和查看数据库中的各种对象，如表、视图、存储过程等。数据库查询和编辑数据库表数据管理数据库版本管理。可进行数据库变更的跟踪和管理，方便团队协作和版本回滚。12.4.3数据库可视