Python 使用 PyMySQL 连接和操作 MySQL 数据库详解
本文将系统介绍基于 Python 的 PyMySQL 库如何连接并操作 MySQL 数据库。内容涵盖从建立连接、配置游标类型，到执行 SQL 语句时的安全性防范（防止 SQL 注入），以及多种数据获取方式 fetchone、fetchall 的实际应用。
连接数据库与创建游标
使用 PyMySQL 进行数据库操作的第一步是建立连接，并创建一个用于执行 SQL 语句的光标对象（Cursor）。光标的存在使得我们可以按顺序遍历查询结果。
第1步：建立数据库连接
目的：连接本地的 MySQL 服务并指定目标数据库。
入口：调用 pymysql.connect() 方法。
动作：传入主机地址 host、端口 port、用户名 user、密码 passwd 及数据库名 db。
import pymysql # 建立连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='@123456', db='db4') # 创建光标（默认返回元组类型） cursor = conn.cursor()
第2步：使用字典类型的游标（可选）
目的：将查询结果以字典格式返回，便于通过字段名访问数据。
入口：pymysql.cursors.DictCursor。
参数：在初始化光标时传入。
# 设置游标为字典类型
cursor = conn.cursor(clear=pymysql.cursors.DictCursor)
sql = "SELECT * FROM department;"
cursor.execute(sql)
result = cursor.fetchone()
print(result) # 输出示例: {'id': 1, 'title': '财务'}
执行 SQL 与防注入策略
在构建动态 SQL 时，严禁直接拼接变量以防止 SQL 注入攻击。PyMySQL 提供了安全参数化传值的方法。
第3步：安全地执行 SQL 语句
目的：执行单条 SQL 并正确传递外部参数。
动作：SQL 字符串中预留占位符 %s，并在 execute 的第二参数中以元组或字典形式传入实际值。
# 方式一：使用元组传参
name = input("请输入用户名: ")
sql = "SELECT uid FROM users WHERE username = %s"
cursor.execute(sql, (name,))
uid = cursor.fetchone()[0]
# 方式二：使用字典传参
cursor.execute(sql, {"u": name})
第4步：批量执行 SQL
目的：向数据库中批量插入多条相同结构的记录。
动作：使用 executemany()，第二个参数需为列表格式的迭代器。
sql = "INSERT INTO department(title) VALUES(%s)"
data = [('经理'), ('总监')]
cursor.executemany(sql, data)
conn.commit() # 批量插入后必须提交事务
获取查询结果的不同方式
执行了查询类 SQL 后，可以通过不同方式提取游标中的结果集。
第5步：逐行获取数据 fetchone
目的：每次仅取回结果集中的下一行数据。
注意：多次调用会消耗掉当前行的数据，适合需要逐一处理数据的场景。
sql = "SELECT * FROM department;" cursor.execute(sql) print(cursor.fetchone()) # 第1行结果 print(cursor.fetchone()) # 第2行结果
第6步：批量获取多行 fetchmany
目的：一次取回指定数量的结果集。
参数：需要指定返回的行数 num。
sql = "SELECT * FROM department;" cursor.execute(sql) result = cursor.fetchmany(2) print(result) # 返回包含两行数据的嵌套元组
第7步：一次性获取所有结果 fetchall
目的：将查询到的所有剩余数据全部装入内存返回。
注意：当数据量巨大时使用此方法可能导致高内存占用。
cursor.execute(sql) all_results = cursor.fetchall()
控制游标位置与自增 ID
第8步：移动游标位置
如果不想从头读取，可以使用 scroll() 方法调整当前光标的位置：
cursor.scroll(1, mode='relative')：相对当前位置向下移动一行；cursor.scroll(2, mode='absolute')：绝对跳转到索引为 2 的行（即第 3 行，如果从 0 计数的话，这里具体取决于底层实现，通常表示偏移量）。
第9步：获取最新的自增 ID
目的：新插入一条记录后，立刻获取该记录的自增主键 ID。
动作：在 commit() 之前调用 cursor.lastrowid。
cursor.execute("INSERT INTO ... VALUES (...)")
new_id = cursor.lastrowid
conn.commit()
print(f'新插入数据的ID为: {new_id}')
综合实战：用户权限查询管理
下面是一个利用多表联合查询和参数化传递来验证“用户登录”并查看其“权限列表”的完整示例，涵盖了建表逻辑与 SQL 编写思路。
环境准备与建表逻辑
我们需要三张表来维护这种权限分配关系：
power：存储系统权限项（如订单管理、用户管理等）。users：存储用户信息（如姓名）。use_pow：关联表，绑定user_id与power_id。
第10步：编写查询脚本
场景：输入用户名，判断是否存在，若存在则显示其拥有的所有权限名称。
# 假设已经完成了上述三张表的创建。
import pymysql
user_name = input('请输入用户名称>>>')
# 连接数据库
db_conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='@123456',
db='db_grant'
)
cursor = db_conn.cursor()
# 第一步：根据用户名查询对应的 uid（防注入）
sql_find_uid = "SELECT uid FROM users WHERE username = %s"
cursor.execute(sql_find_uid, (user_name,))
user_data = cursor.fetchone()
if user_data:
uid = user_data[0] # 取出第一个元素作为用户ID
# 第二步：查询该用户拥有哪些权限名称
sql_get_power = """
SELECT p.purview
FROM power p
INNER JOIN use_pow up ON p.pid = up.power_id
WHERE up.user_id = %s
"""
cursor.execute(sql_get_power, (uid,))
result = cursor.fetchall()
print(f'用户 {user_name} 拥有的权限: {result}')
else:
print("数据库中未找到该用户")
cursor.close()
db_conn.close()
以上就是 Python 通过 PyMySQL 对 MySQL 数据库进行操作的基础与进阶指南，更多关于 Python 数据库编程的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
