时间戳

首页 > 解决方案 > 如何使用 SQLalchemy 正确设置关联表

python - 如何使用 SQLalchemy 正确设置关联表

问题描述

我一直在尝试在需要关联表的 SQLalchemy 中设置模型。

按照文档中的操作，我最终得到了这个：

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")

要添加到数据库，我运行以下代码：

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()

运行此代码以尝试将志愿者添加到数据库会出现此错误：

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'

到目前为止，我一直无法找到解决此问题的方法，我们将不胜感激。

标签： pythondatabasedatabase-designsqlalchemy

解决方案

这是应该的方式。我最初的模型完全错误，我没有正确使用辅助参数来链接关联表。

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")

推荐阅读