Ah. I see. Thus this was a newbie question. Thanks again! Mike Bayer <[email protected]> schrieb am Fr. 18. Dez. 2020 um 19:44:
> 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 > <https://groups.google.com/d/msgid/sqlalchemy/67167075-f3d3-4728-bc76-dcb42047f863%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/CAP-B88j6byi7q7Q9ktQrtCupX30Py%2B447wACbOO-No1q9DSCvQ%40mail.gmail.com.
