Python中如何让SQLite自定义NumPy数组类型持久化到新表？

问题场景 #

使用Python sqlite3 扩展存储NumPy数组时，通过CREATE TABLE AS SELECT创建新表后，新表中自定义array类型无法自动转换回NumPy数组，仅能获取原始二进制数据。临时通过ALTER TABLE+UPDATE的方式可解决，但不够优雅。

原因分析 #

• CREATE TABLE AS SELECT的类型继承逻辑：SQLite执行该语句时，仅复制字段的**原始存储类型（如BLOB）**和数据内容，不会继承原表的自定义字段类型声明。因此test_2的embedding字段实际类型为BLOB，而非自定义的array，查询时不会触发注册的convert_array转换器。
• 内置CAST不支持自定义类型：SQLite原生CAST无法识别用户注册的自定义array类型，CAST(embedding AS array)会被判定为无效转换，导致数据被错误处理为0。

更优解决办法 #

方法1：显式定义表结构后插入数据 #

先手动创建带有正确字段类型的空表，再通过INSERT INTO...SELECT填充数据，确保新表字段类型被正确声明：

# 创建指定自定义类型的空表
cursor.execute('CREATE TABLE test_2 (idx integer primary key, embedding array);')
# 从原表导入数据
cursor.execute('INSERT INTO test_2 SELECT idx, embedding FROM test_1;')
conn.commit()

此方式直接保证test_2的embedding字段类型为array，查询时会自动触发转换器转换为NumPy数组。

方法2：注册SQL自定义函数实现查询时转换 #

如果无法修改表结构，可注册SQL自定义函数，在查询阶段主动将二进制数据转换为NumPy数组：

def convert_blob_to_array(blob):
    return convert_array(blob)

# 向SQLite注册自定义函数
conn.create_function('to_array', 1, convert_blob_to_array)

# 查询时调用函数完成转换
cursor.execute("SELECT idx, to_array(embedding) FROM test_2;")
data_2 = cursor.fetchall()

内容的提问来源于stack exchange，提问作者Henry