Mbox转Sqlite数据库编码问题求助：UTF-8编码失败及特殊字符乱码

一、解决「Error binding parameter 1 - probably unsupported type」报错 #

这个错误是因为邮件对象的部分字段类型无法被SQLite识别（比如邮件头的To/Cc可能是列表格式，或者存在其他非基础数据类型），需要把所有插入字段统一转为字符串类型：

修改数据生成函数如下：

def to_insert():
    for message in mbox.values():
        Dionyversite = dict(message.items())
        # 将所有非字符串类型的字段转为字符串
        for key, value in Dionyversite.items():
            if not isinstance(value, str):
                Dionyversite[key] = str(value)
        # 处理邮件内容：多部分邮件（如带附件、HTML+文本双版本）会返回列表，需转为字符串
        payload = message.get_payload()
        if isinstance(payload, list):
            payload = "\n--- 邮件分割线 ---".join(str(part) for part in payload)
        Dionyversite["payload"] = payload
        yield Dionyversite

二、解决特殊字符显示为「�」的编码问题 #

1. 修复Mbox生成时的编码逻辑 #

原函数强制用UTF-8读取输入文件，且写入时未指定编码，容易导致字符丢失或乱码。修改后的函数：

import os
import tokenize

def makeMBox(fIn,fOut):
    if not os.path.exists(fIn):
        return False
    if os.path.exists(fOut):
        return False

    # 写入Mbox时明确指定UTF-8编码
    with open(fOut, "w", encoding="utf-8") as out:
        lineNum = 0

        # 自动检测输入文件编码，避免硬编码匹配错误
        with open(fIn, 'rb') as f:
            readsource = lambda: next(f)
            fInCodec = tokenize.detect_encoding(readsource)[0]
        
        # 用检测到的编码读取文件，保留特殊字符
        with open(fIn, 'rt', encoding=fInCodec, errors="replace") as f:
            for line in f:
                if line.startswith("From "):
                    if lineNum != 0:
                        out.write("\n")
                    lineNum +=1
                    line = line.replace(" at ", "@")
                out.write(line)
            
    return True

2. 读取Mbox时指定编码 #

读取生成的Mbox文件时，明确指定UTF-8编码，避免解析错误：

mbox = mailbox.mbox(f"Courriels_MBox/{k}.mbox", create=False, encoding="utf-8")

三、完整修改后的转换代码 #

import mailbox
import sqlite_utils
from sqlite3 import InterfaceError as SqlInterfaceError

for k in dates:
    db = sqlite_utils.Database("Courriels_Sqlite/Echanges_Discussion.db")
    mbox = mailbox.mbox(f"Courriels_MBox/{k}.mbox", create=False, encoding="utf-8")

    def to_insert():
        for message in mbox.values():
            Dionyversite = dict(message.items())
            # 统一转换非字符串字段为字符串
            for key, value in Dionyversite.items():
                if not isinstance(value, str):
                    Dionyversite[key] = str(value)
            # 处理多部分邮件内容
            payload = message.get_payload()
            if isinstance(payload, list):
                payload = "\n--- 邮件分割线 ---".join(str(part) for part in payload)
            Dionyversite["payload"] = payload
            yield Dionyversite

    try:
        db["Dionyversite"].upsert_all(to_insert(), alter=True, pk="Message-ID")
    except SqlInterfaceError as e:
        print(f"处理文件{k}.mbox时出错: {e}")

内容的提问来源于stack exchange，提问作者FrançoisR