Use DB data model to generate SQLAlchemy models, schemas, and JSON response

Question

Using Flask and SQLAlchemy for a Python webapp, my goal is to create a system in which I can:

1. Import data models from an existing PostgreSQL DB, and map these to fields in corresponding SQLAlchemy models
2. Use these SQLAlchemy models to automatically generate a schema. This schema will then be used to perform data validation on user-submitted data.(I'm currently trying to use Marshmallow, but am open to other suggestions).
3. Perform JSON response formatting using the schema generated in step 2. (I'm currently trying to format my responses according to JsonAPI's schema - this could change if need be, but I would prefer it).

All of this will be packaged together in a single layer that should allow for simple data access, validation, and response formatting when writing an API, hopefully without ever having to manually define data models or schemas past the definitions that already exist in the DB. This leads me to my question:

Can I utilize existing frameworks to do everything that I'm trying to accomplish? I wouldn't expect that there's one single library that could do everything, but I'm hoping to be able to leverage several existing frameworks. However, I run into direct conflicts, particularly in steps 2 and 3. The stack I've been attempting to use so far is as follows:

• Flask (My web framework)
• SQLAlchemy (Used to access my data through reflection, see below)
• Flask-Marshmallow (To generate a schema for data validation)
• Marshmallow-JsonAPI (To format the JSON response according to the JsonAPI spec)

I have a solution for step 1: SQLAlchemy's reflection. This allows me to read the tables in an existing DB and map them to SQLAlchemy models. This works beautifully.

The combination of steps 2 and 3 is where it gets hazy - I'm trying to use Flask-Marshmallow and Marshmallow-JsonAPI. Flask-Marshmallow has the ModelSchema class, which allows you to generate a schema by passing it an existing SQLAlchemy Model. I can pass it my SQLAlchemy model that was generated in step 1, fulfilling my criteria for step 2. As for Marshmallow-JsonAPI, it has functionality such that you can define a schema for your model, and it will automatically create JSONAPI-compliant responses, fulfilling my criteria for step 3. I can individually get each of these frameworks to do what I need.

Unfortunately, since my schema has to inherit from Schema classes in both the Flask-Marshmallow and Marshmallow-JSONAPI, I run into issues. My Model for a "Dummy" class looks like this:

import flask
from marshmallow_jsonapi import Schema, fields
from sqlalchemy.ext.declarative import DeferredReflection
from flask_marshmallow import Marshmallow
from flask_sqlalchemy import SQLAlchemy

app = flask.Flask(__name__)
DB = SQLAlchemy()
DB.init_app(app)
MA = Marshmallow(app)
DeferredReflection.prepare(DB.get_engine(app))

class Dummy(DeferredReflection, DB.Model):
    __tablename__ = "dummy"  # This model will be reflected from the Dummy table


class DummySchema(MA.ModelSchema, Schema):  # The problem line
    class Meta:
        model = Dummy  # Create schema for the Dummy model

Upon trying to start up my API, I get strange errors like this:

api_1        | [2017-01-05 23:16:13,379] ERROR in app: Exception on /api/dummy [POST]
api_1        | Traceback (most recent call last):
api_1        |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1612, in full_dispatch_request
api_1        |     rv = self.dispatch_request()
api_1        |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1598, in dispatch_request
api_1        |     return self.view_functions[rule.endpoint](**req.view_args)
api_1        |   File "/usr/local/lib/python3.5/site-packages/flask_restful/__init__.py", line 477, in wrapper
api_1        |     resp = resource(*args, **kwargs)
api_1        |   File "/usr/local/lib/python3.5/site-packages/flask/views.py", line 84, in view
api_1        |     return self.dispatch_request(*args, **kwargs)
api_1        |   File "/usr/local/lib/python3.5/site-packages/flask_restful/__init__.py", line 587, in dispatch_request
api_1        |     resp = meth(*args, **kwargs)
api_1        |   File "/dummyproj/api/src/dummyproj/api/v1/dummy_resource.py", line 34, in post
api_1        |     dummy_schema = dummy.DummySchema()
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow_sqlalchemy/schema.py", line 143, in __init__
api_1        |     super(ModelSchema, self).__init__(*args, **kwargs)
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow_jsonapi/schema.py", line 81, in __init__
api_1        |     super(Schema, self).__init__(*args, **kwargs)
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow/schema.py", line 358, in __init__
api_1        |     self._update_fields(many=many)
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow/schema.py", line 750, in _update_fields
api_1        |     self.__set_field_attrs(ret)
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow/schema.py", line 772, in __set_field_attrs
api_1        |     self.on_bind_field(field_name, field_obj)
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow_jsonapi/schema.py", line 164, in on_bind_field
api_1        |     field_obj.load_from = self.inflect(field_name)
api_1        |   File "/usr/local/lib/python3.5/site-packages/marshmallow_jsonapi/schema.py", line 190, in inflect
api_1        |     return self.opts.inflect(text) if self.opts.inflect else text
api_1        | AttributeError: 'SchemaOpts' object has no attribute 'inflect'

It seems that since my Schema class inherits from two different superclasses, it causes issues. I would expect the two marshmallow libraries to be pretty compatible, but there does seem to be an issue with this.

The upshot of all of this is that I'm not quite sure what to try next. I'm not really expecting any specific advice with the Marshmallow frameworks, more just trying to show my train of thought. Is there any sort of best practice surrounding this sort of design, or am I trying to solve too much at once?

(New to both Python and SO - apologies if any of this is unclear).

Answer 1

I'm successfully combining marshmallow-jsonapi + marshmallow-sqlalchemy. Here's the magic:

import marshmallow
import marshmallow_jsonapi
import marshmallow_jsonapi.flask
import marshmallow_sqlalchemy

import myapp.database as db


def make_jsonapi_schema_class(model_class):
    class SchemaOpts(marshmallow_sqlalchemy.SQLAlchemyAutoSchemaOpts, marshmallow_jsonapi.SchemaOpts):
        pass

    class Schema(marshmallow_sqlalchemy.SQLAlchemyAutoSchema, marshmallow_jsonapi.flask.Schema):
        OPTIONS_CLASS = SchemaOpts

        @marshmallow.post_load
        def make_instance(self, data, **kwargs):
            # Return deserialized data as a dict, not a model instance
            return data

        # You can add default behavior here, for example
        # id = fields.Str(dump_only=True)

    # https://marshmallow-sqlalchemy.readthedocs.io/en/latest/recipes.html#automatically-generating-schemas-for-sqlalchemy-models
    class Meta:
        # Marshmallow-SQLAlchemy
        model = model_class
        sqla_session = db.session

        # Marshmallow-JSONAPI
        type_ = model_class.__name__.lower()
        self_view = type_ + '_detail'
        self_view_kwargs = {'id': '<id>'}
        self_view_many = type_ + '_list'

    schema_class = type(model_class.__name__ + 'Schema', (Schema,), {'Meta': Meta})
    return schema_class

You then call

FooSchema = make_jsonapi_schema_class(Foo)

to generate the Marshmallow schema class from your SQLAlchemy declarative model. You can then in turn subclass that class if you want to customize it.

(To implement the REST API, I'm using Flask-REST-JSONAPI, which is built on top of Flask + SQLAlchemy + marshmallow-jsonapi.)