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#rows-that-point-to-themselves-mutually-dependent-rows > > <http://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.html#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.
