I tried running alembic, a Python migration tool

Target

Try running the python migration package alembic · PyPI

What is alembic? ??

A migration tool that manages your DB when using SQLAlchemy in Python

Environmental preparation

Build an environment with Docker

I want to build it easily, so I will do it with Docker

Folder structure


.
├── README.md
├── docker-compose.yml
└── src
    └── model.py

docker-compose.yml


version: "3"

services:
  db:
    image: postgres:11.7
    container_name: alembic-db
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=almebic
  app:
    image: python:3.8.2-slim
    container_name: alembic-app
    volumes:
      - ./src:/usr/src
    environment:
      - PYTHONPATH=${PYTHONPATH}:/usr/src
    tty: true

Install alembic

Install the required packages with pip

pip install alembic psycopg2-binary

If you install alembic, ** SQLAlchemy ** will be installed at the same time. ** psycopg2-binary · PyPI ** is used to connect to postgres

alembic installation


root@9a7582105665:/usr/src# pip install alembic psycopg2-binary
Collecting alembic
  Downloading alembic-1.4.2.tar.gz (1.1 MB)
     |████████████████████████████████| 1.1 MB 7.8 MB/s
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
    Preparing wheel metadata ... done
Collecting psycopg2-binary
  Downloading psycopg2_binary-2.8.5-cp38-cp38-manylinux1_x86_64.whl (3.0 MB)
     |████████████████████████████████| 3.0 MB 32.3 MB/s
Collecting python-dateutil
  Downloading python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)
     |████████████████████████████████| 227 kB 23.8 MB/s
Collecting Mako
  Downloading Mako-1.1.2-py2.py3-none-any.whl (75 kB)
     |████████████████████████████████| 75 kB 11.2 MB/s
Collecting SQLAlchemy>=1.1.0
  Downloading SQLAlchemy-1.3.16-cp38-cp38-manylinux2010_x86_64.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 54.3 MB/s
Collecting python-editor>=0.3
  Downloading python_editor-1.0.4-py3-none-any.whl (4.9 kB)
Collecting six>=1.5
  Downloading six-1.14.0-py2.py3-none-any.whl (10 kB)
Collecting MarkupSafe>=0.9.2
  Downloading MarkupSafe-1.1.1-cp38-cp38-manylinux1_x86_64.whl (32 kB)
Building wheels for collected packages: alembic
  Building wheel for alembic (PEP 517) ... done
  Created wheel for alembic: filename=alembic-1.4.2-py2.py3-none-any.whl size=159543 sha256=dc29f47f6c24908d9413da7e3c969c64c252d0cbf9f90fca7cfbb5782b2452d0
  Stored in directory: /root/.cache/pip/wheels/70/08/70/cea787a7e95817b831469fa42af076046e55a05f7c94657463
Successfully built alembic
Installing collected packages: six, python-dateutil, MarkupSafe, Mako, SQLAlchemy, python-editor, alembic, psycopg2-binary
Successfully installed Mako-1.1.2 MarkupSafe-1.1.1 SQLAlchemy-1.3.16 alembic-1.4.2 psycopg2-binary-2.8.5 python-dateutil-2.8.1 python-editor-1.0.4 six-1.14.0

Check the version of the package


root@ecce2b20848e:/usr/src# pip list
Package         Version
--------------- -------
alembic         1.4.2
Mako            1.1.2
MarkupSafe      1.1.1
pip             20.1
psycopg2-binary 2.8.5
python-dateutil 2.8.1
python-editor   1.0.4
setuptools      46.1.3
six             1.14.0
SQLAlchemy      1.3.16
wheel           0.34.2

Create an alembic environment

Creating a migration environment with alembic init

alembic init {environment name of migration}

Create a migration environment with

alembic initial settings


root@ecce2b20848e:/usr/src# alembic init migration
  Creating directory /usr/src/migration ...  done
  Creating directory /usr/src/migration/versions ...  done
  Generating /usr/src/migration/README ...  done
  Generating /usr/src/alembic.ini ...  done
  Generating /usr/src/migration/env.py ...  done
  Generating /usr/src/migration/script.py.mako ...  done
  Please edit configuration/connection/logging settings in '/usr/src/alembic.ini' before proceeding.

When the creation is completed, it will have the following structure You can see that the ** migration directory ** and ** alembic.ini file ** are created

tree


.
├── README.md
├── docker-compose.yml
└── src
    ├── alembic.ini
    ├── migration
    │   ├── README
    │   ├── env.py
    │   ├── script.py.mako
    │   └── versions
    └── model.py

About files generated by alembic init

-** versions directory ** Directory where migration scripts are stored

Run migration

Editing alembic.ini

Edit the alembic.ini file to connect to the DB Rewrite the following part of the ini file with DB connection information

alembic.ini (before editing)


sqlalchemy.url = driver://user:pass@localhost/dbname

alembic.ini (after editing)


sqlalchemy.url = postgresql://postgres:postgres@alembic-db:5432/almebic

Use the connection information described in ** docker-compose.yml **

Creating a migration file

Create a migration file with the ** revision ** command

alembic revision -m {file name}

Creating a migration file


root@ecce2b20848e:/usr/src# alembic revision -m "create account table"
  Generating /usr/src/migration/versions/b5f586d58141_create_account_table.py ...  done

After execution, a migration file will be created under the versions directory.

b5f586d58141_create_account_table.py


"""create account table

Revision ID: b5f586d58141
Revises:
Create Date: 2020-05-02 17:49:20.493493

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b5f586d58141'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

Edit migration file

Edit the generated migration file Here we copy the official and create an account table

b5f586d58141_create_account_table.py


"""create account table

Revision ID: b5f586d58141
Revises:
Create Date: 2020-05-02 17:49:20.493493

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b5f586d58141'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('description', sa.Unicode(200)),
    )

def downgrade():
    op.drop_table('account')

Run migration

Run migration with the ** upgrade ** command

alembic upgrade head

** head ** will perform migration to the latest version If you want to raise only one version, use ** + 1 ** instead of head If you want to lower the version, use the ** downgrade ** command If you want to return to the initial state

alembic downgrade base

To run Use ** -1 ** instead of base to revert to the previous version

Run migration


root@ecce2b20848e:/usr/src# alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> b5f586d58141, create account table

Automatically generate migration files

By editing env.py as described in Auto Generating Migrations — Alembic 1.4.2 documentation You will be able to automatically create a migration file from the ** SQLAlchemy ** model information defined in Python. First, define the model with SQLAlchemy

SQLAlchemy model definition

In the model definition, define the account table added earlier in the SQLAlchemy model. Add columns for ** created_at ** and ** updated_at ** I want to pass the SQLAlchemy Engine to env.py, so define it

model.py


from datetime import datetime

from sqlalchemy import create_engine, Column, String, Integer, Unicode, DateTime
from sqlalchemy.ext.declarative import declarative_base

#Creating an Engine
Engine = create_engine(
    "postgresql://postgres:postgres@alembic-db:5432/almebic",
    encoding="utf-8",
    echo=False
)

'''
Create Base for model
If you define a model based on this Base, the model information will be stored in metadata.
'''
ModelBase = declarative_base()


class AcountModel(ModelBase):
    """
    AcountModel
    """
    __tablename__ = 'account'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    description = Column(Unicode(200))
    created_at = Column(DateTime, default=datetime.now, nullable=False)
    updated_at = Column(DateTime, default=datetime.now, nullable=False)

Editing env.py

Edit env.py so that you can get information about the model defined in model.py Import the ** ModelBase ** and ** Engine ** defined earlier at the beginning Substitute ** ModelBase.metadata ** for ** target_metadata ** Also, when executing migration, edit ** run_migrations_online () ** so that migration will be executed.

env.py


from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
from model import ModelBase, Engine

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = ModelBase.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

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


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.

    """
    url = config.get_main_option("sqlalchemy.url")
    connectable = Engine

    with connectable.connect() as connection:
        context.configure(
            url=url,
            connection=connection,
            target_metadata=target_metadata
        )

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


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Performing migration using autogenerate

The revision command with the ** --autogenerate ** option creates a migration file from the model information defined in SQLAlchemy.

revision command (--With autogenerate)


root@9a7582105665:/usr/src# alembic revision --autogenerate -m "Added columns."
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 'account_id_seq' as owned by integer column 'account(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected added column 'account.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'account.updated_at'
  Generating /usr/src/migration/versions/dcd0d354f648_added_columns.py ...  done

The following files are created after execution

dcd0d354f648_added_columns.py


"""Added columns.

Revision ID: dcd0d354f648
Revises: b5f586d58141
Create Date: 2020-05-02 18:58:03.864154

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'dcd0d354f648'
down_revision = 'b5f586d58141'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('account', sa.Column('created_at', sa.DateTime(), nullable=False))
    op.add_column('account', sa.Column('updated_at', sa.DateTime(), nullable=False))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('account', 'updated_at')
    op.drop_column('account', 'created_at')
    # ### end Alembic commands ###

After that, migration is completed with the upgrade command

bonus

What if I want to rename the files generated by migration?

Edit alembic.ini if you want to change the naming convention instead of dcd0d354f648_added_columns.py (for example, to include date information) Edit ** file_template ** in alembic.ini

Example


file_template = %%(year)d%%(month).2d%%(day).2d-%%(hour).2d%%(minute).2d_%%(slug)s

Will autogenerate detect any changes?

Please note that some changes may not be detected. See below for details Auto Generating Migrations — Alembic 1.4.2 documentation

Recommended Posts

I tried running alembic, a Python migration tool
I tried running python etc. from a bat file
[Python] I tried running a local server using flask
I tried Grumpy (Go running Python).
I tried running prolog with python 3.8.2.
I tried a functional language with Python
I created a password tool in Python.
[5th] I tried to make a certain authenticator-like tool with python
[2nd] I tried to make a certain authenticator-like tool with python
[3rd] I tried to make a certain authenticator-like tool with python
[4th] I tried to make a certain authenticator-like tool with python
[1st] I tried to make a certain authenticator-like tool with python
I tried running pymc
I tried playing a typing game in Python
I tried Python> autopep8
[Memo] I tried a pivot table in Python
I tried reading a CSV file using Python
I tried running faiss with python, Go, Rust
I tried running python -m summpy.server -h 127.0.0.1 -p 8080
I tried adding a Python3 module in C
I tried running Deep Floor Plan with Python 3.6.10.
I tried Python> decorator
I tried running TensorFlow
I tried to implement what seems to be a Windows snipping tool in Python
I tried to implement a pseudo pachislot in Python
I tried to automatically generate a password with Python3
I tried drawing a pseudo fractal figure using Python
I tried using Python (3) instead of a scientific calculator
Python: I tried a liar and an honest tribe
I tried fp-growth with python
I tried scraping with Python
I tried Python C extension
I made a python text
I tried scraping with python
I tried to implement a one-dimensional cellular automaton in Python
[Markov chain] I tried to read a quote into Python.
I tried "a program that removes duplicate statements in Python"
I tried using Tensorboard, a visualization tool for machine learning
I tried "How to get a method decorated in Python"
I created a class in Python and tried duck typing
I tried to make a stopwatch using tkinter in python
I tried a stochastic simulation of a bingo game with Python
I tried to touch Python (installation)
I tried web scraping with python.
I made a fortune with Python.
I tried running GAN in Colaboratory
I tried using Thonny (Python / IDE)
[Python] Creating a scraping tool Memo
I made a daemon with Python
I tried Line notification in Python
I tried SMTP communication with Python
[Python] I tried using YOLO v3
I tried to convert a Python file to EXE (Recursion error supported)
I tried running the Python Package Repository (Warehouse) that supports PyPI
I tried to make a regular expression of "amount" using Python
I tried to make a regular expression of "time" using Python
[Python] A memo that I tried to get started with asyncio
I tried running platypus which can solve a little optimization problem-Part 2
I tried to create a list of prime numbers with python
I tried to make a regular expression of "date" using Python
I tried to implement a misunderstood prisoner's dilemma game in Python