《数据采集与处理：基于python》 课件 第9章-关系数据库连接与访问

第九章

关系型数据库连接与访问《数据采集与处理：基于Python》2024/5/1112024/5/112教学目标本章学习目标1.了解关系型数据库的基本概念、原理、知识和常用SQL操作,理解关系型数据库的ACID特性和关系模型结构;2.了解嵌入式关系型数据库SQLite的特点和访问方式,掌握基于Python访问SQLite数据库的操作方法;3.了解网络关系型数据库MySQL的特点和访问方式,掌握基于Python模块pymysql、sqlalchemy及pandas访问MySQL数据库的操作方法。本章提纲1.关系型数据库概述2.SQLite数据库连接与访问3.MySQL数据库连接与访问关系型数据库概述2024/5/113关系型数据库概述到目前为止,我们主要使用平面表来存储和处理数据。然而,现实世界中的大多数结构化数据都存储在数据库中,特别是关系型数据库。其他类型的数据库有特定的优点,比如性能优异(如NoSQL),适用于图形(图形数据库,如Neo4j),或者与内存中的OO数据结构(如面向对象数据库)兼容。关系型数据库及其实现(即关系型数据库管理系统(RDBMS))仍然是存储企业数据的主要方式。关系型数据库简介1.什么是关系型数据库关系型数据库是建立在关系模型基础上的数据库,借助于集合代数等概念和方法来处理数据库中的数据,同时也是一组拥有正式描述的表格。该种形式的表格的实质是装载数据项的特殊集合体,这些表格中的数据能以许多不同的方式存取或重新召集而不需要重新组织数据库表格。每个表格(有时称为一个关系)包含用列表示的一个或多个数据种类。每行包含唯一的数据实体,这些数据是被列定义的种类。当创建一个关系型数据库时,可以定义数据列的可能值的范围和可能应用于某个数据值的进一步约束。结构化查询语言(structuredquerylanguage,SQL)是标准用户和应用程序到关系型数据库的接口,其优势是容易扩充,在最初的数据库创建之后,一个新的数据种类能被添加而不需要修改所有的现有应用软件。主流的关系型数据库有Oracle、DB2、SQLServer、Sybase、MySQL等。关系型数据库简介关系型数据库是数据库应用的主流,许多数据库管理系统的数据模型都是基于关系数据模型开发的。关系数据模型包括关系数据结构、关系操作集合和数据完整性约束三个要素。关系数据结构是二维表;常用的关系操作包括查询、插入、删除、修改等;而数据完整性约束包括实体完整性、参照完整性及用户定义的完整性等。关系型数据库分为两类:一类是桌面数据库,例如Access、FoxPro和dBase等;另一类是客户端/服务器(C/S)数据库,例如SQLServer、Oracle和Sybase等。一般而言,桌面数据库用于小型、单机的应用程序,它不需要网络和服务器,实现起来比较方便,但它只提供数据的存取功能。客户端/服务器数据库主要适用于大型、多用户的数据库管理系统,应用程序包括两部分:一部分驻留在客户机上,用于向用户显示信息及实现与用户的交互;另一部分驻留在服务器中,用于实现对数据库的操作和对数据的计算处理。关系型数据库简介2.关系型数据库事务与本地文本文件或Web爬取不同,一般不能直接访问公共数据库。数据库功能强大,但使用起来也有一些困难。更有可能的情况是,我们与之交互的API在幕后由关系型数据库提供动力,从而隐藏了复杂性。此外,虽然主要考虑的是读取数据,但数据库也可以写入,这就增加了滥用的可能性。因此,对数据库进行读/写访问的最有可能的情况是,数据库是由用户或用户所在的组织管理的。关系型数据库的事务(transaction)遵循ACID特性。事务和现实世界中的交易类似,它有如下四个特性。(1)A:atomicity(原子性)。原子性是指事务里的所有操作要么全部做完,要么都不做,事务成功的条件是事务里的所有操作都成功,只要有一个操作失败,整个事务就失败,需要回滚。例如银行转账,从A账户转100元至B账户,分为两个步骤:1)从A账户取100元;2)将100元存至B账户。这两步要么一起完成,要么均不完成,如果只完成第一步,而第二步失败,则钱会莫名其妙少100元。(2)C:consistency(一致性)。一致性是指数据库要一直处于一致的状态,事务的运行不会改变数据库原本的一致性约束。例如,现有完整性约束a+b=10,如果一个事务改变了a,那么必须改变b,使得事务结束后依然满足a+b=10,否则事务失败。关系型数据库简介(3)I:isolation(隔离性)。隔离性是指并发的事务之间不会互相影响,如果一个事务要访问的数据正在被另一个事务修改,只要另一个事务未提交,它所访问的数据就不受未提交事务的影响。例如,现在有一笔交易是从A账户转100元至B账户,在这笔交易还未完成的情况下,如果此时B查询自己的账户,则看不到新增加的100元。(4)D:durability(持久性)。持久性是指一旦事务提交,它所做的修改将会永久地保存在数据库中,即使出现宕机,也不会丢失。关系型数据库简介3.关系模型结构(1)关系(表文件)。关系型数据库采用二维表格来存储数据,是一种按行与列排列的具有相关信息的逻辑组,它类似于Excel工作表。一个数据库可以包含任意多张数据表。通俗地说,一个关系对应一张表。(2)元组(记录)。表中的一行即为一个元组,或称为一条记录。(3)属性(字段)。数据表中的每一列称为一个字段。表是由其包含的各种字段定义的,每个字段描述了它所包含的数据的意义。数据表的设计实际上就是对字段的设计。创建数据表时,为每个字段分配一个数据类型,定义它们的数据长度和其他属性。字段可以包含各种字符、数字,甚至图形。(4)属性值。行和列的交叉位置表示某个属性值,如“数据采集与处理:基于Python”就是课程名称的属性值。(5)主键。主键(primarykey)也称主码或主关键字,是表中唯一确定元组的数据。主键用来确保表中记录的唯一性,可以是一个字段或多个字段,常用作一个表的索引字段。每条记录的主键都是不同的,因而可以唯一地标识一条记录。(6)域。属性的取值范围,如课程学分属性的取值范围是0~10之间的整数。(7)关系模式。关系的描述称为关系模式。关系模式一般表示为:关系名(属性1,属性2,…,属性n)。例如:课程(课程号,课程名称,学分,任课老师)。关系模式这种简单的数据结构能够表达丰富的语义,描述现实世界的实体以及实体间的各种关系。关系型数据库操作1.SQL语言SQL是执行增删改查事务的特定于域的语言。我们将主要研究使用SQL聚合数据的查询。下面是一个非常简单的查询示例:这个语句选择并返回products表中的所有行。我们可以使用“WHERE”子句限制要检索的行数,下面的语句将检索价格高于100的所有行:SELECT*FROMproductsSELECT*FROMproductsWHEREPrice>100关系型数据库操作2.关系型数据库操作流程以常用的MySQL数据库为例,访问MySQL数据库的基本流程如图9-2所示。访问关系型数据库首先要创建对应数据库的连接(connect);然后创建游标(cursor),通过游标对象执行数据库的具体操作,如数据的增、删、改、查等;操作完成后,需要关闭游标;最后关闭数据库连接以释放资源。SQLite数据库连接与访问2024/5/1112SQLite数据库简介大多数数据库管理系统都是用客户端/服务器系统实现的,即数据库托管在一个专用服务器上,多个用户可以在该服务器上读写数据,例如PostgreSQL、MySQL和Oracle。这些数据库管理系统的处理速度快,可以同时处理多个用户的请求和操作,不会引起冲突。但是它们需要安装服务器系统,所以我们使用SQLite,它为单个用户工作并将整个数据库存储在单个文件中。SQLite广泛用于单用户用例。虽然这些不同的数据库具有不同的特性和性能特征,但它们都支持SQL,因此在这里学到的技能可以广泛使用。SQLite是一种嵌入式关系型数据库软件,即数据库是一个本地文件,自身并没有单独的伺服软件,但提供了相应的API,以便访问该数据库文件。另外,也可以下载安装SQLiteExpertPersonal软件查看数据库内容,或者使用通用的关系型数据库管理客户端软件进行管理,如Navicat软件等。Python访问SQLite操作本书使用SQLite示例数据库资源。SQLite数据库模式和关系表示例如图所示。Python访问SQLite操作数据库示例说明如下:●employees表存储员工数据,如员工id、姓、名等。它还包括一个名为“ReportsTo”的字段,用于指定员工向谁报告。●customers表存储客户数据。●invoices和invoice_items表存储发票数据。invoices表存储发票头数据,而invoice_items表存储发票明细项数据。●artists表存储艺术家数据,只包含艺术家id和姓名。●albums表存储专辑曲目列表数据。每张专辑属于一位艺术家,而一位艺术家可能有多张专辑。●media_types表存储媒体类型数据,如MPEG音频文件、AAC音频文件等。●genres表存储音乐类型数据,如摇滚、爵士和重金属等。●tracks表存储歌曲数据,每个曲目(对应一首音乐)属于一张专辑(唱片)。●playlists和playlist_track表:playlists表存储有关播放列表的数据,每个播放列表包含了曲目,每个音轨可能属于多个播放列表。播放列表playlists和曲目表tracks之间的关系是多对多的;playlist_track表用于反映这种关系。图9-3突出显示了每个表中的主键以及它们之间的关系,使用外键彼此连接。注意,“员工”有一个自我引用,以捕获报告给谁(ReportsTo)的关系。Python访问SQLite操作Python中的sqlite3模块可用于操作SQLite数据库,示例程序如下:运行结果如下:这里使用游标来访问数据。要在执行SELECT语句后检索数据,可以将游标视为迭代器,调用游标的fetchone函数来检索单个匹配行,或者调用fetchall函数来获取匹配行的列表。下面的示例程序讲解了如何使用它作为迭代器。importpandasaspdimportsqlite3assq#导入sqlite3模块conn=sq.connect("./chinook.db")#连接到数据库c=conn.cursor() #获取数据库的操作游标c.execute("SELECT*FROMalbums") #执行SQL语句#获取并打印结果中的一行print(c.fetchone()) #游标cursor是一个迭代器,可以循环读取(1,ForThoseAboutToRockWeSaluteYou,1)forrowinc:if(row[0]<10):#限制输出行数print(row)c.execute("SELECT*FROMalbums")c.fetchall() #一次获取所有记录pandas与关系型数据库访问对于关系型数据库表的读写操作，pandas提供了更简易的方法,可以分别使用read_sql和to_sql函数对关系型数据库表进行读写操作。示例程序如下:#类似于read_csv函数pd.read_sql("""SELECT*FROMalbums""",conn).head()#单个表访问#多表内连接查询访问df=pd.read_sql("""SELECTtrackid,asTrack,albums.titleasAlbumFROMtracksINNERJOINalbumsONalbums.albumid=tracks.albumid""",conn).head(30)df.to_sql(name="al_track",con=conn,if_exists="replace")MySQL数据库连接与访问2024/5/1118MySQL数据库简介1.什么是MySQLMySQL是一个小型的关系型数据库管理系统,由于该软件体积小、运行速度快、操作方便,目前广泛应用于中小企业网站的后台。在本地计算机上安装好MySQL后,在Windows命令行中输入“netstartmysql”即可启动该程序。若要进入MySQL可执行程序目录,输入命令“mysql-uroot”即可进入MySQL中的命令行模式。MySQL数据库简介2.MySQL基本操作MySQL数据库的基本操作主要分为操作MySQL数据库和操作MySQL数据表,对应定义数据库或数据表的操作语句称为数据定义语言(datadefinitionlanguage,DDL),而对应增、删、改、查的操作语句则称为数据操纵语言(datamanipulationlanguage,DML)。MySQL数据库的基本操作主要有以下几种:(1)创建数据库:CREATEDATABASE数据库名。(2)查看数据库:SHOWDATABASES。(3)选择指定数据库:USE数据库名。(4)删除数据库:DROPDATABASE数据库名。MySQL数据表的基本操作主要有以下几种:(1)创建数据表:CREATETABLE数据表名。(2)查看数据表:SHOWTABLES。(3)查看数据表结构:DESCRIBE数据表名。(4)向数据表中添加记录:INSERTINTO数据表名VALUES。(5)修改数据表中的记录:UPDATE数据表名。(6)查询数据表记录:SELECT……。Python访问MySQL操作在Python3中可以使用pymysql库来实现这一功能。pymysql库是一个纯Python库，可以直接安装使用，安装时可在Windows命令行中输入以下命令：pipinstallpymysqlPython访问MySQL操作在Python中访问MySql数据库和用C++访问数据库方法基本相同，主要有以下步骤：（1）通过pymysql库的方法，与MySql数据库建立连接。（2）编写SQL语句。（3）通过连接的返回的数据库对象，调用相应方法执行SQL语句。（4）读取数据库返回的数据（即缓存区中的数据）。（5）对相应的返回数据进行操作。（6）关闭数据库对象，关闭数据库。MySQL数据库简介使用pymysql模块访问数据库的一般流程如下:(1)导入模块。importpymysql(2)创建数据库连接对象。con=pymysql.connect(host,port,user,password,database,charset)(3)使用数据库连接对象调用cursor函数创建游标。cur=con.cursor()注意,创建游标时会默认开启一个隐式的事务,在执行增、删、改的操作后需要提交(commit)事务,如果不提交,默认为事务回滚(rollback),即操作并没有生效;如果操作失败,可调用rollback函数回滚撤销事务。(4)编写SQL语句字符串,并执行SQL语句。sql=增删改查的SQL语句cur.execute(sql,参数)execute函数可以使用元组(tuple)、列表(list)、字典(dict)这三种方式传参,一般使用元组或列表的方式。(5)当需要显示查询后的结果时,可以通过fetchone、fetchmany、fetchall函数获取查询后的结果元组。MySQL数据库简介(6)提交事务并关闭游标。对数据进行增、删、改后需要提交事务,否则所有操作无效。mit()cur.close()(7)关闭数据库连接。con.close()MySQL数据库简介2.MySQL访问相关模块sqlalchemysqlalchemy是Python的一款开源软件,提供了SQL工具包及对象关系映射(objectrelationalmapping,ORM)工具,使用MIT许可证发行。sqlalchemy首次发行于2006年2月,并迅速成为Python社区最广泛使用的ORM工具之一,不亚于django内嵌的ORM框架。其安装方式如下:pipinstallsqlalchemy或condainstallsqlalchemysqlalchemy操作数据库的步骤与其他模块基本类似,只是涉及具体的大型网络关系型数据库时,需要下载对应的数据库引擎,即对应数据库模块。如连接MySQL时需要同时下载安装pymysql库。sqlalchemy的使用方法和操作如图9-4所示。MySQL数据库简介连接MySQL数据库的字符串URL形式为:db_url="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8"通过create_eng