Python Flask API : How to send back selective columns from the resultset of a sqlalchemy join

Question

I'm trying to do a simple code snippet to print the result of a 3 tables join query. I'm using python/flask-restplus/sqlalchemy

Here is my example:

class Users(db.Model):
    id = db.Column(db.Integer, db.Sequence('users_id_seq'), primary_key=True)
    email = db.Column(db.String(64), unique=True, nullable=False)
    password = db.Column(db.String(128), nullable=False)
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    expires = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    rights = db.relationship('Rights', backref='user', lazy=True)

    def __repr__(self):
        return '<Users %r>' % self.email

class Applications(db.Model):
    id = db.Column(db.Integer, db.Sequence('applications_id_seq'), primary_key=True)
    code = db.Column(db.String(5), unique=True, nullable=False)
    name = db.Column(db.String(128), nullable=False)
    rights = db.relationship('Rights', backref='application', lazy=True)

    def __repr__(self):
        return '<Applications %r>' % self.code

class Rights(db.Model):
    id = db.Column(db.Integer, db.Sequence('rights_id_seq'), primary_key=True)
    role = db.Column(db.String(16), nullable=False)
    app_id = db.Column(db.Integer, db.ForeignKey('applications.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    def __repr__(self):
        return '<Rights %r>' % self.role

db.drop_all()
db.create_all()
user1 = Users(email='[email protected]', password='password1')
user2 = Users(email='[email protected]', password='password2')
user3 = Users(email='[email protected]', password='password3')
appli1 = Applications(code='APP1', name='Appli 1')
appli2 = Applications(code='APP2', name='Appli 2')
appli3 = Applications(code='APP3', name='Appli 3')
db.session.add(user1)
db.session.add(user2)
db.session.add(user3)
db.session.add(appli1)
db.session.add(appli2)
db.session.add(appli3)

u1=Users.query.get(1)
a1=Applications.query.get(1)
rights1 = Rights(role='MANAGER', application=a1, user=u1)
db.session.add(rights1)

a2=Applications.query.get(2)
rights2 = Rights(role='USER', application=a2, user=u1)
db.session.add(rights2)

u2=Users.query.get(2)
rights3 = Rights(role='MANAGER', application=a2, user=u2)
db.session.add(rights3)

u3=Users.query.get(3)
a3=Applications.query.get(3)
rights4 = Rights(role='USER', application=a1, user=u3)
rights5 = Rights(role='USER', application=a2, user=u3)
rights6 = Rights(role='USER', application=a3, user=u3)
db.session.add(rights4)
db.session.add(rights5)
db.session.add(rights6)
db.session.commit()

nsmanage = api.namespace('manage', description='Api Management Related Operations')

rightresp = api.model('Rights_Response', {
    'email': fields.String(required=True, description='Login/Email'),
    'role': fields.String(required=True, description='[user|admin|appmanager]'),
    'code': fields.String(required=True, description='3-letter app code')
})

@nsmanage.route('/rights/<string:email>')
@nsmanage.param('email', "User's Email")
@nsmanage.response(404, 'User not found')
class GetRightsByEmail(Resource):
    @nsmanage.marshal_with(rightresp)
    def get(self, email):
        '''Fetch a user's rights given its email'''
        query = Rights.query.join(Applications, Users).options(contains_eager('user'),contains_eager('application')).filter(Users.email == email).all()
        for i in query:
            print (i.role,i.user.email,i.application.code)
        return query
        api.abort(404, message="User {} not found".format(email))

Using swagger or curl command: curl -X GET --header 'Accept: application/json' 'http://dbbdevdb1369:5000/manage/rights/user1%40example.com'

returns :

[
  {
    "email": null,
    "role": "MANAGER",
    "code": null
  },
  {
    "email": null,
    "role": "USER",
    "code": null
  }
]

which is obviously not what I want. the query is ok because the for loop before the line "return query" prints the correct answer:

MANAGER [email protected] APP1
USER [email protected] APP2

the rightresp model I use with marshal_with has the correct columns (found in query)

I'm sure this is a newb question (which I am) but I cant find out how to send back the correct answer using the api, ie:

[
  {
    "email": "[email protected]",
    "role": "MANAGER",
    "code": "APP1"
  },
  {
    "email": "[email protected]",
    "role": "USER",
    "code": "APP2"
  }
]

Thanks a lot. Laurent

Edit: In the database the query generated is ok (the join is ok and the columns I want are selected):

SELECT rights.id AS rights_id,
       rights.role AS rights_role,
       rights.app_id AS rights_app_id,
       rights.user_id AS rights_user_id,
       users.id AS users_id,
       users.email AS users_email,
       users.password AS users_password,
       users.created AS users_created,
       users.expires AS users_expires,
       applications.id AS applications_id,
       applications.code AS applications_code,
       applications.name AS applications_name
FROM rights JOIN applications ON applications.id = rights.app_id 
            JOIN users ON users.id = rights.user_id
WHERE users.email = :email_1

Answer 1

Your query for email and code does not return a string, but instances of class User and Application instead. I'm unfamiliar with marshal, so I'm not really following whats going on there. You might want to change your query to:

user = User.query.filter_by(email=email).first()

You can get access to its rights by user.rights, and to its app by user.rights[index].app_id

You then might want to loop over their rights, and add them to a dictionary and send back a jsonified version version of that dictionary or something of the like.