Pandas DataFrame to_sql写入SQLite时如何保留数据类型

问题背景 #

你的DataFrame包含嵌套JSON转成的object类型列，直接用df.to_sql()写入SQLite时失败，而astype(str)的方法会丢失原有数据类型，不够优雅。

可行解决方案 #

SQLite本身没有直接对应Python object的原生类型，但可以通过序列化嵌套数据为JSON字符串的方式存储，读取时再反序列化，以此保留原数据结构和类型。以下是两种具体实现方式：

方式1：自定义序列化+SQLAlchemy类型映射

这种方式兼容性强，适用于大多数SQLite版本：

1. 导入依赖库：

import pandas as pd
import json
from sqlalchemy import create_engine, types

2. 定义序列化/反序列化函数：

def serialize_nested(obj):
    return json.dumps(obj)

def deserialize_nested(json_str):
    return json.loads(json_str)

3. 处理DataFrame并写入数据库：

# 复制原DataFrame避免修改源数据
df_to_write = df.copy()
# 对嵌套列做序列化处理
df_to_write['nested1'] = df_to_write['nested1'].apply(serialize_nested)
df_to_write['nested2'] = df_to_write['nested2'].apply(serialize_nested)

# 创建SQLAlchemy引擎
engine = create_engine('sqlite:///your_db.db')
# 定义列类型映射，指定嵌套列为TEXT类型
type_map = {
    'nested1': types.TEXT,
    'nested2': types.TEXT
}

# 写入数据库
df_to_write.to_sql('target_table', engine, if_exists='replace', index=False, dtype=type_map)

4. 读取时恢复原数据结构：

df_read = pd.read_sql('SELECT * FROM target_table', engine)
df_read['nested1'] = df_read['nested1'].apply(deserialize_nested)
df_read['nested2'] = df_read['nested2'].apply(deserialize_nested)

方式2：利用SQLite原生JSON类型（需SQLite 3.31.0+）

如果你的SQLite版本在3.31.0及以上（支持原生JSON类型），可以直接用SQLAlchemy的JSON类型简化操作：

1. 导入依赖库：

import pandas as pd
import json
from sqlalchemy import create_engine, JSON

2. 处理DataFrame并写入：

df_to_write = df.copy()
df_to_write['nested1'] = df_to_write['nested1'].apply(json.dumps)
df_to_write['nested2'] = df_to_write['nested2'].apply(json.dumps)

engine = create_engine('sqlite:///your_db.db')
type_map = {
    'nested1': JSON,
    'nested2': JSON
}

df_to_write.to_sql('target_table', engine, if_exists='replace', index=False, dtype=type_map)

3. 读取时自动反序列化：

df_read = pd.read_sql('SELECT * FROM target_table', engine)

说明 #

这两种方法都能避免全量转字符串导致的类型丢失，同时正确存储嵌套结构。如果不想用SQLAlchemy，直接使用sqlite3连接时，也可以手动在写入前序列化嵌套列、读取后反序列化，逻辑和上述一致。

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