FastAPI sqlmodel get all rows from the database-CodePudding

I have a FastAPI application that needs to provide GET route to get all Items from a Postgres 13 database. The Item is defined and added from another app. The Item has a model like this:

class ItemDb(SQLModel, table=True):
    __tablename__ = "items"
    id: str = Field(
        default_factory=uuid.uuid4, primary_key=True)
    name: str

There is also table gadgets. Every Gadget has a foreign key which points to an Item:

class GadgetDb(SQLModel, table=True):
    __tablename__ = "gadgets"
    id: str = Field(
        default_factory=uuid.uuid4, primary_key=True)
    item_id: str = Field(..., foreign_key="items.id")
    name: str

In my app, I want to retrieve just all the Items for now. I have created a simple model like this:

class Item(SQLModel):
    id: str
    key_name: str

This is my crud function:

class CRUDItem(CRUDBase[Item, None, None]):

    def list(self, db: Session) -> List[Item]:
        statement = select(self.model)
        results = db.exec(statement)
        return results.all()


item = CRUDItem(Item)

And this is my endpoint function:

from app.crud.crud_item import item

@router.get(
    "/",
    response_model=List[Item],
    status_code=200,
    response_model_exclude_unset=True,
)
def list_items(
    session: Session = Depends(get_session),
) -> Any:
    items = item.list(session)
    return items

Now, this returns an error TypeError: 'SQLModelMetaclass' object is not iterable. Why this error occurs and how can I get all the rows from the database?

Edit. The error traceback:

INFO:     172.30.0.1:51432 - "GET /api/v1/items/ HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 404, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/fastapi/applications.py", line 270, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/cors.py", line 84, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
    raise exc
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/usr/local/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 680, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 275, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 231, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 162, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/usr/local/lib/python3.9/site-packages/starlette/concurrency.py", line 41, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
  File "/usr/local/lib/python3.9/site-packages/anyio/to_thread.py", line 31, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
  File "/usr/local/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
    return await future
  File "/usr/local/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 867, in run
    result = context.run(func, *args)
  File "/app/./app/api/api_v1/endpoints/items.py", line 29, in list_items
    results = session.exec(select(Item)).all()
  File "/usr/local/lib/python3.9/site-packages/sqlmodel/sql/expression.py", line 450, in select
    return SelectOfScalar._create(*entities, **kw)  # type: ignore
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 5309, in _create
    return cls.create_legacy_select(*args, **kw)
  File "<string>", line 2, in create_legacy_select
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 402, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 5165, in create_legacy_select
    self._raw_columns = [
TypeError: 'ModelMetaclass' object is not iterable

Edit2. The class CRUDBase:

ModelType = TypeVar("ModelType", bound=Base)
CreateSchemaType = TypeVar("CreateSchemaType", bound=BaseModel)
UpdateSchemaType = TypeVar("UpdateSchemaType", bound=BaseModel)


class CRUDBase(Generic[ModelType, CreateSchemaType, UpdateSchemaType]):  # 1
    def __init__(self, model: Type[ModelType]):  # 2
    """
    CRUD object with default methods to Create, Read, Update, Delete (CRUD).
    **Parameters**
    * `model`: A SQLAlchemy model class
    * `schema`: A Pydantic model (schema) class
    """
    self.model = model

    def get(self, db: Session, id: Any) -> Optional[ModelType]:
        return db.query(self.model).filter(self.model.id == id).first()  # 3

    def list(
        self, db: Session, *, skip: int = 0, limit: int = 100
    ) -> List[ModelType]:
        return db.query(self.model).offset(skip).limit(limit).all()  # 4

    def create(self, db: Session, *, obj_in: CreateSchemaType) -> ModelType:
        obj_in_data = jsonable_encoder(obj_in)
        db_obj = self.model(**obj_in_data)  # type: ignore
        db.add(db_obj)
        db.commit()  # 5
        db.refresh(db_obj)
        return db_obj

CodePudding user response：

The Item class isn't an ORM model (it doesn't have table=True), so you can't use it in database queries. Using ItemDb should resolve the issue.