Python操作MySQL指南

Python操作MySQL指南

Navicat前期准备，下载直接导入，console.sql文件：https://gitee.com/lrtn/blogsharing

一、操作流程概述

Python操作MySQL的标准流程如下：

1. 导包 import
2. 创建连接（地址、用户名、密码、数据库名、端口、编码）
3. 创建游标（使用连接对象创建）
4. 准备要执行的SQL语句
5. 执行SQL语句
6. 关闭游标
7. 关闭连接

💡 我的理解：可以把这个过程类比为"打电话"——导包就像拿出手机，创建连接就像拨号接通，获取游标就像接通后拿起听筒准备说话，执行SQL就是说话内容，最后挂断电话对应关闭连接和游标。游标（Cursor）可以理解为数据库返回结果的一个"指针"或"迭代器"，我们通过它来逐条或批量获取查询结果。

二、安装第三方库

Python访问MySQL需要使用pymysql库，可以直接在终端输入安装命令：

pip install pymysql
pip list                   #查看已安装库

三、基本操作

前置准备：使用Navicat或其他数据库工具，执行 console.sql 文件创建数据库和表。

数据库名：enterprise_info_db

包含表：employees（员工表）、departments（部门表）、projects（项目表）、emp_project（员工项目关联表）

查询数据(SELECT)

查询操作不需要提交事务，直接获取结果即可。

import pymysql

# 1. 创建连接
db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

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

# 3. 准备SQL并执行
sql = "SELECT * FROM employees WHERE department = '技术部'"
cursor.execute(sql)

# 4. 获取数据（三种方式）
# 方式一：获取全部数据
data = cursor.fetchall()
for row in data:
    print(row)  # 返回元组

# 方式二：获取一条数据
# data = cursor.fetchone()

# 方式三：获取指定条数
# data = cursor.fetchmany(3)

# 5. 关闭游标和连接
cursor.close()
db.close()

💡 使用字典游标（推荐）

默认返回的是元组，可读性差。使用 DictCursor 可以返回字典格式，方便按列名取值：

from pymysql.cursors import DictCursor

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4',
    cursorclass=DictCursor  # ← 指定字典游标
)

cursor = db.cursor()
cursor.execute("SELECT * FROM employees WHERE emp_id = 1")
row = cursor.fetchone()
print(row['emp_name'])  # 通过列名访问，如：张明
print(row['age'])       # 28

插入数据(INSERT)

⚠️ 重点：增删改操作必须执行 commit() 提交事务！

import pymysql

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

cursor = db.cursor()

# 注意：employees 表的 emp_id 没有自增，需要手动生成
# 先查询当前最大ID
cursor.execute("SELECT MAX(emp_id) as max_id FROM employees")
result = cursor.fetchone()
max_id = result[0] if result[0] is not None else 0
new_id = max_id + 1

# 插入一条数据
sql = """
    INSERT INTO employees (emp_id, emp_name, gender, age, department, hire_date)
    VALUES (%s, %s, %s, %s, %s, %s)
"""
cursor.execute(sql, (new_id, '刘阳', '男', 28, '技术部', '2026-07-29'))

# ⚠️ 必须提交！
db.commit()

print(f"✅ 员工添加成功，ID: {new_id}")

cursor.close()
db.close()

更新数据(UPDATE)

import pymysql

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

cursor = db.cursor()

# 更新员工年龄
sql = "UPDATE employees SET age = %s WHERE emp_id = %s"
cursor.execute(sql, (30, 1))

# ⚠️ 必须提交！
db.commit()

print(f"✅ 更新成功，影响了 {cursor.rowcount} 行")

cursor.close()
db.close()

删除数据(DELETE)

⚠️ 注意外键约束： emp_project 表引用了 employees 表的 emp_id，删除员工前需先删除关联记录。

import pymysql

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

cursor = db.cursor()
emp_id = 11

# 第一步：删除关联表中的记录
sql1 = "DELETE FROM emp_project WHERE Demp_id = %s"
cursor.execute(sql1, (emp_id,))

# 第二步：删除员工记录
sql2 = "DELETE FROM employees WHERE emp_id = %s"
cursor.execute(sql2, (emp_id,))

# ⚠️ 必须提交！
db.commit()

print(f"✅ 员工 ID {emp_id} 已删除")

cursor.close()
db.close()

四、进阶操作

异常处理

数据库操作可能因为各种原因失败（连接超时、SQL语法错误、外键约束等），需要使用 try-except 捕获异常，并在失败时执行 rollback() 回滚事务。

import pymysql

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

try:
    cursor = db.cursor()
    
    sql = "INSERT INTO employees VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (11, '王芳', '女', 26, '技术部', '2026-07-29'))
    
    db.commit()
    print("✅ 插入成功")
    
except pymysql.Error as e:
    db.rollback()  # ⚠️ 失败时回滚
    print(f"❌ 数据库操作失败: {e}")
    
finally:
    cursor.close()
    db.close()

使用with管理游标

使用 with 语句可以自动管理游标的关闭，避免忘记关闭导致资源泄露。

import pymysql

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

try:
    # with 会自动关闭游标
    with db.cursor() as cursor:
        sql = "SELECT * FROM employees WHERE department = %s"
        cursor.execute(sql, ('技术部',))
        data = cursor.fetchall()
        for row in data:
            print(row)
            
except pymysql.Error as e:
    print(f"❌ 查询失败: {e}")
    
finally:
    db.close()  # 连接仍需手动关闭

参数化查询（防SQL注入）

❌ 错误写法（存在SQL注入风险）：

# 危险！用户输入可能拼接恶意SQL
emp_id = input("请输入员工ID：")
sql = f"SELECT * FROM employees WHERE emp_id = {emp_id}"  # 不要这样写！
cursor.execute(sql)

✅ 正确写法（使用占位符）

# 安全！使用 %s 占位符，参数以元组形式传入
emp_id = input("请输入员工ID：")
sql = "SELECT * FROM employees WHERE emp_id = %s"
cursor.execute(sql, (emp_id,))  # 参数自动转义，防止注入

获取数据方式详解

方法	说明	返回值	使用场景
`fetchone()`	获取结果集中的下一条记录	元组/字典	查询结果唯一
`fetchmany(n)`	获取结果集中的下n条记录	元组列表	分页加载、大数据分批处理
`fetchall()`	获取结果集中的所有记录	元组列表	数据量不大，需要全部数据

cursor.execute("SELECT * FROM employees")

# fetchone：每次获取一条，指针自动后移
row1 = cursor.fetchone()  # 第1条
row2 = cursor.fetchone()  # 第2条

# fetchmany：获取指定条数
rows = cursor.fetchmany(3)  # 第3-5条

# fetchall：获取剩余所有
all_rows = cursor.fetchall()  # 第6条及以后

批量操作

使用 executemany() 可以一次性插入多条数据，比循环执行 execute() 效率更高。

import pymysql

db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    database='enterprise_info_db',
    charset='utf8mb4'
)

try:
    with db.cursor() as cursor:
        # 先获取最大ID
        cursor.execute("SELECT MAX(emp_id) as max_id FROM employees")
        max_id = cursor.fetchone()[0] or 0
        
        # 准备批量数据
        emp_list = [
            (max_id + 1, '赵一', '男', 25, '市场部', '2026-07-01'),
            (max_id + 2, '钱二', '女', 24, '市场部', '2026-07-02'),
            (max_id + 3, '孙三', '男', 26, '技术部', '2026-07-03'),
        ]
        
        sql = """
            INSERT INTO employees (emp_id, emp_name, gender, age, department, hire_date)
            VALUES (%s, %s, %s, %s, %s, %s)
        """
        
        # 批量执行
        cursor.executemany(sql, emp_list)
        db.commit()
        print(f"✅ 成功插入 {len(emp_list)} 条数据")
        
except pymysql.Error as e:
    db.rollback()
    print(f"❌ 批量插入失败: {e}")
    
finally:
    db.close()

五、封装工具类

在项目开发中，通常将数据库操作封装成工具函数，方便复用。

# db_utils.py
import pymysql

DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': 'root',
    'database': 'enterprise_info_db',
    'charset': 'utf8mb4'
}


def query(sql, params=None):
    """执行查询，返回所有结果"""
    db = None
    try:
        db = pymysql.connect(**DB_CONFIG)
        with db.cursor() as cursor:
            cursor.execute(sql, params or ())
            return cursor.fetchall()
    except pymysql.Error as e:
        print(f"查询失败: {e}")
        return None
    finally:
        if db:
            db.close()


def execute(sql, params=None):
    """执行增删改操作，返回影响行数"""
    db = None
    try:
        db = pymysql.connect(**DB_CONFIG)
        with db.cursor() as cursor:
            affected = cursor.execute(sql, params or ())
            db.commit()
            return affected
    except pymysql.Error as e:
        if db:
            db.rollback()
        print(f"执行失败: {e}")
        return -1
    finally:
        if db:
            db.close()


# 使用示例
if __name__ == "__main__":
    # 查询
    result = query("SELECT * FROM employees WHERE department = %s", ('技术部',))
    print(result)
    
    # 插入
    sql = "INSERT INTO employees VALUES (%s, %s, %s, %s, %s, %s)"
    execute(sql, (11, '刘阳', '男', 28, '技术部', '2026-07-29'))

六、注意事项与常见问题

问题	错误写法	正确写法
SQL注入	f"SELECT * FROM employees WHERE id = {id}"	f"SELECT * FROM employees WHERE id = {id}"
忘记提交	插入/更新后没有 `commit()`	未指定 charset
外键约束	直接删除有外键关联的员工	先删除 `emp_project` 关联记录
游标未关闭	忘记 `cursor.close()`	用 `with db.cursor() as cursor`
字符集乱码	未指定 charset	连接时指定 `charset='utf8mb4'`

📖 总结：Python操作MySQL的核心就是"连接 → 操作 → 提交 → 关闭"这个流程。记住查询不用提交，增删改必须提交，参数永远用占位符，游标用with管理，加上完善的异常处理，就能写出安全稳定的数据库操作代码。

posted @ 2026-07-29 15:55 珉好好阅读(8) 评论(0) 收藏举报

刷新页面返回顶部

Python操作MySQL指南