当前位置:首页 > 文章列表 > 文章 > python教程 > Python连接MySQL教程,PyMySQL使用详解

Python连接MySQL教程,PyMySQL使用详解

2025-07-11 15:54:35 0浏览 收藏

想要轻松连接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连接池优化性能,提升资源复用效率。

Python怎样操作MySQL数据库?PyMySQL连接方法

PyMySQL是Python与MySQL数据库交互的首选库之一,它提供了一套直观的API,让开发者能够轻松地建立连接、执行SQL查询并管理数据。它的纯Python实现意味着安装和部署通常非常简单,不需要额外的编译步骤或系统依赖,这对于许多项目来说是一个巨大的便利。

Python怎样操作MySQL数据库?PyMySQL连接方法

解决方案

使用PyMySQL连接MySQL数据库的核心步骤通常包括导入库、建立连接、创建游标、执行SQL、提交或回滚事务,以及最终关闭连接。

首先,你需要确保PyMySQL已经安装: pip install PyMySQL

Python怎样操作MySQL数据库?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:是管理游标的好习惯,它能确保游标在不再需要时被正确关闭。

Python怎样操作MySQL数据库?PyMySQL连接方法

如何优化PyMySQL的性能与资源管理?

在实际应用中,尤其是在高并发或长时间运行的服务里,如何高效地管理数据库连接是至关重要的。直接每次请求都建立和关闭连接的开销是巨大的,这不仅影响性能,还可能耗尽数据库服务器的连接资源。我曾经在开发一个高并发API服务时,就遇到过因为连接管理不当导致的数据库连接池耗尽问题,服务直接挂掉,那真是个头疼的下午。

一个常见的解决方案是使用连接池(Connection Pool)。连接池预先创建并维护一定数量的数据库连接,当应用程序需要连接时,它从池中获取一个空闲连接;使用完毕后,连接不是关闭,而是归还到池中,供其他请求复用。这样就大大减少了连接的建立和关闭开销。

PyMySQL本身不内置连接池功能,但你可以使用第三方库,比如DBUtils(特别是其中的PooledDBPersistentDB模块),或者自己实现一个简单的连接池。

以下是一个使用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方法

对于INSERTUPDATEDELETE等操作也是同理。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服务器的端口被防火墙挡住了,这种低级错误也常常让人抓狂。

以下是一些常见的故障排除步骤和思路:

  1. 检查连接参数

    • 主机(host):确保你连接的是正确的IP地址或域名。如果是本地开发,通常是localhost127.0.0.1
    • 端口(port):MySQL默认端口是3306。如果MySQL服务器使用了非标准端口,请确保在配置中指定。
    • 用户名(user)密码(password):这是最常见的错误来源。仔细检查,区分大小写。
    • 数据库名(database):确保你要连接的数据库存在且名称正确。
  2. 网络连通性

    • 防火墙:检查客户端机器和MySQL服务器上的防火墙规则。确保MySQL端口(默认3306)是开放的,并且允许你的客户端IP连接。
    • 网络路径:尝试从客户端机器pingMySQL服务器的IP地址,看是否能通。
    • Telnet/nc测试:在客户端机器上使用telnet 3306nc -vz 3306来测试端口是否开放且可达。如果连接被拒绝或超时,很可能是网络或防火墙问题。
  3. MySQL服务器状态

    • 服务是否运行:登录到MySQL服务器,检查MySQL服务是否正在运行。例如,在Linux上可以使用sudo systemctl status mysqlsudo service mysql status
    • 最大连接数:MySQL服务器有最大连接数限制。如果连接池或应用程序创建了太多连接,可能会超出此限制。可以通过SHOW VARIABLES LIKE 'max_connections';查看,并通过SHOW STATUS LIKE 'Threads_connected';查看当前连接数。
    • 用户权限:确保连接使用的MySQL用户拥有从客户端IP连接到指定数据库的权限。例如,用户可能只被授权从localhost连接,而你却尝试从远程IP连接。可以使用SELECT user, host FROM mysql.user;查看用户权限配置。
  4. 编码问题

    • 如果数据库中的字符显示乱码,或者插入数据时报错Incorrect string value,很可能是编码问题。在PyMySQL连接时,确保charset参数与数据库的编码设置一致,通常推荐使用utf8mb4
    • 检查数据库、表和列的字符集设置。
  5. 错误日志

    • PyMySQL错误信息:Python代码中的try...except块会捕获pymysql.err下的各种异常,如OperationalError(连接或操作错误)、ProgrammingError(SQL语法错误)、IntegrityError(数据完整性错误,如唯一键冲突)。仔细阅读这些错误信息,它们通常会提供关键线索。
    • MySQL服务器错误日志:MySQL服务器有自己的错误日志文件(通常在/var/log/mysql/error.logmy.cnf中配置的路径)。查看这个日志文件,可能会发现服务器端拒绝连接或查询失败的具体原因。
  6. 代码审查

    • 资源未释放:确保连接和游标在使用完毕后都被正确关闭。虽然Python的垃圾回收机制会处理,但在高并发场景下,不及时关闭会耗尽资源。使用with语句是最佳实践。
    • SQL语法错误ProgrammingError通常指向SQL语句本身的问题。检查SQL的拼写、关键字、表名和列名是否正确。

通过系统地排查这些方面,通常能定位并解决PyMySQL连接和操作中的大部分问题。

理论要掌握,实操不能落!以上关于《Python连接MySQL教程,PyMySQL使用详解》的详细介绍,大家都掌握了吧!如果想要继续提升自己的能力,那么就来关注golang学习网公众号吧!

豆包账号注册与登录详细教程豆包账号注册与登录详细教程
上一篇
豆包账号注册与登录详细教程
HTML5time标签如何提升SEO?
下一篇
HTML5time标签如何提升SEO?
查看更多
最新文章
查看更多
课程推荐
  • 前端进阶之JavaScript设计模式
    前端进阶之JavaScript设计模式
    设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
    542次学习
  • GO语言核心编程课程
    GO语言核心编程课程
    本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
    509次学习
  • 简单聊聊mysql8与网络通信
    简单聊聊mysql8与网络通信
    如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
    497次学习
  • JavaScript正则表达式基础与实战
    JavaScript正则表达式基础与实战
    在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
    487次学习
  • 从零制作响应式网站—Grid布局
    从零制作响应式网站—Grid布局
    本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
    484次学习
查看更多
AI推荐
  • AI边界平台:智能对话、写作、画图,一站式解决方案
    边界AI平台
    探索AI边界平台,领先的智能AI对话、写作与画图生成工具。高效便捷,满足多样化需求。立即体验!
    394次使用
  • 讯飞AI大学堂免费AI认证证书:大模型工程师认证,提升您的职场竞争力
    免费AI认证证书
    科大讯飞AI大学堂推出免费大模型工程师认证,助力您掌握AI技能,提升职场竞争力。体系化学习,实战项目,权威认证,助您成为企业级大模型应用人才。
    405次使用
  • 茅茅虫AIGC检测:精准识别AI生成内容,保障学术诚信
    茅茅虫AIGC检测
    茅茅虫AIGC检测,湖南茅茅虫科技有限公司倾力打造,运用NLP技术精准识别AI生成文本,提供论文、专著等学术文本的AIGC检测服务。支持多种格式,生成可视化报告,保障您的学术诚信和内容质量。
    542次使用
  • 赛林匹克平台:科技赛事聚合,赋能AI、算力、量子计算创新
    赛林匹克平台(Challympics)
    探索赛林匹克平台Challympics,一个聚焦人工智能、算力算法、量子计算等前沿技术的赛事聚合平台。连接产学研用,助力科技创新与产业升级。
    641次使用
  • SEO  笔格AIPPT:AI智能PPT制作,免费生成,高效演示
    笔格AIPPT
    SEO 笔格AIPPT是135编辑器推出的AI智能PPT制作平台,依托DeepSeek大模型,实现智能大纲生成、一键PPT生成、AI文字优化、图像生成等功能。免费试用,提升PPT制作效率,适用于商务演示、教育培训等多种场景。
    549次使用
微信登录更方便
  • 密码登录
  • 注册账号
登录即同意 用户协议隐私政策
返回登录
  • 重置密码