Python数据库案例如何连接MySQL

wen python案例 2026-06-30 23

Python数据库案例：如何连接MySQL——从零到实战的完整指南

目录导读

为什么要用Python连接MySQL？
环境准备与安装
核心连接方法详解
实战案例：增删改查操作
常见错误与解决方案
性能优化与安全建议
FAQ问答区

为什么要用Python连接MySQL？

在当今的数据驱动时代，Python与MySQL的结合堪称“黄金搭档”，Python作为最流行的编程语言之一，拥有简洁的语法和强大的数据处理库；而MySQL作为开源关系型数据库，广泛应用于Web应用、数据分析、物联网等领域，据统计，超过70%的Python开发者需要与数据库交互,其中MySQL是使用率最高的选择之一。

Python数据库案例如何连接MySQL

核心价值点：

自动化数据处理：Python脚本可定时抓取、清洗和存储数据到MySQL
Web后端开发：Django/Flask框架天然支持MySQL作为数据存储层
数据分析管道：从MySQL提取数据后可直接用Pandas/NumPy处理
物联网数据采集：传感器数据通过Python实时写入MySQL

环境准备与安装

1 必备组件

组件	版本建议	下载地址/安装命令
Python	8+	python.org
MySQL Server	0+	mysql.com (社区版免费)
mysql-connector-python	最新版	`pip install mysql-connector-python`
PyMySQL（备选）	1+	`pip install pymysql`

2 安装验证

打开命令行,逐条执行以下命令：

python --version  # 确认Python已安装
mysql --version   # 确认MySQL服务运行
pip list | grep mysql  # 检查MySQL驱动

小技巧：若遇到权限问题，在Linux/Mac系统下请使用 sudo pip install；Windows用户注意以管理员身份运行CMD。

核心连接方法详解

1 基础连接代码（使用mysql-connector）

import mysql.connector
# 建立连接
conn = mysql.connector.connect(
    host="localhost",      # 数据库主机地址
    port=3306,             # 端口号（默认3306）
    user="root",           # 用户名
    password="your_password",  # 密码
    database="test_db"     # 数据库名（可选）
)
# 创建游标对象
cursor = conn.cursor()
# 执行SQL语句
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MySQL版本: {version[0]}")
# 关闭连接
cursor.close()
conn.close()

2 连接参数详解

参数	说明	示例
host	MySQL服务器地址	本地用localhost，远程用IP地址
port	服务端口	默认3306
user	数据库用户	'root' 或自定义用户
password	密码	建议放在环境变量中
database	目标数据库	需提前创建
charset	字符编码	'utf8mb4'（支持emoji）
use_pure	使用纯Python驱动	True（避免C扩展依赖问题）

3 使用连接池（生产环境推荐）

from mysql.connector.pooling import MySQLConnectionPool
# 创建连接池
pool = MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host="localhost",
    user="root",
    password="your_password",
    database="test_db"
)
# 从池中获取连接
conn = pool.get_connection()
cursor = conn.cursor()
# ... 执行操作 ...
cursor.close()
conn.close()  # 归还连接到池

实战案例：增删改查操作

1 创建表与插入数据

# 建表
create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
cursor.execute(create_table_sql)
conn.commit()
# 插入单条记录
insert_sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
user_data = ("张三", "zhangsan@example.com", 28)
cursor.execute(insert_sql, user_data)
conn.commit()
print(f"插入成功，ID: {cursor.lastrowid}")

2 批量插入与查询

# 批量插入
batch_data = [
    ("李四", "lisi@example.com", 32),
    ("王五", "wangwu@example.com", 25),
    ("赵六", "zhaoliu@example.com", 30)
]
cursor.executemany(insert_sql, batch_data)
conn.commit()
print(f"批量插入 {cursor.rowcount} 条记录")
# 查询所有用户
query_sql = "SELECT * FROM users"
cursor.execute(query_sql)
for row in cursor.fetchall():
    print(row)
# 带条件查询
cursor.execute("SELECT * FROM users WHERE age > %s", (28,))
older_users = cursor.fetchall()

3 更新与删除

# 更新数据
update_sql = "UPDATE users SET age = %s WHERE name = %s"
cursor.execute(update_sql, (35, "张三"))
conn.commit()
print(f"更新影响行数: {cursor.rowcount}")
# 删除数据（请谨慎操作）
delete_sql = "DELETE FROM users WHERE name = %s"
cursor.execute(delete_sql, ("赵六",))
conn.commit()
print(f"删除行数: {cursor.rowcount}")

4 事务处理

try:
    conn.start_transaction()
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    conn.commit()  # 提交事务
except mysql.connector.Error as e:
    conn.rollback()  # 回滚事务
    print(f"事务失败: {e}")

常见错误与解决方案

错误1：`ModuleNotFoundError: No module named 'mysql'`

原因：未安装MySQL驱动
解决：pip install mysql-connector-python

错误2：`mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'xxx' doesn't exist`

原因：目标表不存在
解决：先执行 CREATE TABLE 语句，或检查表名大小写（MySQL在Linux下区分大小写）

错误3：`Authentication plugin 'caching_sha2_password' cannot be loaded`

原因：MySQL 8.0+ 使用新认证插件，旧版本驱动不兼容
解决：

方法1：修改MySQL用户认证方式
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
方法2：升级MySQL驱动到最新版

错误4：连接超时 `(2003, "Can't connect to MySQL server on 'localhost' (10061)")`

原因：MySQL服务未运行或防火墙阻止
解决：

Windows：net start mysql
Linux：sudo systemctl start mysql
检查防火墙：sudo ufw allow 3306

性能优化与安全建议

1 连接管理

使用连接池：避免每次请求都创建新连接，降低延迟

设置超时参数：

conn = mysql.connector.connect(
    connect_timeout=10,
    wait_timeout=28800,  # 空闲超时（秒）
)

2 SQL注入防护

错误做法：

# 危险！千万不要这样拼接SQL
name = input("请输入用户名: ")
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

正确做法（使用参数化查询）：

# 安全的方式
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

3 密码安全

使用环境变量：

import os
password = os.getenv("MYSQL_PASSWORD")

配置.ini文件（确保.gitignore忽略此文件）
使用MySQL配置管理工具（如mysql_config_editor）

4 批量操作优化

使用 executemany 代替循环插入

开启批量提交：

conn.autocommit = False  # 手动控制提交
cursor.executemany(...)
conn.commit()

FAQ问答区

Q1: Python连接MySQL需要装MySQL数据库软件吗？

A: 需要，必须在本地或远程服务器安装MySQL服务，即使你仅是客户端开发者，也需要安装MySQL客户端库,但服务器必须存在。

Q2: mysql-connector-python 和 PyMySQL 哪个更好？

A: 两选一即可，mysql-connector-python是官方驱动，稳定性强；PyMySQL是纯Python实现，兼容性更好，建议新手用官方驱动,迁移到云服务时用PyMySQL。

Q3: 如何连接远程MySQL数据库？

conn = mysql.connector.connect(
    host="你的远程IP",  # 192.168.1.100
    port=3306,
    user="remote_user",
    password="password",
    database="test_db"
)

注意：需在MySQL中授予远程访问权限：
GRANT ALL PRIVILEGES ON test_db.* TO 'remote_user'@'%' IDENTIFIED BY 'password';

Q4: 连接成功后如何关闭资源？

A: 推荐使用 with 语句自动管理：

with mysql.connector.connect(**config) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT 1")

退出with块后自动关闭游标和连接。

Q5: 为什么我的中文数据变成乱码？

A: 确保连接时设置 charset='utf8mb4'，且数据库表字符集为 utf8mb4，执行SQL前先运行 SET NAMES 'utf8mb4'。

Q6: 如何处理大数据集（超过百万行）？

A: 使用分页查询和游标（buffered=True）：

cursor = conn.cursor(buffered=True)
cursor.execute("SELECT * FROM large_table LIMIT 10000 OFFSET 0")

或使用 pandas.read_sql() 分块读取：

import pandas as pd
for chunk in pd.read_sql("SELECT * FROM table", conn, chunksize=5000):
    process(chunk)

通过本文的15个代码示例和详细讲解，你已经掌握了从环境搭建到生产级优化的全流程，Python操作MySQL的核心在于：正确的连接配置 + 参数化查询防注入 + 合理的连接池管理，建议在实际项目中，结合ORM框架（如SQLAlchemy）进一步简化CRUD操作，打开你的编辑器,开始用Python征服MySQL吧！

Python数据库案例如何连接MySQL