[譯] Python 和 Pandas 在 SQLite 數據庫中的運用

• 
  
• 原文作者：Vik Paruchuri
• 譯文出自：掘金翻譯計劃
• 譯者： Nicolas(Yifei) Li
• 校對者：marcmoore, futureshine

SQLite 是一個數據庫引擎，使用它能方便地存儲和處理關系型數據。它和 cvs 格式很相似，SQLite 把數據存儲在一個單獨的文件中，它能方便地與其他人共享。大部分的編程語言和編譯環境都對 SQLite 數據庫提供了很好的支持。Python 也不例外，并且專門有一個訪問 SQLite 數據庫的程序庫，叫做 sqlite3，自從 2.5 版本以來，它就已經被 Python 納入標準庫里。在這篇博文中，我們將學會如何使用 sqlite3 創建、查詢和更新數據庫。也包括了，使用 pandas 程序包如何簡化 SQLite 數據庫。我們會使用 Python 3.5，但是所有的實現方法應該兼容 Python 2。

在我們開始之前，讓我們先快速檢閱下我們之后要處理的數據。我們看到的是航空公司的航班數據，它包含了有關航空公司的信息，機場名稱和往來各個機場的航線名稱。每一條航線代表著有一架航班重復往返于目的地和始發地的機場。

所有的數據都存在一個叫做 flights.db 的數據庫中，它有三張表格 - airports, airliens, routes。你可以到 這里 下載到它。

這里有兩行來自 airlines 表格的數據:

就如你在上表中看到的，每一行都是一個不同的航空公司，每一列是這個航空公司的屬性，例如 name 和 country。每一個航空公司也都有一個獨一無二的 id，所以如果需要的時候，我們能非常方便地查詢到。

這里有兩行來自 airports 表格的數據:

就如你所看到的，每一行都對應了一個機場，并且包含了機場所在地的信息。每一個機場也有一個獨一無二的 id，所以我們也能方便地進行查詢。

這里有兩行來自 routes 表格的數據:

每一條航線包含有一個 airline_id，這個 id 代表飛這條航線的航空公司，souce_id 也是，它是航班始發地機場的 id，而 dest_id 是該航班目的地機場的 id。

至此，我們知道了需要處理的是什么數據，讓我們先從連接數據庫和執行一條查詢指令開始。

使用 Python 執行數據庫的查詢指令

為了通過 Python 使用 SQLite 數據庫，我們先要連接這個數據庫。我們可以使用 connect 方法, 它返回一個 Connection 對象:

import sqlite3

conn = sqlite3.connect("flights.db")

一旦我們有了一個 Connection 對象，之后創建一個 Cursor 對象。Cursors 讓我們能對一個數據庫執行 SQL 查詢指令。

cur = conn.cursor()

一旦我們有了這個 Cursor 對象，我們能使用它通過適當地調用 execute 方法來對數據庫執行查詢指令。通過以下代碼，你能從 airlines 表中獲取前 5 行數據結果。

cur.execute("select * from airlines limit 5;")

你可能已經注意到，我們沒有把之前的查詢結果存儲到一個變量中。這是因為我們需要執行另外一個指令來真正地獲得結果。我們可以使用 fetchall 方法來獲取查詢的結果。

results = cur.fetchall()
print(results)





[(0, '1', 'Private flight', '\\N', '-', None, None, None, 'Y'),
 (1, '2', '135 Airways', '\\N', None, 'GNL', 'GENERAL', 'United States', 'N'),
 (2, '3', '1Time Airline', '\\N', '1T', 'RNX', 'NEXTIME', 'South Africa', 'Y'),
 (3, '4', '2 Sqn No 1 Elementary Flying Training School', '\\N', None, 'WYT', None, 'United Kingdom', 'N'),
 (4, '5', '213 Flight Unit', '\\N', None, 'TFU', None, 'Russia', 'N')]

如你所見，查詢結果以 一組 tuples 的格式返回。每一個 tuple 對應了我們從數據庫中訪問到某一行數據。以這種形式處理數據是非常麻煩的。我們需要人為地增加每一列的表頭，并且手動解析數據。 幸運的是，pandas 提供的庫中有更加便捷的方法，我們會在下一個部分中提到它。

在我們繼續探索之前，及時關閉那些被打開的 Connection 對象 和 Cursor 對象 是良好的習慣。這樣避免了 SQLite 數據庫被鎖上。當一個 SQLite 數據庫被鎖上的時候，你可能就無法對這個數據庫進行更新操作了，也會得到錯誤的提示。我們能通過以下方式關閉 Connection 對象 和 Cusor 對象:

cur.close()
conn.close()

繪制機場地圖

使用我們新發現的查詢指令，我們能在世界地圖上描繪和展示出所有機場的位置。首先，我們先要查詢機場的經緯度坐標：

import sqlite3

conn = sqlite3.connect("flights.db")
cur = conn.cursor()
coords = cur.execute("""
  select cast(longitude as float),
  cast(latitude as float)
  from airports;"""
).fetchall()

以上的查詢代碼將檢索返回 airports 表中每列 latitude 和 longitude 的數據，并把結果轉化成 float 類型。之后，我們調用 fetchall 方法來獲取他們。

接下來，我們通過導入 matplotlib 來創建我們的測繪圖，它是 Python 上主要的繪圖庫。結合 basemap 包，這允許我們只使用 Python 就能創建地圖。

首先，我們需要導入這些庫:

from mpl_toolkits.basemap import Basemap
import matplotlib.pyplot as plt

之后，建立我們的地圖，并且描繪出大陸和海岸線，它們會構成我們地圖的背景。

m = Basemap(
  projection='merc',
  llcrnrlat=-80,
  urcrnrlat=80,
  llcrnrlon=-180,
  urcrnrlon=180,
  lat_ts=20,
  resolution='c'
)

m.drawcoastlines()
m.drawmapboundary()

最后，我們在地圖上描繪出每一個機場的坐標。我們從 SQLite 數據庫中檢索一組 tuples。 在每個 tuple 中第一個元素是飛機場的經度，第二個是緯度。我們會把這些經度和緯度轉換成它們自己的數組，之后把它們描繪在地圖上。

x, y = m(
  [l[0] for l in coords],
  [l[1] for l in coords]
)

m.scatter(
  x,
  y,
  1,
  marker='o',
  color='red'
)

最終，我們把每一個機場都展現在了世界地圖上:

你可能注意到，直接操作來自數據庫的數據讓你痛苦不堪。我們需要記住每一個 tuple 的位置對應到數據庫中每一列是什么，并且手動為每一列解析出每組各自的內容。

用 pandas DataFrame 讀取數據結果

我們能使用 pandas 的 read_sql_query 方法直接把一條 SQL 查詢結果讀取到一個 pandas DataFrame 中。下面的代碼將執行和前文中作用一樣的查詢，但是它會返回一個 DataFrame。對比前文的數據查詢方式，它能帶來諸多好處:

• 我們不需要每次到最后都創建一個 Cursor 對象 或者調用 fetchall。
• 它能自動通過表頭的名字來閱讀整個表。
• 它創建了一個 DataFrame，所以我們能快速的挖掘數據。

    import pandas as pd
    import sqlite3

    conn = sqlite3.connect("flights.db")
    df = pd.read_sql_query("select * from airlines limit 5;", conn)
    df

如你所見，我們得到了一個有著清晰格式的 DataFrame 作為結果。我們能方便地操作這些列:

df["country"]





  0              None
  1     United States
  2      South Africa
  3    United Kingdom
  4            Russia
  Name: country, dtype: object

強烈建議盡可能使用 read_sql_query 方法。

構建航線圖

至此，我們已經知道如何把查詢結果讀取到 pandas DataFrames 中，我們能在世界地圖上創建每一個航空公司的航線圖。首先，我們需要查詢這些數據。查詢方式如下:

• 獲取每一條航線中始發地機場的經緯度。
• 獲取每一條航線中目的地機場的經緯度。
• 把所有這些坐標轉換成 float 類型。
• 把檢索結果讀取進一個 DataFrame 中，并把他們存在變量 routes 中。

    routes = pd.read_sql_query("""
                               select cast(sa.longitude as float) as source_lon,
                               cast(sa.latitude as float) as source_lat,
                               cast(da.longitude as float) as dest_lon,
                               cast(da.latitude as float) as dest_lat
                               from routes
                               inner join airports sa on
                               sa.id = routes.source_id
                               inner join airports da on
                               da.id = routes.dest_id;
                               """,
                               conn)

之后，我們開始創建地圖:

m = Basemap(projection='merc',llcrnrlat=-80,urcrnrlat=80,llcrnrlon=-180,urcrnrlon=180,lat_ts=20,resolution='c')
m.drawcoastlines()

首先，我們開始遍歷最先的 3000 行數據，并繪制他們。代碼如下:

• 把前 3000 行數據遍歷存儲到 routes 中。
• 判斷航線是否太長。
• 如果航線不是很長:
  • 在始發地和目的地之間畫一個圈。

    for name, row in routes[:3000].iterrows():
        if abs(row["source_lon"] - row["dest_lon"]) < 90:
            # Draw a great circle between source and dest airports.
            m.drawgreatcircle(
                row["source_lon"],
                row["source_lat"],
                row["dest_lon"],
                row["dest_lat"],
                linewidth=1,
                color='b'
            )

最后，我們完成了這個地圖:

比起直接使用 sqlite3 處理這些原始檢索數據，當我們使用 pandas 把所有的 SQL 檢索到的數據讀入一個 DataFrame 中是一個非常有效的方法。

至此，我們理解了如何檢索數據庫的內容，接下來，讓我們看看如何對這些數據進行修改。

這篇博文還是挺有趣的吧？ 讓我們使用 Dataquest 學習數據科學

• 選一個你喜歡的瀏覽器繼續學習。
• 操作真實世界的數據。
• 創建一個項目集。

免費在線課堂

修改數據庫的內容

我們可以使用 sqlite3 開發包來修改一個 SQLite 數據庫，比如插入，更新或者刪除某些行內容。創建數據庫的連接和查詢一個數據表的方法一樣，所以我們會跳過這個部分。

使用 Python 插入行內容

為了插入一行數據，我們需要寫一條 INSERT 查詢指令。以下代碼會對 airlines 表中新增加一行數據。我們指定 9 個需要被添加的數據，對應著 airlines 表格的每一列。這會為這個表增加一行新數據。

cur = conn.cursor()
cur.execute("insert into airlines values (6048, 19846, 'Test flight', '', '', null, null, null, 'Y')")

如果你嘗試對這個表格進行檢索，你其實還不能看到這條新的數據。然而，你會看到一個名字為 flights.db-journal 的文件被創建了。在你準備好把它 commit 到主數據庫 flights.db之前，flights.db-journal 會代為存儲新增加的行數據。

SQLite 并不會寫入數據庫直到你提交了一個 transaction。每一個 transaction 包含了 1 個或者多個查詢指令，它能把所有新的變化一次性提交給數據庫。這樣的設計使得從意外的修改或錯誤中恢復變得更加容易。Transaction 允許你執行多個查詢指令，最終這些結果都會修改數據庫。這確保了如果有一條查詢指令失敗了，數據庫不會只有部分內容被更新。

舉個例子來說，如果你有兩張表，一張表包含了對銀行賬戶收取的費用(charges)，另一張表包含了賬戶在銀行內存款的余額(balances)。假定有一位銀行客戶 Roberto，他想給姐妹 Luisa 轉 $50 美元。為了完成這筆交易，銀行應該需要執行以下幾步:

• 在 charges 中新增加一行，描述有 $50 美元正要從 Roberto 的賬戶轉到 Luisa。
• 更新 Roberto balances 表中的數據內容，并且移除 $50 美元。
• 更新 Luisa balances 表中的數據內容，并且增加 $50 美元。

如此來說，為了更新所有的表格需要三次單獨的 SQL 查詢指令。如果一個查詢指令失敗了，我們的數據庫就會被破損的數據卡住。舉例來說，如果前兩條指令成功運行了，第三條失敗了，Roberto 將會損失他的錢，但是 Luisa 也不會獲得這筆錢。Transactions 意味著主數據庫不會被更新除非所有的查詢指令都被成功執行。這避免了系統進入錯誤的狀態，用戶可能會丟失他們的存款。

默認情況下，當你執行了任何會修改數據庫的查詢指令時，sqlite3 會打開一個 transaction。你能在 這里 了解更多。我們能提交 transaction，也能使用 commit 方法對 airlines 表新增加內容:

conn.commit()

現在，當我們檢索 flights.db 的時候，我們將看到這個額外的數據，它包含我們的測試航班。

pd.read_sql_query("select * from airlines where id=19846;", conn)

對檢索增加條件參數

在最后那條查詢指令中，我們把固定值插入到所需要的數據庫中。多數情況下，當你想插入數據到數據庫中的時候，它不會是一些固定值，它應該是一些你想傳入方法的動態值。這些動態值可能來自于下載得到的數據，或者來自于用戶的輸入。

當操作動態值的時候，有些人嘗試用 Python 的格式化字符串來插入這些值:

cur = conn.cursor()
name = "Test Flight"
cur.execute("insert into airlines values (6049, 19847, {0}, '', '', null, null, null, 'Y')".format(name))
conn.commit()

你應該避免這樣做！通過 Python 的格式化字符串插入數值會讓你的程序更加容易受到 SQL 注入 的攻擊。幸運的是，sqlite3 有一個更加直接的方式來注入動態值，而不是依賴格式化的字符串。

cur = conn.cursor()
values = ('Test Flight', 'Y')
cur.execute("insert into airlines values (6049, 19847, ?, '', '', null, null, null, ?)", values)
conn.commit()

任何在查詢指令中以 ? 形式出現的數值都會被 values 中的數值替代。第一個 ? 將會被 values 中的第一個數值替代，第二個也是，其他以此類推。這個方式對任何形式的查詢指令都有用。如此就創建了一個 SQLite 帶參數形式的查詢指令，它有效避免了 SQLite 注入 的問題。

更新行數據內容

通過使用 execute 方法，我們可以修改在 SQLite 表格中某些行數據的內容:

cur = conn.cursor()
values = ('USA', 19847)
cur.execute("update airlines set country=? where id=?", values)
conn.commit()

之后，我們能驗證更新的內容:

pd.read_sql_query("select * from airlines where id=19847;", conn)

刪除某些行數據的內容

最后，通過使用 execute 方法，我們能刪除數據庫中的某些行數據內容:

cur = conn.cursor()
values = (19847, )
cur.execute("delete from airlines where id=?", values)
conn.commit()

之后，通過確認沒有相匹配的查詢內容，我們能驗證這些行數據內容確實被刪除了:

pd.read_sql_query("select * from airlines where id=19847;", conn)

創建表格

我們可以通過執行一條 SQLite 查詢指令來創建表。我們能創建一個表，它能展示每天在某一條航線上的航班，使用以下幾列:

• id — 整型
• departure — 日期型，表示飛機離開機場的時間
• arrival — 日期型，表示飛機到達目的地的時間
• number — 文本型，飛機航班號
• route_id — 整型，正在飛行的航線號

    cur = conn.cursor()
    cur.execute("create table daily_flights (id integer, departure date, arrival date, number text, route_id integer)")
    conn.commit()

一旦我們創建了這個表，我們就能對這個表插入數據:

cur.execute("insert into daily_flights values (1, '2016-09-28 0:00', '2016-09-28 12:00', 'T1', 1)")
conn.commit()

當我們對該表執行查詢指令的時候，我們就能看到這些行數據內容:

pd.read_sql_query("select * from daily_flights;", conn)

使用 pandas 創建表

pandas 包提供給我們一個更加快捷地創建表格的方法。我們只需要先創建一個 DataFrame，之后把它導出到一個 SQL 表格內。首先，我們將創建一個 DataFrame:

from datetime import datetime
df = pd.DataFrame(
    [[1, datetime(2016, 9, 29, 0, 0) , datetime(2016, 9, 29, 12, 0), 'T1', 1]],
    columns=["id", "departure", "arrival", "number", "route_id"]
)

之后，我們就能調用 to_sql 方法，它將 df 轉化成一個數據庫中的數據表。我們把參數 keep_exists 設定成 replace，為了刪除并且替換數據庫中任何已存在的 daily_flights:

df.to_sql("daily_flights", conn, if_exists="replace")

通過對數據庫執行查詢指令，我們能驗證是否正常工作了:

pd.read_sql_query("select * from daily_flights;", conn)

使用 Pandas 修改數據表

對于現實世界中的數據科學來說，最難處理的部分就是那些幾乎經常每秒都不停變換著的數據。拿 aireline 這個例子來說，我們可能決定在 airelines 表中新增加一個 airplanes 的屬性，它顯示出每一個航空公司擁有多少架飛機。幸運的是，在 SQLite 中有一個方式能修改表并且添加這些列:

cur.execute("alter table airlines add column airplanes integer;")

請注意，我們不需要調用 commit 方法 —— alter table 查詢指令會被立刻執行，并且不會發生在一個 transaction 中?，F在，我們能查詢并且看到這些額外的列:

pd.read_sql_query("select * from airlines limit 1;", conn)

你可能注意到了，在 SQLite 中所有的列都被設值成了 null（在 Python 中被轉化成了 None），因為這些列還沒有任何數值。

使用 Pandas 修改表

也可以使用 Pandas 通過把表導出成 DataFrame 去修改表格的內容，僅需要對 DataFrame 進行修改，之后把這個 DataFrame 導出成一個表:

df = pd.read_sql("select * from daily_flights", conn)
df["delay_minutes"] = None
df.to_sql("daily_flights", conn, if_exists="replace")

以上代碼將會對 daily_flight 表增加一個叫做 delay_minutes 的列項。