Flask-migrate:更改模型属性并重命名相应的数据库列 [英] Flask-migrate: change model attributes and rename corresponding database columns

问题描述

我对Flask有一点经验，但是对数据库却没有太多经验(Flask迁移/alembic/SqlAlchemy).

I have a bit of experience with Flask but not very much with databases (Flask-migrate / alembic / SqlAlchemy).

我正在关注此教程，一切正常.

I'm following this tutorial and things are working quite alright.

我有一个这样的用户模型:

I have a User model like this:

# user_model.py

from app import DB
... other imports

class User(UserMixin, DB.Model):

    __tablename__ = 'users'
    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))

然后我可以初始化数据库，进行迁移，升级等

I can then initialize the db, do migrations, upgrades etc.

当我想更改那个 id 属性时，问题就开始了，在Python中这不是一个很好的变量名选择.假设我想将其重命名为 user_id .

The problem started when I wanted to change that id attribute, which in Python is not a great variable name choice. Let's say I want to rename that to user_id instead.

现在显然数据库已经存在，并且里面有一些数据.我认为也许是通过Flask-Migrate/Alembic的某种魔术，只需修改User类即可.只需将上面的id行更改为:

Now obviously the db already exists and there is some data inside. I thought maybe by some kind of magic from Flask-Migrate/Alembic just modifying the User class would work. That is just change the id line above to:

user_id = DB.Column(DB.Integer, primary_key=True)

如果我这样做并运行 flask db migration ，我会得到:

If I do this and run flask db migrate I get:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'users.user_id'
INFO  [alembic.autogenerate.compare] Detected removed column 'users.id'

实际上，Alembic将其检测为被删除的列并添加了一个新列，我认为这是有道理的.

So actually Alembic detects this as a column being removed and a new one added, which I suppose makes sense.

但是，如果我运行 flask db upgrade ，这实际上是行不通的.我收到以下错误:

But this in fact doesn't work if I run flask db upgrade. I get the following error:

错误[alembic.env](sqlite3.OperationalError)无法添加NOT NULL具有默认值NULL的列[SQL:'ALTER TABLE用户ADD COLUMNuser_id INTEGER NOT NULL']

ERROR [alembic.env] (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL [SQL: 'ALTER TABLE users ADD COLUMN user_id INTEGER NOT NULL']

错误非常明显.关键是我不想添加新列，而只想重命名现有列.

The error is quite clear. The point is that I don't want to add a new column, I just want to rename an existing one.

环顾四周，我还尝试修改 script.py 以处理升级以使用alter_column方法:

Looking around I also tried to modify the script.py handling the upgrade to use the alter_column method:

def upgrade():
    ${upgrades if upgrades else "pass"}
    # just added this line below
    op.alter_column('users', 'id', nullable=False, new_column_name='user_id')

但是，这似乎也不起作用(我收到与上述相同的错误).

However this also doesn't seem to work (I get the same error as above).

因此，问题可以归结为一个非常简单的问题:如何使用Flask-Migrate在Flask应用程序中重命名数据库列?或者换句话说，如果我希望修改给定模型的属性，我该怎么做才能正确重命名数据库中的相应列名?

So the question boils down to a very simple one: how do I rename a database columns in a Flask app using Flask-Migrate? Or in other words, if I wish to modify the attributes of a given model, what do I have to do so that the corresponding column names in the database are correctly renamed?

推荐答案

要简单地在alembic脚本中重命名一列(与flask-migrate相同)，您所做的是正确的:

To simply rename a column in an alembic script (which is the same as flask-migrate), what you do is correct:

op.alter_column('users', 'id', nullable=False, new_column_name='user_id')

我认为问题来自于您还需要更改其约束作为主键:

The problem comes from, in my opinion, that you need also to change its constraint as primary key:

op.drop_constraint('user_pkey', 'users', type_='primarykey')
op.create_primary_key('user_pkey', 'users', ['user_id'])

您可能需要根据数据库类型来调整重新创建的主键的名称(对于PostgreSQL来说，它的工作方式如此)

You may need to adjust the name of the primary key you re-create depending of your database type (It works like this for me with PostgreSQL)

应该始终检查自动生成的Alembic脚本，如果不是为了简单的更改，往往会不执行我们想要的操作.

Autogenerated alembic scripts should always be reviewed, quite often they do not do what we want if it's not for simple changes.

注意::如果您的列 id 被用作外键，则您可能还想更改其他表中的外键约束.

Note: If your column id was used as a foreign key, you may also want to change the foreign key constraints in other tables.

Alembic中的Alter主键描述了相同类型的问题.

Alter Primary Key in Alembic describes the same kind of problem.

这篇关于Flask-migrate:更改模型属性并重命名相应的数据库列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！