How to Expand Database Table Structure with Flask Migrate

Preface

When we have created several tables with the sqlchemy module, what should we do if we need to make changes to the table structure in an actual production environment? You can't delete the table, so the data will be lost.

A better solution is to use the database migration framework, which tracks changes to the database schema and then applies the changes to the database.

Flask-Migrate extensions can be used in Flask for data migration. And integrated into Flask-Script, all operations can be completed by commands.

Action example:

1. You need to install the flask-migrate module first, and of course the flask-script module.

pip3 install flask-migrate
pip3 install flask-script

2. Code:

#coding=utf-8
from flask import Flask

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate,MigrateCommand
from flask_script import Shell,Manager

app = Flask(__name__)
manager = Manager(app)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/Flask_test'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

# No. 1 1 The parameters are Flask For an example of, the 2 The parameters are Sqlalchemy Database instance 
migrate = Migrate(app,db) 

#manager Yes Flask-Script Instance of, this statement is in the flask-Script Add to 1 A db Command 
manager.add_command('db',MigrateCommand)

# Defining a model Role
class Role(db.Model):
  #  Define a table name 
  __tablename__ = 'roles'
  #  Defining Column Objects 
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(64), unique=True)
  user = db.relationship('User', backref='role')

  #repr() Method display 1 A readable string, 
  def __repr__(self):
    return 'Role:'.format(self.name)

# Define users 
class User(db.Model):
  __talbe__ = 'users'
  id = db.Column(db.Integer, primary_key=True)
  username = db.Column(db.String(64), unique=True, index=True)
  # Setting foreign keys 
  role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

  def __repr__(self):
    return 'User:'.format(self.username)


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

Focus on lines 8-20, 48. Change the original code to this.

3. Command-line actions

Create a migration warehouse

This command creates the migirations folder where all the migration files are placed

python3 xxx.py db init

Note: db is determined by 20 lines and can be changed

Automatically create migration scripts

python3 xxx.py db migrate -m" Version name ( Notes )"

Update the database

python3 xxx.py db upgrade

When we need to modify the table structure, directly add or delete the corresponding code in xxx. py

After the modification is completed, continue to create a new migration script

python  Documents  db migrate -m" New version name ( Notes )"

Update the database

python3 xxx.py db upgrade

After the update, it is actually a commit operation, which is similar to adding a new version of git.

However, if we want to return to the version of history, what should we do?

Check the version number first

python xxx.py db history

Then remember the version number you want to return.

Returns the specified version

python xxx.py db downgrade(upgrade)  Version number 

Then open your code and find that it automatically recovers!