I. General Situation

During the development of Django framework, we add or delete database fields, modify model classes directly, and then migrate them. It is very convenient. We also want Flask framework to support such operations, we need to use Flask-Migrate extensions to achieve data migration. And integrated into Flask-Script, all operations can be completed by command.

Two, installation

To export database migration commands, Flask-Migrate provides a MigrateCommand class that can be attached to the manager object of flask-script.
First install the following two extensions:

pip install Flask-Script

pip install flask-migrate

Three, configuration

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pymysql
from flask_migrate import Migrate, MigrateCommand
from flask_script import Manager

pymysql.install_as_MySQLdb()
app = Flask(__name__)

# Managing flask Program by Script
manager = Manager(app)

# Setting the URL to connect to the database
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@127.0.0.1:3306/db_flask'

# Setting that changes in the database will be submitted automatically after each request is completed
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

# Synchronized modification of database and model classes
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

# The original SQL statement is displayed when querying
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

# Create database migration objects
Migrate(app, db)

# Adding the database migration command db to step management refers to the alias of the command
manager.add_command('db', MigrateCommand)


# type
class Type(db.Model):
    # Table name
    __tablename__ = 'tbl_types'

    # Fields that really exist in the database
    id = db.Column(db.Integer, primary_key=True)  # Primary key
    name = db.Column(db.String(32), unique=True)  # Name

    # Fields that do not exist in the database are just for lookup and reverse lookup.
    # backref: Adding a reverse reference to another model of the relationship
    heros = db.relationship("Hero", backref='type')

    def __repr__(self):
        return self.name


# Hero
class Hero(db.Model):
    # Table name
    __tablename__ = 'tbl_heros'
    # Fields that really exist in the database
    id = db.Column(db.Integer, primary_key=True)  # Primary key
    name = db.Column(db.String(64), unique=True)  # Name
    gender = db.Column(db.String(64))  # Gender

    # Foreign keys A shooter corresponds to many heroes
    type_id = db.Column(db.Integer, db.ForeignKey("tbl_types.id"))

    def __repr__(self):
        return self.name


if __name__ == '__main__':
    # 0.0.0.0 represents any address that can represent the machine can be accessed.
    # app.run(host='0.0.0.0', port=5000)  # Operation procedure
    manager.run()

IV. Adding Fields

First we create the migrations folder by command, and then all the migration files will be placed in this folder.

python flask_migrate_db.py db init

As shown in the figure:

• Generate migration files

The following command, like makemigrations in Django, is used to generate migration files. Because our model class did not add or delete fields, all the first time there will be no change in the prompt.
- m: Annotate migrated files

 python flask_migrate_db.py db migrate -m 'first create'

Tips:

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.env] No changes in schema detected.

• Add new fields
  Let's add an age field to the hero and move on:

 age = db.Column(db.Integer)  # Age

transfer

 python flask_migrate_db.py db migrate -m 'add age' 

The migration file is generated into the migrations folder as shown below:

Generate migration files, at this time the database has not changed, we also need to use the upgrade command to synchronize to the database:

python flask_migrate_db.py db upgrade

Let's look at the table structure in the local database:

The age field has been added to the database.

Five, regression

To avoid errors, it is recommended to use the python flask_migrate_db.py db history command to view the specific version number of the historical version, and then copy the specific version number to perform the fallback.

base refers to the original version. For example, we found that the fields we just added had no effect, so we could go back to the original version.

python flask_migrate_db.py db downgrade base

Let's look at the table structure in the local database:
If there are many versions later, you can specify the version number directly. For example:

python flask_migrate_db.py db downgrade 4cee71e47df3

In Flask, there is an extension of object model class migration. It is convenient to maintain table structure.