How convert a MySQL table to utf8 character set with alembic?

Question

My database is MySQL. I use SqlAlchemy ORM to define and access it. I use Alembic for migrations. I have a model with a field that used to contain just English text (Ascii/latin-1). Now, this field needs to contain Unicode text. In order to convert my model to support Unicode for MySQL I need to add the following class level attribute: mysql_character_set = 'utf8'

class MyModel(Base):
    __tablename__ = 'mymodel'
    mysql_character_set = 'utf8'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

So far so good. I want to add this attribute as part of an Alembic migration script. I normally use Alembic's excellent auto-generate command:

alembic revision --autogenerate

The problem is that this command doesn't capture every model change and in particular not the addition of the mysql_character_set attribute.

How do I add this attribute manually to the alembic migration script?

Answer 1

I did it like this:

from alembic import op
import sqlalchemy as sa


def upgrade():
    conn = op.get_bind()
    conn.execute(sa.sql.text('ALTER table my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci'))

Answer 2

You should use the utf8mb4 character set, as utf8 (aka utf8mb3) is broken.

To change the default character set for a table and convert all character columns (CHAR, VARCHAR, TEXT) to the new character set, you can use ALTER TABLE in a migration (but see the docs for possible side effects):

from alembic import op


def upgrade():
   op.execute(
       'ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
   )

def downgrade():
    op.execute(
        'ALTER TABLE mytable CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci'
    )

Answer 3

Just specify these parameters in your MyModel class. You'll need to create alembic migrations as well to incorporate those changes into DB.

mysql_charset='utf8mb4', mysql_collate='utf8mb4_bin'