How can I set "mysql_default_charset" and "mysql_collate" for all tables globally in SQLAlchemy and not on a per-table basis using "__table_args__"?

user_1020139 0
Viewed 717

I've been issues setting the "Character Set" and "Collation" for my tables in MySQL created using SQLAlchemy.

In particular, tables were always created using "latin1" for both the "Character Set" and "Collation".

Setting the "charset" in the URL, didn't solve the issue:

f'mysql+pymysql://{_user}:{_pwd}@{_host}:{_port}/{_db}?charset=utf8mb4',

Neither, did create_engine(...,encoding='utf8').

Only by setting the following __table_args__, was the table created with support for UTF-8:

__table_args__ = (
    {'mysql_default_charset': 'utf8',
     'mysql_collate': 'utf8_bin'}
)

How can I set the above table arguments for all tables in the database without having to specify them explicitly as __table_args__ for each of them?

Full example:

class Match(Base):
    __tablename__ = 'stash_match'

    id = Column(Integer, primary_key=True, autoincrement=True)
    blob_id = Column(String(40))
    text = Column(Text, nullable=False)
    regex = Column(Text, nullable=False)

    blob = relationship('Blob', back_populates='matches')

    __table_args__ = (
        ForeignKeyConstraint([blob_id], [Blob.id], onupdate='CASCADE', ondelete='CASCADE'),
        {'mysql_default_charset': 'utf8',
         'mysql_collate': 'utf8_bin'}
    )
sqlalchemy python-3.x mysql
1 Answers

As described in the documentation here you can augment the declarative Base class to customize the default behaviour:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

connection_uri = (
    "mysql+pymysql://root:toot@localhost:3307/mydb?charset=UTF8mb4"
)
engine = sa.create_engine(connection_uri, echo=True,)


class Base(object):
    __table_args__ = {
        "mysql_default_charset": "utf16",
        "mysql_collate": "utf16_icelandic_ci",
    }


Base = declarative_base(cls=Base)


class Team(Base):
    __tablename__ = "team"
    prov = sa.Column(sa.String(2), primary_key=True)
    city = sa.Column(sa.String(20), primary_key=True)
    team_name = sa.Column(sa.String(20))

    def __repr__(self):
        return f"<Team(prov='{self.prov}', city='{self.city}')>"


Base.metadata.create_all(engine)

The DDL that gets rendered is

CREATE TABLE team (
    prov VARCHAR(2) NOT NULL, 
    city VARCHAR(20) NOT NULL, 
    team_name VARCHAR(20), 
    PRIMARY KEY (prov, city)
)DEFAULT CHARSET=utf16 COLLATE utf16_icelandic_ci
user_21443900
Related