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