How to display column with results from many-to-many query in Flask/SQLAlchemy

Question

I am trying to learn Python/Flask/SQLAlchemy by building a simple Wiki (heavily based off of a Flask-Admin example) but am struggling to understand how to get a new column from my many-to-many relationship to display.

I have successfully created the Wiki and have created a many-to-many relationship table for tags with no problem (and tagging works properly as far as I have seen), but I want to display the tags as a column and can't get the logic worked out.

GOAL: I want to display a column that shows the tags that are referenced by the many-to-many association table.

Here is a picture of what I am trying to accomplish:

Here is what I believe to be the relevant code:

wiki_tags_table = db.Table('wiki_tags', db.Model.metadata,
                           db.Column('wiki_id', db.Integer, db.ForeignKey('wiki.id')),
                           db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'))
                           )

class Wiki(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), unique=True)
    description = db.Column(db.Text)
    path = db.Column(db.Unicode(256))
    date_added = db.Column(db.DateTime)
    tags_id = db.Column(db.Integer, db.ForeignKey('tag.id'))
    tags = db.relationship('Tag', secondary=wiki_tags_table, backref=db.backref('wiki_tags_table', lazy='dynamic'))

    def __unicode__(self):
        return self.item

class WikiAdmin(sqla.ModelView):

    column_exclude_list = ['path']

    column_hide_backrefs = False

    form_overrides = {
        'path': form.FileUploadField
    }

    form_args = {
        'path': {
            'label': 'File',
            'base_path': file_path
        }
    }

    column_searchable_list = ('title', 'description', 'path')

    def __init__(self, session):
        super(WikiAdmin, self).__init__(Wiki, session)

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(64))

    def __unicode__(self):
        return self.name

I have been referencing these documents (mostly trying variations of backref) but haven't figured it out yet:

• http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-viii-followers-contacts-and-friends
• http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html

Skidway

Answer 1

Not sure if this will help as I'm learning myself. But I had a similar issue where I wanted to display a column from a 'foreignkey table' and did it like this:

My modle.py

  from app import db

class Member(db.Model):
    __tablename__ = 'members'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(64), index=True)
    phone = db.Column(db.String(10), index=True)
    email = db.Column(db.String(120), index=True, unique=True)
    grade = db.relationship('Grade', backref='member')
    attendance = db.relationship('Attendance', backref='member')

    def __repr__(self):
        return '<User %r>' % self.name


class Grade(db.Model):
    __tablename__ = 'grades'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    member_id = db.Column(db.Integer, db.ForeignKey('members.id'))
    grade = db.Column(db.String(10))
    grade_date = db.Column(db.Date)

    def __repr__(self):
        return '<Grading %r>' % self.id

    def __str__(self):
        return self.grade

    def __unicode__(self):
        return self.grade


class Attendance(db.Model):
    __tablename__ = 'attendance'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    id_member = db.Column(db.Integer, db.ForeignKey('members.id'))
    attend_date = db.Column(db.Date)

    def __repr__(self):
        return '<Attenance %r>' % self.id

my views.py

from app.models import Member, Grade, Attendance
from app import app, admin, db
from flask_admin import BaseView, expose
from flask_admin.contrib.fileadmin import FileAdmin
from flask_admin.contrib.sqla import ModelView
import os.path as op


class AdminView(ModelView):
    column_display_pk = True  # optional, but I like to see the IDs in the list
    column_hide_backrefs = False
    # column_list = ('id', 'name', 'parent')
    create_modal = True
    edit_modal = True


class MemberAdmin(ModelView):
    column_display_pk = True  # optional, but I like to see the IDs in the list
    column_hide_backrefs = False
    can_view_details = True
    create_modal = True
    edit_modal = True
    form_columns = ['name', 'phone', 'email', 'grade', 'attendance']
    column_details_list = ['name', 'phone', 'email', 'grade', 'attendance']
    column_searchable_list = ['name', 'email']
    column_list = ('id', 'name', 'phone','email','grade')


class GradeAdmin(ModelView):
    column_display_pk = True  # optional, but I like to see the IDs in the list
    column_hide_backrefs = False
    column_list = ('id', 'member', 'grade', 'grade_date')
    form_choices = {'grade': [('Beginner', 'Beginner'), ('Yellow', 'Yellow'), ('Orange', 'Orange'),
                              ('Green 1', 'Green 1'), ('Green 2', 'Green 2'), ('Blue 1', 'Blue 1'),
                              ('Blue 2', 'Blue 2'), ('Purple 1', 'Purple 1'), ('Purple 2', 'Purple 2'),
                              ('Brown 1', 'Brown 1'), ('Brown 2', 'Brown 2'), ('Red 1', 'Red 1')]}


admin.add_view(MemberAdmin(Member, db.session, endpoint='member', category='Admin'))
admin.add_view(GradeAdmin(Grade, db.session, endpoint='grades', category='Admin'))
admin.add_view(ModelView(Attendance, db.session, endpoint='attendance', category='Admin'))

As I don't really know what I'm doing (yet) I think the bit that let me see the columns for my Member model with the extra column added which came from the Grade model was these lines in class MemberAdmin(ModelView):

column_hide_backrefs = False
can_view_details = True
...
form_columns = ['name', 'phone', 'email', 'grade', 'attendance']

Ceresin

Answer 2

The relationship attribute loads the related table as a list of objects, so you should be able to print all wiki items and all associated tags like this in a view:

for wiki_item in Wiki.query.all():
    print wiki_item.title
    for tag in wiki_item.tags:
        print tag.name 

What happens if you try that?

Bedsore

Great, didn't know about that, and it does display the tags properly in my terminal (shoved in under the WikiAdmin init). So, one more part to it that I am not grasping: where/how do you create the new column with the tags displayed correctly? I thought you would put it under the Wiki model, but I haven't seen any examples of something close to that. Thanks for the help! – Skidway

Hm, I'm not 100% sure I understand the question. Do you mean how do you set tags? You should be able to just assign to the relationship attribute: wiki_item.tags = [Tag("first tag"), Tag("second tag")] – Bedsore

Sorry, that was a butchered way of just saying "I still am not following where I would insert that to achieve getting a column added where the red lettering is in my picture" ;) – Skidway

@Skidway did you ever figure this out? – Sulfapyrazine

@alisonS no, not really. I became disillusioned with Flask for a while and actually moved this project over Nodejs, Angular, and Sequelize (an ORM for Nodejs). Sorry! – Skidway

Answer 3

I needed to achieve identical thing just now, what worked for me is just adding column_list field in ViewModel class, so in this case, that would be

class WikiAdmin(sqla.ModelView):

    ...
    column_list = ('title', 'description', 'dataadded', 'tags')

This is old question, but this is first thread (and, arguably, the only relevant) I stumbled upon while trying to figure out for myself how to do it, so maybe this answer will save someone's time

Fan

Answer 4

Try something like what I've done, with a special column formatter for the many-to-many fields. This works for me.

def many_to_many_formatter(view, context, model, name: str) -> str:
    """This function formats the many-to-many fields for display in the list view"""
    try:
        rel_field, col_name = name.split(".")
        list_of_tags: list = getattr(model, rel_field)
        list_of_results: list = [str(getattr(tag, col_name)) for tag in list_of_tags]
        new_str: str = ", ".join(list_of_results)
    except Exception:
        current_app.logger.exception("Error trying to format many-to-many relationship field in list view!")
        new_str = ""

    return new_str


class SpecialView(ModelView):
    """Flask-Admin view"""

    column_list = (
        "model",
        "description",
        # many-to-many field
        "parts_rel.part_num"
    )

    column_formatters = {
        "parts_rel.part_num": many_to_many_formatter,
    }

Urumchi