Python SQLite：创建表深度解析

weixin_42107409

1862人浏览 · 2025-09-18 08:55:30

weixin_42107409 · 2025-09-18 08:55:30 发布

引言：数据库结构基石——表
SQLite 的数据类型与类型亲和性
- 2.1 SQLite 的五种核心存储类
- 2.2 类型亲和性：列声明的“偏好”
  - 2.2.1 INTEGER Affinity
  - 2.2.2 TEXT Affinity
  - 2.2.3 REAL Affinity
  - 2.2.4 NUMERIC Affinity (含 BOOLEAN, DATE, DATETIME)
  - 2.2.5 NONE Affinity (BLOB)
- 2.3 类型亲和性如何影响数据存储（行为示例）
CREATE TABLE 语句基础语法
- 3.1 基本结构
- 3.2 IF NOT EXISTS 子句
列约束（Column Constraints）详解
- 4.1 PRIMARY KEY
  - 4.1.1 单列主键
  - 4.1.2 复合主键
- 4.2 AUTOINCREMENT (性能考量与特殊行为)
- 4.3 NOT NULL (与 DEFAULT 的交互)
- 4.4 UNIQUE (深入 NULL 值处理)
- 4.5 DEFAULT
- 4.6 CHECK
- 4.7 FOREIGN KEY (外键)
  - 4.7.1 语法与引用
  - 4.7.2 ON DELETE 和 ON UPDATE 动作
  - 4.7.3 启用外键约束：PRAGMA foreign_keys = ON;
DROP TABLE 语句：删除表
- 5.1 DROP TABLE
- 5.2 IF EXISTS 子句
在 Python sqlite3 中创建表
- 6.1 建立连接与获取游标
- 6.2 执行 CREATE TABLE SQL
- 6.3 提交事务 (推荐 with 语句)
综合代码示例
- 7.1 包含多种类型与约束的表创建
- 7.2 演示约束行为
- 7.3 datetime 和 BOOLEAN 的自动转换演示 (detect_types)
最佳实践与注意事项
- 8.1 始终使用 IF NOT EXISTS
- 8.2 精心规划数据库模式
- 8.3 理解并利用类型亲和性
- 8.4 确保外键已启用
- 8.5 适当处理 datetime 和 BOOLEAN
- 8.6 错误处理与事务管理
- 8.7 ALTER TABLE 限制与常见变通方案
- 8.8 安全警告：避免动态 DDL 中的用户输入
总结

1. 引言：数据库结构基石——表

在关系型数据库中，**表（Table）是组织和存储数据的基本结构。它由一系列行（Rows）和列（Columns）**组成，每列都有一个名称和定义的数据类型，而每行则代表一个独立的记录。在 SQLite 中，创建表是数据库设计的第一步，它定义了数据如何被存储以及可以施加哪些规则和限制。

本指南将深入探讨如何使用 SQL 的 CREATE TABLE 语句在 SQLite 数据库中定义这些结构，并通过 Python 的 sqlite3 模块来执行这些操作。我们将特别关注 SQLite 独特的类型系统、各种约束的精确行为，以及在实际应用中的最佳实践。

2. SQLite 的数据类型与类型亲和性

在创建表时，您需要为每个列指定一个数据类型。理解 SQLite 独特的类型系统是至关重要的。

2.1 SQLite 的五种核心存储类

SQLite 是一种动态类型的数据库。它不强制列必须只存储声明类型的数据。相反，它只有五种原始的存储类（Storage Classes），数据最终会以这些形式存储在磁盘上：

NULL: 表示一个空值。 (Python: None)
INTEGER: 存储带符号的整数。 (Python: int)
REAL: 存储浮点数。 (Python: float)
TEXT: 存储字符串数据。 (Python: str)
BLOB: 存储二进制大对象。 (Python: bytes)

2.2 类型亲和性：列声明的“偏好”

虽然 SQLite 是动态类型的，但您在 CREATE TABLE 语句中为列指定的类型名称（如 INTEGER, TEXT, DATETIME 等）并非毫无用处。这些类型声明会赋予列一个类型亲和性（Type Affinity），这可以理解为列对某种存储类的“偏好”。当数据被插入到具有特定亲和性的列中时，SQLite 会尝试将其转换为该亲和性类型。

SQLite 定义了五种类型亲和性，它们根据列的声明类型名称来确定：

2.2.1 `INTEGER` Affinity

列类型包含: “INT” (如 INTEGER, INT, BIGINT, TINYINT 等)。
偏好: 存储为 INTEGER。
行为: 如果插入的值能被解释为整数，则转换为 INTEGER；如果插入浮点数，会截断小数部分。

2.2.2 `TEXT` Affinity

列类型包含: “CHAR”, “CLOB”, “TEXT” (如 VARCHAR, TEXT, NVARCHAR 等)。
偏好: 存储为 TEXT。
行为: 任何插入的值都会尝试转换为 TEXT。

2.2.3 `REAL` Affinity

列类型包含: “REAL”, “FLOA”, “DOUB” (如 REAL, DOUBLE, FLOAT 等)。
偏好: 存储为 REAL。
行为: 任何插入的值都会尝试转换为 REAL。

2.2.4 `NUMERIC` Affinity (含 `BOOLEAN`, `DATE`, `DATETIME`)

列类型包含: “NUM”, “DEC”, “BOOL”, “DATE”, “DATETIME” (如 NUMERIC, BOOLEAN, DATE, DATETIME 等)。这是最通用的数字类型。
偏好: 存储为 INTEGER 或 REAL。
行为:
- 如果数据能表示为 INTEGER，则存储为 INTEGER。
- 如果数据能表示为 REAL，则存储为 REAL。
- 否则，存储为 TEXT 或 BLOB。
特别说明:
- BOOLEAN: 在 SQLite 中通常被视为具有 NUMERIC 亲和性。Python 的 True 和 False 默认适配为 1 和 0 (INTEGER 存储类)。
- DATE / DATETIME / TIMESTAMP: 这些类型也具有 NUMERIC 亲和性。然而，Python 的 datetime 对象默认适配为 ISO 8601 格式的 TEXT 字符串。在读取时，如果连接启用了 detect_types (例如 sqlite3.PARSE_DECLTYPES)，并且列声明是 DATE/DATETIME/TIMESTAMP，sqlite3 模块会自动将这些文本字符串转换回 Python 的 datetime 对象。

2.2.5 `NONE` Affinity (`BLOB`)

列类型不匹配以上任何规则 (如 BLOB 或自定义类型)。
偏好: 没有特定偏好，按原样存储数据的原始存储类。
行为: 不尝试进行类型转换。

2.3 类型亲和性如何影响数据存储（行为示例）

为了更直观地理解，我们将列比作一个“有偏好但很灵活的容器”。它声明了自己偏爱装什么（类型亲和性），但如果你硬要装别的东西，只要能放进去，它也不会拒绝，只是可能会自己调整一下。

列声明类型	亲和性	插入值 (Python 类型)	SQLite 存储类	备注
`INTEGER`	INTEGER	`10` (`int`)	`INTEGER`	符合亲和性
`INTEGER`	INTEGER	`3.14` (`float`)	`INTEGER`	`REAL` 被截断为 `INTEGER` `3`
`INTEGER`	INTEGER	`'hello'` (`str`)	`TEXT`	无法转换为数字，存储为 `TEXT`
`REAL`	REAL	`5` (`int`)	`REAL`	`INTEGER` 转换为 `REAL` `5.0`
`REAL`	REAL	`'2.71'` (`str`)	`REAL`	`TEXT` 转换为 `REAL` `2.71`
`TEXT`	TEXT	`123` (`int`)	`TEXT`	`INTEGER` 转换为 `TEXT` `'123'`
`DATETIME`	NUMERIC	`datetime.now()`	`TEXT`	默认适配为 ISO 8601 字符串
`DATETIME`	NUMERIC	`12345` (`int`)	`INTEGER`	符合 `NUMERIC` 亲和性，存储为 `INTEGER`
`BLOB`	NONE	`b'raw'` (`bytes`)	`BLOB`	符合亲和性
`BLOB`	NONE	`'any'` (`str`)	`TEXT`	无亲和性，按原样存储值的存储类

这种灵活性是 SQLite 的一个重要特征，但同时也要求开发者清楚数据存储的实际方式，以避免意外行为。

3. `CREATE TABLE` 语句基础语法

CREATE TABLE 语句用于在数据库中创建一个新的表。

3.1 基本结构

CREATE TABLE table_name (
    column1_name DATATYPE [CONSTRAINT],
    column2_name DATATYPE [CONSTRAINT],
    ...
    [table_constraint]
);

table_name: 要创建的表的名称。
column_name: 表中列的名称。
DATATYPE: 列的数据类型声明 (如 INTEGER, TEXT, REAL, BOOLEAN, DATETIME 等)。这决定了列的类型亲和性。
[CONSTRAINT]: 可选的列级约束。
[table_constraint]: 可选的表级约束 (如复合主键、外键)。

3.2 `IF NOT EXISTS` 子句

为了防止在尝试创建已存在的表时发生错误，可以使用 IF NOT EXISTS 子句。如果表已经存在，这条语句将被忽略，不会引发错误。

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

4. 列约束（Column Constraints）详解

约束是施加在表列上的一组规则，用于限制可以存储在列中的数据类型、范围或关系，以维护数据的完整性和准确性。

4.1 `PRIMARY KEY`

主键 (PRIMARY KEY) 是一个唯一标识表中每一行的列或一组列。

特点:
- 值必须唯一。
- 隐含 NOT NULL (主键列不能包含 NULL 值)。
- 一个表只能有一个主键。
- 在 SQLite 中，如果一个 INTEGER PRIMARY KEY 列没有显式指定值，它将自动递增。

4.1.1 单列主键

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY, -- product_id 是主键，并自动递增
    name TEXT NOT NULL
);

4.1.2 复合主键

由多个列组成的主键。在表级定义。

CREATE TABLE order_items (
    order_id INTEGER,
    item_number INTEGER,
    product_name TEXT,
    PRIMARY KEY (order_id, item_number) -- 复合主键
);

4.2 `AUTOINCREMENT` (性能考量与特殊行为)

AUTOINCREMENT 关键字只能用于 INTEGER PRIMARY KEY 列。它的作用是：

强制主键列的值严格递增。
确保即使删除行，其 ID 也不会被重新使用。
保证新的 ID 总是大于任何之前分配的 ID。

重要性能考量: AUTOINCREMENT 会带来额外的性能开销，因为它需要维护一个额外的内部计数器来保证 ID 永不复用。在大多数情况下，仅使用 INTEGER PRIMARY KEY 即可满足自动递增的需求（它会保证唯一性和递增性，但在删除行后，其 ID 可能会被重新使用），AUTOINCREMENT 仅在严格需要确保即使删除行后，其 ID 也绝不被重新使用的特定场景下才建议使用。

CREATE TABLE logs (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT, -- 严格递增且不复用已删除的ID
    message TEXT
);

4.3 `NOT NULL` (与 `DEFAULT` 的交互)

NOT NULL 约束强制列不能存储 NULL 值。

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT
);

NOT NULL 与 DEFAULT 的协同: 如果一个列被定义为 NOT NULL 且您在 INSERT 时未为其提供值：如果该列同时定义了 DEFAULT 值，则会使用默认值；否则，将引发 sqlite3.IntegrityError。

4.4 `UNIQUE` (深入 `NULL` 值处理)

UNIQUE 约束强制列中的所有值都必须是唯一的。可以有多个 UNIQUE 约束在一个表中。

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL, -- username 必须唯一且不为空
    email TEXT UNIQUE
);

特别注意 NULL 值: 与许多其他数据库（如 PostgreSQL 或 SQL Server）不同，SQLite 认为两个 NULL 值是不同的。因此，一个 UNIQUE 约束的列可以包含多个 NULL 值，除非同时指定了 NOT NULL 约束。例如，在上面的 users 表中，email 列可以有多行都包含 NULL 值，但不能有多行包含相同的非 NULL 电子邮件地址。

4.5 `DEFAULT`

DEFAULT 约束为列定义了一个默认值。如果在插入新行时没有为该列指定值，则会自动使用默认值。

CREATE TABLE tasks (
    task_id INTEGER PRIMARY KEY,
    description TEXT NOT NULL,
    status TEXT DEFAULT 'pending', -- 默认状态为 'pending'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- 默认创建时间为当前时间
);

4.6 `CHECK`

CHECK 约束用于强制列中的值满足一个特定的布尔条件。

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK (age >= 18 AND age <= 65), -- 年龄必须在 18 到 65 之间
    salary REAL CHECK (salary > 0)
);

4.7 `FOREIGN KEY` (外键)

外键 (FOREIGN KEY) 用于建立两个表之间的关联。它是一列（或一组列），其值引用了另一个表的主键（或唯一键）。这有助于维护引用完整性。

4.7.1 语法与引用

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

这里，orders.customer_id 是外键，它引用了 customers.customer_id。

4.7.2 `ON DELETE` 和 `ON UPDATE` 动作

当被引用的主键行在父表中被删除或更新时，可以定义外键的行为：

NO ACTION (默认): 如果有引用行，则不允许删除/更新父表行。
RESTRICT: 与 NO ACTION 类似，但通常更严格，明确禁止操作。
SET NULL: 如果父表行被删除/更新，子表中对应的外键值设置为 NULL。
SET DEFAULT: 如果父表行被删除/更新，子表中对应的外键值设置为其默认值。
CASCADE: 如果父表行被删除/更新，子表中对应的引用行也会被删除/更新。

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE       -- 如果客户被删除，其所有订单也将被删除
        ON UPDATE NO ACTION     -- 不允许更新客户ID，如果存在订单引用
);

4.7.3 启用外键约束：`PRAGMA foreign_keys = ON;`

重要提示: 在 SQLite 中，外键约束默认是禁用的，为了让外键生效，您必须在每个连接中执行 PRAGMA foreign_keys = ON; 语句。

import sqlite3
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;") # 启用外键约束
# ... 接下来创建表和操作数据 ...

5. `DROP TABLE` 语句：删除表

5.1 `DROP TABLE`

用于从数据库中删除一个现有表。

DROP TABLE table_name;

5.2 `IF EXISTS` 子句

为了防止在尝试删除不存在的表时引发错误，可以使用 IF EXISTS。

DROP TABLE IF EXISTS old_data;

6. 在 Python `sqlite3` 中创建表

在 Python 中，使用 sqlite3 模块创建表涉及以下几个步骤：

6.1 建立连接与获取游标

首先，您需要使用 sqlite3.connect() 建立与数据库的连接，然后使用 conn.cursor() 创建一个游标对象。

import sqlite3
import os

db_name = 'my_app.db'
# 可以选择性地删除现有数据库文件，以便每次运行都从新开始
if os.path.exists(db_name):
    os.remove(db_name)

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

6.2 执行 `CREATE TABLE` SQL

使用游标对象的 execute() 方法来执行 CREATE TABLE SQL 语句。

# 创建一个简单的表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
    )
''')

6.3 提交事务 (推荐 `with` 语句)

CREATE TABLE 语句是数据定义语言 (DDL) 的一部分，但 SQLite 将 DDL 语句包装在隐式事务中。因此，为了确保更改被持久化到数据库文件，您需要调用 conn.commit()。

推荐实践: 使用 with 语句来管理连接，它会自动处理 commit() 或 rollback() 以及连接的关闭，确保资源正确释放和事务的原子性。

import sqlite3
import os

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

with sqlite3.connect(db_name) as conn:
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    # with 语句结束时，如果无异常则自动 commit，有异常则自动 rollback
print(f"数据库 '{db_name}' 和 'messages' 表已创建。")

7. 综合代码示例

此示例将演示如何创建包含多种类型和约束的表，并展示 datetime 和 BOOLEAN 的默认适配以及通过 detect_types 进行自动转换。

import sqlite3
import os
import datetime

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

# 建议在连接时开启 detect_types，以便自动转换 DATE/DATETIME/TIMESTAMP
# 同时，设置 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 表 (包含外键和 ON DELETE CASCADE 动作)
    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' 已创建。")

    # --- 演示数据插入及约束行为 ---

    # 插入客户数据
    cursor.execute("INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)",
                   ("Alice", "Smith", "alice@example.com"))
    alice_id = cursor.lastrowid
    print(f"\n插入客户: Alice Smith (ID: {alice_id})")

    cursor.execute("INSERT INTO customers (first_name, last_name, email, registration_date) VALUES (?, ?, ?, ?)",
                   ("Bob", "Johnson", "bob@example.com", datetime.date(2023, 1, 15)))
    bob_id = cursor.lastrowid
    print(f"插入客户: Bob Johnson (ID: {bob_id})")

    # 演示 UNIQUE 约束
    try:
        # 尝试插入重复 email (UNIQUE 约束)
        cursor.execute("INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)",
                       ("Charlie", "Brown", "alice@example.com"))
        # 如果没有错误，这里会提交
    except sqlite3.IntegrityError as e:
        print(f"捕获到 UNIQUE 约束错误: {e}")
        conn.rollback() # 回滚失败的事务
        print("重复 email 插入失败，事务已回滚。")
    finally:
        conn.commit() # 提交前面的成功插入

    # 演示 UNIQUE 约束对 NULL 的处理
    cursor.execute("INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)", ("David", "Lee", None))
    cursor.execute("INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)", ("Eve", "Wang", None))
    conn.commit()
    print("\n插入两行 email 为 NULL 的客户，成功。")
    cursor.execute("SELECT * FROM customers WHERE email IS NULL")
    print("NULL 邮件客户:", cursor.fetchall())


    # 插入产品数据
    cursor.executemany("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", [
        ("Widget A", 19.99, 100),
        ("Gadget B", 299.50, 50),
        ("Tool C", 5.00, 200)
    ])
    print("\n插入产品数据。")

    # 演示 CHECK 约束
    try:
        # 尝试插入价格为负的产品 (CHECK 约束)
        cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Invalid Product", -10.00))
        # 如果没有错误，这里会提交
    except sqlite3.IntegrityError as e:
        print(f"捕获到 CHECK 约束错误: {e}")
        conn.rollback()
        print("负价格产品插入失败，事务已回滚。")
    finally:
        conn.commit() # 提交前面的成功插入

    # 插入订单数据
    now = datetime.datetime.now()
    cursor.execute("INSERT INTO orders (customer_id, order_date, total_amount, is_completed) VALUES (?, ?, ?, ?)",
                   (alice_id, now, 150.75, True))
    order1_id = cursor.lastrowid
    print(f"\n插入订单 (ID: {order1_id}, 客户ID: {alice_id}, 完成状态: True)")

    cursor.execute("INSERT INTO orders (customer_id, order_date, total_amount) VALUES (?, ?, ?)",
                   (bob_id, datetime.datetime(2023, 10, 20, 14, 30, 0), 25.00))
    order2_id = cursor.lastrowid
    print(f"插入订单 (ID: {order2_id}, 客户ID: {bob_id}, 完成状态: 默认False)")

    # --- 查询并演示 datetime 和 BOOLEAN 自动转换 ---
    print("\n--- 查询订单数据 (含日期时间和布尔类型自动转换) ---")
    cursor.execute("SELECT customer_id, order_date, is_completed FROM orders WHERE order_id = ?", (order1_id,))
    order_row = cursor.fetchone()
    if order_row:
        print(f"订单ID: {order1_id}")
        print(f"  客户ID: {order_row['customer_id']}")
        print(f"  下单日期: {order_row['order_date']} (类型: {type(order_row['order_date'])})") # 预期 datetime.datetime
        print(f"  是否完成: {order_row['is_completed']} (类型: {type(order_row['is_completed'])})") # 预期 bool
    
    # --- 演示外键 ON DELETE CASCADE ---
    print("\n--- 演示外键 ON DELETE CASCADE ---")
    cursor.execute("SELECT COUNT(*) FROM orders WHERE customer_id = ?", (alice_id,))
    print(f"删除前 Alice 的订单数量: {cursor.fetchone()[0]}") # 预期 1

    cursor.execute("DELETE FROM customers WHERE customer_id = ?", (alice_id,))
    # with 语句会在 block 结束时自动提交，这里无需额外 conn.commit()
    print(f"已删除客户 Alice (ID: {alice_id})。")

    cursor.execute("SELECT COUNT(*) FROM orders WHERE customer_id = ?", (alice_id,))
    print(f"删除后 Alice 的订单数量: {cursor.fetchone()[0]}") # 预期 0 (因为 ON DELETE CASCADE)

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

8. 最佳实践与注意事项

8.1 始终使用 `IF NOT EXISTS`

在创建表时，总是使用 CREATE TABLE IF NOT EXISTS。这可以防止在脚本多次运行时因尝试创建已存在的表而导致 OperationalError。

8.2 精心规划数据库模式

在编写 CREATE TABLE 语句之前，仔细考虑表结构、列名、数据类型和约束。一个良好设计的模式是数据库高效运行和数据完整性的基础。

列名: 保持清晰、一致和描述性。
数据类型: 虽然 SQLite 是动态类型，但仍然应该声明最符合您意图的类型 (例如 INTEGER for IDs, TEXT for names, DATETIME for dates)。这有助于可读性，并使 detect_types 等功能正常工作。

8.3 理解并利用类型亲和性

记住列的类型亲和性如何影响数据的存储，尤其是在插入不同类型的数据时。对于日期时间 (DATETIME, DATE, TIMESTAMP) 和布尔值 (BOOLEAN)，在连接时使用 detect_types=sqlite3.PARSE_DECLTYPES 可以让 sqlite3 自动在 Python 对象和 SQLite 存储类之间进行转换，从而简化代码。

8.4 确保外键已启用

始终记住在每个数据库连接的开始执行 PRAGMA foreign_keys = ON;，否则外键约束将不生效，可能导致数据不一致。

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

datetime: 默认存储为 ISO 8601 字符串 (TEXT)。要自动转换回 Python datetime 对象，请使用 detect_types=sqlite3.PARSE_DECLTYPES 和声明 DATETIME/DATE/TIMESTAMP 列类型。
BOOLEAN: 默认存储为 INTEGER (True->1, False->0)。开启 detect_types=sqlite3.PARSE_DECLTYPES 和声明 BOOLEAN 列类型可以自动将 0/1 转换回 Python bool。

8.6 错误处理与事务管理

在执行 CREATE TABLE 或插入数据时，可能会遇到 sqlite3.OperationalError (例如，SQL 语法错误) 或 sqlite3.IntegrityError (例如，违反 UNIQUE 或 NOT NULL 约束)。使用 try-except 块来捕获这些异常，并在必要时使用 conn.rollback() 来撤销事务中的更改。强烈推荐使用 with 语句来管理数据库连接，它能确保在正常完成时自动提交事务，在发生异常时自动回滚事务，并最终关闭连接。

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

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

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

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

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

9. 总结

为您详细介绍了 Python SQLite 中创建表的过程。这份深度解析覆盖了从核心概念到高级实践的各个方面。

核心要点回顾:

理解 SQLite 的动态类型系统和类型亲和性如何影响数据存储，即使列有声明类型。
掌握 CREATE TABLE 的基本 SQL 语法，并利用 IF NOT EXISTS 子句。
深入理解并应用各种列约束 (PRIMARY KEY, AUTOINCREMENT, NOT NULL, UNIQUE, DEFAULT, CHECK) 来维护数据完整性，并特别注意 AUTOINCREMENT 的性能和 UNIQUE 对 NULL 的处理。
正确使用外键 (FOREIGN KEY) 及其 ON DELETE/ON UPDATE 动作，并务必启用 PRAGMA foreign_keys = ON;。
在 Python 中，通过 sqlite3.connect() 建立连接，cursor.execute() 执行 SQL，并使用 with 语句来自动管理事务和连接。
利用 sqlite3.connect(detect_types=sqlite3.PARSE_DECLTYPES) 和 conn.row_factory = sqlite3.Row 来提高日期时间、布尔值处理的便利性和查询结果的可读性。
了解 SQLite ALTER TABLE 的局限性及其变通方案。
始终警惕动态 DDL 中的安全问题，避免用户输入直接拼接。

通过这些全面而深入的知识，您将能够设计和创建健壮、高效且数据完整性极高的 SQLite 数据库表结构，并避免常见的陷阱。

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

import os
files_to_clean = [
    'my_app.db',
    'my_app_with_context.db',
    'advanced_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：创建表深度解析

weixin_42107409

目录

1. 引言：数据库结构基石——表

2. SQLite 的数据类型与类型亲和性

2.1 SQLite 的五种核心存储类

2.2 类型亲和性：列声明的“偏好”

2.2.1 INTEGER Affinity

2.2.2 TEXT Affinity

2.2.3 REAL Affinity

2.2.4 NUMERIC Affinity (含 BOOLEAN, DATE, DATETIME)

2.2.5 NONE Affinity (BLOB)

2.3 类型亲和性如何影响数据存储（行为示例）

3. CREATE TABLE 语句基础语法

3.1 基本结构

3.2 IF NOT EXISTS 子句

4. 列约束（Column Constraints）详解

4.1 PRIMARY KEY

4.1.1 单列主键

4.1.2 复合主键

4.2 AUTOINCREMENT (性能考量与特殊行为)

4.3 NOT NULL (与 DEFAULT 的交互)

4.4 UNIQUE (深入 NULL 值处理)

4.5 DEFAULT

4.6 CHECK

4.7 FOREIGN KEY (外键)

4.7.1 语法与引用

4.7.2 ON DELETE 和 ON UPDATE 动作

4.7.3 启用外键约束：PRAGMA foreign_keys = ON;

5. DROP TABLE 语句：删除表

5.1 DROP TABLE

5.2 IF EXISTS 子句

6. 在 Python sqlite3 中创建表

6.1 建立连接与获取游标

6.2 执行 CREATE TABLE SQL

6.3 提交事务 (推荐 with 语句)

7. 综合代码示例

8. 最佳实践与注意事项

8.1 始终使用 IF NOT EXISTS

8.2 精心规划数据库模式

8.3 理解并利用类型亲和性

8.4 确保外键已启用

8.5 适当处理 datetime 和 BOOLEAN

8.6 错误处理与事务管理

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

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

9. 总结

所有评论(0)

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

weixin_42107409

2.2.1 `INTEGER` Affinity

2.2.2 `TEXT` Affinity

2.2.3 `REAL` Affinity

2.2.4 `NUMERIC` Affinity (含 `BOOLEAN`, `DATE`, `DATETIME`)

2.2.5 `NONE` Affinity (`BLOB`)

3. `CREATE TABLE` 语句基础语法

3.2 `IF NOT EXISTS` 子句

4.1 `PRIMARY KEY`

4.2 `AUTOINCREMENT` (性能考量与特殊行为)

4.3 `NOT NULL` (与 `DEFAULT` 的交互)

4.4 `UNIQUE` (深入 `NULL` 值处理)

4.5 `DEFAULT`

4.6 `CHECK`

4.7 `FOREIGN KEY` (外键)

4.7.2 `ON DELETE` 和 `ON UPDATE` 动作

4.7.3 启用外键约束：`PRAGMA foreign_keys = ON;`

5. `DROP TABLE` 语句：删除表

5.1 `DROP TABLE`

5.2 `IF EXISTS` 子句

6. 在 Python `sqlite3` 中创建表

6.2 执行 `CREATE TABLE` SQL

6.3 提交事务 (推荐 `with` 语句)

8.1 始终使用 `IF NOT EXISTS`

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

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