第125集 MySQL数据库连接
一、课程导入
在前面的课程中，我们已经学习了SQLite数据库的基本操作和SQL的增删改查功能。SQLite是一种轻量级的嵌入式数据库，适合小型应用和原型开发。但在实际的企业级应用中，我们更多地会使用功能更强大的关系型数据库，如MySQL、PostgreSQL等。
今天，我们将学习如何使用Python连接MySQL数据库。MySQL是目前最流行的开源关系型数据库之一，广泛应用于各种规模的企业应用、网站和数据存储系统中。掌握Python与MySQL的连接技术，将为我们开发更复杂的数据库应用打下坚实的基础。
二、MySQL数据库简介
2.1 MySQL的特点
MySQL是一个开源的关系型数据库管理系统（RDBMS），具有以下主要特点：
- 开源免费：遵循GPL协议，可免费使用和修改
- 跨平台：支持Windows、Linux、macOS等多种操作系统
- 高性能：优化的查询算法和索引机制，处理大量数据的能力强
- 高可靠性：支持事务处理，保证数据的一致性和完整性
- 高可扩展性：支持主从复制、集群等多种扩展方式
- 丰富的功能：支持存储过程、触发器、视图、索引等高级功能
- 良好的兼容性：支持SQL标准，与其他关系型数据库兼容
2.2 MySQL的应用场景
MySQL广泛应用于以下场景：
- 网站和Web应用：如WordPress、Drupal等内容管理系统
- 企业级应用：如客户关系管理（CRM）、企业资源规划（ERP）系统
- 电子商务平台：如在线商店、支付系统
- 数据仓库：存储和分析大量业务数据
- 移动应用后端：为移动应用提供数据存储和访问服务
三、Python连接MySQL的准备工作
3.1 安装MySQL数据库
在使用Python连接MySQL之前，需要先安装MySQL数据库。可以从MySQL官网下载适合自己操作系统的安装包进行安装。
注意：本课程主要讲解Python与MySQL的连接方法，不详细介绍MySQL的安装过程。如果您还没有安装MySQL，可以参考MySQL官方文档或相关教程进行安装。
3.2 安装Python的MySQL驱动
Python需要通过MySQL驱动程序来连接和操作MySQL数据库。常用的MySQL驱动有以下几种：
- mysql-connector-python：MySQL官方提供的Python驱动
- PyMySQL：纯Python实现的MySQL驱动
- MySQLdb：基于C语言实现的MySQL驱动，性能较好但安装较复杂
在本课程中，我们将使用mysql-connector-python，这是MySQL官方提供的驱动，具有良好的兼容性和稳定性。
安装命令：
pip install mysql-connector-python四、Python连接MySQL的基本步骤
使用Python连接MySQL数据库通常需要以下步骤：
- 导入MySQL驱动模块
- 建立数据库连接
- 创建游标对象
- 执行SQL语句
- 处理查询结果
- 关闭游标
- 关闭数据库连接
4.1 导入MySQL驱动模块
首先需要导入mysql-connector-python模块：
import mysql.connector4.2 建立数据库连接
使用mysql.connector.connect()函数建立与MySQL数据库的连接，需要提供以下参数：
- host：MySQL服务器地址（如localhost或IP地址）
- user：用户名
- password：密码
- database：要连接的数据库名称（可选）
- port：MySQL服务器端口号（默认3306）
- charset：字符集（如utf8mb4）
示例：
# 建立数据库连接
conn = mysql.connector.connect(
host='localhost', # 服务器地址
user='root', # 用户名
password='123456', # 密码
database='testdb' # 数据库名称
)4.3 创建游标对象
连接建立后，需要创建一个游标对象来执行SQL语句：
# 创建游标对象
cursor = conn.cursor()4.4 执行SQL语句
使用游标对象的execute()方法执行SQL语句：
# 执行SQL语句
cursor.execute('SELECT * FROM students')4.5 处理查询结果
对于查询语句（如SELECT），可以使用以下方法获取查询结果：
- **fetchone()**：获取一条记录
- **fetchmany(size)**：获取指定数量的记录
- **fetchall()**：获取所有记录
示例：
# 获取所有记录
results = cursor.fetchall()
# 遍历结果
for row in results:
print(row)4.6 关闭游标和连接
操作完成后，需要关闭游标和数据库连接，释放资源：
# 关闭游标
cursor.close()
# 关闭连接
conn.close()五、完整的连接示例
下面是一个完整的Python连接MySQL的示例代码：
import mysql.connector
# 建立数据库连接
try:
conn = mysql.connector.connect(
host='localhost',
user='root',
password='123456',
database='testdb'
)
print("✅ 成功连接到MySQL数据库")
# 创建游标对象
cursor = conn.cursor()
# 执行SQL语句
cursor.execute('SHOW TABLES')
# 获取查询结果
tables = cursor.fetchall()
print("数据库中的表：")
for table in tables:
print(table)
finally:
# 关闭游标和连接
if 'cursor' in locals() and cursor is not None:
cursor.close()
if 'conn' in locals() and conn is not None:
conn.close()
print("✅ 数据库连接已关闭")六、连接参数详解
6.1 基本连接参数
| 参数名 | 描述 | 默认值 |
|---|---|---|
| host | MySQL服务器地址 | localhost |
| user | 用户名 | - |
| password | 密码 | - |
| database | 数据库名称 | None |
| port | 端口号 | 3306 |
| charset | 字符集 | utf8mb4 |
| collation | 排序规则 | utf8mb4_general_ci |
| autocommit | 自动提交事务 | False |
6.2 连接池参数
对于需要频繁连接数据库的应用，可以使用连接池来提高性能：
from mysql.connector.pooling import MySQLConnectionPool
# 创建连接池
pool = MySQLConnectionPool(
pool_name="my_pool",
pool_size=5,
host='localhost',
user='root',
password='123456',
database='testdb'
)
# 从连接池获取连接
conn = pool.get_connection()
# 使用连接执行操作
# ...
# 关闭连接（实际是返回连接池）
conn.close()七、执行SQL语句的方法
7.1 执行单条SQL语句
使用execute()方法执行单条SQL语句：
# 执行创建表的语句
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
gender VARCHAR(10),
email VARCHAR(100)
)
''')
print(f"✅ 创建表成功")7.2 执行带参数的SQL语句
使用参数化查询可以防止SQL注入攻击，提高安全性：
# 插入数据（参数化查询）
name = "张三"
age = 18
gender = "男"
email = "zhangsan@example.com"
cursor.execute(
"INSERT INTO students (name, age, gender, email) VALUES (%s, %s, %s, %s)",
(name, age, gender, email)
)
# 提交事务
conn.commit()
print(f"✅ 插入数据成功，影响 {cursor.rowcount} 条记录")注意：在MySQL的参数化查询中，使用%s作为占位符，而不是Python的格式化字符串{}或SQLite的?。
7.3 批量执行SQL语句
使用executemany()方法可以批量执行SQL语句，提高效率：
# 批量插入数据
students = [
("李四", 19, "男", "lisi@example.com"),
("王五", 20, "女", "wangwu@example.com"),
("赵六", 21, "男", "zhaoliu@example.com")
]
cursor.executemany(
"INSERT INTO students (name, age, gender, email) VALUES (%s, %s, %s, %s)",
students
)
# 提交事务
conn.commit()
print(f"✅ 批量插入数据成功，影响 {cursor.rowcount} 条记录")八、事务处理
MySQL支持事务处理，可以确保一组SQL操作的原子性、一致性、隔离性和持久性（ACID特性）。
8.1 事务的基本概念
- 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败回滚
- 一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务
- 持久性（Durability）：事务一旦提交，对数据库的改变是永久性的
8.2 事务的使用方法
在Python中，可以使用以下方法进行事务处理：
try:
# 开始事务（默认情况下，MySQL自动开始事务）
# 执行SQL操作1
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
# 执行SQL操作2
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
# 提交事务
conn.commit()
print("✅ 事务提交成功")
except Exception as e:
# 回滚事务
conn.rollback()
print(f"❌ 事务执行失败，已回滚：{e}")九、错误处理
在连接和操作MySQL数据库时，可能会遇到各种错误，如连接失败、SQL语法错误、权限不足等。我们需要使用try-except语句来捕获和处理这些错误。
9.1 常见的MySQL错误类型
- ProgrammingError：SQL语法错误、表不存在等
- OperationalError：连接失败、服务器关闭等
- IntegrityError：违反主键约束、外键约束等
- DataError：数据类型不匹配、数据过长等
- NotSupportedError：不支持的SQL语句或功能
- InterfaceError：驱动程序接口错误
9.2 错误处理示例
import mysql.connector
from mysql.connector import errorcode
try:
# 建立数据库连接
conn = mysql.connector.connect(
host='localhost',
user='root',
password='wrong_password', # 故意使用错误的密码
database='testdb'
)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("❌ 用户名或密码错误")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("❌ 数据库不存在")
elif err.errno == errorcode.ER_DBACCESS_DENIED_ERROR:
print("❌ 用户没有数据库访问权限")
else:
print(f"❌ 数据库连接错误：{err}")十、使用配置文件管理连接参数
在实际开发中，为了方便管理和维护，我们通常会将数据库连接参数存储在配置文件中，而不是直接硬编码在代码中。
10.1 使用ini配置文件
创建一个config.ini文件：
[mysql]
host = localhost
user = root
password = 123456
database = testdb
port = 3306
charset = utf8mb4然后使用Python的configparser模块读取配置文件：
import configparser
import mysql.connector
# 读取配置文件
config = configparser.ConfigParser()
config.read('config.ini')
# 获取MySQL配置
mysql_config = config['mysql']
# 建立数据库连接
conn = mysql.connector.connect(
host=mysql_config['host'],
user=mysql_config['user'],
password=mysql_config['password'],
database=mysql_config['database'],
port=int(mysql_config['port']),
charset=mysql_config['charset']
)
print("✅ 成功连接到MySQL数据库")10.2 使用环境变量
也可以将数据库连接参数设置为环境变量：
import os
import mysql.connector
# 从环境变量获取配置
host = os.environ.get('MYSQL_HOST', 'localhost')
user = os.environ.get('MYSQL_USER', 'root')
password = os.environ.get('MYSQL_PASSWORD', '123456')
database = os.environ.get('MYSQL_DATABASE', 'testdb')
port = int(os.environ.get('MYSQL_PORT', '3306'))
# 建立数据库连接
conn = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database,
port=port
)
print("✅ 成功连接到MySQL数据库")十一、最佳实践
- 使用参数化查询：防止SQL注入攻击
- 及时关闭连接：使用try-finally或上下文管理器确保连接关闭
- 使用连接池：对于频繁连接数据库的应用，使用连接池提高性能
- 处理事务：对需要保证数据一致性的操作使用事务
- 错误处理：捕获和处理可能发生的各种错误
- 配置管理：使用配置文件或环境变量管理连接参数
- 定期备份：定期备份数据库，防止数据丢失
- 使用索引：为经常查询的列创建索引，提高查询效率
十二、常见问题解答
12.1 连接MySQL失败怎么办？
- 检查MySQL服务是否已启动
- 检查连接参数是否正确（host、port、user、password等）
- 检查防火墙是否允许MySQL的端口（默认3306）
- 检查用户是否有远程连接权限
12.2 为什么会出现"Access denied for user"错误？
- 用户名或密码错误
- 用户没有访问指定数据库的权限
- 用户没有远程连接权限（默认root用户只能本地连接）
12.3 如何解决中文乱码问题？
- 在连接参数中设置charset为utf8mb4
- 确保数据库和表的字符集也是utf8mb4
- 在Python代码中使用UTF-8编码
12.4 如何处理大量数据的插入？
- 使用批量插入（executemany()方法）
- 调整MySQL的max_allowed_packet参数
- 考虑使用LOAD DATA INFILE语句
十三、课程总结
在本集课程中，我们学习了如何使用Python连接MySQL数据库：
- MySQL简介：了解了MySQL的特点和应用场景
- 准备工作：安装MySQL驱动mysql-connector-python
- 连接步骤：导入模块、建立连接、创建游标、执行SQL、处理结果、关闭连接
- SQL执行：单条执行、参数化查询、批量执行
- 事务处理：确保数据的一致性和完整性
- 错误处理：捕获和处理各种数据库错误
- 配置管理：使用配置文件或环境变量管理连接参数
- 最佳实践：提高代码质量和性能的建议
通过本课程的学习，我们掌握了Python与MySQL的连接技术，为开发更复杂的数据库应用打下了基础。在后续的课程中，我们将学习PostgreSQL数据库的连接和操作，以及ORM框架的使用，进一步提升我们的数据库开发能力。
十四、课后练习
- 安装MySQL数据库和mysql-connector-python驱动
- 创建一个名为school的数据库
- 在school数据库中创建students表（包含id、name、age、gender、email、class_id字段）
- 使用Python连接到school数据库
- 向students表中插入5条学生记录
- 查询所有学生信息并打印
- 查询年龄大于20的学生
- 更新一条学生记录
- 删除一条学生记录
- 使用事务处理模拟转账操作
要求：
- 使用参数化查询防止SQL注入
- 添加适当的错误处理
- 使用配置文件管理连接参数
- 所有操作完成后关闭数据库连接