pandas to_sql 为具有 JSON 列的表提供 UnicodeEncodeError

Question

前言：我知道有关此主题的类似问题，并尝试了他们的解决方案，但无济于事。我正在创建一个 python 脚本来从 ftp 地址获取 JSON 文件，将其转换为 Pandas 数据帧，然后将其发送到 MySQL 以填充表。但是，我遇到了 Pandas.to_sql 的问题。错误是：UnicodeEncodeError: 'ascii' codec can't encode character '\xd1' in position 87: ordinal not in range(128)这是我的代码：import pandas as pdimport jsonfrom sqlalchemy import create_enginecnx = create_engine('mysql://username:password@192.168.1.11/database?charset=utf8',encoding='utf-8')sl1 = pd.read_json('ftp://username:[e-mail]/directory/sailings_cel.txt')sl1 = pd.json_normalize(sl1["Dataset"])sl1.to_sql(name='celsailingtemp',con=cnx,if_exists='append',index=True)可能有用的信息：我已经尝试了多种排列，以确保在 create_engine 语句中将编码设置为 utf-8，按照 StackOverflow 上的类似问题。JSON 文件在 FTP 服务器上提供，扩展名为 .txt，但我认为应该没问题，因为它由 read_json 和 json_normalize 处理得很好。根据https://www.utf8-chartable.de/unicode-utf8-table.pl?unicodeinhtml=hex，看起来 xd1 可能是 Ñ 字符。果然，在行程栏里就能找到违规角色。说到列，JSON 数据结构如下所示。将 json_normalize 级别设置为“数据集”会将嵌套的“行程”列降级为长字符串，这对我来说没问题。我已在 mysql 端将该列的类型设置为“JSON”。MySQL 表结构如下所示：我可能可以简单地删除行程列，但核心编码问题不会得到解决，我想正确执行此操作。任何有关此问题的指导将不胜感激！

Answer 1

json_normalize level ...将嵌套的“行程”列降级为长字符串

事实上不，事实并非如此。该列包含一个列表（或者可能是一个 Series 对象），这使 DBAPI 驱动程序感到困惑。为了让它工作，我必须这样做：

sl1 = pd.read_json(r"C:\Users\Gord\Desktop\sailing.json")

sl1 = pd.json_normalize(sl1["Dataset"])

sl1["MarketName"] = sl1["MarketName"].map(lambda x: json.dumps(x))

sl1["MarketNameList"] = sl1["MarketNameList"].map(lambda x: json.dumps(x))

sl1["Itinerary"] = sl1["Itinerary"].map(lambda x: json.dumps(x))

sl1.to_sql(name="celsailingtemp", con=cnx, if_exists="append", index=True)

Answer 2

我认为你的ftp传输是通过ascii进行的。将 ftp 设置为使用二进制模式。

sl1 = pd.read_json('ftp://username:[e-mail]/directory/sailings_cel.txt;type=I')

我看到你的回溯，似乎是python编码问题，而不是MySQL。设置使用utf-8。

1. python编码 在执行之前设置python编码。

export PYTHONIOENCODING=utf-8

1. OS编码确认utf-8编码类型，执行locale -a命令。

# locale -a
C
C.UTF-8
POSIX

如果有C.UTF-8，请设置C.UTF-8。

export LC_CTYPE="C.UTF-8"

Answer 3

看起来您可以更改数据库中列的字符集，这应该使您的代码运行良好。

顺便说一句，处理此类问题的另一种方法是将数据的子集重新创建为小提琴并在那里使用它。