Empty Json using Flask-Marshmallow and SQLAlchemy

Question

I am trying to create a endpoint to return data from two tables indicator and metadata so I created the model below with indicator = db.relationship('Indicator', backref='metadatas') and indicator_id = db.Column(db.Integer, db.ForeignKey('indicator.id')) but when I call the api/indicators I get empty json as you can see at the end, why?

models.py

from datetime import datetime
from app import db, ma

class Indicator(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    unique_key = db.Column(db.String(32))
    name = db.Column(db.String(255))
    short_name = db.Column(db.String(255))
    valid = db.Column(db.Boolean)
    created_at = db.Column(db.DateTime())
    last_updated_at = db.Column(db.DateTime())

class Metadata(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    indicator_id = db.Column(db.Integer, db.ForeignKey('indicator.id'))
    indicator = db.relationship('Indicator', backref='metadatas')
    type = db.Column(db.String(255))
    inputs = db.Column(db.String(255))
    options = db.Column(db.String(255))
    outputs = db.Column(db.String(255))

class IndicatorSchema(ma.SQLAlchemySchema):
    class Meta:
        model = Indicator 

    id: ma.auto_field()
    unique_key: ma.auto_field()
    name: ma.auto_field()
    short_name: ma.auto_field()
    valid: ma.auto_field()

class MetadataSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Metadata
        include_fk = True

Below you can see the other files:

app.py

from flask_jwt_extended import JWTManager
from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow

app = Flask(__name__)
app.config["JWT_SECRET_KEY"] = "fe7e8955db51c0ff78550419434128cb"
app.config["JWT_ACCESS_TOKEN_EXPIRES "] = 28800
app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:////tmp/test.db"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['JSON_SORT_KEYS'] = False

db = SQLAlchemy(app)
ma = Marshmallow(app)

controller.py

from flask_jwt_extended import jwt_required, create_access_token, get_jwt_identity
from flask import Flask, request, jsonify
from models import Indicator, Metadata, IndicatorSchema, MetadataSchema
from config import conn_string
from app import app, db
import services
import hashlib
import json

@app.route('/api/indicators', methods=['GET'])
@jwt_required()
def indicators():
    short_name = request.args.get('short_name',None)

    if short_name is not None:
        indicator = Indicator.query.filter_by(short_name = short_name).first()
        indicator_schema = IndicatorSchema()
        output = indicator_schema.dump(indicator)

    else:
        indicators = Indicator.query.all()
        indicators_schema = IndicatorSchema(many=True)
        output = indicators_schema.dump(indicators)

    return jsonify(output), 200

When I call the /api/indicators I get response 200 but empty JSON:

$ http :5000/api/indicators 
HTTP/1.0
Content-Type: application/json
Server: Werkzeug/2.0.0 Python/3.8.5

[
    {},
    {},
    {},
    {},
    {},
    {}
]

Answer 1

I think there are two issues:

1. You should be defining the relationship on the Indicator model (one indicator with many metadatas)
2. I believe you're looking for the meta attribute include_relationships, since you're looking to return more than just the foreign key ids

Your code with the adjustments:

from datetime import datetime
from app import db, ma

class Indicator(db.Model):
    __tablename__ = "indicator"
    id = db.Column(db.Integer, primary_key=True)
    unique_key = db.Column(db.String(32))
    name = db.Column(db.String(255))
    short_name = db.Column(db.String(255))
    valid = db.Column(db.Boolean)
    created_at = db.Column(db.DateTime())
    last_updated_at = db.Column(db.DateTime())
    metadatas = db.relationship('Metadata', backref='indicator')

class Metadata(db.Model):
    __tablename__ = "metadata"
    id = db.Column(db.Integer, primary_key=True)
    indicator_id = db.Column(db.Integer, db.ForeignKey('indicator.id'))
    type = db.Column(db.String(255))
    inputs = db.Column(db.String(255))
    options = db.Column(db.String(255))
    outputs = db.Column(db.String(255))

class IndicatorSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Indicator
        include_relationships = True

    id: ma.auto_field()
    unique_key: ma.auto_field()
    name: ma.auto_field()
    short_name: ma.auto_field()
    valid: ma.auto_field()

class MetadataSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Metadata