Python接入Mysql数据库
Python 接入 MySQL 数据库详解
在现代应用开发中，数据持久化 是非常核心的一环。使用 Python 语言访问和操作 MySQL 数据库，不仅能实现数据的存取、更新与查询，还能结合第三方库进行数据可视化与分析。本节将从教学流程、使用场景和最佳实践角度，系统讲解如何使用 Python 接入 MySQL。
一、选择库：PyMySQL vs mysqlclient
✅ 推荐使用：PyMySQL
- 纯 Python 实现，无需编译器，跨平台兼容性更好。
- 安装简单，支持 MySQL 5.x 和 8.x。
- 适用于大多数 Web 和数据处理项目。
❌ mysqlclient
- 基于 C 的封装，需要依赖编译环境（如 GCC + MySQL dev headers）。
- 在 Linux 下可能更高效，但部署复杂。
安装命令（支持 MySQL 8 密码验证）：
pip install pymysql cryptography
二、基本接入流程（五步法）
✅ 通用操作步骤：
- 创建连接对象（Connection）
- 获取游标对象（Cursor）
- 执行 SQL 语句
- 处理事务 / 获取结果
- 关闭连接释放资源
📌 小贴士：所有数据库连接操作建议用
try...finally或with上下文管理，确保稳定关闭连接，防止资源泄露。
三、实用场景演示
### 📌 插入数据场景
适用情境： 添加新用户、新订单、新记录等操作。
cursor.execute(
'INSERT INTO `tb_dept` VALUES (%s, %s, %s)',
(no, name, location)
)
- 事务控制：必须
commit()才真正写入数据库。 - 适合表单提交、数据导入等需求。
📌 删除数据场景
适用情境： 用户删除操作、后台数据清理。
cursor.execute('DELETE FROM `tb_dept` WHERE `dno`=%s', (no,))
- 可以设定
autocommit=True实现自动提交，适用于小型脚本。 - 注意权限控制，避免误删核心数据。
📌 更新数据场景
适用情境： 修改用户信息、更新库存、修正数据等。
cursor.execute(
'UPDATE `tb_dept` SET `dname`=%s, `dloc`=%s WHERE `dno`=%s',
(name, location, no)
)
- 可通过比对修改前后的值确保正确修改。
- 建议加上
WHERE条件防止全表更新！
📌 查询数据场景（读取）
场景 1：逐行抓取大量数据
适用： 表行数大，不适合一次性加载。
row = cursor.fetchone()
while row:
print(row)
row = cursor.fetchone()
场景 2：分页查询（常见于 Web 应用）
适用： 列表页、分页展示、负载均衡。
cursor.execute(
'SELECT `eno`, `ename`, `job`, `sal` FROM `tb_emp` '
'ORDER BY `sal` DESC LIMIT %s,%s',
((page - 1) * size, size)
)
- 使用
LIMIT offset, size实现分页。 - 可结合前端分页组件、API 接口统一返回结构。
四、事务机制与异常处理
🧩 默认行为
- PyMySQL 默认开启事务，需手动
commit()。
🎯 推荐做法（最佳实践）：
try:
with conn.cursor() as cursor:
cursor.execute(...) # SQL 操作
conn.commit()
except pymysql.MySQLError as err:
conn.rollback()
print(err)
finally:
conn.close()
- 将
commit放在业务逻辑之后，确保只在成功时写入。 - 异常处理统一打印错误，避免静默失败。
五、权限与安全建议
❗ 安全最佳实践
- 永远不要在生产环境使用 root 账号连接数据库！
- 使用专用账户并授予必要权限（最小授权原则）。
👇 创建用户和授权的标准命令：
CREATE USER 'guest'@'%' IDENTIFIED BY 'Guest.618';
GRANT INSERT, DELETE, UPDATE, SELECT ON `hrs`.* TO 'guest'@'%';
六、高性能批量插入
✅ 使用 executemany
适用场景： 数据同步、大批量导入。
cursor.executemany(
'INSERT INTO `tb_emp` VALUES (%s, %s, %s, %s)',
[
(1, 'Tom', 'Analyst', 8000),
(2, 'Jane', 'Manager', 10000),
...
]
)
- 比逐条
execute快数倍。 - 在数据量非常大时建议分批执行（如每批 500 条）。
七、扩展场景：导出到 Excel
📦 用途：报表生成、备份导出、离线分析
安装依赖：
pip install openpyxl
示例代码：
sheet.append(('工号', '姓名', '职位', '月薪', '补贴', '部门'))
cursor.execute(
'SELECT `eno`, `ename`, `job`, `sal`, COALESCE(`comm`, 0), `dname` '
'FROM `tb_emp` NATURAL JOIN `tb_dept`'
)
for row in cursor:
sheet.append(row)
- 可用于系统后台报表导出、财务人员汇总分析。
- 可结合自动化调度系统每日/每周生成报表。
八、最佳实践总结
| 场景 | 推荐操作 | 说明 |
|---|---|---|
| 连接管理 | 使用 with 或 try-finally |
保证连接及时关闭 |
| 安全性 | 禁用 root、最小权限 |
防止数据泄露与误操作 |
| 查询优化 | 用分页、迭代器抓取大数据 | 降低内存占用 |
| 插入效率 | 用 executemany 做批处理 |
提高写入速度 |
| 事务控制 | 手动 commit/rollback |
灵活应对业务错误 |
| 可维护性 | 封装为函数/模块 | 便于团队开发和测试 |
九、进阶建议
- 使用 ORM 框架（如 SQLAlchemy、Peewee）进一步简化数据操作逻辑。
- 将数据库连接池集成到 Web 框架中，如 Django、Flask。
- 利用 Python 多线程或异步库（如 asyncio）优化并发数据访问。
如需进一步学习如「数据导入 Excel」「数据库性能分析」「ORM 框架设计」等模块，欢迎继续深入扩展，我可以继续为你提供详细讲解。