python - Flask-SQLAlchemy returning object in query when working with many-to-many relationship

Question

I'm new with SQLAlch and I'm trying to do a simple query in my database but i'm getting objects in response instead of strings. My data model is the following:

wallet_tags_association = db.Table(
    'wallet_tags', db.Model.metadata,
    db.Column('wallet_id', db.Integer, db.ForeignKey('wallet.id')),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'))
)


class WalletData(db.Model):
    __tablename__ = 'wallet'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    total_value = db.Column(db.DECIMAL)
    tags = db.relationship('Tags', secondary='wallet_tags')

    def __init__(self, name):
        self.name = name

class Tags(db.Model):
    __tablename__ = 'tags'

    id = db.Column(db.Integer, primary_key=True)
    tag_name = db.Column(db.String(20))

    def __init__(self, tag_name):
        self.tag_name = tag_name

and the query I'm trying to do is the following:

wallet_tags = WalletData.query.join(Tags, WalletData.tags).all()
for u in wallet_tags:
   print(u.tags)

And this is what I got after iterating...

[<Tags: dividends>, <Tags: value>, <Tags: high yield>]

I have tried to follow the SQLAlch docs and there the approach is to use labels. Couldn't find a way to use labels when querying with Models.

Any help will be highly appreciated

Thanks in advance

question from:https://stackoverflow.com/questions/65931353/flask-sqlalchemy-returning-object-in-query-when-working-with-many-to-many-relati

Answer 1

The corrected query for this needs to be:

wallet_tags = WalletData.query.join(Tags, WalletData.tags).all()
for u in wallet_tags:
   print(', '.join([tag.tag_name for tag in u.tags]))

Explanation: There is a many-to-many join between the two main tables (an intermediate table was constructed). The relationship expression on WalletData will pick up a list of connected Tags objects. Iterating through this collection for each WalletData object will yield the desired set of record field names.