I read a lot of similar questions but none of the clearly answer my issue.
I'm using sqlalchemy-utils EncryptedType on a mysql table column.
The table creation and the insert is ok, but when I'm trying to do a query a receive:
Traceback (most recent call last):
File "workspace/bin/test.py", line 127, in <module>
result = session.query(Tester).all()
File "workspace\ERP\venv\lib\site-packages\sqlalchemy\orm\query.py", line 3244, in all
return list(self)
File "workspace\venv\lib\site-packages\sqlalchemy\orm\loading.py", line 101, in instances
cursor.close()
File "workspace\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "workspace\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
raise exception
File "workspace\venv\lib\site-packages\sqlalchemy\orm\loading.py", line 81, in instances
rows = [proc(row) for row in fetch]
File "workspace\venv\lib\site-packages\sqlalchemy\orm\loading.py", line 81, in <listcomp>
rows = [proc(row) for row in fetch]
File "workspace\venv\lib\site-packages\sqlalchemy\orm\loading.py", line 642, in _instance
populators,
File "workspace\venv\lib\site-packages\sqlalchemy\orm\loading.py", line 779, in _populate_partial
dict_[key] = getter(row)
File "workspace\venv\lib\site-packages\sqlalchemy\engine\result.py", line 107, in __getitem__
return processor(self._row[index])
File "workspace\venv\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 944, in process
value = bytes(value)
TypeError: string argument without an encoding
I found out that this error occurs only using python 3, not using python 2.
And also that the problem is with the sqlalchemy bynary type, because I get the same error with Binary, Varbinary, and Blob columns.
Since bytes in python3 needs an encoding for strings, I changed the code of sqlalchemy\sql\sqltypes.py on line 944 to value = bytes(value, 'utf-8) and al works well, so my question is:
why I need to change the sqlalchemy code? Is sqlalchemy fully usable with python3? Is it safe to change the code of a package?
Here is a code sample to try:
from sqlalchemy import MetaData, Integer, Column, Table, Binary, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
DB_CONFIG = {
'user': 'user_test',
'password': 'PSW_test',
'host': '127.0.0.1',
'database': 'db_test',
}
if __name__ == '__main__':
Base = declarative_base()
engine = create_engine("mysql+mysqlconnector://%(user)s:%(password)s@%(host)s/%(database)s" % DB_CONFIG,
echo=False)
Base.metadata.bind = engine
db_sessionmaker = sessionmaker(bind=engine)
Session = scoped_session(db_sessionmaker)
# create the table
meta = MetaData()
tests = Table(
'test', meta,
Column('id', Integer, primary_key=True),
Column('attr', Binary)
)
meta.create_all(engine)
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
attr = Column(Binary)
new_test = Test(attr='try'.encode('utf-8'))
session = Session()
session.add(new_test)
session.commit()
result = session.query(Test).all()
for a in result:
print(a, a.id, a.attr)
Session.remove()
mysql.connectormethodconnect(**DB_CONFIG), I can get the data correctly. So maybe I'm missing some SQLA configuration.bytesorstrfor the column in question?mysql.connectoruses type information provided by the DB only.str. I also noticed that if in the insert statement I put some data like'try'.encode('utf-8'), the query with mysql.connector works fine, returning a string, but if I put data likebase64.b64decode('data'.encode('utf-8'))the following error occursUnicodeDecodeError: 'utf-8' codec can't decode byte 0xab in position 1: invalid start byte.