【ORM】【SQLAlchemy】使用 Alemic

1 Install

$ pip install alembic

2 初始化

$ cd project_source
$ alembic init alembic
  Creating directory .../project_source/alembic ... done
  Creating directory .../project_source/alembic/versions ... done
  Generating .../project_source/alembic/README ... done
  Generating .../project_source/alembic/env.py ... done
  Generating .../project_source/alembic/script.py.mako ... done
  Generating ../project_source/alembic.ini ... done
  Please edit configuration/connection/logging settings in
  '.../project_source/alembic.ini' before proceeding.
$ tee | grep -v pyc
.
├── alembic
│   ├── env.py
│   ├── README
│   ├── script.py.mako
│   └── versions
├── alembic.ini
├── main.py
├── models.py

3 directories, 7 files
$

3 配置

修改 alembic.ini 文件中的 sqlalchemy.url = 一行即可。

如：
sqlalchemy.url = mysql://yourusername:yourpassword/@localhost:3306/thedatabase

检查：

$ alembic current 
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
$

4 Auto Generating Migrations

Alembic can view the status of the database and compare against the table metadata in the application, generating the “obvious” migrations based on a comparison. This is achieved using the --autogenerateoption to the alembic revision command, which places so-called candidate migrations into our new migrations file. We review and modify these by hand as needed, then proceed normally.

To use autogenerate, we first need to modify our env.py so that it gets access to a table metadata object that contains the target. Suppose our application has a declarative base in myapp.mymodel. This base contains a MetaData object which contains Table objects defining our database. We make sure this is loaded in env.py and then passed to EnvironmentContext.configure() via the target_metadata argument. The env.py sample script used in the generic template already has a variable declaration near the top for our convenience, where we replace None with our MetaData. Starting with:

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

4.4.1 configuration

we change to:

import os, sys
sys.path.append(os.getcwd())
from models import Base
target_metadata = Base.metadata

Note: The above example refers to the generic alembic env.py template, e.g. the one created by default when calling upon alembicinit, and not the special-use templates such as multidb. Please consult the source code and comments within the env.py script directly for specific guidance on where and how the autogenerate metadata is established.

4.4.2 makemigrations

Reference:
If we look later in the script, down in run_migrations_online(), we can see the directive passed to EnvironmentContext.configure():

def run_migrations_online():
    engine = engine_from_config(
                config.get_section(config.config_ini_section), prefix='sqlalchemy.')

    with engine.connect() as connection:
        context.configure(
                    connection=connection,
                    target_metadata=target_metadata
                    )

        with context.begin_transaction():
            context.run_migrations()

We can then use the alembic revision command in conjunction with the --autogenerate option. Suppose our MetaData contained a definition for the account table, and the database did not. We’d get output like:

$ alembic revision --autogenerate -m "Added user and infor table"
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added table 'infor'
  Generating .../project_source/alembic/versions/a97cf62732db_added_user_and_
  infor_table.py ... done
$ tree | grep -v pyc                                             
.
├── alembic
│   ├── env.py
│   ├── README
│   ├── script.py.mako
│   └── versions
│       ├── a97cf62732db_added_user_and_infor_table.py
├── alembic.ini
├── main.py
├── models.py

5 directories, 10 files

Reference:
We can then view our file 27c6a30d7c24.py and see that a rudimentary migration is already present:

"""empty message

Revision ID: 27c6a30d7c24
Revises: None
Create Date: 2011-11-08 11:40:27.089406

"""

# revision identifiers, used by Alembic.
revision = '27c6a30d7c24'
down_revision = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
    'account',
    sa.Column('id', sa.Integer()),
    sa.Column('name', sa.String(length=50), nullable=False),
    sa.Column('description', sa.VARCHAR(200)),
    sa.Column('last_transaction_date', sa.DateTime()),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("account")
    ### end Alembic commands ###

The migration hasn’t actually run yet, of course. We do that via the usual upgrade command. We should also go into our migration file and alter it as needed, including adjustments to the directives as well as the addition of other directives which these may be dependent on - specifically data changes in between creates/alters/drops.

mysql> show tables;
+-----------------------+
| Tables_in_thedatabase |
+-----------------------+
| alembic_version       |
+-----------------------+
1 row in set (0.00 sec)

mysql>

4.4.3 migrate

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> a97cf62732db, Added user and infor table
$ 

result:

mysql> show tables;
+-----------------------+
| Tables_in_thedatabase |
+-----------------------+
| alembic_version       |
| infor                 |
| user                  |
+-----------------------+
3 rows in set (0.00 sec)

mysql> DESC user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| userid   | varchar(20) | YES  |     | NULL    |                |
| username | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>DESC infor;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| infor_id  | int(11)     | NO   | PRI | NULL    | auto_increment |
| yuanchuan | smallint(6) | YES  |     | NULL    |                |
| zhuanzai  | smallint(6) | YES  |     | NULL    |                |
| visitor   | int(11)     | YES  |     | NULL    |                |
| like_     | int(11)     | YES  |     | NULL    |                |
| comment   | int(11)     | YES  |     | NULL    |                |
| rank      | int(11)     | YES  |     | NULL    |                |
| fanses    | int(11)     | YES  |     | NULL    |                |
| follows   | int(11)     | YES  |     | NULL    |                |
| date      | date        | YES  |     | NULL    |                |
| user_id   | int(11)     | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql>

---

Reference:

1. Alembic Tutorial | alembic.sqlalchemy.org
2. Auto Generating Migrations | alembic.sqlalchemy.org
3. SQLalchemy 版本迁移工具 alembic 使用 | jianshu.com