Python 教程 / 连接 MariaDB 并用 PyMySQL 管理事务

Python 通过数据库驱动连接 MariaDB,执行 SQL 时把值与语句文本分开传递。本课使用 PyMySQL,需要已经运行的 MariaDB 服务器、独立练习数据库和权限有限的应用账号,与不需要服务器的 SQLite 不同。

准备独立服务器与应用账号

先准备可访问的本机 MariaDB 实例,再通过管理员连接执行下面 SQL。如果同名数据库或账号已经存在,先核对用途并改用新的练习名称,不要删除或覆盖未知对象。示例密码必须替换为自己生成的本机秘密,应用不能直接使用管理员账号。

CREATE DATABASE python_lab CHARACTER SET utf8mb4;
CREATE USER 'python_app'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-local-secret';
CREATE TABLE python_lab.expenses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  external_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  amount BIGINT NOT NULL CHECK(amount >= 0)
) ENGINE=InnoDB;
GRANT SELECT, INSERT, UPDATE ON python_lab.expenses TO 'python_app'@'127.0.0.1';

InnoDB 支持这里需要的事务。应用只得到 expenses 表的 SELECT、INSERT、UPDATE,没有授予全服务器权限,也不允许本例账号删除数据。设置完成后,用 python_app 进行 Python 连接;管理员连接只用于准备和管理练习环境。

金额采用整韩元,表中 BIGINT 与非负 CHECK 限制存储。本例没有用 Python 完整验证所有业务字段或日期,也不应把数据库非负约束当成适用于任意金额精度的政策。参数与提交接口参见PyMySQL 官方示例。

安装驱动并传递秘密值

使用 Python 3.12 以上项目环境,安装第三方驱动:

python -m pip install PyMySQL

程序从当前进程环境读取 MARIADB_USER 和 MARIADB_PASSWORD。不要把真实密码写入源码、版本库或日志。直接在 shell 命令文字中输入密码也可能进入历史记录;环境变量会传给子进程,本身并不提供专用秘密存储的全部保护。

示例连接 TCP 地址 127.0.0.1,账号 host 与 SQL 中的 127.0.0.1 要相符,不要随意换成 localhost 而假设连接方式相同。默认端口是 3306;独立实例使用其他端口时,在当前会话额外设置 MARIADB_PORT 为实际端口,不能为方便而停止无关服务。

先将下一节完整程序保存为 mariadb_demo.py,再在 Windows PowerShell 的同一练习目录中按下列方式隐藏输入密码。执行后移除当前会话环境值和临时变量:

$env:MARIADB_USER = "python_app"
$secret = Read-Host "MariaDB password" -AsSecureString
$credential = New-Object System.Net.NetworkCredential("", $secret)
$env:MARIADB_PASSWORD = $credential.Password
python mariadb_demo.py
Remove-Item Env:MARIADB_PASSWORD
Remove-Item Env:MARIADB_USER
Remove-Variable secret, credential

macOS 或 Ubuntu 的 Bash 可使用:

export MARIADB_USER=python_app
read -r -s -p "MariaDB password: " MARIADB_PASSWORD
printf "\n"
export MARIADB_PASSWORD
python mariadb_demo.py
unset MARIADB_PASSWORD MARIADB_USER

如果已经确认解释器命令是 python3,就相应替换上述执行行。正常结果为 ('lunch', 8000)。这些片段只把环境设置应用于当前会话,不替你配置 MariaDB 服务,也不会为远程连接自动启用 TLS。

远程服务器应依据服务器与驱动配置 CA 验证和主机名验证的 TLS,不能仅把 host 改成公网地址就认为保留了本地条件。连接参数见PyMySQL 官方连接选项。

运行完整连接与保存代码

在独立练习目录把以下全部内容以 UTF-8 保存为 mariadb_demo.py。若你在准备阶段改变了数据库或账号名称,SQL、连接配置和环境值要一致:

import os
import pymysql

con = pymysql.connect(
    host="127.0.0.1", port=int(os.environ.get("MARIADB_PORT", "3306")),
    user=os.environ["MARIADB_USER"], password=os.environ["MARIADB_PASSWORD"],
    database="python_lab", charset="utf8mb4", autocommit=False,
    connect_timeout=5, read_timeout=5, write_timeout=5,
)
try:
    try:
        with con.cursor() as cur:
            cur.execute("INSERT INTO expenses(external_id,name,amount) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name), amount=VALUES(amount)", ("demo-1", "lunch", 8000))
        con.commit()
    except Exception:
        con.rollback()
        raise
    with con.cursor() as cur:
        cur.execute("SELECT name,amount FROM expenses WHERE external_id=%s", ("demo-1",))
        print(cur.fetchone())
finally:
    con.close()

环境变量已设置后,可以在同一终端执行:

python mariadb_demo.py

正常输出为 ('lunch', 8000)。external_id 的唯一性使相同 demo-1 在重跑时更新名称与金额,而不是不断新增交易;AUTO_INCREMENT 的内部 ID 不应用来判断此次是否新建了一笔业务交易。

PyMySQL 用 %s 绑定数字与字符串值,execute 的第二个参数提供实际元组,不是 Python 的 % 字符串插值,也不是 f-string。表名、列名属于 SQL 结构,不能当成同样的值占位符,需要动态选择时使用受控名单。

autocommit=False 表示写入需要明确 commit。内部 try 发生写入或提交异常时尝试 rollback,再重新抛出;finally 关闭连接。cursor 的上下文管理游标,不等于自动提交事务。

连接本身失败时,还没有可用的 con,所以不会进入后面的清理区块。另一个重要边界是:写入已提交后,如果后续 SELECT 或输出失败,数据库提交不会因此自动撤销。诊断时应查询真实存储状态,不能看见错误就盲目再次执行有副作用的操作。

验证更新、回滚与账号权限

  1. 使用原始程序运行两次,查询确认 demo-1 仍只有一条记录,金额为 8000。
  2. 在副本中只把插入元组的金额改为 9000,确认读取为 ('lunch', 9000)。
  3. 改为 -1,数据库 CHECK 应拒绝写入。独立查询确认先前 9000 保留,再恢复 8000 并重跑。
  4. 使用错误密码确认连接失败,随后恢复有效环境值。按服务器和端口、账号 host 与密码、表级权限的顺序排查。
  5. 用只读账号区分 SELECT 成功和写入拒绝。应用账号原本不允许删除,不要为绕过权限错误而增加全局权限。

约束应在实际支持并启用它们的服务器上核对,见MariaDB 官方约束说明。所有故障实验仅在独立实例中进行,不对生产数据尝试破坏性语句。

数据库 rollback 只影响相应事务的写入,不会撤销此前的外部 HTTP 请求或文件修改。SQLite 事务与异常处理中介绍的资源边界同样适用;服务器数据库还需要额外管理网络、账号与权限。