－Ben's PHOTO－

先說明這篇可能不是什麼手把手新手教學文，而是撰寫 Python 存取 SQLite （或其他）資料庫時，發現有些重要但可能容易被忽略的細節，所以寫篇文章來記錄心得，本文內容會儘量精簡（先前的技術性文章，有些內容廢話太多，複習起來有點痛苦...）

為了快速進入正題，先用 SQLite 工具快速建立 demo 資料庫，產生簡單的電玩遊戲資料如下：

接著撰寫少許程式碼，對資料庫進行 SELECT 讀取：

import sqlite3
conn = sqlite3.connect("demo.db")
rows = conn.execute("select * from games;")
print('type(rows) : {}'.format(type(rows)))
print('rows : {}'.format(rows))
for row in rows:
    print('type(row) : {}'.format(type(row)))
    print('row : {}'.format(row))
conn.close()

結果輸出如下：

type(rows) : <class 'sqlite3.Cursor'>
rows : <sqlite3.Cursor object at 0x7fa31296d180>
type(row) : <class 'tuple'>
row : (1, '地平線 西域禁地', 'PS4,PS5', 1790)
type(row) : <class 'tuple'>
row : (2, '跑車浪漫旅 7', 'PS4,PS5', 1990)
type(row) : <class 'tuple'>
row : (3, '艾爾登法環', 'PS4,PS5,PC,XBOX', 1690)

將程式碼與結果合成，可以看得更清楚：

程式碼總共 9 行，第 2~3 行產生結果 rows ，第 4~5 行 print 檢視 rows 類型與內容，第 6 行 for 迴圈將 rows 逐筆挑出為 row，第 7~8 行 print 分別檢視 row 的類型與內容，最後一行關閉資料庫連線。

程式碼的基本邏輯看起來似乎沒什麼問題，也能跑出預期的結果。不過 rows 卻不是本以為的資料集合類型，而是「sqlite3.Cursor」類型，所以 print 內容也看不出所以然。透過 Debug 工具查看 rows 裡面到底長什麼樣子：

坦白說有點難懂，看起來直覺不太像是資料集合的樣子，不過神奇的是 for 迴圈仍能逐筆挑出資料，到底 for 了什麼原理實在無從得知，不得不對 python 的語法感到博大精深。雖然不太懂，但可看到上圖的 sqlite3.Cousor 中有兩個屬性 connection 與 description，後面會提到。

接下來要進入重點了。程式邏輯基本沒啥問題，但是在實務面上會遇到兩個大問題：關閉資料庫 conn.close() 的時機不對（太晚），以及取得的 row 資料於後續的應用不方便。

問題一：關閉資料庫連線的正確時機

以這次 Demo 的程式步驟來說，取得 rows 之後，再跑 for 迴圈逐筆處理每一筆資料，直到所有資料都處理完時才關閉。但實務應用時，for 迴圈可能會需要一段時間，例如挑出每一筆資料出來後網路爬蟲，到各大電商賣場搜尋遊戲資訊等等，這些動作肯定會花上不少時間，也意味著程式在這段時間內，會持續佔用著連線資源。以本機資料庫 SQLite 而言或許還可以接受，但若是異地端資料庫如 MySQL、MS SQL、Oracle 這類提供多人連線存取的資料庫服務，那麼只要多幾個這種長佔連線資源的用戶端，資料庫服務就很容易出現問題。

所以正確的方式是，用戶端一旦撈完資料時，資料庫端就得關閉。那好啊～很簡單啊～既然如此，直接修改程式，把 conn.close() 直接移到取得 rows 資料後，放在 for 迴圈之前，這樣總行了吧？ 

很遺憾，逮擠不是憨人想的那麼簡單。改完後程式直接報錯，如下：

原因在於 rows 並非單純的資料集合，而是 sqlite3.Cousor 類型的物件，裡面的 connection 屬性與資料庫連線是連動的，當連線關閉之後，rows 就不允許程式對內部的資料進行操作。

那麼，這個問題到底要怎麼解決？其實一般的網路教程，已經告訴你要使用 cursor 操作了，只是很少會告訴你為什麼要這麼做，上面 demo 報錯例子或許可以說明原因。

幾乎所有的網路教學，cursor 操作的範例都是下面的寫法：

這種寫法的概念是，橫空出世宣告一個 cursor 物件 c = conn.cursor() ，由 c.execute(...) 來替代本來的 conn.execute(...) 操作。cursor c 在 execute(...) 取得資料，再加上一個叫 fetchall() 的動作，將資料回傳給 rows 之後，就可以關閉資料庫連線了。

從上圖中也看到了，featchall() 之後回傳資料的 rows 也不再是 sqlite3.Cursor 類，而是標準的資料集合「list」類。用 print 檢視 rows 的內容是 3 筆 tuple 類型的遊戲資料，而這些遊戲資料後續再怎麼爬蟲應用、也與原來的資料庫無關，因為連線早就關閉了。

然而程式碼也因此增加 2 行，只為了特別生出一個 cursor 來解決連線問題，總覺得程式碼又變囉唆了，才撈個資料得需要這樣嗎？上面其實也提到，conn.execute(...) 回傳給 rows 時，就已經是  sqlite3.Cursor 類型的物件，因此程式碼其實可以這樣寫：

import sqlite3
conn = sqlite3.connect("demo.db")
rows = conn.execute("select * from games;").fetchall()
conn.close()
print('type(rows) : {}'.format(type(rows)))
print('rows : {}'.format(rows))
for row in rows:
    print('type(row) : {}'.format(type(row)))
    print('row : {}'.format(row))

執行的結果和上圖完全一樣，rows 是 list 類資料集合，包含 3 筆 tuple 類的遊戲資料。

寫到這裡，大家可以比較一下本文最原始的寫法，只是把 conn.close() 往前移到取得 rows 資料的下一行，並且在 conn.execute(...) 直接加上 .fetchall() 而已，總行數 9 行不變。或許大家也發現了，程式內並沒有特意提到 cursor 這個玩意，但實際上已經包含 cursor 的 fetchall() 操作。

這個寫法我反而覺得比較容易閱讀與撰寫，但奇怪的是，conn.execute(...).fetchall() 這種寫法，我幾乎沒在教學網站上看過，所以寫出來當筆記，放在這裡供將來複習，也當作給大家參考看看。

問題二：取得的 row 資料的後續應用不便

解決完 rows ，來看看裡面的每一筆 row 的內容。本文的 demo 為例，三筆資料都是 tuple 類型，內容如下：

(1, '地平線 西域禁地', 'PS4,PS5', 1790)
(2, '跑車浪漫旅 7', 'PS4,PS5', 1990)
(3, '艾爾登法環', 'PS4,PS5,PC,XBOX', 1690)

tuple 的結構簡單直觀粗暴，最大的問題在於它對每個值少了應有的描述，這裏 Demo 的每個 tuple 只有 4 個值，或許能用經驗法則猜出是可能是編號（ row[0] ）、遊戲名稱（ row[1]）、平台（ row[2]），價格（row[3]）。然而真整的實務經驗中，用以上的寫法撈出數十個、甚至上百個值的 tuple 資料很常見，光是想找出那個值在第幾個位置，整天在那裡算逗號格子就飽了，更不用說到 tuple 資料隨時都有可能增減、型態、順序變化等情況。特別是 demo 的例子中使用 "select * from games;" 語法時，更容易讓 tuple 內容發生非預期的變化，後續很容易導致其他問題。

提到「SELECT * FROM...」，其實是很不好的寫法。自己練習寫寫範例還可以，實務的開發經驗上千萬千萬不能使用，否則後果很可能不堪設想。我個人真的見過因為使用「SELECT * FROM...」而造成的史詩級災難，本文最後的「講古篇」再跟大家聊這個故事。

說了這麼多，我只是想表明 tuple 這類型的資料，可能不是什麼好用的資料結構，後續處理起來很麻煩。所以了，若真的不得不處理 tuple 時，拿到 tuple 資料的第一時間，最好再自行料理一下、例如另外寫一個 class 或是用 dict 加入可識別的 key 值，後續應用這些資料時會比較為妥當。

有鑑於 tuple 處理起來的不便，其實 Python 的 Sqlite3 有提供「row factory」的功能，在 SELECT 取出資料集的時候直接加工，不會直接硬生生吐出 tuple 資料。

而一般常見的網路教學也有教 row factory，只要多一行宣告就行了。

import sqlite3
conn = sqlite3.connect("demo.db")
conn.row_factory = sqlite3.Row
rows = conn.execute("select * from games;").fetchall()
conn.close()
print('type(rows) : {}'.format(type(rows)))
print('rows : {}'.format(rows))
for row in rows:
    print('type(row) : {}'.format(type(row)))
    print('row : {}'.format(row))

於是 row 從原來的 tuple 類型變成 sqlite3.Row 類型。

然而，直接 print 檢視 row 內容卻看不出所以然，到底 sqlite3.Row 類型的 row 要怎麼取出內容值呢？用 Debug 工具看一下：

sqlite3.Row 包含了一個 method 叫 keys，所以改寫一下看看：

列出 row 的所有 key 值。所以按照經驗法則，取值的方法可以寫成 row["key值"] 就行了。此 demo 中的所有 key 值，其實也是 games 表格的欄位值。

經 row_factory 處理後，取 row 的內容值就方便許多，只需填入對應正確的 key 值即可，和取用 dict 內容的方式一模一樣。

雖然取用 sqlite3.Row 的方式跟 dict 雷同，但說到底 sqlite3.Row 還是沒那麼直觀，你還得透過 keys() 查詢有哪些 key 值能用。那麼，到底有沒有更好的方式，直接把結果輸出為 dict 的資料集合呢？

查了一下，答案是有的。其實 conn.row_factory 本身就是個方法函數，我們也可以自己客製化，做一個 row_factory，官方的文件有教你怎麼寫（連結）。所以 demo 程式最終可以改寫成以下這樣：

import sqlite3
def dict_factory(cursor, row):
    d:dict = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
conn = sqlite3.connect("demo.db")
conn.row_factory = dict_factory
rows = conn.execute("select * from games;").fetchall()
conn.close()
print('type(rows) : {}'.format(type(rows)))
print('rows : {}'.format(rows))
for row in rows:
    print('type(row) : {}'.format(type(row)))
    print('row : {}'.format(row))

上面程式碼中的黃底區就是自製的 row factory，裡面用到了前面提到 cursor 的另一個屬性 description，執行結果如下：

如此，就可以把 rows 直接輸出為 dict 類的 list 資料集，有利於該資料後續的使用和轉換（例如 json）。和文章一開始的程式碼做比較，增加了 6 行，並解決關閉連線後無法存取資料的問題，並且讓資料庫異動產生的影響降到最低。

Python + MySQL 資料庫

額外補充 Python + MySQL 資料庫，SELECT 回應 dict 類 list 資料集的部分。有很多種寫法，幾乎都逃不開 cursor 的操作（conn 沒有 execute 方法，且 cursor.execute 回傳數字<資料筆數>），皆以 cursor.fetchall() 接收結果。例舉常見的兩種：

寫法一：使用 pymysql

import pymysql #pip3 install pymysql
conn = pymysql.connect(host="localhost", cursorclass=pymysql.cursors.DictCursor,
                       user="demo", password="demo", database="demo", port=3306, charset="utf8")
cursor = conn.cursor()
# 當 conn 未指定 cursorclass = ... 時，使用以下宣告
# cursor = conn.cursor(pymysql.cursors.DictCursor) 
cursor.execute("select * from games;")
rows = cursor.fetchall()
conn.close()
cursor.close()
print('type(rows) : {}'.format(type(rows)))
print('rows : {}'.format(rows))
for row in rows:
    print('type(row) : {}'.format(type(row)))
    print('row : {}'.format(row))

寫法二：使用 mysql.connector。這種寫法對於 VS Code 的 IntelliSense（Autocomplete）功能並不友善，所以不推薦。

import mysql.connector  # pip install mysql-connector-python
conn = mysql.connector.connect(host="localhost", user="demo",
                               passwd="demo", database="demo", port=3306, charset="utf8")
cursor = conn.cursor(dictionary=True)
cursor.execute("select * from games;")
rows = cursor.fetchall()
conn.close()
cursor.close()
print('type(rows) : {}'.format(type(rows)))
print('rows : {}'.format(rows))
for row in rows:
    print('type(row) : {}'.format(type(row)))
    print('row : {}'.format(row))

講古篇：「SELECT * FROM」造成史詩級災難

很多年前，南部某公務機關的公文系統，發生上萬筆公文資料不明遺失的事件。記得該系統是 D2K + Oracle 寫的，算是比較少見的那種，所以案件就輾轉到我這邊來了。我複製了一套系統做功能測試，發現是該系統中「歷史歸檔」的功能出現問題。要歸檔的公文沒有進入歷史欓，然後本來要歸檔的公文資料就被刪除了。繼續深入調查，我比對了公文檔與歷史檔，發現兩個資料表欄位各有一百多個，排列幾乎相同，唯一不同處在於公文檔尾端比歷史欓多了兩個欄位。當我把歷史檔也補上這兩個欄位後，歷史歸檔的功能就恢復正常了。

所以我的估計是，歷史歸檔程式一定是用了「INSERT INTO 歷史檔 SELECT * FROM 公文檔」這種語法。在兩個資料表 100% 相同欄位時是沒問題的，不過在某次系統改版，公文檔不明原因加了欄位後，這種寫法當然就出問題了，加上程式沒攔截錯誤就 DELETE 公文，於是慘劇就發生了。更慘的是備份機制也有問題，雖然修好了歷史歸檔功能，但那些上萬筆約一年多丟失的公文資料卻再也找不回來，真的是一場史詩級的災難。

後來聽說他們從倉庫裡搬出幾大箱紙本公文，每天找人加班把這些公文重新輸入系統，總算是解決了這件事。