如何使用Flask-Migrate进行数据库迁移? [英] How to use Flask-Migrate to do database migration?

问题描述

在使用Flask-Migrate升级/降级数据库时遇到了一个问题. User和Post这两个表由以下类定义:

I come across a problem when playing with Flask-Migrate to upgrade/downgrade the database. There were two tables, User and Post, defined by the following classes:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def __repr__(self):
        return '<User {}>'.format(self.username)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id')) 

我已经在这些表中添加了一些条目.

And I have added some entries into these tables.

现在是问题所在. :)

Now the problem. :)

为了娱乐，我通过dummy = db.Column(db.String(20))在User表中添加了一个列.修改表架构后，我运行了以下命令:

For fun, I added a column to User table through: dummy = db.Column(db.String(20)). After modified the table schema, I ran the following commands:

1. flask db migrate ----成功
2. flask db upgrade ----成功

1. flask db migrate ---- success
2. flask db upgrade ---- success

然后我想回去:

1. 要将数据库恢复到以前的状态，我运行了flask db downgrade ----失败，并显示错误:sqlite3.OperationalError: near "DROP": syntax error
2. 我试图通过从User类中删除dummy = db.Column(db.String(20))语句来修复错误，然后再次运行flask db downgrade，再次失败，并出现相同的错误.
3. 然后我想也许Flask-Migrate需要意识到删除的存在，所以我运行了flask db migrate ----成功
4. 尝试flask db upgrade ----再次失败，出现相同的错误.

1. To restore the database back to previous state, I ran flask db downgrade ---- failed with the Error: sqlite3.OperationalError: near "DROP": syntax error
2. I tried to fix the error by deleting the dummy = db.Column(db.String(20)) statement from User class, then ran flask db downgrade again ---- failed again with the same error.
3. Then I think maybe the Flask-Migrate need to be aware of the deletion , so I ran flask db migrate ---- success
4. Try flask db upgrade ---- failed again with the same error.

那么长颈瓶迁移的工作原理是什么?具体来说，如何将数据库还原到初始状态(没有dummy属性)?

So How does the flask-migrate work? And specifically, how can I restore the database to initial state (without the dummy attribute)?

谢谢！

推荐答案

SQLite本身不支持删除或更改列(显然我猜您使用的是SQLite).这意味着SQLite不支持ALTER语句，但是关系模式迁移依赖于此语句.

Natively, SQLite does not support dropping or altering columns (obviously I guess you use SQLite). This means that the ALTER statement is not supported by SQLite, yet relational schema migrations rely on this statement.

要解决此问题，您必须制作一系列与新结构相对应的SQLite表副本，将数据从现有表转移到新表中，然后删除旧表.

To work around the problem, you must make a series of copies of the SQLite tables that correspond to the new structure, transfer the data from the existing table to the new one, and then delete the old table.

幸运的是，对于Alembic/Flask迁移，有一个上下文管理器(batch_alter_table)，您可以轻松管理所有这些更改.

Fortunately, for the case of Alembic / Flask-migrate, there is a context manager ( batch_alter_table) that allows you to easily manage all these changes.

对于您而言，解决方案是打开您的迁移脚本，并在downgrade()方法级别将以下指令替换为(可能为op.drop_column('roles', 'dummy')):

In your case, the solution is to open your migration script and at the level of the downgrade() method, replace the instruction that is there (probably op.drop_column('roles', 'dummy')) by:

with op.batch_alter_table('roles') as batch_op:
    batch_op.drop_column('dummy')

有关更多信息，请参见此链接.

See this link for more information.

一些小细节:

• 在使用迁移工具时，请切记，自动迁移并不总是准确的，并且会遗漏一些细节.自动生成的迁移脚本应始终进行审查.

• When using migration tools, always keep in mind that automatic migrations are not always accurate and can miss some details. Migration scripts generated automatically should always be reviewed.

以防万一，在降级数据库时，请确保删除迁移脚本，然后生成一个新脚本来替换它.

Just in case, when downgrade your database, make sure you delete the migration script, and then generate a new one to replace it.

这篇关于如何使用Flask-Migrate进行数据库迁移?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！