yes, do a flush() after removing the old object before applying the new one.
On Fri, Feb 4, 2022, at 2:17 AM, Dane K Barney wrote: > Thanks for the explanation, Mike. That makes sense and now I know about this > behaviour. > > > you can get your test program to succeed by sending name=None for the > > second B(): > > I realize my example was pretty trivial, but suppose in a real-world example > where table B did not simply have the column 'name', but had a long list of > columns, for which it would be much more cumbersome to have to blank them all > out than it would to simply replace the object with a new instance. > > In that case, what would you suggest as a workaround? Would it be to do an > explicit session.flush() before assigning the new instance to the > relationship? > > On Thursday, February 3, 2022 at 7:15:02 PM UTC-8 Mike Bayer wrote: >> __ >> >> this is the long-expected behavior of the unit of work when issuing a >> delete() and then an add() of two different objects that nonetheless have >> the same primary key value - instead of DELETE and INSERT, you get an >> UPDATE. the reasons have to do with the unit-of-work's ordering of >> INSERT/UPDATE vs. DELETE, the basic idea of which is that it runs all >> INSERT/UPDATES before all DELETES so in one flush() cannot achieve the >> operation you are seeking. The issue at >> https://github.com/sqlalchemy/sqlalchemy/issues/2501 has all the old >> thinking on this as well as some more recent discussion regarding how to >> document this. the problem is usually involving a UNIQUE constraint, >> because I think the version you have here is usually not noticed as the >> UPDATE typically works out. >> >> you can get your test program to succeed by sending name=None for the second >> B(): >> >> a.b = B(name=None) >> session.commit() >> >> >> >> >> On Wed, Feb 2, 2022, at 8:31 PM, Dane K Barney wrote: >>> I have two tables, A and B, that have a one-to-one relationship. Because of >>> this, table B uses the same column as its primary key and the foreign key >>> to table A. >>> >>> For some reason, trying to delete and replace an object of table B is not >>> working as expected. Here is a complete runnable example to demonstrate the >>> problem: >>> >>> import sqlalchemy as sa >>> from sqlalchemy.orm import backref, declarative_base, relationship, >>> sessionmaker >>> >>> Base = declarative_base() >>> >>> class A(Base): >>> __tablename__ = "a" >>> id = sa.Column(sa.Integer, primary_key=True) >>> >>> class B(Base): >>> __tablename__ = "b" >>> id = sa.Column(sa.Integer, sa.ForeignKey("a.id"), primary_key=True) >>> name = sa.Column(sa.String) >>> >>> # 1-to-1 relationship >>> a = relationship("A", backref=backref( >>> "b", >>> uselist=False, >>> # according to the docs, using a combination of delete-orphan >>> # with single_parent=True means that a B object can be deleted >>> # by doing A.b = None >>> cascade="all, delete-orphan", >>> single_parent=True, >>> )) >>> >>> engine = sa.create_engine("sqlite://", echo=True) >>> Base.metadata.create_all(engine) >>> >>> Session = sessionmaker(engine) >>> >>> with Session() as session: >>> a = A() >>> session.add(a) >>> >>> a.b = B(name="foo") >>> session.commit() >>> >>> # replace 'b' with a different object >>> a.b = B() >>> session.commit() >>> >>> print("The value of 'b.name' is: ", a.b.name) >>> >>> I would expect the print statement at the end to say "The value of 'b.name' >>> is None" because a.b was last assigned to B(). But instead it says "The >>> value of 'b.name' is foo". >>> >>> My guess is that SQLAlchemy is getting confused because it uses the primary >>> key to identify an object and technically the primary key of a.b hasn't >>> changed, even though I've replaced it with a completely different object, >>> one that doesn't have a 'name'. >>> >>> If I explicitly add the following 2 lines, then it behaves as expected: >>> >>> a.b = B(value="foo") >>> session.commit() >>> >>> *# forcibly delete a.b* >>> *a.b = None* >>> *session.commit()* >>> >>> # replace 'b' with a different object >>> a.b = B() >>> session.commit() >>> >>> However, this seems like very undesirable behaviour, that I have to force >>> an intermediate deletion step and commit that in order for this to work >>> correctly. >>> >>> I'm guessing I have just missed a step in the setup of my tables. Is there >>> an additional flag I need to put on table B or its relationship to table A >>> to get it behave as expected? >>> >>> >>> -- >>> 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 view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/ca98e126-33f6-46b6-9f0f-b444b4ff5f4an%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/ca98e126-33f6-46b6-9f0f-b444b4ff5f4an%40googlegroups.com?utm_medium=email&utm_source=footer>. >> > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/8d03187d-4948-4af4-9146-f91e007cdd82n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/8d03187d-4948-4af4-9146-f91e007cdd82n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f2224e00-a20c-49c9-83c0-835769874d57%40www.fastmail.com.
