On Thu, Oct 11, 2018 at 4:32 PM Alex Rothberg <agrothb...@gmail.com> wrote:
>>
>> given how this model is, I would think you would want just all normal
>> relationships and whichever one you happen to mutate is the one that
>> sets the foreign keys. because you might want to set
>> Employee.department alone or Employee.title which gives you department
>> also. "overlaps" here might want to actually assert the two FK
>> settings aren't conflicting. Otherwise if you set
>> Employee.department = d1 and Employee.title =Title(department=d2),
>> it's random which one "wins".
>
> So the issue comes up when setting any of the relationships to None. For
> example if I cease to have all of fund, department and title, then the
> FundTitle is None. If i assign that to the Employee it then clears all of the
> other (overlapping) fks.
that's where I think the use case is here. but within the ORM it's
better to consider it in terms of overlapping relationships and let it
figure out the FK aspect of it. it's an unusual case but there
should be support for this (at some point :) )
>
> On Wednesday, October 10, 2018 at 8:28:39 PM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Oct 10, 2018 at 7:54 PM Alex Rothberg <agrot...@gmail.com> wrote:
>> >
>> > I'm not totally sure how "overlaps" are used in that example, but yes that
>> > might be fine to have viewonly=False (ie default) and then mark what is
>> > and isn't overlapped.
>> >
>> > So here is the full model with some color:
>> >
>> > Employee (all nullable [slight change from example above]):
>> > - department_id
>> > - title_id
>> > - fund_id
>> >
>> > with the fks as:
>> > department_id -> Department
>> > fund_id -> Fund
>> > (department_id, title_id) -> Title
>> > (department_id, fund_id) -> FundDepartment # not shown in code snipped
>> > earlier, but I also have this too ;-)
>> > (department_id, title_id, fund_id) -> FundTitle
>> >
>> > relationships setup the best I can to avoid overlaps, etc.
>> >
>> >
>> > An employee may have just a fund assigned, just a department, a department
>> > and a title, a department and fund or a department, title and a fund.
>>
>> so...the columns are all nullable and that means the Employee should
>> be flushable before the FundTitle?
>>
>>
>> > Further I want to keep track of the department_id on the title (ie a title
>> > belongs to a department). I want to make sure that the department_id on
>> > the employee matches the department_id on the title, hence the
>> > potentially extraneous composite fk (ie I could just fk from Employee to
>> > title but then there is no constraint that the department matches; an fk
>> > from the title to department does not ensure that). I actually use this
>> > pattern quite a bit with tenancy throughout my models (ie where I use a
>> > composite fk of the standard pk + the tenent to ensure at the db level
>> > that the tenant matches between the two models).>
>> > Let met know if something seems totally silly here!
>>
>> given how this model is, I would think you would want just all normal
>> relationships and whichever one you happen to mutate is the one that
>> sets the foreign keys. because you might want to set
>> Employee.department alone or Employee.title which gives you department
>> also. "overlaps" here might want to actually assert the two FK
>> settings aren't conflicting. Otherwise if you set
>> Employee.department = d1 and Employee.title =Title(department=d2),
>> it's random which one "wins".
>>
>> this is not a use case that's ever been considered.
>>
>>
>>
>>
>> >
>> > On Wednesday, October 10, 2018 at 6:12:59 PM UTC-4, Mike Bayer wrote:
>> >>
>> >> for example why don't we like just using plain relationship() without
>> >> the viewonly=True? Shouldn't you be explicitly associating FundTitle
>> >> with Employee in any case? that is:
>> >>
>> >> class Employee(Base):
>> >> __tablename__ = 'employee'
>> >> id = Column(Integer, primary_key=True)
>> >> title_id = Column(ForeignKey('title.id'), nullable=False)
>> >> department_id = Column(ForeignKey('department.id'), nullable=False)
>> >> fund_id = Column(ForeignKey('fund.id'), nullable=False)
>> >>
>> >> department = relationship(lambda: Department)
>> >> title = relationship("Title")
>> >> fund = relationship("Fund")
>> >>
>> >> fund_title = relationship(FundTitle)
>> >>
>> >> __table_args__ = (
>> >> ForeignKeyConstraint(
>> >> (title_id, department_id, fund_id),
>> >> (FundTitle.title_id, FundTitle.department_id,
>> >> FundTitle.fund_id)
>> >> ),
>> >> )
>> >>
>> >>
>> >> and then:
>> >>
>> >> for i in range(5):
>> >> d1 = Department()
>> >> t1 = Title(department=d1)
>> >> f1 = Fund(department=d1, title=t1)
>> >> ft1 = FundTitle(title=t1, department=d1, fund=f1)
>> >>
>> >> s.add_all([d1, t1, f1, ft1])
>> >>
>> >> e1 = Employee(title=t1, department=d1, fund=f1, fund_title=ft1)
>> >>
>> >> there's still the warning you don't like, but then at least we can
>> >> make an optoin that is narrower in scope:
>> >>
>> >> fund_title = relationship(
>> >> FundTitle, overlaps=('department', 'title', 'fund'))
>> >>
>> >> e.g. we aren't saying viewonly=True but then still having the
>> >> relationship be related to the flush, nor are we making the claim that
>> >> fund_title doesn't populate the department_id, title_id, fund_id
>> >> columns because that seems to contradict what the relationship is
>> >> supposed to do. at least with "overlaps" the intent of what you are
>> >> trying to do is clearer. but im not really sure, because I'm still
>> >> not feeling like I fully understand the model you have. normally
>> >> you'd have employee->fundtitle as the FK, and you would *not* have a
>> >> foreign key from Employee to Department, Title, Fund individually.
>> >> it would be like this:
>> >>
>> >> class Employee(Base):
>> >> __tablename__ = 'employee'
>> >> id = Column(Integer, primary_key=True)
>> >> title_id = Column(nullable=False)
>> >> department_id = Column(nullable=False)
>> >> fund_id = Column(nullable=False)
>> >>
>> >> department = association_proxy("fund_title", "department")
>> >> title = association_proxy("fund_title", "title")
>> >> fund = association_proxy("fund_title", "fund")
>> >>
>> >> fund_title = relationship(FundTitle)
>> >>
>> >> __table_args__ = (
>> >> ForeignKeyConstraint(
>> >> (title_id, department_id, fund_id),
>> >> (FundTitle.title_id, FundTitle.department_id,
>> >> FundTitle.fund_id)
>> >> ),
>> >> )
>> >>
>> >>
>> >> ft1 = FundTitle(title=t1, department=d1, fund=f1)
>> >> e1 = Employee(fund_title=ft1)
>> >>
>> >> e.g. a simple association object pattern. I don't see what the
>> >> redundant foreign keys solves.
>> >>
>> >>
>> >>
>> >>
>> >> On Wed, Oct 10, 2018 at 5:48 PM Mike Bayer <mik...@zzzcomputing.com>
>> >> wrote:
>> >> >
>> >> > On Wed, Oct 10, 2018 at 5:22 PM Alex Rothberg <agrot...@gmail.com>
>> >> > wrote:
>> >> > >
>> >> > > I think so, yes.
>> >> > >
>> >> > > I am open to other ideas, but that seems like the cleanest way to
>> >> > > model the dep without having to add dummy stuff to the model.
>> >> >
>> >> >
>> >> > OK so like some other issues you've raised in the past few days, this
>> >> > is a nice to have but isn't high priority for me right now, since the
>> >> > ORM can already do what you need and designing a good API for this new
>> >> > feature may be difficult to get right. It's often very tough to
>> >> > add support for a new use case without it being too arbitrary and
>> >> > specific to exactly what someone needs at the moment. So the bigger
>> >> > pattern of how you have these foreign keys set up and what it means to
>> >> > be overlapping them like that needs to be understood more canonically.
>> >> >
>> >> >
>> >> >
>> >> > >
>> >> > > On Wednesday, October 10, 2018 at 5:20:51 PM UTC-4, Mike Bayer wrote:
>> >> > >>
>> >> > >> so you want some kind of flag on relationship() that explicitly
>> >> > >> states
>> >> > >> this purpose of the relationship(), which can co-exist with
>> >> > >> viewonly=True such that it doesn't write data, but still represents a
>> >> > >> record that is understood to be created in some other part of the
>> >> > >> transaction. like enforce_dependency=True.
>> >> > >> On Wed, Oct 10, 2018 at 3:50 PM Alex Rothberg <agrot...@gmail.com>
>> >> > >> wrote:
>> >> > >> >
>> >> > >> > Yes, I am suggesting modifying the library such that I can specify
>> >> > >> > a flush dependency ideally without needing to add dummy
>> >> > >> > relationships or use what I am guessing is a pretty low level
>> >> > >> > feature of the ORM (@event.listens_for(Session, "before_flush")).
>> >> > >> > I agree that I can ignore the warnings.
>> >> > >> >
>> >> > >> > On Wednesday, October 10, 2018 at 3:07:24 PM UTC-4, Mike Bayer
>> >> > >> > wrote:
>> >> > >> >>
>> >> > >> >> On Wed, Oct 10, 2018 at 2:57 PM Alex Rothberg
>> >> > >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >
>> >> > >> >> > I actually have that newest warning about "'passive_deletes' is
>> >> > >> >> > normally configured on..." coming up in quite a few places in
>> >> > >> >> > my codebase, In those cases I had added passive_deletes for the
>> >> > >> >> > same reason as here: to avoid a load in the case of a delete.
>> >> > >> >> > In some / many of those other places, I don't have overlapping
>> >> > >> >> > fks.
>> >> > >> >> >
>> >> > >> >> > I guess to make my earlier point more clear: is there any
>> >> > >> >> > chance we could add a flag, etc allowing a viewonly=False but
>> >> > >> >> > where all of the fks are not set by relationship?
>> >> > >> >>
>> >> > >> >> If we are talking about changing the library, then we should work
>> >> > >> >> on
>> >> > >> >> solving use cases, not allowing for hacks to not raise warnings.
>> >> > >> >>
>> >> > >> >>
>> >> > >> >> >
>> >> > >> >> > On Wednesday, October 10, 2018 at 2:54:18 PM UTC-4, Mike Bayer
>> >> > >> >> > wrote:
>> >> > >> >> >>
>> >> > >> >> >> the warnings here are really just to prevent a whole set of
>> >> > >> >> >> very
>> >> > >> >> >> common mistakes - if the usage was never valid, then it would
>> >> > >> >> >> be
>> >> > >> >> >> raising an error. You're doing something that nobody ever
>> >> > >> >> >> does which
>> >> > >> >> >> is have two foreign keys on the same columns, so none of these
>> >> > >> >> >> warnings apply. i dont know what other approach SQLAlchemy
>> >> > >> >> >> could
>> >> > >> >> >> have for this kind of thing.
>> >> > >> >> >>
>> >> > >> >> >>
>> >> > >> >> >>
>> >> > >> >> >> On Wed, Oct 10, 2018 at 2:08 PM Alex Rothberg
>> >> > >> >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >
>> >> > >> >> >> > Adding the passive delete fixes the raise load but adds yet
>> >> > >> >> >> > another warning from sqla:
>> >> > >> >> >> >
>> >> > >> >> >> > sqlalchemy/orm/relationships.py:1790: SAWarning: On
>> >> > >> >> >> > Employee._ft_for_dependency, 'passive_deletes' is normally
>> >> > >> >> >> > configured on one-to-many, one-to-one, many-to-many
>> >> > >> >> >> > relationships only.
>> >> > >> >> >> >
>> >> > >> >> >> > Looking at this:
>> >> > >> >> >> > @event.listens_for(Session, "before_flush")
>> >> > >> >> >> > def _add_dep(session, context, objects):
>> >> > >> >> >> > context.dependencies.update([
>> >> > >> >> >> > (
>> >> > >> >> >> > unitofwork.SaveUpdateAll(context,
>> >> > >> >> >> > inspect(FundTitle)),
>> >> > >> >> >> > unitofwork.SaveUpdateAll(context,
>> >> > >> >> >> > inspect(Employee))
>> >> > >> >> >> > )
>> >> > >> >> >> > ])
>> >> > >> >> >> >
>> >> > >> >> >> > do I not have to mark one Model as dependent on the other?
>> >> > >> >> >> > Or is that implied by the order of the list?
>> >> > >> >> >> >
>> >> > >> >> >> > On Wednesday, October 10, 2018 at 1:36:09 PM UTC-4, Mike
>> >> > >> >> >> > Bayer wrote:
>> >> > >> >> >> >>
>> >> > >> >> >> >> On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg
>> >> > >> >> >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > Well the other way doesn't quite work as if I mark none
>> >> > >> >> >> >> > of the columns as foreign in the primary join, sqla then
>> >> > >> >> >> >> > assumes / guesses all of them are.
>> >> > >> >> >> >>
>> >> > >> >> >> >> that is the case, that is code that has changed a lot over
>> >> > >> >> >> >> the years
>> >> > >> >> >> >> so it has a lot of baggage.
>> >> > >> >> >> >>
>> >> > >> >> >> >>
>> >> > >> >> >> >>
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > Let me test with passive.
>> >> > >> >> >> >> >
>> >> > >> >> >> >> > On Wed, Oct 10, 2018, 13:30 Mike Bayer
>> >> > >> >> >> >> > <mik...@zzzcomputing.com> wrote:
>> >> > >> >> >> >> >>
>> >> > >> >> >> >> >> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg
>> >> > >> >> >> >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >
>> >> > >> >> >> >> >> > And I'll reiterate, not worth doing it all from the
>> >> > >> >> >> >> >> > original single relationship (ie not needing to either
>> >> > >> >> >> >> >> > add more relationships, have warnings or use the more
>> >> > >> >> >> >> >> > obscure feature you outlined)? Seems like that would
>> >> > >> >> >> >> >> > be cleaner in code.
>> >> > >> >> >> >> >>
>> >> > >> >> >> >> >> you mean take the viewonly=True off the existing
>> >> > >> >> >> >> >> relationship? sure
>> >> > >> >> >> >> >> you can do that. but if you mutate the elements in that
>> >> > >> >> >> >> >> collection,
>> >> > >> >> >> >> >> you can incur a change that is conflicting with the
>> >> > >> >> >> >> >> other objects.
>> >> > >> >> >> >> >> that's why I suggested making the non-viewonly a private
>> >> > >> >> >> >> >> member, but
>> >> > >> >> >> >> >> either way works.
>> >> > >> >> >> >> >>
>> >> > >> >> >> >> >>
>> >> > >> >> >> >> >> >
>> >> > >> >> >> >> >> > On Wed, Oct 10, 2018, 13:17 Mike Bayer
>> >> > >> >> >> >> >> > <mik...@zzzcomputing.com> wrote:
>> >> > >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> the raise load issue is because without
>> >> > >> >> >> >> >> >> passive_deletes, it has to
>> >> > >> >> >> >> >> >> load the collection to make sure those objects are
>> >> > >> >> >> >> >> >> all updated.
>> >> > >> >> >> >> >> >> passive_deletes fixes, now you just have a warning.
>> >> > >> >> >> >> >> >> or use the unit
>> >> > >> >> >> >> >> >> of work recipe which is more direct.
>> >> > >> >> >> >> >> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg
>> >> > >> >> >> >> >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >
>> >> > >> >> >> >> >> >> > Not just for warning. Also this raise load issue.
>> >> > >> >> >> >> >> >> > yes, i see that I can't mark none. If I could
>> >> > >> >> >> >> >> >> > though, that would be awesome since I think it
>> >> > >> >> >> >> >> >> > would solve this problem? I can test by setting one
>> >> > >> >> >> >> >> >> > foreign and seeing if that works.
>> >> > >> >> >> >> >> >> >
>> >> > >> >> >> >> >> >> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4,
>> >> > >> >> >> >> >> >> > Mike Bayer wrote:
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg
>> >> > >> >> >> >> >> >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >> >
>> >> > >> >> >> >> >> >> >> > let me get that. in the meantime, what are your
>> >> > >> >> >> >> >> >> >> > thoughts on just removing the view only from the
>> >> > >> >> >> >> >> >> >> > original relationship and then using an explicit
>> >> > >> >> >> >> >> >> >> > primary join where none of the columns are
>> >> > >> >> >> >> >> >> >> > marked foreign? Theoretically that should solve
>> >> > >> >> >> >> >> >> >> > this problem, no?
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> is this just for the warning? I don't think the
>> >> > >> >> >> >> >> >> >> relationship() can
>> >> > >> >> >> >> >> >> >> be set up with no columns marked as foreign, it
>> >> > >> >> >> >> >> >> >> takes that as a cue
>> >> > >> >> >> >> >> >> >> that it should figure out the "foreign" columns on
>> >> > >> >> >> >> >> >> >> its own.
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> There's another way to make sure Employee is
>> >> > >> >> >> >> >> >> >> always dependent on
>> >> > >> >> >> >> >> >> >> FundTitle but it's a little bit off-label. Add
>> >> > >> >> >> >> >> >> >> the dependency you
>> >> > >> >> >> >> >> >> >> want directly into the unit of work:
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> from sqlalchemy.orm import unitofwork
>> >> > >> >> >> >> >> >> >> from sqlalchemy import event
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> @event.listens_for(Session, "before_flush")
>> >> > >> >> >> >> >> >> >> def _add_dep(session, context, objects):
>> >> > >> >> >> >> >> >> >> context.dependencies.update([
>> >> > >> >> >> >> >> >> >> (
>> >> > >> >> >> >> >> >> >> unitofwork.SaveUpdateAll(context,
>> >> > >> >> >> >> >> >> >> inspect(FundTitle)),
>> >> > >> >> >> >> >> >> >> unitofwork.SaveUpdateAll(context,
>> >> > >> >> >> >> >> >> >> inspect(Employee))
>> >> > >> >> >> >> >> >> >> )
>> >> > >> >> >> >> >> >> >> ])
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> >
>> >> > >> >> >> >> >> >> >> > On Wednesday, October 10, 2018 at 12:41:25 PM
>> >> > >> >> >> >> >> >> >> > UTC-4, Alex Rothberg wrote:
>> >> > >> >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> >> Is it possible to specific a non viewonly
>> >> > >> >> >> >> >> >> >> >> relationship in which I have a primary join
>> >> > >> >> >> >> >> >> >> >> specified in which none of the fk's are marked
>> >> > >> >> >> >> >> >> >> >> "foreign"? ie where I can mark the relationship
>> >> > >> >> >> >> >> >> >> >> dependancy but it wont set any columns? It
>> >> > >> >> >> >> >> >> >> >> looks like there may be some logic in sqla that
>> >> > >> >> >> >> >> >> >> >> assume all columns are fk if none are specified
>> >> > >> >> >> >> >> >> >> >> as foreign?
>> >> > >> >> >> >> >> >> >> >>
>> >> > >> >> >> >> >> >> >> >> On Wednesday, October 10, 2018 at 11:56:49 AM
>> >> > >> >> >> >> >> >> >> >> UTC-4, Alex Rothberg wrote:
>> >> > >> >> >> >> >> >> >> >>>
>> >> > >> >> >> >> >> >> >> >>> So one minor issue and one big issue with that
>> >> > >> >> >> >> >> >> >> >>> solution:
>> >> > >> >> >> >> >> >> >> >>> 1. minor issue, I now get these: SAWarning:
>> >> > >> >> >> >> >> >> >> >>> relationship 'XXXX' will copy columnYYYY to
>> >> > >> >> >> >> >> >> >> >>> column ZZZZ, which conflicts with
>> >> > >> >> >> >> >> >> >> >>> relationship(s): '....
>> >> > >> >> >> >> >> >> >> >>> 2. major issue, I use raiseload("*") and now I
>> >> > >> >> >> >> >> >> >> >>> start seeing:
>> >> > >> >> >> >> >> >> >> >>> sqlalchemy.exc.InvalidRequestError:
>> >> > >> >> >> >> >> >> >> >>> 'Employee._ft_for_dependency' is not available
>> >> > >> >> >> >> >> >> >> >>> due to lazy='raise'
>> >> > >> >> >> >> >> >> >> >>>
>> >> > >> >> >> >> >> >> >> >>> On Wednesday, October 10, 2018 at 9:57:55 AM
>> >> > >> >> >> >> >> >> >> >>> UTC-4, Mike Bayer wrote:
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg
>> >> > >> >> >> >> >> >> >> >>>> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > Okay with some small tweaks to your
>> >> > >> >> >> >> >> >> >> >>>> > original code, I am able to show the issue
>> >> > >> >> >> >> >> >> >> >>>> > I am having. comment out flush to see issue:
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> so what you're doing here is making Employee
>> >> > >> >> >> >> >> >> >> >>>> dependent on FundTitle,
>> >> > >> >> >> >> >> >> >> >>>> which makes this a little out of the ordinary
>> >> > >> >> >> >> >> >> >> >>>> but this is fine. You
>> >> > >> >> >> >> >> >> >> >>>> need to give the ORM a clue that this
>> >> > >> >> >> >> >> >> >> >>>> dependency exists, since it
>> >> > >> >> >> >> >> >> >> >>>> never looks at foreign key constraints unless
>> >> > >> >> >> >> >> >> >> >>>> you tell it to.
>> >> > >> >> >> >> >> >> >> >>>> Adding a relationship to FundTitle that
>> >> > >> >> >> >> >> >> >> >>>> doesn't have viewonly=True is
>> >> > >> >> >> >> >> >> >> >>>> an easy way to do this, there's no need to
>> >> > >> >> >> >> >> >> >> >>>> ever make use of the
>> >> > >> >> >> >> >> >> >> >>>> relationship otherwise:
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> class Employee(Base):
>> >> > >> >> >> >> >> >> >> >>>> __tablename__ = 'employee'
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> # ...
>> >> > >> >> >> >> >> >> >> >>>> fund_title = relationship(FundTitle,
>> >> > >> >> >> >> >> >> >> >>>> viewonly=True)
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> _ft_for_dependency =
>> >> > >> >> >> >> >> >> >> >>>> relationship(FundTitle)
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> __table_args__ = (
>> >> > >> >> >> >> >> >> >> >>>> ForeignKeyConstraint(
>> >> > >> >> >> >> >> >> >> >>>> (title_id, department_id,
>> >> > >> >> >> >> >> >> >> >>>> fund_id),
>> >> > >> >> >> >> >> >> >> >>>> (FundTitle.title_id,
>> >> > >> >> >> >> >> >> >> >>>> FundTitle.department_id, FundTitle.fund_id)
>> >> > >> >> >> >> >> >> >> >>>> ),
>> >> > >> >> >> >> >> >> >> >>>> )
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> then you can take the flush() out and there's
>> >> > >> >> >> >> >> >> >> >>>> no issue, as long as
>> >> > >> >> >> >> >> >> >> >>>> you're always making sure that FundTitle
>> >> > >> >> >> >> >> >> >> >>>> object is present either in
>> >> > >> >> >> >> >> >> >> >>>> the current Session or the row in the
>> >> > >> >> >> >> >> >> >> >>>> database exists.
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>>
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > from sqlalchemy import *
>> >> > >> >> >> >> >> >> >> >>>> > from sqlalchemy.orm import *
>> >> > >> >> >> >> >> >> >> >>>> > from sqlalchemy.ext.declarative import
>> >> > >> >> >> >> >> >> >> >>>> > declarative_base
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > Base = declarative_base()
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > class Title(Base):
>> >> > >> >> >> >> >> >> >> >>>> > __tablename__ = 'title'
>> >> > >> >> >> >> >> >> >> >>>> > id = Column(Integer, primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> > department_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > department = relationship(lambda:
>> >> > >> >> >> >> >> >> >> >>>> > Department)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > class Department(Base):
>> >> > >> >> >> >> >> >> >> >>>> > __tablename__ = 'department'
>> >> > >> >> >> >> >> >> >> >>>> > id = Column(Integer, primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > class Fund(Base):
>> >> > >> >> >> >> >> >> >> >>>> > __tablename__ = 'fund'
>> >> > >> >> >> >> >> >> >> >>>> > id = Column(Integer, primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> > title_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('title.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > department_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > department = relationship("Department")
>> >> > >> >> >> >> >> >> >> >>>> > title = relationship("Title")
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > class FundTitle(Base):
>> >> > >> >> >> >> >> >> >> >>>> > __tablename__ = 'fund_title'
>> >> > >> >> >> >> >> >> >> >>>> > id = Column(Integer, primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> > title_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('title.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > department_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > fund_id = Column(ForeignKey('fund.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > department = relationship("Department")
>> >> > >> >> >> >> >> >> >> >>>> > title = relationship("Title")
>> >> > >> >> >> >> >> >> >> >>>> > fund = relationship("Fund")
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > __table_args__ = (
>> >> > >> >> >> >> >> >> >> >>>> > UniqueConstraint(
>> >> > >> >> >> >> >> >> >> >>>> > title_id, department_id, fund_id
>> >> > >> >> >> >> >> >> >> >>>> > ),
>> >> > >> >> >> >> >> >> >> >>>> > )
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > class Employee(Base):
>> >> > >> >> >> >> >> >> >> >>>> > __tablename__ = 'employee'
>> >> > >> >> >> >> >> >> >> >>>> > id = Column(Integer, primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> > title_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('title.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > department_id =
>> >> > >> >> >> >> >> >> >> >>>> > Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> > fund_id = Column(ForeignKey('fund.id'),
>> >> > >> >> >> >> >> >> >> >>>> > nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > department = relationship(lambda:
>> >> > >> >> >> >> >> >> >> >>>> > Department)
>> >> > >> >> >> >> >> >> >> >>>> > title = relationship("Title")
>> >> > >> >> >> >> >> >> >> >>>> > fund = relationship("Fund")
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > fund_title = relationship(FundTitle,
>> >> > >> >> >> >> >> >> >> >>>> > viewonly=True)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > __table_args__ = (
>> >> > >> >> >> >> >> >> >> >>>> > ForeignKeyConstraint(
>> >> > >> >> >> >> >> >> >> >>>> > (title_id, department_id,
>> >> > >> >> >> >> >> >> >> >>>> > fund_id), (FundTitle.title_id,
>> >> > >> >> >> >> >> >> >> >>>> > FundTitle.department_id, FundTitle.fund_id)
>> >> > >> >> >> >> >> >> >> >>>> > ),
>> >> > >> >> >> >> >> >> >> >>>> > )
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > e =
>> >> > >> >> >> >> >> >> >> >>>> > create_engine("postgresql://localhost/test_issue",
>> >> > >> >> >> >> >> >> >> >>>> > echo=False)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > # Base.metadata.drop_all(e)
>> >> > >> >> >> >> >> >> >> >>>> > Base.metadata.create_all(e)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > s = Session(e)
>> >> > >> >> >> >> >> >> >> >>>> > # s.rollback()
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > while True:
>> >> > >> >> >> >> >> >> >> >>>> > d1 = Department()
>> >> > >> >> >> >> >> >> >> >>>> > t1 = Title(department=d1)
>> >> > >> >> >> >> >> >> >> >>>> > f1 = Fund(department=d1, title=t1)
>> >> > >> >> >> >> >> >> >> >>>> > ft1 = FundTitle(title=t1,
>> >> > >> >> >> >> >> >> >> >>>> > department=d1, fund=f1)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > s.add_all([d1, t1, f1, ft1])
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > s.flush()
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > e1 = Employee(title=t1, department=d1,
>> >> > >> >> >> >> >> >> >> >>>> > fund=f1)
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > s.add_all([e1,])
>> >> > >> >> >> >> >> >> >> >>>> > s.commit()
>> >> > >> >> >> >> >> >> >> >>>> >
>> >> > >> >> >> >> >> >> >> >>>> > On Tuesday, October 9, 2018 at 12:20:30 PM
>> >> > >> >> >> >> >> >> >> >>>> > UTC-4, Mike Bayer wrote:
>> >> > >> >> >> >> >> >> >> >>>> >>
>> >> > >> >> >> >> >> >> >> >>>> >> On Tue, Oct 9, 2018 at 10:44 AM Alex
>> >> > >> >> >> >> >> >> >> >>>> >> Rothberg <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> > In looking at what you wrote doesn't
>> >> > >> >> >> >> >> >> >> >>>> >> > this cause an fk violation (it does for
>> >> > >> >> >> >> >> >> >> >>>> >> > me):
>> >> > >> >> >> >> >> >> >> >>>> >> > 2018-10-08 10:18:38,760 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> > sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> > INTO employee (title_id, department_id,
>> >> > >> >> >> >> >> >> >> >>>> >> > fund_id) VALUES (%(title_id)s,
>> >> > >> >> >> >> >> >> >> >>>> >> > %(department_id)s, %(fund_id)s)
>> >> > >> >> >> >> >> >> >> >>>> >> > RETURNING employee.id
>> >> > >> >> >> >> >> >> >> >>>> >> > 2018-10-08 10:18:38,763 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> > sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> > INTO fund_title (title_id,
>> >> > >> >> >> >> >> >> >> >>>> >> > department_id, fund_id) VALUES
>> >> > >> >> >> >> >> >> >> >>>> >> > (%(title_id)s, %(department_id)s,
>> >> > >> >> >> >> >> >> >> >>>> >> > %(fund_id)s) RETURNING fund_title.id
>> >> > >> >> >> >> >> >> >> >>>> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> > in that a a (non deferred) fk is
>> >> > >> >> >> >> >> >> >> >>>> >> > violated between employee and fund_title
>> >> > >> >> >> >> >> >> >> >>>> >> > ?
>> >> > >> >> >> >> >> >> >> >>>> >>
>> >> > >> >> >> >> >> >> >> >>>> >> see we need to see how youve laid out your
>> >> > >> >> >> >> >> >> >> >>>> >> ForeignKeyConstraints, if
>> >> > >> >> >> >> >> >> >> >>>> >> they are composite and overlapping, there
>> >> > >> >> >> >> >> >> >> >>>> >> are additional options that
>> >> > >> >> >> >> >> >> >> >>>> >> may be needed (specifically the
>> >> > >> >> >> >> >> >> >> >>>> >> post_update flag). you'll note I laid
>> >> > >> >> >> >> >> >> >> >>>> >> out all FKs as single column.
>> >> > >> >> >> >> >> >> >> >>>> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> > On Mon, Oct 8, 2018 at 10:20 AM Mike
>> >> > >> >> >> >> >> >> >> >>>> >> > Bayer <mik...@zzzcomputing.com> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> On Sun, Oct 7, 2018 at 7:11 PM Alex
>> >> > >> >> >> >> >> >> >> >>>> >> >> Rothberg <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> > Okay so I investigated / thought
>> >> > >> >> >> >> >> >> >> >>>> >> >> > about this further. The issue is that
>> >> > >> >> >> >> >> >> >> >>>> >> >> > while I do have a relationship
>> >> > >> >> >> >> >> >> >> >>>> >> >> > between the various models, some of
>> >> > >> >> >> >> >> >> >> >>>> >> >> > the relationships are viewonly since
>> >> > >> >> >> >> >> >> >> >>>> >> >> > I have overlapping fks.
>> >> > >> >> >> >> >> >> >> >>>> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> > For example I have a model Employee,
>> >> > >> >> >> >> >> >> >> >>>> >> >> > which has fks: department_id,
>> >> > >> >> >> >> >> >> >> >>>> >> >> > title_id, and fund_id. The related
>> >> > >> >> >> >> >> >> >> >>>> >> >> > models are Department (fk
>> >> > >> >> >> >> >> >> >> >>>> >> >> > department_id), Title (fk
>> >> > >> >> >> >> >> >> >> >>>> >> >> > department_id and title_id) , Fund
>> >> > >> >> >> >> >> >> >> >>>> >> >> > (fk fund_id) and FundTitle (fk
>> >> > >> >> >> >> >> >> >> >>>> >> >> > department_id, title_id and fund_id).
>> >> > >> >> >> >> >> >> >> >>>> >> >> > I have set FundTitle to viewonly.
>> >> > >> >> >> >> >> >> >> >>>> >> >> > When updating / creating an Employee,
>> >> > >> >> >> >> >> >> >> >>>> >> >> > I do create and add a new FundTitle
>> >> > >> >> >> >> >> >> >> >>>> >> >> > to the session, however I don't
>> >> > >> >> >> >> >> >> >> >>>> >> >> > assign it to the employee as the
>> >> > >> >> >> >> >> >> >> >>>> >> >> > relationship is viewonly. If I don't
>> >> > >> >> >> >> >> >> >> >>>> >> >> > flush before making the assignment,
>> >> > >> >> >> >> >> >> >> >>>> >> >> > the final flush / commit attempts to
>> >> > >> >> >> >> >> >> >> >>>> >> >> > update / create the employee before
>> >> > >> >> >> >> >> >> >> >>>> >> >> > creating the FundTitle.
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> let's work with source code that is
>> >> > >> >> >> >> >> >> >> >>>> >> >> runnable (e.g. MCVE). Below is
>> >> > >> >> >> >> >> >> >> >>>> >> >> the model that it seems you are
>> >> > >> >> >> >> >> >> >> >>>> >> >> describing, and then there's a
>> >> > >> >> >> >> >> >> >> >>>> >> >> demonstration of assembly of all those
>> >> > >> >> >> >> >> >> >> >>>> >> >> components using relationships,
>> >> > >> >> >> >> >> >> >> >>>> >> >> a single flush and it all goes in in
>> >> > >> >> >> >> >> >> >> >>>> >> >> the correct order, all FKs are
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False.
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> from sqlalchemy import *
>> >> > >> >> >> >> >> >> >> >>>> >> >> from sqlalchemy.orm import *
>> >> > >> >> >> >> >> >> >> >>>> >> >> from sqlalchemy.ext.declarative import
>> >> > >> >> >> >> >> >> >> >>>> >> >> declarative_base
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> Base = declarative_base()
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> class Employee(Base):
>> >> > >> >> >> >> >> >> >> >>>> >> >> __tablename__ = 'employee'
>> >> > >> >> >> >> >> >> >> >>>> >> >> id = Column(Integer,
>> >> > >> >> >> >> >> >> >> >>>> >> >> primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> >> >> title_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('title.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('fund.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department =
>> >> > >> >> >> >> >> >> >> >>>> >> >> relationship("Department")
>> >> > >> >> >> >> >> >> >> >>>> >> >> title = relationship("Title")
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund = relationship("Fund")
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> class Title(Base):
>> >> > >> >> >> >> >> >> >> >>>> >> >> __tablename__ = 'title'
>> >> > >> >> >> >> >> >> >> >>>> >> >> id = Column(Integer,
>> >> > >> >> >> >> >> >> >> >>>> >> >> primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department =
>> >> > >> >> >> >> >> >> >> >>>> >> >> relationship("Department")
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> class Department(Base):
>> >> > >> >> >> >> >> >> >> >>>> >> >> __tablename__ = 'department'
>> >> > >> >> >> >> >> >> >> >>>> >> >> id = Column(Integer,
>> >> > >> >> >> >> >> >> >> >>>> >> >> primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> class Fund(Base):
>> >> > >> >> >> >> >> >> >> >>>> >> >> __tablename__ = 'fund'
>> >> > >> >> >> >> >> >> >> >>>> >> >> id = Column(Integer,
>> >> > >> >> >> >> >> >> >> >>>> >> >> primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> >> >> title_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('title.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department =
>> >> > >> >> >> >> >> >> >> >>>> >> >> relationship("Department")
>> >> > >> >> >> >> >> >> >> >>>> >> >> title = relationship("Title")
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> class FundTitle(Base):
>> >> > >> >> >> >> >> >> >> >>>> >> >> __tablename__ = 'fund_title'
>> >> > >> >> >> >> >> >> >> >>>> >> >> id = Column(Integer,
>> >> > >> >> >> >> >> >> >> >>>> >> >> primary_key=True)
>> >> > >> >> >> >> >> >> >> >>>> >> >> title_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('title.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('department.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund_id =
>> >> > >> >> >> >> >> >> >> >>>> >> >> Column(ForeignKey('fund.id'),
>> >> > >> >> >> >> >> >> >> >>>> >> >> nullable=False)
>> >> > >> >> >> >> >> >> >> >>>> >> >> department =
>> >> > >> >> >> >> >> >> >> >>>> >> >> relationship("Department")
>> >> > >> >> >> >> >> >> >> >>>> >> >> title = relationship("Title")
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund = relationship("Fund")
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> e =
>> >> > >> >> >> >> >> >> >> >>>> >> >> create_engine("postgresql://scott:tiger@localhost/test",
>> >> > >> >> >> >> >> >> >> >>>> >> >> echo=True)
>> >> > >> >> >> >> >> >> >> >>>> >> >> Base.metadata.create_all(e)
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> s = Session(e)
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> d1 = Department()
>> >> > >> >> >> >> >> >> >> >>>> >> >> t1 = Title(department=d1)
>> >> > >> >> >> >> >> >> >> >>>> >> >> f1 = Fund(department=d1, title=t1)
>> >> > >> >> >> >> >> >> >> >>>> >> >> ft1 = FundTitle(title=t1,
>> >> > >> >> >> >> >> >> >> >>>> >> >> department=d1, fund=f1)
>> >> > >> >> >> >> >> >> >> >>>> >> >> e1 = Employee(title=t1, department=d1,
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund=f1)
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> s.add_all([d1, t1, f1, ft1, e1])
>> >> > >> >> >> >> >> >> >> >>>> >> >> s.commit()
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> the INSERTs can be ordered naturally
>> >> > >> >> >> >> >> >> >> >>>> >> >> here and the unit of work will do
>> >> > >> >> >> >> >> >> >> >>>> >> >> that for you if you use relationship:
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> BEGIN (implicit)
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,750 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> >> INTO
>> >> > >> >> >> >> >> >> >> >>>> >> >> department DEFAULT VALUES RETURNING
>> >> > >> >> >> >> >> >> >> >>>> >> >> department.id
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,750 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine {}
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,753 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> >> INTO
>> >> > >> >> >> >> >> >> >> >>>> >> >> title (department_id) VALUES
>> >> > >> >> >> >> >> >> >> >>>> >> >> (%(department_id)s) RETURNING title.id
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,753 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine
>> >> > >> >> >> >> >> >> >> >>>> >> >> {'department_id': 1}
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,757 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> >> INTO
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund (title_id, department_id) VALUES
>> >> > >> >> >> >> >> >> >> >>>> >> >> (%(title_id)s,
>> >> > >> >> >> >> >> >> >> >>>> >> >> %(department_id)s) RETURNING fund.id
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,757 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine
>> >> > >> >> >> >> >> >> >> >>>> >> >> {'title_id': 1, 'department_id': 1}
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,760 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> >> INTO
>> >> > >> >> >> >> >> >> >> >>>> >> >> employee (title_id, department_id,
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund_id) VALUES (%(title_id)s,
>> >> > >> >> >> >> >> >> >> >>>> >> >> %(department_id)s, %(fund_id)s)
>> >> > >> >> >> >> >> >> >> >>>> >> >> RETURNING employee.id
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,761 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine
>> >> > >> >> >> >> >> >> >> >>>> >> >> {'title_id': 1, 'department_id': 1,
>> >> > >> >> >> >> >> >> >> >>>> >> >> 'fund_id': 1}
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,763 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine INSERT
>> >> > >> >> >> >> >> >> >> >>>> >> >> INTO
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund_title (title_id, department_id,
>> >> > >> >> >> >> >> >> >> >>>> >> >> fund_id) VALUES (%(title_id)s,
>> >> > >> >> >> >> >> >> >> >>>> >> >> %(department_id)s, %(fund_id)s)
>> >> > >> >> >> >> >> >> >> >>>> >> >> RETURNING fund_title.id
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,764 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine
>> >> > >> >> >> >> >> >> >> >>>> >> >> {'title_id': 1, 'department_id': 1,
>> >> > >> >> >> >> >> >> >> >>>> >> >> 'fund_id': 1}
>> >> > >> >> >> >> >> >> >> >>>> >> >> 2018-10-08 10:18:38,766 INFO
>> >> > >> >> >> >> >> >> >> >>>> >> >> sqlalchemy.engine.base.Engine COMMIT
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> > On Tuesday, September 18, 2018 at
>> >> > >> >> >> >> >> >> >> >>>> >> >> > 9:02:30 AM UTC-4, Mike Bayer wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> if there are no dependencies between
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> two particular objects of
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> different classes, say A and B, then
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> there is no deterministic
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> ordering between them. For objects
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> of the same class, they are
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> inserted in the order in which they
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> were added to the Session.
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> the correct way to solve this
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> problem in SQLAlchemy is to use
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> relationship() fully. I know you've
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> stated that these objects have a
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> relationship() between them but you
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> have to actually use it, that is:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> obj_a = A()
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> obj_b = B()
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> obj_a.some_relationship = obj_b #
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> will definitely flush correctly
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> unless there is a bug
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> OTOH if you are only using foreign
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> key attributes, the ORM does *not*
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> have any idea in how it should be
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> flushing these:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> obj_a = A()
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> obj_b = B()
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> obj_a.some_fk = obj_b.some_id #
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> ORM doesn't care about this, no
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> ordering is implied
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> since you said you're not setting
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> any IDs, I'm not sure how you could
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> be doing the above.
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> On Tue, Sep 18, 2018 at 5:53 AM
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> Simon King <si...@simonking.org.uk>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > It's not something I've ever
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > looked into, but I'm not aware of
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > any
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > debugging options here, no. You'd
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > probably want to start by
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > scattering
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > print statements around the
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > UOWTransaction class
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > (https://bitbucket.org/zzzeek/sqlalchemy/src/c94d67892e68ac317d72eb202cca427084b3ca74/lib/sqlalchemy/orm/unitofwork.py?at=master&fileviewer=file-view-default#unitofwork.py-111)
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > Looking at that code made me
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > wonder whether you've set any
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > particular
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > cascade options on your
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > relationship; I'm not sure if
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > cascade options
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > affect the dependency calculation.
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > Simon
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > On Tue, Sep 18, 2018 at 5:28 AM
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > Alex Rothberg <agrot...@gmail.com>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > In order to guide me in
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > stripping down this code to
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > produce an example for positing,
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > are there any options / flags /
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > introspections I can turn on to
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > understand how sql makes
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > decisions about the order in
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > which is writes statements to
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > the DB?
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > On Friday, September 14, 2018 at
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > 10:13:45 AM UTC-4, Simon King
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > > wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> In that case can you show us
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> the code that is causing the
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> problem?
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> On Fri, Sep 14, 2018 at 2:55 PM
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> Alex Rothberg
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > I am not generating any IDs
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > myself and I already have
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > relationships between the
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > models.
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > On Friday, September 14, 2018
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > at 4:33:08 AM UTC-4, Simon
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > King wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> On Thu, Sep 13, 2018 at
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> 10:50 PM Alex Rothberg
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> <agrot...@gmail.com> wrote:
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > Is it possible to hint at
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > sqla the order in which it
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > should write out changes
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > to the DB?
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > I am having issues in
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > which I add two new
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > objects to a session, a
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > and b where a depends on
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > b, but sqla is flushing a
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > before b leading to an fk
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > issue. I can solve this a
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > few ways: explicitly
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > calling flush after adding
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > b, or changing the fk
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > constraint to be initially
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > deferred. Ideally I would
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > not have to do either of
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> > these.
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> >
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >>
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> If you have configured a
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> relationship between the two
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> classes
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> (http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#building-a-relationship),
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> and you've linked the
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> objects together using that
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> relationship (a.b =
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> b), then SQLAlchemy will
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> flush them in the correct
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> order. If you are
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> generating your IDs in
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> Python and assigning them to
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> the primary and
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> foreign key columns
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> directly, SQLAlchemy
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> probably won't understand the
>> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> dependency.
>> >> > >> >> >> >> >> >> >> >>>> >>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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.
