Flask-admin Chapter III: Database Migration Tool Alembic preliminary use

1. Overview

Building the web based on the Flask framework typically uses the Sqlchemy (which is used in flask) as the database engine.

So the logic of the business can not be coupled with the specific database type, the specific back-end business is the use of that kind of database is all business needs.

But the structure of the data table is not necessarily immutable, and the structure of the table may often change as the business needs it. In the face of this situation, simply delete the previous table,

Re-building a new table can result in data loss and trouble if you migrate yourself. Think that, if you migrate, you may only be able to cut to a newer version, but

It is almost impossible to roll back to the previous version, which involves using the database migration tool.

For flask, there are two more important database migration tools:flask migrate and alembic. Flask Migrate is based on alembic, but the individual

Feel flask migrate not flexible, and use alembic is more convenient. So, my side is to use Alembic directly as a database migration tool.

First of all, a little introduction to the next alembic,alembic is created by Sqlchemy author, the compatibility of Sqlchemy is certainly not comparable to the other components at all.

2. Installation

Installation of Alembic is very convenient, directly using the Python installation tool PIP can be, the specific command is as follows:

sudo pip install Alembic

3. Initialization

Go back to the directory where Flask application is located and execute the following command:

Alembic Init Alembic

After successful execution, you will be able to see two more important files in the current directory: thealembic.ini file and the alembic directory.

Alembic.ini saves the configuration information for Alembic, and the database version information is recorded in the Alembic directory.

The organization of the Alembic directory is as follows:

[[email protected] alembic] # LS-TRLH Total 16K-rw-r--r--1 root root *   19:36 README-rw-r--r--1 root root 494 may  19:36
   
     Script.py.mako-rw-r--r--1 root root 2.1K may 02:43
     env.pydrwxr-xr-x 2 root root 4.0K May 12 18:53 Versions
   

This piece works with two, one is the env.py file and the versions directory.

env.py Records Some information related to the environment, the comparison is target_metadata This field, later in the introduction of use will be mentioned.

The versions directory below is a Python file that records all versions of the current database and the corresponding operations.

The contents of each file are broadly as follows:

"" "Empty messagerevision id:27c6a30d7c24revises:nonecreate date:2011-11-08 11:40:27.089406" "" # Revision identifiers, Used by alembic.revision = ' 27c6a30d7c24 ' down_revision = nonefrom Alembic import opimport sqlalchemy as Sadef upgrade (): 
   passdef downgrade ():    Pass

The revision field records the version represented by the current file, and the initial down_revision is none. Specific in the use of the process is generally pointed to the previous version, the specific

is useful during the rollback process. By default, both upgrade and downgrade are empty.

This function must be overridden during database upgrade or rollback, and if you want to upgrade you need to override the upgrade function, you will need to override the downgrade function for rollback.

4. An example of a simple answer

You must first change the sqlalchemy.url field in the alembic.ini file, and the data path you are currently using is OK.

Here, I was instead:

Sqlalchemy.url = Sqlite:////home/dashuju/admin_db.sqlite

The other fields of this file can be changed temporarily, and the current settings can be used.

Change the env.py file under the Alembic directory, mainly setting target_metadata This field:

Import syssys.path.append ('. ') From Admin_app import dbtarget_metadata = Db.metadata

Admin_app is the file name of flask application, DB is the global variable defined in Flask_app,

Specifically initialized to:db = SQLAlchemy (APP)

Change the current version of the Py file under the versions directory, and according to the corresponding upgrade and downgrade functions, the specific code is as follows:

def upgrade ():    op.create_table (        ' users ',        SA. Column (' id ', SA. Integer, primary_key=true),        SA. Column (' name ', SA. String (a), nullable=false),        SA. Column (' description ', SA. Unicode (+)),    def downgrade ():    op.drop_table (' users ')

Perform:

Alembic Upgrade Head

command to upgrade the database and view the corresponding database, you can see that there is one more users table in the database.

Looking at the files in the versions directory, you will find a more py file. Can be performed in the directory where Flask application is located:

Alembic current to see which version of the database is currently in place, or you can use Alembic history to view a historical record of database changes.

If you want to roll back, you can also use the alemibic downgrade-1 instruction to roll back the database to the previous version.

Change Table structure

If you want to change the table structure, such as inserting column or deleting column, deleting column may be special, so say:

Insert rows, typically as follows:

Op.add_column (' users ', SA. Column (' id ', SA. INTEGER (), nullable=true))

Deleting rows may be special, as follows:

With Op.batch_alter_table (' users ') as Batch_op:        batch_op.drop_column (' id ')       

If you want to delete multiple columns, you can add more lines under the WITH statement, or you will get an error:

Sqlalchemy.exc.OperationalError: (sqlite3. Operationalerror) near "DROP": syntax error [sql:u ' ALTER TABLE posts DROP COLUMN XXX ']

5. Follow-up

The use of Alembic is continuing, just touching, recording.

Flask-admin Chapter III: Database Migration Tool Alembic preliminary use