Return JSON list from Flask Sqlalchemy query

Question

I'm trying to make an API that I can send requests to (from a front-end app) and display that information.

I have set up a Postgres database, and am using Flask & Sqlalchemy.

My first function, which returns a single entry in the database does work:

@app.route("/get/<id_a>/<id_b>")
def get_by_id(id_a,id_b):
    try:
        ref=PlanApp.query.filter_by(plan_ref=f"{id_a}/{id_b}").first()
        return jsonify(ref.serialize())
    except Exception as e:
        return(str(e))

where PlanApp is a model class.

But, I have been unsuccesful in returning a list of rows. For instance, I want to get all rows where a certain column (a date) is after a given date.

@app.route("/get/<date>")
def get_since_date(date):
    try:
        result=PlanApp.query.filter(PlanApp.app_date>date).all()
        return jsonify(result.serialize())
    except Exception as e:
        return(str(e))

This returns:

'list' object has no attribute 'serialize'

So I removed the serialize function. Then I'm met with:

Object of type PlanApp is not JSON serializable

Am I missing something here? Many thanks in advance. I will include the full code below.

from flask import Flask, abort, jsonify, make_response, request
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_cors import CORS, cross_origin
from models import db, PlanApp
import os
 
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['DATABASE_URL']
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True

db.init_app(app)
migrate = Migrate(app, db)
 

# @app.route('/')
# def show_all():
#    return render_template('show_all.html', PlanApp = PlanApp.query.all() )

# Get a planning application by its reference number
# Two arguments because the format of a ref is 'xxxx/xx'
@app.route("/get/<id_a>/<id_b>")
def get_by_id(id_a,id_b):
    try:
        print(f"{id_a}/{id_b}")
        ref=PlanApp.query.filter_by(plan_ref=f"{id_a}/{id_b}").first()
        print(ref)
        return jsonify(ref.serialize())
    except Exception as e:
        return(str(e))

@app.route("/get/<date>")
def get_since_date(date):
    try:
        print(date)
        result=PlanApp.query.filter(PlanApp.app_date>date).all()
        print(result)
        # result = [r for r, in result]
        # for r in result:
        #     print(type(r))
        # result = PlanApp.query.filter_by(app_date>date)
        return jsonify(result.serialize())
    except Exception as e:
        return(str(e))

if __name__ == '__main__':
    app.run(debug=True)

Use Flask-Marshmallow. Bascically, you create a `Schema` which you would write something like: `result = PlanApp.query.filter(PlanApp.app_date>date).all()`, `response = planapp_schema.dump(result)`, and then `return jsonify(response)`. Honestly, Marshmallow is really simple and it will fix this issue for you: https://flask-marshmallow.readthedocs.io/en/latest/ — Jake Jackson, Dec 07 '20 at 17:11
Thanks for the advice! I'll check it out. I managed to get around my problem in the end. I hadn't understood what my PlanApp class' serialize() method was doing (it just returns a dict of the class - which represents a row in the table). So my solution was: `result = PlanApp.query.filter(PlanApp.app_date>date).all()`, `result = [r.serialize() for r in result]`, `return jsonify(result)` — Peter, Dec 08 '20 at 19:55
In fact, yeah, that is another way of doing it. I have seen a similar solution before but never used. At least it is all sorted now :) But do check out Marshmallow in the future, it is super simple. — Jake Jackson, Dec 09 '20 at 15:01

Return JSON list from Flask Sqlalchemy query

0 Answers0