Repository navigation
Add an overload to the exec method with _Executable statement for update and delete statements #909
Description
Activity
This is also valid for insert statements
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 methodexec()hasExecutableas 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.
Thetext()returns aTextClause, which inherits fromExecutablethrough 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 TextClauseclass 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
Executableis missing from the@overloadin Session.
I also received a deprecated warning and tried to transition, but most situations where I use execute are notSelectstatements. Therefore, I think the current situation of annotating the existingexecute()function with@deprecatedand only allowingSelectin theexec()function is misleading.In other words, at least in the
0.110version I tested, it's clearly a bad idea to display a@deprecatedwarning when calling theexecute()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 theSession.exec()method. It also creates confusion for users trying to follow best practices and handle deprecation warnings appropriately.Steps to Reproduce:
- Attempt to use
Session.exec()with a non-Select SQL statement wrapped intext(). - Observe the error or unexpected behavior.
Expected Behavior:
Session.exec()should accept all types of SQL statements wrapped intext(), asTextClauseinherits fromExecutable.Actual Behavior:
Session.exec()fails or produces unexpected results with non-Select SQL statements, despite them being validExecutableobjects.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.- Attempt to use
I run into this issue to. Do we have any update?
@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@overloadwould need to be added in the new version.Reacted by Sandy and Abrar Mahi@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
@overloadwould need to be added in the new version.But
executeis 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.
Reacted by David Dahan, Tobias Gårdhus and msftcangoblowmeOne workaround is to use
session.connection().execute(statement). This avoids the deprecation warning by explicitly dropping down to SQLAlchemy and is ultimately what SQLAlchemy'ssession.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 callsession.commit()orsession.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.executeandConnection.executeAPIs.Reacted by Jason Lo, Alexander Eisele, HenriTEL and Fuga TsutsuiI just use the sqlalchemy AsyncSession and use
session.scalarorsession.scalarsinstead. We don't need to modify AsyncSession of sqlmodel.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()
Reacted by Vladimir Lebedev, Mark Van Aken, yubanmeiqin9048, nagomiso, Stephen Bunch, Lucas Colombo, pizzashuai, Fabio Natanael Kepler and Simon KittleI'm kind of confused as to why there's so much discussion about working around this using unwrapped sqlalchemy. Calling
execDOES 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?
Reacted by Mike, Aloizio Macedo, Julian Eberius, Sam Nalty, Lucas Colombo and the-vindicarCan 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)Reacted by Daoji, Anton Fomin and major12surprising 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
Reacted by Paul Coates, Junsang Cheon, Aleksei Protopopov, Vitor Schirmer, 叶芝秋, kenma, pizzashuai, slyf and Guilherme2 remaining items
Why is it not fixed already, it's just adding an overload, why is it taking more than a year, what is going on ?!
Reacted by msftcangoblowmeReacted by Mark Van AkenWhy is it not fixed already, it's just adding an overload, why is it taking more than a year, what is going on ?!
The bystander effect 😁
Why is it not fixed already, it's just adding an overload, why is it taking more than a year, what is going on ?!
I had the exact same question when I was looking at this issue. This is why I decided to take action 😅
Pyright also complains:
/home/sglbl/sg_project_template/src/infra/postgres/database.py:32:15 -
error: No overloads for "exec" match the provided arguments (reportCallIssue)- linked a pull request that will close this issue✨ Add overload for `exec` method to support `insert`, `update`, `delete` statements #1342
on Aug 15, 2025 - marked .exec() with text() does not provide auto-suggestion methods #376 as a duplicate of this issue
on Aug 26, 2025 - unmarked .exec() with text() does not provide auto-suggestion methods #376 as a duplicate of this issue
on Nov 17, 2025 - added a commit that references this issue
on May 2, 2026 I'm using version 0.0.38 and the overload of exec for text still doesn't exist.
No overloads for "exec" match the provided arguments - Pylance[reportCallIssue]
I'm using version 0.0.38 and the overload of exec for text still doesn't exist.
See #1657
I think we should add an overload to the
execmethod to still have the possibility of passing an_Executablestatement:Originally posted by @joachimhuet in #831 (comment)