使用FlaskSqlAlchemy創建多條記錄時出現完整性錯誤

我有一個函式可以使用 SQL Alchemy 在 Flask 應用程式中創建一些新的資料庫條目

def add_volunteer_client_record(volunteer_id, **kwargs):
    try:
        volunteer_client = VolunteerClient(volunteer_id=volunteer_id, **kwargs)

        volunteer_report_action_items = VolunteerReportActionItems(volunteer_client_id = volunteer_client.id)

        db_session.add(volunteer_client) 
        db_session.add(volunteer_report_action_items)
        db_session.commit()

        return volunteer_client
    except IntegrityError as e:
        db_session.rollback()
        message = "Integrity error occurred"
        raise BadRequestError(messages={'volunteer_client': [message]})

志愿者客戶端創建得很好，但是當志愿者報告行動專案被添加到會話中時，我收到了一個 IntegrityError，我不太明白為什么。

我的模特

class VolunteerClient(Base):
    __tablename__ = 'volunteer_client'

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    volunteer_id = Column(Integer, ForeignKey('provider_user.user_id', onupdate='CASCADE', ondelete='RESTRICT'), unique=True)
    client_id = Column(Integer, ForeignKey('user.id', onupdate='CASCADE', ondelete='RESTRICT'), unique=True)

class VolunteerReportActionItems(Base):
    __tablename__ = 'volunteer_report_action_items'

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    volunteer_client_id = Column(Integer, ForeignKey('volunteer_client.id', onupdate='CASCADE', ondelete='RESTRICT'))

SQL

CREATE TABLE IF NOT EXISTS public.volunteer_client
(
  id serial PRIMARY KEY,
  volunteer_id integer NOT NULL,
  client_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
  UNIQUE(volunteer_id, client_id),
  CONSTRAINT fk_volunteer_client_volunteer FOREIGN KEY (volunteer_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_volunteer_client_client FOREIGN KEY (client_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

ALTER TABLE public.volunteer_client OWNER to navigate;

CREATE TABLE IF NOT EXISTS public.volunteer_report_action_items
(
  id serial PRIMARY KEY,
  volunteer_client_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
  CONSTRAINT fk_volunteer_report_action_items_volunteer_client FOREIGN KEY (volunteer_client_id)
      REFERENCES public.volunteer_client (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

ALTER TABLE public.volunteer_report_action_items OWNER to navigate;

這里的任何幫助或建議都會很棒。謝謝！

uj5u.com熱心網友回復：

您需要重繪 VolunteerClient才能訪問VolunteerClient.id

def add_volunteer_client_record(volunteer_id, **kwargs):
    try:
        volunteer_client = VolunteerClient(volunteer_id=volunteer_id, **kwargs)
        db_session.add(volunteer_client)
        db_session.flush() 
        volunteer_report_action_items = VolunteerReportActionItems(volunteer_client_id = volunteer_client.id)
        db_session.add(volunteer_report_action_items)
        db_session.commit()
    ...

標籤：Python sql 烧瓶 sqlalchemy

上一篇：Flask-sqlalchemy在按鈕單擊時添加數字

下一篇：標題中的回傳字符或前導空格無效：授權//-Python