Configure the connection_collation with SQLAlchemy

Question

I'm attempting to get SQLAlchemy to connect to MySQL with a collation_connection of utf8mb4_unicode_ci.

My sever and database are both using the utf8mb4_unicode_ci collation. Without any configuration changes my collation_connection is utf8_general_ci. If I set the charset argument in the connection string to charset=utf8mb4 the collation_connection is then set to utf8mb4_general_ci. I have been unable to find any documentation stating how to set the collation_connection when using SQLAlchemy.

Answer 1

This is a variable specific to mysql and all you can do is to run a statement on each connection initialization:

In [18]: url = 'mysql+pymysql://test:test@localhost/test'

In [19]: connect_args = {'init_command':"SET @@collation_connection='utf8mb4_unicode_ci'"}

In [20]: with sqlalchemy.create_engine(url, connect_args=connect_args).connect() as con:
    ...:     print(con.execute('select @@collation_connection;').fetchall())
    ...:
[('utf8mb4_unicode_ci',)]