将基于aiosqlite的SQLite .db转PostgreSQL遇编码错误，求原因与方案

编码错误常见成因 #

• SQLite对字符串编码校验极宽松：允许TEXT字段存储任意字节（包括非UTF-8的Latin1、GBK等编码数据，甚至NULL字节），而PostgreSQL的TEXT/VARCHAR字段严格要求UTF-8编码，导入不符合规范的字节就会触发错误。
• aiosqlite编码处理疏漏：若SQLite数据库创建时指定了非UTF-8编码，aiosqlite默认用UTF-8读取会导致字节解码失败；或数据中存在无效UTF-8序列，异步读取流程未做错误处理直接传入PostgreSQL。
• 特殊字符冲突：SQLite允许存储NULL字节（\x00），但PostgreSQL的TEXT类型禁止这类字符，写入时直接报错。

更优转换方式 #

方式1：专业迁移工具pgloader #

pgloader是专为数据库迁移设计的工具，内置SQLite到PostgreSQL的适配逻辑，自动处理编码转换、数据类型映射、特殊字符清洗，操作简单：

1. 安装pgloader（如Ubuntu系统：sudo apt install pgloader）
2. 执行迁移命令：

pgloader sqlite:///your_sqlite_db.db postgresql://user:password@host:port/postgres_db

方式2：Python脚本优化（替换aiosqlite） #

放弃异步的aiosqlite，用标准库sqlite3同步读取并提前清洗数据：

import sqlite3
import psycopg2

# 连接SQLite并读取数据
conn_sqlite = sqlite3.connect("your_db.db", encoding="utf-8")
cursor_sqlite = conn_sqlite.cursor()
cursor_sqlite.execute("SELECT * FROM your_table")
rows = cursor_sqlite.fetchall()

# 清洗数据：修复无效UTF-8、移除NULL字节
cleaned_rows = []
for row in rows:
    cleaned_row = []
    for item in row:
        if isinstance(item, str):
            cleaned_item = item.encode("utf-8", errors="replace").decode("utf-8").replace("\x00", "")
            cleaned_row.append(cleaned_item)
        else:
            cleaned_row.append(item)
    cleaned_rows.append(cleaned_row)

# 写入PostgreSQL
conn_pg = psycopg2.connect("dbname=postgres_db user=user password=password host=host")
cursor_pg = conn_pg.cursor()
cursor_pg.executemany("INSERT INTO your_table VALUES (%s, %s, ...)", cleaned_rows)
conn_pg.commit()

方式3：SQL文件导出+导入 #

用命令行工具导出SQLite为SQL文件，修正语法后导入PostgreSQL：

1. 导出SQLite数据（指定UTF-8编码）：

sqlite3 -encoding utf-8 your_db.db .dump > dump.sql

2. 编辑dump.sql：替换SQLite特有语法（如AUTOINCREMENT改为SERIAL或GENERATED ALWAYS AS IDENTITY，BOOLEAN改为BOOL）
3. 导入到PostgreSQL：

psql -d postgres_db -U user -f dump.sql

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