这是一个非常重要的主题,因为 SQLite 的类型系统与许多其他关系型数据库（如 PostgreSQL 或 MySQL）有显著不同，理解它对于正确使用 SQLite 至关重要。

核心概念：Affinity（类型亲和性）

首先要明确一个关键点：SQLite 本身没有静态的、严格的数据类型，它使用一种称为 “类型亲和性”（Type Affinity） 的机制。

这意味着：

• 你创建表时,可以为每个列指定一个数据类型（如 INTEGER, TEXT, REAL）。
• 但这只是一种“建议”或“亲和性”，SQLite 不会强制要求你存入该列的数据必须是这个类型。
• 当你向表中插入数据时,SQLite 会根据列的亲和性，尝试将你提供的数据转换成最合适的类型。

一个定义为 INTEGER 的列，你尝试存入字符串 '123'，SQLite 会自动将其转换为整数 123，但如果你存入字符串 'hello'，它不会报错，而是将其存储为 TEXT 类型。

SQLite 的五种类型亲和性

SQLite 主要定义了五种类型亲和性，它们决定了数据如何被存储和转换。

Python sqlite3 与 SQLite 的类型映射

当你使用 Python 的 sqlite3 模块时，Python 的数据类型和 SQLite 的亲和性之间需要进行转换。sqlite3 模块会自动处理这个过程。

Python 到 SQLite 的映射

当你将 Python 对象通过 cursor.execute() 或 cursor.executemany() 存入数据库时，会发生以下转换：

示例：

import sqlite3
# 创建一个包含不同类型亲和性列的表
conn = sqlite3.connect(':memory:') # 使用内存数据库
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE test_table (
    text_col TEXT,
    int_col INTEGER,
    real_col REAL,
    blob_col BLOB,
    none_col NONE
)
''')
# 插入混合类型的数据
data_to_insert = [
    ('This is a string', 123, 45.67, b'binary_data', None),
    ('123', '456', '78.90', 'not_binary', True) # Python的True会被转为1
]
cursor.executemany('INSERT INTO test_table VALUES (?, ?, ?, ?, ?)', data_to_insert)
conn.commit()
# 查看表结构
cursor.execute("PRAGMA table_info(test_table);")
print("--- Table Schema ---")
for row in cursor.fetchall():
    print(row)
print("\n--- Table Data ---")
# 查询并打印所有数据
cursor.execute("SELECT * FROM test_table;")
for row in cursor.fetchall():
    print(row)
conn.close()

输出分析：

• '123' 存入 INTEGER 列，被转换为 123。
• '456' 存入 REAL 列，被转换为 0。
• '78.90' 存入 REAL 列，被转换为 9。
• 'not_binary' 存入 BLOB 列，因为它不是一个 bytes 对象，SQLite 会将其视为 TEXT。
• True 存入 NONE 列，Python 会将其作为 1 (整数) 传递，SQLite 会将其存储为 INTEGER。

SQLite 到 Python 的映射

当你从数据库中查询数据时,sqlite3 会将 SQLite 的存储类型转换回 Python 的原生类型，这个转换是自动且可预测的。

示例： 接续上面的代码，如果我们查询 test_table：

# 假设连接仍然打开
cursor.execute("SELECT * FROM test_table;")
rows = cursor.fetchall()
for row in rows:
    # 检查每个元素的 Python 类型
    print("\n--- Row Data and Types ---")
    for i, value in enumerate(row):
        print(f"Value: {value}, Python Type: {type(value)}")

输出：

--- Row Data and Types ---
Value: This is a string, Python Type: <class 'str'>
Value: 123, Python Type: <class 'int'>
Value: 45.67, Python Type: <class 'float'>
Value: b'binary_data', Python Type: <class 'bytes'>
Value: None, Python Type: <class 'NoneType'>
--- Row Data and Types ---
Value: 123, Python Type: <class 'str'>  <-- 注意：这里 '123' 被当作 TEXT 存入，所以查询出来是 str
Value: 456, Python Type: <class 'int'>  <-- '456' 被成功转为 INTEGER 存入，查询出来是 int
Value: 78.9, Python Type: <class 'float'> <-- '78.90' 被转为 REAL 存入，查询出来是 float
Value: not_binary, Python Type: <class 'str'> <-- 'not_binary' 被当作 TEXT 存入
Value: 1, Python Type: <class 'int'>  <-- True 被转为 INTEGER 1 存入

关键观察：

• 即使你存入的是字符串 '123'，如果它被存入了 INTEGER 类型的列，查询出来后就是 Python 的整数 123。
• 反之,如果你存入的是字符串 '123'，但列的亲和性是 TEXT（或 NONE），那么查询出来后它仍然是字符串 '123'。

如何控制类型转换？

使用 adapt() 和 converters

虽然 sqlite3 的默认转换已经足够应对大多数场景，但有时你可能需要自定义转换逻辑，这可以通过 sqlite3.register_adapter() 和 sqlite3.register_converter() 来实现。

• register_adapter(function): 告诉 sqlite3 如何将一个自定义的 Python 类型转换为 SQLite 可以理解的基本类型（str, int, float, bytes）。
• register_converter(name, function): 告诉 sqlite3 如何将 SQLite 的列数据（通常是 str 或 bytes）转换回你的自定义 Python 类型。

示例：自定义日期类型

import sqlite3
from datetime import date
# 1. 定义一个适配器，将 Python date 对象转换为字符串
def adapt_date(dt):
    return dt.isoformat() # 格式如 'YYYY-MM-DD'
# 2. 定义一个转换器，将字符串转换回 Python date 对象
def convert_date(s):
    return date.fromisoformat(s)
# 注册适配器和转换器
sqlite3.register_adapter(date, adapt_date)
sqlite3.register_converter("DATE", convert_date)
# 创建连接时，需要指定 detect_types=sqlite3.PARSE_DECLTYPES
# 这样 sqlite3 才会识别列类型声明并应用转换器
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()
# 创建一个带有 DATE 类型亲和性的列
cursor.execute('CREATE TABLE events (name TEXT, event_date DATE)')
# 插入一个 Python date 对象
today = date.today()
cursor.execute("INSERT INTO events VALUES (?, ?)", ('Python Conference', today))
conn.commit()
# 查询数据，event_date 列会自动转换回 date 对象
cursor.execute("SELECT * FROM events;")
name, event_date = cursor.fetchone()
print(f"Name: {name}")
print(f"Event Date: {event_date}")
print(f"Type of Event Date: {type(event_date)}")
conn.close()

输出：

Name: Python Conference
Event Date: 2025-10-27  # (假设今天是这个日期)
Type of Event Date: <class 'datetime.date'>

使用 detect_types

在上面的例子中,我们使用了 detect_types=sqlite3.PARSE_DECLTYPES，这个参数告诉 sqlite3 在创建表时，要解析列的类型声明，并尝试使用已注册的转换器进行转换。

• sqlite3.PARSE_DECLTYPES: 解析列的类型声明（如 DATE）。
• sqlite3.PARSE_COLNAMES: 解析列名中的类型信息（如 event_date [DATE]）。
• 两者可以结合使用：detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES。

最佳实践和建议

1. 明确列类型：即使 SQLite 不强制，也要在创建表时为每个列明确指定类型（如 INTEGER, TEXT, REAL），这为代码和他人阅读提供了清晰的意图，并利用了 SQLite 的类型亲和性。
2. 让 sqlite3 自动处理：对于绝大多数应用，依赖 sqlite3 默认的 Python-to-SQLite 和 SQLite-to-Python 类型转换就足够了，代码简洁且不易出错。
3. 为特定需求注册转换器：当你的应用中需要频繁处理自定义类型（如 datetime, decimal.Decimal, UUID 等）时，使用 register_adapter 和 register_converter 是一个非常好的选择，能让你的代码更符合 Pythonic 风格。
4. 使用 占位符：始终使用参数化查询（ 占位符），而不是自己拼接 SQL 字符串，这不仅能防止 SQL 注入攻击，也能确保 Python 对象被正确地传递给 sqlite3 进行类型转换。
5. 理解 NULL：NULL 在数据库中代表“未知”或“不适用”，不要将其与空字符串 或零 0 混淆，Python 中的 None 对应数据库的 NULL。

掌握 sqlite3 的类型系统，将帮助你写出更健壮、更可维护的 Python 数据库应用程序。