Re: [sqlalchemy] Re: Temporarily drop and re-create constraint

[Mike Bayer]

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
> .
> 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.

-- 
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.

[sqlalchemy] Re: Temporarily drop and re-create constraint

[rob]

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.