On 12/12/2015 06:31 PM, Gerald Thibault wrote:
> 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).
It's not clear why actually using the feature that was designed to do
this (e.g. relationship with post_update) is not sufficient. The
SQLAlchemy ORM provides automation of primary key / foreign key
population and querying using the relationship() construct. That's the
API. Getting all kinds of things that relationship() does to work
without actually using the relationship() API seems like a difficult
case to define.
>
>
> 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
>
> <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 <http://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>
>
> >
>
> <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>
> > <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>
> > <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
> <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 https://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.
