Not JSON serializable - Python + Flask + Sqlalchemy

Question

I am writing small query to fetch the data from mysql database, I have a report table, inside that I have report_id, I need to query the data which matched report_id coming from api params.

My function:

def view_single_thumbnail(idx): // idx coming from params 
    session = Session()

    result = session.query(
        Report
    ).filter(
        Report.report_id == idx
    ).all()

    return jsonify({
        'data': result
    })

Throwing error: 'components.db.core.table_declaration.Report object at 0x000001C1Fsdfsdf51E6A90' is not JSON serializable.

ybl · Answer 1 · 2020-02-24T06:09:10.473

8

SQLAlchemy objects cannot be serialized automatically by jsonify. You can add a property to your SQLAlchemy model class

class Report(db.Model):
    def __init__(self):
        ...

    @property
    def serialized(self):
        """Return object data in serializeable format"""
        return {
            'id': self.id,
            'report_text': "Some text",
            ...
        }

And you view will update to this:

def view_single_thumbnail(idx): // idx coming from params 
    session = Session()

    result = session.query(
        Report
    ).filter(
        Report.report_id == idx
    ).all()

    return jsonify({
        'data': [result.serialized for result in results]
    })

edited Feb 24 '20 at 06:09

answered Jan 07 '19 at 06:42

ybl

1,510
10
16

i cant understand this, i am beginner for python/flask. where i need to put this? – Mohamed Sameer Jan 07 '19 at 06:44
You have to add the property to the `Report` class. When you add the property `serialize` jsonify will call it to get a serializable dictionary. – ybl Jan 07 '19 at 06:59
it should be `result.serialize` (without brackets) instead of `result.serialize()` – You're awesome Oct 16 '19 at 04:00
Ah, I'll fix that. Thanks, you are awesome! (pun intended) – ybl Oct 16 '19 at 04:04

fixatd · Answer 2 · 2019-01-07T06:50:20.543

2

The result from a query is an Object if I remember correctly, you should convert it into a dict first before trying to convert it into a json. I've used a simple lambda function to do mine in a older project

# Lambda vesion
# row2dict = lambda r: {c.name: str(getattr(r, c.name)) for c in r.__table__.columns}

# Function version
def row2dict(row):
    return {
        c.name: str(getattr(row, c.name))
        for c in row.__table__.columns
    }

def view_single_thumbnail(idx): // idx coming from params 
    session = Session()

    result = [
           row2dict(report)
           for report in session.query(Report)
                  .filter(Report.report_id == idx)
                  .all()
    ]

    return jsonify({
        'data': result
    })

edited Jan 07 '19 at 06:50

answered Jan 07 '19 at 06:42

fixatd

1,394
1
11
19

is there any method without using third party libraries like lambda? – Mohamed Sameer Jan 07 '19 at 06:44
`lamba` is a built in keyword in python. It's just a simplified way of declaring a function. – fixatd Jan 07 '19 at 06:45
whats the row2dict actually do? – Mohamed Sameer Jan 07 '19 at 06:46
By default the `query` returns an object instead of an serializeable object. What `row2dict` does is it runs through all the attributes of your object, `Report` object for this case, and then converts that into a `dict`. – fixatd Jan 07 '19 at 06:48
AttributeError: 'Report' object has no attribute 'id' – Mohamed Sameer Jan 07 '19 at 06:49
What does your `Report` class look like? – fixatd Jan 07 '19 at 06:54

Not JSON serializable - Python + Flask + Sqlalchemy

2 Answers2