Flask Admin on postgres database view

Question

Is there a way I can create admin view on Flask-Admin on a database view? I'm using Postgres, and have created a view, let's take the below one as example:

CREATE VIEW test_view AS (
    SELECT id, name, age, score
    FROM student
);

If I want to setup admin view for that, how would I do it?

I tried creating an abstract model for it:

class StudentView(sqa.Model):
    __abstract__ = True
    __tablename__ = 'test_view'

    id = sqa.Column(sqa.Integer)
    name = sqa.Column(sqa.String)
    age = sqa.Column(sqa.Integer)
    score = sqa.Column(sqa.Integer)

And added admin view like this:

from flask_admin.contrib.sqla import ModelView

class StudentViewAdmin(ModelView):
    _model = models.StudentView

Registering this admin view to admin:

admin.add_view(admin_views.StudentViewAdmin(admin_views.DashboardView123._model, db.session))

But, I'm getting following error, while adding the view to admin:

Traceback (most recent call last):
  File "manage.py", line 8, in <module>
    setup_admin(app)
  File "/Users/rohitjain/app.py", line 35, in setup_admin
    admin.add_view(admin_views.StudentViewAdmin(admin_views.StudentViewAdmin._model, db.session))
  File "/Users/rohitjain/.venvs/venv/lib/python3.5/site-packages/flask_admin/contrib/sqla/view.py", line 318, in __init__
    menu_icon_value=menu_icon_value)
  File "/Users/rohitjain/.venvs/venv/lib/python3.5/site-packages/flask_admin/model/base.py", line 771, in __init__
    self._refresh_cache()
  File "/Users/rohitjain/.venvs/venv/lib/python3.5/site-packages/flask_admin/model/base.py", line 847, in _refresh_cache
    self._list_columns = self.get_list_columns()
  File "/Users/rohitjain/.venvs/venv/lib/python3.5/site-packages/flask_admin/model/base.py", line 979, in get_list_columns
    only_columns=self.column_list or self.scaffold_list_columns(),
  File "/Users/rohitjain/.venvs/venv/lib/python3.5/site-packages/flask_admin/contrib/sqla/view.py", line 404, in scaffold_list_columns
    for p in self._get_model_iterator():
  File "/Users/rohitjain/.venvs/venv/lib/python3.5/site-packages/flask_admin/contrib/sqla/view.py", line 340, in _get_model_iterator
    return model._sa_class_manager.mapper.iterate_properties
AttributeError: type object 'StudentView' has no attribute '_sa_class_manager'

Can we actually do this? I've done this in Django, setting up admin for a postgres db view, but am unable to setup the same in Flask till now. Any leads?

Answer 1

Is there a reason to create an abstract model? __abstract__ attribute set to True is the cause of this error. If you don't subclass from this model you don't need it:

class StudentView(sqa.Model):
    __tablename__ = 'test_view'

    id = sqa.Column(sqa.Integer, primary_key=True)
    name = sqa.Column(sqa.String)
    age = sqa.Column(sqa.Integer)
    score = sqa.Column(sqa.Integer)

Also SQLAlchemy mapper needs a primary key (in model not in actual view). The application will treat your view as a table so it won't produce database errors as soon as it doesn't try to update the view.