SQLAlchemy & SQLAlchemySchema GET return jsonified data is empty dictionary

Question

This is my Model:

class Todo(db.Model):
    __tablename__ = "todos"
    id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String(), nullable=False)


class TodoSchema(ma.SQLAlchemySchema):
    class Meta:
        model = Todo

This is my route

@app.route("/data", methods=["GET"])
def data():
    data = Todo.query.all()
    print(len(data)) #28
    print(data) #[<Todo 1>, <Todo 2>, <Todo 3>, <Todo 4>, <Todo 5>, <Todo 6>, <Todo 7>, <Todo 8>, <Todo 9>, <Todo 10>, <Todo 11>, <Todo 12>, <Todo 13>, <Todo 14>, <Todo 15>, <Todo 16>, <Todo 17>, <Todo 18>, <Todo 19>, <Todo 20>, <Todo 21>, <Todo 22>, <Todo 23>, <Todo 24>, <Todo 25>, <Todo 26>, <Todo 27>, <Todo 28>]
    todo_schema = TodoSchema()
    output = todo_schema.dump(data)
    print(output) # {}
    return output

When I do a loop it looks like this:

for item in data:
   print(item.description)

walk the dog
buy groceries
....

You can see the output of the print statements behind it in the comments. So what I want is the make a JSON Object from the data object which I can return. What is the reason output results in an empty dictionary? Is there a simple solution like

data = Todo.query.all()
return jsonify({data})

I took my implementation from there, but I does not help me, since the example does not return an empty dictionary. This is what I do not understand. — Data Mastery, May 22 '20 at 18:12
You don't need to use `output` or `todo_schema` - you have the results in `data` - just convert it to json via the column names as shown in that link. — match, May 22 '20 at 18:13

targhs · Answer 1 · 2020-05-22T18:48:34.730

0

There's one problem with your code. You are fetching all the Todos from the database with

Todo.query.all()

But when you are instantiating your Schema, you didn't specified many parameter. Change your Schema instantiation to

todo_schema = TodoSchema(many=True)

Quoting the documentation

many – Should be set to True if obj is a collection so that the object will be serialized to a list.

For your reference go to https://marshmallow.readthedocs.io/en/latest/api_reference.html#marshmallow.Schema and head to the parameters of marshmallow.Schema class

UPDATE

As mentioned in the comments there's another problem that now it returns a list of empty dictionaries. For that here's a reference link https://marshmallow-sqlalchemy.readthedocs.io/en/latest/api_reference.html

Try using ModelSchema instead of SQLAlchemySchema.

edited May 22 '20 at 18:48

answered May 22 '20 at 18:26

targhs

1,477
2
16
29

A little bit better, but now I get a list with many empty dicionaries. The data is still getting lost. – Data Mastery May 22 '20 at 18:32
I'm not sure but try adding field to Meta class. Something like fields = ("id", "description") – targhs May 22 '20 at 18:37
Silly mistake here. Try using ModelSchema instead of SQLAlchemySchema – targhs May 22 '20 at 18:44
ModelSchema seems to be no more in the package – Data Mastery May 22 '20 at 19:01
Make sure you are using marshmallow-sqlalchemy(https://marshmallow-sqlalchemy.readthedocs.io/en/latest/index.html) and not importing from marshmallow – targhs May 23 '20 at 06:26

score 0 · Answer 2 · edited Jul 03 '22 at 12:48

0

@targhs was spot on. I'll add what ultimately got me past the empty list problem as applicable to your code:

output = todo_schema.dump(data, many=True)
return todo_schema.jsonify(data, many=True)

edited Jul 03 '22 at 12:48

Yunnosch

26,130
9
42
54

answered Mar 31 '22 at 22:49

impilcature

41
4

You know about the commenting privilege which you do not have, so well that you can even put it into words. You are aware of the rule https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead . In that situation please do not decide to misuse a different mechanism (an answer) for something it is not meant for and which you are not allowed yet to do. However, you can turn this into an answer. – Yunnosch Jul 03 '22 at 12:48

SQLAlchemy & SQLAlchemySchema GET return jsonified data is empty dictionary

2 Answers2