One to One relation problem

11 views
Skip to first unread message

Sören Textor

unread,
Dec 18, 2020, 2:01:47 AM12/18/20
to sqlalchemy
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

Mike Bayer

unread,
Dec 18, 2020, 10:31:53 AM12/18/20
to noreply-spamdigest via sqlalchemy
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
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Sören Textor

unread,
Dec 18, 2020, 11:51:49 AM12/18/20
to mik...@zzzcomputing.com, sqlal...@googlegroups.com
Hi Mike
Thanks for looking at my code. Next time I'll post an testcase like you. Sorry for that one. And I cannot believe it. But it works now.
I also updated SQLAlchemy, flast-RESTful, flask-migrate and so on, to their newest version.

And now it seems to work. And problem before was 

> assert m2.daughter:id is None

This failed. But I tried and tried and tried in out production code isntead of sampling a small testcase like you. bad idea. 

And I really have NO idea why it works now. But I think I stop here for today and can have fun at wekkeend.. instead of thinking thinking thinking about that!!

Many thanks!


Sören Textor

unread,
Dec 18, 2020, 12:50:56 PM12/18/20
to mik...@zzzcomputing.com, sqlal...@googlegroups.com
This example fails. Instead of assigning an objekt, I assign just the daughters id ... 
But I think that's "correct"?

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" <soeren...@googlemail.com>
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 <mik...@zzzcomputing.com> schrieb am Fr. 18. Dez. 2020 um 16:31:

Mike Bayer

unread,
Dec 18, 2020, 1:44:51 PM12/18/20
to noreply-spamdigest via sqlalchemy
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.

Sören Textor

unread,
Dec 18, 2020, 1:52:31 PM12/18/20
to sqlal...@googlegroups.com
Ah. I see. Thus this was a newbie question. Thanks again! 

Reply all
Reply to author
Forward
0 new messages