Python连接MySQL数据库入门教程

想要让Python轻松连接MySQL数据库？这篇教程将带你一步步掌握Python与MySQL的“对话”技巧。首先，你需要安装PyMySQL等数据库连接库，它们就像一座桥梁，让你的Python代码能够通过API发送SQL指令给MySQL服务器。教程详细讲解了如何建立连接，配置host、user、password等关键参数，并推荐使用环境变量来保护敏感信息。同时，文章还深入剖析了常见的认证问题，如用户名密码错误、权限不足以及MySQL 8.0+版本caching_sha2_password插件的不兼容问题，并提供了相应的解决方案。此外，还介绍了如何通过executemany()方法进行高效的批量操作，以及如何利用try-except-finally结构进行全面的错误和异常处理，确保数据一致性和程序稳定性。无论你是新手还是有一定经验的开发者，都能从中受益，轻松实现Python与MySQL的无缝连接。

Python连接MySQL需使用PyMySQL等库作为“桥梁”，通过API发送SQL指令。首先安装库并建立连接，注意配置host、user、password等参数，推荐使用环境变量避免硬编码。常见认证问题包括用户名密码错误、权限不足（如'@localhost'与'@%'区别）、MySQL 8.0+的caching_sha2_password插件不兼容，可通过升级库或修改用户认证方式解决。网络防火墙或端口阻塞3306也需排查。批量操作应使用executemany()方法减少通信开销，并结合事务管理提升效率与一致性；超大规模导入可考虑LOAD DATA INFILE。异常处理必须使用try-except-finally结构，捕获pymysql.Error及其子类（如IntegrityError、OperationalError），出错时回滚事务，确保连接最终关闭，并结合logging记录日志以利排查。

Python连接MySQL数据库，通常会依赖一个专门的数据库连接库，比如PyMySQL或mysql-connector-python。这背后其实就是通过这些库提供的API，让Python代码能够发送SQL指令给MySQL服务器，并接收执行结果。它提供了一个桥梁，让你的Python程序能和数据库“对话”。

解决方案

要让Python和MySQL“说上话”，我们得先准备好“翻译官”——也就是对应的连接库。我个人比较偏爱PyMySQL，因为它用起来感觉更轻量、更Pythonic一些。

首先，安装这个库：

pip install PyMySQL

安装好之后，就可以开始写代码了。一个基本的连接和查询流程大概是这样的：

import pymysql

# 1. 建立连接
# 注意：这里需要替换成你自己的数据库信息
# 我通常会把这些敏感信息放在环境变量或者配置文件里，避免硬编码
try:
    connection = pymysql.connect(
        host='localhost',          # 数据库服务器地址
        user='your_username',      # 数据库用户名
        password='your_password',  # 数据库密码
        database='your_database',  # 要连接的数据库名
        charset='utf8mb4',         # 字符集，防止中文乱码
        cursorclass=pymysql.cursors.DictCursor # 返回字典形式的结果，我个人觉得更方便
    )
    print("数据库连接成功！")

    # 2. 创建游标对象
    # 游标就像是你在数据库里操作的“光标”，所有的SQL命令都通过它来执行
    with connection.cursor() as cursor:
        # 3. 执行SQL查询
        sql_query = "SELECT id, name, email FROM users WHERE status = %s"
        cursor.execute(sql_query, ('active',)) # 参数化查询是好习惯，防止SQL注入

        # 4. 获取查询结果
        # fetchall() 获取所有结果
        # fetchone() 获取一个结果
        # fetchmany(size) 获取指定数量的结果
        results = cursor.fetchall()
        for row in results:
            print(row) # 如果cursorclass是DictCursor，这里会打印字典

        # 5. 执行数据插入操作
        insert_sql = "INSERT INTO products (name, price) VALUES (%s, %s)"
        product_data = ('新产品A', 99.99)
        cursor.execute(insert_sql, product_data)
        print(f"插入了一条数据，ID: {cursor.lastrowid}") # 获取最后插入的ID

        # 6. 执行数据更新操作
        update_sql = "UPDATE products SET price = %s WHERE name = %s"
        cursor.execute(update_sql, (109.99, '新产品A'))
        print(f"更新了 {cursor.rowcount} 条数据。")

        # 7. 提交事务
        # 对于INSERT, UPDATE, DELETE操作，需要提交才能真正写入数据库
        connection.commit()
        print("事务已提交。")

except pymysql.Error as e:
    print(f"数据库操作失败: {e}")
    # 发生错误时，回滚事务，撤销所有未提交的更改
    if 'connection' in locals() and connection.open:
        connection.rollback()
        print("事务已回滚。")
finally:
    # 8. 关闭连接
    # 无论成功失败，都确保关闭连接，释放资源
    if 'connection' in locals() and connection.open:
        connection.close()
        print("数据库连接已关闭。")

这段代码基本涵盖了连接、查询、插入、更新和关闭的完整流程。关键在于pymysql.connect建立连接，cursor.execute执行SQL，以及connection.commit()提交更改。

Python连接MySQL时常见的认证问题有哪些？

在Python里连MySQL，最头疼的往往不是代码逻辑，而是连接不上。这其中，认证问题绝对是重灾区。我见过不少人卡在这里，搞得焦头烂额。

一个很常见的场景是用户名或密码错误。这听起来有点傻，但真的很多人会因为输错一个字母、少一个符号，或者复制粘贴的时候多了一个空格而连接失败。所以，第一步永远是仔细核对你的user和password。

接着是用户权限不足。即使用户名密码都对，如果这个用户没有权限从你的程序所在的IP地址连接，或者没有权限访问你指定的数据库，那也会报错。MySQL的用户权限管理很细致，比如'your_username'@'localhost'和'your_username'@'%'是不同的，前者只允许本地连接，后者允许任何IP连接（但出于安全考虑，一般不推荐直接用%）。检查一下你的MySQL用户对应的Host是不是允许你的程序连接，以及它是否有SELECT, INSERT, UPDATE, DELETE等必要的权限。

再来就是MySQL 8.0+版本带来的认证插件问题。MySQL 8.0默认的认证插件从mysql_native_password改成了caching_sha2_password。如果你的Python连接库或者MySQL客户端版本比较老，可能不支持这个新的认证方式，就会出现Authentication plugin 'caching_sha2_password' cannot be loaded之类的错误。解决办法通常有两个：

1. 升级你的连接库：确保PyMySQL或者mysql-connector-python是最新版本，它们通常会支持新的认证方式。
2. 修改MySQL用户认证方式：如果你不想升级库或者有其他兼容性考虑，可以在MySQL服务器上把特定用户的认证方式改回mysql_native_password。例如：

   ALTER USER 'your_username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
   FLUSH PRIVILEGES;

   但这样做会降低一点安全性，所以要权衡。

最后，还有网络防火墙或端口问题。有时候数据库服务器和你的Python程序不在同一台机器上，网络中间可能存在防火墙阻挡了3306端口（MySQL默认端口）的访问。或者，MySQL服务器本身配置了只监听特定IP，没有监听你程序所在的IP。这些都需要服务器管理员去排查。

如何高效地在Python中执行批量数据库操作？

当你需要一次性插入、更新大量数据时，如果每条数据都单独执行一次INSERT或UPDATE语句，那效率会非常低，因为每次执行都会有网络往返和数据库解析SQL的开销。这时候，批量操作就显得尤为重要了。

最直接也是最推荐的方式是使用连接库提供的executemany()方法。这个方法专门为批量操作设计，它会把多条数据打包成一个批次发送给数据库，大大减少了网络通信和SQL解析的次数。

比如，我们要插入几百条用户数据：

import pymysql

# 假设已经建立了连接 connection
# ... (连接代码同上) ...

try:
    with connection.cursor() as cursor:
        users_to_insert = [
            ('Alice', 'alice@example.com'),
            ('Bob', 'bob@example.com'),
            ('Charlie', 'charlie@example.com'),
            # ... 更多用户数据 ...
        ]

        # 准备SQL模板
        insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"

        # 使用executemany批量执行
        cursor.executemany(insert_sql, users_to_insert)

        # 提交事务
        connection.commit()
        print(f"成功插入了 {cursor.rowcount} 条用户数据。")

except pymysql.Error as e:
    print(f"批量插入失败: {e}")
    connection.rollback()
    print("事务已回滚。")
finally:
    if connection.open:
        connection.close()

executemany()的第一个参数是SQL语句，第二个参数是一个列表的列表或列表的元组，每个子列表或子元组代表一条记录的数据。

除了executemany()，另一个提升效率的关键是事务管理。将一系列相关的数据库操作放在一个事务中，要么全部成功，要么全部失败回滚。这不仅保证了数据的一致性，也能在一定程度上提高性能，因为数据库在事务结束前不需要频繁地写入磁盘（当然，这取决于数据库的隔离级别和配置）。在批量操作时，把executemany()放在一个事务里是标准做法，避免了每次操作都进行隐式提交。

最后，如果你面对的是超大规模的数据导入（比如上百万行），executemany()可能仍然不够快。这时候，可以考虑使用MySQL的LOAD DATA INFILE命令。这种方式通常需要你先将数据准备成一个CSV或其他文本文件，然后让MySQL直接从这个文件加载数据。Python可以通过cursor.execute()来执行LOAD DATA INFILE语句，但需要确保MySQL服务器有权限访问这个文件，并且文件路径是服务器上的路径。这在实际项目中比较少见，通常用于数据迁移或大规模数据初始化。

Python操作数据库时如何处理错误和异常？

在任何实际的应用程序中，数据库操作都不能假设永远成功。网络波动、数据库宕机、SQL语法错误、数据完整性约束冲突等等，都可能导致操作失败。所以，健壮的错误和异常处理是必不可少的。

Python中处理异常的核心机制是try...except...finally块。对于数据库操作，我们通常会把所有可能出错的代码放在try块里，然后在except块里捕获特定的数据库异常，进行处理，最后在finally块里确保资源被清理（比如关闭数据库连接）。

以PyMySQL为例，所有的数据库相关异常都继承自pymysql.Error。你可以捕获这个通用的异常，也可以根据需要捕获更具体的子类，例如pymysql.IntegrityError（数据完整性错误，如违反唯一约束）、pymysql.OperationalError（操作错误，如连接失败、权限不足）等。

import pymysql
from pymysql.err import OperationalError, IntegrityError, ProgrammingError

connection = None # 初始化连接为None，方便finally块判断
try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database',
        charset='utf8mb4'
    )
    print("数据库连接成功。")

    with connection.cursor() as cursor:
        # 尝试执行一个可能导致完整性错误的插入
        # 假设 products 表的 name 字段是唯一的
        try:
            insert_sql = "INSERT INTO products (name, price) VALUES (%s, %s)"
            cursor.execute(insert_sql, ('独一无二的产品', 123.45))
            cursor.execute(insert_sql, ('独一无二的产品', 543.21)) # 再次插入同名产品，会报错
            connection.commit()
            print("数据插入成功。")
        except IntegrityError as e:
            print(f"数据完整性错误：{e}。可能是重复的唯一键。")
            connection.rollback() # 发生错误时回滚事务
            print("事务已回滚。")
        except ProgrammingError as e:
            print(f"SQL语法或编程错误：{e}。请检查SQL语句或参数。")
            connection.rollback()
            print("事务已回滚。")

        # 尝试执行一个错误的查询，比如表名写错
        try:
            cursor.execute("SELECT * FROM non_existent_table")
            print("查询成功。")
        except OperationalError as e:
            print(f"操作错误：{e}。可能是表不存在或连接问题。")
            # 这里通常不需要回滚，因为查询操作不会修改数据
        except pymysql.Error as e: # 捕获其他所有PyMySQL错误
            print(f"发生未知数据库错误: {e}")
            connection.rollback()
            print("事务已回滚。")


except OperationalError as e:
    print(f"数据库连接失败：{e}。请检查主机、端口、用户名、密码或网络。")
except pymysql.Error as e:
    print(f"发生更广泛的数据库错误: {e}")
finally:
    if connection and connection.open: # 确保连接对象存在且是打开状态
        connection.close()
        print("数据库连接已关闭。")

在这个例子中，我们捕获了OperationalError（通常用于连接问题）、IntegrityError（用于唯一约束、外键约束等完整性问题）和ProgrammingError（用于SQL语法错误）。一个好的实践是，对于会修改数据库状态（INSERT, UPDATE, DELETE）的操作，一旦发生错误，就应该立即调用connection.rollback()来撤销当前事务中所有未提交的更改，保持数据库状态的一致性。

日志记录也是异常处理中不可或缺的一部分。将捕获到的异常信息、堆栈跟踪以及相关的上下文数据记录到日志文件中，对于后续的故障排查和系统维护至关重要。我通常会结合Python的logging模块来做这件事，而不是简单地print出来。这样，即使程序崩溃了，也能通过日志找到问题所在。

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~