SQLAlchemy:如何将列添加到现有表? [英] SQLAlchemy: How to add column to existing table?

问题描述

我正在尝试使用SQLAchemy将字段或列添加到现有表中.

I am trying to add a field or column to an existing table using SQLAchemy.

下面是表类

class ReleaseVersion(Base):

    __tablename__ = 'versions'

    id = Column(Integer, primary_key=True, autoincrement=True)
    release = Column(String(128), nullable=False, unique=True)


    def __init__(self,release, id=None):

        if(id):
            self.id = id

        self.release = release

我使用下面的行初始化了表

I initialized the table using following line

myDB.ReleaseVersion.__table__.create(bind=self.engine, checkfirst=True)

使用DB一段时间后，我需要添加一个布尔字段'is_currentversion'，同时保留表的所有现有内容，但是我不确定如何做到这一点.

After using the DB for some time, I need to add a boolean field, 'is_currentversion' while keeping all the existing content of the table, but I am not so sure how to do this.

我应该手动在表中创建字段以更新类吗? 或者，在表类中添加字段，如果初始化函数中不存在该字段，则添加列?

Should I manually create the field to the table an update the class? Alternatively, add the field in the table class and add column if it does not exist in the initialization function?

推荐答案

您正在寻找的被称为数据库迁移.使用类似烧瓶迁移(由Miguel Grinberg使用 alembic 模块)允许您更改数据库架构-添加或删除列-而不会丢失数据.它还会对这些数据库迁移进行版本控制，以便在必要时可以还原.

What you’re looking for is called a database migration. Using something like Flask-Migrate (written by Miguel Grinberg using the alembic module) allows you to change the database schema - adding or deleting columns - without losing your data. It also versions these database migrations so you can revert back if necessary.

Flask-Migrate使用Alembic模块，因此它们具有相同的功能，Flask-Migrate用于在Flask和SQL-Alchemy应用程序中正确设置Alembic.

Flask-Migrate uses the Alembic module so they both have the same functionality, Flask-Migrate is used to correctly setup alembic with your Flask and SQL-Alchemy application.

这是精美印刷的精彩视频，用于设置Flask-Migrate.请注意，Flask-Migrate适用于将SQL-Alchemy用作ORM的Flask应用程序.

Here is a great video by Pretty Printed for setting up Flask-Migrate. Note that Flask-Migrate is intended for Flask applications that are using SQL-Alchemy as the ORM.

您有两个选择.

1. 如果您只想跟踪将来的数据库迁移

运行flask db init，以创建迁移存储库. 将新列添加到您的数据库模型. 运行flask db migration，以生成迁移.迁移脚本中将仅包含新列. 运行flask db upgrade将新迁移应用到您的数据库. 此时，您的数据库应具有新列，您可以继续工作.每当您需要进行其他更改时，请重复上述步骤.

Run flask db init, to create the migration repository. Add the new column to your database model. Run flask db migrate, to generate a migration. The migration script will only have the new column in it. Run flask db upgrade to apply the new migration to your database. At this point your database should have the new column, and you can continue working. Repeat the above steps any time you need to make additional changes.

请注意，使用这种方法，您不能从头开始重新创建整个数据库.您必须有一种方法可以将数据库初始化为您在第1天拥有的架构，然后可以将迁移历史记录应用于该数据库，以将其升级到当前架构.

Note that with this approach, you cannot recreate the entire database from scratch. You have to have a way to initialize the database to the schema you had on Day 1, and then you can apply the migration history to that to upgrade it to your current schema.

1. 如果要跟踪整个迁移历史记录，包括将Flask-Migrate添加到应用程序当天的架构.

这有点棘手，但是可以做到.

This is a little bit tricky, but it can be done.

从flask db init开始，以创建迁移存储库. 接下来，您需要诱使Flask-Migrate认为您的数据库为空.您可以通过重命名实际的数据库并创建一个没有表名称的新数据库来做到这一点.在那种状态下，运行flask db migration.这将生成一个包含数据库整个架构的迁移. 完成初始迁移后，将数据库还原到正确的状态. 运行flask db Stamp head以将数据库标记为已更新. 将新列添加到您的数据库模型. 再次运行flask db migration，以生成第二次迁移.迁移脚本中将仅包含新列. 运行flask db upgrade将新的迁移应用于您的数据库.

Start with flask db init, to create the migration repository. Next, you need to trick Flask-Migrate into thinking your database is empty. You can do this by renaming your actual db, and creating a new db with the same name that has no tables in it. In that state, run flask db migrate. This will generate a migration that contains the entire schema of your database. Once you have that initial migration, restore your database to the correct state. Run flask db stamp head to mark the database as updated. Add the new column to your database model. Run flask db migrate again, to generate a second migration. The migration script will only have the new column in it. Run flask db upgrade to apply the new migration to your database.

(改编自Miguel Grinberg的

(Adapted from Miguel Grinberg's answer)

这篇关于SQLAlchemy:如何将列添加到现有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！