starred/sandman2-jeffknupp

Fork 0

mirror of https://github.com/jeffknupp/sandman2.git synced 2026-04-25 00:25:49 +03:00

[GH-ISSUE #45] Including views along with tables #30

New issue

Open

opened 2026-02-26 01:32:56 +03:00 by kerem · 6 comments

kerem commented

2026-02-26 01:32:56 +03:00

Owner

Originally created by @chris-hailstorm on GitHub (Sep 27, 2016).
Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/45

How can Sandman2 include both views and tables in the API?

-- can views be auto-discovered the same way tables are?

-- is there an example of a user-defined model for a view?

Originally created by @chris-hailstorm on GitHub (Sep 27, 2016). Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/45 How can Sandman2 include both views and tables in the API? -- can views be auto-discovered the same way tables are? -- is there an example of a user-defined model for a view?

kerem added the

enhancement

research

labels

2026-02-26 01:32:56 +03:00

kerem commented

2026-02-26 01:32:57 +03:00

Author

Owner

@hanielburton commented on GitHub (Sep 25, 2018):

I'm also interested in this, most of the time we will create views to pre-format data rather than accessing tables directly.

@hanielburton commented on GitHub (Sep 25, 2018): I'm also interested in this, most of the time we will create views to pre-format data rather than accessing tables directly.

kerem commented

2026-02-26 01:32:57 +03:00

Author

Owner

@Carelvd commented on GitHub (Nov 5, 2019):

Just to clarify, by view do you mean separate HTML a representation; that is neither the JSON response nor the Admin view (also in HTML).

If so one replaces the Flask application with an instance of Flask_API a variant of Django's rest framework which allows for a navigable API yet still serves data in the desired format (XML/YAML/CSV formats are then aslo possible in this case). I have used this but there are either a few quirks in the package or I had mis-configured it. Then there are 2-3- points in the Sandman 2 code base that jsonify the data into an HTML representation; one has to disable this behaviour to get things operational. Flask_API only intercepts the standard Python data structures and wraps them into the request format, if the underlying view sends HTML already it ignores the response and lets it pass through to the client as is.

@Carelvd commented on GitHub (Nov 5, 2019): Just to clarify, by view do you mean separate HTML a representation; that is neither the JSON response nor the Admin view (also in HTML). If so one replaces the ``Flask`` application with an instance of [``Flask_API``](https://www.flaskapi.org/) a variant of Django's rest framework which allows for a navigable API yet still serves data in the desired format (XML/YAML/CSV formats are then aslo possible in this case). I have used this but there are either a few quirks in the package or I had mis-configured it. Then there are 2-3- points in the Sandman 2 code base that `jsonify` the data into an HTML representation; one has to disable this behaviour to get things operational. Flask_API only intercepts the standard Python data structures and wraps them into the request format, if the underlying view sends HTML already it ignores the response and lets it pass through to the client as is.

kerem commented

2026-02-26 01:32:57 +03:00

Author

Owner

@chris-hailstorm commented on GitHub (Nov 5, 2019):

No, this has nothing whatsoever to do with UI. This is referring to database views, ie virtual tables / queries embodied in the database as if they were tables. See https://en.m.wikipedia.org/wiki/View_(SQL). In a relational database, a majority of operations allowed for tables can also be done with views. It’s a strange limitation of sandman2 that it doesn’t handle views.

@chris-hailstorm commented on GitHub (Nov 5, 2019): No, this has nothing whatsoever to do with UI. This is referring to database views, ie virtual tables / queries embodied in the database as if they were tables. See https://en.m.wikipedia.org/wiki/View_(SQL). In a relational database, a majority of operations allowed for tables can also be done with views. It’s a strange limitation of sandman2 that it doesn’t handle views.

kerem commented

2026-02-26 01:32:57 +03:00

Author

Owner

@hanielburton commented on GitHub (Nov 22, 2019):

Like @chris-hailstorm, I was also referring to SQL views. Placing SQL logic in a view allows the underlying RDBMS to make the best access decision rather than having to pull from multiple tables and passing on the logic to the application layer, which reduces latency, unnecessary network requests, and database I/O.

@hanielburton commented on GitHub (Nov 22, 2019): Like @chris-hailstorm, I was also referring to SQL views. Placing SQL logic in a view allows the underlying RDBMS to make the best access decision rather than having to pull from multiple tables and passing on the logic to the application layer, which reduces latency, unnecessary network requests, and database I/O.

kerem commented

2026-02-26 01:32:57 +03:00

Author

Owner

@Carelvd commented on GitHub (Jun 11, 2020):

I saw a remark in the SQL Alchemy documentation that you can readily treat a view as an additional table (See Reflecting Views.) SQL Alchemy should detect these automatically when sandman is invoked. If not one will have to force the issue by sub-classing the Automap'd Base class provided by sandman and explicitly name the view as the table argument. It will probably be necessary to specify a PrimaryKeyConstraint for the view as well if there is no natural key or if the view has none.

class SQLVIEW(AutomapModel) :

    # ID = Column('ASTId', INTEGER, nullable=False, link_to_name=True)
    __endpoint__   = "sqlview"
    __tablename__  = "SQLVIEW"
    __model_view__ = ModelView
    # __table_args__ = ( PrimaryKeyConstraint('ID'), )

Additionally you may have to mark the view as read only or support writing to it on the SQL side, YMMV.

There are a few related projects upon PYPI, this is more for the developers then the users information:

The latter seems more relevant in all honesty but I have dug into neither.

@Carelvd commented on GitHub (Jun 11, 2020): I saw a remark in the SQL Alchemy documentation that you can readily treat a view as an additional table (See [Reflecting Views](https://docs.sqlalchemy.org/en/13/core/reflection.html#reflecting-views).) SQL Alchemy should detect these automatically when sandman is invoked. If not one will have to force the issue by sub-classing the Automap'd Base class provided by sandman and explicitly name the view as the table argument. It will probably be necessary to specify a `PrimaryKeyConstraint` for the view as well if there is no natural key or if the view has none. ``` class SQLVIEW(AutomapModel) : # ID = Column('ASTId', INTEGER, nullable=False, link_to_name=True) __endpoint__ = "sqlview" __tablename__ = "SQLVIEW" __model_view__ = ModelView # __table_args__ = ( PrimaryKeyConstraint('ID'), ) ``` Additionally you may have to mark the view as read only or support writing to it on the SQL side, YMMV. There are a few related projects upon PYPI, this is more for the developers then the users information: * [SQLALchemy Views](https://pypi.org/project/sqlalchemy-views/) * [Flask-Alchemy](https://pythonhosted.org/Flask-AlchemyView/) The latter seems more relevant in all honesty but I have dug into neither.

kerem commented

2026-02-26 01:32:57 +03:00

Author

Owner

@Carelvd commented on GitHub (Jun 11, 2020):

The other way to go is to create a view in the SQL Alchemy layer, If you're sub-classing the declarative base/Auto-mapped model you can pass it a query of your own making in lieu of a table/view and let the class represent that.

@Carelvd commented on GitHub (Jun 11, 2020): The other way to go is to create a view in the SQL Alchemy layer, If you're sub-classing the declarative base/Auto-mapped model you can pass it a query of your own making in lieu of a table/view and let the class represent that.