If you run Mike's code exactly, do you get the error? If not, can you
edit it to make it more like your code in order to trigger the error?
We need to figure out what is special about your situation before we
can suggest how to fix it.
Simon
On Tue, Oct 9, 2018 at 3:46 PM Alex Rothberg <agrothb...@gmail.com> wrote:
>
> I should say, I didn't run your exact code but essentially that ordering is
> what is causing my issues with my code in that the new fund_title is inserted
> after the new employee.
>
> On Tue, Oct 9, 2018 at 10:44 AM Alex Rothberg <agrothb...@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 ?
>>
>> On Mon, Oct 8, 2018 at 10:20 AM Mike Bayer <mike...@zzzcomputing.com> wrote:
>>>
>>> On Sun, Oct 7, 2018 at 7:11 PM Alex Rothberg <agrothb...@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 a topic in the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/sqlalchemy/fZMJQoI2qkY/unsubscribe.
>>> To unsubscribe from this group and all its topics, 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.
--
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.
