I have background in DjangoREST. Now I am practicing the alternative combination tools. SQLAlchemy + marshmallow.

I am trying to deserialize the instance with foreign relation between another table. Here is my code.

Problem:
I am unable to return response with correct payload.

Attempts:

1. created_by = SmartNested(UserSchema) I got black dictionary {}
2. created_by = fields.Nested(UserSchema, attribute="username") # created_by does not show up.
3. created_by = fields.Nested(UserSchema, attribute="id") ValueError: The attribute argument for one or more fields collides with another field’s name or attribute argument. Check the following field names and attribute arguments: [‘id’]

Question:
Where am I wrong?

models.py

from datetime import date, datetime
import enum
from sqlmodel import Field, SQLModel


class StatusEnum(enum.Enum):
    """Enum class of status field."""
    pending = "pending"
    in_progress = "in_progress"
    completed = "completed"


class GenericTask(SQLModel, table=True):
    """Model class for GenericTask."""
    id: int = Field(primary_key=False)  # For human use
    identifier: str = Field(primary_key=True)   # For redo mechanism


class TaskContent(SQLModel, table=True):
    """Model class for TaskContent."""
    identifier: str = Field(primary_key=True)   # For redo mechanism
    id: int = Field(primary_key=False)  # For human use
    title: str = Field(nullable=True)
    description: str = Field(nullable=True)
    due_date: date = Field(default=None, nullable=True)
    status: StatusEnum = Field(default=StatusEnum.pending)
    created_by: int = Field(nullable=True, default=None, foreign_key="user.id")
    created_at: datetime = Field(default=date.today())  # For redo mechanism
    updated_at: datetime = Field(default=date.today())


class User(SQLModel, table=True):
    """User model of this application."""
    id: int = Field(primary_key=True)
    username: str = Field(nullable=False)

serializers.py

"""Deserialize the instance to a dictionary."""
from marshmallow import fields, Schema
from marshmallow import validate
from marshmallow.fields import Nested
from sqlmodel import Session

from models import TaskContent, User, StatusEnum


# Use this because marshmellow can exclude identifier field. But for clarity.
# I intentionally not use `exclude` to be explicit.


class SmartNested(Nested):
    def serialize(self, attr, obj, accessor=None):
        if attr not in obj.__dict__:
            return {"id": int(getattr(obj, attr + "_id"))}
        return super(SmartNested, self).serialize(attr, obj, accessor)

class UserSchema(Schema):
    """Schema for User."""
    class Meta:
        model = User

    id = fields.Integer()
    username = fields.String()


class TaskContentSchema(Schema):
    """Schema for TaskContent."""
    class Meta:
        """Meta class for TaskContentSchema."""
        model = TaskContent
        load_instance = True
        include_relationships = True

    id = fields.Integer()
    title = fields.String()
    description = fields.String()
    due_date = fields.Date()
    status = fields.String(validate=validate.OneOf([
        StatusEnum.pending, StatusEnum.in_progress, StatusEnum.completed]))
    # created_by = SmartNested(UserSchema)  # Got {}
    created_by = fields.Nested(UserSchema, attribute="username")  # created_by does not show up.

requirements.txt

#
# This file is autogenerated by pip-compile with Python 3.12
# by the following command:
#
#    pip-compile --output-file=requirements.txt requirements.in
#
alembic==1.13.1
    # via -r requirements.in
annotated-types==0.6.0
    # via pydantic
anyio==4.3.0
    # via
    #   httpx
    #   starlette
    #   watchfiles
certifi==2024.2.2
    # via
    #   httpcore
    #   httpx
click==8.1.7
    # via uvicorn
dnspython==2.6.1
    # via email-validator
email-validator==2.1.1
    # via fastapi
fastapi[all]==0.110.2
    # via -r requirements.in
greenlet==3.0.3
    # via sqlalchemy
h11==0.14.0
    # via
    #   httpcore
    #   uvicorn
httpcore==1.0.5
    # via httpx
httptools==0.6.1
    # via uvicorn
httpx==0.27.0
    # via fastapi
idna==3.7
    # via
    #   anyio
    #   email-validator
    #   httpx
itsdangerous==2.2.0
    # via fastapi
jinja2==3.1.3
    # via fastapi
mako==1.3.3
    # via alembic
markupsafe==2.1.5
    # via
    #   jinja2
    #   mako
marshmallow==3.21.1
    # via marshmallow-sqlalchemy
marshmallow-sqlalchemy==1.0.0
    # via -r requirements.in
orjson==3.10.1
    # via fastapi
packaging==24.0
    # via marshmallow
psycopg2-binary==2.9.9
    # via -r requirements.in
pydantic==2.7.1
    # via
    #   -r requirements.in
    #   fastapi
    #   pydantic-extra-types
    #   pydantic-settings
    #   sqlmodel
pydantic-core==2.18.2
    # via pydantic
pydantic-extra-types==2.7.0
    # via fastapi
pydantic-settings==2.2.1
    # via fastapi
python-decouple==3.8
    # via -r requirements.in
python-dotenv==1.0.1
    # via
    #   pydantic-settings
    #   uvicorn
python-multipart==0.0.9
    # via fastapi
pyyaml==6.0.1
    # via
    #   fastapi
    #   uvicorn
sniffio==1.3.1
    # via
    #   anyio
    #   httpx
sqlalchemy==2.0.29
    # via
    #   alembic
    #   marshmallow-sqlalchemy
    #   sqlmodel
sqlmodel==0.0.16
    # via -r requirements.in
starlette==0.37.2
    # via fastapi
typing-extensions==4.11.0
    # via
    #   alembic
    #   fastapi
    #   pydantic
    #   pydantic-core
    #   sqlalchemy
ujson==5.9.0
    # via fastapi
uvicorn[standard]==0.29.0
    # via
    #   -r requirements.in
    #   fastapi
uvloop==0.19.0
    # via uvicorn
watchfiles==0.21.0
    # via uvicorn
websockets==12.0
    # via uvicorn