'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 |
|---|
