How to select only some of the columns from the database with Flask-SQLAlchemy?

Question

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?

score 4 · Accepted Answer · answered Jun 16 '19 at 17:07

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

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. — code_dredd, Oct 04 '19 at 18:30

score 2 · Answer 2 · answered Apr 11 '19 at 15:06

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)

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? — UrmLmn, Apr 11 '19 at 15:18
It is not statically defined. you can pass it in the request header. I showed a way to have default specified. curl -X GET "http://localhost:5000/mymodel" -H "accept: application/json" -H "X-Fields: first_column" — Ravishankar Sivasubramaniam, Apr 11 '19 at 15:55
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. — UrmLmn, Apr 11 '19 at 16:04

How to select only some of the columns from the database with Flask-SQLAlchemy?

2 Answers2