Notes on using Alembic

Alembic is a migration tool for sqlalchemy, a python orm. There aren't many articles in Japanese, and I often didn't realize that I was using it, so I'll write down the point.

Alembic:http://alembic.zzzcomputing.com/en/latest/

I want to handle multiple model files

When there are multiple model files, it is necessary to combine the metadata of the Base class. http://liuhongjiang.github.io/hexotech/2015/10/14/alembic-support-multiple-model-files/ The method described in this blog works. In my case, I tried using dynamic import.

env.py


import importlib
from sqlalchemy.schema import MetaData

target_models =[
    'path.to.models',
    'another.models'
]

def import_model_bases():
    """import all target models base metadatas."""
    lst = list(map(
        lambda x: importlib.import_module(x).Base.metadata,
        target_models
    ))
    return lst


def combine_metadata(lst):
    m = MetaData()
    for metadata in lst:
        for t in metadata.tables.values():
            t.tometadata(m)
    return m

target_metadata = combine_metadata(import_model_bases())

Does not detect type change

If you keep the same column name, it will not detect the type change. It seems that it is set so by default. http://stackoverflow.com/questions/17174636/can-alembic-autogenerate-column-alterations This is also modified as in this article.

env.py


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix='sqlalchemy.',
        poolclass=pool.NullPool)

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True #add to
        )

        with context.begin_transaction():
            context.run_migrations()

Judge Boolean and Tinyint as different types

Now that you're aware of the type change, it's a problem when dealing with Boolean types. Boolean is set by tinyint in mysql, but when comparing, it is judged that the type is different and it tries to delete and create columns every time. Also, changing the type from tinyint to integer does not detect it well.

If you fix it with the feeling that it is written with reference to here, it will work. I tried as follows.

env.py


#add to
from sqlalchemy import engine_from_config, types
from sqlalchemy.dialects import mysql

def my_compare_type(context, inspected_column,
                    metadata_column, inspected_type, metadata_type):
    """my compser type for mysql."""
    if isinstance(inspected_type, mysql.TINYINT) and\
       isinstance(metadata_type, types.Boolean):
        return False
    if isinstance(inspected_type, mysql.TINYINT) and\
       isinstance(metadata_type, types.Integer):
        return True
    return None

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix='sqlalchemy.',
        poolclass=pool.NullPool)

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=my_compare_type #Change
        )

        with context.begin_transaction():
            context.run_migrations()

Since alembic has many functions and many functions that I haven't used yet, I will add what I was addicted to later.

Recommended Posts

Notes on using Alembic
[Django] Notes on using django-debug-toolbar
Notes on using MeCab from Python
Notes on using post-receive and post-merge
Notes on installing Python using PyEnv
Notes on using rstrip with python.
Notes on using matplotlib on the server
(Beginner) Notes on using pyenv on Mac
Notes on Flask
Notes for using OpenCV on Windows10 Python 3.8.3.
Notes on implementing APNs tests using Pytest
Notes on using OpenCL on Linux on the RX6800
Notes on using code formatter in Python
Notes on neural networks
Celery notes on Django
Notes on using dict in python [Competition Pro]
Notes on installing PycURL
Notes for using TensorFlow on Bash on Ubuntu on Windows
Notes on SciPy.linalg functions
[Python] Notes on accelerating genetic algorithms using multiprocessing
Minimum notes when using Python on Mac (Homebrew edition)
Notes on tf.function and Tracing
Notes on installing dlib on mac
Notes on python's sqlite3 module
Notes on * args and ** kargs
Notes on defining PySide slots (2)
Try using OpenCV on Windows
Notes on pyenv and Atom
Notes on defining PySide slots
[Python] Notes on data analysis
Notes on installing Python on Mac
Notes on studying multidimensional scaling
Process on GPU using chainer.cuda.elementwise
Notes on installing pipenv on Mac
Broadcast on LINE using python
Notes on installing Anaconda 3 on Windows
Notes on imshow () in OpenCV
Notes on installing Python on CentOS
Notes on Python and dictionary types
Notes on package management with conda
Try using Pillow on iPython (Part 1)
Introducing Python using pyenv on Ubuntu 20.04
Preparing python using vscode on ubuntu
[Golang] Notes on frequently used functions
Notes on how to use pywinauto
Notes on how to use featuretools
Try using ArUco on Raspberry Pi
Study on Tokyo Rent Using Python (3-2)
Try using Pillow on iPython (Part 3)
Using a serial console on Ubuntu 20.04
Notes on accessing dashDB from python
Install Python on CentOS using Pyenv
Study on Tokyo Rent Using Python (3-3)
Notes on how to use doctest
Notes on how to write requirements.txt
Notes on installing Ubuntu 18.04 on the XPS 15 7590
Run Yocto on Ubuntu using QEMU.
Install Python on CentOS using pyenv
Notes on setting pyenv and python environment using Homebrew on Mac OS Marvericks
Notes using cChardet and python3-chardet in Python 3.3.1.
Install opencv on Mac using Anaconda Navigator