2

I'm developing an API with flask_restplus and flask_sqlalchemy, and I have a special case where certain applications should access only certain columns from the API.

I have a model:

class MyModel(db.Model):
    __tablename_ = 'my_table'
    id = db.Column(db.Integer, primary_key=True)
    first_column = db.Column(db.Unicode)
    second_column = db.Column(db.Unicode)

I also have specified a flask_resplus' model to be returned from the API:

my_model = api.model('MyModel',
                    {'first_column': fields.String(),
                     'second_column': fields.String()})

Where api is a flask_restplus' Api instance and db a flask_sqlachmey's instance.

Sometimes I want to select only some of the columns, and for the other column to be null in the api.model that is returned as a JSON repsonse.

After searching on the Internet, I found two methods which neither of them work in my case: load_only() from sqlalchemy.orm, which returns the columns as a list. As a result, I can't return those results, since my model expects a dictionary with same keys as described in my_model. The other method, with_entities() returns an instance of MyModel, which is what I need, but it loads all the columns at the time I pass that instance to the my_model, since it does only a lazy selection, i.e. it selects the specified columns, but if the other columns are required, it does a query again to get the values of other columns, thus loading, in my case, all the columns, which is not what I want.

How can I do a SQL SELECT where only some of the columns are returned and the result is an instance of the db.Model?

Improve this question

2 Answers 2

Reset to default
4

If you use flask-marshmallow for serialization, you can load the schema with a list of included (only()) or excluded (load_only()) fields.

So a rough mock-up with what you've got above:

from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow

db = SQLAlchemy()
ma = Marshmallow()

class MyModel(db.Model):
    __tablename_ = 'my_table'
    id = db.Column(db.Integer, primary_key=True)
    first_column = db.Column(db.Unicode)
    second_column = db.Column(db.Unicode)


class MyModelSchema(ma.ModelSchema):
    class Meta:
        model = MyModel

varlist = ['id','first_column']

def somefunction():
    results = MyModel.query.all()
    mymodelschema = MyModelSchema(many=True, only=varlist)
    output = mymodelschema.dump(results).data
    return jsonify(output) # or whatever you're doing with it

Here are the docs for the Marshmallow Schema API where there are a few options including excluding everything in a list, for example: https://marshmallow.readthedocs.io/en/latest/api_reference.html#marshmallow.Schema

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In the Meta class of the ModelSchema, you can specify the exclude = ('field1', 'field2', ..., 'fieldN') tuple to exclude the specified fields from the serialized output.
2

You can use Field Masking in flask-restplus to access only the field you want.

Check Out this link: https://flask-restplus.readthedocs.io/en/stable/mask.html

curl -X GET "http://localhost:5000/mymodel" -H  "accept: application/json" -H  "X-Fields: first_column"

from flask import Flask
from flask_restplus import Resource, Api
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
import os
from flask_restplus import Resource, fields

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///{0}/app-dev.db'.format(
    basedir)
app.config['SECRET_KEY'] = '\xa4?\xca`\xa4~zG\xdf\xdbh\xba\xc2\xc6\xfc\x88\xc6x"\x11\xe8X8\n'

db = SQLAlchemy(app)
api = Api(app)


class MyModel(db.Model):
    __tablename_ = 'my_table'
    id = db.Column(db.Integer, primary_key=True)
    first_column = db.Column(db.String(255))
    second_column = db.Column(db.String(255))


my_model = api.model('MyModel',
                     {'first_column': fields.String(),
                      'second_column': fields.String()})


@api.route('/mymodel')
class GetModel(Resource):
    @api.marshal_with(my_model, envelope='resource', mask='first_column')
    def get(self, **kwargs):
        return MyModel.query.all()  # Some function that queries the db


@api.route('/hello')
class HelloWorld(Resource):
    def get(self):
        return {'hello': 'world'}


if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    model1 = MyModel(first_column='m1_first', second_column='M1_SECOND')
    model2 = MyModel(first_column='m2_first', second_column='M2_SECOND')
    db.session.add(model1)
    db.session.add(model2)
    db.session.commit()
    app.run(debug=True)
Improve this answer

3 Comments

Thank you for your answer! In my case, I would need a way to dynamically specify the columns in the mask parameter given in the marshal_with() method, depending on the application it makes the request. That is, I want to specify which application has which rights to get which columns, not the other way around. In your code, the columns are statically definded, i.e. beforehand. Is there a way to do that programmatically?
It is not statically defined. you can pass it in the request header. I showed a way to have default specified. curl -X GET "localhost:5000/mymodel" -H "accept: application/json" -H "X-Fields: first_column"
Yes, but in my case I need to specify which columns to returned, not the user. Since the applications that use the API need to be authenticated, I know which applications are making the request, thus I would also like to implement some kind of authorization, where certain applications have the right to access only certain columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.