How to fix SQLAlchemy (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Problem:

When trying to connect to your MySQL/MariaDB database using SQLAlchemy, you see the following error message:

Traceback (most recent call last):
  File "/home/uli/myproject.py", line 65, in make_sql_connection
    conn = engine.connect()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3276, in connect
    return self._connection_cls(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2440, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection
    return self.pool.connect()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
    with util.safe_reraise():
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py", line 620, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/pooling.py", line 322, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/connection_cext.py", line 151, in __init__
    self.connect(**kwargs)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/abstracts.py", line 1411, in connect
    self._post_connection()
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/abstracts.py", line 1351, in _post_connection
    self.set_charset_collation(charset=self._charset_id)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/abstracts.py", line 1326, in set_charset_collation
    self._execute_query(f"SET NAMES '{charset_name}' COLLATE '{collation_name}'")
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/connection_cext.py", line 713, in cmd_query
    raise get_mysql_exception(
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
(Background on this error at: https://sqlalche.me/e/20/4xp6)

Solution

You are using a connection URL such as:

engine = sqlalchemy.create_engine(f'mysql+mysqlconnector://localhost:3306/mydatabase?auth_plugin=mysql_native_password')

Instead, use the recommended mysql+pymysql scheme and **explicitly specify an encoding:

engine = sqlalchemy.create_engine(f'mysql+pymysql://localhost:3306/mydatabase?charset=utf8mb4')

Keep in mind that you need to install the pymysql package:

pip install pymysql