One-to-one relationship not behaving as expected when trying to replace object

[Dane K Barney]

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?

[Mike Bayer]

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()

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ca98e126-33f6-46b6-9f0f-b444b4ff5f4an%40googlegroups.com.

[Dane K Barney]

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?

[Mike Bayer]

yes, do a flush() after removing the old object before applying the new one.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8d03187d-4948-4af4-9146-f91e007cdd82n%40googlegroups.com.