Python 資料庫操作：深入了解 `sqlite3` 模組

簡介

在 Python 開發中，資料庫是儲存與查詢資料的核心工具。即使是小型專案或原型開發，常常也需要一個輕量、零設定、跨平台的資料庫，而 SQLite 正是最合適的選擇。Python 標準函式庫自帶 sqlite3 模組，讓我們可以直接在程式碼中建立、操作 SQLite 資料庫，無需額外安裝第三方套件。

使用 sqlite3 的好處包括：

純檔案儲存：資料庫只是一個 .db 檔案，搬移、備份都非常簡單。
零伺服器需求：不需要安裝或管理資料庫伺服器，適合桌面程式、腳本、測試環境。
完整的 SQL 支援：支援大部分 ANSI‑SQL 語法，讓你可以使用熟悉的查詢方式。

本篇文章將從 核心概念、實作範例、常見陷阱與最佳實踐，一路帶你掌握 sqlite3 的使用方法，並提供 實務應用場景，讓你在開發過程中即能上手、快速產出可靠的資料存取程式碼。

核心概念

1. 建立與連線資料庫

sqlite3.connect() 會自動建立檔案（若不存在）並回傳一個 Connection 物件。

import sqlite3

# 若 test.db 不存在會自動建立
conn = sqlite3.connect('test.db')

Tip：若想在記憶體中建立暫時資料庫，可使用 sqlite3.connect(':memory:')。

2. 執行 SQL 語句

透過 Connection 產生 Cursor，再呼叫 execute()、executemany()、executescript() 等方法執行 SQL。

cur = conn.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')

3. 參數化查詢（防止 SQL Injection）

使用 ? 佔位符，並以 tuple 或 list 提供參數。

cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))

4. 交易（Transaction）

SQLite 會自動將每一次 execute() 包在一個交易中，若想手動控制，可使用 conn.commit() 與 conn.rollback()。

try:
    conn.execute('BEGIN')
    cur.execute('UPDATE users SET age = age + 1 WHERE name = ?', ('Bob',))
    conn.commit()
except Exception:
    conn.rollback()
    raise

5. 取得查詢結果

fetchone()、fetchall()、fetchmany() 用於讀取結果列。

cur.execute('SELECT * FROM users WHERE age > ?', (25,))
rows = cur.fetchall()      # 取得所有符合條件的列
for row in rows:
    print(row)            # (id, name, age)

程式碼範例

以下示範 5 個在日常開發中最常用的 sqlite3 操作，並附上說明與註解。

範例 1：建立資料表與插入多筆資料

import sqlite3

def init_db(db_path='sample.db'):
    """建立資料表並一次插入多筆資料"""
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    # 建立 users 表格
    cur.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id   INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age  INTEGER
        )
    ''')

    # 使用 executemany 批次插入
    users = [
        ('Tom',   28),
        ('Jerry', 22),
        ('Anna',  35),
    ]
    cur.executemany('INSERT INTO users (name, age) VALUES (?, ?)', users)

    conn.commit()
    conn.close()

if __name__ == '__main__':
    init_db()

說明：executemany 能有效減少大量 INSERT 時的 I/O 開銷。

範例 2：查詢與條件過濾

import sqlite3

def query_by_age(min_age, db_path='sample.db'):
    """取得年齡大於等於 min_age 的使用者列表"""
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    cur.execute('SELECT id, name, age FROM users WHERE age >= ?', (min_age,))
    for user in cur.fetchall():
        print(f'ID:{user[0]}  Name:{user[1]}  Age:{user[2]}')

    conn.close()

query_by_age(30)

技巧：使用 f-string 讓輸出更易讀。

範例 3：更新與刪除資料

import sqlite3

def update_user_name(user_id, new_name, db_path='sample.db'):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute('UPDATE users SET name = ? WHERE id = ?', (new_name, user_id))
    conn.commit()
    conn.close()

def delete_user(user_id, db_path='sample.db'):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute('DELETE FROM users WHERE id = ?', (user_id,))
    conn.commit()
    conn.close()

# 範例呼叫
update_user_name(1, 'Thomas')
delete_user(2)

提醒：更新或刪除後一定要 commit()，否則變更不會寫入磁碟。

範例 4：使用 `with` 管理連線（自動關閉）

import sqlite3

def count_users(db_path='sample.db'):
    """利用 with 語法自動釋放資源，回傳使用者總筆數"""
    with sqlite3.connect(db_path) as conn:
        cur = conn.cursor()
        cur.execute('SELECT COUNT(*) FROM users')
        (total,) = cur.fetchone()
        return total

print('使用者總數：', count_users())

優點：with 會在程式區塊結束時自動呼叫 conn.close()，減少資源洩漏的風險。

範例 5：自訂 Row Factory（讓結果以字典形式返回）

import sqlite3

def fetch_as_dict(db_path='sample.db'):
    """設定 row_factory，讓每筆結果以 dict 形式呈現"""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row   # 重要設定
    cur = conn.cursor()
    cur.execute('SELECT * FROM users')
    rows = cur.fetchall()
    for row in rows:
        # Row 物件可像 dict 取值
        print(dict(row))

    conn.close()

fetch_as_dict()

好處：在大型程式中，以欄位名稱存取資料比使用索引更具可讀性。

常見陷阱與最佳實踐

常見問題	可能原因	建議的解決方式
資料庫檔案被鎖定	多個執行緒或程式同時寫入同一個 `.db` 檔案	使用 SQLite 的 WAL 模式（`PRAGMA journal_mode=WAL;`）或在多執行緒環境下使用 `check_same_thread=False` 並自行管理鎖定
忘記 `commit()`	執行 INSERT/UPDATE/DELETE 後未提交	養成在每次修改後呼叫 `conn.commit()`，或使用 `with conn:` 讓 Python 自動提交
SQL Injection	直接把字串拼接成 SQL 語句	永遠使用參數化查詢 (`?` 佔位符)；不要使用 `%` 或 `format` 直接拼接
資料類型不一致	SQLite 是弱型別，傳入 Python `bool`、`datetime` 可能被轉成 0/1 或文字	使用適當的轉換（如 `datetime.isoformat()`）或自訂 adapter / converter
大量寫入效能低	每筆 INSERT 都自動提交	批次寫入（`executemany`）或手動包在一個交易 (`BEGIN` … `COMMIT`) 中

最佳實踐：

使用 with 管理連線，確保即使發生例外也會正確關閉。
設定 row_factory 為 sqlite3.Row，提升程式可讀性。
啟用 WAL 模式（寫入前日誌）以改善多執行緒/多進程的併發效能。
建立索引（CREATE INDEX）在搜尋頻繁的欄位上，以降低查詢時間。
定期備份：使用 sqlite3.backup() 或直接複製 .db 檔案，避免資料遺失。

實際應用場景

場景	為何選擇 SQLite + `sqlite3`
桌面應用程式（如筆記軟體、個人理財）	輕量、免安裝、跨平台，資料存放於使用者本機。
測試環境或原型開發	快速建立臨時資料庫，無需部署正式的 MySQL / PostgreSQL 伺服器。
IoT 裝置（樹莓派、嵌入式系統）	佔用資源極低，適合資源受限的設備。
小型網站或部落格	當流量不大時，可直接用 SQLite 作為後端資料庫，降低維運成本。
資料分析前的臨時儲存	讀取 CSV/JSON 後寫入 SQLite，方便使用 SQL 進行過濾、聚合，再匯出結果。

舉例：一個 Python Flask 部落格專案，僅有幾千篇文章與使用者評論，使用 SQLite 作為資料庫即可滿足需求，且部署只需要把 .db 檔案放到伺服器上即可。

總結

sqlite3 是 Python 標準函式庫中最實用的資料庫介面之一，零安裝、單檔案、完整 SQL 支援 的特性讓它成為從學習階段到小型產品的理想選擇。本文透過 概念說明、五個實作範例、常見陷阱與最佳實踐，以及 實務應用場景，希望讀者能：

快速上手建立、查詢、更新與刪除資料。
正確使用參數化查詢與交易機制，寫出安全且可靠的程式。
在日常開發中善用 with、row_factory、WAL 等技巧，提高程式可讀性與效能。

掌握了 sqlite3 後，你將能在 桌面、Web、IoT 等多種環境中，輕鬆完成資料持久化的需求，為未來更複雜的資料庫（如 PostgreSQL、MySQL）奠定堅實基礎。祝你開發順利，資料永遠不會「丟」！

Python 資料庫操作：深入了解 sqlite3 模組

簡介