MariaDB 教程 / Python 连接、参数化查询与事务处理
Python 连接 MariaDB 时,应通过驱动 API 分开传递 SQL 文本与输入值,避免把用户内容拼接成 SQL。参数化查询负责值的安全处理,最小权限账户与明确的 commit、rollback 则控制可以执行的操作和数据变更边界。
准备 Python 环境与受限应用账户
本例使用 Python 3、PyMySQL 1.1.2 和本机 MariaDB TCP 连接。PyMySQL 是 Python 数据库驱动,本篇采用它的 %s 值占位符,不能直接换成其他驱动的问号写法。示例核心行为的验证环境为 Python 3.12 与 MariaDB 12.3.3;容器网络、远程 TLS 或其他驱动需要分别核对。
先用管理员连接检查 appops_lab、app_notes 及 'appops_app'@'127.0.0.1' 是否已被使用。已有数据库应检查归属和结构,已有账户应检查授权,不要盲目重建。把公开占位密码替换为自己的练习密码后,在 MariaDB 客户端执行完整设置:
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';
管理员建表与应用读写是不同职责,参见账户与最小权限教程。本例账户只获本表 SELECT、INSERT,不需要给应用建表或删表权限。
账户来源 127.0.0.1 假设本机原生服务器的 TCP 连接。容器和远程服务器看到的客户端来源可能不同,应按账户匹配与连接路径确认,而不是通过允许任意来源的管理员访问消除错误。
建立独立项目并保存完整程序
创建新的项目目录,在其中运行 Windows PowerShell 命令。直接调用虚拟环境 Python,不必修改 PowerShell 激活脚本执行策略:
python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2
Linux 或 macOS 使用对应的 .venv/bin/python 路径。将下面完整程序保存为 UTF-8 的 notes_app.py。程序运行时通过 getpass 提示读取密码,不把密码写在源代码、SQL 文本或命令参数中。
import argparse
import getpass
import os
import pymysql
def save_and_find(conn, name, sort_key="id"):
if not name or len(name) > 80:
raise ValueError("Name must contain 1 to 80 characters")
columns = {"id": "note_id", "name": "note_name, note_id"}
if sort_key not in columns:
raise ValueError("Unknown sort key")
with conn.cursor() as cur:
cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
cur.execute(
"SELECT note_id, note_name FROM app_notes "
"WHERE note_name = %s ORDER BY " + columns[sort_key],
(name,),
)
return cur.fetchall()
def main():
parser = argparse.ArgumentParser()
parser.add_argument("--name", default="O'Reilly")
parser.add_argument("--sort", choices=("id", "name"), default="id")
args = parser.parse_args()
password = getpass.getpass("Database password: ")
conn = pymysql.connect(
host=os.getenv("DB_HOST", "127.0.0.1"),
port=int(os.getenv("DB_PORT", "3306")),
user=os.getenv("DB_USER", "appops_app"),
password=password,
database="appops_lab",
charset="utf8mb4", autocommit=False,
connect_timeout=5, read_timeout=10, write_timeout=10,
)
try:
rows = save_and_find(conn, args.name, args.sort)
conn.commit()
for note_id, note_name in rows:
print(note_id, note_name)
except Exception:
conn.rollback()
raise
finally:
conn.close()
if __name__ == "__main__":
main()
连接设置指定 utf8mb4,以及连接、读取、写入超时。这个完整程序针对本机学习连接;跨网络连接还需要验证服务器身份的 TLS 配置,密码认证本身不会保护传输链路。不要直接把本机示例当成远程部署配置。
分别运行普通名称与带单引号的名称
.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name
两条命令是两次独立程序运行,每次在提示处输入数据库密码。第一次保存 Notebook,第二次保存包含单引号的 O'Reilly,再查询相同名称。全新表中各运行一次时,每次应得到相应的一条结果;不应要求固定自增编号,旧数据和回滚插入都可能影响编号。
再次使用相同名称会新增一行,并返回所有匹配该名称的记录,本例没有名称唯一约束。如果业务要求去重,应先定义身份规则并设置合适约束,不能认为程序已经自动保证唯一。
若输出不存在或重复数量不符,先确认使用同一虚拟环境、连接同一服务器与数据库,并检查是否发生异常和回滚;不要只按输出编号判断成功。
区分值参数、SQL 结构与标识符白名单
关键调用是 cur.execute(sql, (name,)),SQL 和参数元组作为两个独立参数传递;末尾逗号表示单元素元组。不要手动给 %s 再加引号,也不要用 Python 的 %、f-string 或字符串拼接把用户值插入 SQL。PyMySQL 官方示例采用同样的接口分离方式。
例如 x' OR 1=1 -- 必须作为一个完整名称存储并按相同字符串匹配,不能变成返回全表的查询条件。这里“参数化”描述驱动 API 的正确使用,不表示所有驱动都采用服务器端预处理语句协议。
列名、表名、ASC、DESC 等 SQL 结构不能当作普通值参数替换。程序的 ORDER BY 片段仅来自开发者预先写好的 columns 映射;argparse 和函数本身都验证排序选项,避免另一个调用方绕过白名单。参数形式可查阅 Cursor.execute 文档。
一起验证事务、异常处理与最小权限
autocommit=False 时,辅助函数返回查询结果并不代表插入已经持久化。main 在插入和查询均成功后提交;发生异常则回滚,再向调用方抛出错误;finally 关闭连接。应结合事务与其他连接可见性理解“本连接已查到”与“已提交可被其他连接查到”的区别。
一个有效的失败检查是:插入本来允许,但随后查询被拒绝时,整个尝试不应留下此前的新行,连接也应关闭。不能因为 INSERT 执行过就跳过后续异常处理;实际应用还应根据错误分类决定是否重试,避免不明确的重复写入。
应用只有 SELECT 和 INSERT,未获 CREATE、DROP、UPDATE。最小权限不能替代正确参数绑定,但能限制程序缺陷所能触及的操作。连接选项文档说明了 autocommit、超时与 TLS 参数。名称空值、超过 80 字符或未知排序键也应在执行插入前拒绝。
按失败阶段诊断,并清理练习对象
| 症状 | 检查内容 |
|---|---|
| ModuleNotFoundError | 是否用安装 PyMySQL 的同一个 .venv Python 运行 |
| Access denied | 用户名、密码、账户主机部分与服务器看到的客户端来源 |
| 拒绝连接或超时 | 服务器状态、DB_HOST、DB_PORT、防火墙与路径 |
| 处理后没有行 | 异常是否回滚,是否查询了正确且已提交的数据库 |
| 只有单引号输入失败 | 是否手工拼接用户值,而非传给 execute |
非机密连接选项可通过环境变量覆盖,例如 PowerShell 中 $env:DB_PORT = "3307",密码仍使用交互提示。临时端口覆盖不再需要时,用 Remove-Item Env:DB_PORT 移除,避免后续误连其他服务器。
结束时先停止程序,管理员确认账户与表确为本篇可丢弃对象,备份需要保留的内容后再清理。不要代入业务账户或表名:
DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;
DROP TABLE 和账户删除不能通过普通 ROLLBACK 恢复;应用账户退出与数据库对象清理应分别核对。




