Repository navigation
How to query View in sqlmodel #258
Description
Activity
I would certainly not recommend it, but if you really want / must, change you class definition into
HeroTeamView(SQLModel, table=True). You might have to add__tablename__ = "viewname"and define a primary key to satisfy sqlalchemy.SQLModel is SQLAlchemy declarative_base, so you often find the solution by searching for that instead. This SO answer is informative: https://stackoverflow.com/a/53253105
Has an example that uses SQLAlchemy-utils, based on an example there - this includes the view definition part also:
class ArticleView(Base): __table__ = create_view( name='article_view', selectable=sa.select( [ Article.id, Article.name, User.id.label('author_id'), User.name.label('author_name') ], from_obj=( Article.__table__ .join(User, Article.author_id == User.id) ) ), metadata=Base.metadata )
There's also https://pypi.org/project/sqlalchemy-views/ but I understood that it does not have ORM support, like SQLAlchemy-utils does.
Haven't tested these, we considered and it's still an option, but we now just try by filtering some queries and relationships instead as the case is pretty simple.
@byrman - why would you not recommend what you say? AFAIK the solution I mentioned above boils down to the same pretty much? Maybe the lib impl there actually does more though.
Reacted by Jie (Edward) Xu and Davidwhy would you not recommend what you say?
Wasn't the question about how to do it using SQLModel on an existing view?
Reacted by konstantinTo clarify, I tried to map a view directly on a single
HeroTeamViewmodel. This actually works, because for the generated SQL it doesn't matter whether it selects from a table or a view. Your proposed solution, @antont, looks promising / better, but I'm still struggling with it.@byrman you are right, my answer is different that way - it includes both parts.
AFAIK the mapping of the class to the view is:
__table__ = create_view(Which I think ends up being the same as what you said:
__tablename__ = "viewname"(or did you mean__table__?)But in my case just to a new view defined there.
Maybe the lib does extra though, as there is also the
metadata=Base.metadata? Or perhaps that's not necessary when dealing with an existing view.Am curious about whether your solution indeed just works, or if something else is needed. And if it works, why would you not recommend it?
Am curious about whether your solution indeed just works, or if something else is needed.
I created a view in PostgreSQL:
test=# \d+ teamsandheroes View "public.teamsandheroes" Column | Type | Collation | Nullable | Default | Storage | Description --------------+-------------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | name | character varying | | | | extended | secret_name | character varying | | | | extended | age | integer | | | | plain | team_id | integer | | | | plain | team | character varying | | | | extended | headquarters | character varying | | | | extended | View definition: SELECT h.id, h.name, h.secret_name, h.age, h.team_id, t.name AS team, t.headquarters FROM hero h JOIN team t ON t.id = h.team_id;Queried it like this:
from sqlmodel import Field, Session, SQLModel, create_engine, select DB_URL = "postgresql://postgres:postgres@db:5432/test" engine = create_engine(DB_URL, echo=True) class HeroTeamView(SQLModel, table=True): __tablename__ = "teamsandheroes" team_id: int = Field(primary_key=True) id: int = Field(primary_key=True) name: str secret_name: str age: int team: str headquarters: str def main(): with Session(engine) as session: results = session.exec(select(HeroTeamView)) for result in results: print(result) if __name__ == "__main__": main()And got this output:
id=1 team_id=1 age=30 secret_name='Dive Wilson' headquarters='Sharp Tower' name='Deadpond' team='Preventers' id=2 team_id=1 age=20 secret_name='Spider-Boy' headquarters='Sharp Tower' name='Toby Maguire' team='Preventers'__tablename__ = "viewname" (or did you mean __table__ ?)
The latter gave me a runtime error:
AttributeError: 'str' object has no attribute 'c'!?And if it works, why would you not recommend it?
Personally, I would prefer to interact with individual
HeroandTeamobjects in my code, navigating relationships via properties. Such aHeroTeamViewdoesn't feel very natural to me. Counting teams becomes less easy, migration tools might complain, etc. But there might be a use case for this, for example when you don't have permissions on the individual tables but are allowed to query the view.Reacted by Toni Alatalo, Ivan C., Kiriti Badam, g13n4, João Flávio Santos, Ihsan Topaloglu, David and Francesco BruzzesiRight-o, thanks for the info @byrman . We've been also happy with relationships, have not planned multi table views.
We looked into using a view to implement the trashcan pattern for a kind of soft delete, like in https://michaeljswart.com/2014/04/implementing-the-recycle-bin-pattern-in-sql/
After some consideration, we didn't try views (yet), but have just now
deleted==Nonechecks in queries and relationships. We'll see whether stick with that or start using a view later.There is this , which is maybe useful to make an SQLModel implementation not sure. I would be happy for a proper view table in SQLModel, for example for use with auto-generating migrations and hiding columns when not wanting to allow access to the underlying table.
any update on view support in sqlmodel? 👀
Reacted by João Flávio Santos, Tardigrada, Adam Montgomerie, David, The Bits, Francesco Bruzzesi and annececiledunandEstou curioso para saber se a sua solução realmente funciona, ou se algo mais é necessário.
Criei uma visualização no PostgreSQL:
test=# \d+ teamsandheroes View "public.teamsandheroes" Column | Type | Collation | Nullable | Default | Storage | Description --------------+-------------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | name | character varying | | | | extended | secret_name | character varying | | | | extended | age | integer | | | | plain | team_id | integer | | | | plain | team | character varying | | | | extended | headquarters | character varying | | | | extended | View definition: SELECT h.id, h.name, h.secret_name, h.age, h.team_id, t.name AS team, t.headquarters FROM hero h JOIN team t ON t.id = h.team_id;Consultei assim:
from sqlmodel import Field, Session, SQLModel, create_engine, select DB_URL = "postgresql://postgres:postgres@db:5432/test" engine = create_engine(DB_URL, echo=True) class HeroTeamView(SQLModel, table=True): __tablename__ = "teamsandheroes" team_id: int = Field(primary_key=True) id: int = Field(primary_key=True) name: str secret_name: str age: int team: str headquarters: str def main(): with Session(engine) as session: results = session.exec(select(HeroTeamView)) for result in results: print(result) if __name__ == "__main__": main()E consegui essa saída:
id=1 team_id=1 age=30 secret_name='Dive Wilson' headquarters='Sharp Tower' name='Deadpond' team='Preventers' id=2 team_id=1 age=20 secret_name='Spider-Boy' headquarters='Sharp Tower' name='Toby Maguire' team='Preventers'tablename = "viewname" (ou você quis dizer table ?)
Este último me deu um erro de tempo de execução: !?
AttributeError: 'str' object has no attribute 'c'E se funciona, por que você não recomendaria?
Pessoalmente, prefiro interagir com indivíduos e objetos em meu código, navegando em relacionamentos por meio de propriedades. Isso não me parece muito natural. Contar equipes se torna menos fácil, ferramentas de migração podem reclamar, etc. Mas pode haver um caso de uso para isso, por exemplo, quando você não tem permissões nas tabelas individuais, mas tem permissão para consultar a exibição.
Hero``Team``HeroTeamView@byrman Thank you very much! 🙏
Another use-case is processing the results of a complex SQL query as a Python object.
Sometimes quite complex SQL queries are created to extract information from numerous tables and it's not practical or feasible to repeat the same effort in SQLModel but there's a need to process the query results and having it defined as a Pydantic model really helps.
Reacted by Craig Hills@itopaloglu83 , if you are not issuing a create statement, why does it matter if the underlying sql object is a view or a table?
@riziles , in a way it doesn't matter, it's either a complex SQL query being loaded from a text file or just querying a view.
Some users just need a simple way to load the result of a query into python or Pydantic objects but it's not feasible for an ORM library to support every use-case. I personally use SQLAlchemy text statements with dataclasses as a workaround but it brings a lot of boilerplate. It is appealing to just create a view and tie that to a class and just pulling the data that way.
@itopaloglu83, I'm not sure I understand what you are saying. Why can't you use a SQLModel table on a view?
This works, and presumably the user doesn't need to actually execute creates and deletes every single time they run:
from typing import Optional from sqlmodel import Field, Session, SQLModel, create_engine, select, text class Hero(SQLModel, table=True): name: str = Field(primary_key=True) secret_name: str age: Optional[int] = None class HeroView(SQLModel, table=True): name: str = Field(primary_key=True) secret_name: str age: Optional[int] = None full_name: str sqlite_file_name = "database.db" sqlite_url = f"sqlite:///{sqlite_file_name}" engine = create_engine(sqlite_url, echo=True) def create_db_and_tables(): SQLModel.metadata.drop_all(engine, tables = [Hero.__table__]) SQLModel.metadata.create_all(engine, tables = [Hero.__table__]) with Session(engine) as session: session.execute(text("DROP VIEW HeroView")) session.execute(text("CREATE VIEW HeroView as select *, name || ' aka ' || secret_name as full_name from Hero")) session.commit() def create_heroes(): hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") with Session(engine) as session: session.add(hero_1) session.commit() def select_heroes(): with Session(engine) as session: print("adding hero2") heroes = session.exec(select(HeroView)).all() print(heroes) def main(): create_db_and_tables() create_heroes() select_heroes() if __name__ == "__main__": main()
Reacted by Ihsan TopalogluJust FYI, the use cases for views extend beyond simply joining existing models. Views are a valid tool in abstraction and, in particular, materialized views are an important performance tool.
Our use case is a table that has "effective dating" of rows, but 99% of the time we only want the "current" version of something. In our case there are efficiency gains to create a
materialized view, add any independent indexes and map to that with thetable=Trueoption as shown in examples above.That being said, it would be nice to add readonly support to a "table" model so the framework knows not to attempt to mutate it, typing hints are updated, and it's obvious to the next developer.
Reacted by David, Andrew Fergusson, Mat, Craig Hills, Raphael Mitsch, annececiledunand and Tim Rabl- locked and limited conversation to collaborators
on May 18, 2026
First Check
Commit to Help
Example Code
Description
I have a view(Lets say HeroTeamView) created in mysql db. I want to read this. This view is essentially a left join of Hero and Teams table Joined on
Hero.Id.As shown in example above as soon as I try to select This view I get error
HeroTeamViewis not a'SQLModelMetaclass' object is not iterableI am not quiet sure I understand how to access rows created by view
Any pointers appreciated
Operating System
Windows
Operating System Details
No response
SQLModel Version
0.06
Python Version
3.9.7
Additional Context
I dont want to use Hero and Team tables directly to write a select query as there are multiple tables and joins in "real" world problem for me. Using Views provides me some obvious benefits like mentioned here