Python操作MySQL数据库：从入门到实战的完整指南
目录导读
- 为什么选择Python操作MySQL？
- 环境搭建：安装必要的库与数据库
- 核心操作：连接、查询、插入、更新与删除
- 实战案例：构建一个简单的图书管理系统
- 常见问题与最佳实践
- Q&A：高频问题解答
为什么选择Python操作MySQL？
在数据驱动的时代,Python凭借其简洁的语法和强大的生态，成为操作MySQL数据库的首选语言，无论是数据分析、Web开发还是自动化运维，Python + MySQL的组合都能高效处理结构化数据，相比直接使用SQL命令行，Python提供了更灵活的编程接口，支持事务管理、批量操作以及与其他库（如Pandas）的无缝对接，据统计，超过70%的Python开发者会在项目中用到数据库操作，而MySQL作为开源关系型数据库的标杆，自然成为首选。
环境搭建：安装必要的库与数据库
1 安装MySQL数据库
- 访问MySQL官网下载社区版（8.0+），按向导安装。
- 设置root密码,启动服务（Windows可在服务管理器启动，Linux用
systemctl start mysql）。
2 安装Python连接库
推荐使用pymysql（纯Python实现，兼容性好）或mysql-connector-python（官方驱动）。
在终端执行：
pip install pymysql
如果使用ORM框架,可安装SQLAlchemy：
pip install sqlalchemy pymysql
3 创建测试数据库
登录MySQL：
CREATE DATABASE python_demo;
USE python_demo;
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,VARCHAR(200) NOT NULL,
author VARCHAR(100),
price DECIMAL(10,2),
isbn VARCHAR(20) UNIQUE
);
核心操作：连接、查询、插入、更新与删除
基础连接模板
import pymysql
# 建立连接
connection = pymysql.connect(
host='localhost',
user='root',
password='your_password',
database='python_demo',
charset='utf8mb4'
)
# 创建游标
cursor = connection.cursor()
查询操作
sql = "SELECT * FROM books WHERE author = %s"
cursor.execute(sql, ('刘慈欣',))
results = cursor.fetchall()
for row in results:
print(row)
插入与事务
try:
sql = "INSERT INTO books (title, author, price) VALUES (%s, %s, %s)"
cursor.execute(sql, ('三体', '刘慈欣', 68.00))
connection.commit() # 提交事务
except:
connection.rollback() # 回滚
批量更新
sql = "UPDATE books SET price = price * 0.9 WHERE author = %s"
cursor.execute(sql, ('刘慈欣',))
print(f"更新了{cursor.rowcount}条记录")
删除操作
cursor.execute("DELETE FROM books WHERE price < %s", (20.00,))
connection.commit()
实战案例：构建一个简单的图书管理系统
功能需求
- 添加新图书
- 查询所有图书
- 按ISBN查找图书
- 删除指定图书
完整代码示例
class BookManager:
def __init__(self):
self.conn = pymysql.connect(host='localhost', user='root', password='123456', database='python_demo')
self.cursor = self.conn.cursor()
def add_book(self, title, author, price, isbn):
sql = "INSERT INTO books (title, author, price, isbn) VALUES (%s,%s,%s,%s)"
self.cursor.execute(sql, (title, author, price, isbn))
self.conn.commit()
return self.cursor.lastrowid
def search_by_isbn(self, isbn):
self.cursor.execute("SELECT * FROM books WHERE isbn=%s", (isbn,))
return self.cursor.fetchone()
def delete_book(self, book_id):
self.cursor.execute("DELETE FROM books WHERE id=%s", (book_id,))
self.conn.commit()
return self.cursor.rowcount
# 使用示例
if __name__ == "__main__":
mgr = BookManager()
mgr.add_book("Python编程", "Eric Matthes", 89.00, "978-7-111-57668-2")
print(mgr.search_by_isbn("978-7-111-57668-2"))
常见问题与最佳实践
连接池管理
使用DBUtils库可以避免频繁创建连接：
from dbutils.pooled_db import PooledDB pool = PooledDB(pymysql, maxconnections=5, host='localhost') conn = pool.connection()
防止SQL注入
永远不要用字符串拼接SQL,使用参数化查询（%s占位符），如上面的所有示例。
处理大数据量
使用cursor.fetchmany(size)或cursor.iter()，避免一次性加载所有数据。
编码问题
连接时指定charset='utf8mb4'，支持emoji等特殊字符。
Q&A：高频问题解答
Q1：连接数据库时出现“Authentication plugin 'caching_sha2_password'”错误怎么办？
A：MySQL 8.0默认使用新的认证插件，解决方案：
- 在连接参数中添加
auth_plugin='mysql_native_password' - 或在MySQL中修改用户认证：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
Q2：如何高效地执行批量插入？
A：使用executemany()方法：
data = [('书A', '作者A', 50), ('书B', '作者B', 60)]
cursor.executemany("INSERT INTO books (title, author, price) VALUES (%s,%s,%s)", data)
connection.commit()
Q3：查询结果如何直接转换为字典格式？
A：创建游标时指定cursorclass=pymysql.cursors.DictCursor：
cursor = connection.cursor(pymysql.cursors.DictCursor)
Q4：事务提交失败时如何自动回滚？
A：使用try...except...else结构：
try:
cursor.execute(sql1)
cursor.execute(sql2)
connection.commit()
except Exception as e:
connection.rollback()
print(f"事务失败: {e}")
Q5：如何安全地关闭连接？
A：即使出现异常也应确保关闭，推荐使用with上下文管理器：
with pymysql.connect(...) as conn:
with conn.cursor() as cursor:
cursor.execute(...)
# 自动提交或回滚取决于自动提交设置
你已经掌握了Python操作MySQL的核心技能,从本地开发到生产环境，建议始终使用参数化查询、合理管理连接池，并利用logging模块记录SQL执行日志以便排查问题，打开你的终端，开始第一个Python + MySQL项目吧！
