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

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

　　在如何使用Python和Pandas處理SQLite數(shù)據(jù)庫中，我們將逐步介紹如何使用它sqlite3來創(chuàng)建，查詢和更新數(shù)據(jù)庫。我們還將介紹如何使用pandas包簡(jiǎn)化使用SQLite數(shù)據(jù)庫的工作。我們將使用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ù)庫flights.db，其中包含三個(gè)表- airports，airlines和routes。您可以在此處下載數(shù)據(jù)。
 

　　這是airlines表格中的兩行：

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

　　這是airports表格中的兩行：

　　如您所見，每一行都對(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ù)庫并運(yùn)行查詢開始。
 

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

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

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

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

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

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

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

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

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

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

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

　　我們首先需要導(dǎo)入庫：

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

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

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

　　您可能已經(jīng)注意到，使用數(shù)據(jù)庫中的數(shù)據(jù)有些麻煩。我們需要記住每個(gè)元組中的哪個(gè)位置對(duì)應(yīng)于哪個(gè)數(shù)據(jù)庫列，并手動(dòng)解析出每個(gè)列的單獨(dú)列表。幸運(yùn)的是，pandas庫為我們提供了一種使用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ù)。

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

　　強(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。

　　然后，我們?cè)O(shè)置地圖：

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

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

　　2)找出路線是否太長。

　　3)如果路線不太長：

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

　　我們最終得到以下地圖：

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

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

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

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

　　使用Python插入行
 

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

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

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

　　一個(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ù)庫中保留錯(cuò)誤數(shù)據(jù)。例如，如果前兩個(gè)查詢有效，則第三個(gè)查詢失敗，Roberto會(huì)賠錢，但Luisa不會(huì)。事務(wù)意味著除非所有查詢成功，否則不會(huì)更新主數(shù)據(jù)庫。這樣可以防止系統(tǒng)陷入客戶損失金錢的糟糕狀態(tài)。
 

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

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

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

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

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

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

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

　　更新行
 

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

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

　　刪除行
 

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

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

　　建立表格
 

　　我們可以通過執(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

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

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

　　用熊貓創(chuàng)建表
 

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

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

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

　　用熊貓修改表
 

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

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

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

　　用熊貓修改表
 

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

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

　　下一步
 

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

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