[Solved]-TypeError: Object of type ResultProxy is not JSON serializable: result in sqlalchemy query?

To resolve the TypeError: Object of type ResultProxy is not JSON serializable error when working with SQLAlchemy queries, you can follow these steps:

1. Understand the error: This error occurs when you try to serialize a SQLAlchemy ResultProxy object directly to JSON. The ResultProxy object represents the result set of a query executed against the database, and it cannot be directly serialized to JSON because it contains database-specific data structures.
2. Convert the result to a serializable format: Before serializing the result, you need to convert it to a serializable format such as a list of dictionaries or a custom data structure.
3. Example solution:

from sqlalchemy import create_engine, MetaData, Table, select
import json

# Assuming you have already executed a query and obtained a ResultProxy object named result
# Convert the ResultProxy object to a list of dictionaries
result_dicts = [dict(row) for row in result]

# Serialize the list of dictionaries to JSON
result_json = json.dumps(result_dicts)

print(result_json)

4. In this example, we first convert each row in the ResultProxy object to a dictionary using the dict() constructor. This step makes the data structure serializable because dictionaries are inherently serializable to JSON.
5. Then, we use the json.dumps() function to serialize the list of dictionaries to JSON format.
6. By following this approach, you can avoid the TypeError: Object of type ResultProxy is not JSON serializable error and successfully serialize the query result to JSON.

Row objects are similar to tuples, but as the error message indicates, they are not JSON serializable.

results = crsr.execute(query).fetchall()
print(f"{type(results)} of type {type(results[0])}")
# <class 'list'> of type <class 'pyodbc.Row'>

We can convert the Row objects to true tuples so they can be serialized:

results = [tuple(row) for row in results]
print(f"{type(results)} of type {type(results[0])}")
# <class 'list'> of type <class 'tuple'>

Now, serialization won't raise an error (unless the tuples themselves contain elements that are not serializable).

If the tuples contain non-serializable elements (like Decimal objects), we can coerce them to strings:

json_string = json.dumps(results, default=str)

Alternatively, we can modify the query to cast the decimals to floats:

query = "SELECT CAST(x AS FLOAT) AS x FROM tbl"

Instead of:

query = "SELECT x FROM tbl"

In this case, the error indicates that the RowProxy object returned by fetchall is not JSON serializable.

A solution to this problem is to pass the result of your query as a dictionary, which is JSON serializable.

Since the result of your query is returning a list of tuples, you can perform the following steps:

res = db.execute("SELECT title,author,year FROM books WHERE year = 2011 LIMIT 4").fetchall()
user_books = {}
index = 0
for entry in res:
    user_books[index] = {
        'title': res[index][0],
        'author': res[index][1],
        'year': res[index][2],
    }
    index += 1

session['list'] = user_books

A word of caution: using the title of the book as a key may lead to overwriting information if there are two books with the same title. Consider using a unique id as the key instead.

Also, note that the dictionary construction above is specific to the existing query. If you add another column to the select statement, you'll need to edit the code to include the extra column information.