0

I am using pydantic, sqlmodel, fastapi and python.

I have a few tables, e.g. Social, Email that adds a new record to VStatus table whenever a new record is created in them, e.g. a new record in Social creates a corresponding record in VStatus table. The intent is to track the verification status of each record in these tables like Social, Email in the VStatus table.

Also, I want to delete the corresponding record in VStatus table whenever a record that created it e.g. in Social table is deleted.

How should I write the sa_relationship_kwargs for the Relationship in the 'parent' tables? E.g.

Social table:

class Social(BaseUUIDModel, SocialBase, table=True):
    vstatus_id: Optional[UUID] = Field(
        default=None, foreign_key='VStatus.id', nullable=False)

    project: Optional["Project"] = Relationship(back_populates="socials")
    vstatus: "VStatus" = Relationship(
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "Social.vstatus_id==VStatus.id",
            'cascade': 'all, delete',
            'single_parent': True})

VStatus table:

class VStatusBase(SQLModel):
    veri_status: VerificationStatusEnum = Field(
        default=VerificationStatusEnum.Unverified, nullable=False)
    vdesc: str = Field(default='', sa_column=Column("vdesc", String))
    # make it type Optional[bool] and default=None and let validator sets its
    # value
    is_verified: Optional[bool] = Field(default=False)


class VStatus(BaseUUIDModel, VStatusBase, table=True):
    # pass
    social: "Social" = Relationship(
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "VStatus.id==Social.vstatus_id",
            'cascade': 'all, delete, delete-orphan',
            'single_parent': True})  # need to have two ways one-to-one relationship
    # vstatus <-> social
    email: "Email" = Relationship(
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "VStatus.id==Email.vstatus_id",
            'cascade': 'all, delete, delete-orphan',
            'single_parent': True})
    coin: "Coin" = Relationship(
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "VStatus.id==Coin.vstatus_id",
            'cascade': 'all, delete, delete-orphan',
            'single_parent': True})

Update 18 March 2023 SGT 1218AM

@daniil had asked me to provide a minimal viable example to reproduce my problem. Here it is:

from sqlmodel import SQLModel

class VStatusBase(SQLModel):
    veri_status: str = Field(
        default="Unverified", nullable=False)
    vdesc: str = Field(default='', sa_column=Column("vdesc", String))
    is_verified: Optional[bool] = Field(default=False)
    
class VStatus(VStatusBase, table=True):
    id: Optional[int] = Field(
        primary_key=True,
        index=True,
        nullable=False,
    )
    social: "Social" = Relationship(
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "VStatus.id==Social.vstatus_id",
            'cascade': 'all, delete, delete-orphan',
            'single_parent': True})  # need to have two ways one-to-one relationship
            
class SocialBase(SQLModel):
    name: str = Field(nullable=False)
    url: AnyHttpUrl = Field(
        nullable=False, sa_column_kwargs={'unique': True})

class Social(SocialBase, table=True):
    id: Optional[int] = Field(
            primary_key=True,
            index=True,
            nullable=False,
        )
    vstatus_id: Optional[UUID] = Field(
        default=None, foreign_key='VStatus.id', nullable=False)
    vstatus: "VStatus" = Relationship(
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "Social.vstatus_id==VStatus.id",
            'cascade': 'all, delete, delete-orphan',
            'single_parent': True})

From image below, we can see a record exists in the Social table. enter image description here

From the image below, we can see that a corresponding record also exists in the VStatus table where VStatus.id = Social.vstatus_id. enter image description here

I delete all the records (there is actually only one record) from the Social table. enter image description here

I would expect the corresponding record in the VStatus table should be deleted but it doesn't, as shown. enter image description here

Jim
  • 450
  • 2
  • 10
  • Jim, may be you can check the discussion (https://github.com/tiangolo/fastapi/discussions/2718). Depends on one-to-one and many-to-manz and so on relationship, add "uselist" and "back_populates" fields. – Prudhviraj Mar 16 '23 at 17:49
  • I suggest you learn how to create a [minimal reproducible example](https://stackoverflow.com/help/mcve). It helps you better understand the essence of your problem, it helps us figure out how to help you with that problem and it helps other readers transfer the example (and solution) to their specific problem. As it is right now, your code is not complete, not minimal and not reproducible. You have not shown any input/error message or input/output. – Daniil Fajnberg Mar 16 '23 at 21:29
  • Hi @DaniilFajnberg, I have updated my question with a minimal viable codes and also attached screenshots showing the problem that I face, hope I can get the help needed. – Jim Mar 17 '23 at 16:26

0 Answers0