Fastapi app: empty array or TypeError: Boolean value of this clause is not defined

So, Im doing simple todo-api app with fastapi and sqlmodel. Migrations went fine, but if I run my server, I dont see anything except empy array. I added some data in db file with DB Browser for SQLite, so it isn’t empty. And when I run my server and go to “/”, I see only empty array and no data, and if I go to “/1/”, I see this: TypeError: Boolean value of this clause is not defined

My main.py:

from fastapi import FastAPI
import uvicorn
from endpoints.routers import router
from database.db import engine
from sqlmodel import SQLModel

app = FastAPI()
app.include_router(router)

# def create_db_and_tables():
#     SQLModel.metadata.create_all(engine)

if __name__ == '__main__':
    uvicorn.run("main:app", host='localhost', port=8000, reload=True)
    # create_db_and_tables()

routers.py

from fastapi import APIRouter
from models.todo import ToDo
from repos.todo_repo import select_all_todos, select_todo
from database.db import session
from sqlmodel import Session, select

router = APIRouter()

@router.get("/", tags=['Todos'])
def show_todos():
    todos = select_all_todos()
    return todos


@router.get('/{id}/', response_model=ToDo, tags=['Todos'])
def select_one(id: int):
    todo_found = select_todo(id)
    return todo_found

repos:

from models.todo import ToDo
from sqlmodel import Session, select, or_

def select_all_todos():
    with Session(engine) as session:
        todos = select(ToDo)
        results = session.exec(todos)
        todo = results.all()
        return todo
    
def select_todo(id):
    with Session(engine) as session:
        statement = select(ToDo)
        statement = statement.where(ToDo.id==id)
        if not statement:
            return "Error"
        result = session.exec(statement)
        return result.first()

models:

from sqlmodel import Field, SQLModel


class ToDo(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    is_done: bool = False

database.py file:

from sqlmodel import create_engine, Session

eng = 'database.db'

sqlite_url = f'sqlite:///{eng}'
engine = create_engine(sqlite_url, echo=True)
session = Session(bind=engine)

Traceback:

Traceback (most recent call last):
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/uvicorn/protocols/http/h11_impl.py", line 407, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/fastapi/applications.py", line 270, in __call__
    await super().__call__(scope, receive, send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
    raise exc
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
    await self.app(scope, receive, sender)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/routing.py", line 680, in __call__
    await route.handle(scope, receive, send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/routing.py", line 275, in handle
    await self.app(scope, receive, send)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/fastapi/routing.py", line 231, in app
    raw_response = await run_endpoint_function(
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/fastapi/routing.py", line 162, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/starlette/concurrency.py", line 41, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/anyio/to_thread.py", line 31, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
    return await future
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 867, in run
    result = context.run(func, *args)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/endpoints/routers.py", line 17, in select_one
    todo_found = select_todo(id)
  File "/home/aleksandr/Programming/Sites/myTodo/backend/repos/todo_repo.py", line 16, in select_todo
    if not statement:
  File "/home/aleksandr/Programming/Sites/myTodo/backend/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 590, in __bool__
    raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

Answer

So, the exception is that you’re not actually executing the statement in select_todo before checking for error. In SqlModel statement is a special kind of object to build and process a query, so it can’t be directly checked for boolean value.

If you want to check if there are any results, place this statement under exec and check results instead.

There’s no sense to check the statement itself in if, it’s constructed successfully if no exception raised by that point