2

I am using Python library flask-rest-jsonapi to design an API. The library uses flask-sqlalchemy and marshmallow. Marshmallow has a requirement to have an "id" and a "type" field mandatory in the schema.

The database tables that I am designing APIs for doesn't have "id" column. The two tables that I am trying to define the relationship are called Department and Teacher where one department has multiple teachers so there is one to many relationship between department and teacher. Their primary keys columns are called "Department_Unique_ID" and "Teacher_Unique_ID".

Here is my code:

from flask import Flask
from flask_rest_jsonapi import Api, ResourceDetail, ResourceList, ResourceRelationship
from flask_rest_jsonapi.exceptions import ObjectNotFound
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm.exc import NoResultFound
from marshmallow_jsonapi.flask import Schema, Relationship
from marshmallow_jsonapi import fields
import connectDB

# Create the Flask application
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

app = connectDB.connectToDb(app)

db = SQLAlchemy(app)

class Department(db.Model):
    db.Model.metadata.schema ='UNIVERSITY'
    __tablename__ ='Department'
    Department_Unique_ID = db.Column(db.String, primary_key=True)
    Department_Name = db.Column(db.String)
    uni_teacher = db.relationship('Teacher', backref=db.backref('Department'))

    __mapper_args__ = {
        "order_by":Department_Unique_ID
    }

class Teacher(db.Model):
    db.Model.metadata.schema ='UNIVERSITY'
    __tablename__ ='Teacher'
    Teacher_Unique_ID = db.Column(db.String, primary_key=True)
    Teacher_Name = db.Column(db.String)
    Department_ID_Unique = db.Column(db.String, db.ForeignKey('Department.Department_Unique_ID'))

    __mapper_args__ = {
        "order_by":Teacher_Unique_ID
    }

class DepartmentSchema(Schema):
    class Meta:
        type_ ='uni_department'
        self_view ='uni_department_detail'
        self_view_kwargs = {'id': '<Department_Unique_ID>'}
        self_view_many ='uni_department_list'
        strict = True
        __model__ = Department

    id = fields.Integer(as_string=True, many=True, dump_only=True) #not used, if I remove this, I get an error "Must have an 'id' field"

    Department_Unique_ID = fields.Str()
    Department_Name = fields.Str()
    uni_teacher = Relationship(self_view='uni_department_uni_teacher',
                             self_view_kwargs={'id':'<Department_Unique_ID>'},
                             related_view='uni_teacher_list',
                             related_view_kwargs={'id':'<Department_Unique_ID>'},
                             many=True,
                             schema='TeacherSchema',
                             type_='uni_teacher',
                              id_field='Teacher_Unique_ID')

class TeacherSchema(Schema):
    class Meta:
        type_ ='uni_teacher'
        self_view ='uni_teacher_detail'
        self_view_kwargs = {'id': '<Teacher_Unique_ID>'}
        self_view_many ='uni_teacher_list'
        strict = True
        __model__ = Teacher

    id = fields.Integer(as_string=True, many=True, dump_only=True) #not used, if I remove this, I get an error "Must have an 'id' field"

    Teacher_Unique_ID = fields.Str()
    Teacher_Name = fields.Str()
    owner = Relationship(attribute='uni_department',
                         self_view='uni_teacher_uni_department',
                         self_view_kwargs={'id':'<Teacher_Unique_ID>'},
                         related_view='uni_department_detail',
                         related_view_kwargs={'id':'<Department_Unique_ID>'},
                         schema='DepartmentSchema',
                         type_='uni_department')

class DepartmentList(ResourceList):
    schema = DepartmentSchema
    data_layer = {'session': db.session,
                  'model': Department}


class TeacherList(ResourceList):
    def query(self, view_kwargs):
        query_ = self.session.query(Teacher)
        if view_kwargs.get('Teacher_Unique_ID') is not None:
            try:
                self.session.query(Department).filter_by(Teacher_Unique_ID=view_kwargs['Teacher_Unique_ID']).one()
            except NoResultFound:
                raise ObjectNotFound({'parameter': 'Teacher_Unique_ID'}, "Teacher: {} not found".format(view_kwargs['Teacher_Unique_ID']))
            else:
                query_ = query_.join(Department).filter(Department.id == view_kwargs['Teacher_Unique_ID'])
        return query_

    def before_create_object(self, data, view_kwargs):
        if view_kwargs.get('Teacher_Unique_ID') is not None:
            uni_department = self.session.query(Department).filter_by(Teacher_Unique_ID=view_kwargs['Department_Unique_ID']).one()
            data['Department_Unique_ID'] = uni_department.Department_Unique_ID

    schema = TeacherSchema
    data_layer = {'session': db.session,
                  'model': Teacher}

class DepartmentDetail(ResourceDetail):
    def before_get_object(self, view_kwargs):
        if view_kwargs.get('Teacher_Unique_ID') is not None:
            try:
                uni_teacher = self.session.query(Teacher).filter_by(Department_Unique_ID=view_kwargs['Department_Unique_ID']).one()
            except NoResultFound:
                raise ObjectNotFound({'parameter': 'Department_Unique_ID'},
                                     "Teacher: {} not found".format(view_kwargs['Department_Unique_ID']))
            else:
                if uni_teacher.uni_department is not None:
                    view_kwargs['Department_Unique_ID'] = uni_teacher.uni_department.Department_Unique_ID
                else:
                    view_kwargs['Department_Unique_ID'] = None

    schema = DepartmentSchema
    data_layer = {'session': db.session,
                  'model': Department}


class TeacherDetail(ResourceDetail):
    schema = TeacherSchema
    data_layer = {'session': db.session,
                  'model': Teacher}

class DepartmentRelationship(ResourceRelationship):
    schema = DepartmentSchema
    data_layer = {'session': db.session,
                  'model': Department}

class TeacherRelationship(ResourceRelationship):
    schema = TeacherSchema
    data_layer = {'session': db.session,
                  'model': Teacher}

api = Api(app)
api.route(DepartmentList, 'uni_department_list', '/uni_department')
api.route(TeacherList, 'uni_teacher_list', '/uni_teacher', '/uni_teacher/<int:Department_Unique_ID>/uni_teacher')

api.route(DepartmentDetail, 'uni_department_detail', '/uni_department/<int:Department_Unique_ID>', '/uni_teacher/<int:Department_ID_Unique>/owner')
api.route(TeacherDetail, 'uni_teacher_detail', '/uni_teacher/<int:Teacher_Unique_ID>')

api.route(DepartmentRelationship, 'uni_department_uni_teacher', '/uni_department/<int:Department_Unique_ID>/relationships/uni_teacher')
api.route(TeacherRelationship, 'uni_teacher_uni_department', '/uni_teacher/<int:Teacher_Unique_Department>/relationships/owner')

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

I am following the example from flask-rest-jsonapi. Any help would be appreciated.

EDIT1- Here is the connectDB.py code as requested-

import yaml

def readDbDetails():
    with open('database_config.yaml', 'r') as stream:
        return (yaml.load(stream))


dbDetails = readDbDetails()


def connectToDb(app):

    database_host = dbDetails['database_host']
    database_username = dbDetails['database_username']
    database_name = dbDetails['database_name']
    database_password = dbDetails['database_password']
    database_port = dbDetails['database_port']
    print(database_host)
    print(database_name)
    print(database_username)
    print(database_password)
    print(database_port)

    app.config[
        'SQLALCHEMY_DATABASE_URI'] ='mssql+pymssql://'+database_username+':'+database_password+'@'+database_host+':'+database_port+'/'+database_name+''

    return app
  • Can you include the content of connectToDb(app) in the question? – arsho Apr 15 at 9:38
  • @arsho I have updated the question with connectToDb(app) code. – user2798227 Apr 15 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.