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?

Sergey Shubin · Accepted Answer · 2017-03-13 13:39:37Z

3

+50

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.

edited Mar 13, 2017 at 13:39

answered Mar 13, 2017 at 11:09

Sergey Shubin

3,2684 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rohit Jain Over a year ago

Ah! Got it. Just made one of my columns a primary key in the model itself, and it worked. I see it's not needed to be primary key in view itself.

Rohit Jain Over a year ago

Thank you so much. Got the dashboard up and running. Looks like the previous answer was also correct.

melchoir55 · Accepted Answer · 2017-03-13 08:29:54Z

1

I think it would help if you were to post the rest of the file in which you define the model. You might be extending the wrong class(sqa.Model).

Your ModelView also has no columns listed. I think that might be required?

Here's the hello_world documentation for flask_admin. You can see they explicitly specify columns, filters, and searchability in the ModelView.

Excerpt:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(128))

class UserView(ModelView):
    # Show only name and email columns in list view
    column_list = ('name', 'email')

    # Enable search functionality - it will search for terms in
    # name and email fields
    column_searchable_list = ('name', 'email')

    # Add filters for name and email columns
    column_filters = ('name', 'email')

answered Mar 13, 2017 at 8:29

melchoir55

7,3768 gold badges69 silver badges112 bronze badges

2 Comments

Rohit Jain Over a year ago

Just to clarify my question again, I want to create ModelView on database view, not table. You're creating on normal table.

Rohit Jain Over a year ago

BTW, in postgres, a database view cannot have primary key.

Anand Tripathi · Accepted Answer · 2017-03-16 11:30:42Z

0

Admin view can be achieved with sandman library in Python.Here is the link for that sandman

Here's the code that uses flask-admin. But first you have to install library by command
pip install sandman

from sandman2 import app

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost/testsmsmagic'
#path of your database.

from sandman2.model import activate

activate()

app.run(port=5999)

run that file after that you can see your admin portal on browser with http://localhost:5999/admin

edited Mar 16, 2017 at 11:30

answered Mar 16, 2017 at 11:24

Anand Tripathi

16.4k2 gold badges52 silver badges57 bronze badges

Collectives™ on Stack Overflow

Flask Admin on postgres database view

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related