Unable to use exec when querying data in FastAPI

[maxmantz]

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

import os
from typing import List
from fastapi import FastAPI
from pydantic import BaseModel
from sqlalchemy import create_engine, select
from sqlmodel import Field, SQLModel, Session
from dotenv import load_dotenv
import uvicorn

load_dotenv()

app = FastAPI()

class CategoryBase(SQLModel):
    name: str = Field(nullable=False, max_length=255)
    position: int = Field(nullable=False, default=0)

class Category(CategoryBase, table=True):
    __tablename__ = "category"
    id: int | None = Field(primary_key=True)
    
sql_engine = create_engine(url=os.getenv("DATABASE_URL"), echo=True)

def get_session():
    return Session(bind=sql_engine)


class CategoryModel(BaseModel):     
    id: int
    name: str
    position: int



@app.get("/", response_model=List[CategoryModel])
async def get_categories():
    with get_session() as session:
        categories = session.exec(select(Category).order_by(Category.position)).all()
        return categories
    
    


if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)

Description

When running this app, I get the following error:

fastapi.exceptions.ResponseValidationError: 3 validation errors:
  {'type': 'missing', 'loc': ('response', 0, 'id'), 'msg': 'Field required', 'input': (Category(id=1, position=1, name='Einstieg'),)}
  {'type': 'missing', 'loc': ('response', 0, 'name'), 'msg': 'Field required', 'input': (Category(id=1, position=1, name='Einstieg'),)}
  {'type': 'missing', 'loc': ('response', 0, 'position'), 'msg': 'Field required', 'input': (Category(id=1, position=1, name='Einstieg'),)}

However, the data is correctly loaded as seen during debugging, or even when looking at the error message.

When omitting the response_model in the decorator, I get the following error:

File "{path}\.venv\Lib\site-packages\fastapi\encoders.py", line 332, in jsonable_encoder
    raise ValueError(errors) from e
ValueError: [ValueError('dictionary update sequence element #0 has length 3; 2 is required'), TypeError('vars() argument must have __dict__ attribute')]

Strangely, when using the following calls to either query or execute, it works flawlessly.

# working fine with query
@app.get("/", response_model=List[CategoryModel])
async def get_categories():
    with get_session() as session:
        categories = session.query(Category).order_by(Category.position).all()
        return categories

# working fine with execute
@app.get("/", response_model=List[CategoryModel])
async def get_categories():
    with get_session() as session:
        categories = session.execute(
            select(Category).order_by(Category.position)
        ).scalars().all()
        return categories

Both query and execute are deprecated. Why is using exec not working?

Operating System

Linux, Windows

Operating System Details

Tried both Windows and WSL Ubuntu

SQLModel Version

0.0.24

Python Version

3.13.1

Additional Context

I'm using Postgresql17

[maxmantz]

The problem was that I imported the wrong select function.

Wrong
from sqlalchemy import select

Correct
from sqlmodel import select