FastAPI not returning list of SQLAlchemy rows properly-CodePudding

I am trying to return a list of SQLAlchemy rows and output it from a FastAPI endpoint. Each row in the list consists of the actor's name and the actor's total number of lines from a show. The query itself I believe is correct, but when viewing the output from the endpoint, one of the columns is missing for some reason.

The query function:

def get_actors(db: Session, detailed: bool = False) -> list[str]:
    """Return a list of actors and their total lines from the show"""
    if not detailed:
        query = (
            db.query(models.Script.actor, func.count(models.Script.detail))  # (<actor name>, <total lines>)
              .filter(models.Script.actor.isnot(None))                       # Skip null actors.
              .group_by(models.Script.actor)                                 # Group unique actors.
              .order_by(func.count(models.Script.detail).desc())             # Sort by most to least lines.
        )
        actors_list = [actor for actor in query]
        print("ACTORS LIST:", actors_list)                                   # Debug print, looks fine.
        return actors_list
    ...

FastAPI endpoint

@holy_api.get("/actors", response_class=PrettyJSONResponse)
def get_actors(detailed: bool = False, db: Session = Depends(get_db)):
    """Get a list of all the actors from the show with their total lines, optionally detailed view"""
    return crud.get_actors(db, detailed=detailed)

Now, when I open up /actors, the terminal prints out the debug looking like:

ACTORS LIST: [('Michael Palin', 2454), ('Eric Idle', 2107), ('John Cleese', 2044), ('Graham Chapman', 1848), ('Terry Jones', 1801), ('Carol Cleveland', 277), ('Terry Gilliam', 85), ('Terry\nJones', 35), ('Neil Innes', 12), ('Ian Davidson', 8), ('Connie Booth', 5), ('Katya Wyeth', 4), ('Rita Davies', 3), ('Marjorie Wilde', 3), ('Donna Reading', 2), ('Nicki Howorth', 1), ('Julia Breck', 1), ('Caron Gardener', 1)]
INFO:     127.0.0.1:54057 - "GET /actors HTTP/1.1" 200 OK

This is exactly what I need. But the actual JSON response looks like:

[
    {
        "actor": "Michael Palin"
    }, 
    {
        "actor": "Eric Idle"
    }, 
    {
        "actor": "John Cleese"
    }, 
    {
        "actor": "Graham Chapman"
    }, 
    {
        "actor": "Terry Jones"
    }, 
    {
        "actor": "Carol Cleveland"
    },
    ...
]

The total lines aren't shown next to the actor. Why? I am not sure if this is a problem with FastAPI not parsing the JSON properly or if this is a SQLAlchemy thing or I'm doing something plainly wrong.

CodePudding user response：

Wow! Shortly after posting this question, I fixed it. All I had to do was attach a label to the aggregate count function: func.count(models.Script.detail).label("total_lines") I've been on this problem for hours and I feel really, really dumb right now.