Python使用pymysql操作MySQL数据库建表与插入查询数据详解

在Python数据库操作中，pymysql是最常用的第三方库之一，它能够高效地与MySQL数据库进行交互，轻松完成建表、数据插入、查询与更新等常规操作。本教程将带你逐步掌握pymysql的核心用法。

一、前置准备

在开始pymysql实战之前，需要确保以下环境已就绪。首先，MySQL数据库已安装并启动（建议使用5.7及以上版本），并记录好连接信息：主机地址、用户名、密码以及需要操作的数据库名称。目标数据库需提前手动创建，例如通过MySQL客户端执行 CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 来创建 test_db。其次，Python环境建议为3.7及以上版本，且 pip 包管理器可正常使用。最后，通过 pip 安装pymysql库，若网络不佳，可借助国内镜像源加速下载。

# 常规安装
pip install pymysql

# 国内清华镜像源加速安装（解决下载慢、超时）
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

二、核心步骤1：连接MySQL数据库

首先通过pymysql建立与MySQL数据库的连接，获取连接对象和游标对象（游标用于执行SQL语句）。请将连接信息替换为实际数据。参考以下代码示例：

import pymysql

# 1. 建立数据库连接
try:
    conn = pymysql.connect(
        host='localhost',  # 数据库主机地址，本地默认localhost
        user='root',       # MySQL用户名（默认常为root）
        password='your_mysql_password',  # 你的MySQL密码（替换为实际密码）
        database='test_db',  # 提前创建的目标数据库名
        charset='utf8mb4'    # 字符集，支持中文及特殊字符，避免乱码
    )
    print("数据库连接成功！")

    # 2. 获取游标对象（用于执行SQL语句）
    cursor = conn.cursor()

except pymysql.Error as e:
    print(f"数据库连接失败：{e}")

三、核心步骤2：创建数据表

连接成功后，通过游标对象执行 CREATE TABLE 语句来创建数据表。以 user_info（用户信息表）为例，包含自增主键、用户名、年龄、创建时间等字段。添加 IF NOT EXISTS 可避免表已存在时报错。代码示例如下：

# 定义建表SQL语句
create_table_sql = """
CREATE TABLE IF NOT EXISTS user_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户自增ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一不可重复',
    age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄，非负',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户信息表';
"""

try:
    # 执行建表SQL
    cursor.execute(create_table_sql)
    # 提交事务（建表、插入、更新等修改操作需提交事务才生效）
    conn.commit()
    print("数据表创建成功（或已存在）！")

except pymysql.Error as e:
    # 若出错则回滚事务
    conn.rollback()
    print(f"数据表创建失败：{e}")

四、核心步骤3：插入数据

插入数据支持单条插入和批量插入两种方式。推荐使用参数化查询，用 %s 作为占位符，既能避免SQL注入风险，又能提升代码的安全性与可维护性。

1. 单条数据插入

# 定义单条插入SQL（%s为参数占位符，无需加引号）
insert_single_sql = "INSERT INTO user_info (username, age) VALUES (%s, %s);"
# 待插入的数据（与SQL占位符顺序对应）
single_data = ("zhangsan", 25)

try:
    # 执行单条插入SQL
    cursor.execute(insert_single_sql, single_data)
    # 提交事务
    conn.commit()
    print(f"单条数据插入成功，插入数据ID：{cursor.lastrowid}")

except pymysql.Error as e:
    conn.rollback()
    print(f"单条数据插入失败：{e}")

2. 多条数据批量插入

批量插入使用 executemany() 方法，效率远高于循环执行单条插入，适合一次性插入大量数据：

# 定义批量插入SQL
insert_batch_sql = "INSERT INTO user_info (username, age) VALUES (%s, %s);"
# 待插入的多条数据（列表嵌套元组，每个元组对应一条数据）
batch_data = [
    ("lisi", 28),
    ("wangwu", 30),
    ("zhaoliu", 22)
]

try:
    # 执行批量插入SQL
    cursor.executemany(insert_batch_sql, batch_data)
    # 提交事务
    conn.commit()
    print(f"批量数据插入成功，共插入{cursor.rowcount}条数据")

except pymysql.Error as e:
    conn.rollback()
    print(f"批量数据插入失败：{e}")

五、核心步骤4：查询数据

查询数据属于只读操作，无需提交事务。执行查询SQL后，可以通过 fetchone()（获取单条结果）、fetchmany(n)（获取n条结果）、fetchall()（获取所有结果）来获取数据。下面是两个常见场景：

1. 查询所有数据

# 定义查询所有数据的SQL
query_all_sql = "SELECT id, username, age, create_time FROM user_info;"

try:
    # 执行查询SQL
    cursor.execute(query_all_sql)
    # 获取所有查询结果（返回列表嵌套元组，每个元组对应一条记录）
    all_results = cursor.fetchall()
    
    print("n所有用户数据如下：")
    print("IDt用户名t年龄t创建时间")
    for row in all_results:
        id, username, age, create_time = row
        print(f"{id}t{username}t{age}t{create_time}")

except pymysql.Error as e:
    print(f"查询数据失败：{e}")

2. 条件查询（示例：查询年龄大于25的用户）

# 定义条件查询SQL（参数化查询，避免SQL注入）
query_cond_sql = "SELECT id, username, age FROM user_info WHERE age > %s;"
# 查询条件参数
cond_data = (25,)

try:
    cursor.execute(query_cond_sql, cond_data)
    # 获取所有符合条件的结果
    cond_results = cursor.fetchall()
    
    print("n年龄大于25的用户如下：")
    print("IDt用户名t年龄")
    for row in cond_results:
        id, username, age = row
        print(f"{id}t{username}t{age}")

except pymysql.Error as e:
    print(f"条件查询失败：{e}")

六、收尾工作：关闭连接

所有操作完成后，请依次关闭游标对象和数据库连接，释放系统资源：

# 关闭游标
if cursor:
    cursor.close()
# 关闭数据库连接
if conn:
    conn.close()
print("n数据库连接已关闭")

七、常见问题注意事项

1. 中文乱码：确保数据库、数据表、pymysql连接的字符集均设置为 utf8mb4（utf8 不支持部分特殊中文）。
2. 操作失效：建表、插入、更新等修改操作必须执行 conn.commit() 提交事务，否则修改不生效；出错时建议执行 conn.rollback() 回滚事务。
3. SQL注入：所有带参数的操作都要用 %s 占位符进行参数化查询，切勿直接拼接SQL字符串。
4. 连接失败：检查MySQL是否已启动，主机地址、用户名、密码、数据库名是否正确，以及防火墙是否拦截了MySQL端口（默认3306）。