| 驅動 | 特點 | 適用場景 |
| mysqlclient | C 擴展、效能最佳、MySQL 官方推薦、支援 Python 3.7+ | 生產環境、高併發、同步框架 (Django, Flask) |
| PyMySQL | 純 Python、相容性好、無需編譯、效能稍遜 | 無編譯環境、跨平台、異步框架配合 aiomysql |
| asyncmy | 純 Python 異步、效能接近 mysqlclient | FastAPI、Starlette 等異步框架 |
安裝 mysqlclient
# Ubuntu/Debian
sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
pip install mysqlclient
# CentOS/RHEL
sudo yum install python3-devel mysql-devel gcc
pip install mysqlclient
# Windows
pip install mysqlclient # 提供預編譯 wheel
基本 CRUD 操作
import MySQLdb
conn = MySQLdb.connect(
host="localhost",
user="root",
passwd="password",
db="test_db",
charset="utf8mb4", # 完整 Unicode 支援
autocommit=False # 手動控制交易
)
cursor = conn.cursor()
# 建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
# 參數化插入 (防 SQL 注入!)
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
cursor.execute(sql, ("Alice", "alice@example.com"))
cursor.execute(sql, ("Bob", "bob@example.com"))
conn.commit()
print(f"插入 {cursor.rowcount} 筆,最後 ID: {cursor.lastrowid}")
# 查詢
cursor.execute("SELECT id, name, email FROM users WHERE id > %s", (0,))
rows = cursor.fetchall()
for row in rows:
print(row) # (1, 'Alice', 'alice@example.com')
# 更新
cursor.execute("UPDATE users SET name=%s WHERE id=%s", ("Alice Updated", 1))
conn.commit()
# 刪除
cursor.execute("DELETE FROM users WHERE id=%s", (2,))
conn.commit()
cursor.close()
conn.close()
DictCursor:返回字典而非元組
import MySQLdb.cursors
conn = MySQLdb.connect(..., cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
print(row['name'], row['email'])
交易控制
try:
cursor.execute("INSERT INTO accounts (user_id, amount) VALUES (%s, %s)", (1, -100))
cursor.execute("INSERT INTO accounts (user_id, amount) VALUES (%s, %s)", (2, 100))
conn.commit()
except Exception as e:
conn.rollback()
raise
finally:
cursor.close()
conn.close()
或使用上下文管理器 (Python 3.7+):
with MySQLdb.connect(...) as conn:
with conn.cursor() as cursor:
cursor.execute(...)
conn.commit() # 無異常自動 commit,有異常自動 rollback
連線池:生產環境必備
from DBUtils.PooledDB import PooledDB
import MySQLdb
pool = PooledDB(
creator=MySQLdb,
maxconnections=20,
mincached=5,
maxcached=10,
blocking=True,
host="localhost",
user="root",
passwd="password",
db="test_db",
charset="utf8mb4"
)
# 取得連線
conn = pool.connection()
try:
with conn.cursor() as cursor:
cursor.execute("SELECT 1")
print(cursor.fetchone())
finally:
conn.close() # 歸還連線池,非真正關閉
SQLAlchemy Core / ORM 整合
from sqlalchemy import create_engine, Column, Integer, String, Text
from sqlalchemy.orm import declarative_base, sessionmaker
engine = create_engine(
"mysql+mysqlclient://root:password@localhost/test_db?charset=utf8mb4",
pool_size=10,
max_overflow=20,
pool_pre_ping=True # 檢測斷線自動重連
)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False)
email = Column(String(100), unique=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# ORM 操作
user = User(name="Charlie", email="charlie@example.com")
session.add(user)
session.commit()
users = session.query(User).filter(User.name.like('%Ali%')).all()
for u in users:
print(u.name, u.email)
異步支援 (FastAPI + asyncmy)
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
async_engine = create_async_engine(
"mysql+asyncmy://root:password@localhost/test_db?charset=utf8mb4",
pool_size=10,
max_overflow=20
)
AsyncSessionLocal = sessionmaker(async_engine, class_=AsyncSession, expire_on_commit=False)
async def get_user(user_id: int):
async with AsyncSessionLocal() as session:
result = await session.execute(select(User).where(User.id == user_id))
return result.scalar_one_or_none()
安全與最佳實踐清單
- ✅ **永遠使用參數化查詢** (`%s` 佔位符),絕不拼接 SQL 字串。
- ✅ 使用 `utf8mb4` 而非 `utf8` (支援 emoji、完整 Unicode)。
- ✅ 生產環境**必須使用連線池**,避免頻繁建立/銷毀連線開銷。
- ✅ 設定 `pool_pre_ping=True` (SQLAlchemy) 或 `ping=True` (PooledDB) 自動處理斷線。
- ✅ 敏感資訊 (密碼) 從環境變數/密鑰管理系統讀取,不寫入程式碼。
- ✅ 適當設定 `connect_timeout`、`read_timeout`、`write_timeout` 避免卡死。
- ✅ 大量寫入使用 `executemany` 或批次 `INSERT ... VALUES (...), (...), ...`。
- ✅ 索引設計:WHERE、JOIN、ORDER BY 欄位建立索引,避免全表掃描。
- ✅ 監控慢查詢:`long_query_time` + `slow_query_log`。
copyright ©
萬盛學電腦網 all rights reserved