How to query View in sqlmodel

[mrudulp]

First Check

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

Commit to Help

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

[itopaloglu83]

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.

[riziles]

@itopaloglu83 , if you are not issuing a create statement, why does it matter if the underlying sql object is a view or a table?

[itopaloglu83]

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

[riziles]

@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()

[mbyrne00]

Just 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 the table=True option 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.