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. 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 > <javascript:>> 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 > <javascript:>> 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 <javascript:>. > > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > > 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 <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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.