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.
