Python SQLite：插入数据深度解析

weixin_42107409

1210人浏览 · 2025-09-18 08:57:24

weixin_42107409 · 2025-09-18 08:57:24 发布

引言：填充数据库的基石——数据插入
SQLite INSERT 语句基础
- 2.1 基本 INSERT 语法
- 2.2 指定列与隐式列
- 2.3 插入默认值
- 2.4 INSERT OR IGNORE 与 INSERT OR REPLACE
Python sqlite3 模块与数据插入
- 3.1 建立连接与获取游标
- 3.2 执行 INSERT 语句：参数绑定 (?)
- 3.3 获取插入行的 ROWID (cursor.lastrowid)
- 3.4 提交事务 (conn.commit())
Python 数据类型与 SQLite 存储类的映射
- 4.1 Python None <-> SQLite NULL
- 4.2 Python int <-> SQLite INTEGER
- 4.3 Python float <-> SQLite REAL
- 4.4 Python str <-> SQLite TEXT
- 4.5 Python bytes <-> SQLite BLOB
- 4.6 Python bool (True/False) <-> SQLite INTEGER (1/0)
- 4.7 Python datetime / date <-> SQLite TEXT (ISO 8601) 或 INTEGER (Unix时间戳)
批量插入数据：executemany() 的效率
- 5.1 executemany() 语法与优势
- 5.2 性能对比 (单行插入 vs. 批量插入)
特殊插入操作
- 6.1 INSERT INTO ... SELECT ...
- 6.2 INSERT INTO ... DEFAULT VALUES
错误处理与数据完整性
- 7.1 sqlite3.IntegrityError (约束违反)
- 7.2 sqlite3.OperationalError (SQL 语法错误等)
- 7.3 事务管理 (try-except-finally 与 with 语句)
- 7.4 外键约束的启用 (PRAGMA foreign_keys = ON;)
最佳实践与注意事项
- 8.1 始终使用参数绑定 (防止 SQL 注入，自动类型转换)
- 8.2 使用 with 语句管理数据库连接和事务
- 8.3 executemany() 用于批量插入以提高性能
- 8.4 预先验证数据
- 8.5 理解 lastrowid 的局限性
- 8.6 适当处理 datetime 和 BOOLEAN
综合代码示例
- 9.1 表结构定义 (复用上一篇的 advanced_db.db 结构)
- 9.2 单行插入、批量插入、INSERT OR IGNORE、错误处理、外键演示
总结

1. 引言：填充数据库的基石——数据插入

在上一篇深度解析中，我们学习了如何在 SQLite 数据库中创建表，定义了数据的结构和约束。然而，一个空的数据库是没有实际价值的。**插入数据（Insert Data）**是数据库操作的核心环节，它将实际的信息存储到预先定义的表中，从而使数据库能够被查询和利用。

本指南将详细讲解如何在 SQLite 中使用 SQL INSERT 语句，以及如何在 Python 中利用 sqlite3 模块执行这些插入操作。我们将深入探讨单行插入、批量插入、数据类型映射、错误处理以及至关重要的安全和性能最佳实践。

2. SQLite `INSERT` 语句基础

INSERT 语句用于向数据库表中添加新的行。

2.1 基本 `INSERT` 语法

最基本的 INSERT 语法要求您为表中的所有列按顺序提供值：

INSERT INTO table_name VALUES (value1, value2, value3, ...);

table_name: 要插入数据的目标表的名称。
value1, value2, value3, ...: 按照表中列的顺序提供的值列表。

示例:
假设有一个 users 表：CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);

INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');

2.2 指定列与隐式列

通常，为了更好的可读性和避免因表结构变化（如新增列）导致的问题，建议显式指定要插入数据的列：

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

您不需要为所有列提供值。如果某列被省略，且它允许 NULL 或有 DEFAULT 值，则该列将分别存储 NULL 或其默认值。
列的顺序在 (column1, column2, ...) 中定义，对应的 VALUES 列表必须与此顺序匹配。

示例:

INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'); -- id 会自动生成

2.3 插入默认值

如果一列定义了 DEFAULT 值，您可以通过两种方式插入其默认值：

省略该列:

INSERT INTO tasks (description) VALUES ('Clean room'); -- status 和 created_at 将使用默认值

显式使用 DEFAULT 关键字: (适用于所有列)

INSERT INTO tasks (description, status) VALUES ('Do laundry', DEFAULT); -- status 使用默认值

2.4 `INSERT OR IGNORE` 与 `INSERT OR REPLACE`

当插入数据可能违反 UNIQUE 或 PRIMARY KEY 约束时，SQLite 提供了特殊的冲突解决策略：

INSERT OR IGNORE: 如果插入操作违反了任何约束（如 UNIQUE、PRIMARY KEY、NOT NULL），则忽略此行插入，不产生错误，也不会插入数据。
```
INSERT OR IGNORE INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
```
如果 id=1 的用户已存在，此语句将不执行任何操作。
INSERT OR REPLACE: 如果插入操作违反了 UNIQUE 或 PRIMARY KEY 约束，则替换（先删除旧行，再插入新行）与冲突行相关的所有信息。
```
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, 'Alicia', 'alicia@example.com');
```
如果 id=1 的用户已存在，旧的用户信息将被删除，然后插入 id=1, name='Alicia', email='alicia@example.com' 这条新行。

3. Python `sqlite3` 模块与数据插入

在 Python 中，通过 sqlite3 模块执行数据插入与创建表类似，但需要特别注意参数绑定和事务管理。

3.1 建立连接与获取游标

首先，您需要连接到数据库并创建一个游标对象。

import sqlite3
import os

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

conn = sqlite3.connect(db_name)
cursor = conn.cursor()

# 创建一个示例表用于插入数据
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
''')
conn.commit() # 提交表创建

3.2 执行 `INSERT` 语句：参数绑定 (`?`)

核心实践: 始终使用参数绑定 (?) 来传递要插入的值，而不是直接将值拼接到 SQL 字符串中。

# 单行插入
user_name = "John Doe"
user_email = "john.doe@example.com"

# 使用问号 (?) 作为占位符
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", (user_name, user_email))
print(f"插入用户 '{user_name}' 成功。")

为什么使用参数绑定？

安全性: 有效防止 SQL 注入攻击，因为数据库驱动会负责正确地转义特殊字符。
类型处理: sqlite3 模块会自动将 Python 对象转换为 SQLite 兼容的存储类（例如，Python str 转换为 SQLite TEXT）。
可读性与维护性: SQL 语句更清晰，易于阅读和维护。

3.3 获取插入行的 `ROWID` (`cursor.lastrowid`)

对于具有 INTEGER PRIMARY KEY 的表，SQLite 会自动为每一行分配一个唯一的 ROWID。您可以使用 cursor.lastrowid 属性获取最近一次成功插入操作所生成的 ROWID。

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Jane Smith", "jane.smith@example.com"))
last_id = cursor.lastrowid
print(f"插入用户 'Jane Smith' 成功，其 ID 为: {last_id}")

注意: cursor.lastrowid 仅在单行插入后有效。对于 executemany()，它只返回最后插入行的 ROWID。

3.4 提交事务 (`conn.commit()`)

所有修改数据库的操作（包括 INSERT、UPDATE、DELETE 和 DDL 语句）都发生在一个事务中。为了使这些更改永久保存到数据库文件，您必须调用 conn.commit()。否则，当连接关闭时，所有未提交的更改都将被回滚。

# 示例：多次插入后一次提交
cursor.execute("INSERT INTO users (name) VALUES (?)", ("Peter",))
cursor.execute("INSERT INTO users (name) VALUES (?)", ("Mary",))

conn.commit() # 提交之前的所有插入操作
print("Peter 和 Mary 已提交到数据库。")

conn.close() # 关闭数据库连接

推荐实践: 使用 with 语句来自动管理事务和连接关闭，如前一篇深度解析所述。

4. Python 数据类型与 SQLite 存储类的映射

sqlite3 模块负责将 Python 数据类型适配到 SQLite 的五种存储类。理解这种映射对于避免数据丢失或格式错误至关重要。

4.1 Python `None` <-> SQLite `NULL`

插入: Python None 直接插入为 SQLite NULL。
读取: SQLite NULL 读取为 Python None。

4.2 Python `int` <-> SQLite `INTEGER`

插入: Python int (无论大小) 插入为 SQLite INTEGER。SQLite 的 INTEGER 可以存储任意大小的整数。
读取: SQLite INTEGER 读取为 Python int。

4.3 Python `float` <-> SQLite `REAL`

插入: Python float 插入为 SQLite REAL。
读取: SQLite REAL 读取为 Python float。

4.4 Python `str` <-> SQLite `TEXT`

插入: Python str 插入为 SQLite TEXT。
读取: SQLite TEXT 读取为 Python str。

4.5 Python `bytes` <-> SQLite `BLOB`

插入: Python bytes 对象插入为 SQLite BLOB。
读取: SQLite BLOB 读取为 Python bytes 对象。

4.6 Python `bool` (True/False) <-> SQLite `INTEGER` (1/0)

插入: Python True 插入为 1，False 插入为 0。
读取: 默认情况下，SQLite INTEGER 的 1 和 0 读取为 Python int。
- 要实现自动转换: 在连接时使用 detect_types=sqlite3.PARSE_DECLTYPES 并确保表列声明为 BOOLEAN 或其他具有 NUMERIC 亲和性的类型 (NUMERIC 亲和性会将 0/1 识别为整数，并可以通过 detect_types 转换回 bool)。

4.7 Python `datetime` / `date` <-> SQLite `TEXT` (ISO 8601) 或 `INTEGER` (Unix时间戳)

插入: sqlite3 模块默认将 Python datetime.datetime 和 datetime.date 对象转换为 ISO 8601 格式的字符串 (YYYY-MM-DD HH:MM:SS.ffffff 或 YYYY-MM-DD) 存储在 TEXT 列中。
读取: 默认情况下，存储在 TEXT 列中的日期时间字符串会被读取为 Python str。
- 要实现自动转换: 在连接时使用 detect_types=sqlite3.PARSE_DECLTYPES 并确保表列声明为 DATE, DATETIME 或 TIMESTAMP。这样 sqlite3 会尝试将这些字符串解析回 Python datetime 或 date 对象。

5. 批量插入数据：`executemany()` 的效率

当需要插入大量数据时，逐行执行 execute() 会非常低效，因为每次 execute() 都可能涉及数据库的往返通信。cursor.executemany() 方法专为批量操作设计，显著提高了性能。

5.1 `executemany()` 语法与优势

executemany() 接受两个参数：

SQL 语句: 包含占位符的 SQL INSERT 语句。
参数序列: 一个可迭代对象（如列表），其中每个元素都是一个元组或列表，代表一行要插入的数据，与 SQL 语句中的占位符顺序对应。

# 示例数据
users_to_insert = [
    ("Charlie", "charlie@example.com"),
    ("David", "david@example.com"),
    ("Eve", "eve@example.com"),
]

# 批量插入
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users_to_insert)
conn.commit()
print(f"\n批量插入 {len(users_to_insert)} 条用户数据成功。")

5.2 性能对比 (单行插入 vs. 批量插入)

executemany() 的性能优势在于：

减少通信开销: 驱动程序将多行数据作为一个批次发送到数据库，而不是为每行发送一个单独的请求。
优化内部处理: SQLite 数据库引擎可以更高效地处理批量操作。

在插入成百上千甚至更多数据时，executemany() 可以比多次 execute() 快几个数量级。

6. 特殊插入操作

6.1 `INSERT INTO ... SELECT ...`

这种语法允许您从一个或多个现有表中查询数据，并将结果插入到另一个表中。

# 假设我们有一个 old_users 表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS old_users (
        old_id INTEGER PRIMARY KEY,
        old_name TEXT,
        old_email TEXT UNIQUE
    )
''')
cursor.executemany("INSERT INTO old_users VALUES (?, ?, ?)", [
    (101, 'Old John', 'oldjohn@example.com'),
    (102, 'Old Jane', 'oldjane@example.com')
])
conn.commit()

# 将 old_users 的数据导入到 users 表
cursor.execute('''
    INSERT INTO users (name, email)
    SELECT old_name, old_email FROM old_users WHERE old_id > 101;
''')
conn.commit()
print("\n从 old_users 表中导入数据到 users 表成功。")

6.2 `INSERT INTO ... DEFAULT VALUES`

如果所有列都有默认值，或者您只想插入一个完全由默认值组成的行，可以使用此语法：

# 假设 tasks 表有默认值 status='pending', created_at=CURRENT_TIMESTAMP
# CREATE TABLE tasks (task_id INTEGER PRIMARY KEY, description TEXT, status TEXT DEFAULT 'pending', created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
cursor.execute("INSERT INTO tasks DEFAULT VALUES;") # 插入一行所有列都为默认值或 NULL 的数据
conn.commit()
print("\n插入一行默认值的任务数据。")

注意: 这种语法只适用于没有 NOT NULL 约束但又没有默认值的列，或者所有 NOT NULL 列都有默认值的情况。

7. 错误处理与数据完整性

数据插入过程中可能会遇到各种错误，正确处理这些错误对于维护数据库的健壮性至关重要。

7.1 `sqlite3.IntegrityError` (约束违反)

当插入的数据违反了表的约束（如 PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY）时，会抛出 sqlite3.IntegrityError。

try:
    # 尝试插入一个 email 已经存在（UNIQUE 约束）的用户
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Duplicate User", "charlie@example.com"))
    conn.commit()
except sqlite3.IntegrityError as e:
    print(f"\n捕获到 IntegrityError: {e}")
    conn.rollback() # 回滚当前事务中的所有更改
    print("因约束冲突，插入操作已回滚。")

7.2 `sqlite3.OperationalError` (SQL 语法错误等)

当 SQL 语句语法错误、数据库文件被锁定、权限问题等操作性错误发生时，会抛出 sqlite3.OperationalError。

try:
    # 尝试执行一个错误的 SQL 语句
    cursor.execute("INSERT INTO non_existent_table (name) VALUES ('Test');")
    conn.commit()
except sqlite3.OperationalError as e:
    print(f"\n捕获到 OperationalError: {e}")
    conn.rollback()
    print("因操作错误，插入操作已回滚。")

7.3 事务管理 (`try-except-finally` 与 `with` 语句)

为了确保数据操作的原子性、一致性、隔离性和持久性 (ACID)，正确管理事务至关重要。

try-except-finally 块: 在操作可能失败时，使用 try-except 捕获异常，并在 except 块中调用 conn.rollback()。无论成功与否，在 finally 块中调用 conn.close()。
with 语句 (推荐): 这是最 Pythonic 的方式。当 with 块正常退出时，会自动调用 conn.commit()；如果 with 块内发生异常，则会自动调用 conn.rollback()，并在结束后关闭连接。

# 推荐使用 with 语句进行事务管理
import sqlite3
import os
import datetime

db_name = 'transaction_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;") # 启用外键

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS parents (
            parent_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL UNIQUE
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS children (
            child_id INTEGER PRIMARY KEY,
            parent_id INTEGER NOT NULL,
            child_name TEXT NOT NULL,
            FOREIGN KEY (parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE
        )
    ''')

    try:
        # 插入一个父级
        cursor.execute("INSERT INTO parents (parent_id, name) VALUES (?, ?)", (1, "Father John"))
        # 插入一个子级，引用上述父级
        cursor.execute("INSERT INTO children (child_id, parent_id, child_name) VALUES (?, ?, ?)", (101, 1, "Child A"))
        print("\n成功插入父级和子级。")

        # 尝试插入一个重复的父级名称 (UNIQUE 约束)
        cursor.execute("INSERT INTO parents (parent_id, name) VALUES (?, ?)", (2, "Father John")) # 会引发 IntegrityError
        # 如果这里没有异常，会继续执行
        cursor.execute("INSERT INTO children (child_id, parent_id, child_name) VALUES (?, ?, ?)", (102, 2, "Child B"))

    except sqlite3.IntegrityError as e:
        print(f"事务中捕获到 IntegrityError: {e}")
        # with 语句会在捕获到异常时自动回滚
    
    # 检查数据库状态
    cursor.execute("SELECT * FROM parents")
    print("Parents:", cursor.fetchall())
    cursor.execute("SELECT * FROM children")
    print("Children:", cursor.fetchall())
    # 预期结果：Father John (id 1) 存在，Child A (parent_id 1) 存在。
    # 第二次插入 Father John 失败，整个 try 块的事务回滚，
    # 所以 Child A 也不会存在 (因为事务被回滚了)。
    # 实际上，由于 `with` 块自动回滚了，因此 Parents 和 Children 表应该都是空的。
    # 这证明了事务的原子性：要么全部成功，要么全部失败。
    # 修正：在这个 `try` 块的例子中，如果捕获到异常，`with` 语句会在块结束时自动回滚。
    # 所以 Child A 也不会存在。
    # 预期结果：Parents 和 Children 表都是空的。

7.4 外键约束的启用 (`PRAGMA foreign_keys = ON;`)

非常重要: 每次建立新的数据库连接时，您都需要显式地执行 PRAGMA foreign_keys = ON; 语句来启用外键约束。否则，即使在 CREATE TABLE 语句中定义了外键，它也可能不会生效，导致您可以插入违反引用完整性的数据。

8. 最佳实践与注意事项

8.1 始终使用参数绑定 (防止 SQL 注入，自动类型转换)

这是最关键的实践。绝不应通过字符串拼接来构建包含用户提供数据的 SQL 语句。cursor.execute() 和 cursor.executemany() 的参数绑定机制是防止 SQL 注入的最佳防线。

8.2 使用 `with` 语句管理数据库连接和事务

它简化了错误处理和资源管理，自动处理 commit()/rollback() 和 close()，使代码更健壮、更简洁。

8.3 `executemany()` 用于批量插入以提高性能

当需要插入多行数据时，executemany() 是首选方法。它能显著减少数据库往返次数，提高插入效率。

8.4 预先验证数据

在尝试将数据插入数据库之前，尽可能在 Python 应用程序层面进行数据验证。这可以减少数据库因约束冲突而拒绝插入的次数，提高应用程序的响应速度和用户体验。

8.5 理解 `lastrowid` 的局限性

cursor.lastrowid 在 executemany() 后只返回最后插入行的 ROWID。如果您需要获取所有插入行的 ID，可能需要为每行单独执行 execute() 或使用其他策略 (例如在插入前生成 ID)。

8.6 适当处理 `datetime` 和 `BOOLEAN`

datetime: 默认存储为 ISO 8601 字符串。在连接时使用 detect_types=sqlite3.PARSE_DECLTYPES 并确保列声明为 DATE/DATETIME/TIMESTAMP，以便 sqlite3 自动在 Python datetime 对象和数据库字符串之间进行转换。
BOOLEAN: 默认存储为 1 或 0。同样，使用 detect_types=sqlite3.PARSE_DECLTYPES 和声明 BOOLEAN 列类型可以自动在 Python bool 和数据库整数之间进行转换。

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

（复用上一篇的提醒）SQLite 对 ALTER TABLE 的支持相对有限。例如，它不能直接删除列，也不能直接修改列的数据类型或约束（除了少数例外）。对于更复杂的模式更改（例如，删除列、修改列类型、重新排序列），常见的变通方法是：

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

8.8 安全警告：避免动态 DDL 中的用户输入

（复用上一篇的提醒）尽管 CREATE TABLE 语句通常是静态的，但在极少数情况下，如果您的应用程序需要根据用户输入动态地创建表名或列名，请务必对这些输入进行严格验证和白名单过滤。与 execute() 方法的参数绑定不同，表名和列名不能通过参数绑定传递。直接拼接未经净化的用户输入到 DDL 语句中可能会导致严重的 SQL 注入漏洞。在可能的情况下，应避免使用用户提供的名称来动态生成 DDL。

9. 综合代码示例

此示例将演示多种插入方法，包括单行插入、批量插入、INSERT OR IGNORE、错误处理、外键行为以及 Python 数据类型的映射。

import sqlite3
import os
import datetime

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

# 建议在连接时开启 detect_types，以便自动转换 DATE/DATETIME/TIMESTAMP 和 BOOLEAN
# 同时，设置 row_factory 以便通过列名访问数据
with sqlite3.connect(db_name, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    conn.row_factory = sqlite3.Row # 使结果集可以通过列名访问
    cursor = conn.cursor()

    # 1. 启用外键约束 (非常重要)
    cursor.execute("PRAGMA foreign_keys = ON;")

    # 2. 创建 Customers 表
    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,
            registration_date DATE DEFAULT CURRENT_DATE
        )
    ''')
    print("表 'customers' 已创建。")

    # 3. 创建 Products 表
    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)
        )
    ''')
    print("表 'products' 已创建。")

    # 4. 创建 Orders 表 (包含外键)
    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,
            is_completed BOOLEAN DEFAULT FALSE, -- BOOLEAN 具有 NUMERIC 亲和性，存储为 0/1
            FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
                ON DELETE CASCADE
                ON UPDATE NO ACTION
        )
    ''')
    print("表 'orders' 已创建。")

    # 5. 创建 order_items 表 (用于演示复合主键和外键)
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS order_items (
            order_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL CHECK(quantity > 0),
            price_at_order REAL NOT NULL,
            PRIMARY KEY (order_id, product_id),
            FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT -- 限制删除被引用产品
        )
    ''')
    print("表 'order_items' 已创建。\n")

    # --- 演示数据插入 ---

    # 5.1. 单行插入客户 (使用 Python datetime.date 对象)
    customer_data = ("Alice", "Smith", "alice@example.com", datetime.date(2023, 1, 1))
    cursor.execute("INSERT INTO customers (first_name, last_name, email, registration_date) VALUES (?, ?, ?, ?)", customer_data)
    alice_id = cursor.lastrowid
    print(f"单行插入客户 Alice (ID: {alice_id}, 注册日期: {customer_data[3]})")

    # 5.2. 批量插入产品
    products_to_insert = [
        ("Laptop", 1200.50, 20),
        ("Mouse", 25.00, 150),
        ("Keyboard", 75.99, 80),
        ("Monitor", 300.00, 30)
    ]
    cursor.executemany("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", products_to_insert)
    print(f"批量插入 {len(products_to_insert)} 个产品。")

    # 5.3. 插入一个订单 (使用 Python datetime.datetime 对象和 bool)
    bob_data = ("Bob", "Johnson", "bob@example.com", datetime.date(2023, 3, 15))
    cursor.execute("INSERT INTO customers (first_name, last_name, email, registration_date) VALUES (?, ?, ?, ?)", bob_data)
    bob_id = cursor.lastrowid
    print(f"插入客户 Bob (ID: {bob_id})")

    order_date = datetime.datetime.now()
    cursor.execute("INSERT INTO orders (customer_id, order_date, total_amount, is_completed) VALUES (?, ?, ?, ?)",
                   (alice_id, order_date, 1300.50, False)) # False 会被转换为 0
    alice_order_id = cursor.lastrowid
    print(f"插入 Alice 的订单 (ID: {alice_order_id}, 日期: {order_date}, 完成状态: False)")

    cursor.execute("INSERT INTO orders (customer_id, order_date, total_amount, is_completed) VALUES (?, ?, ?, ?)",
                   (bob_id, datetime.datetime(2023, 11, 1, 10, 0, 0), 200.00, True)) # True 会被转换为 1
    bob_order_id = cursor.lastrowid
    print(f"插入 Bob 的订单 (ID: {bob_order_id}, 完成状态: True)")

    # 5.4. 演示 INSERT OR IGNORE
    print("\n--- 演示 INSERT OR IGNORE ---")
    try:
        # 尝试插入一个 email 已经存在的客户 (Alice)
        cursor.execute("INSERT OR IGNORE INTO customers (first_name, last_name, email) VALUES (?, ?, ?)",
                       ("Alice Duplicate", "Smith Duplicate", "alice@example.com"))
        print("尝试用 INSERT OR IGNORE 插入重复邮件客户。")
        # 验证是否插入成功
        cursor.execute("SELECT * FROM customers WHERE email = ?", ("alice@example.com",))
        result = cursor.fetchall()
        print(f"查询重复邮件: {result}") # 应该只返回原始 Alice 的信息
    except Exception as e:
        print(f"捕获到意外错误: {e}")

    # 5.5. 演示 INSERT INTO ... SELECT ...
    print("\n--- 演示 INSERT INTO ... SELECT ---")
    cursor.execute('''
        INSERT INTO order_items (order_id, product_id, quantity, price_at_order)
        SELECT ?, product_id, 1, price FROM products WHERE name IN ('Laptop', 'Mouse');
    ''', (alice_order_id,))
    print(f"从 products 表中选择数据，插入 Alice 的订单项 (订单ID: {alice_order_id})。")

    # 5.6. 演示外键约束错误
    print("\n--- 演示外键约束错误 ---")
    try:
        # 尝试插入一个引用不存在客户的订单
        cursor.execute("INSERT INTO orders (customer_id, total_amount) VALUES (?, ?)", (999, 50.00))
        print("错误：不应该能插入引用不存在客户的订单。")
    except sqlite3.IntegrityError as e:
        print(f"捕获到外键 IntegrityError (引用不存在的客户): {e}")
        # with 语句会在 block 结束时自动回滚

    # 5.7. 演示 NOT NULL 约束错误
    print("\n--- 演示 NOT NULL 约束错误 ---")
    try:
        cursor.execute("INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)", ("Frank", None, "frank@example.com"))
        print("错误：不应该能插入 last_name 为 NULL 的客户。")
    except sqlite3.IntegrityError as e:
        print(f"捕获到 NOT NULL IntegrityError (last_name): {e}")

    # --- 查询以验证插入和类型转换 ---
    print("\n--- 查询数据以验证 ---")
    cursor.execute("SELECT customer_id, first_name, email, registration_date FROM customers ORDER BY customer_id")
    print("所有客户:")
    for row in cursor.fetchall():
        print(f"  ID: {row['customer_id']}, 姓名: {row['first_name']}, 邮箱: {row['email']}, 注册日期: {row['registration_date']} (类型: {type(row['registration_date'])})")

    cursor.execute("SELECT order_id, customer_id, order_date, is_completed FROM orders ORDER BY order_id")
    print("\n所有订单:")
    for row in cursor.fetchall():
        print(f"  订单ID: {row['order_id']}, 客户ID: {row['customer_id']}, 日期: {row['order_date']} (类型: {type(row['order_date'])}), 完成状态: {row['is_completed']} (类型: {type(row['is_completed'])})")

    cursor.execute("SELECT order_id, product_id, quantity FROM order_items ORDER BY order_id, product_id")
    print("\n所有订单项:")
    for row in cursor.fetchall():
        print(f"  订单ID: {row['order_id']}, 产品ID: {row['product_id']}, 数量: {row['quantity']}")

print(f"\n所有操作完成，数据库 '{db_name}' 已更新。")

10. 总结

为您详细介绍了 Python SQLite 中插入数据的过程。

核心要点回顾:

掌握 INSERT 语句的基本语法，理解指定列与隐式列的区别，以及 INSERT OR IGNORE 和 INSERT OR REPLACE 的冲突解决策略。
在 Python 中，始终使用参数绑定 (?) 进行数据插入，以确保安全性（防止 SQL 注入）和正确的类型转换。
理解 Python 数据类型如何映射到 SQLite 的五种存储类，并利用 detect_types=sqlite3.PARSE_DECLTYPES 处理 datetime 和 BOOLEAN 类型的自动转换。
利用 cursor.lastrowid 获取自动生成的 ROWID。
使用 cursor.executemany() 进行高效的批量数据插入。
学习 INSERT INTO ... SELECT ... 和 INSERT INTO ... DEFAULT VALUES 等特殊插入语法。
通过 try-except 块和 with 语句妥善处理 sqlite3.IntegrityError 和 sqlite3.OperationalError，确保事务的原子性。
务必在每个连接中启用外键约束 (PRAGMA foreign_keys = ON;)。
坚持最佳实践，如数据预验证和利用 with 语句进行事务管理。

通过这些知识，您将能够高效、安全、准确地将数据插入到 SQLite 数据库中，并构建健壮的数据库驱动应用程序。

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

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

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

更多推荐

两分钟安装 Hermes Agent

在当今快速发展的科技时代，人工智能（artificial intelligence，AI）技术正以惊人的速度改变着人们的生活和工作方式。在这个新时代的浪潮中，大模型技术成为AI领域的一颗耀眼新星。ChatGPT作为大模型技术的重要应用之一，正在引领着人机交互领域的革新浪潮。本书将带领读者深入探索大模型新时代，通过ChatGPT实战项目和内部解析，深入掌握基于ChatGPT的大模型应用开发领域的关键

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

【人工智能】AI基础知识（第七节：RAG）

RAG（检索增强生成）技术解决了大模型在处理私有和实时数据时的局限性。其核心流程包括：1）将用户提问向量化，通过语义搜索匹配相似内容；2）检索向量数据库找出最相关的TopK数据片段；3）将检索结果与原始问题结合形成增强提示；4）由大模型生成最终答案。通过向量化技术（将词语特征转化为多维数值）和余弦相似度计算，RAG能有效处理语义相近的查询（如"退商品"与"退货"），从而避免大模型的"幻觉"问题，为

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

大白话讲透RAG！彻底搞懂检索增强生成原理

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

所有评论(0)

查看更多评论

weixin_42107409

@weixin_42107409

已为社区贡献114条内容

Python SQLite：插入数据深度解析

weixin_42107409

目录

1. 引言：填充数据库的基石——数据插入

2. SQLite INSERT 语句基础

2.1 基本 INSERT 语法

2.2 指定列与隐式列

2.3 插入默认值

2.4 INSERT OR IGNORE 与 INSERT OR REPLACE

3. Python sqlite3 模块与数据插入

3.1 建立连接与获取游标

3.2 执行 INSERT 语句：参数绑定 (?)

3.3 获取插入行的 ROWID (cursor.lastrowid)

3.4 提交事务 (conn.commit())

4. Python 数据类型与 SQLite 存储类的映射

4.1 Python None <-> SQLite NULL

4.2 Python int <-> SQLite INTEGER

4.3 Python float <-> SQLite REAL

4.4 Python str <-> SQLite TEXT

4.5 Python bytes <-> SQLite BLOB

4.6 Python bool (True/False) <-> SQLite INTEGER (1/0)

4.7 Python datetime / date <-> SQLite TEXT (ISO 8601) 或 INTEGER (Unix时间戳)

5. 批量插入数据：executemany() 的效率

5.1 executemany() 语法与优势

5.2 性能对比 (单行插入 vs. 批量插入)

6. 特殊插入操作

6.1 INSERT INTO ... SELECT ...

6.2 INSERT INTO ... DEFAULT VALUES

7. 错误处理与数据完整性

7.1 sqlite3.IntegrityError (约束违反)

7.2 sqlite3.OperationalError (SQL 语法错误等)

7.3 事务管理 (try-except-finally 与 with 语句)

7.4 外键约束的启用 (PRAGMA foreign_keys = ON;)

8. 最佳实践与注意事项

8.1 始终使用参数绑定 (防止 SQL 注入，自动类型转换)

8.2 使用 with 语句管理数据库连接和事务

8.3 executemany() 用于批量插入以提高性能

8.4 预先验证数据

8.5 理解 lastrowid 的局限性

8.6 适当处理 datetime 和 BOOLEAN

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

8.8 安全警告：避免动态 DDL 中的用户输入

9. 综合代码示例

10. 总结

所有评论(0)

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

weixin_42107409

2. SQLite `INSERT` 语句基础

2.1 基本 `INSERT` 语法

2.4 `INSERT OR IGNORE` 与 `INSERT OR REPLACE`

3. Python `sqlite3` 模块与数据插入

3.2 执行 `INSERT` 语句：参数绑定 (`?`)

3.3 获取插入行的 `ROWID` (`cursor.lastrowid`)

3.4 提交事务 (`conn.commit()`)

4.1 Python `None` <-> SQLite `NULL`

4.2 Python `int` <-> SQLite `INTEGER`

4.3 Python `float` <-> SQLite `REAL`

4.4 Python `str` <-> SQLite `TEXT`

4.5 Python `bytes` <-> SQLite `BLOB`

4.6 Python `bool` (True/False) <-> SQLite `INTEGER` (1/0)

4.7 Python `datetime` / `date` <-> SQLite `TEXT` (ISO 8601) 或 `INTEGER` (Unix时间戳)

5. 批量插入数据：`executemany()` 的效率

5.1 `executemany()` 语法与优势

6.1 `INSERT INTO ... SELECT ...`

6.2 `INSERT INTO ... DEFAULT VALUES`

7.1 `sqlite3.IntegrityError` (约束违反)

7.2 `sqlite3.OperationalError` (SQL 语法错误等)

7.3 事务管理 (`try-except-finally` 与 `with` 语句)

7.4 外键约束的启用 (`PRAGMA foreign_keys = ON;`)

8.2 使用 `with` 语句管理数据库连接和事务

8.3 `executemany()` 用于批量插入以提高性能

8.5 理解 `lastrowid` 的局限性

8.6 适当处理 `datetime` 和 `BOOLEAN`

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