Creating an index if it doesn't exist

[Mike Bissell]

How might I convince SQLAlchemy 0.7.9 to create a newly added index on a table in the event that the index doesn't already exist? This new index is created as a member of __table_args__; it is not instantiated with index=True. I have many such index objects to create.

Calling create_all() doesn't appear to issue CREATEs for a table if it already exists. I think that's because the optional checkfirst parameter defaults to True.

Setting checkfirst=False breaks because trying to create an already existing DB object is an error, and most of my DB already exists:

ModelBase.metadata.create_all(checkfirst=False)
ProgrammingError: (ProgrammingError) relation "organism" already exists
'\nCREATE TABLE organism (\n\tid SERIAL NOT NULL, \n\tname VARCHAR NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}


I did check stackoverflow, and their unsatisfactory advice was simply to cut and paste the generated DDL:

http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation


In a perfect world, I'd like a way to drop a new index onto a column with the assurance that SQLAlchemy will create it as needed. In my case, this DB is just a big testing data set, so there is no need for formal migrations.

If this feature doesn't exist, I would settle for a function that would take a table and conditionally create any missing pieces (specifically indexes).

As a last resort, is the correct way to do this simply to call Index.create for each index I make?

MB

[Mariano Mara]

I would suggest event.listen with a DDL event:

1- first a routine to check if the objects exists (vendors providing the
"create if not exists" idiom could help here, too):

def should_create(ddl, target, connection, state, **kw):
"""http://www.sqlalchemy.org/docs/core/schema.html"""
row = connection.execute("select relname from pg_class where "
"relname='{0}'".format(state)).scalar()
return not bool(row)

2- then an event that will be execute when the time is right:

event.listen(Base.metadata, "after_create", DDL("create index
lower_value_ix on
dummy_table(lower(value))".execute_if(callable_=should_create,
state="lower_value_ix"))


These chapters should be of help:

http://docs.sqlalchemy.org/en/rel_0_7/core/events.html?highlight=after_create#sqlalchemy.events.DDLEvents.after_create

http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#customizing-ddl

[Michael Bayer]

On May 23, 2013, at 8:06 PM, Mike Bissell <bis...@amyris.com> wrote:

> How might I convince SQLAlchemy 0.7.9 to create a newly added index on a table in the event that the index doesn't already exist? This new index is created as a member of __table_args__; it is not instantiated with index=True. I have many such index objects to create.

an Index has a create() method for single creates.

>
> I did check stackoverflow, and their unsatisfactory advice was simply to cut and paste the generated DDL:
>
> http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation

that answer is a disaster and I've added my own answer to that question, and I would imagine it threw you off the path here.

> If this feature doesn't exist, I would settle for a function that would take a table and conditionally create any missing pieces (specifically indexes).
>
> As a last resort, is the correct way to do this simply to call Index.create for each index I make?

OK confused, you apparently know about index.create(), so what exactly is the feature you're looking for as far as conditionally creating missing pieces ? Other kinds of "missing pieces" like constraints use the AddConstraint() construct,plenty of detail on that here: http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#controlling-ddl-sequences , note however we don't support "checking" for individual table constraints right now (indexes are a separate concern).