在创建表之后向SQLAlchemy模型添加索引 [英] Adding indexes to SQLAlchemy models after table creation
问题描述
我有一个flask-sqlalchemy模型:
class MyModel(db.Model):
__tablename__ =' targets'
id = db.Column(db.Integer,primary_key = True)
url = db.Column(db.String(2048))
该表已经创建,并且正在使用中。我想在url属性上创建一个索引,所以我将index = True传递给它:
url = db.Column( db.String(2048),index = True)
如何使此索引生效,删除并重新创建表格?
当我登陆这里时,当前的最佳答案对我没有帮助。在阅读了文档(和源代码)之后,我发现这有用了,希望它有所帮助。
给出原始问题中的模型类。
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()
,也不会创建索引在已创建的表上。
相反,您需要创建一个独立的 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)
如果__name__ =='__ main__':
mymodel_url_index.create(bind = engine)
现在 engine
来自你的sqlalchemy配置,但这段代码应该传达需要发生的事情的要点。 / p>
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))
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?
The current top answer did not help me when I landed here. After reading docs (and source code) I found this to work, hope it helps.
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=True
because even if you called db.Model.metadata.create_all()
the index will not be created on an already created table.
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)
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屋!