Python 数据库（一）：使用 mysql-connector-python 操作 MySQL 数据库

大家好，我是水滴~~

当涉及到使用 python 操作 mysql 数据库时，mysql-connector-python 库是一个强大而常用的选择。该库提供了与 MySQL 数据库的交互功能，使您能够执行各种数据库操作，如连接数据库、执行查询和插入数据等。在本文中，我们将介绍如何使用 mysql-connector-python 库来操作 MySQL 数据库。

文章内容包含大量的示例代码，希望能够帮助新手同学快速入门。

《Python入门核心技术》专栏总目录・点这里

前言

MySQL Connector/Python 是一个用于在 Python 和 MySQL 数据库之间进行交互的官方 MySQL 驱动程序。它提供了一个简单而强大的接口，使开发人员能够轻松地连接到 MySQL 数据库，并执行查询、插入、更新和删除等操作。

以下是 MySQL Connector/Python 库的一些关键特点和功能：

• 官方支持：MySQL Connector/Python 是由 MySQL 官方团队开发和维护的，因此它与 MySQL 数据库的兼容性非常好。它遵循最新的 MySQL 协议，并与 MySQL 服务器紧密集成。
• 简单易用：MySQL Connector/Python 提供了一个直观而简单的 API，使开发人员能够轻松地连接到数据库、执行查询和修改数据。它提供了一组简洁的方法和函数，使数据库操作变得简单而直观。
• 支持事务：MySQL Connector/Python 支持事务，允许您在多个数据库操作之间维护一致性。您可以开始一个事务、执行一系列操作，并在需要时提交或回滚事务。
• 支持预处理语句：MySQL Connector/Python 支持预处理语句，可以提高执行相同查询多次的效率。通过使用预处理语句，您可以预先准备查询并多次执行，而不必每次都重新解析和编译查询。
• 支持连接池：MySQL Connector/Python 支持连接池，允许您在应用程序中管理和重用数据库连接。连接池可以提高性能和可伸缩性，特别是在高并发环境下。
• SSL/TLS 加密支持：MySQL Connector/Python 支持通过 SSL/TLS 使用加密连接连接到 MySQL 服务器。这提供了对数据传输的安全保护，尤其在通过公共网络连接到数据库时非常重要。
• 跨平台支持：MySQL Connector/Python 可在多个平台上运行，包括 Windows、Linux 和 macOS。这使得它成为开发人员在不同环境中构建和部署 Python 应用程序时的理想选择。

总之，MySQL Connector/Python 是一个功能强大且易于使用的库，用于在 Python 和 MySQL 数据库之间进行交互。它提供了广泛的功能和选项，使开发人员能够灵活地连接、查询和修改 MySQL 数据库，从而构建出高效和可靠的应用程序。无论是简单的查询还是复杂的数据库操作，MySQL Connector/Python 都是一个值得考虑的工具。

下面是相关链接：

一、连接器的版本

在安装 MySQL 连接器之前需要先确定安装的版本，如果你的 MySQL 版本在 5.6 及以上，可以直接安装最新版本的连接器。

下表总结了连接器 与 MySQL、Python的版本对照表：

二、安装 mysql-connector-python 库

在使用之前，需要先安装 mysql-connector-python 库，下面列出一些常用的 pip 命令。

安装最新版本：

pip install mysql-connector-python

安装到指定版本：

pip install mysql-connector-python==8.0.26

请注意，确保指定的版本在PyPI（Python软件包索引）上可用。你可以在PyPI网站上搜索mysql-connector-python来查找可用的版本列表。

升级到最新版本：

pip install mysql-connector-python --upgrade

升级到指定版本：

pip install mysql-connector-python==8.0.26 --upgrade

三、连接 MySQL 数据库

使用 mysql-connector-python 库连接 MySQL 数据库，需要使用 connect() 函数，该函数会创建与 MySQL 服务器的连接并返回一个 MySQLConnection对象。

3.1 创建一个连接

以下示例展示了如何连接到 MySQL 服务器：

import mysql.connector

# 连接 MySQL
***x = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 关闭连接
***x.close()

在上述代码中，host 表示数据库主机地址，user 表示用户名，password 表示密码，database 表示要连接的数据库名称。

3.2 使用字典定义连接参数

也可以将连接参数封装到字典中，然后以这个字典传递参数：

import mysql.connector

# 将连接信息存入字典中
conn_params = {
    'host': 'your_host',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database'
}

# 使用 ** 运算符传递字典参数
***x = mysql.connector.connect(**conn_params)

# 关闭连接
***x.close()

3.3 从配置文件中获取连接信息

在生产环境中，通常是将连接信息写在配置文件中的，需要从配置文件中获取，然后连接 MySQL。

创建一个配置文件（例如，config.ini），其中包含MySQL数据库的连接参数。配置文件的示例内容如下：

[mysql]
host = your_host
user = your_username
password = your_password
database = your_database

使用configparser模块来解析配置文件并获取连接参数。以下是示例代码：

import configparser
import mysql.connector

# 创建配置解析器对象
config = configparser.ConfigParser()
# 读取配置文件
config.read('config.ini')

# 从配置文件中获取连接参数，并封装至字典中
conn_params = {
    'host': config.get('mysql', 'host'),
    'user': config.get('mysql', 'user'),
    'password': config.get('mysql', 'password'),
    'database': config.get('mysql', 'database')
}

# 连接到MySQL数据库
***x = mysql.connector.connect(**conn_params)

# 关闭连接
***x.close()

3.4 捕获连接异常

在连接数据库时也可能因为意外而发生异常，我们需要捕获它：

import mysql.connector
from mysql.connector import errorcode

try:
    # 连接 MySQL 数据库
    ***x = mysql.connector.connect(
        host="your_host",
        user="your_username",
        password="your_password",
        database="your_database"
    )
    # 捕获异常
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_A***ESS_DENIED_ERROR:
        print("账号或密码错误")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("数据库不存在")
    else:
        print(err)
else:
    # 关闭连接
    ***x.close()

获取更多连接参数，请参见官网：https://dev.mysql.***/doc/connector-python/en/connector-python-connectargs.html

四、执行 SQL 查询

连接成功后，可以使用 cursor() 函数获取游标对象，然后使用游标对象执行 SQL 查询。

4.1 查询并返回多条数据（元组列表）

下面示例将演示如何执行查询语句，并将查询结果放到列表中，代码如下：

import mysql.connector

# 获取连接
***x = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 获取游标
cursor = ***x.cursor()

# 执行查询
cursor.execute("SELECT * FROM oss_file")

# 将查询结果放到列表中
rows = cursor.fetchall()

# 遍历查询结果
for row in rows:
    print(row)

# 关闭游标和连接
cursor.close()
***x.close()

在上述代码中，execute() 函数用于执行 SQL 查询，fetchall() 函数用于获取查询结果集，其返回值为列表，列表中存储的是元组，每个元组就是一条数据。

4.2 查询并返回一条数据（元组）

下面示例将演示如何执行查询语句，并返回一条查询结果，代码如下：

import mysql.connector

# 获取连接
***x = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 获取游标
cursor = ***x.cursor()

# 执行查询
cursor.execute("SELECT * FROM oss_file limit 1")

# 获取查询结果
row = cursor.fetchone()
print(row)

# 关闭游标和连接
cursor.close()
***x.close()

在上述代码中，execute() 函数用于执行 SQL 查询，fetchone() 函数用于获取一条查询结果，其返回值为元组。

4.3 查询时传递参数

执行查询时也可以进行参数传递，可以将参数组织为元组或列表，并将其作为execute()方法的第二个参数传递。以下是一个示例：

import mysql.connector

# 获取连接
***x = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 获取游标
cursor = ***x.cursor()

# 查询参数，参数可以是元组或列表
params = ('app/', 1)
# 执行查询
cursor.execute("SELECT * FROM oss_file where prefix = %s and status = %s", params=params)

# 将查询结果放到列表中
rows = cursor.fetchall()

# 遍历查询结果
for row in rows:
    print(row)

# 关闭游标和连接
cursor.close()
***x.close()

上述示例中，传递了两个参数，而SQL中使用 %s 作为点位符，表示参数的位置 。在SQL查询语句中，%s占位符通常是按照出现的顺序进行参数替换的。当你在查询语句中使用多个%s占位符时，传递给数据库驱动程序的参数值应按照相应的顺序进行传递。

参数也可以是字典类型，如下示例：

import mysql.connector

# 获取连接
***x = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="oss"
)

# 获取游标
cursor = ***x.cursor()

# 定义字典类型的参数
params = {
    'prefix': 'app/',
    'status': 1
}
# 执行查询
cursor.execute("SELECT * FROM oss_file where prefix = %(prefix)s and status = %(status)s", params=params)

# 将查询结果放到列表中
rows = cursor.fetchall()

# 遍历查询结果
for row in rows:
    print(row)

# 关闭游标和连接
cursor.close()
***x.close()

当传递字典类型的参数，占位符就需要指明具体的参数了，如上面的 %(prefix)s 和 %(status)s。

五、执行 SQL 插入、更新和删除操作

可以使用 execute() 函数来执行 SQL 插入、更新和删除操作。

示例代码如下：

import mysql.connector

# 获取连接
***x = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 获取游标
cursor = ***x.cursor()

# SQL 语句和参数
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
params = ("水滴", "青岛")

# 执行插入语句
cursor.execute(sql, params)

# 提交事务
***x.***mit()

# 获取受影响的行数
print('插入行数', cursor.rowcount)

# 关闭游标和连接
cursor.close()
***x.close()

在上述代码中，execute() 函数用于执行 SQL 插入操作，***mit() 函数用于提交事务。而更新、删除和插入是一样的，这里就不演示了。

六、支持事务

mysql-connector-python 库支持事务功能。可以使用 start_transaction() 函数开始一个事务，使用 ***mit() 函数提交事务，使用 rollback() 函数回滚事务。

示例代码如下：

import mysql.connector

# 建立与数据库的连接
***x = mysql.connector.connect(
    user='your_username',
    password='your_password',
    host='your_host',
    database='your_database'
)

try:
    # 开始事务
    ***x.start_transaction()

    # 创建游标对象
    cursor = ***x.cursor()

    # 执行第一个插入操作
    insert_query1 = "INSERT INTO table1 (column1, column2) VALUES (%s, %s)"
    data1 = ('value1', 'value2')
    cursor.execute(insert_query1, data1)

    # 执行第二个插入操作
    insert_query2 = "INSERT INTO table2 (column1, column2) VALUES (%s, %s)"
    data2 = ('value3', 'value4')
    cursor.execute(insert_query2, data2)

    # 提交事务
    ***x.***mit()

    # 关闭游标和连接
    cursor.close()
    ***x.close()

    print("事务执行成功")

except mysql.connector.Error as error:
    # 回滚事务
    ***x.rollback()

    # 关闭游标和连接
    cursor.close()
    ***x.close()

    print(f"事务执行失败: {error}")

在示例代码中，我们首先建立与数据库的连接。然后，在 try 块中开始事务，并创建游标对象。接下来，我们执行两个插入操作，分别向 table1 和 table2 表插入数据。如果所有的操作都执行成功，我们通过调用 ***x.***mit() 提交事务。如果任何一个操作失败，我们通过调用 ***x.rollback() 回滚事务。最后，我们关闭游标和连接。

七、支持连接池

mysql-connector-python 库还支持连接池功能，可以使用 mysql.connector.pooling 模块来创建连接池。

示例代码如下：

import mysql.connector
from mysql.connector import pooling

# 创建连接池配置
pool_config = {
    "pool_name": "my_pool",
    "pool_size": 5,
    "user": "your_username",
    "password": "your_password",
    "host": "your_host",
    "database": "your_database"
}

# 创建连接池
***x_pool = mysql.connector.pooling.MySQLConnectionPool(**pool_config)

# 从连接池获取连接
***x = ***x_pool.get_connection()

try:
    # 创建游标对象
    cursor = ***x.cursor()

    # 执行查询操作
    query = "SELECT * FROM your_table"
    cursor.execute(query)

    # 获取查询结果
    result = cursor.fetchall()
    for row in result:
        print(row)

    # 关闭游标
    cursor.close()

except mysql.connector.Error as error:
    print(f"查询执行出错: {error}")

finally:
    # 将连接返回给连接池
    ***x.close()

在示例代码中，我们首先创建连接池配置，其中包括连接池的名称、大小以及数据库连接的相关信息。然后，我们使用这些配置创建连接池对象 ***x_pool。接下来，我们通过调用 ***x_pool.get_connection() 从连接池中获取一个连接对象 ***x。在 try 块中，我们创建游标对象并执行查询操作。最后，无论查询是否成功，我们都通过调用 ***x_pool.release_connection(***x) 将连接返回给连接池。

总结

这样，我们就完成了使用 mysql-connector-python 库来操作 MySQL 数据库的流程。
总结起来，使用 mysql-connector-python 库操作 MySQL 数据库包括建立数据库连接、执行查询操作、执行插入操作、提交事务和关闭连接等步骤。通过这个强大的库，您可以轻松地与 MySQL 数据库进行交互，并实现各种数据库操作。
希望本文能够帮助您入门使用 mysql-connector-python 库来操作 MySQL 数据库。祝你在使用 Python 进行数据库操作时取得成功！