[Error] Object of type Decimal is not JSON serializable

📝Object of type Decimal is not JSON serializable 에러 해결하기

Falsk 웹 페이지에서 리스트를 가져오는 코드 개발중 에러발생

Object of type Decimal is not JSON serializable (Decimal 유형의 객체는 JSON 직렬화가 가능하지 않습니다)

class MovieListResource(Resource) :

    def get(self) :
        order = request.args.get('order')
        offset = request.args.get('offset')
        limit = request.args.get('limit')

        try :
            connection = get_connection()

            query = '''select m.id, m.title, ifnull(count(r.movie_id),0) as cnt,
                    ifnull(round(avg(r.rating),1),0) as avg
                    from movie m
                    left join rating r
                    on m.id = r.movie_id
                    group by m.id
                    order by ''' + order + ''' desc
                    limit ''' + offset + ''' , ''' + limit + ''' ; '''

            cursor = connection.cursor(dictionary= True)

            cursor.execute(query, )

            result_list = cursor.fetchall()

            cursor.close()
            connection.close()

        except Error as e :
            print(e)
            cursor.close()
            connection.close()

            return {"error" : str(e)}, 500

        return {"result" : "success", "items" : result_list, "count" : len(result_list)}, 200

 

쿼리문 결과를 찍어보니 mysql에서 avg한 결과값이 mysql Decimal값으로 반환되서 에러가 발생한 것 같다

for문에서 에러가 난 avg컬럼을 float으로 변환해서 저장 후 전송했다

class MovieListResource(Resource) :

    def get(self) :
        order = request.args.get('order')
        offset = request.args.get('offset')
        limit = request.args.get('limit')

        try :
            connection = get_connection()

            query = '''select m.id, m.title, ifnull(count(r.movie_id),0) as cnt,
                    ifnull(round(avg(r.rating),1),0) as avg
                    from movie m
                    left join rating r
                    on m.id = r.movie_id
                    group by m.id
                    order by ''' + order + ''' desc
                    limit ''' + offset + ''' , ''' + limit + ''' ; '''

            cursor = connection.cursor(dictionary= True)

            cursor.execute(query, )

            result_list = cursor.fetchall()

            i = 0
            for row in result_list :
                result_list[i]['avg'] = float(row['avg'])
                i = i + 1

            cursor.close()
            connection.close()

        except Error as e :
            print(e)
            cursor.close()
            connection.close()

            return {"error" : str(e)}, 500

        return {"result" : "success", "items" : result_list, "count" : len(result_list)}, 200

 

+ 날짜형식의 데이터가 에러났다면 isoformat()을 사용해서 변환

i = 0
            for row in result_list :
                result_list[i]['datetime'] = row['datetime'].isoformat()
                result_list[i]['createdAt'] = row['createdAt'].isoformat()
                result_list[i]['updatedAt'] = row['updatedAt'].isoformat()
                i = i + 1