Multiple UUID casting in an SQL Alchemy select statement

Question

If I try to retrieve a value from a column which typed as UUID I get a Python error: TypeError("a bytes-like object is required, not 'str'") when I call fetchall() on the CursorObject.

Code that raises the error:

import sqlalchemy as sa

def getScheduleData(engine: sa.engine, scheduleID: str) -> dict:
    rows = []
    metadata = sa.MetaData()
    table = sa.Table('schedules', metadata, autoload_with=engine)

    stmt = sa.select(table.c['userID']).where(table.c["ID"] == scheduleID)
    print(stmt)

    try:
        with engine.connect() as conn:
            result = conn.execute(stmt)
            rows = result.fetchall()
            print(rows)
    except Exception as e:
        print(e)

The greater issue is that i want to retrieve all the column values (so a whole row) from the DB because there are several columns which are typed as UUID in the schedules table:

import sqlalchemy as sa

def getScheduleData(engine: sa.engine, scheduleID: str) -> dict:
    rows = []
    metadata = sa.MetaData()
    table = sa.Table('schedules', metadata, autoload_with=engine)

    //all column values
    stmt = sa.select(table).where(table.c["ID"] == scheduleID)
    print(stmt)

    try:
        with engine.connect() as conn:
            result = conn.execute(stmt)
            rows = result.fetchall()
            print(rows)
    except Exception as e:
        print(e)

I found a solution if I only want to retrieve specific values, in this way there is no TypeError:

import sqlalchemy as sa

def getScheduleData(engine: sa.engine, scheduleID: str) -> dict:
    rows = []
    metadata = sa.MetaData()
    table = sa.Table('schedules', metadata, autoload_with=engine)

    //cast the value to string in the statement
    stmt = sa.select(sa.cast(table.c['userID'], sa.String(36))).where(table.c["ID"] == scheduleID)
    print(stmt)

    try:
        with engine.connect() as conn:
            result = conn.execute(stmt)
            rows = result.fetchall()
            print(rows)
    except Exception as e:
        print(e)

But how can I achieve this taking the second scenario into consideration where I want all the rows and there can be several UUID typed values?

More info:

• driver: 'mysqlconnector'
• RDBMS: MariaDB
• engine creation:

self._engine_str = f"mysql+mysqlconnector://{self._user}:{self._password}@{self._host}/"
engine = sa.create_engine(f"{self._engine_str}{db_name}")

• error traceback:

Traceback (most recent call last):
  File "C:\projects\myWebApp\frontend\fastapi\controllers\schedule.py", line 598, in getScheduleData
    rows = result.fetchall()
           ^^^^^^^^^^^^^^^^^
  File "c:\Users\Luke\.conda\envs\mywebapp-server\Lib\site-packages\sqlalchemy\engine\result.py", line 1317, in fetchall
    return self._allrows()
           ^^^^^^^^^^^^^^^
  File "c:\Users\Luke\.conda\envs\mywebapp-server\Lib\site-packages\sqlalchemy\engine\result.py", line 551, in _allrows
    made_rows = [make_row(row) for row in rows]
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Users\Luke\.conda\envs\mywebapp-server\Lib\site-packages\sqlalchemy\engine\result.py", line 551, in <listcomp>
    made_rows = [make_row(row) for row in rows]
                 ^^^^^^^^^^^^^
  File "lib\\sqlalchemy\\cyextension\\resultproxy.pyx", line 22, in sqlalchemy.cyextension.resultproxy.BaseRow.__init__
  File "lib\\sqlalchemy\\cyextension\\resultproxy.pyx", line 79, in sqlalchemy.cyextension.resultproxy._apply_processors
  File "c:\Users\Luke\.conda\envs\mywebapp-server\Lib\site-packages\sqlalchemy\sql\sqltypes.py", line 3615, in process
    value = _python_UUID(value)
            ^^^^^^^^^^^^^^^^^^^
  File "c:\Users\Luke\.conda\envs\mywebapp-server\Lib\uuid.py", line 175, in __init__
    hex = hex.replace('urn:', '').replace('uuid:', '')
          ^^^^^^^^^^^^^^^^^^^^^^^
TypeError: a bytes-like object is required, not 'str'

Answer 1

The error is reproducible on MariaDb 10.7 using mysql-connector-python versions 8.0.5 and 8.0.6 but not with later versions.

The solution is to upgrade to a morerecent version of mysql-connector-python (the current version is 8.4.0)