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.
From the image below, we can see that a corresponding record also exists in the VStatus
table where VStatus.id
= Social.vstatus_id
.
I delete all the records (there is actually only one record) from the Social
table.
I would expect the corresponding record in the VStatus
table should be deleted but it doesn't, as shown.