What is the default character set in SQLAlchemy for MySQL?

Question

I want to move a database from utf8mb3 to utf8mb4. In my code I create an engine:

url = make_url(database_url)
if 'charset' not in url.query:
    url = url.update_query_pairs([("charset", "utf8")])
create_engine(str(url))

I have database with character set utf8mb4 and I have these entries in my.cnf:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8_general_ci
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

So the question is: When I create my SQLAlchemy engine, what will the character set be? Will it be utf8mb4?

Answer 1

what will the character set be? Will it be utf8mb4?

No, because you specify the equivalent of charset=utf8 in the connection URL and MySQL interprets that as utf8mb3

import sqlalchemy as sa

base_url = sa.URL.create(
    "mysql+mysqldb",
    username="scott",
    password="tiger",
    host="localhost",
    port=3307,
    database="test",
)

url = base_url.update_query_pairs([("charset", "utf8")])
engine = sa.create_engine(url)
cnxn = engine.raw_connection().driver_connection
print(cnxn.character_set_name())  # utf8mb3

Instead, use utf8mb4 specifically:

url = base_url.update_query_pairs([("charset", "utf8mb4")])
engine = sa.create_engine(url)
cnxn = engine.raw_connection().driver_connection
print(cnxn.character_set_name())  # utf8mb4