python怎么连接mysql数据库_python数据库操作指南

冰火之心

发布时间：2025-08-27 17:32:01

375人浏览过

来源于php中文网

原创

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数据库_python数据库操作指南

Python连接MySQL数据库，通常会依赖一个专门的数据库连接库，比如

PyMySQL

或

mysql-connector-python

。这背后其实就是通过这些库提供的API，让Python代码能够发送SQL指令给MySQL服务器，并接收执行结果。它提供了一个桥梁，让你的Python程序能和数据库“对话”。

解决方案

要让Python和MySQL“说上话”，我们得先准备好“翻译官”——也就是对应的连接库。我个人比较偏爱

PyMySQL

，因为它用起来感觉更轻量、更Pythonic一些。

首先，安装这个库：

pip install PyMySQL

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

立即学习“Python免费学习笔记（深入）”；

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

之类的错误。解决办法通常有两个：

升级你的连接库：确保
```
PyMySQL
```
或者
```
mysql-connector-python
```
是最新版本，它们通常会支持新的认证方式。
修改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。这些都需要服务器管理员去排查。

Cardify卡片工坊

使用Markdown一键生成精美的小红书知识卡片

下载

如何高效地在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("数据库连接已关闭。")

在这个例子中，我们捕获了