SQLAlchemy error when creating relationship

Question

I'm trying to create a many to many relationship and I am getting am error which I can't seem to resolve (the language is Python and the db is MySQL).

The three classes I am using (all derived from declarative base) are CMClass, Grades and then the associated table AssociationTable_CMClass_Grade.

They are as follows (sorry about the intentation):

class CMClass(med.DeclarativeBase):
__tablename__ = "CMClasses"

id = Column('ID', Integer, primary_key=True, autoincrement=True)
name = Column('NAME', String(100), unique=False)

cmClassTypeID = Column('CMCLASS_TYPE_ID', Integer, ForeignKey(CMClassType.id))
venueID = Column('VENUE_ID', Integer, ForeignKey(Venue.id))
staffMemberID = Column('STAFF_MEMBER_ID', Integer, ForeignKey(StaffMember.id))

classTimeFrom = Column('CLASS_TIME_FROM', DateTime, nullable=True)
classTimeTo = Column('CLASS_TIME_TO', DateTime, nullable=True)

cmClassType = relationship('CMClassType', foreign_keys='CMClass.cmClassTypeID')
venue = relationship('Venue', foreign_keys='CMClass.venueID')
staffMember = relationship('StaffMember', foreign_keys='CMClass.staffMemberID')

listGradeObjects = relationship('Grades', secondary=AssociationTable_CMClass_Grade, backref='CMClasses')

--

class Grade(med.DeclarativeBase):
__tablename__ = "Grades"

id = Column('ID', Integer, primary_key=True, autoincrement=True)
name = Column('NAME', String(50), unique=True)
schoolTypeID = Column('SCHOOL_TYPE_ID', Integer, ForeignKey(SchoolType.id))
gradeLevelID = Column('GRADE_LEVEL_ID', Integer, ForeignKey(GradeLevel.id))
gradeValueID = Column('GRADE_VALUE_ID', Integer, ForeignKey(GradeValue.id))

--

class AssociationTable_CMClass_Grade(med.DeclarativeBase):
    __tablename__ = 'AssociationTable_CMClass_Grade'
    cmClassID = Column('cmClassID', Integer, ForeignKey('CMClasses.ID'), primary_key=True)
    gradeID = Column('gradeID', Integer, ForeignKey('Grades.ID'), primary_key=True)

The kink in the system comes in listGradeObjects, the relationship in the CMClass class. It just doesn't seem to want to work. I'm getting an error message, but I'n not finding it of much help. I'm sure I've used this same method before!

sqlalchemy.exc.ArgumentError: secondary argument <class 'MainDirectory.ORMClasses.AssociationTableClasses.AssocationTable_CMClasses_Grades.AssociationTable_CMClass_Grade'> passed to to relationship() CMClass.listGradeObjects must be a Table object or other FROM clause; can't send a mapped class directly as rows in 'secondary' are persisted independently of a class that is mapped to that same table.

Thanks for any assistance!

moon548834 · Accepted Answer · 2023-08-30 01:38:52Z

5

Try this:

listGradeObjects = relationship('Grades', secondary=AssociationTable_CMClass_Grade.__table__, backref='CMClasses')

It works for me

answered Aug 30, 2023 at 1:38

moon548834

5248 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheFaithfulLearner · Accepted Answer · 2021-07-26 07:14:42Z

3

Well, I want to reply to any of you who looked up that error and ended up here. I looked it up myself and got very little.

Anyhow, here's how it works:

_targetGradesClassName = 'Grade'
_targetAssociationTableName = 'AssociationTable_CMClass_Grades'
_targetGradesBackRefName = 'CMClasses'

@declared_attr
def listGradeObjects(cls):
    return relationship(f'{cls._targetGradesClassName}', secondary=f'{cls._targetAssociationTableName}', backref=f'{cls._targetGradesBackRefName}')

I'm sure there are other ways too, simpler ways, but I'm going to say if it works, it works.

answered Jul 26, 2021 at 7:14

TheFaithfulLearner

74715 silver badges38 bronze badges

1 Comment

wiwa1978 Over a year ago

What is cls? Please specify.

Collectives™ on Stack Overflow

SQLAlchemy error when creating relationship

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related