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 都是一个值得考虑的工具。

下面是相关链接：
项目链接官方指南https://dev.mysql.com/doc/connector-python/PyPI 地址https://pypi.org/project/mysql-connector-python/

一、连接器的版本

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

下表总结了连接器 与 MySQL、Python的版本对照表：
连接器版本MySQL 版本Python 版本8.x8.1, 8.0, 5.7, 5.63.12 (8.2.0), 3.11, 3.10, 3.9, 3.88.08.0, 5.7, 5.6, 5.53.11, 3.10, 3.9, 3.8, 3.7, (在 8.0.29 之前的 3.6), ( 在 8.0.24 之前的 2.7 和 3.5)2.2 (后续为 8.0)5.7, 5.6, 5.53.5, 3.4, 2.72.15.7, 5.6, 5.53.5, 3.4, 2.7, 2.62.05.7, 5.6, 5.53.5, 3.4, 2.7, 2.61.25.7, 5.6, 5.5 (5.1, 5.0, 4.1)3.4, 3.3, 3.2, 3.1, 2.7, 2.6

二、安装 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
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database")# 关闭连接
cnx.close()

在上述代码中，

host

表示数据库主机地址，

user

表示用户名，

password

表示密码，

database

表示要连接的数据库名称。

3.2 使用字典定义连接参数

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

import mysql.connector

# 将连接信息存入字典中
conn_params ={'host':'your_host','user':'your_username','password':'your_password','database':'your_database'}# 使用 ** 运算符传递字典参数
cnx = mysql.connector.connect(**conn_params)# 关闭连接
cnx.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数据库
cnx = mysql.connector.connect(**conn_params)# 关闭连接
cnx.close()

3.4 捕获连接异常

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

import mysql.connector
from mysql.connector import errorcode

try:# 连接 MySQL 数据库
    cnx = 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_ACCESS_DENIED_ERROR:print("账号或密码错误")elif err.errno == errorcode.ER_BAD_DB_ERROR:print("数据库不存在")else:print(err)else:# 关闭连接
    cnx.close()

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

四、执行 SQL 查询

连接成功后，可以使用

cursor()

函数获取游标对象，然后使用游标对象执行 SQL 查询。

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

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

import mysql.connector

# 获取连接
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database")# 获取游标
cursor = cnx.cursor()# 执行查询
cursor.execute("SELECT * FROM oss_file")# 将查询结果放到列表中
rows = cursor.fetchall()# 遍历查询结果for row in rows:print(row)# 关闭游标和连接
cursor.close()
cnx.close()

在上述代码中，

execute()

函数用于执行 SQL 查询，

fetchall()

函数用于获取查询结果集，其返回值为列表，列表中存储的是元组，每个元组就是一条数据。

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

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

import mysql.connector

# 获取连接
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database")# 获取游标
cursor = cnx.cursor()# 执行查询
cursor.execute("SELECT * FROM oss_file limit 1")# 获取查询结果
row = cursor.fetchone()print(row)# 关闭游标和连接
cursor.close()
cnx.close()

在上述代码中，

execute()

函数用于执行 SQL 查询，

fetchone()

函数用于获取一条查询结果，其返回值为元组。

4.3 查询时传递参数

执行查询时也可以进行参数传递，可以将参数组织为元组或列表，并将其作为

execute()

方法的第二个参数传递。以下是一个示例：

import mysql.connector

# 获取连接
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database")# 获取游标
cursor = cnx.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()
cnx.close()

上述示例中，传递了两个参数，而SQL中使用

%s

作为点位符，表示参数的位置 。在SQL查询语句中，

%s

占位符通常是按照出现的顺序进行参数替换的。当你在查询语句中使用多个

%s

占位符时，传递给数据库驱动程序的参数值应按照相应的顺序进行传递。

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

import mysql.connector

# 获取连接
cnx = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="oss")# 获取游标
cursor = cnx.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()
cnx.close()

当传递字典类型的参数，占位符就需要指明具体的参数了，如上面的

%(prefix)s

和

%(status)s

。

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

可以使用

execute()

函数来执行 SQL 插入、更新和删除操作。

示例代码如下：

import mysql.connector

# 获取连接
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database")# 获取游标
cursor = cnx.cursor()# SQL 语句和参数
sql ="INSERT INTO customers (name, address) VALUES (%s, %s)"
params =("水滴","青岛")# 执行插入语句
cursor.execute(sql, params)# 提交事务
cnx.commit()# 获取受影响的行数print('插入行数', cursor.rowcount)# 关闭游标和连接
cursor.close()
cnx.close()

在上述代码中，

execute()

函数用于执行 SQL 插入操作，

commit()

函数用于提交事务。而更新、删除和插入是一样的，这里就不演示了。

六、支持事务

mysql-connector-python

库支持事务功能。可以使用

start_transaction()

函数开始一个事务，使用

commit()

函数提交事务，使用

rollback()

函数回滚事务。

示例代码如下：

import mysql.connector

# 建立与数据库的连接
cnx = mysql.connector.connect(
    user='your_username',
    password='your_password',
    host='your_host',
    database='your_database')try:# 开始事务
    cnx.start_transaction()# 创建游标对象
    cursor = cnx.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)# 提交事务
    cnx.commit()# 关闭游标和连接
    cursor.close()
    cnx.close()print("事务执行成功")except mysql.connector.Error as error:# 回滚事务
    cnx.rollback()# 关闭游标和连接
    cursor.close()
    cnx.close()print(f"事务执行失败: {error}")

在示例代码中，我们首先建立与数据库的连接。然后，在

try

块中开始事务，并创建游标对象。接下来，我们执行两个插入操作，分别向

table1

和

table2

表插入数据。如果所有的操作都执行成功，我们通过调用

cnx.commit()

提交事务。如果任何一个操作失败，我们通过调用

cnx.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"}# 创建连接池
cnx_pool = mysql.connector.pooling.MySQLConnectionPool(**pool_config)# 从连接池获取连接
cnx = cnx_pool.get_connection()try:# 创建游标对象
    cursor = cnx.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:# 将连接返回给连接池
    cnx.close()

在示例代码中，我们首先创建连接池配置，其中包括连接池的名称、大小以及数据库连接的相关信息。然后，我们使用这些配置创建连接池对象

cnx_pool

。接下来，我们通过调用

cnx_pool.get_connection()

从连接池中获取一个连接对象

cnx

。在

try

块中，我们创建游标对象并执行查询操作。最后，无论查询是否成功，我们都通过调用

cnx_pool.release_connection(cnx)

将连接返回给连接池。

总结

这样，我们就完成了使用

mysql-connector-python

库来操作 MySQL 数据库的流程。
总结起来，使用

mysql-connector-python

库操作 MySQL 数据库包括建立数据库连接、执行查询操作、执行插入操作、提交事务和关闭连接等步骤。通过这个强大的库，您可以轻松地与 MySQL 数据库进行交互，并实现各种数据库操作。
希望本文能够帮助您入门使用

mysql-connector-python

库来操作 MySQL 数据库。祝你在使用 Python 进行数据库操作时取得成功！