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.
