旗下產(chǎn)業(yè): A產(chǎn)業(yè)/?A實(shí)習(xí)/?A計(jì)劃
全國(guó)統(tǒng)一咨詢熱線:010-5367 2995
首頁(yè) > 熱門文章 > 大數(shù)據(jù)分析 > 如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)

時(shí)間:2020-10-09來源:m.5wd995.cn點(diǎn)擊量:作者:Sissi
時(shí)間:2020-10-09點(diǎn)擊量:作者:Sissi




  SQLite是一個(gè)數(shù)據(jù)庫(kù)引擎,可以簡(jiǎn)化關(guān)系數(shù)據(jù)的存儲(chǔ)和使用。與csv格式非常相似,SQLite將數(shù)據(jù)存儲(chǔ)在單個(gè)文件中,可以輕松地與其他人共享。大多數(shù)編程語(yǔ)言和環(huán)境都支持使用SQLite數(shù)據(jù)庫(kù)。Python也不例外,sqlite3自版本以來,Python已包含一個(gè)用于訪問SQLite數(shù)據(jù)庫(kù)的庫(kù)2.5。
 

  在如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)中,我們將逐步介紹如何使用它sqlite3來創(chuàng)建,查詢和更新數(shù)據(jù)庫(kù)。我們還將介紹如何使用pandas包簡(jiǎn)化使用SQLite數(shù)據(jù)庫(kù)的工作。我們將使用Python 3.5,但是同樣的方法應(yīng)該適用于Python 2。
 

  如果您想學(xué)習(xí)SQL的基礎(chǔ)知識(shí),則可能想先閱讀我們的SQL基礎(chǔ)知識(shí)文章。
 

  在開始之前,讓我們快速看一下將要使用的數(shù)據(jù)。我們將查看航空公司的航班數(shù)據(jù),其中包含有關(guān)航空公司,機(jī)場(chǎng)以及機(jī)場(chǎng)之間路線的信息。每條路線代表航空公司在源機(jī)場(chǎng)和目的地機(jī)場(chǎng)之間飛行的重復(fù)航班。
 

  所有的數(shù)據(jù)是在一個(gè)名為SQLite數(shù)據(jù)庫(kù)flights.db,其中包含三個(gè)表- airports,airlines和routes。您可以在此處下載數(shù)據(jù)。
 

  這是airlines表格中的兩行:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  如上所示,每一行是不同的航空公司,每一列都是該航空公司的屬性,例如name和country。每個(gè)航空公司都有一個(gè)獨(dú)特的id,因此我們可以在需要時(shí)輕松查找它。
 

  這是airports表格中的兩行:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  如您所見,每一行都對(duì)應(yīng)一個(gè)機(jī)場(chǎng),并包含有關(guān)機(jī)場(chǎng)位置的信息。每個(gè)機(jī)場(chǎng)都有一個(gè)獨(dú)特的id,因此我們可以輕松查找它。
 

  這是routes表格中的兩行:
 

  每個(gè)路由包含airline_id,其中id該飛行路線,以及航空公司source_id,這是機(jī)場(chǎng)的ID,該航線從起源,和dest_id,這是飛行的目的地機(jī)場(chǎng)的標(biāo)識(shí)。
 

  現(xiàn)在我們知道我們正在使用哪種數(shù)據(jù),讓我們從連接到數(shù)據(jù)庫(kù)并運(yùn)行查詢開始。
 

  在Python中查詢數(shù)據(jù)庫(kù)行
 

  為了使用來自Python的SQLite數(shù)據(jù)庫(kù),我們首先必須連接到它。我們可以使用connect函數(shù)來做到這一點(diǎn),該函數(shù)返回一個(gè)Connection對(duì)象:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  一旦有了Connection對(duì)象,就可以創(chuàng)建一個(gè)Cursor對(duì)象。游標(biāo)使我們能夠?qū)?shù)據(jù)庫(kù)執(zhí)行SQL查詢:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  一旦有了Cursor對(duì)象,就可以使用它以適當(dāng)命名的execute方法對(duì)數(shù)據(jù)庫(kù)執(zhí)行查詢。下面的代碼將從表中獲取第一5行airlines:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  您可能已經(jīng)注意到,我們沒有將上述查詢的結(jié)果分配給變量。這是因?yàn)槲覀冃枰\(yùn)行另一個(gè)命令來實(shí)際獲取結(jié)果。我們可以使用fetchall方法來獲取查詢的所有結(jié)果:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  如您所見,結(jié)果被格式化為元組列表。每個(gè)元組對(duì)應(yīng)于我們?cè)L問的數(shù)據(jù)庫(kù)中的一行。用這種方式處理數(shù)據(jù)是很痛苦的。我們需要手動(dòng)添加列標(biāo)題,并手動(dòng)解析數(shù)據(jù)。幸運(yùn)的是,pandas庫(kù)有一個(gè)更簡(jiǎn)單的方法,我們將在下一部分中介紹。
 

  在繼續(xù)之前,最好關(guān)閉已打開的Connection對(duì)象和Cursor對(duì)象。這樣可以防止SQLite數(shù)據(jù)庫(kù)被鎖定。當(dāng)SQLite數(shù)據(jù)庫(kù)被鎖定時(shí),您可能無(wú)法更新數(shù)據(jù)庫(kù),并且可能會(huì)出錯(cuò)。我們可以這樣關(guān)閉游標(biāo)和連接:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  映射機(jī)場(chǎng)
 

  利用我們新發(fā)現(xiàn)的查詢知識(shí),我們可以創(chuàng)建一個(gè)圖表,顯示世界上所有機(jī)場(chǎng)的位置。首先,我們查詢緯度和經(jīng)度:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  上面的查詢將從中檢索latitude和longitude列airports,并將它們都轉(zhuǎn)換為浮點(diǎn)數(shù)。然后,我們調(diào)用該fetchall方法以檢索它們。
 

  然后,我們需要通過導(dǎo)入matplotlib(Python的主要繪圖庫(kù))來設(shè)置繪圖。與底圖軟件包結(jié)合使用,這使我們只能使用Python創(chuàng)建地圖。
 

  我們首先需要導(dǎo)入庫(kù):

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  然后,我們?cè)O(shè)置地圖,并繪制將構(gòu)成地圖背景的大陸和海岸線:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  最后,我們?cè)诘貓D上繪制每個(gè)機(jī)場(chǎng)的坐標(biāo)。我們從SQLite數(shù)據(jù)庫(kù)中檢索了一個(gè)元組列表。每個(gè)元組中的第一個(gè)元素是機(jī)場(chǎng)的經(jīng)度,第二個(gè)元素是緯度。我們將經(jīng)度和緯度轉(zhuǎn)換為它們自己的列表,然后在地圖上繪制它們:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  我們最終得到一張顯示世界上每個(gè)機(jī)場(chǎng)的地圖:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  您可能已經(jīng)注意到,使用數(shù)據(jù)庫(kù)中的數(shù)據(jù)有些麻煩。我們需要記住每個(gè)元組中的哪個(gè)位置對(duì)應(yīng)于哪個(gè)數(shù)據(jù)庫(kù)列,并手動(dòng)解析出每個(gè)列的單獨(dú)列表。幸運(yùn)的是,pandas庫(kù)為我們提供了一種使用SQL查詢結(jié)果的簡(jiǎn)便方法。
 

  將結(jié)果讀入pandas DataFrame
 

  我們可以使用pandas read_sql_query函數(shù)將SQL查詢的結(jié)果直接讀入pandas DataFrame中。下面的代碼將執(zhí)行與我們剛才相同的查詢,但是它將返回一個(gè)DataFrame。與我們上面的查詢相比,它具有幾個(gè)優(yōu)點(diǎn):
 

  1)它不需要我們?cè)谧詈髣?chuàng)建一個(gè)Cursor對(duì)象或調(diào)用fetchall。

  2)它會(huì)自動(dòng)從表中讀取標(biāo)題的名稱。

  3)它創(chuàng)建了一個(gè)DataFrame,因此我們可以快速瀏覽數(shù)據(jù)。

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  如您所見,結(jié)果得到了格式正確的DataFrame。我們可以輕松地操作列:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  強(qiáng)烈建議盡可能使用此read_sql_query功能。
 

  映射路線
 

  現(xiàn)在我們知道了如何將查詢讀取到熊貓數(shù)據(jù)框,我們可以創(chuàng)建世界上每條航線的地圖。我們首先從查詢數(shù)據(jù)開始。以下查詢將:
 

  1)獲取每個(gè)路線的源機(jī)場(chǎng)的緯度和經(jīng)度。

  2)獲取每個(gè)路線的目標(biāo)機(jī)場(chǎng)的緯度和經(jīng)度。

  3)將所有坐標(biāo)值轉(zhuǎn)換為浮點(diǎn)數(shù)。

  4)將結(jié)果讀取到DataFrame中,并將其存儲(chǔ)到變量中routes。

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  然后,我們?cè)O(shè)置地圖:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  我們遍歷第一3000行并繪制它們。以下代碼將:
 

  1)循環(huán)瀏覽中的第一3000行routes。

  2)找出路線是否太長(zhǎng)。

  3)如果路線不太長(zhǎng):

  a)在起點(diǎn)和終點(diǎn)之間畫一個(gè)圓。

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  我們最終得到以下地圖:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  當(dāng)我們使用pandas將SQL查詢的結(jié)果轉(zhuǎn)換為DataFrame而不是處理來自的原始結(jié)果時(shí),上述方法效率更高sqlite3。
 

  現(xiàn)在我們知道了如何查詢數(shù)據(jù)庫(kù)行,讓我們繼續(xù)進(jìn)行修改。
 

  修改數(shù)據(jù)庫(kù)行
 

  我們可以使用該sqlite3包通過插入,更新或刪除行來修改SQLite數(shù)據(jù)庫(kù)。創(chuàng)建連接的過程與查詢表時(shí)的創(chuàng)建過程相同,因此我們將跳過該部分。
 

  使用Python插入行
 

  要插入一行,我們需要編寫一個(gè)INSERT查詢。以下代碼將在airlines表中添加新行。我們指定9要插入的值,為中的每一列輸入一個(gè)airlines。這將在表中添加新行。

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  如果您現(xiàn)在嘗試查詢?cè)摫?,?shí)際上您將不會(huì)看到新行。相反,您會(huì)看到創(chuàng)建了一個(gè)名為的文件flights.db-journal。flights.db-journal將存儲(chǔ)新行,直到準(zhǔn)備好將commit其存儲(chǔ)到主數(shù)據(jù)庫(kù)中為止flights.db。
 

  在提交事務(wù)之前,SQLite不會(huì)寫入數(shù)據(jù)庫(kù)。一個(gè)事務(wù)由1個(gè)或多個(gè)查詢組成,這些查詢?nèi)恳淮螌?duì)數(shù)據(jù)庫(kù)進(jìn)行更改。目的是使從意外更改或錯(cuò)誤中恢復(fù)更加容易。事務(wù)使您可以運(yùn)行多個(gè)查詢,然后最終使用所有查詢的結(jié)果更改數(shù)據(jù)庫(kù)。這樣可以確保如果其中一個(gè)查詢失敗,則不會(huì)部分更新數(shù)據(jù)庫(kù)。
 

  一個(gè)很好的例子是,如果您有兩個(gè)表,其中一個(gè)表包含對(duì)人民銀行帳戶收取的費(fèi)用(charges),而另一個(gè)表則包含銀行帳戶中的美元金額(balances)。假設(shè)某位銀行客戶羅伯托(Roberto)想寄50美元給他的妹妹路易莎(Luisa)。為了使這項(xiàng)工作有效,銀行需要:
 

  1)在charges其中創(chuàng)建一行,說從Roberto的帳戶中取出了$ 50并發(fā)送給了Luisa。

  2)更新balances表中Roberto的行并刪除$ 50。

  3)更新balances表中Luisa的行并添加$ 50。
 

  這些將需要三個(gè)單獨(dú)的SQL查詢來更新所有表。如果查詢失敗,我們將在數(shù)據(jù)庫(kù)中保留錯(cuò)誤數(shù)據(jù)。例如,如果前兩個(gè)查詢有效,則第三個(gè)查詢失敗,Roberto會(huì)賠錢,但Luisa不會(huì)。事務(wù)意味著除非所有查詢成功,否則不會(huì)更新主數(shù)據(jù)庫(kù)。這樣可以防止系統(tǒng)陷入客戶損失金錢的糟糕狀態(tài)。
 

  默認(rèn)情況下,sqlite3當(dāng)您執(zhí)行任何修改數(shù)據(jù)庫(kù)的查詢時(shí),將打開一個(gè)事務(wù)。您可以在此處了解更多信息。我們可以提交事務(wù),并airlines使用commit方法將新行添加到表中:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  現(xiàn)在,當(dāng)我們查詢時(shí)flights.db,我們將看到包含測(cè)試飛行的額外行:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  將參數(shù)傳遞給查詢
 

  在上一個(gè)查詢中,我們對(duì)要插入數(shù)據(jù)庫(kù)的值進(jìn)行了硬編碼。在大多數(shù)情況下,當(dāng)您將數(shù)據(jù)插入數(shù)據(jù)庫(kù)時(shí)??,它不會(huì)被硬編碼,而是您要傳遞的動(dòng)態(tài)值。這些動(dòng)態(tài)值可能來自下載的數(shù)據(jù),也可能來自用戶輸入。
 

  使用動(dòng)態(tài)數(shù)據(jù)時(shí),可能很想使用Python字符串格式插入值:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  您要避免這樣做!使用Python字符串格式插入值會(huì)使您的程序容易受到SQL Injection攻擊。幸運(yùn)的是,sqlite3有一種簡(jiǎn)單的方法可以在不依賴字符串格式的情況下注入動(dòng)態(tài)值:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  ?查詢中的任何值都將替換為中的值values。第一個(gè)?將被替換為第一個(gè)項(xiàng)目values,第二個(gè)被替換為第二個(gè)項(xiàng)目,依此類推。這適用于任何類型的查詢。這創(chuàng)建了一個(gè)SQLite參數(shù)化查詢,避免了SQL注入問題。
 

  更新行
 

  我們可以使用以下execute方法修改SQLite表中的行:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  然后,我們可以驗(yàn)證更新是否發(fā)生:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  刪除行
 

  最后,我們可以使用以下execute方法刪除數(shù)據(jù)庫(kù)中的行:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  然后,通過確保沒有行與查詢匹配,我們可以驗(yàn)證刪除操作的發(fā)生:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  建立表格
 

  我們可以通過執(zhí)行SQL查詢來創(chuàng)建表。我們可以創(chuàng)建一個(gè)表格來表示航線上的每個(gè)每日航班,其中包括以下幾列:
 

  1)id - 整數(shù)

  2)departure —日期,航班離開機(jī)場(chǎng)時(shí)

  3)arrival —日期,航班到達(dá)目的地

  4)number —文字,航班號(hào)

  5)route_id —整數(shù),航班飛行的路線ID

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  創(chuàng)建表后,我們可以正常地將數(shù)據(jù)插入其中:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  查詢表時(shí),現(xiàn)在將看到以下行:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  用熊貓創(chuàng)建表
 

  pandas包為我們提供了一種更快的創(chuàng)建表的方法。我們只需要首先創(chuàng)建一個(gè)DataFrame,然后將其導(dǎo)出到SQL表即可。首先,我們將創(chuàng)建一個(gè)DataFrame:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  然后,我們將能夠調(diào)用to_sql方法以轉(zhuǎn)換df為數(shù)據(jù)庫(kù)中的表。我們將keep_exists參數(shù)設(shè)置為,replace以刪除和替換任何名為的現(xiàn)有表daily_flights:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  然后,我們可以通過查詢數(shù)據(jù)庫(kù)來驗(yàn)證一切正常:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  用熊貓修改表
 

  實(shí)際數(shù)據(jù)科學(xué)中最困難的部分之一是,每條記錄所擁有的數(shù)據(jù)經(jīng)常更改。使用我們的航空公司示例,我們可能決定airplanes在airlines表中添加一個(gè)字段,以指示每個(gè)航空公司擁有多少架飛機(jī)。幸運(yùn)的是,有一種方法可以更改表以在SQLite中添加列:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  請(qǐng)注意,我們不需要調(diào)用commit -alter table查詢會(huì)立即執(zhí)行,并且不會(huì)放入事務(wù)中?,F(xiàn)在,我們可以查詢并看到額外的列:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  請(qǐng)注意,所有列都null在SQLite中設(shè)置為(None在Python中轉(zhuǎn)換為),因?yàn)樵摿羞€沒有任何值。
 

  用熊貓修改表
 

  也可以使用Pandas來更改表,方法是將表導(dǎo)出到DataFrame,對(duì)DataFrame進(jìn)行修改,然后將DataFrame導(dǎo)出到表:

如何使用Python和Pandas處理SQLite數(shù)據(jù)庫(kù)
 

  上面的代碼將delay_minutes在daily_flights表中添加一列。
 

  下一步
 

  現(xiàn)在,您應(yīng)該掌握如何使用Python和pandas處理SQLite數(shù)據(jù)庫(kù)中的數(shù)據(jù)。我們介紹了查詢數(shù)據(jù)庫(kù),更新行,插入行,刪除行,創(chuàng)建表和更改表。它涵蓋了所有主要的SQL操作,以及您日常使用的幾乎所有內(nèi)容。
 

  如果您想了解有關(guān)如何使用Python和SQL的更多信息,可以在AAA教育上查看我們的交互式SQL課程。


 

預(yù)約申請(qǐng)免費(fèi)試聽課

填寫下面表單即可預(yù)約申請(qǐng)免費(fèi)試聽!怕錢不夠?可先就業(yè)掙錢后再付學(xué)費(fèi)! 怕學(xué)不會(huì)?助教全程陪讀,隨時(shí)解惑!擔(dān)心就業(yè)?一地學(xué)習(xí),可推薦就業(yè)!

?2007-2021/北京漫動(dòng)者教育科技有限公司版權(quán)所有
備案號(hào):京ICP備12034770號(hào)

?2007-2022/ m.5wd995.cn 北京漫動(dòng)者數(shù)字科技有限公司 備案號(hào): 京ICP備12034770號(hào) 監(jiān)督電話:010-53672995 郵箱:bjaaa@aaaedu.cc

京公網(wǎng)安備 11010802035704號(hào)

網(wǎng)站地圖