FastAPI - SQLModel, Relationship and Insertion of New Row leading to Insertion of New Row in Another Table

Question

Suppose I have two tables, Email and VStatus ('V' stands for 'Verification').

class Email(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str
    vstatus_id: Optional[int] = Field(default=None, foreign_key='VStatus.id')
    vstatus: Optional["VStatus"] = Relationship()

class VStatus(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    vstatus: StatusEnum = Field(default=StatusEnum.Unverified) # InProgress, Verified, Unverified
    vdesc: str = Field(default="") # Verification Description

How should I structure my SQLModel tables such that whenever I create a new email via the API say '/create_email', with just a single field email, it will automatically created a new row in the VStatus table via the Relationship defined in the Email table?

Example:

POST /create_email
{
    'email': 'example@example.com'
}

I will get a response that looks like:

response = {
    'email': 'example@example.com',
    'vstatus_id': '1001', # example
}

And in the VStatus table, there will be a new row that looks like:

id	vstatus	vdesc
1001	Unverified	Null

Since you haven't given a default value, how do you expect a given value to be set or picked? (Also, generally, there isn't a need for a new row - you need one row for each possible status, not one row for each possible status for each email - so you'd have three rows in `VStatus`, and `Email` would reference one of those three ids instead). — MatsLindh, Feb 07 '23 at 23:12

score 0 · Answer 1 · answered Feb 24 '23 at 13:21

I think that you don't have to create a new table. You could do something like this:

from sqlmodel import Field, JSON, Enum, Column


class Email(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str
    vstatus: VStatus = Field(sa_column=Column(Enum(VStatus)))

class VStatus(str, enum.Enum):
    InProgress = "InProgress"
    Verified = "Verified"
    Unverified = "Unverified"

FastAPI - SQLModel, Relationship and Insertion of New Row leading to Insertion of New Row in Another Table

1 Answers1