Using Python, Flask, SQLAlchemy, Marshmallow -- dumps method returns an empty list of objects with my many=true schema

Question

Here are my classes for the two SQL tables I'll be using:

#Players Table for the database
class Players(masdb.Model):
    __tablename__ = 'players'

    id=masdb.Column(masdb.Integer, primary_key=True)
    game_key=masdb.Column(masdb.String(255))
    games=masdb.relationship('Game', backref='player', lazy=True)

    def __init__(self, game_key): # constructor function
        self.game_key = game_key

#Game table for the database
class Game(masdb.Model):
    __tablename__ = 'game'

    id = masdb.Column(masdb.Integer, primary_key=True)
    settlement_name = masdb.Column(masdb.String(50)) 
    created_at = masdb.Column(masdb.DateTime, default=datetime.datetime.now)
    player_id = masdb.Column(masdb.Integer, ForeignKey('players.id')) 

    def __init__(self, settlement_name, player_id):
        self.settlement_name = settlement_name
        self.player_id = player_id

Here are the schemas for serializing the data:

class PlayersSchema(ma.Schema): 
    class Meta:
        fields = ('id', 'game_key')


class GameSchema(ma.Schema): 
    class Meta:
        fields = ('id', 'settlement_name', 'created_at', 'player_id')

# Classes for serializing data
player_schema = PlayersSchema()
players_schema = PlayersSchema(many=True)
game_schema = GameSchema()
games_schema = GameSchema(many=True)

And finally, here I am trying to return a json with multiple game entries:

@app.route('/getGamesByGameKey/<cookieGame_Key>/', methods =['GET'])
def getGamesByGameKey(cookieGame_Key):
    findPlayerStatement = select(Players).filter_by(game_key = cookieGame_Key) # SQL statement
    playerObject = masdb.session.execute(findPlayerStatement).first() # Executes the statement. I think first() just stops the search at the first find always returning an array of 1.
    findGamesStatement = select(Game).filter_by(player_id = playerObject[0].id) #Data will only ever have a single entry due to the above, hence the playerObject[0]
    games = masdb.session.execute(findGamesStatement).all()
    results = games_schema.dump(games)
    return jsonify(results)

But when I get to results = games_schema.dump(games) it returns an array of empty objects

I created 3 games in my database for my test and associated them all to a single player for my test. When I send the request for them I get the following response back:

[
    {},
    {},
    {}
]

What I've deduced:

games = masdb.session.execute(findGamesStatement).all() line is producing an array of 3 objects exactly as expected and the data is all being stored in the variable.

If I change the .all() to .first() it will return just a single object, and even using the games_schema (which has many=True) it will work! It just... only returns the single item. So there doesn't appear to be anything wrong with the schema itself. The output is as follows:

[
    {
        "created_at": "2023-04-10T20:23:39",
        "id": 120,
        "player_id": 118,
        "settlement_name": "Multi Game to Get"
    }
]

So there's something going wrong with serializing an array of objects. The symptoms of this are similar to others who have posted that were missing the many=True for the schema. But I have that.

Answer 1

Your query returns a list of tuples with a single element: (game,). The schema(many=True) acts on the tuple, not the game, so it doesn't find anything and returns a list of empty dicts.

When using first, you return the first tuple and the schema(many=True) treats this (one element) tuple as a list and dumps the game correctly.

You need to rework the query or its output to return a list of games, not a list of tuples with game as first and only element.