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
