使用sqlmodel在fastapi中无法获得连接两个表的数据

Question

我试图使用sqlmodel在fastapi中连接两个表

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)

我得到一个错误

File "C:UsersTestAppDataLocalpypoetryCachevirtualenvsrtm-fast-unified-_4z-YO9J-py3.9libsite-packagesfastapiencoders.py", line 144, in jsonable_encoder
raise ValueError(errors)
ValueError: [TypeError("'RMKeyView' object is not callable"), TypeError('vars() argument must have __dict__ attribute')]

Answer 1

在您的repository.py文件中，将db.exec(statement)替换为db.exec(statement).fetchall()，将return results.scalars()替换为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