Hi All,
I'm struggling to put several concepts together. I am working with a
legacy SQL Server database (2008 R2 and 2019) that was not setup with much
planning, let alone ORM in mind. I would like to set up a many to many
relationship that joins through columns I specify and all the tables are
autoloaded from the database schema. I've been doing fine until I hit this
association object pattern. Can you please offer any advice?
Thanks,
Rob
In the end my goal is to execute the following and have it issue a single
statement to the database.
results =
Class.query.join(Enrollment).join(Student).join(StudentDetails).all()
But even this call fails and gets the error below. I assume I've
incorrectly defined the relationships if it doesn't know how to do the
join.
results = Class.query.join(Enrollment).all()
sqlalchemy.exc.InvalidRequestError: Don't know how to join to <class '
__main__.Enrollment'>. Please use the .select_from() method to establish an
explicit left side, as well as providing an explcit ON clause if not
present already to help resolve the ambiguity.
Here's a trimmed example illustrating what I'm trying
class Class(Model):
__table__ = Table("classes", metadata, autoload=True, autoload_with=engine)
class Student(Model):
__table__ = Table("Students", metadata, autoload=True, autoload_with=engine)
class Enrollment(Model):
__table__ = Table("Enrollment", metadata, autoload=True,
autoload_with=engine)
class_join_string =
("and_(remote(Class.ClassID)==foreign(Enrollment.class),"
"remote(Class.TimeSlot)==foreign(Enrollment.time_slot))")
class_ = relationship("Class", primaryjoin=class_join_string,
backref="enrollments")
student_join_string =
"remote(Student.StudentId)==foreign(Enrollment.studentID)"
student = relationship("Student", primaryjoin=student_join_string,
backref="classes")
class StudentDetails(Model):
# 1 to 1 with Student
__table__ = Table("StudentDetails", metadata, autoload=True,
autoload_with=engine)
student_join_string =
"foreign(StudentDetails.student)==remote(Student.Student_id)"
student = Db.db().relationship('Student', primaryjoin=student_join_string,
backref=Db.db().backref('details',
uselist=False))
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAHUdip%3DZLwO-KE-3ifiCHNjZ0v7EbqQpA1d7BGhqao9bGLtnCw%40mail.gmail.com.
