hey there -
you can assign the "id" but that doesn't give SQLAlchemy any clue that you are
working with the "daughter" relationship so it doesn't know to deassociate
m2.daughter. You'll note that relationally, there's no issue as from a foreign
key perspective Mama->daughter is many to one. Some FAQ on this here:
https://docs.sqlalchemy.org/en/13/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7
Usually if you were modelling "Mama->Daughter", you'd have Daughter.mama_id
foreign key since Parent->Chlld is typically one-to-many, not many-to-one.
On Fri, Dec 18, 2020, at 12:50 PM, 'Sören Textor' via sqlalchemy wrote:
> This example fails. Instead of assigning an objekt, I assign just the
> daughters id ...
> But I think that's "correct"?
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class Super(Base):
> __tablename__ = "super"
> id = Column(Integer, primary_key=True)
>
>
> class Mama(Super):
> __tablename__ = "mama"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>
> daughter_id = Column(Integer, ForeignKey("daughter.id"))
> daughter = relationship("Daughter", foreign_keys=[daughter_id],
> back_populates="mama" )
>
>
> class Daughter(Super):
> __tablename__ = "daughter"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
> mama = relationship( "Mama", foreign_keys=[Mama.daughter_id],
> uselist=False, back_populates="daughter", )
>
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> session = Session(e)
>
> m1 = Mama()
> m2 = Mama()
> d1 = Daughter()
> d2 = Daughter()
>
> session.add(m1)
> session.add(m2)
> session.add(d1)
> session.add(d2)
> session.commit()
>
> m1.daughter = d1
> m2.daughter = d2
> session.commit()
>
> m1.daughter_id = d2.id #instead of m1.daughter = d2
> session.commit()
>
> assert m1.daughter is d2
> assert m2.daughter is None # FAILS
> assert m2.daughter_id is None #FAILS
>
> SirAnn
> ------ Originalnachricht ------
> Von: "Sören Textor" <[email protected]>
> An: "[email protected]" <[email protected]>
> Gesendet: 18.12.2020 16:52:35
> Betreff: Re: [sqlalchemy] One to One relation problem
>
>> Hi Mike.
>> Thanks for answering. I‘ll check it out on monday.
>> We use MSSQL2016 and flask. That‘s the only difference I see at the first
>> look.
>>
>> I‘ll send a detailed answer to the group afterwards. Without the super class
>> it also works fine. That‘s why I thought it is an issue with foreign keys.
>>
>> We are running the newest 1.3.x of SQLAlchemy.
>>
>> Mike Bayer <[email protected]> schrieb am Fr. 18. Dez. 2020 um 16:31:
>>> __
>>> hey there -
>>>
>>> these mappings are pretty good, as is always the case I cannot predict why
>>> an issue is occurring, or usually even understand the issue, without
>>> running the code. your code is pretty runnable with a few imports added so
>>> that's great. however adding an assertion for the condition you describe
>>> "m2.daughter is not None" is not reproducible on my end. Try out the
>>> script below and see if you have different results.
>>>
>>>
>>> from sqlalchemy import Column
>>> from sqlalchemy import create_engine
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy import Integer
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import relationship
>>> from sqlalchemy.orm import Session
>>>
>>> Base = declarative_base()
>>>
>>>
>>> class Super(Base):
>>> __tablename__ = "super"
>>> id = Column(Integer, primary_key=True)
>>>
>>>
>>> class Mama(Super):
>>> __tablename__ = "mama"
>>> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>>>
>>> daughter_id = Column(Integer, ForeignKey("daughter.id"))
>>> daughter = relationship(
>>> "Daughter", foreign_keys=[daughter_id], back_populates="mama"
>>> )
>>>
>>>
>>> class Daughter(Super):
>>> __tablename__ = "daughter"
>>> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>>> mama = relationship(
>>> "Mama",
>>> foreign_keys=[Mama.daughter_id],
>>> uselist=False,
>>> back_populates="daughter",
>>> )
>>>
>>>
>>> e = create_engine("sqlite://", echo=True)
>>> Base.metadata.create_all(e)
>>>
>>> session = Session(e)
>>>
>>> m1 = Mama()
>>> m2 = Mama()
>>> d1 = Daughter()
>>> d2 = Daughter()
>>>
>>> session.add(m1)
>>> session.add(m2)
>>> session.add(d1)
>>> session.add(d2)
>>> session.commit()
>>>
>>> m1.daughter = d1
>>> m2.daughter = d2
>>> session.commit()
>>>
>>> m1.daughter = d2
>>> session.commit()
>>>
>>>
>>> assert m2.daughter is None
>>>
>>>
>>>
>>> On Fri, Dec 18, 2020, at 2:01 AM, 'Sören Textor' via sqlalchemy wrote:
>>>> Hello
>>>> I have a huge problem with süecific "one to one" relation.
>>>>
>>>> Woking (it's the tutorial code)
>>>>
>>>> class Son(db.Model):
>>>> __tablename__ = 'son'
>>>> id = db.Column(db.Integer, primary_key=True)
>>>> papa_id = db.Column(db.Integer, db.ForeignKey('papa.id'))
>>>> papa = db.relationship("Papa", foreign_keys=[papa_id],
>>>> back_populates="son")
>>>>
>>>> class Papa(db.Model):
>>>> __tablename__ = 'papa'
>>>> id = db.Column(db.Integer, primary_key=True)
>>>> son = db.relationship("Son", uselist=False, back_populates="papa")
>>>>
>>>> main:
>>>> p1 = Papa()
>>>> p2 = Papa()
>>>> s1 = Son()
>>>> s2 = Son()
>>>>
>>>> db.session.add(p1)
>>>> db.session.add(p2)
>>>> db.session.add(s1)
>>>> db.session.add(s2)
>>>>
>>>> db.session.commit()
>>>> p1.son = s1
>>>> p2.son = s2
>>>> db.session.commit()
>>>> p1.son = s2
>>>> db.session.commit()
>>>>
>>>> Works like a charm. afterwards every relation is correct
>>>>
>>>> My code (I have to use a super class, that's the only difference):
>>>>
>>>> class Super(db.Model):
>>>> __tablename__ = 'super'
>>>> id = db.Column(db.Integer, primary_key=True)
>>>>
>>>> class Mama(Super):
>>>> __tablename__ = 'mama'
>>>> id = db.Column(db.Integer, db.ForeignKey('super.id'), primary_key=True)
>>>>
>>>> daughter_id = db.Column(db.Integer, db.ForeignKey('daughter.id'))
>>>> daughter = db.relationship("Daughter", foreign_keys=[daughter_id],
>>>> back_populates="mama")
>>>>
>>>> class Daughter(Super):
>>>> __tablename__ = 'daughter'
>>>> id = db.Column(db.Integer, db.ForeignKey('super.id'), primary_key=True)
>>>> mama = db.relationship("Mama", foreign_keys=[Mama.daughter_id],
>>>> uselist=False, back_populates="daughter")
>>>>
>>>> main:
>>>> m1 = Mama()
>>>> m2 = Mama()
>>>> d1 = Daughter()
>>>> d2 = Daughter()
>>>>
>>>> db.session.add(m1)
>>>> db.session.add(m2)
>>>> db.session.add(d1)
>>>> db.session.add(d2)
>>>> db.session.commit()
>>>>
>>>> m1.daughter = d1
>>>> m2.daughter = d2
>>>> db.session.commit()
>>>>
>>>> m1.daughter = d2
>>>> db.session.commit()
>>>>
>>>> everything is correct EXCEPT:
>>>> *m2.daughter! *it still points on d2 instead of None. And the table
>>>> contains still the daughter_id of d2.
>>>>
>>>> Thus, what foreign key did I miss?
>>>>
>>>> All the best and stay healthy!
>>>> SirAnn
>>>>
>>>>
>>>> --
>>>> 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/592197fc-0f15-4c99-a2a7-a9443767bcedn%40googlegroups.com
>>>>
>>>> <https://groups.google.com/d/msgid/sqlalchemy/592197fc-0f15-4c99-a2a7-a9443767bcedn%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/f114a07b-78e9-449b-85be-54210b7ce029%40www.fastmail.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/f114a07b-78e9-449b-85be-54210b7ce029%40www.fastmail.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/em92642068-399e-40da-991e-098bb3e065e7%40textors-01
>
> <https://groups.google.com/d/msgid/sqlalchemy/em92642068-399e-40da-991e-098bb3e065e7%40textors-01?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/67167075-f3d3-4728-bc76-dcb42047f863%40www.fastmail.com.
