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

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

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

　　如果您想學習SQL的基礎(chǔ)知識，則可能想先閱讀我們的SQL基礎(chǔ)知識文章。
 

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

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

　　這是airlines表格中的兩行：

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

　　這是airports表格中的兩行：

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

　　這是routes表格中的兩行：
 

　　每個路由包含airline_id，其中id該飛行路線，以及航空公司source_id，這是機場的ID，該航線從起源，和dest_id，這是飛行的目的地機場的標識。
 

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

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

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

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

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

　　您可能已經(jīng)注意到，我們沒有將上述查詢的結(jié)果分配給變量。這是因為我們需要運行另一個命令來實際獲取結(jié)果。我們可以使用fetchall方法來獲取查詢的所有結(jié)果：

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

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

　　映射機場
 

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

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

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

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

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

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

　　我們最終得到一張顯示世界上每個機場的地圖：

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

　　將結(jié)果讀入pandas DataFrame
 

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

　　1)它不需要我們在最后創(chuàng)建一個Cursor對象或調(diào)用fetchall。

　　2)它會自動從表中讀取標題的名稱。

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

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

　　強烈建議盡可能使用此read_sql_query功能。
 

　　映射路線
 

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

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

　　2)獲取每個路線的目標機場的緯度和經(jīng)度。

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

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

　　然后，我們設(shè)置地圖：

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

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

　　2)找出路線是否太長。

　　3)如果路線不太長：

　　a)在起點和終點之間畫一個圓。

　　我們最終得到以下地圖：

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

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

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

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

　　使用Python插入行
 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　更新行
 

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

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

　　刪除行
 

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

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

　　建立表格
 

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

　　1)id - 整數(shù)

　　2)departure —日期，航班離開機場時

　　3)arrival —日期，航班到達目的地

　　4)number —文字，航班號

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

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

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

　　用熊貓創(chuàng)建表
 

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

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

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

　　用熊貓修改表
 

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

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

　　請注意，所有列都null在SQLite中設(shè)置為(None在Python中轉(zhuǎn)換為)，因為該列還沒有任何值。
 

　　用熊貓修改表
 

　　也可以使用Pandas來更改表，方法是將表導(dǎo)出到DataFrame，對DataFrame進行修改，然后將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課程。