[Python] How to define self relations in python SQL model ORM

This is the sqlAlchemy version of a Entity model

class Folder(Base):
__tablename__ = "folder"
__table_args__ = (
ForeignKeyConstraint(
["account_id", "parent_id"], ["folder.account_id", "folder.folder_id"]
),
)

account_id = mapped_column(Integer, primary_key=True)
folder_id = mapped_column(Integer, primary_key=True)
parent_id = mapped_column(Integer)
name = mapped_column(String)

parent_folder = relationship(
"Folder", back_populates="child_folders", remote_side=[account_id, folder_id]
)

child_folders = relationship("Folder", back_populates="parent_folder")


How to convert this into SQL model?

I used

from typing import List, Optional
from sqlmodel import SQLModel, Field, Column, Relationship
from uuid import UUID, uuid4

class Folder(SQLModel, table=True):
__tablename__ = "folder"
...
folder_id: int = Field(primary_key=True)
parent_id: int = Field()
...
parent_folder = Relationship(
back_populates="child_folders",
)

child_folders = Relationship(back_populates="parent_folder", sa_relationship_kwargs={"primaryjoin":"folder.folder_id== folder.folder_id"})


it didnt work and returned below error

Could not determine join condition between parent/child tables on relationship Folder.children - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.


Anyone have a solution for this?

Continue reading...