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/e4620d9f-d8c5-478e-80e5-bec3fac340fb%40www.fastmail.com.
