Python使用PyMySQL连接MySQL数据库的实战指南_Python

一、前言

python 作为最流行的编程语言之一，与关系型数据库 mysql 的结合非常常见。无论是 web 开发、数据分析还是自动化脚本，掌握 python 操作 mysql 都是一项必备技能。

本文将带你 从零开始，使用 pymysql 库完成 mysql 数据库的连接、建表、增删改查等完整流程，并提供一份可以直接运行的实战代码。无论你是刚接触数据库的新手，还是想规范代码风格的老手，这篇文章都能给你提供参考。

二、准备工作

2.1 安装 pymysql

pymysql 是 python 中连接 mysql 的第三方库，纯 python 实现，兼容 mysql 5.x 和 8.x。

下载对应的安装包：

pip install pymysql

如果你使用了较新版本的 mysql（8.0+），建议同时安装 cryptography 以支持新认证插件：

pip install pymysql cryptography

2.2 启动 mysql 服务

确保你的 mysql 服务已启动，并知道以下连接信息：

主机地址（通常为 localhost 或 127.0.0.1）
端口（默认 3306）
用户名（如 root）
密码
数据库名（需提前创建，或通过代码自动创建）

三、连接 mysql 数据库的核心步骤

使用 pymysql 连接数据库只需四步：

创建连接对象：调用 pymysql.connect()，传入连接参数。
获取游标对象：通过 conn.cursor() 获取，用于执行 sql。
执行 sql 语句：使用 cursor.execute(sql, params)。
关闭游标和连接：释放资源。

3.1 基本连接示例

import pymysql

# 连接参数
config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'db': 'test_db',          # 必须已经存在的数据库
    'charset': 'utf8mb4',
    'autocommit': true        # 自动提交事务
}

# 1. 建立连接
conn = pymysql.connect(**config)

# 2. 创建游标
cursor = conn.cursor()

# 3. 执行查询
cursor.execute("select version()")
version = cursor.fetchone()
print(f"mysql 版本: {version[0]}")

# 4. 关闭资源
cursor.close()
conn.close()

注意：db 参数指定的数据库必须已存在，否则连接会失败。若数据库不存在，可先连接到默认库（如 mysql）再创建目标库，下文会给出自动建库的方法。

四、完整实战：用户表增删改查

下面我们设计一个完整的示例，涵盖：

自动创建数据库（如果不存在）
创建 users 表
插入多条数据
删除指定用户
修改用户密码
查询并展示所有数据

4.1 完整代码结构

整个流程封装在一个脚本中，便于理解和复用。

# encoding=utf8
 
'''python连接mysql数据库，实现：增-删-改-查'''
 
# 需要安装三方库
# pip install pymysql==1.1.0

import pymysql

db_host = "localhost"
db_port = 3306
db_username = "root"
db_password = "123456"
db_database = "liming"

# 1. 先连接默认数据库，创建目标库
temp_conn = pymysql.connect(host=db_host, port=db_port, user=db_username, password=db_password, charset="utf8")
temp_cur = temp_conn.cursor()
temp_cur.execute(f"create database if not exists `{db_database}` character set utf8mb4")
temp_cur.close()
temp_conn.close()

# 2. 正式连接目标库
conn = pymysql.connect(host=db_host, port=db_port, user=db_username, password=db_password,
                       db=db_database, charset="utf8", autocommit=true)
cur = conn.cursor()

# 3. 创建表 users
try:
    cur.execute("""
        create table if not exists `users` (
            `id` int not null auto_increment,
            `username` varchar(32) null,
            `password` varchar(32) null,
            `datatime` datetime null,
            primary key (`id`)
        ) engine=innodb default charset=utf8mb4;
    """)
    print("创建/检查 users 表成功")
except exception as e:
    print("创建表失败:", e)

# 4. 插入数据（使用参数化查询避免注入）
users = [
    (1, '张三', '123456', '2024-03-28'),
    (2, '李四', '123456', '2024-03-29'),
    (3, '王五', '123456', '2024-03-30')
]
try:
    for u in users:
        cur.execute(
            "insert ignore into `users` (id, username, password, datatime) values (%s, %s, %s, %s)",
            u
        )
    print("数据插入成功")
except exception as e:
    print("插入失败:", e)

# 5. 删除李四
try:
    cur.execute("delete from `users` where username = %s", ('李四',))
    print("删除成功")
except exception as e:
    print("删除失败:", e)

# 6. 修改张三密码
try:
    cur.execute("update `users` set password = %s where username = %s", ('456789', '张三'))
    print("修改成功")
except exception as e:
    print("修改失败:", e)

# 7. 查询并显示所有数据
cur.execute("select * from `users`")
rows = cur.fetchall()
for row in rows:
    print(row)

# 关闭连接
cur.close()
conn.close()

五、重点知识详解

5.1 连接参数说明

参数名	说明	示例
`host`	mysql 服务器地址	`'localhost'` 或 `'192.168.1.100'`
`port`	端口号，默认 3306	`3306`
`user`	登录用户名	`'root'`
`password`	登录密码	`'123456'`
`db`	要连接的数据库名	`'mydb'`
`charset`	字符集，推荐 `utf8mb4`	`'utf8mb4'`
`autocommit`	是否自动提交事务	`true` 或 `false`

5.2 游标（cursor）的作用

游标是执行 sql 的“管道”，所有 sql 语句都必须通过游标发送。常用的游标方法：

execute(sql, params)：执行单条 sql。
executemany(sql, seq_of_params)：批量执行。
fetchone()：获取一行结果。
fetchall()：获取所有行。
fetchmany(size)：获取指定行数。

5.3 参数化查询（防sql注入）

禁止直接拼接字符串（如 f"select * from users where name='{name}'"），这极易引发 sql 注入攻击。

正确做法：使用 %s 占位符，并将实际值作为参数传入，pymysql 会自动转义。

# 安全写法
cursor.execute("select * from users where name = %s", (username,))

5.4 事务处理

如果不开启 autocommit=true，则需要显式调用 conn.commit() 提交更改，或 conn.rollback() 回滚。

try:
    cursor.execute("insert ...")
    cursor.execute("update ...")
    conn.commit()          # 提交事务
except exception:
    conn.rollback()        # 出错回滚

5.5 关闭连接

使用完数据库后，务必关闭游标和连接，释放资源。

cursor.close()
conn.close()

更好的做法是使用 with 上下文管理器（但 pymysql 本身不直接支持，可自定义）。

六、常见问题与排查思路

问题现象	可能原因	解决方法
`can't connect to mysql server`	mysql 服务未启动，或主机/端口错误	检查服务状态，确认 ip 和端口
`access denied for user`	用户名或密码错误	核对账号密码，注意大小写
`unknown database 'xxx'`	目标数据库不存在	先创建数据库（参考本文的自动建库）
`packet sequence number wrong`	连接超时或网络不稳定	增加 `connect_timeout` 参数，或检查防火墙
中文乱码	字符集不匹配	统一使用 `utf8mb4`，并在连接和表上都指定该字符集