Try running the python migration package alembic · PyPI
A migration tool that manages your DB when using SQLAlchemy in Python
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 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
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
env.py Contains a Python script that runs every time the alembic tool is launched It is written so that SQLAlchemy Engine can be set and generated and migration can be executed. Customizable script
READEME.md It describes what kind of environment the migration environment was created in.
script.py.mako Mako template file used to generate new migration scripts Anything here is used to generate a new file in version /
-** versions directory ** Directory where migration scripts are stored
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 **
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 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 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
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
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)
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()
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
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
Please note that some changes may not be detected. See below for details Auto Generating Migrations — Alembic 1.4.2 documentation
Recommended Posts