mysql example alembic change enum (snippet)

Python mysql example 'alembic change enum'

Functions in program:

def downgrade():
def upgrade():

Modules used in program:

import sqlalchemy as sa

python alembic change enum

Python mysql example: alembic change enum

# The situation: You have an existing mysql database (with data!) with the enum field person.sex with options 'male', 'female', 'unassigned'
# and you need to change 'unassigned' to 'unspecified'. The database is using Flask-Migrate and thus Alembic, but Flask-Migrate
# is unable to auto-generate this migration file for you. Instead, generate and edit a blank migration file via
#     $ flask db revision 
#     $ flask db edit

# The following is the resulting migration file:


"""
Change person.sex enum from male/female/unassigned to male/female/unspecified

Revision ID: c1a5258f1135
Revises: 3fa5f4586bcd
Create Date: 2018-06-02 13:37:46.655289

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql  # this import is not auto-generated by Flask-Migrate
from sqlalchemy.sql import text  # this import is not auto-generated by Flask-Migrate

# revision identifiers, used by Alembic.
revision = 'c1a5258f1135'
down_revision = '3fa5f4586bcd'
branch_labels = None
depends_on = None


def upgrade():
    op.alter_column('person', 'sex',
               existing_type=mysql.ENUM('male', 'female', 'unassigned'), existing_server_default='unassigned',
               nullable=False, type_=mysql.ENUM('male', 'female', 'unassigned', 'unspecified'), server_default='unspecified')

    conn = op.get_bind()
    conn.execute(text("UPDATE person SET sex = 'unspecified' WHERE sex = 'unassigned'"))

    op.alter_column('person', 'sex',
               existing_type=mysql.ENUM('male', 'female', 'unassigned', 'unspecified'), existing_server_default='unspecified',
               nullable=False, type_=mysql.ENUM('male', 'female', 'unspecified'), server_default='unspecified')

def downgrade():
    op.alter_column('person', 'sex',
               existing_type=mysql.ENUM('male', 'female', 'unspecified'), existing_server_default='unspecified',
               nullable=False, type_=mysql.ENUM('male', 'female', 'unassigned', 'unspecified'), server_default='unassigned')

    conn = op.get_bind()
    conn.execute(text("UPDATE person SET sex = 'unassigned' WHERE sex = 'unspecified'"))

    op.alter_column('person', 'sex',
               existing_type=mysql.ENUM('male', 'female', 'unassigned', 'unspecified'), existing_server_default='unassigned',
               nullable=False, type_=mysql.ENUM('male', 'female', 'unassigned'), server_default='unassigned')

Python links

Learn Python: https://pythonbasics.org/
Python Tutorial: https://pythonprogramminglanguage.com