On 10/31/15 5:48 PM, r...@rosenfeld.to wrote: > 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) > forforeign_key inforeign_keys: > ifforeign_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
that approach is fine, you can use reflection and/or the Inspector to get at those foreign key constraints also automatically if you weren't defining them explicitly. > | > > > > > > > > -- > 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 > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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.
