Support emoji or utf8mb4 when using mysql and python SQLAlchemy¶

To support emoji in your table data, you should set encoding of mysql to utf8mb4.

Multiple aspects are related.

Mysql cli¶

When use mysql cli, tell it the default character-set:

mysql -h server -u user1  --default-character-set=utf8mb4

SQLAlchemy connection string¶

In connection url, append charset=utf8mb4. For example:

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://user:password@localhost:3306/db?charset=utf8mb4")

SQLAlchemy model¶

If you create tables automatically based on SQLAlchemy models using db.create_all(), specify mysql_charset in models.

To avoid specifying mysql_charset in every model, you can specify in a base model and let normal models inherit from the base model.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# specify mysql_charset in base model
class BaseModel(Base):
    __abstract__ = True
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }

# inherit from base model
class User(BaseModel):
    # column definitions omitted
    pass

SQL of creating table¶

Specify CHARSET when create tables using SQL. For example:

CREATE TABLE IF NOT EXISTS `user`(
  `uid` bigint(64) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uid`)
) ENGINE = InnoDB CHARSET = utf8mb4;