Why BINARY usage in SQLAlchemy with Python3 cause a TypeError: ‘string argument without an encoding’

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 "workspaceERPvenvlibsite-packagessqlalchemyormquery.py", line 3244, in all
    return list(self)
  File "workspacevenvlibsite-packagessqlalchemyormloading.py", line 101, in instances
    cursor.close()
  File "workspacevenvlibsite-packagessqlalchemyutillanghelpers.py", line 69, in __exit__
    exc_value, with_traceback=exc_tb,
  File "workspacevenvlibsite-packagessqlalchemyutilcompat.py", line 178, in raise_
    raise exception
  File "workspacevenvlibsite-packagessqlalchemyormloading.py", line 81, in instances
    rows = [proc(row) for row in fetch]
  File "workspacevenvlibsite-packagessqlalchemyormloading.py", line 81, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "workspacevenvlibsite-packagessqlalchemyormloading.py", line 642, in _instance
    populators,
  File "workspacevenvlibsite-packagessqlalchemyormloading.py", line 779, in _populate_partial
    dict_[key] = getter(row)
  File "workspacevenvlibsite-packagessqlalchemyengineresult.py", line 107, in __getitem__
    return processor(self._row[index])
  File "workspacevenvlibsite-packagessqlalchemysqlsqltypes.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 sqlalchemysqlsqltypes.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()

Answer

Thanks to the hint provided by Ilja Everilä, I was able to find a solution. Maybe not the best solution, but now is working.

I think that the root cause is that my DB-API automatically converts bytes to str during the query. So I just disabled this behavior by adding a parameter to the create_engine:

engine = create_engine("mysql+mysqlconnector://%(user)s:%(password)s@%(host)s/%(database)s" % DB_CONFIG, connect_args={'use_unicode': False})

The consequence is that if you have a String column it will be returned in queries as bytes not as ‘str’, and you have to manually decode it.

Surely there is a better solution.

Advertisement

Answer