Add an overload to the `exec` method with `_Executable` statement for update and delete statements

[joachimhuet]

I think we should add an overload to the exec method to still have the possibility of passing an _Executable statement:

    @overload
    def exec(
        self,
        statement: _Executable,
        *,
        params: Optional[Union[Mapping[str, Any], Sequence[Mapping[str, Any]]]] = None,
        execution_options: Mapping[str, Any] = util.EMPTY_DICT,
        bind_arguments: Optional[Dict[str, Any]] = None,
        _parent_execute_state: Optional[Any] = None,
        _add_event: Optional[Any] = None,
    ) -> TupleResult[_TSelectParam]:
        ...

Originally posted by @joachimhuet in #831 (comment)

[federicober]

This is also valid for insert statements

[viaSeunghyun]

I agree.

The exec() method of Session allows Executable as a parameter for the statement, as shown in the code block below.

Yes, Session's method exec() has Executable as its type.

class Session(_Session):
    def exec(
        self,
        statement: Union[
            Select[_TSelectParam],
            SelectOfScalar[_TSelectParam],
            Executable[_TSelectParam],            # Here
        ],
        *,
        params: Optional[Union[Mapping[str, Any], Sequence[Mapping[str, Any]]]] = None,
        execution_options: Mapping[str, Any] = util.EMPTY_DICT,
        bind_arguments: Optional[Dict[str, Any]] = None,
        _parent_execute_state: Optional[Any] = None,
        _add_event: Optional[Any] = None,
    ) -> Union[TupleResult[_TSelectParam], ScalarResult[_TSelectParam]]:

When using direct SQL statements, especially when preparing user input, it needs to be wrapped with the text() function.
The text() returns a TextClause, which inherits from Executable through multiple inheritance.

To safely perform SQL queries, you need to wrap them in text(),

@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
def text(text: str) -> TextClause:   # Return Hint is TextClause

class TextClause(
    roles.DDLConstraintColumnRole,
    roles.DDLExpressionRole,
    roles.StatementOptionRole,
    roles.WhereHavingRole,
    roles.OrderByRole,
    roles.FromClauseRole,
    roles.SelectStatementRole,
    roles.InElementRole,
    Generative,
    Executable,                        # TextClause inherits Executable
    DQLDMLClauseElement,
    roles.BinaryElementRole[Any],
    inspection.Inspectable["TextClause"],
):

However, the problem arises because Executable is missing from the @overload in Session.
I also received a deprecated warning and tried to transition, but most situations where I use execute are not Select statements. Therefore, I think the current situation of annotating the existing execute() function with @deprecated and only allowing Select in the exec() function is misleading.

In other words, at least in the 0.110 version I tested, it's clearly a bad idea to display a @deprecated warning when calling the execute() method.

If only select statements are allowed, execute and exec should be considered as functions with different characteristics.
This inconsistency causes issues when working with non-Select SQL statements and limits the functionality of the Session.exec() method. It also creates confusion for users trying to follow best practices and handle deprecation warnings appropriately.

Steps to Reproduce:

1. Attempt to use Session.exec() with a non-Select SQL statement wrapped in text().
2. Observe the error or unexpected behavior.

Expected Behavior:
Session.exec() should accept all types of SQL statements wrapped in text(), as TextClause inherits from Executable.

Actual Behavior:
Session.exec() fails or produces unexpected results with non-Select SQL statements, despite them being valid Executable objects.

I've been checking and testing this and it does indeed output an error.

• Exception Type: AttributeError
• Exception.e: 'Session' object has no attribute 'exec'

Additional Notes:
This issue not only affects current usage but also complicates the transition process for users responding to deprecation warnings. It's important to maintain consistency in function behavior and support for different SQL statement types.

[sandangel]

I run into this issue to. Do we have any update?

[viaSeunghyun]

@sandangel
Unofficially, don't use exec, ignore the warning, use execute.
This is the cleanest way to do it without customising the FastAPI, otherwise a new @overload would need to be added in the new version.

[jtseven]

@sandangel Unofficially, don't use exec, ignore the warning, use execute. This is the cleanest way to do it without customising the FastAPI, otherwise a new @overload would need to be added in the new version.

But execute is marked as deprecated by the type checker. It says:

You probably want to use session.exec() instead of session.execute().

This is the original SQLAlchemy session.execute() method that returns objects of type Row, and that you have to call scalars() to get the model objects.

[Blue9]

One workaround is to use session.connection().execute(statement). This avoids the deprecation warning by explicitly dropping down to SQLAlchemy and is ultimately what SQLAlchemy's session.execute(statement) uses under the hood (link). This shares the session transaction so you can mix this with other operations, and you will still have to call session.commit() or session.rollback() at the end of your transaction.

This could also cause the local ORM state to be stale so just be wary to refresh relevant ORM objects if you need to use them. Also, if you need to pass in custom options there are some discrepancies between the Session.execute and Connection.execute APIs.

[sandangel]

I just use the sqlalchemy AsyncSession and use session.scalar or session.scalars instead. We don't need to modify AsyncSession of sqlmodel.

[ddahan]

These workarounds falling back to raw SQLAlchemy instead of SQLModel feel clunky and confusing to me.
I'm not sure to understand the real reason of why it is impossible to write this code without having a warning?

def delete_bulk_objects(model: type[T], conditions: list[Any], session: Session):
    statement = delete(model).where(*conditions)
    session.exec(statement)  # without type: ignore, it shows the 'session.exec(statement)' error.
    session.commit()

[vanakema]

I'm kind of confused as to why there's so much discussion about working around this using unwrapped sqlalchemy. Calling exec DOES work in my experience for these situations. It just fails the type checker because it's missing the overload.

Am I missing something? If not, why isn't it just added as another overload?

[chriscarrollsmith]

Can confirm that exec works, but raises mypy errors:

tests/conftest.py:48: error: No overload variant of "exec" of "Session" matches argument type "Delete"  [call-overload]
tests/conftest.py:48: note: Possible overload variants:
tests/conftest.py:48: note:     def [_TSelectParam: Any] exec(self, statement: Select[_TSelectParam], *, params: Mapping[str, Any] | Sequence[Mapping[str, Any]] | None = ..., execution_options: Mapping[str, Any] = ..., bind_arguments: dict[str, Any] | None = ..., _parent_execute_state: Any | None = ..., _add_event: Any | None = ...) -> TupleResult[_TSelectParam]
tests/conftest.py:48: note:     def [_TSelectParam: Any] exec(self, statement: SelectOfScalar[_TSelectParam], *, params: Mapping[str, Any] | Sequence[Mapping[str, Any]] | None = ..., execution_options: Mapping[str, Any] = ..., bind_arguments: dict[str, Any] | None = ..., _parent_execute_state: Any | None = ..., _add_event: Any | None = ...) -> ScalarResult[_TSelectParam]
tests/conftest.py:49: error: No overload variant of "exec" of "Session" matches argument type "Delete"  [call-overload]
tests/conftest.py:49: note: Possible overload variants:
tests/conftest.py:49: note:     def [_TSelectParam: Any] exec(self, statement: Select[_TSelectParam], *, params: Mapping[str, Any] | Sequence[Mapping[str, Any]] | None = ..., execution_options: Mapping[str, Any] = ..., bind_arguments: dict[str, Any] | None = ..., _parent_execute_state: Any | None = ..., _add_event: Any | None = ...) -> TupleResult[_TSelectParam]
tests/conftest.py:49: note:     def [_TSelectParam: Any] exec(self, statement: SelectOfScalar[_TSelectParam], *, params: Mapping[str, Any] | Sequence[Mapping[str, Any]] | None = ..., execution_options: Mapping[str, Any] = ..., bind_arguments: dict[str, Any] | None = ..., _parent_execute_state: Any | None = ..., _add_event: Any | None = ...) -> ScalarResult[_TSelectParam]
Found 2 errors in 1 file (checked 14 source files)

[mimoo]

surprising that the doc doesn't have an example on deleting many rows! indeed I can't find a way to do it without a typing error

https://sqlmodel.tiangolo.com/tutorial/delete/#recap

[seriaati]

I did the following according to the documentation and there are no more type errors.

Original code:

stmt = delete(EmailVerification).where(
    EmailVerification.user_id == user.id, EmailVerification.verified == False
)

Modified code:

from sqlmodel import col

stmt = delete(EmailVerification).where(
    col(EmailVerification.user_id) == user.id, col(EmailVerification.verified) == False
)

[gaardhus]

@seriaati I tried updating my code to implement the col(), which does remove the type error for the definition, however not when executing the statement with session.exec(stmt).