用 Python 连 MySQL 时,安全第一原则是:永远不要把用户输入拼进 SQL 字符串。本文给出 PyMySQL 与 SQLAlchemy 的正确姿势。
PyMySQL 参数化查询
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="app",
password="***",
database="demo",
charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor,
)
def get_user(user_id: int):
sql = "SELECT id, name, email FROM users WHERE id=%s"
with conn.cursor() as cur:
cur.execute(sql, (user_id,))
return cur.fetchone()
# 错误示范:f-string 拼接,有 SQL 注入风险
# cur.execute(f"SELECT * FROM users WHERE id={user_id}")
批量插入
rows = [("Alice", "a@x.com"), ("Bob", "b@x.com")]
sql = "INSERT INTO users(name, email) VALUES (%s, %s)"
with conn.cursor() as cur:
cur.executemany(sql, rows)
conn.commit()
SQLAlchemy 核心写法(简版)
from sqlalchemy import create_engine, text
engine = create_engine("mysql+pymysql://app:***@127.0.0.1:3306/demo?charset=utf8mb4")
with engine.begin() as conn:
result = conn.execute(
text("SELECT * FROM users WHERE city=:city"),
{"city": "北京"},
)
for row in result.mappings():
print(row)
工程实践清单
- 连接信息放环境变量,不写进仓库
- 生产库账号最小权限(不要随便用 root)
- 读写超时、连接池、失败重试要有
- 变更数据必须事务:成功
commit,失败rollback