萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Python 連接 MySQL 完整指南:mysqlclient、PyMySQL、SQLAlchemy 選型與避坑

驅動選型對比

驅動類型效能相容性推薦場景
mysqlclientC 擴展 (MySQL C API)⭐⭐⭐⭐⭐Django 官方推薦高效能、Django 專案、生產環境
PyMySQL純 Python⭐⭐⭐純 Python 環境、無編譯工具鏈開發測試、容器構建、無法編譯 C 擴展
mysql-connector-python官方純 Python / C 擴展⭐⭐⭐官方維護需官方支援的企業環境
asyncmyC 擴展 + AsyncIO⭐⭐⭐⭐⭐Python 3.7+FastAPI、Starlette 異步框架

安裝實戰

mysqlclient(推薦生產)

# Ubuntu/Debian
apt-get install -y default-libmysqlclient-dev build-essential pkg-config
pip install mysqlclient

# CentOS/RHEL
yum install -y mysql-devel gcc gcc-c++ python3-devel
pip install mysqlclient

# macOS
brew install mysql-client pkg-config
export PATH="/usr/local/opt/mysql-client/bin:$PATH"
pip install mysqlclient

# Windows
pip install mysqlclient  # 提供預編譯 wheel

PyMySQL(無編譯環境)

pip install pymysql
# 兼容 MySQLdb API
import pymysql
pymysql.install_as_MySQLdb()

SQLAlchemy + mysqlclient(ORM 標準)

pip install sqlalchemy mysqlclient
# 連線字串
DATABASE_URL = "mysql+mysqldb://user:pass@host:3306/db?charset=utf8mb4"
engine = create_engine(DATABASE_URL, pool_pre_ping=True, pool_recycle=3600)

常見安裝錯誤與解決

錯誤原因解決
mysql_config not found 缺少 MySQL 開發庫 安裝 libmysqlclient-dev / mysql-devel
library not found: libssl / libcrypto OpenSSL 開發庫缺失 安裝 libssl-dev / openssl-devel
ImportError: libmysqlclient.so.21: cannot open shared object file 運行時找不到共享庫 echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf && ldconfig
Authentication plugin 'caching_sha2_password' not supported 驅動版本過舊 升級 mysqlclient≥2.0 / PyMySQL≥1.0,或改用 mysql_native_password

連線池與最佳實踐

原生連線池

import mysql.connector.pooling
cnxpool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    host="localhost",
    user="app",
    password="pass",
    database="mydb",
    charset="utf8mb4",
    autocommit=True
)
cnx = cnxpool.get_connection()

SQLAlchemy 引擎池

engine = create_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,      # 檢測斷開連線
    pool_recycle=3600,       # 1小時回收,防 MySQL wait_timeout 斷開
    pool_timeout=30
)

CRUD 範例

# mysqlclient 參數化查詢(防注入)
conn = MySQLdb.connect(host='localhost', user='app', passwd='pass', db='mydb', charset='utf8mb4')
cur = conn.cursor()
cur.execute("SELECT id, name FROM users WHERE email = %s AND status = %s", ('test@example.com', 'active'))
rows = cur.fetchall()
for row in rows:
    print(row)

# 批次插入
cur.executemany("INSERT INTO logs (level, message) VALUES (%s, %s)", 
                [('INFO', 'start'), ('ERROR', 'failed')])
conn.commit()

異步操作

# asyncmy + asyncio
import asyncmy
pool = await asyncmy.create_pool(host='localhost', user='app', password='pass', db='mydb', minsize=5, maxsize=20)
async with pool.acquire() as conn:
    async with conn.cursor() as cur:
        await cur.execute("SELECT 1")
        result = await cur.fetchall()

總結

生產環境首選 mysqlclient + SQLAlchemy**,效能最佳、生態成熟。無編譯環境用 PyMySQL。異步框架用 asyncmy。關鍵配置:charset=utf8mb4、pool_pre_ping=True、pool_recycle 防斷連。安裝失敗 90% 因缺少 MySQL 開發庫與 OpenSSL 庫,補齊即可。

copyright © 萬盛學電腦網 all rights reserved