Bring back the raw sql query support

[NaviteLogger]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 sqlmodel import Field, Session, SQLModel, create_engine, select  


class Hero(SQLModel, table=True):  
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None


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.create_all(engine)  


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")  
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:  
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:  
        statement = select(Hero)  
        results = session.exec(statement)  
        for hero in results:  
            print(hero)  
    


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()  


if __name__ == "__main__":
    main()

Description

What is currently happening is session.execute is going out of support, it is deprecated as of this moment. How to execute raw SQL queries on the database using session? Session.exec seems to have no support for raw queries.
#772
this is a traceback to a discussion about raw queries, but it uses the deprecated session.execute()

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.19

Python Version

3.12.3

Additional Context

No response

[antspy]

Is there any update on this? Is it impossible to execute a raw SQL query with SQLModels at the moment?

[benglewis]

@AlmogBaku The original poster mentioned that session.execute is deprecated and hence this doesn't actually help with his issue

[riziles]

@NaviteLogger and @antspy ,

just use

result = session.connection().execute(text(sql), query_params)

session.connection() accesses the underlying SQLAlchemy connection object and thus gives you access to EVERYTHING you can do with SQLAlchemy.

[NaviteLogger]

So why bother with SQLmodel then? I can just use SQL alchemy and pydantic

[darrenclark]

+1 to having an officially supported way of running SQL directly.

I've had success with something like this to at least hide the deprecation warning:

sql = "SELECT ......."
await super(AsyncSession, async_session).execute(text(sql))

Haven't tested it, but I assume similar would work for non-async:

sql = "SELECT ......."
super(Session, session).execute(text(sql))

[riziles]

@darrenclark , what do you mean by "officially supported"? What you are doing is officially supported by SQLAlchemy, but I believe that it's well outside the scope of what SQLModel aims to do (facilitate ORM between SQL tables and Pydantic objects).

[NaviteLogger]

My solution was to drop sqlmodel and go back sqlalchemy. It provides way more options instead of this limited library

[riziles]

@NaviteLogger ,

This is a somewhat confusing statement. SQLModel is built on top of SQLAlchemy and makes SA's ORM model compatible with Pydantic for better type checking, IDE support and developer experience. It's impossible for SQLAlchemy to provide more options than SQLModel because SQLAlchemy is a dependency. By definition, SQLModel provides more options. You can always import both into any script, and there are many use cases where you should.

[NaviteLogger]

It's a level of abstraction. With abstraction, some limits may be applied to the underlying technology, like in this case

[riziles]

I actually disagree. I don't think that SQLModel's abstraction layer is any deeper than SQLAlchemy's native ORM model. It just makes it compatible with Pydantic. Perhaps you are more used to using SQLAlchemy Core?

[YuriiMotov]

There is related issue: #909
And open PR that solves this: #1342

I'll close this discussion, let's track this issue there