《數(shù)據(jù)采集與處理：基于python》 課件 第9章-關(guān)系數(shù)據(jù)庫連接與訪問

第九章

關(guān)系型數(shù)據(jù)庫連接與訪問《數(shù)據(jù)采集與處理：基于Python》2024/5/1112024/5/112教學(xué)目標(biāo)本章學(xué)習(xí)目標(biāo)1.了解關(guān)系型數(shù)據(jù)庫的基本概念、原理、知識和常用SQL操作,理解關(guān)系型數(shù)據(jù)庫的ACID特性和關(guān)系模型結(jié)構(gòu);2.了解嵌入式關(guān)系型數(shù)據(jù)庫SQLite的特點(diǎn)和訪問方式,掌握基于Python訪問SQLite數(shù)據(jù)庫的操作方法;3.了解網(wǎng)絡(luò)關(guān)系型數(shù)據(jù)庫MySQL的特點(diǎn)和訪問方式,掌握基于Python模塊pymysql、sqlalchemy及pandas訪問MySQL數(shù)據(jù)庫的操作方法。本章提綱1.關(guān)系型數(shù)據(jù)庫概述2.SQLite數(shù)據(jù)庫連接與訪問3.MySQL數(shù)據(jù)庫連接與訪問關(guān)系型數(shù)據(jù)庫概述2024/5/113關(guān)系型數(shù)據(jù)庫概述到目前為止,我們主要使用平面表來存儲和處理數(shù)據(jù)。然而,現(xiàn)實(shí)世界中的大多數(shù)結(jié)構(gòu)化數(shù)據(jù)都存儲在數(shù)據(jù)庫中,特別是關(guān)系型數(shù)據(jù)庫。其他類型的數(shù)據(jù)庫有特定的優(yōu)點(diǎn),比如性能優(yōu)異(如NoSQL),適用于圖形(圖形數(shù)據(jù)庫,如Neo4j),或者與內(nèi)存中的OO數(shù)據(jù)結(jié)構(gòu)(如面向?qū)ο髷?shù)據(jù)庫)兼容。關(guān)系型數(shù)據(jù)庫及其實(shí)現(xiàn)(即關(guān)系型數(shù)據(jù)庫管理系統(tǒng)(RDBMS))仍然是存儲企業(yè)數(shù)據(jù)的主要方式。關(guān)系型數(shù)據(jù)庫簡介1.什么是關(guān)系型數(shù)據(jù)庫關(guān)系型數(shù)據(jù)庫是建立在關(guān)系模型基礎(chǔ)上的數(shù)據(jù)庫,借助于集合代數(shù)等概念和方法來處理數(shù)據(jù)庫中的數(shù)據(jù),同時也是一組擁有正式描述的表格。該種形式的表格的實(shí)質(zhì)是裝載數(shù)據(jù)項(xiàng)的特殊集合體,這些表格中的數(shù)據(jù)能以許多不同的方式存取或重新召集而不需要重新組織數(shù)據(jù)庫表格。每個表格(有時稱為一個關(guān)系)包含用列表示的一個或多個數(shù)據(jù)種類。每行包含唯一的數(shù)據(jù)實(shí)體,這些數(shù)據(jù)是被列定義的種類。當(dāng)創(chuàng)建一個關(guān)系型數(shù)據(jù)庫時,可以定義數(shù)據(jù)列的可能值的范圍和可能應(yīng)用于某個數(shù)據(jù)值的進(jìn)一步約束。結(jié)構(gòu)化查詢語言(structuredquerylanguage,SQL)是標(biāo)準(zhǔn)用戶和應(yīng)用程序到關(guān)系型數(shù)據(jù)庫的接口,其優(yōu)勢是容易擴(kuò)充,在最初的數(shù)據(jù)庫創(chuàng)建之后,一個新的數(shù)據(jù)種類能被添加而不需要修改所有的現(xiàn)有應(yīng)用軟件。主流的關(guān)系型數(shù)據(jù)庫有Oracle、DB2、SQLServer、Sybase、MySQL等。關(guān)系型數(shù)據(jù)庫簡介關(guān)系型數(shù)據(jù)庫是數(shù)據(jù)庫應(yīng)用的主流,許多數(shù)據(jù)庫管理系統(tǒng)的數(shù)據(jù)模型都是基于關(guān)系數(shù)據(jù)模型開發(fā)的。關(guān)系數(shù)據(jù)模型包括關(guān)系數(shù)據(jù)結(jié)構(gòu)、關(guān)系操作集合和數(shù)據(jù)完整性約束三個要素。關(guān)系數(shù)據(jù)結(jié)構(gòu)是二維表;常用的關(guān)系操作包括查詢、插入、刪除、修改等;而數(shù)據(jù)完整性約束包括實(shí)體完整性、參照完整性及用戶定義的完整性等。關(guān)系型數(shù)據(jù)庫分為兩類:一類是桌面數(shù)據(jù)庫,例如Access、FoxPro和dBase等;另一類是客戶端/服務(wù)器(C/S)數(shù)據(jù)庫,例如SQLServer、Oracle和Sybase等。一般而言,桌面數(shù)據(jù)庫用于小型、單機(jī)的應(yīng)用程序,它不需要網(wǎng)絡(luò)和服務(wù)器,實(shí)現(xiàn)起來比較方便,但它只提供數(shù)據(jù)的存取功能?？蛻舳?服務(wù)器數(shù)據(jù)庫主要適用于大型、多用戶的數(shù)據(jù)庫管理系統(tǒng),應(yīng)用程序包括兩部分:一部分駐留在客戶機(jī)上,用于向用戶顯示信息及實(shí)現(xiàn)與用戶的交互;另一部分駐留在服務(wù)器中,用于實(shí)現(xiàn)對數(shù)據(jù)庫的操作和對數(shù)據(jù)的計算處理。關(guān)系型數(shù)據(jù)庫簡介2.關(guān)系型數(shù)據(jù)庫事務(wù)與本地文本文件或Web爬取不同,一般不能直接訪問公共數(shù)據(jù)庫。數(shù)據(jù)庫功能強(qiáng)大,但使用起來也有一些困難。更有可能的情況是,我們與之交互的API在幕后由關(guān)系型數(shù)據(jù)庫提供動力,從而隱藏了復(fù)雜性。此外,雖然主要考慮的是讀取數(shù)據(jù),但數(shù)據(jù)庫也可以寫入,這就增加了濫用的可能性。因此,對數(shù)據(jù)庫進(jìn)行讀/寫訪問的最有可能的情況是,數(shù)據(jù)庫是由用戶或用戶所在的組織管理的。關(guān)系型數(shù)據(jù)庫的事務(wù)(transaction)遵循ACID特性。事務(wù)和現(xiàn)實(shí)世界中的交易類似,它有如下四個特性。(1)A:atomicity(原子性)。原子性是指事務(wù)里的所有操作要么全部做完,要么都不做,事務(wù)成功的條件是事務(wù)里的所有操作都成功,只要有一個操作失敗,整個事務(wù)就失敗,需要回滾。例如銀行轉(zhuǎn)賬,從A賬戶轉(zhuǎn)100元至B賬戶,分為兩個步驟:1)從A賬戶取100元;2)將100元存至B賬戶。這兩步要么一起完成,要么均不完成,如果只完成第一步,而第二步失敗,則錢會莫名其妙少100元。(2)C:consistency(一致性)。一致性是指數(shù)據(jù)庫要一直處于一致的狀態(tài),事務(wù)的運(yùn)行不會改變數(shù)據(jù)庫原本的一致性約束。例如,現(xiàn)有完整性約束a+b=10,如果一個事務(wù)改變了a,那么必須改變b,使得事務(wù)結(jié)束后依然滿足a+b=10,否則事務(wù)失敗。關(guān)系型數(shù)據(jù)庫簡介(3)I:isolation(隔離性)。隔離性是指并發(fā)的事務(wù)之間不會互相影響,如果一個事務(wù)要訪問的數(shù)據(jù)正在被另一個事務(wù)修改,只要另一個事務(wù)未提交,它所訪問的數(shù)據(jù)就不受未提交事務(wù)的影響。例如,現(xiàn)在有一筆交易是從A賬戶轉(zhuǎn)100元至B賬戶,在這筆交易還未完成的情況下,如果此時B查詢自己的賬戶,則看不到新增加的100元。(4)D:durability(持久性)。持久性是指一旦事務(wù)提交,它所做的修改將會永久地保存在數(shù)據(jù)庫中,即使出現(xiàn)宕機(jī),也不會丟失。關(guān)系型數(shù)據(jù)庫簡介3.關(guān)系模型結(jié)構(gòu)(1)關(guān)系(表文件)。關(guān)系型數(shù)據(jù)庫采用二維表格來存儲數(shù)據(jù),是一種按行與列排列的具有相關(guān)信息的邏輯組,它類似于Excel工作表。一個數(shù)據(jù)庫可以包含任意多張數(shù)據(jù)表。通俗地說,一個關(guān)系對應(yīng)一張表。(2)元組(記錄)。表中的一行即為一個元組,或稱為一條記錄。(3)屬性(字段)。數(shù)據(jù)表中的每一列稱為一個字段。表是由其包含的各種字段定義的,每個字段描述了它所包含的數(shù)據(jù)的意義。數(shù)據(jù)表的設(shè)計實(shí)際上就是對字段的設(shè)計。創(chuàng)建數(shù)據(jù)表時,為每個字段分配一個數(shù)據(jù)類型,定義它們的數(shù)據(jù)長度和其他屬性。字段可以包含各種字符、數(shù)字,甚至圖形。(4)屬性值。行和列的交叉位置表示某個屬性值,如“數(shù)據(jù)采集與處理:基于Python”就是課程名稱的屬性值。(5)主鍵。主鍵(primarykey)也稱主碼或主關(guān)鍵字,是表中唯一確定元組的數(shù)據(jù)。主鍵用來確保表中記錄的唯一性,可以是一個字段或多個字段,常用作一個表的索引字段。每條記錄的主鍵都是不同的,因而可以唯一地標(biāo)識一條記錄。(6)域。屬性的取值范圍,如課程學(xué)分屬性的取值范圍是0~10之間的整數(shù)。(7)關(guān)系模式。關(guān)系的描述稱為關(guān)系模式。關(guān)系模式一般表示為:關(guān)系名(屬性1,屬性2,…,屬性n)。例如:課程(課程號,課程名稱,學(xué)分,任課老師)。關(guān)系模式這種簡單的數(shù)據(jù)結(jié)構(gòu)能夠表達(dá)豐富的語義,描述現(xiàn)實(shí)世界的實(shí)體以及實(shí)體間的各種關(guān)系。關(guān)系型數(shù)據(jù)庫操作1.SQL語言SQL是執(zhí)行增刪改查事務(wù)的特定于域的語言。我們將主要研究使用SQL聚合數(shù)據(jù)的查詢。下面是一個非常簡單的查詢示例:這個語句選擇并返回products表中的所有行。我們可以使用“WHERE”子句限制要檢索的行數(shù),下面的語句將檢索價格高于100的所有行:SELECT*FROMproductsSELECT*FROMproductsWHEREPrice>100關(guān)系型數(shù)據(jù)庫操作2.關(guān)系型數(shù)據(jù)庫操作流程以常用的MySQL數(shù)據(jù)庫為例,訪問MySQL數(shù)據(jù)庫的基本流程如圖9-2所示。訪問關(guān)系型數(shù)據(jù)庫首先要創(chuàng)建對應(yīng)數(shù)據(jù)庫的連接(connect);然后創(chuàng)建游標(biāo)(cursor),通過游標(biāo)對象執(zhí)行數(shù)據(jù)庫的具體操作,如數(shù)據(jù)的增、刪、改、查等;操作完成后,需要關(guān)閉游標(biāo);最后關(guān)閉數(shù)據(jù)庫連接以釋放資源。SQLite數(shù)據(jù)庫連接與訪問2024/5/1112SQLite數(shù)據(jù)庫簡介大多數(shù)數(shù)據(jù)庫管理系統(tǒng)都是用客戶端/服務(wù)器系統(tǒng)實(shí)現(xiàn)的,即數(shù)據(jù)庫托管在一個專用服務(wù)器上,多個用戶可以在該服務(wù)器上讀寫數(shù)據(jù),例如PostgreSQL、MySQL和Oracle。這些數(shù)據(jù)庫管理系統(tǒng)的處理速度快,可以同時處理多個用戶的請求和操作,不會引起沖突。但是它們需要安裝服務(wù)器系統(tǒng),所以我們使用SQLite,它為單個用戶工作并將整個數(shù)據(jù)庫存儲在單個文件中。SQLite廣泛用于單用戶用例。雖然這些不同的數(shù)據(jù)庫具有不同的特性和性能特征,但它們都支持SQL,因此在這里學(xué)到的技能可以廣泛使用。SQLite是一種嵌入式關(guān)系型數(shù)據(jù)庫軟件,即數(shù)據(jù)庫是一個本地文件,自身并沒有單獨(dú)的伺服軟件,但提供了相應(yīng)的API,以便訪問該數(shù)據(jù)庫文件。另外,也可以下載安裝SQLiteExpertPersonal軟件查看數(shù)據(jù)庫內(nèi)容,或者使用通用的關(guān)系型數(shù)據(jù)庫管理客戶端軟件進(jìn)行管理,如Navicat軟件等。Python訪問SQLite操作本書使用SQLite示例數(shù)據(jù)庫資源。SQLite數(shù)據(jù)庫模式和關(guān)系表示例如圖所示。Python訪問SQLite操作數(shù)據(jù)庫示例說明如下:●employees表存儲員工數(shù)據(jù),如員工id、姓、名等。它還包括一個名為“ReportsTo”的字段,用于指定員工向誰報告?！馽ustomers表存儲客戶數(shù)據(jù)。●invoices和invoice_items表存儲發(fā)票數(shù)據(jù)。invoices表存儲發(fā)票頭數(shù)據(jù),而invoice_items表存儲發(fā)票明細(xì)項(xiàng)數(shù)據(jù)?！馻rtists表存儲藝術(shù)家數(shù)據(jù),只包含藝術(shù)家id和姓名。●albums表存儲專輯曲目列表數(shù)據(jù)。每張專輯屬于一位藝術(shù)家,而一位藝術(shù)家可能有多張專輯。●media_types表存儲媒體類型數(shù)據(jù),如MPEG音頻文件、AAC音頻文件等?！駁enres表存儲音樂類型數(shù)據(jù),如搖滾、爵士和重金屬等?！駎racks表存儲歌曲數(shù)據(jù),每個曲目(對應(yīng)一首音樂)屬于一張專輯(唱片)?！駊laylists和playlist_track表:playlists表存儲有關(guān)播放列表的數(shù)據(jù),每個播放列表包含了曲目,每個音軌可能屬于多個播放列表。播放列表playlists和曲目表tracks之間的關(guān)系是多對多的;playlist_track表用于反映這種關(guān)系。圖9-3突出顯示了每個表中的主鍵以及它們之間的關(guān)系,使用外鍵彼此連接。注意,“員工”有一個自我引用,以捕獲報告給誰(ReportsTo)的關(guān)系。Python訪問SQLite操作Python中的sqlite3模塊可用于操作SQLite數(shù)據(jù)庫,示例程序如下:運(yùn)行結(jié)果如下:這里使用游標(biāo)來訪問數(shù)據(jù)。要在執(zhí)行SELECT語句后檢索數(shù)據(jù),可以將游標(biāo)視為迭代器,調(diào)用游標(biāo)的fetchone函數(shù)來檢索單個匹配行,或者調(diào)用fetchall函數(shù)來獲取匹配行的列表。下面的示例程序講解了如何使用它作為迭代器。importpandasaspdimportsqlite3assq#導(dǎo)入sqlite3模塊conn=sq.connect("./chinook.db")#連接到數(shù)據(jù)庫c=conn.cursor() #獲取數(shù)據(jù)庫的操作游標(biāo)c.execute("SELECT*FROMalbums") #執(zhí)行SQL語句#獲取并打印結(jié)果中的一行print(c.fetchone()) #游標(biāo)cursor是一個迭代器,可以循環(huán)讀取(1,ForThoseAboutToRockWeSaluteYou,1)forrowinc:if(row[0]<10):#限制輸出行數(shù)print(row)c.execute("SELECT*FROMalbums")c.fetchall() #一次獲取所有記錄pandas與關(guān)系型數(shù)據(jù)庫訪問對于關(guān)系型數(shù)據(jù)庫表的讀寫操作，pandas提供了更簡易的方法,可以分別使用read_sql和to_sql函數(shù)對關(guān)系型數(shù)據(jù)庫表進(jìn)行讀寫操作。示例程序如下:#類似于read_csv函數(shù)pd.read_sql("""SELECT*FROMalbums""",conn).head()#單個表訪問#多表內(nèi)連接查詢訪問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數(shù)據(jù)庫連接與訪問2024/5/1118MySQL數(shù)據(jù)庫簡介1.什么是MySQLMySQL是一個小型的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),由于該軟件體積小、運(yùn)行速度快、操作方便,目前廣泛應(yīng)用于中小企業(yè)網(wǎng)站的后臺。在本地計算機(jī)上安裝好MySQL后,在Windows命令行中輸入“netstartmysql”即可啟動該程序。若要進(jìn)入MySQL可執(zhí)行程序目錄,輸入命令“mysql-uroot”即可進(jìn)入MySQL中的命令行模式。MySQL數(shù)據(jù)庫簡介2.MySQL基本操作MySQL數(shù)據(jù)庫的基本操作主要分為操作MySQL數(shù)據(jù)庫和操作MySQL數(shù)據(jù)表,對應(yīng)定義數(shù)據(jù)庫或數(shù)據(jù)表的操作語句稱為數(shù)據(jù)定義語言(datadefinitionlanguage,DDL),而對應(yīng)增、刪、改、查的操作語句則稱為數(shù)據(jù)操縱語言(datamanipulationlanguage,DML)。MySQL數(shù)據(jù)庫的基本操作主要有以下幾種:(1)創(chuàng)建數(shù)據(jù)庫:CREATEDATABASE數(shù)據(jù)庫名。(2)查看數(shù)據(jù)庫:SHOWDATABASES。(3)選擇指定數(shù)據(jù)庫:USE數(shù)據(jù)庫名。(4)刪除數(shù)據(jù)庫:DROPDATABASE數(shù)據(jù)庫名。MySQL數(shù)據(jù)表的基本操作主要有以下幾種:(1)創(chuàng)建數(shù)據(jù)表:CREATETABLE數(shù)據(jù)表名。(2)查看數(shù)據(jù)表:SHOWTABLES。(3)查看數(shù)據(jù)表結(jié)構(gòu):DESCRIBE數(shù)據(jù)表名。(4)向數(shù)據(jù)表中添加記錄:INSERTINTO數(shù)據(jù)表名VALUES。(5)修改數(shù)據(jù)表中的記錄:UPDATE數(shù)據(jù)表名。(6)查詢數(shù)據(jù)表記錄:SELECT……。Python訪問MySQL操作在Python3中可以使用pymysql庫來實(shí)現(xiàn)這一功能。pymysql庫是一個純Python庫，可以直接安裝使用，安裝時可在Windows命令行中輸入以下命令：pipinstallpymysqlPython訪問MySQL操作在Python中訪問MySql數(shù)據(jù)庫和用C++訪問數(shù)據(jù)庫方法基本相同，主要有以下步驟：（1）通過pymysql庫的方法，與MySql數(shù)據(jù)庫建立連接。（2）編寫SQL語句。（3）通過連接的返回的數(shù)據(jù)庫對象，調(diào)用相應(yīng)方法執(zhí)行SQL語句。（4）讀取數(shù)據(jù)庫返回的數(shù)據(jù)（即緩存區(qū)中的數(shù)據(jù)）。（5）對相應(yīng)的返回數(shù)據(jù)進(jìn)行操作。（6）關(guān)閉數(shù)據(jù)庫對象，關(guān)閉數(shù)據(jù)庫。MySQL數(shù)據(jù)庫簡介使用pymysql模塊訪問數(shù)據(jù)庫的一般流程如下:(1)導(dǎo)入模塊。importpymysql(2)創(chuàng)建數(shù)據(jù)庫連接對象。con=pymysql.connect(host,port,user,password,database,charset)(3)使用數(shù)據(jù)庫連接對象調(diào)用cursor函數(shù)創(chuàng)建游標(biāo)。cur=con.cursor()注意,創(chuàng)建游標(biāo)時會默認(rèn)開啟一個隱式的事務(wù),在執(zhí)行增、刪、改的操作后需要提交(commit)事務(wù),如果不提交,默認(rèn)為事務(wù)回滾(rollback),即操作并沒有生效;如果操作失敗,可調(diào)用rollback函數(shù)回滾撤銷事務(wù)。(4)編寫SQL語句字符串,并執(zhí)行SQL語句。sql=增刪改查的SQL語句cur.execute(sql,參數(shù))execute函數(shù)可以使用元組(tuple)、列表(list)、字典(dict)這三種方式傳參,一般使用元組或列表的方式。(5)當(dāng)需要顯示查詢后的結(jié)果時,可以通過fetchone、fetchmany、fetchall函數(shù)獲取查詢后的結(jié)果元組。MySQL數(shù)據(jù)庫簡介(6)提交事務(wù)并關(guān)閉游標(biāo)。對數(shù)據(jù)進(jìn)行增、刪、改后需要提交事務(wù),否則所有操作無效。mit()cur.close()(7)關(guān)閉數(shù)據(jù)庫連接。con.close()MySQL數(shù)據(jù)庫簡介2.MySQL訪問相關(guān)模塊sqlalchemysqlalchemy是Python的一款開源軟件,提供了SQL工具包及對象關(guān)系映射(objectrelationalmapping,ORM)工具,使用MIT許可證發(fā)行。sqlalchemy首次發(fā)行于2006年2月,并迅速成為Python社區(qū)最廣泛使用的ORM工具之一,不亞于django內(nèi)嵌的ORM框架。其安裝方式如下:pipinstallsqlalchemy或condainstallsqlalchemysqlalchemy操作數(shù)據(jù)庫的步驟與其他模塊基本類似,只是涉及具體的大型網(wǎng)絡(luò)關(guān)系型數(shù)據(jù)庫時,需要下載對應(yīng)的數(shù)據(jù)庫引擎,即對應(yīng)數(shù)據(jù)庫模塊。如連接MySQL時需要同時下載安裝pymysql庫。sqlalchemy的使用方法和操作如圖9-4所示。MySQL數(shù)據(jù)庫簡介連接MySQL數(shù)據(jù)庫的字符串URL形式為:db_url="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8"通過create_eng