.exec() with text() does not provide auto-suggestion methods

[Zaffer]

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 Any, List

from fastapi import APIRouter, Depends
from sqlmodel import Session, select, text, SQLModel
from pydantic import BaseModel

from app.api.deps import get_session_legacy

router = APIRouter(prefix="/")

class Test(BaseModel):
    hello: str

@router.get("/data", response_model=List[Test])
async def get_data(
    db: Session = Depends(get_session_legacy)
):

    sql = text(
        """
            SELECT 'hi' AS "hello"
        """
    )
    result = db.exec(sql).all()
    for res in result:
        logger.info(f"response: {res}")

    return result

Description

When using db.exec(select(Hero)) you can use auto-suggestion in vscode to see the methods that belong to exec().
However when using db.exec(text("SELECT * FROM hero")) then there are no suggestions following the exec().

Wanted Solution

I want to see the suggestions: db.exec(text()).[one(), all(), scalars(), etc...]

Wanted Code

from typing import Any, List

from fastapi import APIRouter, Depends
from sqlmodel import Session, select, text, SQLModel
from pydantic import BaseModel

from app.api.deps import get_session_legacy

router = APIRouter(prefix="/")

class Test(BaseModel):
    hello: str

@router.get("/data", response_model=List[Test])
async def get_data(
    db: Session = Depends(get_session_legacy)
):

    sql = text(
        """
            SELECT 'hi' AS "hello"
        """
    )
    result = db.exec(sql).[all(), one(), scallars()... etc]
    for res in result:
        logger.info(f"response: {res}")

    return result

Alternatives

No response

Operating System

Linux

Operating System Details

wsl2 on windows, vscode

SQLModel Version

0.0.6

Python Version

3.10

Additional Context

No response

[Niccolum]

It's issue incorrect, i think, because of https://github.com/tiangolo/sqlmodel/blob/main/sqlmodel/__init__.py#L77

So, you must send it to sqlalchemy or sqlalchemy_stubs.

[YuriiMotov]

So, this is basically about overriding Session.exec method to return Result[Any] instead of Any in case the type of statement is TextClause.

It works this way with raw SQLAlchemy:

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

with Session(create_engine("sqlite:///")) as session:
    sql = text(
        """
            SELECT 'hi' AS "hello"
        """
    )

    result = session.execute(sql)

---

---

And with SQLModel return type is still Any:

from sqlmodel import Session, create_engine, text

with Session(create_engine("sqlite:///")) as session:
    sql = text(
        """
            SELECT 'hi' AS "hello"
        """
    )
    result = session.exec(sql)

---

[YuriiMotov]

Closing this in favor of more general issue: #909