Python3迁移后SQLAlchemy调用.all()遇UnicodeDecodeError问题

将代码从Python2迁移至Python3，基于ubuntu:20.04的Docker镜像运行。调用SQLAlchemy的sqlalchemy.orm.Query.all()时，Python2环境下正常运行，但Python3中抛出错误：

UnicodeDecodeError: 'ascii' codec can't decode byte 0xe7 in position 1: ordinal not in range(128)

已完成的配置检查：

• Dockerfile中设置了locale，调用SQLAlchemy前执行locale.getlocale()返回('en_US', 'UTF-8')
• SQLAlchemy连接URL指定了utf8编码：sqlalchemy.url='mysql+pymysql:{server}&charset=utf8&binary_prefix=true

排查后定位问题出在使用PickleType的blob列：

from sqlalchemy import PickleType

class Schedule(OrgRefMixin, DeclarativeBase):
    __tablename__ = 'flights'
    ...
    routing = Column(PickleType())
    ...

完整报错栈：

Traceback (most recent call last):
  File "/opt/venv/lib/python3.8/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/opt/venv/lib/python3.8/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/opt/venv/lib/python3.8/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/opt/venv/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/opt/venv/lib/python3.8/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/venv/lib/python3.8/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/code/flights/flights/json.py", line 182, in view_wrapper
    return view_func(*args, **kwargs)
  File "/code/flights/flights/blueprints/flights.py", line 62, in get_flights
    flights = Flights.get(filters, **valid_args)
  File "/code/flights/flights/resources/flights.py", line 254, in get
    response = query.all()
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2643, in all
    return list(self)
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 90, in instances
    util.raise_from_cause(err)
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 77, in instances
    rows = [keyed_tuple([proc(row) for proc in process])
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 77, in <listcomp>
    rows = [keyed_tuple([proc(row) for proc in process])
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 77, in <listcomp>
    rows = [keyed_tuple([proc(row) for proc in process])
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 93, in __getitem__
    return processor(self._row[index])
  File "/opt/venv/lib/python3.8/site-packages/sqlalchemy/sql/sqltypes.py", line 1478, in process
    return loads(value)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe7 in position 1: ordinal not in range(128)

问题根源：Python2与Python3的pickle序列化/反序列化存在编码差异——Python2的pickle会将字符串序列化为字节，而Python3默认用ascii编码反序列化这些字节，导致非ASCII字符解码失败。

方法一：指定encoding='latin1'（推荐，兼容遗留数据） #

latin1编码可以完美映射所有字节值，无需修改已有数据，就能直接在Python3中加载Python2序列化的内容：

from sqlalchemy import PickleType

class Schedule(OrgRefMixin, DeclarativeBase):
    __tablename__ = 'flights'
    ...
    routing = Column(PickleType(encoding='latin1'))
    ...

方法二：指定protocol=2并使用bytes模式（适合新数据） #

如果后续无需再兼容Python2，可以重新序列化数据时指定protocol=2，并设置反序列化编码为bytes，但这种方式需要先处理数据库中已有的遗留数据：

# 仅适用于新数据或已完成迁移的场景
routing = Column(PickleType(protocol=2, encoding='bytes'))

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