'How to effectively query associated objects in SQLAlchemy ORM (SQLite3)?

I am trying to create a working relationship between two objects Mentor and Student, and be able to retrieve student objects from their mentor:

class Mentor_Student(Base):
  __tablename__ = 'mentor_student'
  __table_args__ = {'extend_existing': True}
  mentor_id = Column("mentor_id", Integer, ForeignKey('mentor.mentor_id'), primary_key = True)
  student_id = Column("student_id", Integer, ForeignKey('student.student_id'), primary_key = True)

  def __init__(self, student_id):
    self.mentor_id = random.choice(list(session.query(Mentor.mentor_id)))[0]
    self.student_id = student_id
    session.add(self)
    session.commit()
    

  
class Mentor(Base):
  __tablename__ = 'mentor'
  __table_args__ = {'extend_existing': True}
  mentor_id = Column(Integer, primary_key=True)
  name = Column(String(255), nullable=False)
  phone = Column(String(20), nullable=False)
  mentees = relationship(
        "Mentor",
        secondary='mentor_student',
        backref=backref("student",  lazy='joined'))

  def __init__(self):
    self.name = Faker().name()
    self.phone = Faker().phone_number()
    session.add(self)
    session.commit()

  def __str__(self):
    return self.name

class Student(Base):
  __tablename__ = 'student'
  __table_args__ = {'extend_existing': True}
  student_id = Column(Integer, primary_key=True)
  name = Column(String(255), nullable=False)
  phone = Column(String(20), nullable=False)
  mentors = relationship(
        "Student",
        secondary='mentor_student',
        backref=backref("mentor",
       lazy='joined'))

                        
  
  def __init__(self):
    self.name = Faker().name()
    self.phone = Faker().phone_number()
    session.add(self)
    session.commit()
    Mentor_Student(self.student_id)

  def __str__(self):
    return self.name

Every mentor has multiple students. I would like to create a query that will return the students(mentees) associated with each Mentor. Please observe below:

for x, y in session.query(Mentors, Mentors.mentees).all()
  print(x,':',y)

could produce the result:

MentorObject : [StudentObject, StudentObject, StudentObject]

Right now the closest I can get is printing out a single mentor object and a single student object associated with it. I also could hard code it with the accumulator pattern into a dicitonary:

maps = {}
for student, mentor in session.query(Student, Mentor).filter(Student.student_id == Mentor_Student.student_id, Mentor_Student.mentor_id == Mentor.mentor_id).all():
    if mentor in maps.keys():
      maps[mentor].append(student)
    else:
      maps[mentor] = [student]

Which gives me the result:

{<__main__.Mentor object at 0x7f3309887070>: [<__main__.Student object at 0x7f3309850f40>, <__main__.Student object at 0x7f3309887280>, <__main__.Student object at 0x7f3309887580>, <__main__.Student object at 0x7f330988f7c0>, <__main__.Student object at 0x7f330988fa00>, <__main__.Student object at 0x7f330982b4c0>], 
...
 <__main__.Mentor object at 0x7f33097e2550>: [<__main__.Student object at 0x7f33097e2490>, <__main__.Student object at 0x7f33097e2790>]}
'''
But this does not seem like a refined solution. Any ideas how I can improve my code. I am relatively new to SQLAlchemy.


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source