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

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.

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.

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.