Adding indexes to SQLAlchemy models after table creation

Question

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?

Answer 1

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.