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

在Python开发中，操作MySQL数据库最常用的第三方库之一就是pymysql，它能实现与MySQL数据库的高效交互，满足建表、数据增删改查等常规需求。

一、前置准备

1. 已安装并启动MySQL数据库（建议5.7及以上版本），且知晓MySQL的连接信息（主机地址、用户名、密码、需操作的数据库名），提前手动创建好目标数据库（例如test_db，可通过MySQL客户端执行CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;创建）。
2. 已安装Python环境（建议3.7及以上版本），确保pip包管理器可用。
3. 安装pymysql库：打开终端（Windows用CMD，Mac/Linux用终端），执行以下命令安装，网络不佳可使用国内镜像源加速。

# 常规安装
pip install pymysql

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

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

首先需要通过pymysql建立与MySQL数据库的连接，获取连接对象和游标对象（用于执行SQL语句）。注意填写自己的MySQL实际连接信息，代码示例如下：

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字符串导致的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）。