多个外键插入

Question

我与祖父母之间有一种关系，这种关系是一对多关系，父母与孩子之间有一对多关系，祖父母与孩子之间也有一对多关系。我的模特看起来像

class Department(Base):
    __tablename__ = 'department'

    id = Column(Integer, primary_key=True)
    deptId = Column(String(8), unique=True, nullable=False)
    deptName = Column(String(200))
    agency = relationship("Agency", backref='dept_agency', lazy='dynamic')
    office = relationship("ContractingOffice", backref='dept_office', lazy='dynamic')

    def __repr__(self):
        return f"Department(deptId={self.deptId}, deptName={self.deptName})"


class Agency(Base):
    __tablename__ = 'agency'

    id = Column(Integer, primary_key=True)
    agencyId = Column(String(8), unique=True, nullable=False)
    agencyName = Column(String(200))
    deptId = Column(Integer, ForeignKey('department.id'))
    office = relationship("ContractingOffice", backref='agency_office', lazy='dynamic')

    def __repr__(self):
        return f"Agency(agencyId={self.agencyId}, agencyName={self.agencyName})"


class ContractingOffice(Base):
    __tablename__ = 'contractingOffice'

    id = Column(Integer, primary_key=True)
    officeId = Column(String(8), unique=True, nullable=False)
    officeName = Column(String(200))
    deptId = Column(Integer, ForeignKey('department.id'))
    agencyId = Column(Integer, ForeignKey('agency.id'))

    def __repr__(self):
        return f"ContractingOffice(officeID={self.officeId}, officeName={self.officeName})"

当我尝试从csv文件插入一行时

# Test Data
DEPARTMENT_ID = '0000'
DEPARTMENT_NAME = 'THE LEGISLATIVE BRANCH'
AGENCY_CODE = '0099'
AGENCY_NAME = 'JOINT HOUSE AND SENATE ENTITIES'
CONTRACTING_OFFICE_CODE = '023106'
CONTRACTING_OFFICE_NAME = 'US CAPITOL POLICE PROCUREMENT'

dept = Department(deptId=DEPARTMENT_ID, deptName=DEPARTMENT_NAME)
agency = Agency(agencyId=AGENCY_CODE, agencyName=AGENCY_NAME, dept_agency=dept)
office = ContractingOffice(officeId=CONTRACTING_OFFICE_CODE,
                           officeName=CONTRACTING_OFFICE_NAME,
                           dept_office=dept,
                           agency_office=agency)

logger.info(dept)
logger.info(agency)
logger.info(office)

db.session.add(dept)
db.session.add(agency)
db.session.add(office)
db.session.commit()

我得到了错误

2019-06-18 13:43:24,800 - Agency-Insert - INFO - Department(deptId=0000, deptName=THE LEGISLATIVE BRANCH)
2019-06-18 13:43:24,801 - Agency-Insert - INFO - Agency(agencyId=0099, agencyName=JOINT HOUSE AND SENATE ENTITIES)
2019-06-18 13:43:24,802 - Agency-Insert - INFO - ContractingOffice(officeID=023106, officeName=US CAPITOL POLICE PROCUREMENT)
Traceback (most recent call last):
  File "C:\Users\spitf\AppData\Local\Programs\Python\Python37\Lib\runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "C:\Users\spitf\AppData\Local\Programs\Python\Python37\Lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "D:\python\mario\mario\scripts\agency_insert.py", line 60, in <module>
    db.session.add(office)
  File "D:\python\mario\.venv\lib\site-packages\sqlalchemy\orm\session.py", line 1947, in add
    self._save_or_update_state(state)
  File "D:\python\mario\.venv\lib\site-packages\sqlalchemy\orm\session.py", line 1960, in _save_or_update_state
    self._save_or_update_impl(state)
  File "D:\python\mario\.venv\lib\site-packages\sqlalchemy\orm\session.py", line 2301, in _save_or_update_impl
    self._save_impl(state)
  File "D:\python\mario\.venv\lib\site-packages\sqlalchemy\orm\session.py", line 2254, in _save_impl
    to_attach = self._before_attach(state, obj)
  File "D:\python\mario\.venv\lib\site-packages\sqlalchemy\orm\session.py", line 2374, in _before_attach
    % (state_str(state), state.session_id, self.hash_key)
sqlalchemy.exc.InvalidRequestError: Object '<ContractingOffice at 0x23a47b92048>' is already attached to session '2' (this is '3')

我的插入或模型/表格关系有什么问题