还在MySQL Workbench和命令行之间反复横跳？还在为忘记SQL语法而频繁查文档？今天教你用Python统一天下，一个脚本搞定所有数据库操作！学完这篇，你会发现原来Python和MySQL的配合，比咖啡和牛奶还要丝滑。
一、开篇：为什么非要用Python操作MySQL？
“我直接用MySQL Workbench不香吗？为什么要多此一举写Python代码？”——这是很多初学者的真实想法。
说实话，我第一次也有这个疑问。直到有一次，我需要在凌晨3点定时更新10万条用户数据，用Workbench手动操作简直是噩梦。而用Python脚本？设置好定时任务，美美睡一觉，第二天数据自动更新完成。
Python + MySQL的黄金组合，到底香在哪里？
先看一个真实对比：
场景
纯手工操作
Python自动化
每天导出销售报表
开Workbench → 执行SQL → 导出CSV → 整理格式（30分钟/天）
运行脚本 → 自动生成精美报表（30秒/天）
批量导入1000个用户
一个个复制粘贴（手酸到怀疑人生）
读取Excel → 批量导入（1分钟搞定）
凌晨定时数据备份
定闹钟起床操作（谁用谁知道）
配置crontab任务（一劳永逸）
处理异常和错误
出错从头再来（心态崩了）
try...except自动重试（稳稳的幸福）
更关键的是，一旦你用Python把数据库操作封装起来，就会开启新的可能：
- 数据分析：直接Pandas读取数据，省去导出导入的麻烦
- Web应用：Flask/Django框架天然支持数据库操作
- 自动化运维：监控数据库状态，自动告警和修复
- 团队协作：代码共享，新同事不用重新学你的操作习惯
学完这一篇，你会收获什么？
- 告别手动操作：再也不用在Workbench和命令行之间切换
- 一键处理海量数据：批量导入、更新、删除变得轻松简单
- 构建可复用的工具库：封装成函数，随用随调
- 为后续学习铺路：这是学习Flask、Django等Web框架的必备基础
- 提升工作效率10倍：把重复劳动交给代码，专注更有价值的事情
准备好了吗？让我们开始这段解放双手的旅程！
二、环境准备：5分钟搞定开发环境
工欲善其事，必先利其器。咱们先把“厨房”准备好，再来“烧菜”。
第一步：检查你的“装备”
需要确保你的电脑上已经安装了：
- Python 3.8+（推荐3.11+，性能更好）
- MySQL 8.0+（本地或远程都可以）
- 一个代码编辑器（VS Code、PyCharm都行）
检查Python版本：
python --version
# 或
python3 --version
检查MySQL版本：
mysql --version
第二步：安装Python的MySQL驱动
这是连接Python和MySQL的“桥梁”。有多个选择，我推荐官方驱动：
# 安装mysql-connector-python（官方驱动，功能全面）
pip install mysql-connector-python==9.0.0
# 如果安装慢，可以用国内镜像
# pip install mysql-connector-python==9.0.0 -i https://pypi.tuna.tsinghua.edu.cn/simple
为什么推荐官方驱动？
- 官方维护，更新及时，bug少
- 支持连接池（高并发必备）
- 性能优化好
- 文档齐全，遇到问题好解决
第三步：创建测试数据库
打开MySQL，创建一个测试用的数据库：
-- 登录MySQL（如果本地没有密码，直接按回车）
mysql -u root -p
-- 创建数据库
CREATE DATABASE python_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 切换到新数据库
USE python_test;
-- 创建一个测试表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT CHECK (age >= 0),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 插入几条测试数据
INSERT INTO users (name, email, age) VALUES
('张三', 'zhangsan@example.com', 25),
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28);
到这里，准备工作就完成了。是不是很简单？
三、第一次握手：Python如何连接MySQL？
想象一下你要跟朋友打电话，需要：1.知道电话号码 2.拨号 3.对方接听。Python连接MySQL也是类似的三步。
基础连接：最简单的“打电话”方式
# basic_connection.py
import mysql.connector
# 配置连接参数（就像输入电话号码）
config = {
'host': 'localhost', # 数据库地址
'user': 'root', # 用户名
'password': '', # 密码（如果没有密码就留空）
'database': 'python_test', # 要连接的数据库
'charset': 'utf8mb4', # 字符编码（支持中文和emoji）
'port': 3306 # 端口号（MySQL默认3306）
}
try:
# 建立连接（拨号）
conn = mysql.connector.connect(**config)
print("🎉 连接成功！数据库版本：", conn.get_server_info())
# 创建游标（通话通道）
cursor = conn.cursor()
# 执行SQL（开始对话）
cursor.execute("SELECT * FROM users")
# 获取结果（听对方说话）
rows = cursor.fetchall()
print("📊 查询结果：")
for row in rows:
print(f" ID: {row[0]}, 姓名: {row[1]}, 邮箱: {row[2]}, 年龄: {row[3]}")
# 关闭游标和连接（挂电话）
cursor.close()
conn.close()
except mysql.connector.Error as err:
print(f"❌ 连接失败: {err}")
保存为basic_connection.py，然后运行：
python basic_connection.py
如果一切顺利，你会看到类似这样的输出：
plaintext
🎉 连接成功！数据库版本： 8.0.36
📊 查询结果：
ID: 1, 姓名: 张三, 邮箱: zhangsan@example.com, 年龄: 25
ID: 2, 姓名: 李四, 邮箱: lisi@example.com, 年龄: 30
ID: 3, 姓名: 王五, 邮箱: wangwu@example.com, 年龄: 28
恭喜！你已经完成了Python和MySQL的第一次“握手”。
连接参数详解：这些选项到底有什么用？
前面的config字典里有好几个参数，它们各自扮演什么角色？
参数
作用
示例值
必填
host
数据库服务器地址
'localhost'、'192.168.1.100'
是
user
登录用户名
'root'、'app_user'
是
password
登录密码
'mypassword123'
是（可为空）
database
要操作的数据库
'python_test'、'production_db'
可选
port
端口号
3306
可选（默认3306）
charset
字符编码
'utf8mb4'
强烈推荐
autocommit
是否自动提交事务
True、False
可选
**重点提示1 **：一定要用utf8mb4编码，不要用utf8。MySQL的utf8是阉割版，不支持emoji和某些特殊字符，而utf8mb4才是真正的全功能UTF-8。
**重点提示2 **：autocommit参数很关键。如果设为True，每次SQL执行后自动提交；如果设为False，需要手动conn.commit()。根据场景选择：
- 简单操作：用
True，省心 - 复杂事务：用
False，出错时可以rollback()
四、CRUD操作进阶：像操作字典一样操作数据库
掌握了基础连接，接下来咱们玩点实用的——增删改查（CRUD）。我会教你如何把数据库操作封装成函数，像操作Python字典一样简单。
1. 封装连接工具：打造你的“数据库管家”
每次都写一大段连接代码太麻烦了。咱们先封装一个工具函数：
# db_utils.py
import mysql.connector
from mysql.connector import Error
class DatabaseManager:
"""MySQL数据库管理工具类"""
def __init__(self, host='localhost', user='root', password='',
database='python_test', charset='utf8mb4'):
self.config = {
'host': host,
'user': user,
'password': password,
'database': database,
'charset': charset,
'autocommit': True, # 默认自动提交
'pool_size': 5 # 连接池大小（后续会用到）
}
self.connection = None
def connect(self):
"""建立数据库连接"""
try:
self.connection = mysql.connector.connect(** self.config)
print(f"✅ 连接成功！当前数据库：{self.config['database']}")
return True
except Error as e:
print(f"❌ 连接失败: {e}")
return False
def close(self):
"""关闭数据库连接"""
if self.connection and self.connection.is_connected():
self.connection.close()
print("🔌 连接已关闭")
def execute_query(self, query, params=None, fetch=True):
"""执行SQL查询"""
result = None
cursor = None
try:
if not self.connection or not self.connection.is_connected():
self.connect()
cursor = self.connection.cursor(dictionary=True) # 返回字典格式
if params:
cursor.execute(query, params)
else:
cursor.execute(query)
if fetch:
if query.strip().upper().startswith('SELECT'):
result = cursor.fetchall()
else:
result = cursor.rowcount # 返回影响的行数
cursor.close()
return result
except Error as e:
print(f"❌ SQL执行错误: {e}")
if cursor:
cursor.close()
return None
# 下面是会陆续添加的便捷方法
def get_all_users(self):
"""获取所有用户"""
return self.execute_query("SELECT * FROM users ORDER BY id")
def add_user(self, name, email, age):
"""添加新用户"""
query = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
return self.execute_query(query, (name, email, age), fetch=False)
def update_user_age(self, user_id, new_age):
"""更新用户年龄"""
query = "UPDATE users SET age = %s WHERE id = %s"
return self.execute_query(query, (new_age, user_id), fetch=False)
def delete_user(self, user_id):
"""删除用户"""
query = "DELETE FROM users WHERE id = %s"
return self.execute_query(query, (user_id,), fetch=False)
# 用法示例
if __name__ == "__main__":
db = DatabaseManager()
# 获取所有用户
users = db.get_all_users()
print("📋 所有用户列表：")
for user in users:
print(f" {user['id']}: {user['name']} ({user['email']})")
# 添加新用户
db.add_user("赵六", "zhaoliu@example.com", 35)
print("➕ 已添加新用户：赵六")
db.close()
这个DatabaseManager类就是我们的“数据库管家”，它封装了所有基础操作。后续我们会在它的基础上不断添加新功能。
2. 安全第一：防止SQL注入攻击
这里有个超级重要的知识点，关系到你整个系统的安全。
什么是SQL注入？
想象一下，你有个登录功能，代码是这样的：
# ❌ 危险！容易被SQL注入攻击
username = input("请输入用户名：")
password = input("请输入密码：")
sql = f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
cursor.execute(sql)
如果用户输入admin' -- 作为用户名，那么SQL语句变成：
SELECT * FROM users WHERE username='admin' -- ' AND password=''
--在SQL中是注释符，后面的密码验证就被跳过了！黑客可以直接登录管理员账户。
如何防御？用参数化查询！
# ✅ 安全！使用参数化查询
username = input("请输入用户名：")
password = input("请输入密码：")
sql = "SELECT * FROM users WHERE username=%s AND password=%s"
cursor.execute(sql, (username, password))
参数化查询的原理：
- SQL语句和参数分开传输
- 数据库先编译SQL（确定操作）
- 再把参数作为纯数据填入
- 参数中的特殊字符（如引号）会被转义
记住这个黄金法则：
- 永远不要用字符串拼接（
f"SELECT ... {变量}"） - 一定要用参数化查询（
cursor.execute(sql, (参数1, 参数2))）
3. 事务处理：要么全成功，要么全失败
事务是数据库的“原子操作”。比如银行转账：A账户减100元，B账户加100元。这两个操作必须一起成功或一起失败。
def transfer_money(self, from_id, to_id, amount):
"""转账操作（使用事务保证数据一致性）"""
try:
# 关闭自动提交，开启事务
self.connection.autocommit = False
cursor = self.connection.cursor()
# 第一步：从from_id账户扣款
cursor.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s AND balance >= %s",
(amount, from_id, amount)
)
if cursor.rowcount == 0:
raise Exception("扣款失败：余额不足或账户不存在")
# 第二步：向to_id账户存款
cursor.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, to_id)
)
# 提交事务
self.connection.commit()
print(f"✅ 转账成功：{from_id} → {to_id}, 金额：{amount}")
return True
except Exception as e:
# 回滚事务
self.connection.rollback()
print(f"❌ 转账失败: {e}")
return False
finally:
# 恢复自动提交模式
self.connection.autocommit = True
cursor.close()
事务的核心特性（ACID） ：
- 原子性：事务内的操作要么全做，要么全不做
- 一致性：事务前后数据保持合法状态
- 隔离性：多个事务并发执行互不干扰
- 持久性：事务提交后，修改永久保存
什么时候用事务？
- 银行转账、订单支付
- 库存增减操作
- 批量数据导入
- 任何需要“要么全成功，要么全失败”的场景
五、性能优化：连接池让高并发不再可怕
如果你的应用有很多用户同时访问（比如Web应用），每次都新建数据库连接会非常慢。这时候就需要连接池。
连接池原理：像共享单车一样复用连接
想象一下：每次骑车都买辆新车 vs 用共享单车。连接池就是数据库连接的“共享单车系统”。
# connection_pool.py
from mysql.connector import pooling
import threading
import time
class ConnectionPoolManager:
"""连接池管理器"""
_instance = None
_lock = threading.Lock()
def __new__(cls):
"""单例模式，确保只有一个连接池实例"""
with cls._lock:
if cls._instance is None:
cls._instance = super().__new__(cls)
return cls._instance
def __init__(self):
if not hasattr(self, 'pool'):
self.pool = None
self.init_pool()
def init_pool(self, host='localhost', user='root', password='',
database='python_test', pool_size=10):
"""初始化连接池"""
config = {
'host': host,
'user': user,
'password': password,
'database': database,
'charset': 'utf8mb4',
'pool_name': 'my_pool',
'pool_size': pool_size, # 最大连接数
'pool_reset_session': True
}
try:
self.pool = pooling.MySQLConnectionPool(**config)
print(f"🏊 连接池初始化成功！最大连接数：{pool_size}")
except Exception as e:
print(f"❌ 连接池初始化失败: {e}")
def get_connection(self):
"""从连接池获取连接"""
try:
conn = self.pool.get_connection()
return conn
except Exception as e:
print(f"❌ 获取连接失败: {e}")
return None
def return_connection(self, conn):
"""归还连接到连接池（实际会自动处理，这里演示原理）"""
if conn and conn.is_connected():
conn.close() # 实际是归还到池，不是真正关闭
# print("🔁 连接已归还到连接池")
# 使用示例：模拟多线程并发访问
def worker(worker_id):
"""模拟一个工作线程"""
pool_manager = ConnectionPoolManager()
# 获取连接
conn = pool_manager.get_connection()
if not conn:
print(f"线程 {worker_id} 获取连接失败")
return
try:
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()[0]
print(f"线程 {worker_id} 查询结果：{count} 个用户")
# 模拟一些数据库操作
time.sleep(0.1)
cursor.close()
finally:
# 归还连接（实际会自动处理）
pool_manager.return_connection(conn)
if __name__ == "__main__":
# 初始化连接池
pool_manager = ConnectionPoolManager()
# 模拟10个并发线程
threads = []
for i in range(10):
t = threading.Thread(target=worker, args=(i,))
threads.append(t)
t.start()
# 等待所有线程完成
for t in threads:
t.join()
print("🎯 所有线程任务完成！")
**连接池的核心参数 **：
参数
作用
推荐值
pool_size
最大连接数
10-50（根据服务器性能）
pool_name
连接池名称
便于多个连接池区分
pool_reset_session
重置会话
True（防止会话状态污染）
**连接池的好处 **：
- **性能提升 **：复用连接，避免重复创建开销
- **资源控制 **：防止连接数过多拖垮数据库
- **连接管理 **：自动处理连接的创建、验证、回收
什么时候用连接池？
- Web应用（Flask、Django）
- 多线程/多进程程序
- 高并发访问场景
- 需要频繁数据库操作的应用
六、错误处理：让程序优雅地“跌倒”再“爬起来”
程序出错不可怕，可怕的是出错后直接崩溃，数据不一致。好的错误处理能让程序“优雅地失败”。
1. 常见错误类型及处理方法
import mysql.connector
from mysql.connector import Error
def safe_database_operation(func):
"""数据库操作装饰器，统一错误处理"""
def wrapper(*args, **kwargs):
try:
return func(*args, **kwargs)
except mysql.connector.Error as err:
# 根据错误类型采取不同处理策略
error_code = err.errno
if error_code == 1045:
print("🔐 认证失败：用户名或密码错误")
# 可以记录日志、发送告警邮件
elif error_code == 2003:
print("🌐 连接失败：无法连接到数据库服务器")
# 可以尝试重连或切换到备用数据库
elif error_code == 1062:
print("⚡ 唯一约束冲突：数据已存在（如重复邮箱）")
# 提示用户数据重复
elif error_code == 1213:
print("🔄 死锁：多个事务互相等待")
# 可以稍后重试
elif error_code == 1054:
print("🔍 字段不存在：SQL语句中有不存在的列名")
# 检查SQL语句或数据库表结构
else:
print(f"❌ 数据库错误 [{error_code}]: {err}")
# 根据业务需求决定是否重新抛出异常
# raise # 如果需要上层处理
return None
return wrapper
# 使用装饰器
@safe_database_operation
def get_user_by_id(user_id):
"""根据ID获取用户"""
conn = mysql.connector.connect(
host='localhost',
user='root',
password='',
database='python_test'
)
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cursor.fetchone()
cursor.close()
conn.close()
return user
# 示例：尝试获取用户
user = get_user_by_id(999) # 不存在的用户
if user:
print(f"找到用户：{user['name']}")
else:
print("未找到用户或查询失败")
2. 重试机制：不抛弃，不放弃
网络不稳定时，一次失败不代表永远失败。合理的重试能提高系统可用性。
import time
from functools import wraps
def retry_on_failure(max_retries=3, delay=1, backoff=2):
"""重试装饰器"""
def decorator(func):
@wraps(func)
def wrapper(*args, **kwargs):
retries = 0
current_delay = delay
while retries < max_retries:
try:
return func(*args, **kwargs)
except Exception as e:
retries += 1
if retries == max_retries:
print(f"❌ 操作失败，已重试 {max_retries} 次: {e}")
raise # 达到最大重试次数，抛出异常
print(f"⚠️ 第 {retries} 次尝试失败，{current_delay} 秒后重试: {e}")
time.sleep(current_delay)
# 指数退避：每次重试等待时间加倍
current_delay *= backoff
return None
return wrapper
return decorator
@retry_on_failure(max_retries=3, delay=1, backoff=2)
def critical_database_operation():
"""关键数据库操作，失败后自动重试"""
# 模拟不稳定的网络环境
import random
if random.random() < 0.7: # 70%概率失败
raise Exception("网络不稳定，连接超时")
print("✅ 操作成功！")
return "重要数据"
# 尝试执行
result = critical_database_operation()
if result:
print(f"获取到结果：{result}")
**重试策略的要点 **：
- **设置上限 **：避免无限重试
- **指数退避 **：避免加重服务器压力
- **记录日志 **：方便排查问题
- **区分错误 **：有些错误重试也没用（如密码错误）
七、实战案例：搭建简易用户管理系统
理论知识讲完了，咱们来点真家伙。用一个完整的实战项目，把前面学的都串起来。
项目结构设计
user_management/
├── db/
│ ├── __init__.py
│ ├── models.py # 数据模型定义
│ ├── connection.py # 数据库连接管理
│ └── queries.py # SQL查询定义
├── services/
│ ├── __init__.py
│ └── user_service.py # 用户业务逻辑
├── utils/
│ ├── __init__.py
│ └── validators.py # 数据验证工具
├── scripts/
│ ├── init_db.py # 初始化数据库脚本
│ └── backup_db.py # 备份数据库脚本
├── main.py # 主程序入口
└── requirements.txt # 依赖列表
核心代码实现
1. 数据库连接管理（db/connection.py）
import mysql.connector
from mysql.connector import pooling
from mysql.connector import Error
class DatabaseConnection:
"""数据库连接管理（单例模式 + 连接池）"""
_instance = None
_pool = None
def __new__(cls):
if cls._instance is None:
cls._instance = super().__new__(cls)
return cls._instance
@classmethod
def init_pool(cls, config=None):
"""初始化连接池"""
if cls._pool:
return cls._pool
default_config = {
'host': 'localhost',
'user': 'root',
'password': '',
'database': 'user_management',
'charset': 'utf8mb4',
'pool_name': 'user_pool',
'pool_size': 10,
'pool_reset_session': True
}
if config:
default_config.update(config)
try:
cls._pool = pooling.MySQLConnectionPool(** default_config)
print(f"🏊 连接池初始化成功：{default_config['pool_name']}")
return cls._pool
except Error as e:
print(f"❌ 连接池初始化失败: {e}")
raise
@classmethod
def get_connection(cls):
"""获取数据库连接"""
if not cls._pool:
cls.init_pool()
try:
conn = cls._pool.get_connection()
return conn
except Error as e:
print(f"❌ 获取连接失败: {e}")
raise
2. 用户数据模型（db/models.py）
from datetime import datetime
class User:
"""用户实体类"""
def __init__(self, id=None, name=None, email=None, age=None,
created_at=None, updated_at=None):
self.id = id
self.name = name
self.email = email
self.age = age
self.created_at = created_at or datetime.now()
self.updated_at = updated_at or datetime.now()
def to_dict(self):
"""转换为字典（便于JSON序列化）"""
return {
'id': self.id,
'name': self.name,
'email': self.email,
'age': self.age,
'created_at': self.created_at.isoformat() if self.created_at else None,
'updated_at': self.updated_at.isoformat() if self.updated_at else None
}
@classmethod
def from_dict(cls, data):
"""从字典创建用户对象"""
return cls(
id=data.get('id'),
name=data.get('name'),
email=data.get('email'),
age=data.get('age'),
created_at=datetime.fromisoformat(data['created_at']) if data.get('created_at') else None,
updated_at=datetime.fromisoformat(data['updated_at']) if data.get('updated_at') else None
)
3. 用户服务（services/user_service.py）
from db.connection import DatabaseConnection
from db.models import User
class UserService:
"""用户服务类"""
def __init__(self):
self.db = DatabaseConnection()
def create_user(self, name, email, age):
"""创建新用户"""
conn = self.db.get_connection()
cursor = conn.cursor(dictionary=True)
try:
# 检查邮箱是否已存在
cursor.execute("SELECT id FROM users WHERE email = %s", (email,))
if cursor.fetchone():
return None, "邮箱已被注册"
# 插入新用户
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
(name, email, age)
)
# 获取刚插入的用户
user_id = cursor.lastrowid
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user_data = cursor.fetchone()
conn.commit()
# 转换为User对象
user = User(
id=user_data['id'],
name=user_data['name'],
email=user_data['email'],
age=user_data['age'],
created_at=user_data['created_at'],
updated_at=user_data['updated_at']
)
return user, "创建成功"
except Exception as e:
conn.rollback()
return None, f"创建失败: {str(e)}"
finally:
cursor.close()
conn.close()
def get_user_by_id(self, user_id):
"""根据ID获取用户"""
conn = self.db.get_connection()
cursor = conn.cursor(dictionary=True)
try:
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user_data = cursor.fetchone()
if not user_data:
return None, "用户不存在"
user = User(
id=user_data['id'],
name=user_data['name'],
email=user_data['email'],
age=user_data['age'],
created_at=user_data['created_at'],
updated_at=user_data['updated_at']
)
return user, "获取成功"
except Exception as e:
return None, f"获取失败: {str(e)}"
finally:
cursor.close()
conn.close()
def get_all_users(self, page=1, per_page=20):
"""获取所有用户（分页）"""
conn = self.db.get_connection()
cursor = conn.cursor(dictionary=True)
try:
# 计算偏移量
offset = (page - 1) * per_page
# 查询数据
cursor.execute(
"SELECT * FROM users ORDER BY id DESC LIMIT %s OFFSET %s",
(per_page, offset)
)
rows = cursor.fetchall()
# 查询总数
cursor.execute("SELECT COUNT(*) as total FROM users")
total = cursor.fetchone()['total']
# 转换为User对象列表
users = []
for row in rows:
user = User(
id=row['id'],
name=row['name'],
email=row['email'],
age=row['age'],
created_at=row['created_at'],
updated_at=row['updated_at']
)
users.append(user)
return users, {
'page': page,
'per_page': per_page,
'total': total,
'pages': (total + per_page - 1) // per_page
}
except Exception as e:
return [], f"查询失败: {str(e)}"
finally:
cursor.close()
conn.close()
def update_user(self, user_id, name=None, email=None, age=None):
"""更新用户信息"""
conn = self.db.get_connection()
cursor = conn.cursor(dictionary=True)
try:
# 构建更新字段和参数
updates = []
params = []
if name is not None:
updates.append("name = %s")
params.append(name)
if email is not None:
# 检查新邮箱是否被其他人使用
cursor.execute(
"SELECT id FROM users WHERE email = %s AND id != %s",
(email, user_id)
)
if cursor.fetchone():
return None, "邮箱已被其他用户使用"
updates.append("email = %s")
params.append(email)
if age is not None:
updates.append("age = %s")
params.append(age)
# 如果没有更新的字段
if not updates:
return None, "没有需要更新的字段"
# 添加WHERE条件参数
params.append(user_id)
# 执行更新
sql = f"UPDATE users SET {', '.join(updates)} WHERE id = %s"
cursor.execute(sql, params)
if cursor.rowcount == 0:
return None, "用户不存在"
# 获取更新后的用户
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user_data = cursor.fetchone()
conn.commit()
user = User(
id=user_data['id'],
name=user_data['name'],
email=user_data['email'],
age=user_data['age'],
created_at=user_data['created_at'],
updated_at=user_data['updated_at']
)
return user, "更新成功"
except Exception as e:
conn.rollback()
return None, f"更新失败: {str(e)}"
finally:
cursor.close()
conn.close()
def delete_user(self, user_id):
"""删除用户"""
conn = self.db.get_connection()
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
if cursor.rowcount == 0:
return False, "用户不存在"
conn.commit()
return True, "删除成功"
except Exception as e:
conn.rollback()
return False, f"删除失败: {str(e)}"
finally:
cursor.close()
conn.close()
4. 主程序入口（main.py）
from services.user_service import UserService
import json
def print_menu():
"""打印菜单"""
print("\n" + "="*50)
print("用户管理系统")
print("="*50)
print("1. 查看所有用户")
print("2. 添加新用户")
print("3. 查询用户")
print("4. 更新用户")
print("5. 删除用户")
print("6. 退出")
print("="*50)
def main():
service = UserService()
while True:
print_menu()
choice = input("请选择操作 (1-6): ").strip()
if choice == '1':
# 查看所有用户
page = input("请输入页码 (默认为1): ").strip()
page = int(page) if page else 1
users, info = service.get_all_users(page=page)
if isinstance(info, str): # 出错的情况
print(f"❌ {info}")
continue
print(f"\n📋 用户列表 (第{info['page']}页，共{info['pages']}页，总计{info['total']}人)：")
for user in users:
print(f" {user.id}: {user.name} ({user.email}) - {user.age}岁")
print(f"\n📊 分页信息：{json.dumps(info, ensure_ascii=False, default=str)}")
elif choice == '2':
# 添加新用户
print("\n➕ 添加新用户")
name = input("姓名: ").strip()
email = input("邮箱: ").strip()
age = input("年龄: ").strip()
if not age.isdigit():
print("❌ 年龄必须是数字")
continue
user, message = service.create_user(name, email, int(age))
if user:
print(f"✅ {message}")
print(f" 用户ID: {user.id}")
print(f" 创建时间: {user.created_at}")
else:
print(f"❌ {message}")
elif choice == '3':
# 查询用户
print("\n🔍 查询用户")
user_id = input("请输入用户ID: ").strip()
if not user_id.isdigit():
print("❌ ID必须是数字")
continue
user, message = service.get_user_by_id(int(user_id))
if user:
print(f"✅ {message}")
print(f" ID: {user.id}")
print(f" 姓名: {user.name}")
print(f" 邮箱: {user.email}")
print(f" 年龄: {user.age}")
print(f" 创建时间: {user.created_at}")
print(f" 最后更新: {user.updated_at}")
else:
print(f"❌ {message}")
elif choice == '4':
# 更新用户
print("\n✏️ 更新用户")
user_id = input("请输入要更新的用户ID: ").strip()
if not user_id.isdigit():
print("❌ ID必须是数字")
continue
print("请输入新信息（留空表示不修改）:")
name = input("姓名: ").strip() or None
email = input("邮箱: ").strip() or None
age = input("年龄: ").strip()
age = int(age) if age.isdigit() else None
user, message = service.update_user(int(user_id), name, email, age)
if user:
print(f"✅ {message}")
print(f" 更新后的邮箱: {user.email}")
print(f" 最后更新时间: {user.updated_at}")
else:
print(f"❌ {message}")
elif choice == '5':
# 删除用户
print("\n🗑️ 删除用户")
user_id = input("请输入要删除的用户ID: ").strip()
if not user_id.isdigit():
print("❌ ID必须是数字")
continue
confirm = input(f"⚠️ 确定删除用户 {user_id} 吗？(输入 'yes' 确认): ").strip()
if confirm.lower() != 'yes':
print("取消删除")
continue
success, message = service.delete_user(int(user_id))
if success:
print(f"✅ {message}")
else:
print(f"❌ {message}")
elif choice == '6':
print("👋 再见！")
break
else:
print("❌ 无效选择，请重新输入")
if __name__ == "__main__":
main()
如何使用这个系统？
-
初始化数据库：
先创建一个数据库
mysql -u root -p CREATE DATABASE user_management CHARACTER SET utf8mb4; exit
安装依赖
pip install mysql-connector-python
运行系统
python main.py
-
系统功能：
- 查看所有用户（支持分页）
- 添加新用户（自动检查重复邮箱）
- 根据ID查询用户
- 更新用户信息（部分更新）
- 删除用户（需要确认）
- 扩展建议：
- 添加用户搜索功能（按姓名或邮箱）
- 实现用户导入/导出（从Excel/CSV）
- 添加权限控制（不同角色不同功能）
- 集成日志系统（记录操作历史）
八、常见问题与解决方案
Q1: 连接数据库时出现Access denied for user错误
可能原因：
- 用户名或密码错误
- 用户没有访问该数据库的权限
- 用户只能在特定主机连接
解决方案：
-- 1. 检查用户是否存在
SELECT User, Host FROM mysql.user;
-- 2. 创建用户并授权
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON python_test.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
-- 3. 或者修改现有用户
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Q2: 中文数据变成乱码
可能原因：
- 数据库字符集不是
utf8mb4 - Python连接时没有指定正确的字符集
- 表或列的字符集设置错误
解决方案：
# 确保连接配置正确
config = {
'host': 'localhost',
'user': 'root',
'password': '',
'database': 'python_test',
'charset': 'utf8mb4', # 关键！
'collation': 'utf8mb4_unicode_ci'
}
# 检查数据库字符集
SHOW CREATE DATABASE python_test;
SHOW CREATE TABLE users;
Q3: 批量插入数据速度很慢
可能原因：
- 一条一条插入，没有使用批量操作
- 没有使用事务
- 每次插入都重新建立连接
解决方案：
def batch_insert_users(users_data):
"""批量插入用户（性能优化版）"""
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
try:
# 关闭自动提交，开启事务
conn.autocommit = False
# 批量插入
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
cursor.executemany(sql, users_data)
# 提交事务
conn.commit()
print(f"✅ 批量插入成功：{cursor.rowcount} 条记录")
except Exception as e:
conn.rollback()
print(f"❌ 批量插入失败: {e}")
finally:
cursor.close()
conn.close()
# 使用示例
users_data = [
("张三", "zhangsan@example.com", 25),
("李四", "lisi@example.com", 30),
# ... 更多数据
]
batch_insert_users(users_data)
Q4: 高并发时出现连接数不足
**可能原因 **：
- 没有使用连接池
- 连接池大小设置不合理
- 连接没有正确关闭
**解决方案 **：
-
**使用连接池 **（如前文示例）
-
**调整连接池参数 **：
pool_config = { 'pool_size': 20, # 最大连接数 'max_overflow': 30, # 最多可超过的连接数 'pool_recycle': 3600, # 连接回收时间（秒） 'pool_timeout': 30 # 获取连接超时时间（秒） }
-
**确保连接正确关闭 **：使用
with语句或try-finally
九、最佳实践总结
1. 代码组织原则
- **分离关注点 **：连接管理、业务逻辑、数据模型分开
- **单一职责 **：每个函数/类只做一件事
- **依赖注入 **：便于测试和扩展
2. 性能优化要点
- **连接池 **：高并发必备
- **批量操作 **：减少网络往返
- **索引优化 **：查询性能关键
- **合理事务 **：平衡性能与一致性
3. 安全防护措施
- **参数化查询 **：防止SQL注入
- **最小权限原则 **：应用使用专用数据库用户
- **数据验证 **：Python层和数据库层双重验证
- **错误处理 **：不暴露敏感信息
4. 可维护性建议
- **统一日志 **：记录关键操作和错误
- **配置管理 **：敏感信息不硬编码
- **版本控制 **：数据库迁移脚本
- **文档注释 **：重要函数写清楚用途
十、下一步学习方向
掌握了Python操作MySQL的基础后，你可以继续深入：
-
**学习SQLAlchemy ORM **：
- 用面向对象的方式操作数据库
- 自动处理复杂的关联关系
- 支持多种数据库（MySQL、PostgreSQL、SQLite等）
-
**Web框架集成 **：
- **Flask **：轻量级，集成SQLAlchemy很方便
- **Django **：全功能框架，自带ORM
- **FastAPI **：现代异步框架，性能优秀
-
**高级数据库技术 **：
- **数据库设计 **：规范化、反规范化设计
- **性能调优 **：查询优化、索引设计、分区表
- **高可用方案 **：主从复制、读写分离、集群部署
-
**实战项目推荐 **：
- **个人博客系统 **：用户、文章、评论功能
- **电商后台管理 **：商品、订单、库存管理
- **数据报表系统 **：定时统计、可视化展示
结语：告别手动，拥抱自动化
还记得开篇的问题吗？为什么要用Python操作MySQL？
现在你已经有答案了：** 为了效率，为了稳定，为了未来的可能性 **。
从今天起，你可以：
- 用脚本代替手动操作，节省90%的时间
- 用自动化代替重复劳动，减少人为错误
- 用封装好的工具快速构建新应用
- 用Python的数据分析能力挖掘数据库价值
这不仅是技术能力的提升，更是工作效率的革命。
记住：好的开发者不是更努力地工作，而是更聪明地工作。把重复的事情交给代码，把你的时间和智慧用在更有价值的地方。
现在，打开你的编辑器，开始编写你的第一个Python数据库脚本吧！
编程不是魔法，而是把复杂变简单的艺术。祝你在这条路上越走越远，越走越轻松！
