On 12/10/2015 08:02 AM, Leily Zafari wrote:
> Hello,
> I want to copy some tables from one database to another using
> SQLAlchemy. The tables have circular dependency which is resolved by
> use_alter attribute.
>
>
> class Product(Base):
> __tablename__ = 'products'
> Int_Class_ID = Column(Integer, primary_key=True)
> components = relationship("Component",
> passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
>
> class Component(Base):
> __tablename__ = 'components'
> Int_Class_ID = Column(Integer, primary_key=True)
> product_id = Column(Integer, ForeignKey('products.Int_Class_ID'
> , ondelete='cascade'),nullable=True)
> connection_id =
> Column(Integer,ForeignKey('connections.Int_Class_ID' ,
> ondelete='cascade',use_alter=True,name='connection_component'))
> ports = relationship("Port", passive_deletes=True,cascade =
> "all,delete-orphan",lazy='dynamic' )
> Name = Column(Text)
>
> class Port(Base):
> __tablename__ = 'ports'
> Int_Class_ID = Column(Integer, primary_key=True)
> component_id = Column(Integer,
> ForeignKey('components.Int_Class_ID' , ondelete='cascade'))
> connections = relationship("Connection",
> passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
> Name = Column(Text)
>
> class Connection(Base):
> __tablename__ = 'connections'
> Int_Class_ID = Column(Integer, primary_key=True)
> port_id = Column(Integer, ForeignKey('ports.Int_Class_ID' ,
> ondelete='cascade'))
> components = relationship("Component", post_update=True,
> passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
> Name = Column(Text)
>
>
> When I create tables with their columns and insert the data from the old
> tables to the new tables, I have an IntegrityError, due to ForeignKey
> constraint on the 'Component' table.
> I suppose this is due to the circular dependency. So I tried to drop all
> constraints, migrate the data and then add the constraints. Until the
> data migration is successful, but I do not succeed to add the
> constraints to the tables.
>
>
> table.append_constraint(Constraint)
>
> seems not to affect the database. No SQL commands are emitted.
> What is the best way to copy such tables using SQLAlchemy?
> Any help is appreciated.
OK well you somehow "dropped all constraints", which meant you had to
emit an "ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>"
command, so if you have existing tables and you want to add those
constraints back, you'd need to emit "ALTER TABLE <tablename> ADD
CONSTRAINT <define the constraint>" to do so.
table.append_constraint() only impacts the in-Python definition of the
Table.
If you're looking for helpers that know how to emit the ALTER
statements, you can check out Alembic: http://alembic.readthedocs.org/.
>
>
> --
> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> 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 [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.
