4. Alembic data migration tool

4. Alembic data migration tool

alembic is used for the migration and mapping of ORM model and database. The usage of alembic is similar to that of git, in two aspects. 1. all commands of alembic start with alembic;

2. the migration files of alembic are also controlled by version. Installation method: pip install alembic

1.1. Use of alembic

 (1) Define the model

models.py

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

DB_URI = "mysql+pymysql://root:123456@127.0.0.1:3306/alembic_demo?charset=utf8"

engine = create_engine(DB_URI)

Base = declarative_base(engine)

class User(Base):
    __tablename__ ='user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

(2) Initialize in the cmd terminal and create a warehouse

alembic init learn_alembic

(3) Modify the configuration file and specify the connected database

alembic.ini

sqlalchemy.url = mysql+pymysql://root:123456@127.0.0.1:3306/alembic_demo?charset=utf8

(4) Add the directory path where models are located to env.py and specify target_metadata

import sys,os
# 1.__file__: current file (env.py)
#2.os.path.dirname(__file__): Get the directory of the current file
#3.os.path.dirname(os.path.dirname(__file__)): Get the upper level directory of the current file directory
#4.sys.path: python finds all paths of imported packages
sys.path.append(os.path.dirname(os.path.dirname(__file__)))
import models


target_metadata = models.Base.metadata

(5) Generate migration script

alembic revision --autogenerate -m "first commit"

(6) Map the generated migration script to the database

alembic upgrade head

If you want to add or modify the model in the future, repeat steps 5 and 6

1.2.alembic common commands and classic error solutions

Common commands and parameter explanation:

  • init: create an alembic warehouse
  • rebision: create a new version file
  • --autogenerate: automatically modify the current model to generate a migration script
  • -m: What changes have been made in this migration
  • upgrade: Map the migration file of the specified version to the database, and execute the upgrade function in the version file
  • head: represents the version number of the current migration script
  • downgrade: The downgrade function in the migration file of the specified version will be executed
  • heads: Display the currently available heads script files
  • history: List all migration versions and their information
  • current: Display the version number in the current database

Classic error

1.FAILED:Target databases is not up to date.

   Reason: Mainly heads and current are not the same. current is behind the version of heads

   Solution: Move current to the head. alembic upgrade head

2.FAILED: Can't locate revision identified by'xxxxxxx'

   Reason: The version number stored in the database is not in the migration script file

   Solution: Delete the data in the alembic_version table of the data and re-execute alembic upgrade head

1.3. Introduction to the use of the current command

Use alembic tool: a table alembic_version will be automatically generated in the database

The current version number can be viewed in the database

In the cmd terminal, you can also use the current command to view

alembic current

1.4. Use alembic under Flask-SQLAlchemy

(1) config.py

DB_URI = "mysql+pymysql://root:123456@127.0.0.1:3306/flask_alembic_demo?charset=utf8"

SQLALCHEMY_DATABASE_URI = DB_URI

(2) flask_alembic_demo.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config

app = Flask(__name__)
app.config.from_object(config)

db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ ='user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(50),nullable=False)
  

@app.route('/')
def hello_world():
    return'Hello World!'

if __name__ =='__main__':
    app.run()

(3) Initialization

alembic init alembic

(4) alembic.ini

sqlalchemy.url = mysql+pymysql://root:123456@127.0.0.1:3306/flask_alembic_demo?charset=utf8

(5) env.py

import sys,os
sys.path.append(os.path.dirname(os.path.dirname(__file__)))
import flask_alembic_demo

#Used is db.Model
target_metadata = flask_alembic_demo.db.Model.metadata

(6) Generate migration script

alembic revision --autogenerate -m "first commit"

(7) Upgrade to the database

alembic upgrade head

(8) Add fields

Suppose you want to add a field age

class User(db.Model):
    __tablename__ ='user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(50),nullable=False)
    age = db.Column(db.Integer)

Repeat the steps

alembic revision --autogenerate -m "add column age"

alembic upgrade head
Reference: https://cloud.tencent.com/developer/article/1390514 4.alembic data migration tool-cloud + community-Tencent Cloud