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

fromsqlmodelimportSQLModel, Field, RelationshipfromdatetimeimportdatetimefromsqlalchemyimportColumn, JSONclassReportItemBase(SQLModel): id: int=Field( primary_key=True, index=True, nullable=False, sa_column_kwargs={"autoincrement": True}, ) pub_date: datetime|Nonerisk_level: intfull_content: dict|None=Field(sa_column=Column(JSON)) classReportItem(ReportItemBase, table=True): __tablename__="reportitem_json"report_rule: "ReportRule"=Relationship(back_populates="rule_risk_items") classArchivedItem(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:

classReportItemBase(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:

classCustomer(TypedDict): name: Annotated[str, Field(description="Name")] tel: Annotated[NotRequired[str|None], Field(description="Tel")] =NoneclassFactory(SQLModel): customers: Annotated[list[Customer], Field(sa_type=JSON, description="Customer list")] = []