[sqlalchemy] Using use_alter=True when doing a table.metadata.create_all()

[Mike Arbelaez]

I'm currently reflecting a few tables from a MSSQL database and then
creating the table structure over to Postgres. I'm using sqlalchemy 0.9 and
python 2.7.

So far I've been very successful doing this with most of the tables except
on a few tables I've received a 'sqlalchemy.exc.CircularDependencyError:
Circular dependency detected.'

I've done some research and it looks like I'll need to do a
'use_alter=True', however, I'm not defining any or foreign keys those are
being picked up by the reflection process.

How would I add this parameter to my 'create_all()' statement. I've
included the basic logic below.


def make_session(connection_string):
engine = create_engine(connection_string, echo=True, convert_unicode=True)
Session = sessionmaker(bind=engine)
return Session(), engine



sengine = 'mssql+pyodbc://User:***@sourcedb'
dengine = 'mssql+pyodbc://User:***@destinationdb'


source, engine = make_session(sengine)
smeta = MetaData(bind=sengine)


destination, dengine = make_session(dengine)


table_name = 'SomeTable'


#Load the table
table = Table(table_name, smeta, autoload=True)

#Create table at new destination
table.metadata.create_all(dengine)

[Michael Bayer]

best way is probably to add it on after the fact. this is the flag but also
because the flag isn’t set up front, seems to need the add/drop directives
to be applied as in
http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html#controlling-ddl-sequences:

from sqlalchemy.schema import AddConstraint, DropConstraint
from sqlalchemy import event

for table in Base.metadata.tables.values():

for fkc in table.foreign_keys:
fkc.use_alter = True

# if not named, then add a name, though yours are reflected so this is already there
fkc.constraint.name = “fk_%s” % table.name # need a better naming scheme here likely

event.listen(Base.metadata, "after_create", AddConstraint(fkc.constraint))
event.listen(Base.metadata, "before_drop", DropConstraint(fkc.constraint))

I'm currently reflecting a few tables from a MSSQL database and then creating the table structure over to Postgres. I'm using sqlalchemy 0.9 and python 2.7.
So far I've been very successful doing this with most of the tables except on a few tables I've received a 'sqlalchemy.exc.CircularDependencyError: Circular dependency detected.'
I've done some research and it looks like I'll need to do a 'use_alter=True', however, I'm not defining any or foreign keys those are being picked up by the reflection process.
How would I add this parameter to my 'create_all()' statement. I've included the basic logic below.
engine = create_engine(connection_string, echo=True, convert_unicode=True)
Session = sessionmaker(bind=engine)
return Session(), engine
source, engine = make_session(sengine)
smeta = MetaData(bind=sengine)
destination, dengine = make_session(dengine)
table_name = 'SomeTable'
#Load the table
table = Table(table_name, smeta, autoload=True)
#Create table at new destination
table.metadata.create_all(dengine)
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

Post by Michael Bayer
best way is probably to add it on after the fact. this is the flag but also
because the flag isn’t set up front, seems to need the add/drop directives
to be applied as in

I wasn’t really happy with this system given the use case here, so for 1.0
I’ve made it such that the use_alter flag is pretty much no longer needed at
least when working with metadata.create_all / drop_all, unless specific
sequences are desired. Cycles between tables are now resolved by breaking
out those specific foreign key constraints when the create is done:

http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#feature-3282

Also the use_alter flag in 1.0 if you were to set it after the fact would be
understood as is without needing to set up those events.

Post by Michael Bayer
from sqlalchemy.schema import AddConstraint, DropConstraint
from sqlalchemy import event
fkc.use_alter = True
# if not named, then add a name, though yours are reflected so this is already there
fkc.constraint.name = “fk_%s” % table.name # need a better naming scheme here likely
event.listen(Base.metadata, "after_create", AddConstraint(fkc.constraint))
event.listen(Base.metadata, "before_drop", DropConstraint(fkc.constraint))

I'm currently reflecting a few tables from a MSSQL database and then creating the table structure over to Postgres. I'm using sqlalchemy 0.9 and python 2.7.
So far I've been very successful doing this with most of the tables except on a few tables I've received a 'sqlalchemy.exc.CircularDependencyError: Circular dependency detected.'
I've done some research and it looks like I'll need to do a 'use_alter=True', however, I'm not defining any or foreign keys those are being picked up by the reflection process.
How would I add this parameter to my 'create_all()' statement. I've included the basic logic below.
engine = create_engine(connection_string, echo=True, convert_unicode=True)
Session = sessionmaker(bind=engine)
return Session(), engine
source, engine = make_session(sengine)
smeta = MetaData(bind=sengine)
destination, dengine = make_session(dengine)
table_name = 'SomeTable'
#Load the table
table = Table(table_name, smeta, autoload=True)
#Create table at new destination
table.metadata.create_all(dengine)
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
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.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[m1yag1]

Mike,

Thanks a lot for the response it really helped. I look forward to 1.0 and
having everything work automatically.

Post by Michael Bayer

Post by Michael Bayer
best way is probably to add it on after the fact. this is the flag but

also

Post by Michael Bayer
because the flag isn’t set up front, seems to need the add/drop

directives

Post by Michael Bayer
to be applied as in

I wasn’t really happy with this system given the use case here, so for 1.0
I’ve made it such that the use_alter flag is pretty much no longer needed
at
least when working with metadata.create_all / drop_all, unless specific
sequences are desired. Cycles between tables are now resolved by breaking
http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#feature-3282
Also the use_alter flag in 1.0 if you were to set it after the fact would be
understood as is without needing to set up those events.

Post by Michael Bayer
from sqlalchemy.schema import AddConstraint, DropConstraint
from sqlalchemy import event
fkc.use_alter = True
# if not named, then add a name, though yours are reflected so

this is already there

Post by Michael Bayer
fkc.constraint.name = “fk_%s” % table.name # need a better

naming scheme here likely

Post by Michael Bayer
event.listen(Base.metadata, "after_create",

AddConstraint(fkc.constraint))

Post by Michael Bayer
event.listen(Base.metadata, "before_drop",

DropConstraint(fkc.constraint))

Post by Michael Bayer

Post by Mike Arbelaez
I'm currently reflecting a few tables from a MSSQL database and then

creating the table structure over to Postgres. I'm using sqlalchemy 0.9 and
python 2.7.

Post by Michael Bayer

Post by Mike Arbelaez
So far I've been very successful doing this with most of the tables

except on a few tables I've received a
'sqlalchemy.exc.CircularDependencyError: Circular dependency detected.'

Post by Michael Bayer

Post by Mike Arbelaez
I've done some research and it looks like I'll need to do a

'use_alter=True', however, I'm not defining any or foreign keys those are
being picked up by the reflection process.

Post by Michael Bayer

Post by Mike Arbelaez
How would I add this parameter to my 'create_all()' statement. I've

included the basic logic below.

Post by Michael Bayer

Post by Mike Arbelaez
engine = create_engine(connection_string, echo=True,

convert_unicode=True)

Post by Michael Bayer

Post by Mike Arbelaez
Session = sessionmaker(bind=engine)
return Session(), engine
source, engine = make_session(sengine)
smeta = MetaData(bind=sengine)
destination, dengine = make_session(dengine)
table_name = 'SomeTable'
#Load the table
table = Table(table_name, smeta, autoload=True)
#Create table at new destination
table.metadata.create_all(dengine)
--
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by Michael Bayer

Post by Mike Arbelaez
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by Michael Bayer

Post by Mike Arbelaez
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.

Post by Michael Bayer
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by Michael Bayer
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

best way is probably to add it on after the fact. this is the flag but also
because the flag isn’t set up front, seems to need the add/drop directives
to be applied as in
http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html#controlling-ddl-sequences:

from sqlalchemy.schema import AddConstraint, DropConstraint
from sqlalchemy import event

for table in Base.metadata.tables.values():

for fkc in table.foreign_keys:
fkc.use_alter = True

# if not named, then add a name, though yours are reflected so this is already there
fkc.constraint.name = “fk_%s” % table.name # need a better naming scheme here likely

event.listen(Base.metadata, "after_create", AddConstraint(fkc.constraint))
event.listen(Base.metadata, "before_drop", DropConstraint(fkc.constraint))

I'm currently reflecting a few tables from a MSSQL database and then creating the table structure over to Postgres. I'm using sqlalchemy 0.9 and python 2.7.
So far I've been very successful doing this with most of the tables except on a few tables I've received a 'sqlalchemy.exc.CircularDependencyError: Circular dependency detected.'
I've done some research and it looks like I'll need to do a 'use_alter=True', however, I'm not defining any or foreign keys those are being picked up by the reflection process.
How would I add this parameter to my 'create_all()' statement. I've included the basic logic below.
engine = create_engine(connection_string, echo=True, convert_unicode=True)
Session = sessionmaker(bind=engine)
return Session(), engine
source, engine = make_session(sengine)
smeta = MetaData(bind=sengine)
destination, dengine = make_session(dengine)
table_name = 'SomeTable'
#Load the table
table = Table(table_name, smeta, autoload=True)
#Create table at new destination
table.metadata.create_all(dengine)
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.