Python 3 SQLAlchemey UnicodeDecodeError: 'ascii' codec can't decode byte

Question

I have some code which I'm finally getting around to updating to Python 3.

It runs in Docker using ubuntu:20.04 as the base image.

I am using SQLAlchemy query .all() method method sqlalchemy.orm.Query.all()

The code works with Python 2 however, it fails on Python 3 with the following error:

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

I am struggling to understand why the code is trying to decode to ascii.

I've set the locale in the Dockerfile and running locale.getlocale() on the line above the SQLAlchemy line prints ('en_US', 'UTF-8')

The SQLAlchemy connection url specifies utf8:

sqlalchemy.url='mysql+pymysql:{server}&charset=utf8&binary_prefix=true

I've read all the other similar questions but still cannot get this working.

Update:

I've tracked it down to one column:

from sqlalchemy import PickleType


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

This is a blob column in the database.

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)

A complete stack trace might be helpful. Also, what versions of SQLAlchemy and PyMySQL are you using? — Gord Thompson
– Gord Thompson, Commented Mar 30, 2023 at 13:24
Thanks, I've added full stack trace. I'm using sqlalchemy==1.1.3 and pymysql==0.9.2 — user7692855
– user7692855, Commented Mar 30, 2023 at 13:29
Wow, those versions are really old. Are you hitting a similarly old version of MySQL server? — Gord Thompson
– Gord Thompson, Commented Mar 30, 2023 at 13:58
You might be encountering an issue (hinted at here) where objects pickled by Python_2 are not 100% compatible with un-pickling in Python_3. You could try retrieving the raw bytes with something like bin_data = engine.execute("SELECT routing FROM flights WHERE id = 1").scalar() and then see if you can un-pickle it manually with thing = pickle.loads(bin_data). — Gord Thompson
– Gord Thompson, Commented Apr 3, 2023 at 19:39

Phoenix · Accepted Answer · 2023-04-09 14:48:54Z

0

when Python tries to decode a byte string to Unicode using the ASCII codec, but the byte the input string contains non-ASCII characters such a error happens. it seems that one of the rows being loaded contains non-ASCII characters. My suspect is explicitly set the encoding used by PickleType to 'bytes'. something like this:

routing = Column(PickleType(pickler=lambda v: pickle.dumps(v, protocol=pickle.HIGHEST_PROTOCOL), 
                                    unpickler=lambda v: pickle.loads(v), 
                                    encoding='bytes'))

answered Apr 9, 2023 at 14:48

Phoenix

1,0488 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python 3 SQLAlchemey UnicodeDecodeError: 'ascii' codec can't decode byte

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related