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:
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 <[email protected]
> <javascript:>> 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 <[email protected]
> <javascript:>> wrote:
> >>
> >> On Sun, Oct 7, 2018 at 7:11 PM Alex Rothberg <[email protected]
> <javascript:>> 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 <[email protected]>
> 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 <[email protected]>
> 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 <
> [email protected]> 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 <
> [email protected]> 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 [email protected].
> >> >> > >> > To post to this group, send email to
> [email protected].
> >> >> > >> > 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 [email protected].
> >> >> > > To post to this group, send email to [email protected].
> >> >> > > 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 [email protected].
> >> >> > To post to this group, send email to [email protected].
> >> >> > 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 [email protected] <javascript:>.
> >> > To post to this group, send email to [email protected]
> <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 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
> [email protected] <javascript:>.
> >> To post to this group, send email to [email protected]
> <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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
