On Nov 29, 2011, at 11:19 AM, Nathan Finstein wrote:
> "teacher" has two many-to-one relationships with "student".
> A teacher can have a favorite student and a worst student.
> A student can be the favorite (or worst) of many teachers
>
> 1) Error is not surprising, since there is nothing to indicate whether
> "favorite" relationship uses "favorite_id" ForeignKey or "worst_id"
> ForeignKey.
>
> sqlalchemy.exc.ArgumentError: Could not determine join condition between
> parent/child tables on relationship Teacher.favorite. Specify a
> 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
> needed as well.
>
> How should one indicate association between relationship and ForeignKey
> column?
"primaryjoin" looks like this:
favorite = relationship("Student", backref="likes_me_best",
primaryjoin="Student.id==Teacher.favorite_id") # my
favorite student
>
> 2) When I comment out worst_id:
> ###worst_id = Column(Integer, ForeignKey('student.id')) # for many-to-one
well then worst and favorite reference the same foreign key.
> No error is reported. But with worst_id Column commented out, running the
> second module below produces weird result. Before the commit teacher 1 has a
> favorite student and different worst student. After commit, the favorite
> relationship is corrupted, favorite and worst are now the same student.
> True the mto.py module is not good, but producing corrupt data rather than
> gracefully raising an exception seems like a bug.
>
>
> result with line commented out ###worst_id = Column(Integer,
> ForeignKey('student.id')) # for many-to-one
>
> ('sA likes_me_best:', [<mto.Teacher object at 0xb76b3f8c>], '
> sA.likes_me_least', [])
> ('sB likes_me_best:', [], ' sB.likes_me_least', [<mto.Teacher object at
> 0xb76b3f8c>])
> ('t1.favorite_id:', None, 't1.favorite:', <mto.Student object at 0xb7344e4c>,
> 't1.worst:', <mto.Student object at 0xb7344e8c>)
> ('sA.id:', None, 'sB.id:', None)
>
> ============== AFTER COMMIT ==================
>
> ('sA likes_me_best:', [], ' sA.likes_me_least', [])
> ('sB likes_me_best:', [<mto.Teacher object at 0xb76b3f8c>], '
> sB.likes_me_least', [<mto.Teacher object at 0xb76b3f8c>])
> ('t1.favorite_id:', 2, 't1.favorite:', <mto.Student object at 0xb7344e8c>,
> 't1.worst:', <mto.Student object at 0xb7344e8c>)
> ('sA.id:', 1, 'sB.id:', 2)
>
>
>
> mto.py
> ---------
> #!/usr/bin/env python
>
> from sqlalchemy import Table, Column, Integer, ForeignKey
> from sqlalchemy.orm import relationship, backref
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class Teacher(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True)
> favorite_id = Column(Integer, ForeignKey('student.id')) # for many-to-one
> favorite = relationship("Student", backref="likes_me_best") # my favorite
> student
>
> worst_id = Column(Integer, ForeignKey('student.id')) # for many-to-one
> worst = relationship("Student", backref="likes_me_least") # my worst
> student
>
> class Student(Base):
> __tablename__ = 'student'
> id = Column(Integer, primary_key=True)
>
>
> ====================================
>
> #!/usr/bin/env python
>
> import mto
> import sqlalchemy
>
> t1 = mto.Teacher()
> #t2 = mto.Teacher()
>
> sA = mto.Student()
> sB = mto.Student()
>
> t1.favorite = sA
> t1.worst = sB
>
> print("sA likes_me_best:",sA.likes_me_best, "
> sA.likes_me_least",sA.likes_me_least)
> print("sB likes_me_best:",sB.likes_me_best, "
> sB.likes_me_least",sB.likes_me_least)
>
> print("t1.favorite_id:", t1.favorite_id, "t1.favorite:", t1.favorite,
> "t1.worst:", t1.worst)
> print("sA.id:", sA.id, "sB.id:", sB.id)
>
> sqla_engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=False)
> mto.Base.metadata.create_all(sqla_engine)
> Session = sqlalchemy.orm.sessionmaker(bind=sqla_engine)
> session = Session()
> session.add(t1)
> session.commit()
>
> print("\n ============== AFTER COMMIT ==================\n")
> print("sA likes_me_best:",sA.likes_me_best, "
> sA.likes_me_least",sA.likes_me_least)
> print("sB likes_me_best:",sB.likes_me_best, "
> sB.likes_me_least",sB.likes_me_least)
>
> print("t1.favorite_id:", t1.favorite_id, "t1.favorite:", t1.favorite,
> "t1.worst:", t1.worst)
> print("sA.id:", sA.id, "sB.id:", sB.id)
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
