Python怎么连接MySQL数据库_PyMySQL与mysql-connector-python使用教程

下次还敢

发布时间：2025-09-12 17:30:02

471人浏览过

来源于php中文网

原创

Python连接MySQL常用PyMySQL和mysql-connector-python，前者纯Python实现易部署，后者官方出品性能强兼容好；核心步骤包括连接、游标、执行、提交、关闭；选择依据项目需求与团队偏好。

python怎么连接mysql数据库_pymysql与mysql-connector-python使用教程

Python连接MySQL数据库，通常我们会借助第三方库来实现。目前最主流且推荐的两个选择是

PyMySQL

和

mysql-connector-python

。它们都提供了一套Pythonic的API接口，让开发者能够方便地与MySQL服务器进行交互，执行SQL查询、管理数据等操作。选择哪个，往往取决于项目的具体需求、团队偏好以及对官方支持的考量。

解决方案

要让Python程序与MySQL数据库“对话”，核心步骤无外乎那么几步：建立连接、创建游标、执行SQL语句、处理结果、提交或回滚事务，最后关闭连接。下面我将分别用

PyMySQL

和

mysql-connector-python

来演示这个过程。

使用 PyMySQL

PyMySQL

是一个纯Python实现的MySQL客户端库，这意味着它不需要编译任何C扩展，安装和部署起来相对简单。

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

安装 PyMySQL:
```
pip install PyMySQL
```

连接与操作示例:

import pymysql

# 数据库连接参数
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4', # 确保支持emoji等字符
    'cursorclass': pymysql.cursors.DictCursor # 返回字典形式的结果
}

def execute_query(query, params=None, fetch_one=False):
    """
    一个简单的查询执行函数
    """
    conn = None
    try:
        conn = pymysql.connect(**DB_CONFIG)
        with conn.cursor() as cursor:
            cursor.execute(query, params)
            if query.strip().upper().startswith('SELECT'):
                if fetch_one:
                    return cursor.fetchone()
                return cursor.fetchall()
            else:
                conn.commit() # 非查询操作需要提交
                return cursor.rowcount
    except pymysql.Error as e:
        print(f"数据库操作失败: {e}")
        if conn:
            conn.rollback() # 发生错误时回滚
        return None
    finally:
        if conn:
            conn.close()

# 示例：创建表
create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
print(f"创建表结果: {execute_query(create_table_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:
    print(f"插入数据结果: {execute_query(insert_sql, user_data)}")

# 示例：查询数据
select_sql = "SELECT id, name, email FROM users WHERE name = %s"
user_info = execute_query(select_sql, ("张三",), fetch_one=True)
print(f"查询到用户: {user_info}")

select_all_sql = "SELECT id, name, email FROM users"
all_users = execute_query(select_all_sql)
print(f"所有用户: {all_users}")

# 示例：更新数据
update_sql = "UPDATE users SET email = %s WHERE name = %s"
print(f"更新结果: {execute_query(update_sql, ('zhangsan_new@example.com', '张三'))}")

# 示例：删除数据
delete_sql = "DELETE FROM users WHERE name = %s"
print(f"删除结果: {execute_query(delete_sql, ('李四',))}")

使用 mysql-connector-python

mysql-connector-python

是Oracle官方提供的MySQL连接器，它通常被认为在某些方面（如性能、与MySQL新特性的兼容性）更具优势，因为它可能包含C扩展。

安装 mysql-connector-python:
```
pip install mysql-connector-python
```

连接与操作示例:

import mysql.connector
from mysql.connector import Error

# 数据库连接参数
DB_CONFIG_CONNECTOR = {
    'host': 'localhost',
    'database': 'your_database',
    'user': 'your_username',
    'password': 'your_password',
    # 'port': 3306, # 默认3306，可省略
    'charset': 'utf8mb4' # 同样重要
}

def execute_query_connector(query, params=None, fetch_one=False):
    """
    一个简单的查询执行函数 (使用mysql-connector-python)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**DB_CONFIG_CONNECTOR)
        if conn.is_connected():
            cursor = conn.cursor(dictionary=True) # 返回字典形式的结果
            cursor.execute(query, params)

            if query.strip().upper().startswith('SELECT'):
                if fetch_one:
                    return cursor.fetchone()
                return cursor.fetchall()
            else:
                conn.commit()
                return cursor.rowcount
    except Error as e:
        print(f"数据库操作失败 (mysql-connector): {e}")
        if conn:
            conn.rollback()
        return None
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

# 示例：创建表 (与PyMySQL示例相同，确保数据库中没有重复表)
create_table_sql_conn = """
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
print(f"创建表结果 (connector): {execute_query_connector(create_table_sql_conn)}")

# 示例：插入数据
insert_sql_conn = "INSERT INTO products (name, price) VALUES (%s, %s)"
products_to_insert = [
    ("笔记本电脑", 8999.00),
    ("机械键盘", 599.50)
]
for product_data in products_to_insert:
    print(f"插入数据结果 (connector): {execute_query_connector(insert_sql_conn, product_data)}")

# 示例：查询数据
select_product_sql_conn = "SELECT id, name, price FROM products WHERE name = %s"
product_info = execute_query_connector(select_product_sql_conn, ("笔记本电脑",), fetch_one=True)
print(f"查询到产品 (connector): {product_info}")

select_all_products_sql_conn = "SELECT id, name, price FROM products"
all_products = execute_query_connector(select_all_products_sql_conn)
print(f"所有产品 (connector): {all_products}")

如何选择适合你的Python MySQL连接库？

在Python生态中，连接MySQL确实有好几个选择，

PyMySQL

和

mysql-connector-python

就是其中最常用的。我个人觉得，选择哪个库，其实更多是看你的项目场景和个人偏好。

PyMySQL

最大的优点在于它是纯Python实现。这意味着它没有C语言扩展的依赖，安装起来非常省心，通常

pip install PyMySQL

就能搞定，不太会遇到编译问题。对于大多数日常脚本、小型Web应用或者那些对部署环境有严格限制（比如一些轻量级容器）的项目，

PyMySQL

的表现已经足够优秀了。它的API设计也挺直观，用起来很顺手。如果你追求的是简洁、易部署，并且对极致性能没有那么苛刻的要求，

PyMySQL

会是一个非常好的选择。

而

mysql-connector-python

，作为Oracle官方维护的连接器，它最大的优势就是“官方”二字。这意味着它通常能更好地支持MySQL的新特性，兼容性也可能更强，尤其是在一些企业级应用中，对官方支持的信赖度会更高。它内部可能会包含一些C语言的优化，理论上在处理大量数据或高并发场景下，性能可能略胜一筹。但相对地，它的安装有时可能会因为系统缺少C编译器或其他依赖而稍微复杂一点点。如果你是在构建大型、对性能和稳定性有极高要求的企业级应用，或者你的团队对使用官方工具链有明确要求，那么

mysql-connector-python

无疑是更稳妥、更专业的选择。

我自己的经验是，如果只是写一些数据分析脚本，或者开发一个简单的Flask/Django小应用，我通常会倾向于用

PyMySQL

，因为它够轻量，能快速启动项目。但如果是在一个需要长期维护、可能会扩展到很复杂功能的系统中，我可能会更倾向于使用

mysql-connector-python

，毕竟有官方背书，心里会更踏实一些。当然，最终还是要看实际测试和项目团队的共识。

连接MySQL时常见的坑与解决方案

连接数据库这事儿，看起来简单，但实际操作中总会遇到一些意想不到的“坑”。我这就把一些我个人踩过的、或者经常看到别人踩的坑，以及对应的解决方案分享一下。

连接失败：Access denied for user...
- 问题描述: 数据库报“拒绝访问”的错误，通常是用户名、密码不对，或者你尝试连接的IP地址没有被授权。
- 解决方案:
  - 检查凭证: 仔细核对
```
user
```
    和
```
password
```
    是否正确。有时候手滑输错一个字符就得找半天。
  - 检查主机权限: MySQL的用户权限是绑定到特定主机的。例如
```
'your_username'@'localhost'
```
    和
```
'your_username'@'%'
```
    是不同的。如果你从远程机器连接，确保MySQL用户被授权从该IP地址（或
```
%
```
    表示所有IP）连接。可以使用
```
GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'your_ip_address' IDENTIFIED BY 'your_password';
```
    来授权。授权后记得
```
FLUSH PRIVILEGES;
```
    。
连接失败：Can't connect to MySQL server on '...' (111)

Facetune
一款在线照片和视频编辑工具，允许用户创建AI头像

下载
- 问题描述: 无法连接到MySQL服务器。这通常是网络问题、服务器未运行或端口被占用/防火墙阻挡。
- 解决方案:
  - MySQL服务状态: 确认MySQL服务是否正在运行。在Linux上可以用
```
sudo systemctl status mysql
```
    或
```
sudo service mysql status
```
    。
  - IP地址和端口: 检查
```
host
```
    和
```
port
```
    是否正确。默认端口是3306。
  - 防火墙: 服务器的防火墙可能阻止了来自你Python程序的连接。你需要允许3306端口的入站连接。例如在Linux上，
```
sudo ufw allow 3306
```
    或配置
```
iptables
```
    。
  - 网络连通性: 尝试从Python程序运行的机器
```
ping
```
    一下MySQL服务器的IP，或者用
```
telnet your_mysql_host 3306
```
    看看端口是否开放。
乱码问题：字符集不匹配
- 问题描述: 从数据库读取的数据出现乱码，或者写入的数据在数据库中显示为问号。
- 解决方案:
  - 统一字符集: 最常见的解决方案是确保Python连接时使用的字符集与数据库、表、列的字符集保持一致。对于多语言和emoji支持，强烈建议使用
```
utf8mb4
```
    。
  - 在连接参数中明确指定
```
charset='utf8mb4'
```
    。
  - 检查数据库、表、列的字符集是否也是
```
utf8mb4
```
    。可以通过
```
SHOW VARIABLES LIKE 'character_set_database';
```
    和
```
SHOW CREATE TABLE your_table_name;
```
    来查看。
SQL注入漏洞
- 问题描述: 如果你直接用字符串拼接的方式来构建SQL查询，比如
```
"SELECT * FROM users WHERE name = '" + username + "'"
```
  ，那么恶意用户就可以通过输入特殊字符来执行任意SQL，造成严重的安全漏洞。
- 解决方案:
  - 使用参数化查询: 这是最重要、最有效的防范措施。Python的数据库API（DB-API 2.0）标准支持参数化查询。你只需要在SQL语句中使用占位符（
```
%s
```
    或
```
?
```
    ，取决于库），然后将参数作为元组或列表传递给
```
execute()
```
    方法。数据库驱动会负责正确地转义这些参数。
```
# 错误示例 (容易SQL注入)
# username = "admin' OR '1'='1"
# cursor.execute(f"SELECT * FROM users WHERE name = '{username}'")
```
  正确示例 (参数化查询)
  
  username = "admin" cursor.execute("SELECT * FROM users WHERE name = %s", (username,)) # PyMySQL/mysql-connector-python 常用 %s
资源泄露：忘记关闭连接和游标
- 问题描述: 每次操作完数据库，如果没有显式地关闭连接（
```
conn.close()
```
  )和游标（
```
cursor.close()
```
  )，可能会导致数据库连接池耗尽，或者服务器资源被长时间占用。
- 解决方案:
  - 使用
    with
    语句: Python的
```
with
```
    语句是管理资源（如文件、数据库连接）的利器。它能确保在代码块执行完毕后，无论是否发生异常，资源都能被正确关闭。
```
import pymysql
try:
conn = pymysql.connect(**DB_CONFIG)
with conn.cursor() as cursor: # 游标会自动关闭
    # 执行数据库操作
    cursor.execute("SELECT 1")
conn.commit() # 事务提交
except pymysql.Error as e:
if conn:
    conn.rollback()
finally:
if conn:
    conn.close() # 连接也需要手动关闭或在连接池中管理
```
    对于连接，如果使用连接池，则不需要手动关闭，而是将连接归还给连接池。

这些都是我在实际开发中经常碰到的问题，提前了解并采取预防措施，能省下不少调试时间。

如何在Python应用中更优雅地管理MySQL连接？

在实际的Python应用，尤其是在Web服务或长时间运行的后台任务中，仅仅知道如何连接和执行查询是远远不够的。连接的管理，比如如何高效复用、如何安全存储凭证、如何处理异常，这些都直接影响到应用的性能、稳定性和安全性。我个人在构建系统时，会特别关注以下几个方面，让数据库连接的管理变得更“优雅”。

连接池（Connection Pooling）：性能与资源复用的基石

为什么需要它？ 每次建立数据库连接都有不小的开销，包括TCP握手、身份验证等。在高并发场景下，频繁地创建和销毁连接会显著降低应用性能，并可能耗尽数据库服务器的连接资源。连接池就是为了解决这个问题而生。
工作原理: 连接池维护了一组预先建立好的数据库连接。当应用需要连接时，它从池中“借用”一个连接；当操作完成后，连接会被“归还”到池中，而不是真正关闭。这样就大大减少了连接建立和关闭的开销。

如何实现？

许多Web框架（如Django、SQLAlchemy）都内置或提供了连接池的集成。
对于纯DB-API的使用，可以使用像
```
DBUtils
```
这样的库来创建连接池。

PyMySQL示例（使用DBUtils的PooledDB）:

from DBUtils.PooledDB import PooledDB
import pymysql

DB_CONFIG_POOL = {
    'host': 'localhost',
    'port': 3306,
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# 创建连接池，mincached表示启动时创建的最小连接数，maxcached表示最多可缓存的连接数
# maxconnections表示允许的最大连接数，blocking表示当连接池满时是否阻塞等待
POOL = PooledDB(pymysql, mincached=5, maxcached=10, maxconnections=20, blocking=True, **DB_CONFIG_POOL)

def get_pooled_connection():
    return POOL.connection()

# 示例使用
def query_with_pool(sql, params=None):
    conn = None
    try:
        conn = get_pooled_connection() # 从池中获取连接
        with conn.cursor() as cursor:
            cursor.execute(sql, params)
            if sql.strip().upper().startswith('SELECT'):
                return cursor.fetchall()
            else:
                conn.commit()
                return cursor.rowcount
    except pymysql.Error as e:
        print(f"数据库操作失败 (带连接池): {e}")
        if conn:
            conn.rollback()
        return None
    finally:
        if conn:
            conn.close() # 归还连接到池中，而不是真正关闭

我个人觉得，在任何需要处理并发请求的应用中，连接池都是一个必须考虑的组件。它能极大地提升应用的响应速度和资源利用率。