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:
self._engine_str = f"mysql+mysqlconnector://{self._user}:{self._password}@{self._host}/"
engine = sa.create_engine(f"{self._engine_str}{db_name}")
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'
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)