CODEWITHSUNDEEP
pythonmysqlpython-3.xsqlalchemysqlalchemy-utils
mda
mda

Reputation: 95

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 "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()

Upvotes: 1

Views: 4759

Answers (3)

Prox
Prox

Reputation: 894

Instead of mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> you'll have mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

The SQLAchemy docs suggests using mysqlclient (fork of MySQL-Python)¶ over MySQL-Connector¶.

The MySQL Connector/Python DBAPI has had many issues since its release, some of which may remain unresolved, and the mysqlconnector dialect is not tested as part of SQLAlchemy’s continuous integration. The recommended MySQL dialects are mysqlclient and PyMySQL.

Upvotes: 1

dm.zhurko
dm.zhurko

Reputation: 11

There does seem to be anything wrong with the MySQL connector. Just switch your mysql-connector-python to mysqlclient. I had the same problem and it helped me.

Upvotes: 1

mda
mda

Reputation: 95

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.

Upvotes: 1

Related Questions