目录

  1. 引言：SQLite 的动态类型特性
  2. SQLite 的核心存储类 (Storage Classes)
    • 2.1 NULL
    • 2.2 INTEGER
    • 2.3 REAL
    • 2.4 TEXT
    • 2.5 BLOB
  3. SQLite 的类型亲和性 (Type Affinity)
    • 3.1 TEXT Affinity
    • 3.2 NUMERIC Affinity
    • 3.3 INTEGER Affinity
    • 3.4 REAL Affinity
    • 3.5 NONE Affinity
    • 3.6 类型亲和性如何影响数据存储
  4. Python sqlite3 模块的默认类型映射
    • 4.1 Python 到 SQLite 的默认映射 (插入数据)
    • 4.2 SQLite 到 Python 的默认映射 (读取数据)
  5. 自定义类型处理 (Adapters & Converters)
    • 5.1 类型适配器 (Type Adapters)：Python -> SQLite
      • 5.1.1 注册适配器 sqlite3.register_adapter()
      • 5.1.2 示例：datetime 对象适配
    • 5.2 类型转换器 (Type Converters)：SQLite -> Python
      • 5.2.1 detect_types 参数
      • 5.2.2 注册转换器 sqlite3.register_converter()
      • 5.2.3 示例：datetime 对象转换
  6. 代码示例
    • 6.1 默认类型映射示例
    • 6.2 使用 datetime 对象 (默认行为)
    • 6.3 自定义 datetime 适配器和转换器
    • 6.4 使用 detect_types=sqlite3.PARSE_DECLTYPES
  7. 最佳实践与注意事项
    • 7.1 理解 SQLite 的类型灵活性
    • 7.2 始终明确声明列类型
    • 7.3 优先使用内置 detect_types 处理常见类型
    • 7.4 慎用 BLOB 存储大型文件
    • 7.5 考虑 NULL 值的处理
  8. 总结

1. 引言：SQLite 的动态类型特性

与其他严格类型（如 MySQL, PostgreSQL）的数据库不同，SQLite 是一种弱类型或动态类型的数据库。这意味着在 SQLite 中，您可以在一个声明为 INTEGER 类型的列中存储一个字符串值，而不会报错。SQLite 更加关注存储的值本身的数据类型（称为存储类），而不是列声明时指定的类型。

然而，这并不意味着列类型声明是无用的。列类型声明（如 INTEGER, TEXT）会给列赋予一个类型亲和性 (Type Affinity)，这会影响 SQLite 如何处理插入的数据以及在某些操作中如何进行类型转换。

理解 SQLite 的这种动态类型特性，以及 Python sqlite3 模块如何与它交互，是高效、正确使用 SQLite 的关键。

2. SQLite 的核心存储类 (Storage Classes)

SQLite 实际上只有五种原始的数据类型，它们被称为存储类 (Storage Classes)。当您将数据插入 SQLite 时，它会根据值的特性将其存储为这五种存储类之一。

2.1 NULL

表示一个空值。

  • Python 对应: None

2.2 INTEGER

存储带符号的整数。根据值的大小，它可以存储在 1 到 8 字节的存储空间中。

  • Python 对应: int

2.3 REAL

存储浮点数，使用 8 字节的 IEEE 浮点格式。

  • Python 对应: float

2.4 TEXT

存储字符串数据，使用数据库编码（UTF-8, UTF-16BE 或 UTF-16LE）。

  • Python 对应: str

2.5 BLOB

存储二进制大对象（Binary Large Object），以原始字节形式存储，不进行任何解释。

  • Python 对应: bytes

3. SQLite 的类型亲和性 (Type Affinity)

尽管 SQLite 是动态类型的，但每个列在 CREATE TABLE 语句中声明时都会被赋予一个类型亲和性。类型亲和性是 SQLite 对列的“偏好”存储类型。当您尝试将某种数据插入到具有特定类型亲和性的列中时，SQLite 会尝试将数据转换为该亲和性类型。

SQLite 定义了五种类型亲和性：

3.1 TEXT Affinity

列类型包含 “CHAR”, “CLOB”, “TEXT” 等字符串。

  • 偏好: 存储为 TEXT。如果插入 INTEGER 或 REAL，SQLite 会尝试转换为 TEXT。
  • 示例: VARCHAR(20), TEXT, CLOB

3.2 NUMERIC Affinity

列类型包含 “NUM”, “DEC”, “BOOL”, “DATE”, “DATETIME” 等。这是最通用的数字类型。

  • 偏好: 存储为 INTEGER 或 REAL。
  • 规则:
    • 如果数据能表示为 INTEGER，则存储为 INTEGER。
    • 如果数据能表示为 REAL，则存储为 REAL。
    • 否则，存储为 TEXT 或 BLOB。
  • 示例: NUMERIC, BOOLEAN, DATE, DATETIME

3.3 INTEGER Affinity

列类型包含 “INT” 等字符串。

  • 偏好: 存储为 INTEGER。是 NUMERIC 的一个特例，更倾向于 INTEGER。
  • 规则: 与 NUMERIC 类似，但当插入浮点数时，会先尝试将其转换为整数。
  • 示例: INTEGER, INT, BIGINT

3.4 REAL Affinity

列类型包含 “REAL”, “FLOA”, “DOUB” 等字符串。

  • 偏好: 存储为 REAL。
  • 规则: 如果插入整数，会将其转换为浮点数。
  • 示例: REAL, DOUBLE, FLOAT

3.5 NONE Affinity

列类型不匹配以上任何规则。

  • 偏好: 没有特定偏好，按原样存储数据的存储类。
  • 示例: BLOB, ANY 或未声明类型的列。

3.6 类型亲和性如何影响数据存储

例如，如果您有一个 INTEGER 亲和性的列：

  • 插入 10 (Python int) -> 存储为 INTEGER 10。
  • 插入 3.14 (Python float) -> 存储为 INTEGER 3 (小数部分被截断)。
  • 插入 'hello' (Python str) -> 存储为 TEXT 'hello' (因为无法转换为数字)。

这种灵活性是 SQLite 的一个重要特征，但有时也可能导致意想不到的行为，因此在 CREATE TABLE 语句中明确声明列类型仍然是最佳实践。

4. Python sqlite3 模块的默认类型映射

sqlite3 模块在数据进出数据库时，会有一套默认的类型映射规则。

4.1 Python 到 SQLite 的默认映射 (插入数据)

当您使用 cursor.execute() 或 cursor.executemany() 插入数据时，Python 对象会被自动转换为 SQLite 的存储类：

Python 类型 SQLite 存储类 备注
None NULL
int INTEGER 任意大小的整数
float REAL 浮点数
str TEXT 字符串
bytes BLOB 二进制数据
bool INTEGER True 存储为 1，False 存储为 0
datetime.date TEXT 转换为 ISO 8601 字符串 YYYY-MM-DD
datetime.datetime TEXT 转换为 ISO 8601 字符串 YYYY-MM-DD HH:MM:SS.ffffff

4.2 SQLite 到 Python 的默认映射 (读取数据)

当您使用 cursor.fetchone(), cursor.fetchall(), cursor.fetchmany() 读取数据时，SQLite 的存储类会被自动转换为 Python 对象：

SQLite 存储类 Python 类型 备注
NULL None
INTEGER int
REAL float
TEXT str
BLOB bytes

重要提示: 默认情况下，sqlite3 模块不会将 TEXT 存储类中的 YYYY-MM-DD HH:MM:SS 格式的日期时间字符串自动转换回 Python 的 datetime 对象。它们将以 str 类型返回。这正是需要自定义转换器或使用 detect_types 的原因之一。

5. 自定义类型处理 (Adapters & Converters)

sqlite3 模块提供了强大的机制来自定义 Python 类型与 SQLite 存储类之间的转换：类型适配器 (Type Adapters) 和 类型转换器 (Type Converters)。

5.1 类型适配器 (Type Adapters)：Python -> SQLite

当您想将一个非标准（或默认未处理）的 Python 对象存储到 SQLite 数据库时，您需要注册一个适配器。适配器函数接受一个 Python 对象作为输入，并返回一个 SQLite 能够理解的存储类（NULL, INTEGER, REAL, TEXT, BLOB）的 Python 表示（如 None, int, float, str, bytes）。

5.1.1 注册适配器 sqlite3.register_adapter()

sqlite3.register_adapter(type, callable)

  • type: 您想要适配的 Python 类型 (例如 datetime.datetime)。
  • callable: 一个可调用对象 (函数)，它接受该 Python 类型的一个实例，并返回一个 str, bytes, int, float 或 None。
5.1.2 示例：datetime 对象适配

默认情况下 datetime 对象被适配为 ISO 8601 字符串。但如果您想将其存储为 Unix 时间戳（整数），可以这样做：

import sqlite3
import datetime

# 适配器函数：将 datetime 对象转换为 Unix 时间戳 (整数)
def adapt_datetime_to_timestamp(dt):
    return int(dt.timestamp())

# 注册适配器
sqlite3.register_adapter(datetime.datetime, adapt_datetime_to_timestamp)
sqlite3.register_adapter(datetime.date, adapt_datetime_to_timestamp) # 也可以适配 date

# 现在，当插入 datetime.datetime 对象时，它会被适配为 INTEGER

5.2 类型转换器 (Type Converters)：SQLite -> Python

当您想从 SQLite 数据库读取数据并将其转换为特定的 Python 对象时，您需要注册一个转换器。转换器函数接受从 SQLite 读取的原始字节（bytes）数据作为输入，并返回所需的 Python 对象。

5.2.1 detect_types 参数

要让 sqlite3 模块知道需要应用转换器，您必须在 sqlite3.connect() 中设置 detect_types 参数。

  • sqlite3.PARSE_DECLTYPES: 根据列的声明类型 (如 TIMESTAMP, DATE) 触发转换器。这是最推荐的方式。
  • sqlite3.PARSE_COLNAMES: 根据列名中的类型信息触发转换器。
  • sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES: 结合两种模式。
5.2.2 注册转换器 sqlite3.register_converter()

sqlite3.register_converter(typename, callable)

  • typename: SQLite 列的声明类型（字符串形式，不区分大小写）。例如，如果您在 CREATE TABLE 中声明 timestamp DATETIME，则 typename 可以是 'DATETIME'。
  • callable: 一个可调用对象 (函数)，它接受 SQLite 返回的原始 bytes 数据，并返回一个 Python 对象。
5.2.3 示例：datetime 对象转换

要将存储为 ISO 8601 字符串或 Unix 时间戳的日期时间数据转换回 datetime 对象：

import sqlite3
import datetime

# 转换器函数：将 ISO 8601 字符串转换为 datetime 对象
def convert_datetime_from_string(s):
    return datetime.datetime.fromisoformat(s.decode('utf-8')) # s 是 bytes 类型

# 转换器函数：将 Unix 时间戳 (bytes) 转换为 datetime 对象
def convert_datetime_from_timestamp(s):
    return datetime.datetime.fromtimestamp(int(s))

# 注册转换器
# 注意：typename 是列声明中的类型，不区分大小写
sqlite3.register_converter("DATETIME", convert_datetime_from_string)
sqlite3.register_converter("TIMESTAMP", convert_datetime_from_timestamp) # 如果列声明是 TIMESTAMP
sqlite3.register_converter("DATE", lambda s: datetime.date.fromisoformat(s.decode('utf-8')))

# 连接时需要设置 detect_types
# conn = sqlite3.connect('my_db.db', detect_types=sqlite3.PARSE_DECLTYPES)

6. 代码示例

6.1 默认类型映射示例

import sqlite3
import os
import datetime

db_name = 'default_types.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 data_types_demo (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            col_null NULL,          -- NONE affinity
            col_integer INTEGER,    -- INTEGER affinity
            col_real REAL,          -- REAL affinity
            col_text TEXT,          -- TEXT affinity
            col_blob BLOB,          -- NONE affinity
            col_boolean BOOLEAN,    -- NUMERIC affinity
            col_datetime DATETIME   -- NUMERIC affinity
        )
    ''')
    conn.commit()

    # 插入不同 Python 类型的数据
    now = datetime.datetime.now()
    blob_data = b'this is raw bytes'

    cursor.execute("INSERT INTO data_types_demo VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                   (None, None, 100, 3.14, "Hello SQLite", blob_data, True, now))
    conn.commit()

    # 查询数据并查看 Python 类型
    cursor.execute("SELECT * FROM data_types_demo")
    row = cursor.fetchone()

    print("\n--- 默认类型映射示例 ---")
    print(f"原始数据: {row}")
    print(f"Python 类型: {[type(item) for item in row]}")
    # 预期输出:
    # (1, None, 100, 3.14, 'Hello SQLite', b'this is raw bytes', 1, 'YYYY-MM-DD HH:MM:SS.ffffff')
    # [<class 'int'>, <class 'NoneType'>, <class 'int'>, <class 'float'>, <class 'str'>, <class 'bytes'>, <class 'int'>, <class 'str'>]

6.2 使用 datetime 对象 (默认行为)

可以看到 datetime 对象在插入时被转为字符串，读取时仍是字符串。

import sqlite3
import os
import datetime

db_name = 'datetime_default.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 events (
            id INTEGER PRIMARY KEY,
            name TEXT,
            event_time DATETIME
        )
    ''')
    conn.commit()

    event_time = datetime.datetime.now()
    cursor.execute("INSERT INTO events (name, event_time) VALUES (?, ?)", ("Meeting", event_time))
    conn.commit()

    cursor.execute("SELECT name, event_time FROM events")
    name, retrieved_time = cursor.fetchone()

    print("\n--- datetime 默认行为 ---")
    print(f"插入的 Python datetime 类型: {type(event_time)}, 值: {event_time}")
    print(f"读取的 Python 类型: {type(retrieved_time)}, 值: {retrieved_time}")
    # 预期 retrieved_time 是 <class 'str'>

6.3 自定义 datetime 适配器和转换器

将 datetime 存储为 Unix 时间戳 (INTEGER)，并读取回 datetime 对象。

import sqlite3
import os
import datetime

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

# 1. 注册适配器 (Python -> SQLite)
def adapt_datetime_to_timestamp(dt):
    return int(dt.timestamp()) # 将 datetime 转换为整数时间戳

sqlite3.register_adapter(datetime.datetime, adapt_datetime_to_timestamp)

# 2. 注册转换器 (SQLite -> Python)
def convert_timestamp_to_datetime(s):
    # s 是 bytes 类型，需要解码为 int
    return datetime.datetime.fromtimestamp(int(s))

# 注册针对 'TIMESTAMP' 声明类型的转换器
sqlite3.register_converter("TIMESTAMP", convert_timestamp_to_datetime)

# 连接数据库时，必须指定 detect_types=sqlite3.PARSE_DECLTYPES
with sqlite3.connect(db_name, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE tasks (
            id INTEGER PRIMARY KEY,
            description TEXT,
            created_at TIMESTAMP -- 声明为 TIMESTAMP 以触发转换器
        )
    ''')
    conn.commit()

    current_time = datetime.datetime.now()
    cursor.execute("INSERT INTO tasks (description, created_at) VALUES (?, ?)", ("Finish report", current_time))
    conn.commit()

    cursor.execute("SELECT description, created_at FROM tasks")
    description, retrieved_timestamp = cursor.fetchone()

    print("\n--- 自定义 datetime 适配器和转换器 ---")
    print(f"插入的 Python datetime 类型: {type(current_time)}, 值: {current_time}")
    print(f"读取的 Python 类型: {type(retrieved_timestamp)}, 值: {retrieved_timestamp}")
    # 预期 retrieved_timestamp 是 <class 'datetime.datetime'>
    print(f"读取的值是否与插入值相同: {current_time == retrieved_timestamp}")

6.4 使用 detect_types=sqlite3.PARSE_DECLTYPES

不注册自定义适配器，而是使用内置的 detect_types 功能。sqlite3 模块内置了对 DATE, TIME, TIMESTAMP (存储为 ISO 8601 字符串) 的转换。

import sqlite3
import os
import datetime

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

# 3. 连接时，开启类型检测。这里我们没有自定义适配器/转换器。
#    sqlite3 模块会根据声明类型 (如 DATETIME) 自动将 ISO 8601 字符串转换为 datetime 对象。
with sqlite3.connect(db_name, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE logs (
            id INTEGER PRIMARY KEY,
            message TEXT,
            log_time DATETIME -- 声明为 DATETIME
        )
    ''')
    conn.commit()

    log_entry_time = datetime.datetime.now()
    # 默认适配器会将 datetime.datetime 转换为 ISO 8601 字符串
    cursor.execute("INSERT INTO logs (message, log_time) VALUES (?, ?)", ("System startup", log_entry_time))
    conn.commit()

    cursor.execute("SELECT message, log_time FROM logs")
    message, retrieved_log_time = cursor.fetchone()

    print("\n--- 使用 detect_types=sqlite3.PARSE_DECLTYPES (内置转换) ---")
    print(f"插入的 Python datetime 类型: {type(log_entry_time)}, 值: {log_entry_time}")
    print(f"读取的 Python 类型: {type(retrieved_log_time)}, 值: {retrieved_log_time}")
    # 预期 retrieved_log_time 是 <class 'datetime.datetime'>
    print(f"读取的值是否与插入值相同: {log_entry_time == retrieved_log_time}")

# 清理测试文件
if os.path.exists(db_name): os.remove(db_name)
if os.path.exists('default_types.db'): os.remove('default_types.db')
if os.path.exists('datetime_default.db'): os.remove('datetime_default.db')
if os.path.exists('datetime_custom.db'): os.remove('datetime_custom.db')

7. 最佳实践与注意事项

7.1 理解 SQLite 的类型灵活性

SQLite 的动态类型特性既是优势也是挑战。它允许您在不严格遵守类型约束的情况下快速开发，但也可能在不经意间存储了预期之外的数据类型，导致查询或应用逻辑错误。始终清楚您正在存储什么，以及如何存储。

7.2 始终明确声明列类型

虽然 SQLite 允许您在 INTEGER 列中存储字符串，但强烈建议在 CREATE TABLE 语句中明确声明列的预期类型（如 INTEGER, REAL, TEXT, BLOB, DATETIME 等）。这有助于：

  • 可读性: 提高数据库模式的可读性。
  • 类型亲和性: 确保 SQLite 对列应用正确的类型亲和性。
  • 内置转换: 配合 detect_types 参数时，实现自动的 Python 类型转换。
  • 工具支持: 许多数据库管理工具会依赖这些声明来正确显示和验证数据。

7.3 优先使用内置 detect_types 处理常见类型

对于日期时间 (datetime.date, datetime.datetime) 和布尔值 (bool) 等常见类型，sqlite3 模块提供了内置的适配器和转换器。只需在 sqlite3.connect() 中设置 detect_types=sqlite3.PARSE_DECLTYPES，并确保您的列声明是 DATE, DATETIME, TIMESTAMP 或 BOOLEAN，即可实现自动的 Python 对象转换。这通常比手动注册自定义函数更简单和推荐。

7.4 慎用 BLOB 存储大型文件

尽管 SQLite 可以存储 BLOB 数据，但将非常大的文件（如高分辨率图片、视频）直接存储在数据库中通常不是最佳实践。这会显著增加数据库文件的大小，影响性能（尤其是备份和恢复），并且通常不如在文件系统上存储文件并在数据库中存储文件路径高效。BLOB 更适合存储小到中等大小的二进制数据，如序列化的 Python 对象、加密密钥等。

7.5 考虑 NULL 值的处理

Python 的 None 会映射到 SQLite 的 NULL 存储类。在设计数据库模式时，考虑哪些列允许 NULL 值（默认）以及哪些列应该使用 NOT NULL 约束。

8. 总结

为您详细介绍了 Python SQLite 的数据类型及其与 Python 类型的映射。

核心要点回顾:

  • SQLite 是动态类型数据库，数据存储基于五种存储类: NULL, INTEGER, REAL, TEXT, BLOB。
  • 列的类型声明赋予其类型亲和性 (TEXT, NUMERIC, INTEGER, REAL, NONE)，影响数据存储和转换的偏好。
  • Python sqlite3 模块对 Python 类型和 SQLite 存储类有默认的映射规则。
  • 对于需要特殊处理的类型（如将 datetime 存储为时间戳），可以使用 类型适配器 (sqlite3.register_adapter) 将 Python 对象转换为 SQLite 兼容的存储类。
  • 从 SQLite 读取时，可以使用 类型转换器 (sqlite3.register_converter) 结合 sqlite3.connect(detect_types=...) 将 SQLite 原始数据转换为特定的 Python 对象。
  • 最佳实践是明确声明列类型，利用内置的 detect_types 处理常见类型，并在必要时使用自定义适配器和转换器，以确保数据的一致性和代码的可维护性。

理解和掌握这些类型处理机制，将使您能够更精确、更安全地在 Python 应用程序中使用 SQLite 数据库。

# python # sqlite # jvm
Logo
亚马逊云科技技术品牌专区

更多推荐

cover

把整个物联网开发搬进了微信：MCPIOT Studio 这个 AI+IoT 开发工具，让我重新定义了“硬件开发“

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

【花雕动手做】行空板 K10 系列实验之 3630 无刷减速电机语音识别控制双轮差速小车

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

航天空仿真试验大模型系统已融合人工智能AI软件平台

航天空仿真试验大模型系统已融合人工智能AI软件平台

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