使用Alembic维护数据库结构

日常的工作就是在数据库上做增删改查操作。在刀耕火种的年代使用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,
sqlalchemy.url = postgresql://postgres:passwd@127.0.0.1:5432/test

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

开始维护数据库
$ alembic revision -m "create account table"
-m 参数可以填写这次操作的备注信息。

类似git一样,这个是创建一个维护SQL版本,这个命令执行成功会在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
        )
    )
)

创建表
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("''::character varying"), comment='用户名'),
    sa.Column(name='reg_time', type_=sa.DateTime, nullable=False,
              server_default=sa.text("'1970-01-01 08:00:00'::timestamp without time zone"), 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')

评论