Not able to get data for joining two tables in fastapi using sqlmodel

Question

I am trying to join two tables in fastapi using sqlmodel

repository.py

def test(db: Session = Depends(get_db)):
    statement = select(Tool, CountryTool).where(Tool.tool_id == CountryTool.tool_id)
    results = db.exec(statement)
    return results.scalars()

user.py

@router.get("/test",tags=['test'])
def get_user(db: Session = Depends(get_db)):
    result = repository.test(db)
    return result

tables.py

class Tool(SQLModel, table=True):
    __tablename__ = 'tools'

    tool_id: Optional[int] = Field(default=None, primary_key=True)
    tool_name : str = Field(sa_column=Column("tool_name", VARCHAR(54),nullable=False))
    tool_description : str = Field(sa_column=Column("tool_description", TEXT , nullable=True))
    tool_guide: str = Field(sa_column=Column("tool_guide", TEXT, nullable=True))

#Relationship Table between Tool and Country
class CountryTool(SQLModel, table=True):
    __tablename__ = 'country_tool'

    country_id: Optional[int] = Field(default=None, foreign_key='countries.country_id',primary_key=True)
    tool_id :Optional[int] = Field(default=None, foreign_key='tools.tool_id',primary_key=True)

I am getting an error

File "C:\Users\Test\AppData\Local\pypoetry\Cache\virtualenvs\rtm-fast-unified-_4z-YO9J-py3.9\lib\site-packages\fastapi\encoders.py", line 144, in jsonable_encoder
    raise ValueError(errors)
ValueError: [TypeError("'RMKeyView' object is not callable"), TypeError('vars() argument must have __dict__ attribute')]

score 0 · Answer 1 · answered Nov 11 '22 at 04:31

In your repository.py file, instead of db.exec(statement), replace it with db.exec(statement).fetchall() and replace return results.scalars() with return results

def test(db: Session = Depends(get_db)):
    statement = select(Tool, CountryTool).where(Tool.tool_id == CountryTool.tool_id)
    results = db.exec(statement).fetchall()
    return results

Not able to get data for joining two tables in fastapi using sqlmodel

1 Answers1