使用Alembic维护数据库结构

2022.01.22 17:14

日常的工作就是在数据库上做增删改查操作。在刀耕火种的年代使用SQL文件保存数据库的维护记录，这样的手动操作，效率低，而且容易出错。所以现在的框架里都有专业的工具来管理数据库的维护记录，避免直接操作数据库。Laravel 框架里有 db migration 工具。

Python里使用 Alembic 维护数据。Alembic是SQLAlchemy团队的作品，熟悉SQLAlchemy，对Alembic的使用就不陌生。把数据库操作生成好python文件，就方便进行版本控制。

那如果已有数据库，但是需要生成model code文件，可以使用sqlacodegen（https://github.com/agronholm/sqlacodegen）

简单的用法：

sqlacodegen postgresql+psycopg2://postgres:server_pwd@127.0.0.1:5432/test_db --outfile=db_model.py

在这里操作使用的数据库是PostgreSQL。

安装好alembic后，先初始化环境：

$ alembic init migrate

migrate可以改成其它名字，数据库维护文件保存在这里。执行完成后，项目的根目录会生成一个alembic.ini文件

修改alembic.ini，更新数据库的连接信息

PostgreSQL：sqlalchemy.url = postgresql://postgres:passwd@127.0.0.1:5432/test

MySQLmysql+pymysql://root:password@127.0.0.1:3306/test

简单的环境初始化工作完成。

开始维护数据库

新建账号表$ alembic revision -m "create account table"

和git相似，这个操作创建一个数据库的维护版本。-m参数是这次版本的备注信息。这个命令执行成功会在migrate/versions目录生成一个py文件，文件名类似于1975ea83b712_create_account_table.py，文件名前面的1975ea83b712是版本号，后面是注释信息。
这个文件里两个空方法，upgrade表示升级时执行，downgrade表示回退版本执行。
版本号不是数字，alembic运行会遍历所有的versions目录里的文件。down_revision为None就是代表数据库维护记录的第一个文件。

在upgrade、downgrade里写好相对的代码后，开始执行

$ alembic upgrade head

这个时候需求变了，产品觉得，还是上个版本好，只好回退一波了

$ alembic downgrade -1

不是，老板还是觉得之前做的那个好，再加回来吧

$ alembic upgrade +1

反复横跳应付自如。

查看当前版本信息

$ alembic current

下面是个简单的例子，上手操作一下。

alembic revision -m "create account table"

开始第一个创建第一个版本。这里的备注我随便写的。实践中最好写一个「信达雅」的备注信息。

在这个版本里我们添加用户表。我们需要用户表自增的uid。这里使用op.execute创建序列对象

op.execute(
    sa.schema.CreateSequence(
        sa.schema.Sequence(
            name='user_id_seq',
            start=1,
            increment=1,
            minvalue=1
        )
    )
)

创建表，设置server_default的值，指定nextval为user_id_seq

op.create_table(
    'user',
    sa.Column(name='uid', type_=sa.BigInteger, server_default=sa.text("nextval('user_id_seq'::regclass)")),
    sa.Column(name='username', type_=sa.String(128), nullable=False,
              server_default=sa.text("''::charactervarying"), comment='用户名'),
    sa.Column(name='reg_time', type_=sa.DateTime, nullable=False,
              server_default=sa.text("'1970-01-0108:00:00'::timestampwithouttimezone"), comment='注册时间'),
    sa.Column(name='status', type_=sa.SmallInteger, nullable=False,
              server_default=sa.text("1"), comment='用户状态；1正常；2禁用'),
)

uid设置主键

op.create_primary_key(constraint_name='user_pk',table_name='user',columns=['uid'])

username设置唯一键

op.create_unique_constraint(constraint_name='u_uk',table_name='user',columns=['username'])

reg_time设置索引

op.create_index(index_name='u_ik',table_name='user',columns=['reg_time'])

表添加备注

op.create_table_comment(table_name='user',comment='用户表')

这些是升级时的执行操作，添加在upgrade里。对应的downgrade方法里添加回退版本操作。

删除表

op.drop_table('user')

删除序列对象

op.execute(
    sa.schema.DropSequence(
        sa.schema.Sequence(
            name='user_id_seq'
        )
    )
)

注意，要先删除引用序列对象的表，然后再删除序列对象。

执行一下改动

$ alembic upgrade head

数据库里就多出来user表。

创建外键。表添加外键限制，可以使用ForeignKey方法来指定关系。

sa.Column(
    sa.ForeignKey(
        column='user.uid',
        name='fp_aq_user_id',
        ondelete='CASCADE',
        onupdate='CASCADE'
    ),
    name='uid',
    type_=sa.BigInteger,
    comment='博文的作者uid'
)

Alembic