Python SQLite：在表中删除数据 (`DELETE` 子句) 深度解析

weixin_42107409

1374人浏览 · 2025-09-18 09:23:27

weixin_42107409 · 2025-09-18 09:23:27 发布

引言：数据清理与谨慎操作——DELETE 子句
SQL DELETE 语句基础
- 2.1 语法结构
- 2.2 WHERE 子句的重要性：精准删除
- 2.3 慎用：删除所有行
WHERE 子句在 DELETE 中的应用
- 3.1 比较运算符 (=, !=, <, >, <=, >=)
- 3.2 逻辑运算符 (AND, OR, NOT)
- 3.3 范围运算符 (BETWEEN)
- 3.4 集合运算符 (IN, NOT IN)
- 3.5 模式匹配 (LIKE, GLOB)
- 3.6 空值判断 (IS NULL, IS NOT NULL)
外键约束与 DELETE 操作的联动
- 4.1 PRAGMA foreign_keys = ON;：启用外键是前提
- 4.2 ON DELETE CASCADE：级联删除
- 4.3 ON DELETE SET NULL：设置为空
- 4.4 ON DELETE RESTRICT / NO ACTION：限制删除
- 4.5 ON DELETE SET DEFAULT (SQLite 不支持)
在 Python sqlite3 中执行 DELETE 操作
- 5.1 连接数据库与创建游标
- 5.2 参数绑定：防止 SQL 注入的关键
  - 5.2.1 位置参数 (?)
  - 5.2.2 命名参数 (:name)
- 5.3 执行删除与提交事务
- 5.4 获取受影响的行数 (cursor.rowcount)
事务管理：commit() 与 rollback()
- 6.1 为什么数据修改操作需要事务
- 6.2 演示 commit()
- 6.3 演示 rollback()
错误处理
- 7.1 sqlite3.OperationalError (语法错误、数据库锁定)
- 7.2 sqlite3.IntegrityError (外键约束违规)
最佳实践与注意事项
- 8.1 再次强调：永远使用 WHERE 进行有条件的删除！
- 8.2 再次强调：永远使用参数绑定！
- 始终启用外键约束
- 始终使用事务
- 先备份数据，再执行重大删除
- 在非生产环境测试 DELETE 查询
- 理解并规划外键的 ON DELETE 行为
- 使用 with 语句管理资源
- ALTER TABLE 限制与常见变通方案
综合代码示例
- 9.1 数据库结构和数据准备 (包含外键)
- 9.2 各种 DELETE 操作示例
- 9.3 事务与回滚示例
总结

1. 引言：数据清理与谨慎操作——`DELETE` 子句

在数据库管理中，除了插入、查询和更新数据，删除数据也是一项基本而重要的操作。无论是清理旧的、不再需要的数据，还是处理错误的录入，DELETE 语句都是必不可少的。然而，与 INSERT 和 UPDATE 不同，DELETE 操作具有更高的风险：一旦数据被删除，通常难以恢复（除非有备份或事务回滚）。因此，在使用 DELETE 时，务必谨慎并精确。

DELETE 语句允许您从一个或多个表中删除满足特定条件的行。本指南将深入探讨 DELETE 语句的语法、与 WHERE 子句的结合、外键约束对其行为的影响，以及如何在 Python 的 sqlite3 模块中安全高效地执行删除操作，并强调事务管理、错误处理和最佳实践。

2. SQL `DELETE` 语句基础

2.1 语法结构

DELETE 语句的基本语法非常简单：

DELETE FROM table_name
[WHERE condition];

DELETE FROM table_name: 指定要从中删除行的表。
WHERE condition: (可选) 一个布尔表达式，用于指定哪些行应该被删除。只有满足此条件的行才会被删除。

2.2 `WHERE` 子句的重要性：精准删除

WHERE 子句是 DELETE 语句中最关键的部分。如果省略 WHERE 子句，DELETE 语句将删除表中的所有行！这通常不是您想要的结果，可能会导致严重的数据丢失。

强烈建议: 在编写任何 DELETE 语句时，先问自己：“我是否只想删除特定的行？”如果是，那么 WHERE 子句是必不可少的。

示例:

-- 删除 user_id 为 1 的用户
DELETE FROM users WHERE user_id = 1;

-- 删除注册日期在 2023 年之前的订单
DELETE FROM orders WHERE order_date < '2023-01-01';

2.3 慎用：删除所有行

正如前文所述，如果 DELETE 语句没有 WHERE 子句，它将删除指定表中的所有行。

-- 警告：这将删除 users 表中的所有数据！
DELETE FROM users;

在 SQLite 中，DELETE FROM table_name; 的行为与其他数据库系统的 TRUNCATE TABLE 类似，但存在一些关键区别：

DELETE 操作会记录事务日志，可以回滚（如果在一个事务中）。TRUNCATE 通常不能回滚。
DELETE 不会重置 AUTOINCREMENT 计数器（除非重建表）。TRUNCATE 通常会重置。

因此，即使是删除所有行，也应该在明确知道其后果的情况下进行，并且最好在一个事务中操作以便回滚。

3. `WHERE` 子句在 `DELETE` 中的应用

DELETE 语句中的 WHERE 子句支持与 SELECT 语句中相同的运算符和逻辑，以便您可以构建复杂的条件来精确指定要删除的行。

3.1 比较运算符 (`=`, `!=`, `<`, `>`, `<=`, `>=`)

DELETE FROM products WHERE stock <= 0; -- 删除库存为 0 或更少的产品
DELETE FROM customers WHERE registration_date < '2022-01-01'; -- 删除 2022 年前注册的客户

3.2 逻辑运算符 (`AND`, `OR`, `NOT`)

DELETE FROM orders WHERE is_completed = TRUE AND order_date < '2023-01-01'; -- 删除 2023 年前已完成的订单
DELETE FROM employees WHERE department_id = 101 OR salary < 30000; -- 删除部门ID为101的员工或工资低于30000的员工

3.3 范围运算符 (`BETWEEN`)

DELETE FROM logs WHERE timestamp BETWEEN '2023-01-01' AND '2023-01-31'; -- 删除 1 月份的日志

3.4 集合运算符 (`IN`, `NOT IN`)

DELETE FROM users WHERE user_id IN (10, 15, 20); -- 删除指定 ID 的用户
DELETE FROM old_data WHERE category NOT IN ('Active', 'Current');

3.5 模式匹配 (`LIKE`, `GLOB`)

DELETE FROM products WHERE name LIKE 'Old_Item%'; -- 删除名称以 'Old_Item' 开头的产品

3.6 空值判断 (`IS NULL`, `IS NOT NULL`)

DELETE FROM customers WHERE email IS NULL; -- 删除邮箱为空的客户

4. 外键约束与 `DELETE` 操作的联动

外键约束不仅维护了数据的一致性，还定义了当父表中的记录被删除时，子表中的关联记录如何响应的行为。

4.1 `PRAGMA foreign_keys = ON;`：启用外键是前提

在 SQLite 中，外键约束默认是禁用的。您需要在每个数据库连接开始时执行 PRAGMA foreign_keys = ON; 才能使其生效。否则，即使定义了外键，DELETE 操作也不会触发任何关联行为。

4.2 `ON DELETE CASCADE`：级联删除

当父表中的行被删除时，子表中所有引用该父表行的对应行也会被自动删除。这是一种强大的行为，但必须谨慎使用。

示例:
如果 orders 表的 customer_id 外键定义为 ON DELETE CASCADE，当您删除 customers 表中的某个客户时，该客户的所有订单及其订单项也会被自动删除。

-- 定义外键时
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE -- 客户被删除时，其所有订单也会被删除
);

4.3 `ON DELETE SET NULL`：设置为空

当父表中的行被删除时，子表中所有引用该父表行的外键值将被设置为 NULL。这要求外键列允许 NULL 值。

示例:
如果 employees 表的 dept_id 外键定义为 ON DELETE SET NULL，当您删除 departments 表中的某个部门时，该部门的所有员工的 dept_id 字段将被设置为 NULL。

-- 定义外键时
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    dept_id INTEGER, -- 允许为 NULL
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL -- 部门被删除时，员工的部门ID设为 NULL
);

4.4 `ON DELETE RESTRICT` / `NO ACTION`：限制删除

RESTRICT: 如果子表中存在引用父表行的记录，将阻止父表行的删除操作。
NO ACTION: 与 RESTRICT 类似，也是阻止删除。在 SQLite 中，RESTRICT 和 NO ACTION 的行为是相同的，即如果存在子引用，则不允许删除父行，并会抛出 sqlite3.IntegrityError。

示例:
如果 orders 表的 customer_id 外键定义为 ON DELETE RESTRICT，当您尝试删除 customers 表中一个有订单的客户时，操作将被阻止。

-- 定义外键时
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT -- 如果客户有订单，则阻止删除客户
);

4.5 `ON DELETE SET DEFAULT` (SQLite 不支持)

某些数据库系统支持 ON DELETE SET DEFAULT，但在 SQLite 中不直接支持此选项。如果需要类似行为，通常需要通过触发器或应用程序逻辑来实现。

5. 在 Python `sqlite3` 中执行 `DELETE` 操作

5.1 连接数据库与创建游标

import sqlite3
import os

db_name = 'delete_example.db'
# 假设 db_name 和连接代码已存在，并已设置 foreign_keys = ON
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")

5.2 参数绑定：防止 SQL 注入的关键

与 INSERT 和 UPDATE 类似，当 DELETE 语句的 WHERE 子句中包含动态值时，必须使用参数绑定。这不仅可以防止 SQL 注入攻击，还能正确处理各种数据类型的转义。

5.2.1 位置参数 (`?`)

user_id_to_delete = 3
cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id_to_delete,))

5.2.2 命名参数 (`:name`)

min_stock_to_delete = 0
cursor.execute("DELETE FROM products WHERE stock <= :threshold", {'threshold': min_stock_to_delete})

5.3 执行删除与提交事务

执行 DELETE 语句后，更改默认情况下不会立即写入磁盘。您需要调用 conn.commit() 来保存更改。如果不想保存更改，可以调用 conn.rollback()。

# 示例：删除用户 ID 为 1 的用户
user_id_to_delete = 1
cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id_to_delete,))
conn.commit() # 提交更改
print(f"用户 ID {user_id_to_delete} 已删除。")

5.4 获取受影响的行数 (`cursor.rowcount`)

在执行 DELETE 语句后，cursor.rowcount 属性将返回受影响的行数。这对于验证删除操作是否按预期执行非常有用。

email_pattern_to_delete = '%@olddomain.com'
cursor.execute("DELETE FROM customers WHERE email LIKE ?", (email_pattern_to_delete,))
deleted_count = cursor.rowcount
conn.commit()
print(f"删除了 {deleted_count} 个邮箱以 '{email_pattern_to_delete}' 结尾的客户。")

6. 事务管理：`commit()` 与 `rollback()`

数据修改操作（INSERT, UPDATE, DELETE）应该始终在事务中进行。事务确保了操作的原子性、一致性、隔离性和持久性 (ACID)。

6.1 为什么数据修改操作需要事务

原子性: 要么所有操作都成功，要么所有操作都失败并回滚。避免部分更改。
一致性: 确保数据库从一个有效状态转换到另一个有效状态。
持久性: 提交的更改是永久的。

Python sqlite3 模块默认在 commit() 之前的所有操作都在一个隐式事务中。

6.2 演示 `commit()`

# conn 和 cursor 已创建并设置 foreign_keys = ON
try:
    # 开始一个隐式事务
    cursor.execute("DELETE FROM products WHERE stock < 10")
    deleted_products = cursor.rowcount
    cursor.execute("DELETE FROM old_logs WHERE log_date < '2023-01-01'")
    deleted_logs = cursor.rowcount
    
    conn.commit() # 如果没有异常，提交所有更改
    print(f"成功删除了 {deleted_products} 个产品和 {deleted_logs} 条旧日志。")
except sqlite3.Error as e:
    print(f"删除失败: {e}")
    conn.rollback() # 出现异常时回滚所有更改
    print("事务已回滚。")

6.3 演示 `rollback()`

回滚用于撤销在一个事务中执行的所有更改，将数据库恢复到事务开始时的状态。

# conn 和 cursor 已创建并设置 foreign_keys = ON
try:
    # 模拟一个会失败的操作，或我们决定不提交的操作
    print("\n尝试删除一些数据，然后回滚...")
    cursor.execute("DELETE FROM customers WHERE customer_id = 999") # 假设这个ID不存在
    deleted_customers = cursor.rowcount
    print(f"尝试删除了 {deleted_customers} 个客户。")
    
    # 我们决定不提交这些更改
    conn.rollback() 
    print("事务已回滚，所有更改已被撤销。")

    # 验证数据是否未被删除 (这里以查询所有客户数量为例)
    cursor.execute("SELECT COUNT(*) FROM customers")
    remaining_customers = cursor.fetchone()[0]
    print(f"回滚后，客户表中仍有 {remaining_customers} 条记录。")

except sqlite3.Error as e:
    print(f"删除失败: {e}")
    conn.rollback()
    print("事务已回滚。")

注意: with sqlite3.connect(...) as conn: 语句块会自动处理 commit() 或 rollback()。如果 with 块正常退出，则 commit()；如果发生异常，则 rollback()。这是管理数据库连接和事务的推荐方式。

7. 错误处理

在使用 DELETE 子句执行查询时，最常见的错误是 sqlite3.OperationalError 和 sqlite3.IntegrityError。

7.1 `sqlite3.OperationalError` (语法错误、数据库锁定)

这通常表示 SQL 语法错误、引用的表不存在、数据库文件被锁定或损坏等问题。

try:
    # 尝试删除不存在的表
    cursor.execute("DELETE FROM non_existent_table WHERE id = 1")
    conn.commit()
except sqlite3.OperationalError as e:
    print(f"\n捕获到 OperationalError: {e}")
    print("删除失败，请检查表名和 SQL 语法。")
    conn.rollback() # 确保回滚，以防部分操作成功

7.2 `sqlite3.IntegrityError` (外键约束违规)

当您尝试删除一个父表中的行，而子表中仍然存在引用该父表行的记录，并且外键约束设置为 ON DELETE RESTRICT 或 NO ACTION 时，会引发此错误。

# 假设有一个客户（customer_id = 1）有订单，并且外键约束是 ON DELETE RESTRICT
try:
    print("\n尝试删除一个有订单的客户 (应失败 due to RESTRICT)...")
    customer_id_with_orders = 1 # 假设客户 1 有订单
    cursor.execute("DELETE FROM customers WHERE customer_id = ?", (customer_id_with_orders,))
    conn.commit()
    print(f"客户 ID {customer_id_with_orders} 已删除。") # 这条不会执行
except sqlite3.IntegrityError as e:
    print(f"捕获到 IntegrityError: {e}")
    print("删除客户失败，因为该客户仍有订单。事务已回滚。")
    conn.rollback()
except sqlite3.OperationalError as e:
    print(f"捕获到 OperationalError: {e}")
    conn.rollback()

8. 最佳实践与注意事项

8.1 再次强调：永远使用 `WHERE` 进行有条件的删除！

这是防止意外数据丢失的黄金法则。在执行 DELETE 语句之前，请仔细检查 WHERE 子句。

8.2 再次强调：永远使用参数绑定！

对于 WHERE 子句中的任何动态值，必须使用参数绑定 (? 或 :name)。这不仅防止 SQL 注入，也简化了值类型转换和转义。

8.3 始终启用外键约束

在每个数据库连接开始时，执行 PRAGMA foreign_keys = ON;。这确保了您的数据完整性得到维护，并且 ON DELETE 行为按预期工作。

8.4 始终使用事务

将 DELETE 操作（以及其他数据修改操作）封装在事务中。使用 conn.commit() 来保存更改，或 conn.rollback() 来撤销更改。使用 with sqlite3.connect(...) as conn: 是管理事务和连接的推荐方式。

8.5 先备份数据，再执行重大删除

在生产环境中执行任何大规模或关键数据的删除操作之前，务必进行数据库备份。

8.6 在非生产环境测试 `DELETE` 查询

在对生产数据执行 DELETE 之前，始终在开发或测试环境中运行和验证您的删除查询。可以先将 DELETE 改为 SELECT 来检查哪些行会被删除。

8.7 理解并规划外键的 `ON DELETE` 行为

在设计数据库模式时，仔细考虑每个外键的 ON DELETE 行为。选择 CASCADE, SET NULL, RESTRICT 中的哪一个取决于您的业务逻辑和数据完整性需求。

8.8 使用 `with` 语句管理资源

with sqlite3.connect(...) as conn: 结构确保连接在块结束时自动关闭（或在异常时回滚）。

8.9 `ALTER TABLE` 限制与常见变通方案

（这是针对表结构修改的提醒，与 DELETE 查询本身无关，但作为整个数据库操作的通用最佳实践值得再次提及）SQLite 对 ALTER TABLE 的支持相对有限。例如，它不能直接删除列，也不能直接修改列的数据类型或约束（除了少数例外）。对于更复杂的模式更改（例如，删除列、修改列类型、重新排序列），常见的变通方法是：

创建具有所需新结构的新表。
将旧表中的数据复制到新表（只复制兼容的列）。
删除旧表。
将新表重命名为旧表的名称。
理解这些限制对于规划数据库模式演进至关重要。

9. 综合代码示例

此示例将构建一个简单的电商数据库模型，并演示各种 DELETE 操作，包括使用 WHERE 子句、触发外键级联删除、遇到外键约束错误以及事务回滚。

import sqlite3
import os
import datetime

db_name = 'comprehensive_delete_db.db'
if os.path.exists(db_name):
    os.remove(db_name)

with sqlite3.connect(db_name, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("PRAGMA foreign_keys = ON;") # 确保外键生效

    # --- 数据库结构和数据准备 (包含外键，定义 ON DELETE 行为) ---
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS categories (
            category_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS products (
            product_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            price REAL CHECK(price > 0) DEFAULT 0.0,
            stock INTEGER DEFAULT 0 CHECK(stock >= 0),
            category_id INTEGER,
            FOREIGN KEY (category_id) REFERENCES categories(category_id)
                ON DELETE SET NULL -- 分类被删除时，产品category_id设为NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS customers (
            customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER NOT NULL,
            order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            total_amount REAL,
            FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
                ON DELETE CASCADE -- 客户被删除时，其所有订单也删除
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS order_items (
            item_id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER CHECK(quantity > 0),
            FOREIGN KEY (order_id) REFERENCES orders(order_id)
                ON DELETE CASCADE, -- 订单被删除时，其所有订单项也删除
            FOREIGN KEY (product_id) REFERENCES products(product_id)
                ON DELETE RESTRICT -- 产品有订单项时，不能删除该产品
        )
    ''')

    # 插入数据
    cursor.executemany("INSERT INTO categories (name) VALUES (?)", [("Electronics",), ("Books",), ("Home",)])
    cursor.execute("SELECT category_id FROM categories WHERE name = 'Electronics'")
    electronics_cat_id = cursor.fetchone()['category_id']
    cursor.execute("SELECT category_id FROM categories WHERE name = 'Books'")
    books_cat_id = cursor.fetchone()['category_id']

    cursor.executemany("INSERT INTO products (name, price, stock, category_id) VALUES (?, ?, ?, ?)", [
        ("Laptop", 1200.50, 20, electronics_cat_id),
        ("Mouse", 25.00, 150, electronics_cat_id),
        ("Keyboard", 75.99, 80, electronics_cat_id),
        ("Python Book", 49.99, 50, books_cat_id),
        ("Old Item", 10.00, 5, electronics_cat_id), # 用于删除演示
        ("Zero Stock Item", 5.00, 0, electronics_cat_id) # 用于删除演示
    ])
    
    cursor.executemany("INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)", [
        ("Alice", "Smith", "alice@example.com"),
        ("Bob", "Johnson", "bob@example.com"),
        ("Charlie", "Brown", "charlie@example.com") # 这个客户没有订单，用于演示删除
    ])

    cursor.execute("SELECT customer_id FROM customers WHERE email = 'alice@example.com'")
    alice_id = cursor.fetchone()['customer_id']
    cursor.execute("SELECT customer_id FROM customers WHERE email = 'bob@example.com'")
    bob_id = cursor.fetchone()['customer_id']

    cursor.execute("SELECT product_id FROM products WHERE name = 'Laptop'")
    laptop_id = cursor.fetchone()['product_id']
    cursor.execute("SELECT product_id FROM products WHERE name = 'Mouse'")
    mouse_id = cursor.fetchone()['product_id']
    cursor.execute("SELECT product_id FROM products WHERE name = 'Python Book'")
    python_book_id = cursor.fetchone()['product_id']

    cursor.execute("INSERT INTO orders (customer_id, order_date, total_amount) VALUES (?, ?, ?)",
                   (alice_id, datetime.datetime(2023, 1, 10), 1250.50))
    order1_id = cursor.lastrowid
    cursor.execute("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)",
                   (order1_id, laptop_id, 1))

    cursor.execute("INSERT INTO orders (customer_id, order_date, total_amount) VALUES (?, ?, ?)",
                   (bob_id, datetime.datetime(2023, 2, 15), 74.99))
    order2_id = cursor.lastrowid
    cursor.execute("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)",
                   (order2_id, mouse_id, 2))
    cursor.execute("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)",
                   (order2_id, python_book_id, 1))

    conn.commit() # 提交初始数据
    print("数据库和初始数据已准备好。\n")

    # --- 演示各种 DELETE 操作 ---

    print("--- 1. 删除单个产品 (无订单项的产品) ---")
    # 假设 'Zero Stock Item' 没有订单项
    product_name_to_delete = "Zero Stock Item"
    cursor.execute("DELETE FROM products WHERE name = ?", (product_name_to_delete,))
    deleted_count = cursor.rowcount
    conn.commit()
    print(f"  删除了 {deleted_count} 个名为 '{product_name_to_delete}' 的产品。")
    cursor.execute("SELECT COUNT(*) FROM products WHERE name = ?", (product_name_to_delete,))
    print(f"  验证：剩余同名产品数量: {cursor.fetchone()[0]}")

    print("\n--- 2. 删除多个产品 (根据价格范围) ---")
    min_price_delete = 10.0
    max_price_delete = 100.0
    # 'Old Item' (10.0) 在这个范围内，'Mouse' (25.0), 'Keyboard' (75.99) 也在
    # 但 Mouse 和 Keyboard 有订单项，会被 ON DELETE RESTRICT 阻止
    # 这里我们只删除 'Old Item'，因为它没有订单项
    cursor.execute("DELETE FROM products WHERE price BETWEEN ? AND ? AND name = 'Old Item'", (min_price_delete, max_price_delete))
    deleted_count = cursor.rowcount
    conn.commit()
    print(f"  删除了 {deleted_count} 个价格在 ${min_price_delete:.2f} 到 ${max_price_delete:.2f} 之间且名为 'Old Item' 的产品。")
    cursor.execute("SELECT name, price FROM products WHERE price BETWEEN ? AND ?", (min_price_delete, max_price_delete))
    print(f"  验证：剩余这些价格范围内的产品: {[row['name'] for row in cursor.fetchall()]}")


    print("\n--- 3. 触发 ON DELETE SET NULL (删除分类) ---")
    # 删除 'Books' 分类，其下的 'Python Book' 的 category_id 将变为 NULL
    cursor.execute("SELECT name, category_id FROM products WHERE name = 'Python Book'")
    print(f"  删除分类前，Python Book: {cursor.fetchone()}")
    
    cursor.execute("DELETE FROM categories WHERE name = 'Books'")
    deleted_count = cursor.rowcount
    conn.commit()
    print(f"  删除了 {deleted_count} 个名为 'Books' 的分类。")
    
    cursor.execute("SELECT name, category_id FROM products WHERE name = 'Python Book'")
    print(f"  删除分类后，Python Book: {cursor.fetchone()}")

    print("\n--- 4. 触发 ON DELETE CASCADE (删除客户，同时删除订单及其订单项) ---")
    # 删除 Alice，她的订单和订单项都会被删除
    cursor.execute("SELECT COUNT(*) FROM orders WHERE customer_id = ?", (alice_id,))
    alice_orders_count_before = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(oi.item_id) FROM order_items oi JOIN orders o ON oi.order_id = o.order_id WHERE o.customer_id = ?", (alice_id,))
    alice_order_items_count_before = cursor.fetchone()[0]
    print(f"  删除客户 Alice 前，订单数: {alice_orders_count_before}, 订单项数: {alice_order_items_count_before}")
    
    cursor.execute("DELETE FROM customers WHERE customer_id = ?", (alice_id,))
    deleted_count = cursor.rowcount
    conn.commit()
    print(f"  删除了 {deleted_count} 个客户 (Alice)。")

    cursor.execute("SELECT COUNT(*) FROM orders WHERE customer_id = ?", (alice_id,))
    alice_orders_count_after = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(oi.item_id) FROM order_items oi LEFT JOIN orders o ON oi.order_id = o.order_id WHERE o.customer_id = ?", (alice_id,))
    alice_order_items_count_after = cursor.fetchone()[0]
    print(f"  删除客户 Alice 后，订单数: {alice_orders_count_after}, 订单项数: {alice_order_items_count_after}")


    print("\n--- 5. 触发 ON DELETE RESTRICT (尝试删除有订单项的产品，应失败) ---")
    # 'Mouse' 产品有订单项，其外键定义为 ON DELETE RESTRICT
    product_name_with_items = "Mouse"
    cursor.execute("SELECT COUNT(*) FROM order_items WHERE product_id = (SELECT product_id FROM products WHERE name = ?)", (product_name_with_items,))
    print(f"  产品 '{product_name_with_items}' 有 {cursor.fetchone()[0]} 个订单项。")
    
    try:
        cursor.execute("DELETE FROM products WHERE name = ?", (product_name_with_items,))
        conn.commit()
        print(f"  成功删除了 '{product_name_with_items}'。") # 这条不应该被执行
    except sqlite3.IntegrityError as e:
        print(f"  捕获到 IntegrityError: {e}")
        print(f"  无法删除产品 '{product_name_with_items}'，因为它存在订单项。事务已回滚。")
        conn.rollback()
    
    cursor.execute("SELECT COUNT(*) FROM products WHERE name = ?", (product_name_with_items,))
    print(f"  验证：剩余 '{product_name_with_items}' 产品数量: {cursor.fetchone()[0]}")

    print("\n--- 6. 演示事务回滚 (删除客户然后回滚) ---")
    # 查找 Charlie 客户，他没有订单
    cursor.execute("SELECT customer_id, first_name FROM customers WHERE first_name = 'Charlie'")
    charlie_info = cursor.fetchone()
    charlie_id = charlie_info['customer_id']
    
    # 记录删除前客户总数
    cursor.execute("SELECT COUNT(*) FROM customers")
    total_customers_before_tx = cursor.fetchone()[0]
    print(f"  事务前客户总数: {total_customers_before_tx}")

    try:
        print(f"  尝试删除客户 '{charlie_info['first_name']}'...")
        cursor.execute("DELETE FROM customers WHERE customer_id = ?", (charlie_id,))
        deleted_count = cursor.rowcount
        print(f"  删除了 {deleted_count} 个客户。")
        
        # 决定回滚
        conn.rollback()
        print("  事务已回滚，删除操作已撤销。")

        # 验证回滚后客户数是否恢复
        cursor.execute("SELECT COUNT(*) FROM customers")
        total_customers_after_rollback = cursor.fetchone()[0]
        print(f"  回滚后客户总数: {total_customers_after_rollback}")
        if total_customers_after_rollback == total_customers_before_tx:
            print("  回滚成功。")
        else:
            print("  回滚失败！")

    except sqlite3.Error as e:
        print(f"  发生错误: {e}")
        conn.rollback()
        print("  事务已回滚。")

    print("\n--- 7. 删除表中所有数据 ---")
    cursor.execute("SELECT COUNT(*) FROM customers")
    initial_count = cursor.fetchone()[0]
    print(f"  删除前 customers 表有 {initial_count} 条记录。")

    cursor.execute("DELETE FROM customers") # 没有 WHERE 子句，删除所有
    deleted_total = cursor.rowcount
    conn.commit()
    print(f"  删除了 {deleted_total} 条客户记录。")

    cursor.execute("SELECT COUNT(*) FROM customers")
    final_count = cursor.fetchone()[0]
    print(f"  删除后 customers 表有 {final_count} 条记录。")

print(f"\n所有 `DELETE` 子句演示完成，数据库 '{db_name}' 已更新。")

10. 总结

为您详细介绍了 Python SQLite 中删除数据 (DELETE 子句) 的各个方面。

核心要点回顾:

DELETE 语句用于从表中删除行。
WHERE 子句是强制性的，用于指定要删除的特定行；省略 WHERE 子句将删除表中的所有行。
DELETE 中的 WHERE 子句支持所有常见的 SQL 运算符进行复杂的条件判断。
外键约束 (ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT/NO ACTION) 决定了父表记录被删除时子表记录的响应行为。务必在连接时启用 PRAGMA foreign_keys = ON;。
在 Python sqlite3 中执行 DELETE 操作时，必须使用参数绑定来传递 WHERE 子句中的动态值，以防止 SQL 注入。
事务管理 (conn.commit() 和 conn.rollback()) 对于数据修改操作至关重要，确保了数据的一致性和可回滚性。
cursor.rowcount 属性可以获取受影响的行数。
处理 sqlite3.OperationalError (语法或连接问题) 和 sqlite3.IntegrityError (外键约束违规)。
最佳实践包括：始终使用有条件的删除、参数绑定、启用外键、使用事务、备份数据和在安全环境中测试。

通过这些全面而深入的知识，您将能够安全、高效、准确地在 SQLite 数据库中执行数据删除操作，并构建更健壮的 Python 数据库应用程序。

清理测试文件 (为确保后续测试环境干净)

import os
files_to_clean = [
    'delete_example.db',
    'comprehensive_delete_db.db'
]
for f in files_to_clean:
    if os.path.exists(f):
        os.remove(f)
print("\n所有测试数据库文件已清理。")

亚马逊云科技技术品牌专区

更多推荐

云原生+AI 驱动：用友 YonSuite 助力福州企业业财税一体化实战

亚马逊云科技技术品牌专区

基于MATLAB的深度学习工具箱实战解析：从环境配置到模型构建的全流程指南

在人工智能飞速发展的今天，深度学习已成为解决图像识别、自然语言处理等复杂问题的关键技术。MATLAB作为强大的科学计算平台，通过其深度学习工具箱（Deep Learning Toolbox）为研究人员和工程师提供了高效、直观的解决方案。GitHub项目正是基于这一背景，旨在帮助开发者利用MATLAB快速入门并掌握深度学习的核心流程。该项目并非单一的脚本，而是一个系统性的学习资源集合，涵盖了从理论概

亚马逊云科技技术品牌专区

AI探索地震预测新方法

研究人员利用AI模型分析地震波、地壳运动以及地下结构变化等数据，尝试识别传统方法难以发现的异常信号。在部分实验中，AI展现出了辅助判断地震风险变化的潜力。地震发生具有高度复杂性，准确预测一直是科学研究中的难题。近年来，人工智能技术开始被应用于地震数据分析，希望从大量历史信息中寻找更多规律。不过，目前地震预测仍然面临巨大挑战，复杂的地下环境和数据不足等因素都会影响判断结果。未来，人工智能或将作为辅助

亚马逊云科技技术品牌专区

所有评论(0)

查看更多评论

weixin_42107409

@weixin_42107409

已为社区贡献114条内容

Python SQLite：在表中删除数据 (`DELETE` 子句) 深度解析

weixin_42107409

目录

1. 引言：数据清理与谨慎操作——DELETE 子句

2. SQL DELETE 语句基础

2.1 语法结构

2.2 WHERE 子句的重要性：精准删除

2.3 慎用：删除所有行

3. WHERE 子句在 DELETE 中的应用

3.1 比较运算符 (=, !=, <, >, <=, >=)

3.2 逻辑运算符 (AND, OR, NOT)

3.3 范围运算符 (BETWEEN)

3.4 集合运算符 (IN, NOT IN)

3.5 模式匹配 (LIKE, GLOB)

3.6 空值判断 (IS NULL, IS NOT NULL)

4. 外键约束与 DELETE 操作的联动

4.1 PRAGMA foreign_keys = ON;：启用外键是前提

4.2 ON DELETE CASCADE：级联删除

4.3 ON DELETE SET NULL：设置为空

4.4 ON DELETE RESTRICT / NO ACTION：限制删除

4.5 ON DELETE SET DEFAULT (SQLite 不支持)

5. 在 Python sqlite3 中执行 DELETE 操作

5.1 连接数据库与创建游标

5.2 参数绑定：防止 SQL 注入的关键

5.2.1 位置参数 (?)

5.2.2 命名参数 (:name)

5.3 执行删除与提交事务

5.4 获取受影响的行数 (cursor.rowcount)

6. 事务管理：commit() 与 rollback()

6.1 为什么数据修改操作需要事务

6.2 演示 commit()

6.3 演示 rollback()

7. 错误处理

7.1 sqlite3.OperationalError (语法错误、数据库锁定)

7.2 sqlite3.IntegrityError (外键约束违规)

8. 最佳实践与注意事项

8.1 再次强调：永远使用 WHERE 进行有条件的删除！

8.2 再次强调：永远使用参数绑定！

8.3 始终启用外键约束

8.4 始终使用事务

8.5 先备份数据，再执行重大删除

8.6 在非生产环境测试 DELETE 查询

8.7 理解并规划外键的 ON DELETE 行为

8.8 使用 with 语句管理资源

8.9 ALTER TABLE 限制与常见变通方案

9. 综合代码示例

10. 总结

所有评论(0)

温馨提示：您尚未绑定手机号

weixin_42107409

1. 引言：数据清理与谨慎操作——`DELETE` 子句

2. SQL `DELETE` 语句基础

2.2 `WHERE` 子句的重要性：精准删除

3. `WHERE` 子句在 `DELETE` 中的应用

3.1 比较运算符 (`=`, `!=`, `<`, `>`, `<=`, `>=`)

3.2 逻辑运算符 (`AND`, `OR`, `NOT`)

3.3 范围运算符 (`BETWEEN`)

3.4 集合运算符 (`IN`, `NOT IN`)

3.5 模式匹配 (`LIKE`, `GLOB`)

3.6 空值判断 (`IS NULL`, `IS NOT NULL`)

4. 外键约束与 `DELETE` 操作的联动

4.1 `PRAGMA foreign_keys = ON;`：启用外键是前提

4.2 `ON DELETE CASCADE`：级联删除

4.3 `ON DELETE SET NULL`：设置为空

4.4 `ON DELETE RESTRICT` / `NO ACTION`：限制删除

4.5 `ON DELETE SET DEFAULT` (SQLite 不支持)

5. 在 Python `sqlite3` 中执行 `DELETE` 操作

5.2.1 位置参数 (`?`)

5.2.2 命名参数 (`:name`)

5.4 获取受影响的行数 (`cursor.rowcount`)

6. 事务管理：`commit()` 与 `rollback()`

6.2 演示 `commit()`

6.3 演示 `rollback()`

7.1 `sqlite3.OperationalError` (语法错误、数据库锁定)

7.2 `sqlite3.IntegrityError` (外键约束违规)

8.1 再次强调：永远使用 `WHERE` 进行有条件的删除！

8.6 在非生产环境测试 `DELETE` 查询

8.7 理解并规划外键的 `ON DELETE` 行为

8.8 使用 `with` 语句管理资源

8.9 `ALTER TABLE` 限制与常见变通方案