How to inherit Class with ‘sa_column’ in a proper way?

[zhu-lingfeng]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import SQLModel, Field, Relationship
from datetime import datetime
from sqlalchemy import Column, JSON


class ReportItemBase(SQLModel):
    id: int = Field(
        primary_key=True,
        index=True,
        nullable=False,
        sa_column_kwargs={"autoincrement": True},
    )
    pub_date: datetime | None
    risk_level: int
    full_content: dict | None = Field(sa_column=Column(JSON))


class ReportItem(ReportItemBase, table=True):
    __tablename__ = "reportitem_json"
    report_rule: "ReportRule" = Relationship(back_populates="rule_risk_items")


class ArchivedItem(ReportItemBase, BaseIntIDModel, table=True):
    __tablename__ = "reportitem_archived"
    report_rule: "ReportRule" = Relationship(back_populates="archived_items")

Description

When I run fastapi, i get an error "sqlalchemy.exc.ArgumentError: Column object 'full_content' already assigned to Table 'reportitem_json'".

I've read SQLAlchemy's doc Mapping Class Inheritance Hierarchies and still have no idea.

Why this happen and what should i do to fix it?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

3.10.12

Additional Context

SQLAlchemy 2.0.29

[joachimhuet]

You are right there is a strange behaviour here but what you can actually do is to use sa_type instead of sa_column.

This should work as expected:

class ReportItemBase(SQLModel):
    ...
    full_content: Optional[dict] = Field(None, sa_type=JSON)

[zhu-lingfeng]

thanks a lot!

[zhu-lingfeng]

邮件已收到，我会尽快回复~【自动回复】

[elefher]

I am facing a similar problem but I use the onupdate attr from Column.
Example Code:

class TimeStampedBase(SQLModel):
    created_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc), sa_column=Column(DateTime(timezone=True), onupdate=lambda: datetime.now(timezone.utc), nullable=False))


class User(TimeStampedBase, SQLModel, table=True):
    id: UUID = Field(primary_key=True, default=func.gen_random_uuid())
    transactions: List["Transaction"] = Relationship(back_populates="user")


class Transaction(TimeStampedBase, SQLModel, table=True):
    id: UUID = Field(primary_key=True, default=func.gen_random_uuid())

    user_id: UUID = Field(foreign_key="user.id")
    user: "User" = Relationship(back_populates="transactions")

I get this error sqlalchemy.exc.ArgumentError: Column object 'updated_at' already assigned to Table 'user'.

Is there a solution to this?

[joachimhuet]

I answered this question in this thread: #876 (comment)

The thing is by using Column in a base thread the column object is instantiated and can't be used in several tables. So the way is to use sa_column_args or sa_column_kwargs to instantiate the sqlalchemy Column only on inheriting table.

[elefher]

thank you @joachimhuet I just saw your answer. I tried it and works fine 🚀

[zhu-lingfeng]

After Python 3.11, you can use TypedDict/NotRequired from typing to deal with sa_type=JSON properly, supporting Swagger Doc schema, like:

class Customer(TypedDict):
    name: Annotated[str, Field(description="Name")]
    tel: Annotated[NotRequired[str | None], Field(description="Tel")] = None

class Factory(SQLModel):
    customers: Annotated[list[Customer], Field(sa_type=JSON, description="Customer list")] = []