On 05/23/2013 09:06 PM, Mike Bissell 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.
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
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
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
