I ended up with the following code to preprocess the session and collect
the problematic updates into a collection, which I then issue after the
initial flush. It looks really ugly, and I'm wondering if any of this could
be more easily handled via a hook somewhere in SQLA? Particularly the
section where I am generating the list of post_update_keys, I found
references in the source code to 'post_update_cols', and it looked to have
the info I was looking for, but I'm having trouble finding a way to access
it. I tried writing my own DependencyProcessor, but it didn't go so well.
If you could suggest any way to improve this by using SQLA functionality
instead of reinventing the wheel, I'd love to hear it. This works, but it
seems like a lot of code for something that already has a working base
(post_update).
def get_post_update_attrs(cls):
insp = inspect(cls)
rels = [r for r in insp.relationships if r.post_update]
if not rels:
return None
keys = []
for rel in rels:
for c in rel.local_columns:
attr = insp.get_property_by_column(c)
keys.append(attr.key)
return keys
def pk_as_query_filter(obj):
insp = inspect(type(obj))
pk_values = insp.primary_key_from_instance(obj)
if None in pk_values:
return None
pk_keys = [key.name for key in insp.primary_key]
return dict(zip(pk_keys, pk_values))
def get_deferred_updates(session):
deferred = []
post_update_keys = {}
for obj in session:
cls = type(obj)
if cls not in post_update_keys:
post_update_keys[cls] = get_post_update_attrs(cls)
keys = post_update_keys[cls]
if not keys:
continue
pk = pk_as_query_filter(obj)
if pk is None:
continue
update = {}
for key in keys:
update[key] = getattr(obj, key)
setattr(obj, key, None)
deferred.append((cls, pk, update))
return deferred
# this doesn't
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])
deferred = get_deferred_updates(session)
session.flush()
for cls, pk, update in deferred:
session.query(cls).filter_by(**pk).update(update)
session.flush()
On Friday, December 11, 2015 at 5:49:02 PM UTC-8, Michael Bayer wrote:
>
>
>
> 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:>
> <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+...@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.
> > 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.
