TypeError: Object of type ResultProxy is not JSON serializable: result in sqlalchemy query?

Question

I am trying to query for 3 fields in a table like this in sqlalchemy:

if request.method == 'GET':
        search_form = SearchForm()
        result = dbSession.execute(
            "SELECT * FROM books WHERE (isbn LIKE '%:text%') OR (title LIKE '%:text%') OR (author LIKE '%:text%') LIMIT 10",
            { "text": search_form.searchText.data }
        )
        return jsonify({'result': result})

Is my query correct? why would i have this error?

TypeError: Object of type ResultProxy is not JSON serializable

metmirr · Accepted Answer · 2018-09-23 09:08:07Z

22

Simply error says result is not a dictionary. To solve it:

jsonify({'result': [dict(row) for row in result]})

It will convert each row to a dictionary.

answered Sep 23, 2018 at 9:08

metmirr

4,3222 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

haneulkim Over a year ago

thanks you. Have one question though. How does python know appropriate column name?

Yan Yang Over a year ago

It doesn't work when in some cases results return no rows. (like in post/put)

Vallie Over a year ago

you can add an if condition

Tom M · Accepted Answer · 2020-12-16 22:37:39Z

I hit this error and it was initially a bit confusing, so it's probably worth further explanation.

The result from an SQLAlchemy SQL query is a list (you can check this with type(results)) and it's confusing because usually you'd be able to add a list directly into a dictionary and jsonify it. Further, it looks like a list of tuples, which again would usually be accepted by jsonify.

But this is the key: it's actually a list of objects of SQLAlchemy's ResultsProxy type - hence the error.

Thus the error is because it's a list of a non-jsonify-able type. However, rather than having to convert it to a dictionary, as per metmirr's answer above, you can also choose to convert it to a list, and then include that list within a dictionary later. Neither approach is right or wrong, but if you're used to getting lists of tuples back from psycopg2, this approach might match your usual workflow better:

results = [list(row) for row in result]
result_dict = {'results': results}
return jsonify(results_dict)

Collectives™ on Stack Overflow

TypeError: Object of type ResultProxy is not JSON serializable: result in sqlalchemy query?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related