How to query View in sqlmodel

[mrudulp]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select

class HeroTeamView(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

sqlite_file_name = "my.db"
db_url = f"mysql+mysqldb://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"
engine = create_engine(db_url, echo=True)
with Session(engine) as session:
   statement = select(HeroTeamView)
   orgs = session.exec(statement)
   print(f"orgs::{orgs}")
   org_list = orgs.fetchall()

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 HeroTeamView is not a 'SQLModelMetaclass' object is not iterable

I 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

[byrman]

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.

[antont]

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.

[byrman]

why would you not recommend what you say?

Wasn't the question about how to do it using SQLModel on an existing view?

[byrman]

To clarify, I tried to map a view directly on a single HeroTeamView model. 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.

[antont]

@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?

[byrman]

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 Hero and Team objects in my code, navigating relationships via properties. Such a HeroTeamView doesn'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.

[antont]

Right-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==None checks in queries and relationships. We'll see whether stick with that or start using a view later.

[methuselah-0]

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.

[giovanni-bellini-argo]

any update on view support in sqlmodel? 👀

[joaoflaviosantos]

Estou 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! 🙏