I am loading data from json files, and then creating instances via
ObjClass(**data), loading them into a session, and committing. I do not see
an easy way to go from having an integer foreign_key value to populating
the relationship with the corresponding model in order to have post_update
work.
As an example, a fixture file might look like this:
[
{
"__model__": "Widget",
"widget_id": 1,
"favorite_entry_id": 1,
"name": "somename"
},
{
"__model__": "Entry",
"entry_id": 1,
"widget_id": 1,
"name": "somename"
}
]
The fixture loader deserialiizes the file contents, looks up the __model__
in the SQLA class registry, creates the instance via Model(**data), adds to
the session, and once all instances have been added, it commits. Do you
know a way I could have post_update take effect given the format of the
data that I am starting with? Would I need to introspect the relationships,
determine which ones are post_update, get the remote class of the rel via
introspection, use that to generate the identity_key, then lookup the
object in the active session via the id_key, then assign that, while also
deleting the value of the fk field to prevent it from failing?
It seems super complicated, and I'm looking for the cleanest way to
accomplish my goal. If you have a better idea, I would love to hear it,
because I'm really not happy with the one I've come up with. It works, but
it seems so wasteful.
On Saturday, December 12, 2015 at 6:34:29 PM UTC-8, Michael Bayer wrote:
>
>
>
> 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:> <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:>
> <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 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.
