萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

Python 連接 MySQL 完整指南:mysqlclient、連線池、ORM 與最佳實踐

Python 連接 MySQL 是後端開發核心技能。本文涵蓋底層驅動 mysqlclient、連線池管理、主流 ORM (SQLAlchemy、Tortoise、Django ORM) 整合、參數化查詢防注入、交易控制與生產環境最佳實踐。

驅動選擇

安裝 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
驅動特點適用場景
mysqlclientC 擴展、效能最佳、MySQL 官方推薦、支援 Python 3.7+生產環境、高併發、同步框架 (Django, Flask)
PyMySQL純 Python、相容性好、無需編譯、效能稍遜無編譯環境、跨平台、異步框架配合 aiomysql
asyncmy純 Python 異步、效能接近 mysqlclientFastAPI、Starlette 等異步框架