How can I properly set up association tables with SQLalchemy

user_9245173 0
Viewed 72

I have been trying to set up models in SQLalchemy requiring association tables.

Following what was done in the docs, I ended up with this:

class VolunteerQualAssociation(Base):
    __tablename__ = "volunteer_qualifications"
    volunteer_id = Column(String(20), ForeignKey("volunteers.id"), primary_key=True)
    qual_id = Column(Integer, ForeignKey("qualifications.id"), primary_key=True)
    volunteer = relationship("Volunteer", back_populates = "qualifications")
    qualification = relationship("Qualification", back_populates = "volunteers")

class VolunteerEventAssociation(Base):
    __tablename__ = "event_volunteers"
    event_ref = Column(String(20), ForeignKey("events.ref"),primary_key=True)
    volunteer_id = Column(String(20), ForeignKey("volunteers.id"),primary_key=True)
    event = relationship("Event", back_populates = "volunteers")
    volunteer = relationship("Volunteer", back_populates = "events")

class Volunteer(Base):
    __tablename__ = "volunteers"
    id = Column(String(20), primary_key = True)
    ...
    qualifications = relationship("VolunteerQualAssociation", back_populates = "volunteer")
    events = relationship("VolunteerEventAssociation", back_populates = "volunteer")

class Qualification(Base):
    __tablename__ = "qualifications"
    id = Column(Integer, primary_key=True)
    qualification = Column(String(20))
    volunteers = relationship("VolunteerQualAssociation", back_populates = "qualification")

class Event(Base):
    __tablename__ = "events"
    ref = Column(String(20), primary_key = True)
    ...
    volunteers = relationship("VolunteerEventAssociation", back_populates = "event")

To add to the database I run this code:

def add_volunteers(volunteer_list): #pass in a list of dictionaries containing volunteer data
    volunteers = []
    for x in volunteer_list:

        qual_list = []
        for qual, val in x["qualifications"].items(): #dictionary passed in {qualification1 : "true" , qualification2 : "false", ...}
            if val == "true":
                qual_list.append(Qualification(qualification = qual))


        volunteer = Volunteer(id=x["volunteer_ID"],
                               first_name=x["first_name"],
                               last_name=x["last_name"],
                               unit=x["unit"])

        volunteer.qualifications = qual_list
        volunteers.append(volunteer)

    session.add_all(volunteers)
    session.commit()

Running this code to try to add a volunteer to the database gives this error:

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1488, in emit_backref_from_collection_append_event
sja-signup-website |     child_impl = child_state.manager[key].impl
sja-signup-website | KeyError: 'volunteer'

I have been unable to find a solution to this issue so far, any help would be appreciated.

sqlalchemy database-design python database
1 Answers

This is the way it should have been. My initial models were completely wrong, I did not make proper use of the secondary parameter to link the association tables.

class VolunteerQualAssociation(Base):
        __tablename__ = "volunteer_qualifications"
        volunteer_id = Column(String(20), ForeignKey("volunteers.id"), primary_key=True)
        qual_id = Column(Integer, ForeignKey("qualifications.id"), primary_key=True)
    
class VolunteerEventAssociation(Base):
    __tablename__ = "event_volunteers"
    event_ref = Column(String(20), ForeignKey("events.ref"),primary_key=True)
    volunteer_id = Column(String(20), ForeignKey("volunteers.id"),primary_key=True)

class Volunteer(Base):
    __tablename__ = "volunteers"
    id = Column(String(20), primary_key = True)
    first_name = Column(String(20), nullable = False)
    last_name = Column(String(20), nullable = False)
    unit = Column(String(20), nullable = False)
    qualifications = relationship("Qualification", back_populates = "volunteers", secondary = "volunteer_qualifications")
    events = relationship("Event", back_populates = "volunteers", secondary = "event_volunteers")

class Qualification(Base):
    __tablename__ = "qualifications"
    id = Column(Integer, primary_key=True)
    qualification = Column(String(20))
    volunteers = relationship("Volunteer", back_populates = "qualifications", secondary = "volunteer_qualifications")

class Event(Base):
    __tablename__ = "events"
    ref = Column(String(20), primary_key = True)
    name = Column(String(100), nullable = False)
    start = Column(DateTime, nullable = False)
    end = Column(DateTime, nullable = False)
    location = Column(String(1000), nullable = False)
    description = Column(String(10000), nullable = True)
    accreditation = Column(Boolean, nullable = False)
    cancelled = Column(Boolean, nullable = False)
    FA_needed = Column(Integer, nullable=False)
    AFA_needed = Column(Integer, nullable = False)
    volunteers = relationship("Volunteer", back_populates = "events", secondary = "event_volunteers")
user_92451730
Related