Python 与 MySQL 进行增删改查的操作以及防止SQL注入

Python 与 MySQL 的增删改查操作及防止 SQL 注入

在 Python 中，使用 PyMySQL 库与 MySQL 数据库进行交互非常简单和方便。本文将通过示例详细介绍如何使用 PyMySQL 实现 MySQL 数据库的基本操作，并讨论如何防止 SQL 注入。

准备工作

在开始之前，确保安装了 PyMySQL 库：

pip install pymysql

接下来，在 MySQL 中创建一个数据库 testdb 和一个表 users，表结构如下：

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE users (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT(3) NOT NULL
);

连接数据库

首先，我们需要连接到 MySQL 数据库：

import pymysql

def connect_db():
    """建立数据库连接"""
    return pymysql.connect(
        host='localhost',  # 数据库服务器地址
        user='root',       # 数据库用户名
        password='123456', # 数据库密码
        db='testdb',      # 数据库名称
        charset='utf8mb4', # 字符集
        cursorclass=pymysql.cursors.DictCursor # 返回字典类型的查询结果
    )

插入数据

单条插入

我们可以使用以下代码向 users 表中插入一条数据：

def insert_user(name, age):
    """插入一条用户数据"""
    conn = connect_db()
    cursor = conn.cursor()
    sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
    cursor.execute(sql, (name, age))
    conn.commit()
    cursor.close()
    conn.close()

# 示例用法
insert_user('张三', 25)

批量插入

如果我们需要插入多条数据，可以使用批量插入的方式：

def insert_multiple_users(users):
    """批量插入用户数据"""
    conn = connect_db()
    cursor = conn.cursor()
    sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
    cursor.executemany(sql, users)  # 使用 executemany 进行批量插入
    conn.commit()
    cursor.close()
    conn.close()

# 示例用法
users_data = [('李四', 30), ('王五', 22), ('赵六', 28)]
insert_multiple_users(users_data)

查询数据

我们可以查询 users 表中的所有数据，并将结果打印出来：

def fetch_all_users():
    """查询所有用户数据"""
    conn = connect_db()
    cursor = conn.cursor()
    sql = "SELECT * FROM users"
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

# 示例用法
users = fetch_all_users()
for user in users:
    print(user)

更新数据

我们可以更新 users 表中 id 为特定值的用户年龄：

def update_user_age(user_id, new_age):
    """更新用户年龄"""
    conn = connect_db()
    cursor = conn.cursor()
    sql = "UPDATE users SET age = %s WHERE id = %s"
    cursor.execute(sql, (new_age, user_id))
    conn.commit()
    cursor.close()
    conn.close()

# 示例用法
update_user_age(1, 30)

删除数据

可以根据用户的 id 删除对应的用户数据：

def delete_user(user_id):
    """删除用户数据"""
    conn = connect_db()
    cursor = conn.cursor()
    sql = "DELETE FROM users WHERE id = %s"
    cursor.execute(sql, (user_id,))
    conn.commit()
    cursor.close()
    conn.close()

# 示例用法
delete_user(1)

防止 SQL 注入

在数据库操作时，确保防止 SQL 注入非常重要。以下是一些有效的方法：

1. 使用占位符

使用占位符可以有效地避免 SQL 注入问题。例如：

sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
cursor.execute(sql, (name, age))

2. 使用参数化查询

参数化查询可以确保 SQL 语句和参数分离，避免恶意代码注入。例如：

sql = "SELECT * FROM users WHERE name = %s"
cursor.execute(sql, (name,))

3. 过滤用户输入

在处理用户输入时，始终进行过滤和验证，以确保输入符合预期：

def sanitize_input(user_input):
    """过滤用户输入"""
    return user_input.replace("'", "").replace('"', "").strip()  # 过滤单引号和双引号

user_name = sanitize_input(input("请输入用户名: "))

4. 使用 ORM

使用 ORM（对象关系映射）库，如 SQLAlchemy 或 Django ORM，可以自动处理 SQL 注入问题，因为它们内部使用参数化查询。

错误处理

在进行数据库操作时，处理可能出现的错误也是至关重要的。我们可以使用 try-except 语句来捕获异常：

def insert_user_safe(name, age):
    """安全插入用户数据，带有错误处理"""
    try:
        conn = connect_db()
        cursor = conn.cursor()
        sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
        cursor.execute(sql, (name, age))
        conn.commit()
    except pymysql.MySQLError as e:
        print(f"数据库错误: {e}")
    finally:
        cursor.close()
        conn.close()

# 示例用法
insert_user_safe('王五', 22)

记录日志

在开发应用程序时，记录数据库操作的日志对于调试和监控非常有用。可以使用 Python 的 logging 模块来实现：

import logging

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def insert_user_with_logging(name, age):
    """插入用户数据，并记录日志"""
    try:
        conn = connect_db()
        cursor = conn.cursor()
        sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
        cursor.execute(sql, (name, age))
        conn.commit()
        logging.info(f"成功插入用户: {name}, 年龄: {age}")
    except pymysql.MySQLError as e:
        logging.error(f"插入用户失败: {e}")
    finally:
        cursor.close()
        conn.close()

# 示例用法
insert_user_with_logging('张三', 25)

总结

本文详细介绍了如何在 Python 中使用 PyMySQL 库进行 MySQL 数据库的增删改查操作，并提供了防止 SQL 注入的有效措施，包括使用占位符、参数化查询、过滤输入等。此外，还介绍了错误处理和日志记录的基本方法。这些实践能够帮助你构建更安全、更健壮的数据库应用程序。希望这些示例能帮助你更好地理解 Python 与 MySQL 的交互。