Python连接MySQL教程,PyMySQL使用详解
想要轻松连接Python与MySQL数据库?本文为你提供一份详尽的PyMySQL使用教程,助你快速上手!PyMySQL作为Python与MySQL交互的首选库,以其纯Python实现、安装简便而备受青睐。本文将深入讲解PyMySQL连接MySQL数据库的核心步骤,包括**导入库、建立连接、创建游标、执行SQL查询、事务处理**以及**关闭连接**。同时,着重介绍了如何通过参数化查询有效**防止SQL注入**,并分享了利用DBUtils连接池**优化性能**、提升资源复用效率的实用技巧。无论你是新手还是有一定经验的开发者,都能从中受益,掌握PyMySQL连接MySQL的最佳实践。
PyMySQL连接MySQL数据库的核心步骤包括导入库、建立连接、创建游标、执行SQL、事务处理及关闭连接。1. 导入pymysql模块;2. 使用pymysql.connect()建立连接,传入数据库配置参数;3. 通过with conn.cursor()创建并自动管理游标;4. 使用cursor.execute()执行SQL语句,支持参数化查询防止SQL注入;5. 对于写操作需调用conn.commit()提交事务,出错时调用conn.rollback()回滚;6. 最后在finally块中确保conn.close()关闭连接。此外,高并发场景建议使用DBUtils连接池优化性能,提升资源复用效率。
PyMySQL是Python与MySQL数据库交互的首选库之一,它提供了一套直观的API,让开发者能够轻松地建立连接、执行SQL查询并管理数据。它的纯Python实现意味着安装和部署通常非常简单,不需要额外的编译步骤或系统依赖,这对于许多项目来说是一个巨大的便利。

解决方案
使用PyMySQL连接MySQL数据库的核心步骤通常包括导入库、建立连接、创建游标、执行SQL、提交或回滚事务,以及最终关闭连接。
首先,你需要确保PyMySQL已经安装:
pip install PyMySQL

接下来,一个基本的连接和查询流程会是这样:
import pymysql # 数据库连接参数 db_config = { 'host': 'localhost', 'user': 'your_username', 'password': 'your_password', 'database': 'your_database', 'charset': 'utf8mb4', # 推荐使用utf8mb4以支持更广泛的字符集 'cursorclass': pymysql.cursors.DictCursor # 推荐使用DictCursor,结果以字典形式返回 } conn = None try: # 建立连接 conn = pymysql.connect(**db_config) print("数据库连接成功!") # 创建游标对象,用于执行SQL查询 # 使用with语句可以确保游标在代码块结束时自动关闭 with conn.cursor() as cursor: # 示例:创建表(如果不存在) create_table_sql = """ CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; """ cursor.execute(create_table_sql) print("表 'users' 检查或创建完成。") # 示例:插入数据 # 注意:使用参数化查询来防止SQL注入 insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)" users_to_insert = [ ("张三", "zhangsan@example.com"), ("李四", "lisi@example.com") ] for user_data in users_to_insert: try: cursor.execute(insert_sql, user_data) print(f"插入数据: {user_data[0]} 成功。") except pymysql.err.IntegrityError as e: # 捕获唯一键冲突等错误 print(f"插入数据: {user_data[0]} 失败,可能已存在或数据冲突: {e}") except Exception as e: print(f"插入数据: {user_data[0]} 发生未知错误: {e}") # 提交事务,使更改生效 conn.commit() print("数据插入事务已提交。") # 示例:查询数据 select_sql = "SELECT id, name, email FROM users WHERE name = %s" cursor.execute(select_sql, ("张三",)) result = cursor.fetchone() # 获取一条结果 if result: print(f"查询到用户张三: ID={result['id']}, Name={result['name']}, Email={result['email']}") # 示例:查询所有数据 cursor.execute("SELECT id, name, email FROM users") all_users = cursor.fetchall() # 获取所有结果 print("\n所有用户:") for user in all_users: print(f"ID={user['id']}, Name={user['name']}, Email={user['email']}") except pymysql.err.OperationalError as e: # 捕获连接或操作数据库时的错误 print(f"数据库操作错误: {e}") if conn: conn.rollback() # 发生错误时回滚事务 print("事务已回滚。") except Exception as e: # 捕获其他未知错误 print(f"发生未知错误: {e}") if conn: conn.rollback() print("事务已回滚。") finally: # 确保连接最终被关闭 if conn: conn.close() print("数据库连接已关闭。")
这段代码展示了一个相对完整的流程,包括了连接、创建表、插入数据(并处理了可能的冲突)、查询以及错误处理和资源释放。使用with conn.cursor() as cursor:
是管理游标的好习惯,它能确保游标在不再需要时被正确关闭。

如何优化PyMySQL的性能与资源管理?
在实际应用中,尤其是在高并发或长时间运行的服务里,如何高效地管理数据库连接是至关重要的。直接每次请求都建立和关闭连接的开销是巨大的,这不仅影响性能,还可能耗尽数据库服务器的连接资源。我曾经在开发一个高并发API服务时,就遇到过因为连接管理不当导致的数据库连接池耗尽问题,服务直接挂掉,那真是个头疼的下午。
一个常见的解决方案是使用连接池(Connection Pool)。连接池预先创建并维护一定数量的数据库连接,当应用程序需要连接时,它从池中获取一个空闲连接;使用完毕后,连接不是关闭,而是归还到池中,供其他请求复用。这样就大大减少了连接的建立和关闭开销。
PyMySQL本身不内置连接池功能,但你可以使用第三方库,比如DBUtils
(特别是其中的PooledDB
或PersistentDB
模块),或者自己实现一个简单的连接池。
以下是一个使用DBUtils.PooledDB
的简要示例:
from DBUtils.PooledDB import PooledDB import pymysql # 数据库配置,与之前类似 db_config = { 'host': 'localhost', 'user': 'your_username', 'password': 'your_password', 'database': 'your_database', 'charset': 'utf8mb4', 'cursorclass': pymysql.cursors.DictCursor } # 初始化连接池,通常在应用启动时执行一次 # mincached: 启动时创建的空闲连接数 # maxcached: 连接池中允许的最大空闲连接数 # maxconnections: 连接池允许的最大连接数(包括使用中和空闲的) # blocking: 如果连接池已满,是否阻塞等待 pool = PooledDB( creator=pymysql, # 指定底层数据库模块 mincached=5, maxcached=10, maxconnections=20, blocking=True, ping=1, # 检查连接是否可用,如果断开会自动重新连接 **db_config ) def execute_query_with_pool(sql, params=None): conn = None cursor = None result = None try: # 从连接池获取连接 conn = pool.connection() with conn.cursor() as cursor: cursor.execute(sql, params) if sql.strip().upper().startswith("SELECT"): result = cursor.fetchall() else: conn.commit() result = cursor.rowcount # 返回影响的行数 except Exception as e: print(f"数据库操作错误: {e}") if conn: conn.rollback() # 发生错误时回滚 raise # 重新抛出异常,让调用者处理 finally: if conn: # 连接被归还到连接池,而不是真正关闭 conn.close() return result # 示例使用 try: # 插入数据 execute_query_with_pool("INSERT INTO users (name, email) VALUES (%s, %s)", ("王五", "wangwu@example.com")) print("王五插入成功。") # 查询数据 users = execute_query_with_pool("SELECT * FROM users") print("\n通过连接池查询到的所有用户:") for user in users: print(user) except Exception as e: print(f"操作失败: {e}")
使用连接池后,每次调用pool.connection()
都会从池中获取一个连接,conn.close()
实际上是把连接归还给连接池。这显著提高了性能和资源利用率。同时,ping=1
参数在PooledDB
中也很关键,它能在连接被使用前检查其有效性,避免使用到已断开的连接,这在网络环境复杂或数据库空闲超时设置较短时特别有用。
PyMySQL中处理SQL注入和事务管理的最佳实践是什么?
SQL注入是一个古老但依然普遍存在的安全漏洞,而事务管理则是确保数据一致性的基石。在PyMySQL中,遵循一些最佳实践可以有效规避这些风险。
防止SQL注入:参数化查询
PyMySQL(以及几乎所有现代数据库驱动)都提供了参数化查询的机制,这是防御SQL注入最有效的方法。它的原理是,SQL语句的结构和数据是分开传递的。数据库驱动会负责将数据安全地插入到SQL模板中,无论是字符串还是数字,都会被正确地转义,从而避免恶意代码被当作SQL指令执行。
永远不要通过字符串拼接的方式来构建SQL查询,特别是当查询中包含用户输入时。
错误示例(易受SQL注入攻击):
# 千万不要这样做! user_input_name = "张三'; DROP TABLE users;--" # 恶意输入 sql = f"SELECT * FROM users WHERE name = '{user_input_name}'" cursor.execute(sql)
如果执行上述代码,数据库表可能就会被删掉。
正确做法(使用参数化查询):
# 推荐做法 user_input_name = "张三" # 或者任何用户输入 sql = "SELECT * FROM users WHERE name = %s" # 使用%s作为占位符 cursor.execute(sql, (user_input_name,)) # 将参数作为元组传递给execute方法
对于INSERT
、UPDATE
、DELETE
等操作也是同理。PyMySQL会根据%s
占位符自动处理参数的转义,确保它们被视为数据而不是可执行的SQL代码。
事务管理:确保数据一致性
事务是一系列数据库操作的逻辑单元,这些操作要么全部成功(提交),要么全部失败(回滚)。这对于保持数据的一致性和完整性至关重要,特别是在涉及多个相关操作时。
PyMySQL默认是自动提交(autocommit)模式关闭的,这意味着你需要显式地调用conn.commit()
来保存更改,或者调用conn.rollback()
来撤销更改。我个人更倾向于这种显式控制,它让事务的边界更加清晰。
基本事务流程:
conn = None try: conn = pymysql.connect(**db_config) # 默认autocommit=False,所以需要手动提交 with conn.cursor() as cursor: # 操作1:更新用户余额 cursor.execute("UPDATE accounts SET balance = balance - %s WHERE user_id = %s", (100, 1)) # 模拟一个可能失败的操作 # 如果这里发生错误,下面的commit就不会执行 # 操作2:记录交易日志 cursor.execute("INSERT INTO transactions (user_id, amount, type) VALUES (%s, %s, %s)", (1, -100, 'expense')) conn.commit() # 所有操作都成功,提交事务 print("事务提交成功,数据已更新。") except Exception as e: print(f"事务失败: {e}") if conn: conn.rollback() # 任何一个操作失败,回滚所有更改 print("事务已回滚,数据未更改。") finally: if conn: conn.close()
如果你希望PyMySQL在每次执行SQL后都自动提交,可以设置autocommit=True
在连接参数中:
conn = pymysql.connect(autocommit=True, **db_config)
但对于复杂的业务逻辑,我通常不建议开启autocommit
,因为这会让你失去对事务的精细控制,一旦某个操作出错,前面的操作也可能已经提交,导致数据不一致。显式提交和回滚能让你更好地把握数据流向。
PyMySQL连接遇到问题时,如何进行故障排除?
在使用PyMySQL连接MySQL数据库时,遇到各种连接或操作错误是常有的事。这些问题可能源于配置、网络、权限或数据库状态。我记得有一次,花了半天时间才发现是MySQL服务器的端口被防火墙挡住了,这种低级错误也常常让人抓狂。
以下是一些常见的故障排除步骤和思路:
检查连接参数:
- 主机(host):确保你连接的是正确的IP地址或域名。如果是本地开发,通常是
localhost
或127.0.0.1
。 - 端口(port):MySQL默认端口是3306。如果MySQL服务器使用了非标准端口,请确保在配置中指定。
- 用户名(user)和密码(password):这是最常见的错误来源。仔细检查,区分大小写。
- 数据库名(database):确保你要连接的数据库存在且名称正确。
- 主机(host):确保你连接的是正确的IP地址或域名。如果是本地开发,通常是
网络连通性:
- 防火墙:检查客户端机器和MySQL服务器上的防火墙规则。确保MySQL端口(默认3306)是开放的,并且允许你的客户端IP连接。
- 网络路径:尝试从客户端机器
ping
MySQL服务器的IP地址,看是否能通。 - Telnet/nc测试:在客户端机器上使用
telnet
或3306 nc -vz
来测试端口是否开放且可达。如果连接被拒绝或超时,很可能是网络或防火墙问题。3306
MySQL服务器状态:
- 服务是否运行:登录到MySQL服务器,检查MySQL服务是否正在运行。例如,在Linux上可以使用
sudo systemctl status mysql
或sudo service mysql status
。 - 最大连接数:MySQL服务器有最大连接数限制。如果连接池或应用程序创建了太多连接,可能会超出此限制。可以通过
SHOW VARIABLES LIKE 'max_connections';
查看,并通过SHOW STATUS LIKE 'Threads_connected';
查看当前连接数。 - 用户权限:确保连接使用的MySQL用户拥有从客户端IP连接到指定数据库的权限。例如,用户可能只被授权从
localhost
连接,而你却尝试从远程IP连接。可以使用SELECT user, host FROM mysql.user;
查看用户权限配置。
- 服务是否运行:登录到MySQL服务器,检查MySQL服务是否正在运行。例如,在Linux上可以使用
编码问题:
- 如果数据库中的字符显示乱码,或者插入数据时报错
Incorrect string value
,很可能是编码问题。在PyMySQL连接时,确保charset
参数与数据库的编码设置一致,通常推荐使用utf8mb4
。 - 检查数据库、表和列的字符集设置。
- 如果数据库中的字符显示乱码,或者插入数据时报错
错误日志:
- PyMySQL错误信息:Python代码中的
try...except
块会捕获pymysql.err
下的各种异常,如OperationalError
(连接或操作错误)、ProgrammingError
(SQL语法错误)、IntegrityError
(数据完整性错误,如唯一键冲突)。仔细阅读这些错误信息,它们通常会提供关键线索。 - MySQL服务器错误日志:MySQL服务器有自己的错误日志文件(通常在
/var/log/mysql/error.log
或my.cnf
中配置的路径)。查看这个日志文件,可能会发现服务器端拒绝连接或查询失败的具体原因。
- PyMySQL错误信息:Python代码中的
代码审查:
- 资源未释放:确保连接和游标在使用完毕后都被正确关闭。虽然Python的垃圾回收机制会处理,但在高并发场景下,不及时关闭会耗尽资源。使用
with
语句是最佳实践。 - SQL语法错误:
ProgrammingError
通常指向SQL语句本身的问题。检查SQL的拼写、关键字、表名和列名是否正确。
- 资源未释放:确保连接和游标在使用完毕后都被正确关闭。虽然Python的垃圾回收机制会处理,但在高并发场景下,不及时关闭会耗尽资源。使用
通过系统地排查这些方面,通常能定位并解决PyMySQL连接和操作中的大部分问题。
理论要掌握,实操不能落!以上关于《Python连接MySQL教程,PyMySQL使用详解》的详细介绍,大家都掌握了吧!如果想要继续提升自己的能力,那么就来关注golang学习网公众号吧!

- 上一篇
- 豆包账号注册与登录详细教程

- 下一篇
- HTML5time标签如何提升SEO?
-
- 文章 · python教程 | 3分钟前 |
- Python加密解密技巧与hashlib实战教程
- 395浏览 收藏
-
- 文章 · python教程 | 12分钟前 |
- Python轻松实现GPT-2文本生成教程
- 117浏览 收藏
-
- 文章 · python教程 | 13分钟前 |
- Python模块导入全解析
- 134浏览 收藏
-
- 文章 · python教程 | 44分钟前 | Python kafka 流数据处理 PySpark StructuredStreaming
- Python流处理:Kafka与Spark实战教程
- 257浏览 收藏
-
- 文章 · python教程 | 1小时前 |
- Python处理嵌套JSON数据技巧
- 349浏览 收藏
-
- 文章 · python教程 | 1小时前 |
- Pythonround函数使用方法详解
- 192浏览 收藏
-
- 文章 · python教程 | 1小时前 |
- Python视频流处理:OpenCV帧操作详解
- 137浏览 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 509次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 484次学习
-
- 边界AI平台
- 探索AI边界平台,领先的智能AI对话、写作与画图生成工具。高效便捷,满足多样化需求。立即体验!
- 393次使用
-
- 免费AI认证证书
- 科大讯飞AI大学堂推出免费大模型工程师认证,助力您掌握AI技能,提升职场竞争力。体系化学习,实战项目,权威认证,助您成为企业级大模型应用人才。
- 405次使用
-
- 茅茅虫AIGC检测
- 茅茅虫AIGC检测,湖南茅茅虫科技有限公司倾力打造,运用NLP技术精准识别AI生成文本,提供论文、专著等学术文本的AIGC检测服务。支持多种格式,生成可视化报告,保障您的学术诚信和内容质量。
- 542次使用
-
- 赛林匹克平台(Challympics)
- 探索赛林匹克平台Challympics,一个聚焦人工智能、算力算法、量子计算等前沿技术的赛事聚合平台。连接产学研用,助力科技创新与产业升级。
- 641次使用
-
- 笔格AIPPT
- SEO 笔格AIPPT是135编辑器推出的AI智能PPT制作平台,依托DeepSeek大模型,实现智能大纲生成、一键PPT生成、AI文字优化、图像生成等功能。免费试用,提升PPT制作效率,适用于商务演示、教育培训等多种场景。
- 548次使用
-
- Flask框架安装技巧:让你的开发更高效
- 2024-01-03 501浏览
-
- Django框架中的并发处理技巧
- 2024-01-22 501浏览
-
- 提升Python包下载速度的方法——正确配置pip的国内源
- 2024-01-17 501浏览
-
- Python与C++:哪个编程语言更适合初学者?
- 2024-03-25 501浏览
-
- 品牌建设技巧
- 2024-04-06 501浏览