On Friday, October 30, 2015 at 6:02:47 PM UTC-5, [email protected] 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
