On Wed, Oct 10, 2018 at 7:54 PM Alex Rothberg <agrothb...@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. >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> Does using a relationship fix >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> your problem? >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> >> Simon >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > -- >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > 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+...@googlegroups.com. >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > To post to this group, send >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > email to >> > >> >> >> >> >> >> >> >>>> >> >> >> > >> > sqlal...@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+...@googlegroups.com. >> > >> >> >> >> >> >> >> >>>> >> >> >> > > To post to this group, send email >> > >> >> >> >> >> >> >> >>>> >> >> >> > > to sqlal...@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+...@googlegroups.com. >> > >> >> >> >> >> >> >> >>>> >> >> >> > To post to this group, send email to >> > >> >> >> >> >> >> >> >>>> >> >> >> > sqlal...@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. -- 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.