Alternatively, you could make the constraints deferrable, and set them to deferred mode before adding objects with circular dependencies.
> -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:sqlalchemy@googlegroups.com] On Behalf Of Mike Bayer > Sent: 12 December 2015 02:49 > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] How to commit objects with circular FK references? > > > > On 12/11/2015 07:47 PM, Gerald Thibault wrote: > > Is there a way to perform a Metadata.create_all() but have it only > > create the tables, without any of the FKs? And then create the FKs in > > one go after the fixture data has been loaded into the DB? > > first off, this is unnecessary because the DDL system can now create > mutually-dependent foreign key constraints without any use of the > use_alter flag; ALTER TABLE ADD CONSTRAINT is used automatically when a > cycle is detected and this flag is legacy. > > However, if you want to manually control this you can use the rules at > http://docs.sqlalchemy.org/en/latest/core/ddl.html#controlling-ddl- > sequences > to achieve this effect. You'd apply the AddConstraint object to all of the > ForeignKeyConstraint objects and then set them up with events. Here is an > example: > > > from sqlalchemy import Column, MetaData, Integer, create_engine, > ForeignKey, ForeignKeyConstraint from sqlalchemy.ext.declarative import > declarative_base from sqlalchemy import event > > convention = { > "fk": > "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", > } > > metadata = MetaData(naming_convention=convention) > > Base = declarative_base(metadata=metadata) > > > class A(Base): > __tablename__ = 'a' > id = Column(Integer, primary_key=True) > > > class B(Base): > __tablename__ = 'b' > id = Column(Integer, primary_key=True) > a_id = Column(ForeignKey('a.id')) > > from sqlalchemy.schema import AddConstraint, DropConstraint > > for table in Base.metadata.tables.values(): > for constraint in table.constraints: > if isinstance(constraint, ForeignKeyConstraint): > event.listen(Base.metadata, "after_create", > AddConstraint(constraint)) > event.listen(Base.metadata, "before_drop", > DropConstraint(constraint)) > > > e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) > > > Base.metadata.create_all(e) > > Base.metadata.drop_all(e) > > > > > > > > > > On Friday, December 11, 2015 at 3:38:16 PM UTC-8, Michael Bayer wrote: > > > > > > > > On 12/11/2015 05:25 PM, Gerald Thibault wrote: > > > I am basing my question off the code > > > at > > > http://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.html#ro > ws-that-point-to-themselves-mutually-dependent-rows > > > > <http://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.htm > > l#rows-that-point-to-themselves-mutually-dependent-rows>, > > > > > with a few changes. > > > > > > I am trying to handle a situation very similar to the one in that > > > example, with 2 classes having the same relationship types as > > those in > > > the example. However, I would like to create the instances without > > using > > > the relationships, and instead populate the fk values directly. The > > > example uses this, and it works. > > > > > > w1 = Widget(name='somewidget') > > > e1 = Entry(name='someentry') > > > w1.favorite_entry = e1 > > > w1.entries = [e1] > > > session.add_all([w1, e1]) > > > session.commit() > > > > > > I would like to do this: > > > > > > w1 = Widget(widget_id=1, favorite_entry_id=1, name='somewidget') > > > e1 = Entry(entry_id=1, widget_id=1, name='someentry') > > > session.add_all([w1, e1]) > > > session.commit() > > > > > > The reason I am doing it this way is because I am operating from a > > JSON > > > fixture file, and trying to populate a database for unit testing. The > > > method used in the example works perfectly, but trying to do it my > > way > > > yields: > > > > > > sqlalchemy.exc.IntegrityError: (IntegrityError) (1452, 'Cannot add or > > > update a child row: a foreign key constraint fails (`test`.`widget`, > > > CONSTRAINT `fk_favorite_entry` FOREIGN KEY (`favorite_entry_id`) > > > REFERENCES `entry` (`entry_id`))') 'INSERT INTO widget (widget_id, > > > favorite_entry_id, name) VALUES (%s, %s, %s)' (1, 1, 'somewidget') > > > > > > I understand the post_update option is on the relationship, and > > not the > > > column, so it has no effect on column population. Is there an > > > alternative method to have that column populated separately via a > > second > > > statement, similar to the post_update functionality? > > > > Sure, you have to do it manually: > > > > w1 = Widget(widget_id=1, favorite_entry_id=None, > name='somewidget') > > e1 = Entry(entry_id=1, widget_id=1, name='someentry') > > session.add_all([w1, e1]) > > session.flush() # optional if you're on autoflush > > > > session.query(Widget).filter_by(widget_id=1).update(favorite_entry_id= > > 1) > > > > session.commit() > > > > > > > > > > > > > > > > > > > > -- > > > 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+...@googlegroups.com <javascript:> > > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > > To post to this group, send email to sqlal...@googlegroups.com > > <javascript:> > > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > > Visit this group at http://groups.google.com/group/sqlalchemy > > <http://groups.google.com/group/sqlalchemy>. > > > For more options, visit https://groups.google.com/d/optout > > <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 > > <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 https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -------------------------------------------------------------------------------- NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies; do not disclose, use or act upon the information; and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers. If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing. -- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
