参考 https://sqlalchemy-migrate.readthedocs.org/en/latest/versioning.html#writing-for-a-specific-database
0.作用
可对数据库进行版本升级/降级
新版本的代码,数据库表结构发生变化。采用migrate可直接改变数据库的表结构而不影响原有的数据。
一。 SQLALchemy 迁移提供两种方式
1)api方式 migrate.versioning API
2)migrate 命令行方式
二。涉及概念
repository : 数据库迁移所需要的配置文件、脚本等保存的目录
三。迁移的工作流程
命令行方式
1.建立repository
- migrate create /home/cloud/your_repository "example project"
在创建新的repository(/home/cloud/your_repository),名为'example project'
目录下的内容
- cloud@ubuntu22:~/your_repository$ ls -l
- total 20
- -rw-r--r-- 1 cloud cloud 0 五月 29 15:18 __init__.py
- -rw-r--r-- 1 cloud cloud 185 五月 29 15:18 __init__.pyc
- -rw-rw-r-- 1 cloud cloud 116 九月 10 17:02 manage.py
- -rw-r--r-- 1 cloud cloud 1242 九月 10 17:02 migrate.cfg
- -rw-r--r-- 1 cloud cloud 107 五月 29 15:18 README
- drwxr-sr-x 2 cloud cloud 4096 五月 29 15:18 versions
versions: 保存迁移所需要的各个版本脚本的目录
manage.py : 执行这个脚本跟执行migrate命令一样的效果,但是manage.py可以预设一些参数值(现在还没有设置,后面会说明)
migrate.cfg: 配置文件
repository_id : 上面命令行定义的name
version_table: 数据库记录migrate版本的表名
2.建立版本控制(即说明要迁移的数据库名,并且将所需信息写入数据库)
- python /home/cloud/your_repository/manage.py version_control sqlite:///test.db /home/cloud/your_repository/
查看当前版本
- python /home/cloud/your_repository/manage.py db_version sqlite:///test.db your_repository/
- >>0
使用manage.py的预设参数,省去每次执行命令都带上数据库信息以及repository信息
- migrate manage manage.py --repository=/home/cloud/your_repository --url=sqlite:///test.db
新添了url以及repository的值
查看当前版本
- python /home/cloud/your_repository/manage.py db_version
- >>0
4.添加版本脚本(版本1,添加新表account)
- python /home/cloud/your_repository/manage.py script "add account table"
编辑001_add_account_table.py,
upgrade() 更新数据库到该新版本,需要包含有对数据库schema的更新
downgrade() 回滚upgrade做的操作。
这里upgrade()方法创建新表account,downgrade方法删除表account
- from sqlalchemy import Table, Column, Integer, String, MetaData
- meta = MetaData()
- account = Table(
- 'account', meta,
- Column('id', Integer, primary_key=True),
- Column('login', String(40)),
- Column('passwd', String(40)),
- )
- def upgrade(migrate_engine):
- meta.bind = migrate_engine
- account.create()
- def downgrade(migrate_engine):
- meta.bind = migrate_engine
- account.drop()
5.测试脚本
执行版本升级前,测试脚本是否有语法错误
- python /home/cloud/your_repository/manage.py test
- >>Upgrading...
- done
- Downgrading...
- done
- Success
- python /home/cloud/your_repository/manage.py upgrade
- >>0 -> 1...
- done
添加了新表account,以及更新数据库表migrate_version,版本变为1
7.修改已存在的表
给表account添加新属性email
- python /home/cloud/your_repository/manage.py script 'add email column'
- from sqlalchemy import Table, MetaData, String, Column
- def upgrade(migrate_engine):
- meta = MetaData(bind=migrate_engine)
- account = Table('account', meta, autoload=True)
- emailc = Column('email', String(128))
- emailc.create(account)
- def downgrade(migrate_engine):
- meta = MetaData(bind=migrate_engine)
- account = Table('account', meta, autoload=True)
- account.c.email.drop()
执行升级操作
- python /home/cloud/your_repository/manage.py upgrade
- >>1 -> 2...
- done
8.数据库降级
- python /home/cloud/your_repository/manage.py db_version
- >>2
- python /home/cloud/your_repository/manage.py downgrade 1
- >>2 -> 1...
- done
- python /home/cloud/your_repository/manage.py db_version
- >>1
9.其它migrate命令
migrate -h
migrate -h db_version
api方式
参考https://sqlalchemy-migrate.readthedocs.org/en/latest/api.html#module-migrate.versioning.api
migrate.versioning.api 函数与migrate命令是相互对应。
如migrate.versioning.api.upgrade 对应migrate upgrade
实现过程
1.仿照your_repository建立新目录/home/cloud/my_repository及目录下的文件
manage.py
- #!/usr/bin/env python
- from migrate.versioning.shell import main
- if __name__ == '__main__':
- main(debug='False', repository='.')
- #main(url='sqlite:///project.db', debug='False', repository='my_repository')
migrate.cfg
- [db_settings]
- # Used to identify which repository this database is versioned under.
- # You can use the name of your project.
- repository_id=example_project
- # The name of the database table used to track the schema version.
- # This name shouldn't already be used by your project.
- # If this is changed once a database is under version control, you'll need to
- # change the table name in each database too.
- version_table=migrate_version
- # When committing a change script, Migrate will attempt to generate the
- # sql for all supported databases; normally, if one of them fails - probably
- # because you don't have that database installed - it is ignored and the
- # commit continues, perhaps ending successfully.
- # Databases in this list MUST compile successfully during a commit, or the
- # entire commit will fail. List the databases your application will actually
- # be using to ensure your updates to that database work properly.
- # This must be a list; example: ['postgres','sqlite']
- required_dbs=[]
- # When creating new change scripts, Migrate will stamp the new script with
- # a version number. By default this is latest_version + 1. You can set this
- # to 'true' to tell Migrate to use the UTC timestamp instead.
- use_timestamp_numbering=False
/my_repository/versions/001_Add_account_table.py
- from sqlalchemy import Column,MetaData,Integer,String,Table
- from migrate import *
- meta = MetaData()
- account = Table(
- 'account', meta,
- Column('id', Integer, primary_key=True),
- Column('login', String(40)),
- Column('passwd', String(40)),
- )
- def upgrade(migrate_engine):
- # Upgrade operations go here. Don't create your own engine; bind
- # migrate_engine to your metadata
- #meta =MetaData()
- meta.bind = migrate_engine
- #account = Table(
- # 'account',meta,
- # Column('id', Integer, primary_key=True),
- # Column('login', String(40)) )
- try:
- account.create()
- except:
- pass
- from sqlalchemy import Table, MetaData, String,Column
- from migrate import *
- def upgrade(migrate_engine):
- # Upgrade operations go here. Don't create your own engine; bind
- # migrate_engine to your metadata
- meta = MetaData()
- meta.bind = migrate_engine
- account = Table('account', meta, autoload=True)
- emailc = Column('email', String(128))
- emailc.create(account)
- def downgrade(migrate_engine):
- # Operations to reverse the above upgrade go here.
- meta = MetaData(bind=migrate_engine)
- account = Table('account', meta, autoload=True)
- account.c.email.drop()
2.编写db迁移程序migrate.py
参考openstack cinder-manager db sync
- from migrate.versioning import api as versioning_api
- import sqlalchemy
- _ENGINE = None
- def get_engine():
- global _ENGINE
- if _ENGINE:
- return _ENGINE
- sql_connection = "sqlite:home/cloud/project.db"
- engine_args = {
- "pool_recycle": 3600,
- "echo": False
- }
- _ENGINE = sqlalchemy.create_engine(sql_connection, **engine_args)
- _ENGINE.connect()
- return _ENGINE
- def get_repository():
- repository_path = "/home/cloud/my_repository"
- return repository_path
- def db_version():
- repository = get_repository()
- try:
- return versioning_api.db_version(get_engine(), repository)
- except :
- # if we aren't version controlled we may already have the database
- # in the state from before we started version control, check for that
- # and set up version_control appropriately
- meta = sqlalchemy.MetaData()
- engine = get_engine()
- meta.bind = engine
- tables = meta.tables
- if len(tables) == 0:
- db_version_control(000)
- return versioning_api.db_version(get_engine(), repository)
- else:
- #db has tables without migrate_version, it out of control
- raise
- def db_version_control(version=None):
- repository = get_repository()
- versioning_api.version_control(get_engine(), repository, version)
- def migrate_db(version=None):
- if version is not None:
- try:
- version = int(version)
- except ValueError as exception:
- raise exception
- #get current version
- current_version = db_version()
- repository = get_repository()
- if version is None or version > current_version:
- return versioning_api.upgrade(get_engine(), repository, version)
- else:
- return versioning_api.downgrade(get_engine(), repository, version)
- if __name__ == "__main__":
- migrate_db()