Python 创建表后向 SQLAlchemy 模型添加索引

Adding indexes to SQLAlchemy models after table creation

提问by user964375

I have a flask-sqlalchemy model:

我有一个flask-sqlalchemy模型：

class MyModel(db.Model):
__tablename__ = 'targets'
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String(2048))

The table has already been created, and is in use. I want to create an index on the url attribute, so I pass index=True to it:

该表已创建并正在使用中。我想在 url 属性上创建一个索引，所以我将 index=True 传递给它：

url = db.Column(db.String(2048), index=True)

How can I make this index take effect, without deleting and recreating the table?

如何让这个索引生效，而不需要删除和重新创建表？

回答by vvladymyrov

Please note that this is incorrect and over-complicated answer

请注意，这是不正确且过于复杂的答案

The right way is to use index.createas it was said here.

正确的方法是index.create像这里所说的那样使用。

---

---

First of all make sure that you have latest snapshot of your database and is able to restore database from this snapshot.

首先确保您拥有数据库的最新快照并且能够从该快照恢复数据库。

For medium and large size projects (the ones that you might need to support several versions at the same time and are installed on multiple environments) there is special procedure which is part of database management lifecycle called "database migration". DB migrations includes changes to existing schema. SQLAlchemy doesn't support migration out of the box.

对于中型和大型项目（您可能需要同时支持多个版本并安装在多个环境中的项目），有一个特殊的过程，它是数据库管理生命周期的一部分，称为“数据库迁移”。数据库迁移包括对现有架构的更改。SQLAlchemy 不支持开箱即用的迁移。

There are two SQLAlchemy compatible database migration tools available:

有两种 SQLAlchemy 兼容的数据库迁移工具可用：

• Alembic
• SQLAlchemy-Migrate

• 阿朗比克
• SQLAlchemy-迁移

See more information and links to these tools in SQLAlchemy documentation page: Altering Schemas through Migrations.

在 SQLAlchemy 文档页面中查看更多信息和这些工具的链接：通过迁移改变模式。

But if your are working on small project I would suggest to manually run ALTER TABLE DDL query from the database command line utility or through connection.execute() in python script.

但是，如果您正在处理小型项目，我建议您从数据库命令行实用程序或通过 python 脚本中的 connection.execute() 手动运行 ALTER TABLE DDL 查询。

In the production application I'm working at right now, we support only one latest version of application. For every database schema change we do the following steps:

在我现在工作的生产应用程序中，我们只支持一个最新版本的应用程序。对于每个数据库架构更改，我们执行以下步骤：

• make a snapshot of the production database
• load this snapshot on development environment
• update sqlalchemy data model module
• prepare and run alter table query and save this query for later
• make other related changes to the code
• run tests on dev environment
• deploy latest version of the code to production
• do alter table on production

• 制作生产数据库的快照
• 在开发环境上加载此快照
• 更新 sqlalchemy 数据模型模块
• 准备并运行alter table query并保存此查询以备后用
• 对代码进行其他相关更改
• 在开发环境上运行测试
• 将最新版本的代码部署到生产中
• 更改生产表

Also I'm using the following trick for generating create table/index queries: I point my application to brand new database, enable logging of sqlalchemy queries and run metadata.create_all()- so in logs (or STDOUT) I see create query generated by sqlalchemy

此外，我使用以下技巧来生成创建表/索引查询：我将我的应用程序指向全新的数据库，启用 sqlalchemy 查询的日志记录并运行metadata.create_all()- 所以在日志（或 STDOUT）中我看到由 sqlalchemy 生成的创建查询

Depending on the database system you are using index creation query will be little different. Generic query would look like this:

根据您使用的数据库系统，索引创建查询会略有不同。通用查询如下所示：

create index targets_i on targets(url);

回答by zzzeek

Call create()on the Index:

调用create()索引：

index.create()

http://docs.sqlalchemy.org/en/latest/core/constraints.html#sqlalchemy.schema.Index.create

http://docs.sqlalchemy.org/en/latest/core/constraints.html#sqlalchemy.schema.Index.create

回答by brthornbury

Given the model class from the original question.

给定来自原始问题的模型类。

class MyModel(db.Model):
    __tablename__ = 'targets'
    id = db.Column(db.Integer, primary_key=True)
    url = db.Column(db.String(2048))

You cannot just add index=Truebecause even if you called db.Model.metadata.create_all()the index will not be created on an already created table.

您不能只是添加，index=True因为即使您调用db.Model.metadata.create_all()索引也不会在已创建的表上创建。

Instead, you need to create an independent Indexobject, and then create it. It will look something like this:

相反，您需要创建一个独立的Index对象，然后再创建它。它看起来像这样：

class MyModel(db.Model):
    __tablename__ = 'targets'
    id = db.Column(db.Integer, primary_key=True)
    url = db.Column(db.String(2048))

mymodel_url_index = Index('mymodel_url_idx', MyModel.url)

if __name__ == '__main__':
    mymodel_url_index.create(bind=engine)

Now where enginecomes from will be up to your sqlalchemy configuration, but this code should convey the gist of what needs to happen.

现在从哪里来engine取决于你的 sqlalchemy 配置，但这段代码应该传达需要发生什么的要点。

回答by Lucas Currah

I am not sure if this conforms to best practices but I found Alembicwould notify me of Indexes in the __table_args__but not actually make them for me during migrations. I made this small script that can generate new indexes found in the __table_args__property. It makes use of Index.create()as mentioned above, but will generate new indexes if they do not exist.

我不确定这是否符合最佳实践，但我发现Alembic会__table_args__在迁移期间通知我索引但实际上并没有为我制作它们。我制作了这个小脚本，可以生成在__table_args__属性中找到的新索引。它Index.create()如上所述使用，但如果它们不存在，将生成新索引。

def create_indexes(db, drop_index=False):
    """
    Creates all indexes on models in project if they do not exists already. Assumes all models
    inherit from RequiredFields class, otherwise will need to adjust search for subclasses. If the index
    exists SQLAlchemy throws an error and we assume everything is ok. 
    :param db: The app db object, acts as the engine param for the Index.create()
    :param drop_index: specifies whether the indexes should be dropped or created
    :return:
    """
    from application.base_models import RequiredFields
    from sqlalchemy import Index
    from sqlalchemy.exc import ProgrammingError
    for klass in RequiredFields.__subclasses__():
        if hasattr(klass, '__table_args__'):
            for item in getattr(klass, '__table_args__'):
                if isinstance(item, Index):
                    try:
                        if not drop_index:
                            item.create(db.engine)
                        else:
                            item.drop(db.engine)
                    except ProgrammingError:  # If index exists, creation fails on error
                        pass
    return

Here is a sample class showing the indexes.

这是一个显示索引的示例类。

class MyModel(RequiredFields):

    __table_args__ = (
         db.Index( ... ),
         db.Index( ... ),
    )

回答by bass chuck

Use flask-migrate.It's cool. After you add the index,just use this command:

使用flask-migrate。它很酷。添加索引后，只需使用以下命令：

python manage.py db migrate

Everything works fine

一切正常

回答by Rob Grant

If you're using Alembic, you can create a migration to do this, and avoid adding it into the model at all:

如果您使用的是 Alembic，您可以创建一个迁移来执行此操作，并完全避免将其添加到模型中：

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_index('table1_id', 'table1', ['id'], unique=True)
    op.create_index('table2_id', 'table2', ['id'], unique=True)
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('table1_id', table_name='table1')
    op.drop_index('table2_id', table_name='table2')
    # ### end Alembic commands ###

回答by flomaster

Since the question was asked, support has been added for this.

自从提出问题以来，已为此添加了支持。

Now you can just add index=Trueto an existing column, and auto-generate the migration.

现在您只需添加index=True到现有列，并自动生成迁移。

Checked on the following package versions:

检查以下软件包版本：

alembic==1.0.10
SQLAlchemy==1.3.4
SQLAlchemy-Utils==0.34.0
Flask-SQLAlchemy==2.4.0
Flask-Migrate==2.5.2