One to One relationship

[Alfred Soeng]

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, ForeignKey

from sqlalchemy.orm import relationship

Base = declarative_base()

class Teacher(Base):

    __tablename__ = 'teacher'

    id = Column(Integer, ForeignKey("student.id"), primary_key=True)

    student = relationship("Student", foreign_keys=[id], uselist=False,

                            back_populates="teacher")

class Student(Base):

    __tablename__ = 'student'

    id = Column(Integer, primary_key=True)

    teacher_id = Column(Integer, ForeignKey("teacher.id"))

    teacher = relationship("Teacher", foreign_keys=[teacher_id],

                            back_populates="student")

I define the 2 classes and they are one to one relation.

But each time the binary run said:

sqlalchemy.exc.ArgumentError: Teacher.student and back-reference Student.teacher are both of the same direction symbol('MANYTOONE').  Did you mean to set remote_side on the many-to-one side ?

Can I ask what's wrong with it?

Thanks so much.

[mike bayer]

On 11/10/2016 06:52 PM, Alfred Soeng wrote:
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column, Integer, ForeignKey
> from sqlalchemy.orm import relationship
>
> Base = declarative_base()
>
>
> class Teacher(Base):
> __tablename__ = 'teacher'

> id = Column(Integer, ForeignKey("student.id <http://student.id>"),

> primary_key=True)
> student = relationship("Student", foreign_keys=[id], uselist=False,
> back_populates="teacher")
>
>
> class Student(Base):
> __tablename__ = 'student'
> id = Column(Integer, primary_key=True)
> teacher_id = Column(Integer, ForeignKey("teacher.id

> <http://teacher.id>"))

> teacher = relationship("Teacher", foreign_keys=[teacher_id],
> back_populates="student")
>
> I define the 2 classes and they are one to one relation.
> But each time the binary run said:
>
> sqlalchemy.exc.ArgumentError: Teacher.student and back-reference
> Student.teacher are both of the same direction symbol('MANYTOONE'). Did
> you mean to set remote_side on the many-to-one side ?
>
> Can I ask what's wrong with it?

you have conflicting foreign_keys settings, each one states the opposite
relationship. Only "teacher_id" qualifies for foreign_keys here (would
be set on both sides), but there is no need to set this parameter at all
as you already have ForeignKey present on the actual teacher_id Column
object.



>
> Thanks so much.
>
> --
> 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 sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Alfred Soeng]

It's the issue in the inheritance:

What I want to do is like this:

class Employee(Base):

    __tablename__ = 'employee'

    id = Column(

        Integer,

        primary_key=True,

    )

    type_name = Column(DBT.STRING)

    __mapper_args__ = {'polymorphic_on': 'type_name'}

class Teacher(Employee):

    __tablename__ = 'teacher'

    id = Column(

        Integer,

        ForeignKey("employee.id"),

        primary_key=True,

    )

    related_student = relationship(

        "Student",

        uselist=False,

        back_populates="teacher")

    __mapper_args__ = {

        'polymorphic_identity': 'TEACHER',

        'inherit_condition': (id == Employee.id),

    }

class Student(Employee):

    __tablename__ = 'student'

    id = Column(

        Integer,

        ForeignKey("employee.id"),

        primary_key=True,

    )

    teacher_id = Column(Integer, ForeignKey("teacher.id"))

    teacher = relationship(

        "Teacher",

        foreign_keys=[teacher_id],

        back_populates="related_student"

    )

    __mapper_args__ = {

        'polymorphic_identity': 'STUDENT',

        'inherit_condition': (id == Employee.id),

    }

If I didn't specify the foreign_keys for Teacher relationship, it will say sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Teacher.related_student - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

After I setting the foreign_keys, like below(add foreign_keys for related_student relationship):

class Employee(Base):

    __tablename__ = 'employee'

    id = Column(

        Integer,

        primary_key=True,

    )

    type_name = Column(DBT.STRING)

    __mapper_args__ = {'polymorphic_on': 'type_name'}

class Teacher(Employee):

    __tablename__ = 'teacher'

    id = Column(

        Integer,

        ForeignKey("employee.id"),

        primary_key=True,

    )

    related_student = relationship(

        "Student",

        uselist=False,

        foreign_keys=[id]

        back_populates="teacher")

    __mapper_args__ = {

        'polymorphic_identity': 'TEACHER',

        'inherit_condition': (id == Employee.id),

    }

class Student(Employee):

    __tablename__ = 'student'

    id = Column(

        Integer,

        ForeignKey("employee.id"),

        primary_key=True,

    )

    teacher_id = Column(Integer, ForeignKey("teacher.id"))

    teacher = relationship(

        "Teacher",

        foreign_keys=[teacher_id],

        back_populates="related_student"

    )

    __mapper_args__ = {

        'polymorphic_identity': 'STUDENT',

        'inherit_condition': (id == Employee.id),

    }

The error is:

sqlalchemy.exc.ArgumentError: Student.teacher and back-reference Teacher.related_student are both of the same direction symbol('MANYTOONE').  Did you mean to set remote_side on the many-to-one side ?

Is there any way to solve the problem keeping the inheritance structure and 1:1 relationship?

Thanks so much. 

Best,

Alfred

[Simon King]

Here are your relationship definitions:

teacher = relationship(
"Teacher",
foreign_keys=[teacher_id],
back_populates="related_student"
)

related_student = relationship(
"Student",
uselist=False,

foreign_keys=[id] # <-- this is incorrect
back_populates="teacher")


The "teacher" relationship looks correct - teacher_id is indeed a
foreign key pointing to the Teacher.id column. But the
"related_student" relationship is wrong - Teacher.id is not a foreign
key (for the purposes of this relationship). I'm not sure if you need
the foreign_keys argument here, but if you do, it should probably be
[Student.teacher_id].

Simon