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

Question

I'm trying to learn Python / Flask / SQLAlchemy by creating a simple Wiki (largely based on the Flask-Admin example ), but I'm trying to figure out how to get a new column from my many-to-many relationship for display.

I successfully created a Wiki and created a many-to-many relationship table for tags without problems (and the tagging works correctly, as far as I saw), but I want to display the tags in a column and I can’t “It turned out that the logic worked out.

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

Here is an image of what I'm trying to accomplish:

Here is what I consider the appropriate 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 am referencing these documents (mostly trying to use backref options), 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

Answer 1

The relationship attribute loads the linked table into the list of objects, so you should be able to print all wiki elements and all related tags like this in the 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 this?

Answer 2

Not sure if this will help when I teach myself. But I had a similar problem when I wanted to display a column from the "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 know what I am doing (for now) I think that the bit that allows me to see the columns for my member model with the added extra column that came from the Grade model are these lines in the MemberAdmin (ModelView) class:

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

Answer 3

I needed to achieve an identical thing just now, which worked for me just adding the column_list field to the ViewModel class, so in this case it will be

 class WikiAdmin(sqla.ModelView): ... column_list = ('title', 'description', 'dataadded', 'tags') 

This is an old question, but this is the first thread (and maybe the only relevant one) that I came across trying to figure out how to do this, so maybe this answer will save time.