How to query data from relations tables and show them in json format

Question

I have data class for Coach And data class for User. How to query all the coach results with the list of users inside in json format. So e.g I want to achieve this:"{"coach_id":1,"coach_login":"Alan","subscribers":[ {"user_id":1,"user_login":"John"}]}"

@dataclass
class User(db.Model):
    __tablename__='user'
    user_id: int
    login: str
    user_id = db.Column(db.BigInteger, primary_key=True)
    login = db.Column(db.String(255))
    password_hash = db.Column(db.String(255))
    subscriptions = db.relationship('Coach', secondary=subs, backref='subscribers')

    @property
    def password(self):
        raise AttributeError('Password is not in valid format')

    @password.setter
    def password(self,password):
        self.password_hash = generate_password_hash(password)
    def varify_password(self, password):
        return check_password_hash(self.password_hash,password)
@dataclass
class Coach(db.Model):
    __tablename__='coach'
    coach_id: int
    login: str
    coach_id = db.Column(db.BigInteger, primary_key=True)
    login = db.Column(db.String(255))
    password_hash = db.Column(db.String(255))

    @property
    def password(self):
        raise AttributeError('Password is not in valid format')

    @password.setter
    def password(self,password):
        self.password_hash = generate_password_hash(password)
    def varify_password(self, password):
        return check_password_hash(self.password_hash,password)

score 0 · Answer 1 · answered Nov 30 '21 at 13:03

0

You can get you data with SqlAlchemy and then parse it to JSON thank to dataclass decorator :

# Endpoint to get a list of all users
@app.route("/api/users/", methods=['GET'])
def users():
    users = User.query.all()
    return jsonify(users)

answered Nov 30 '21 at 13:03

Unknow

178
2
2
12

Yes I know that and I'm using it at the moment but what I'm trying to achieve is: I created 2 data models (users and coaches). It's many to many relationship. Now I try to displays coaches data which will have list of users assign to that coach. When I query Coach.query.all() it will show me only coaches data(e.g id, login) but it doesn't show list of users which are assign to that coach. How can I query the data to achieve it? (json format result) – sneaky2 Nov 30 '21 at 13:25

score 0 · Answer 2 · answered Nov 30 '21 at 13:27

You can use Marshmallow (https://marshmallow.readthedocs.io/)

For flask-marshmallow: https://flask-marshmallow.readthedocs.io/en/latest/

marshmallow is an ORM/ODM/framework-agnostic library for converting complex datatypes, such as objects, to and from native Python datatypes.

You can initialize Marshmallow the same whay as SQLAlchemy (you can find good examples in the documentation)

Then you can use ma.SQLAlchemyAutoSchema to create schema for your table (for relationships: https://marshmallow.readthedocs.io/en/stable/nesting.html)

class UserSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model= User
        sqla_session = db.session

Then you can load a User in in a UserSchema and dump it

user = User.query.first()
user_schema = UserSchema()
user_schema.dump(user)

I used Marshmallow for it too. But still I didn't receive the result which I wanted.I have 2 data models (users and coaches). It's many to many relationship. Now I try to displays coaches data which will have list of users assign to that coach. When I used marshmallow schema and dump the result it will show me only coaches data(e.g id, login) but it doesn't show list of users which are assign to that coach. How can I query the data to achieve it? (json format result) — sneaky2, Nov 30 '21 at 13:30

How to query data from relations tables and show them in json format

2 Answers2