创建表后向 SQLAlchemy 模型添加索引 [英] Adding indexes to SQLAlchemy models after table creation

问题描述

我有一个flask-sqlalchemy模型:

I have a flask-sqlalchemy model:

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

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

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 = db.Column(db.String(2048), index=True)

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

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

推荐答案

给定原始问题中的模型类.

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))

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

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

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

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

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)

现在 engine 的来源将取决于您的 sqlalchemy 配置，但此代码应传达需要发生的事情的要点.

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

这篇关于创建表后向 SQLAlchemy 模型添加索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！