您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

Python MySQLdb 完整使用指南：連線、CRUD、事務、編碼與 DictCursor

編輯：MySQL 綜合教學

Python MySQLdb 完整使用指南

MySQLdb（即 mysqlclient）是 Python 連接 MySQL 最經典、效能最好的驅動之一。本文從安裝連線、游標操作、參數化查詢、事務控制、批次操作、編碼防亂碼到 DictCursor 字典式結果集，提供可直接落地的完整實戰範例。

一、環境準備與安裝

# Ubuntu/Debian
sudo apt-get install python3-dev default-libmysqlclient-dev
pip install mysqlclient

# CentOS/RHEL
sudo yum install python3-devel mysql-devel
pip install mysqlclient

# Windows
pip install mysqlclient  # 已提供 wheel，無需編譯

二、建立連線

import MySQLdb

conn = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="your_password",
    db="your_database",
    charset="utf8mb4",          # 推薦 utf8mb4 支援 emoji
    port=3306,
    cursorclass=MySQLdb.cursors.DictCursor  # 直接回傳字典，省去轉換
)

常用連線參數

參數	說明	預設值
host	資料庫主機	localhost
user	登入使用者	當前系統使用者
passwd	密碼	空字串
db	預設資料庫	無（必填）
port	TCP 連接埠	3306
charset	連線字元集	latin1
cursorclass	游標類別	Cursor（元組）
autocommit	自動提交	False

三、游標操作核心 API

cursor = conn.cursor()

執行指令類

方法	用途	回傳值
`execute(query, args)`	執行單條 SQL	影響行數
`executemany(query, args_list)`	批次執行同一 SQL，參數列表為可迭代物件	影響行數
`callproc(procname, args)`	呼叫預存程序	影響行數
`nextset()`	移動到下一個結果集	Boolean

取得結果類

方法	說明
`fetchall()`	取得所有結果行（列表/元組）
`fetchmany(size)`	取得指定行數，預設 `cursor.arraysize`
`fetchone()`	取得單行
`scroll(value, mode='relative')`	移動游標指標

四、參數化查詢：防 SQL 注入的鐵律

# 佔位符永遠用 %s，無論資料型別
sql = "INSERT INTO cdinfo (title, singer, imgurl, url, alpha) VALUES (%s, %s, %s, %s, %s)"
param = (title, singer, imgurl, url, alpha)  # tuple 或 list

n = cursor.execute(sql, param)  # 成功回傳 1

# 批次插入
params = [
    (title1, singer1, imgurl1, url1, alpha1),
    (title2, singer2, imgurl2, url2, alpha2),
]
n = cursor.executemany(sql, params)  # 回傳總影響行數

核心規則：

佔位符永遠用 %s，不要用 %d、%f 或字串格式化。
參數必須為 tuple 或 list，單參數時寫 (val,) 保留逗號。
驅動會自動處理轉義、型別轉換與引號包裹。

五、事務控制

try:
    cursor.execute("INSERT INTO accounts (user, amount) VALUES ('alice', 100)")
    cursor.execute("INSERT INTO accounts (user, amount) VALUES ('bob', -100)")
    conn.commit()  # 顯式提交
except Exception as e:
    conn.rollback()  # 回滾
    raise
finally:
    cursor.close()
    conn.close()

關鍵點：

InnoDB 預設開啟事務，必須 commit() 才會持久化。
commit() 可批次多條 SQL 後再呼叫，提升效能。
rollback() 僅能回滾未提交的變更，commit() 後無法回滾。

六、DictCursor：字典式結果集

預設回傳 tuple，需靠索引存取（row[0]），易出錯且可讀性差。兩種啟用方式：

# 方式 1：連線時指定
conn = MySQLdb.connect(..., cursorclass=MySQLdb.cursors.DictCursor)

# 方式 2：建立游標時指定
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

結果對比：

# Tuple 模式
('ccc', 33)
('ddd', 44)

# DictCursor 模式
{'name': 'ccc', 'created': 33}
{'name': 'ddd', 'created': 44}

七、完整實戰範例（含 CRUD、批次、編碼）

# -*- coding: utf-8 -*-
import time
import MySQLdb

# 1. 連線（指定 utf8mb4 + DictCursor）
conn = MySQLdb.connect(
    host="localhost", user="root", passwd="root",
    db="test", charset="utf8mb4",
    cursorclass=MySQLdb.cursors.DictCursor
)
cursor = conn.cursor()

# 2. 建表
cursor.execute("DROP TABLE IF EXISTS user")
cursor.execute("""
    CREATE TABLE IF NOT EXISTS user (
        name VARCHAR(128) PRIMARY KEY,
        created INT(10)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")

# 3. 單筆插入
sql = "INSERT INTO user (name, created) VALUES (%s, %s)"
cursor.execute(sql, ("aaa", int(time.time())))

# 4. 批次插入
cursor.executemany(sql, [("bbb", int(time.time())), ("ccc", 33), ("ddd", 44)])

# 5. 更新
cursor.execute("UPDATE user SET name=%s WHERE name='aaa'", ("zzz",))

# 6. 查詢
cursor.execute("SELECT * FROM user")
for row in cursor.fetchall():
    print(row)           # {'name': 'zzz', 'created': 1234567890}
    print(row['name'])   # 直接用欄位名存取

# 7. 刪除
cursor.execute("DELETE FROM user WHERE name=%s", ("bbb",))

# 8. 提交與關閉
conn.commit()
cursor.close()
conn.close()

八、編碼防亂碼四重保險

Python 檔案編碼：檔案首行加 # -*- coding: utf-8 -*-。
資料庫/表/欄位字元集：CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci。
連線參數：charset="utf8mb4"（注意：MySQLdb 參數用 utf8mb4，而非 utf-8）。
Python 執行環境（Python 2 專用，Python 3 已預設 Unicode）：
```
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
```

九、常見坑與避雷指南

坑點	症狀	解決
`executemany` 資料過大	Server 端 buffer 溢出、封包過大錯誤	分批次執行，每批 1000~5000 行
游標未關閉	連線洩漏、`Too many connections`	使用 `with` 上下文管理或 `finally` 確實關閉
自動提交關閉	資料寫入後查不到	記得 `conn.commit()` 或設 `autocommit=True`
字元集不一致	中文亂碼、Emoji 存成 ????	全鏈路 `utf8mb4`

掌握 MySQLdb 核心 API 與最佳實踐，能讓 Python 操作 MySQL 既安全又高效。建議新專案優先考慮 mysqlclient（MySQLdb 分支）或 PyMySQL（純 Python 實現，相容性佳），並搭配連線池（如 DBUtils、SQLAlchemy engine）實現生產級連線管理。