Python Flask, sqlalchemy and marshmallow serialization Issue in JSON representation and DB mirroring structure

Question

Writing a REST API with Flask - I have declared 2 simple Models (Company and Address) using SQLAlchemy and i have also created the underlying DB and Tables in PostgreSQL (running Locally on PGAdmin).

Here the Models with (repr andjson methods included):

import os
import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
app = Flask(__name__)
app.config.from_object(os.environ['APP_SETTINGS'])
db = SQLAlchemy(app)
ma = Marshmallow(app)



#MODELS
class Company(db.Model):
__tablename__ = 'company'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
description = db.Column(db.String, nullable=False)
website = db.Column(db.Text, nullable=False)
addresses = db.relationship('Address', backref='company')

def json(self):
    return {'id': self.id,'description': self.description, 'website': self.website}

def __repr__(self):
    company_object = {
        'id': self.id,
        'description': self.description,
        'website': self.website,
        'addresses': self.addresses
    }
    return json.dumps(company_object)


def get_company(_id):
    return Company.json(Company.query.filter_by(id=_id).first())



class Address(db.Model):
__tablename__ = 'address'

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
street = db.Column(db.String, nullable=False)
postcode = db.Column(db.String, nullable=False)
country = db.Column(db.String, nullable=False)
company_Id = db.Column(db.Integer,db.ForeignKey('company.id'))

def json(self):
    return {'id': self.id, 'street': self.street, 'postcode':self.postcode, 'country':self.country}

def __repr__(self):
    Address_object = {
        'id': self.id,
        'street': self.street, 
        'postcode':self.postcode, 
        'country':self.country
        }
    return json.dumps(Address_object)

Here is the Controller for the Compnay API ( a simple get company via id that should return a company with an array of object addresses nested inside ) a company can have one or more addresses) notice i have tried to implement the marshmallow schema Serialization ( i think i did something worng there- not really sure):

from models.company_model import Company,Address
import json
from marshmallow import Schema, fields
#This below import ma from database.py --> from flask_marshmallow import Marshmallow
from database import  ma
producstAPI = Blueprint('companyAPI', __name__)

class AddressSchema(ma.Schema):
    id = fields.Int(dump_only=True)
    street = fields.String()
    postcode = fields.String()
    country = fields.String()


class CompanySchema(ma.Schema):
        id = fields.Int(dump_only=True)
        title = fields.String()
        description = fields.String()
        imageURL = fields.String()
        addresses= fields.Nested(AddressSchema,many=True)

    

# GET A Compaany via id - this company ALREADY exists in the Postgresql db with id 1 in the table Company and so  the 2 addresses on the table Address
@companyAPI.route('/api/v1/companies/<int:company_id>')
def get_company_via_company_id(company_id):
    company = Company.get_company(company_id)
    company_schema = CompanySchema()
    output = company_schema.dump(company)
    return jsonify(output)

I start my Flask app and hit this URL 'http://127.0.0.1:5000/api/v1/companies/1' with POSTMAN to test my code. here Below is the result:

{
  "description": "My Company",
  id": 1,
  "price": 23.45,
  "addresses": [],
  "website": "A link"
}

But I am trying to acheive this format Below:

{
  "id": 1,
  "description": "My Company",
  "website": "A link",
  "addresses": [
    {
      "id": 1,
      "street": "1 dark street",
      "postcode": "00001",
      "country": "UK"
    },
    {
      "id": 1,
      "street": "1 light street",
      "postcode": "00002",
      "country": "Australia"
    }
  ]
}

I have some inexperience with more nested/complicated models declarations - i have worked with flat json structures and i never had issues like this before - or maybe the fact that creating 2 tables is the actuall isssue here? i have made 2 tables to maintain data integrity between the 2 object making sure every address is linked to a company - i tought that marshmallow would simplify things but it's becoming a bit of a nightmare. i believe this could be a simple Serialization issue. can someone help? also if i had to write in the DB via the api would i encoutner same issue?

Answer 1

It could be a typo in the question, but the fields should be named addresses, shouldn't it?

class CompanySchema(ma.Schema):
        id = fields.Int(dump_only=True)
        title = fields.String()
        description = fields.String()
        price = fields.Float()
        imageURL = fields.String()
        # Here:
        # sizeOptions = fields.Nested(AddressSchema,many=True)
        addresse = fields.Nested(AddressSchema,many=True)

There may be other issues because I don't see where price comes from.