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.
