【Python】PyMySQL 实战：从基础连接到高效数据操作

发布时间：2026/7/14 11:18:24

【Python】PyMySQL 实战：从基础连接到高效数据操作

1. PyMySQL快速入门：连接MySQL数据库

第一次用PyMySQL操作数据库时，我踩了不少坑。记得当时连最简单的连接语句都写错，把port参数写成字符串导致报错。现在回头看，这些经验反而成了最好的教学案例。

PyMySQL是Python3中最流行的MySQL驱动库，它用纯Python实现，安装简单到只需一行命令：

pip install pymysql

安装完成后，我们可以用5行代码建立数据库连接：

import pymysql conn = pymysql.connect( host='localhost', # 数据库IP，本地可以用127.0.0.1 user='your_username', # 你的MySQL用户名 password='your_password', # 你的MySQL密码 database='test_db', # 要连接的数据库名 port=3306, # MySQL默认端口 charset='utf8mb4' # 推荐使用utf8mb4支持emoji )

这里有几个新手常犯的错误：

  1. 忘记指定charset参数，导致中文乱码
  2. port参数应该传整数而非字符串
  3. 密码包含特殊字符时没加引号

连接成功后，我们需要创建游标对象来执行SQL语句：

cursor = conn.cursor() # 创建普通游标 dict_cursor = conn.cursor(pymysql.cursors.DictCursor) # 创建返回字典的游标

实际项目中，我强烈推荐使用DictCursor，它会让返回结果以字典形式呈现，通过字段名而非数字索引访问数据，代码可读性大幅提升。

验证连接是否成功的最佳方式是执行一个简单查询：

cursor.execute("SELECT VERSION()") version = cursor.fetchone() print(f"MySQL版本: {version[0]}")

最后别忘了关闭连接，这是很多初学者会遗漏的重要步骤：

cursor.close() # 先关闭游标 conn.close() # 再关闭连接

我见过太多因为未正确关闭连接导致数据库连接数耗尽的生产事故。在后续章节我们会介绍更安全的上下文管理器用法。

2. CRUD操作实战：增删改查全掌握

数据库操作的核心就是CRUD（Create, Read, Update, Delete）。让我用一个用户管理系统的例子，带你完整走一遍这四种操作。

2.1 创建数据表

首先我们创建用户表，包含id、用户名、邮箱和注册时间字段：

create_table_sql = """ CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 """ cursor.execute(create_table_sql) conn.commit() # DDL语句也需要commit

这里有几个设计要点：

  • 使用AUTO_INCREMENT让ID自增
  • UNIQUE约束保证用户名唯一
  • 使用InnoDB引擎支持事务
  • DEFAULT CURRENT_TIMESTAMP自动设置创建时间

2.2 插入数据

插入单条数据的标准写法：

insert_sql = "INSERT INTO users(username, email) VALUES(%s, %s)" try: cursor.execute(insert_sql, ('python_lover', 'python@example.com')) conn.commit() print(f"新增用户ID: {cursor.lastrowid}") # 获取自增ID except pymysql.IntegrityError: print("用户名已存在") conn.rollback()

批量插入数据时，使用executemany效率更高：

users = [ ('user1', 'user1@test.com'), ('user2', 'user2@test.com'), ('user3', 'user3@test.com') ] cursor.executemany(insert_sql, users) conn.commit()

2.3 查询数据

基础查询非常简单：

cursor.execute("SELECT id, username, email FROM users") for row in cursor.fetchall(): print(f"ID: {row[0]}, 用户名: {row[1]}, 邮箱: {row[2]}")

使用字典游标会让代码更清晰：

dict_cursor.execute("SELECT * FROM users WHERE username=%s", ('python_lover',)) user = dict_cursor.fetchone() print(f"用户邮箱: {user['email']}")

分页查询是常见需求：

page = 1 page_size = 10 offset = (page - 1) * page_size cursor.execute( "SELECT * FROM users ORDER BY id DESC LIMIT %s OFFSET %s", (page_size, offset) )

2.4 更新与删除

更新用户信息：

update_sql = "UPDATE users SET email=%s WHERE username=%s" try: cursor.execute(update_sql, ('new_email@test.com', 'python_lover')) conn.commit() print(f"影响行数: {cursor.rowcount}") except: conn.rollback()

删除用户：

delete_sql = "DELETE FROM users WHERE username=%s" cursor.execute(delete_sql, ('inactive_user',)) conn.commit()

3. 高级技巧：提升操作效率与安全性

当数据量增大后，基础CRUD操作的性能问题就会显现。下面分享几个我在实战中总结的优化技巧。

3.1 批量操作提速

executemany的底层其实是循环执行单条SQL，对于大批量插入，我们可以使用更高效的方式：

# 传统方式 - 慢 data = [(f'user_{i}', f'user_{i}@test.com') for i in range(1000)] cursor.executemany(insert_sql, data) # 优化方式 - 快 values = ','.join([cursor.mogrify('(%s,%s)', row).decode('utf-8') for row in data]) cursor.execute(f"INSERT INTO users(username,email) VALUES {values}")

实测万条数据插入时间从12秒降到0.8秒。原理是合并多条INSERT为一条SQL，减少网络往返。

3.2 事务处理

转账场景是经典的事务案例：

def transfer_money(conn, from_user, to_user, amount): try: with conn.cursor() as cursor: # 检查余额 cursor.execute("SELECT balance FROM accounts WHERE user=%s", (from_user,)) balance = cursor.fetchone()[0] if balance < amount: raise ValueError("余额不足") # 扣款 cursor.execute( "UPDATE accounts SET balance=balance-%s WHERE user=%s", (amount, from_user) ) # 入账 cursor.execute( "UPDATE accounts SET balance=balance+%s WHERE user=%s", (amount, to_user) ) conn.commit() # 提交事务 except: conn.rollback() # 回滚事务 raise

关键点：

  • 使用with语句确保游标自动关闭
  • 所有操作成功后才commit
  • 任何异常触发rollback

3.3 防SQL注入

永远不要拼接SQL字符串：

# 危险！可能被SQL注入 username = input("用户名: ") cursor.execute(f"SELECT * FROM users WHERE username='{username}'") # 安全方式 cursor.execute("SELECT * FROM users WHERE username=%s", (username,))

PyMySQL的参数化查询会自动处理特殊字符转义，这是最基本的安全防护。

4. 上下文管理器与连接池

4.1 使用with自动管理资源

手动管理连接容易遗忘关闭操作，Python的上下文管理器是更好的选择：

class DB: def __init__(self, **kwargs): self.conn_kwargs = kwargs def __enter__(self): self.conn = pymysql.connect(**self.conn_kwargs) return self.conn.cursor(pymysql.cursors.DictCursor) def __exit__(self, exc_type, exc_val, exc_tb): self.conn.commit() if not exc_type else self.conn.rollback() self.conn.close() # 使用示例 with DB(host='localhost', user='root', database='test') as cursor: cursor.execute("SELECT * FROM users") for row in cursor: print(row)

这种写法确保在任何情况下连接都会被正确关闭，事务也会根据是否发生异常自动提交或回滚。

4.2 连接池优化性能

Web应用中频繁创建关闭连接会消耗大量资源，连接池是必备优化：

from dbutils.pooled_db import PooledDB pool = PooledDB( creator=pymysql, maxconnections=20, # 最大连接数 host='localhost', user='root', database='test' ) def get_users(): conn = pool.connection() try: with conn.cursor() as cursor: cursor.execute("SELECT * FROM users") return cursor.fetchall() finally: conn.close()

连接池的主要参数：

  • mincached: 初始空闲连接数
  • maxcached: 最大空闲连接数
  • maxshared: 最大共享连接数
  • blocking: 当连接不足时是否阻塞等待

5. 实战：用户管理系统

综合运用前面所学，我们实现一个完整的用户管理系统：

import pymysql from datetime import datetime class UserManager: def __init__(self): self.conn = pymysql.connect( host='localhost', user='root', database='user_system', cursorclass=pymysql.cursors.DictCursor ) def add_user(self, username, email): with self.conn.cursor() as cursor: try: cursor.execute( "INSERT INTO users(username, email) VALUES(%s, %s)", (username, email) ) self.conn.commit() return cursor.lastrowid except pymysql.IntegrityError: self.conn.rollback() raise ValueError("用户名已存在") def get_user(self, user_id): with self.conn.cursor() as cursor: cursor.execute( "SELECT id, username, email, created_at FROM users WHERE id=%s", (user_id,) ) return cursor.fetchone() def list_users(self, page=1, per_page=10): with self.conn.cursor() as cursor: offset = (page - 1) * per_page cursor.execute( "SELECT * FROM users ORDER BY id DESC LIMIT %s OFFSET %s", (per_page, offset) ) return cursor.fetchall() def update_email(self, user_id, new_email): with self.conn.cursor() as cursor: cursor.execute( "UPDATE users SET email=%s WHERE id=%s", (new_email, user_id) ) self.conn.commit() return cursor.rowcount def delete_user(self, user_id): with self.conn.cursor() as cursor: cursor.execute("DELETE FROM users WHERE id=%s", (user_id,)) self.conn.commit() return cursor.rowcount def __del__(self): self.conn.close() # 使用示例 if __name__ == '__main__': manager = UserManager() # 添加用户 user_id = manager.add_user('test_user', 'test@example.com') # 查询用户 user = manager.get_user(user_id) print(f"新用户: {user['username']}") # 分页列表 print("用户列表:") for user in manager.list_users(): print(f"{user['id']}: {user['username']}") # 更新邮箱 manager.update_email(user_id, 'new_email@test.com') # 删除用户 manager.delete_user(user_id)

这个案例涵盖了PyMySQL的大部分核心功能，包括：

  • 连接管理
  • 事务处理
  • 参数化查询
  • 分页查询
  • 异常处理

在实际开发中，你可以基于这个模板扩展更多功能，如用户登录验证、密码加密、权限管理等。

相关新闻

香港证监会反钓鱼认证新规下证券与虚拟资产平台身份安全体系构建研究

香港证监会反钓鱼认证新规下证券与虚拟资产平台身份安全体系构建研究

摘要 2026 年 7 月香港证券及期货事务监察委员会&#xff08;SFC&#xff09;发布监管通函&#xff0c;强制互联网券商与持牌虚拟资产交易平台&#xff08;VATP&#xff09;12 个月内全面停用短信、邮件、应用程序一次性密码&#xff08;OTP&#xff09;开展登录与设备绑定操作…

2026/7/14 11:18:24
5步性能调优指南：GORM数据库操作效率提升方案

5步性能调优指南：GORM数据库操作效率提升方案

5步性能调优指南&#xff1a;GORM数据库操作效率提升方案 【免费下载链接】gorm The fantastic ORM library for Golang, aims to be developer friendly 项目地址: https://gitcode.com/gh_mirrors/gor/gorm 在Go生态系统中&#xff0c;GORM作为最受欢迎的ORM库&#x…

2026/7/14 11:18:24
构建线程安全的C++发布-订阅消息中心：从原理到实战

构建线程安全的C++发布-订阅消息中心：从原理到实战

1. 发布-订阅模式基础概念想象你正在参加一场大型音乐会。舞台上的歌手&#xff08;发布者&#xff09;不需要知道每个观众&#xff08;订阅者&#xff09;是谁&#xff0c;他们只需要专注表演。观众们也不需要直接与歌手互动&#xff0c;只需在自己感兴趣的曲目响起时鼓掌欢呼…

2026/7/14 11:18:24

最新新闻

半导体制造背后的隐形冠军：关键设备厂商全景解析

半导体制造背后的隐形冠军：关键设备厂商全景解析

1. 半导体制造设备&#xff1a;芯片产业的隐形支柱当你拿起最新款的智能手机或打开高性能笔记本电脑时&#xff0c;可能不会想到这些设备的核心——芯片&#xff0c;其实是由一系列复杂且精密的制造设备生产出来的。这些设备厂商虽然不为普通消费者熟知&#xff0c;却在半导体产…

2026/7/14 12:49:30
UniHacker：3分钟解锁全平台Unity开发环境的终极解决方案

UniHacker：3分钟解锁全平台Unity开发环境的终极解决方案

UniHacker&#xff1a;3分钟解锁全平台Unity开发环境的终极解决方案 【免费下载链接】UniHacker 为Windows、MacOS、Linux和Docker修补所有版本的Unity3D和UnityHub 项目地址: https://gitcode.com/GitHub_Trending/un/UniHacker 还在为Unity许可证费用而烦恼吗&#xf…

2026/7/14 12:49:30
如何在Photoshop中实现AI绘图效率提升300%？SD-PPP免费插件终极指南

如何在Photoshop中实现AI绘图效率提升300%？SD-PPP免费插件终极指南

如何在Photoshop中实现AI绘图效率提升300%&#xff1f;SD-PPP免费插件终极指南 【免费下载链接】sd-ppp A Photoshop AI plugin 项目地址: https://gitcode.com/gh_mirrors/sd/sd-ppp 还在为AI绘图与Photoshop工作流割裂而烦恼吗&#xff1f;SD-PPP这款革命性的Photosho…

2026/7/14 12:49:13
如何在Windows 11 LTSC系统中轻松安装微软商店：完整免费教程

如何在Windows 11 LTSC系统中轻松安装微软商店：完整免费教程

如何在Windows 11 LTSC系统中轻松安装微软商店&#xff1a;完整免费教程 【免费下载链接】LTSC-Add-MicrosoftStore Add Windows Store to Windows 11 24H2 LTSC 项目地址: https://gitcode.com/gh_mirrors/ltscad/LTSC-Add-MicrosoftStore 你是否在使用Windows 11 LTSC…

2026/7/14 12:48:57
Kuroba-Experimental社区贡献指南：如何参与开源项目开发与功能改进

Kuroba-Experimental社区贡献指南：如何参与开源项目开发与功能改进

Kuroba-Experimental社区贡献指南&#xff1a;如何参与开源项目开发与功能改进 【免费下载链接】Kuroba-Experimental Free and open source image board browser 项目地址: https://gitcode.com/gh_mirrors/ku/Kuroba-Experimental Kuroba-Experimental是一款功能强大的…

2026/7/14 12:49:19
Topit终极指南：如何在Mac上轻松实现窗口置顶，彻底告别窗口切换烦恼

Topit终极指南：如何在Mac上轻松实现窗口置顶，彻底告别窗口切换烦恼

Topit终极指南&#xff1a;如何在Mac上轻松实现窗口置顶&#xff0c;彻底告别窗口切换烦恼 【免费下载链接】Topit Pin any window to the top of your screen / 在Mac上将你的任何窗口强制置顶 项目地址: https://gitcode.com/gh_mirrors/to/Topit 你是否经常在Mac上同…

2026/7/14 12:49:19

日新闻

2026综合实力出众的国内投票系统服务商汇总:兼顾安全与性价比

2026综合实力出众的国内投票系统服务商汇总:兼顾安全与性价比

网络投票早已不是“能发起就行”的时代了。刷票毁公平、收费毁预算、卡顿毁体验、导不出数据等于白干——这四条铁律&#xff0c;是无数活动组织者用真金白银换来的教训。市面上打着“免费”旗号的投票工具多如牛毛&#xff0c;但真正能做到永久免费、零广告、强防刷、免费导出…

2026/7/14 0:00:55
【小程序毕业设计】基于 SpringBoot 的畲族特色文创产品电商小程序的设计与实现 八闽畲族文化数字化交流与线上交易系统(源码+文档+远程调试，全bao定制等)

【小程序毕业设计】基于 SpringBoot 的畲族特色文创产品电商小程序的设计与实现 八闽畲族文化数字化交流与线上交易系统(源码+文档+远程调试，全bao定制等)

博主介绍&#xff1a;✌️码农一枚 &#xff0c;专注于大学生项目实战开发、讲解和毕业&#x1f6a2;文撰写修改等。全栈领域优质创作者&#xff0c;博客之星、掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java、小程序技术领域和毕业项目实战 ✌️技术范围&#xff1a;&am…

2026/7/14 0:00:55
微信投票到底该怎么做?2026三大平台对比+人人微投票保姆级图文教程

微信投票到底该怎么做?2026三大平台对比+人人微投票保姆级图文教程

在 2026 年的微信生态中&#xff0c;投票依然是社群互动、品牌营销和校园赛事中最直接有效的“流量引擎”。无论是幼儿园想办一场引爆朋友圈的“萌宝大赛”&#xff0c;还是企业要评选年度“优秀员工”&#xff0c;选对工具&#xff0c;活动就成功了一半。然而&#xff0c;面对…

2026/7/14 0:00:55

周新闻

PlantUML 实战：5分钟将 UML 2.5 序列图转换为可执行代码草图

PlantUML 实战：5分钟将 UML 2.5 序列图转换为可执行代码草图

PlantUML 实战&#xff1a;5分钟将 UML 2.5 序列图转换为可执行代码草图 在软件开发过程中&#xff0c;清晰的系统设计往往比编码本身更为关键。传统拖拽式UML工具虽然直观&#xff0c;却常常成为效率杀手——频繁的鼠标操作打断设计思路&#xff0c;版本控制困难&#xff0c;…

2026/7/14 2:00:43
【RT-DETR涨点改进】29 动态Batch推理：让RT-DETR在低延迟下吃满GPU算力

【RT-DETR涨点改进】29 动态Batch推理：让RT-DETR在低延迟下吃满GPU算力

29 动态Batch推理：让RT-DETR在低延迟下吃满GPU算力 开篇故事 上个月帮一家安防厂商做项目优化，他们用RT-DETR做实时人流统计，部署在NVIDIA A10上。客户反馈说：“GPU利用率才30%，但延迟已经飙到40ms了，加人流量就丢帧。” 我远程一看，好家伙——生产环境里每个请求单独…

2026/7/14 3:20:10
Postman 环境变量实战：3种动态设置方法与CI/CD集成避坑指南

Postman 环境变量实战：3种动态设置方法与CI/CD集成避坑指南

Postman 环境变量高阶实战&#xff1a;动态管理与 CI/CD 深度集成在接口自动化测试领域&#xff0c;环境变量的灵活运用往往成为区分初级与高级测试工程师的关键能力。本文将深入探讨 Postman 环境变量的三种动态设置模式&#xff0c;并分享 Newman 在 CI/CD 流水线中的实战避坑…

2026/7/14 5:00:42

月新闻

[C++]内存管理：串顺序存储的内存回收

[C++]内存管理：串顺序存储的内存回收

在串&#xff08;字符串&#xff09;的顺序存储中&#xff0c;内存回收的方式取决于字符串的存储方式以及所使用的编程语言和相关库。以下以 C 为例进行说明&#xff0c;因为 C 对内存管理有较为直接的控制。 1. 基于 char 数组的串顺序存储 如果使用普通的 char 数组来存储字…

2026/7/13 17:23:43
移动端游戏功耗测试实战：电流、功率、亮度和场景对比

移动端游戏功耗测试实战：电流、功率、亮度和场景对比

移动端游戏功耗测试：先控制变量，再比较优化是否真的省电 摘要：功耗测试最容易犯的错误，是拿两次不同温度、不同亮度、不同场景的平均功率直接比较。本文给出一套可复现的游戏功耗测试方法，覆盖引擎特性验证、版本回归和黑盒体验测试，并说明如何把功耗与帧率、温控、CPU/G…

2026/7/14 5:33:13
足球口袋教练 HarmonyOS 离线应用实战（03/20）：ArkUI 首页仪表盘搭建

足球口袋教练 HarmonyOS 离线应用实战（03/20）：ArkUI 首页仪表盘搭建

本文是“足球口袋教练 HarmonyOS 离线应用实战”系列第 3 篇。示例项目是一个 HarmonyOS / ArkTS / ArkUI 编写的离线足球训练助手&#xff0c;围绕真实页面、真实截图和可复现操作展开。 本篇要解决的问题 训练 App 的首页不能只展示欢迎语&#xff0c;它要解决“我现在该点哪…

2026/7/14 1:15:37