On Friday, October 30, 2015 at 6:02:47 PM UTC-5, r...@rosenfeld.to wrote: > > I would like to temporarily drop a foreign key constraint while loading > data and then revert the constraint's removal when done. I'm hoping to do > this without needing any specific knowledge of the constraints definition > at the time I re-create it. By that I mean something similar to this > fantasy example that's missing a couple key imaginary functions. > > inspector = sqlalchemy.engine.reflection.Inspector.from_engine(engine) > foreign_keys = inspector.get_foreign_keys(table_name) > for foreign_key in foreign_keys: > if foreign_key['name'] == key_name_to_drop: > foreign_key_data_to_recreate = foreign_key > sqlalchemy.schema.DropConstraint(SOME_FUNCTION( > foreign_key_data_to_recreate)) > > # Load the data > > sqlalchemy.schema.AddConstraint(SOME_OTHER_FUNCTION( > foreign_key_data_to_recreate)) > > The above is just one way I imagine it could work. But maybe instead of > `get_foreign_keys` and grabbing the data, I can directly grab the > ForeignKey object of interest to delete from the table and add back later. > > My goal is to not need to re-code the details of the foreign key at the > time I need to reapply it to the table. > > > Thanks much, > Rob >
I've been working on this some more and have figured out something that works. I'm guessing it could be generalized to work with any constraint, but won't work on that until I need it. The metadata I'm using is the one I pass to declarative_base, but I'm not sure it matters. from sqlalchemy.schema import DropConstraint from sqlalchemy.schema import AddConstraint from sqlalchemy import Table class WorkWithoutForeignKey(object): def __init__(self, engine, table_name, foreign_key_name): table = Table(table_name, metadata, autoload=True, autoload_with=engine) for column in table.columns: foreign_keys = column.foreign_keys for foreign_key in foreign_keys: if foreign_key.constraint.name == foreign_key_name: self.foreign_key_constraint = foreign_key.constraint self.connection = engine.connect() def __enter__(self): self.connection.execute(DropConstraint(self.foreign_key_constraint)) def __exit__(self, exc_type, exc_val, exc_tb): self.connection.execute(AddConstraint(self.foreign_key_constraint)) with WorkWithoutForeignKey(engine, 'my_table', 'fk_schema_table_column_foreigncolumn'): # Load the data -- 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. For more options, visit https://groups.google.com/d/optout.