Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/dml.py: 48%

1# sql/dml.py 

2# Copyright (C) 2009-2025 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

7""" 

8Provide :class:`_expression.Insert`, :class:`_expression.Update` and 

9:class:`_expression.Delete`. 

10 

11""" 

12from __future__ import annotations 

13 

14import collections.abc as collections_abc 

15import operator 

16from typing import Any 

17from typing import cast 

18from typing import Dict 

19from typing import Iterable 

20from typing import List 

21from typing import MutableMapping 

22from typing import NoReturn 

23from typing import Optional 

24from typing import overload 

25from typing import Sequence 

26from typing import Set 

27from typing import Tuple 

28from typing import Type 

29from typing import TYPE_CHECKING 

30from typing import TypeVar 

31from typing import Union 

32 

33from . import coercions 

34from . import roles 

35from . import util as sql_util 

36from ._typing import _TP 

37from ._typing import _unexpected_kw 

38from ._typing import is_column_element 

39from ._typing import is_named_from_clause 

40from .base import _entity_namespace_key 

41from .base import _exclusive_against 

42from .base import _from_objects 

43from .base import _generative 

44from .base import _select_iterables 

45from .base import ColumnCollection 

46from .base import ColumnSet 

47from .base import CompileState 

48from .base import DialectKWArgs 

49from .base import Executable 

50from .base import Generative 

51from .base import HasCompileState 

52from .elements import BooleanClauseList 

53from .elements import ClauseElement 

54from .elements import ColumnClause 

55from .elements import ColumnElement 

56from .elements import Null 

57from .selectable import Alias 

58from .selectable import ExecutableReturnsRows 

59from .selectable import FromClause 

60from .selectable import HasCTE 

61from .selectable import HasPrefixes 

62from .selectable import Join 

63from .selectable import SelectLabelStyle 

64from .selectable import TableClause 

65from .selectable import TypedReturnsRows 

66from .sqltypes import NullType 

67from .visitors import InternalTraversal 

68from .. import exc 

69from .. import util 

70from ..util.typing import Self 

71from ..util.typing import TypeGuard 

72 

73if TYPE_CHECKING: 

74 from ._typing import _ColumnExpressionArgument 

75 from ._typing import _ColumnsClauseArgument 

76 from ._typing import _DMLColumnArgument 

77 from ._typing import _DMLColumnKeyMapping 

78 from ._typing import _DMLTableArgument 

79 from ._typing import _T0 # noqa 

80 from ._typing import _T1 # noqa 

81 from ._typing import _T2 # noqa 

82 from ._typing import _T3 # noqa 

83 from ._typing import _T4 # noqa 

84 from ._typing import _T5 # noqa 

85 from ._typing import _T6 # noqa 

86 from ._typing import _T7 # noqa 

87 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa 

88 from .base import ReadOnlyColumnCollection 

89 from .compiler import SQLCompiler 

90 from .elements import KeyedColumnElement 

91 from .selectable import _ColumnsClauseElement 

92 from .selectable import _SelectIterable 

93 from .selectable import Select 

94 from .selectable import Selectable 

95 

96 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ... 

97 

98 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ... 

99 

100 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ... 

101 

102else: 

103 isupdate = operator.attrgetter("isupdate") 

104 isdelete = operator.attrgetter("isdelete") 

105 isinsert = operator.attrgetter("isinsert") 

106 

107 

108_T = TypeVar("_T", bound=Any) 

109 

110_DMLColumnElement = Union[str, ColumnClause[Any]] 

111_DMLTableElement = Union[TableClause, Alias, Join] 

112 

113 

114class DMLState(CompileState): 

115 _no_parameters = True 

116 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None 

117 _multi_parameters: Optional[ 

118 List[MutableMapping[_DMLColumnElement, Any]] 

119 ] = None 

120 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None 

121 _parameter_ordering: Optional[List[_DMLColumnElement]] = None 

122 _primary_table: FromClause 

123 _supports_implicit_returning = True 

124 

125 isupdate = False 

126 isdelete = False 

127 isinsert = False 

128 

129 statement: UpdateBase 

130 

131 def __init__( 

132 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any 

133 ): 

134 raise NotImplementedError() 

135 

136 @classmethod 

137 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]: 

138 return { 

139 "name": ( 

140 statement.table.name 

141 if is_named_from_clause(statement.table) 

142 else None 

143 ), 

144 "table": statement.table, 

145 } 

146 

147 @classmethod 

148 def get_returning_column_descriptions( 

149 cls, statement: UpdateBase 

150 ) -> List[Dict[str, Any]]: 

151 return [ 

152 { 

153 "name": c.key, 

154 "type": c.type, 

155 "expr": c, 

156 } 

157 for c in statement._all_selected_columns 

158 ] 

159 

160 @property 

161 def dml_table(self) -> _DMLTableElement: 

162 return self.statement.table 

163 

164 if TYPE_CHECKING: 

165 

166 @classmethod 

167 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ... 

168 

169 @classmethod 

170 def _get_multi_crud_kv_pairs( 

171 cls, 

172 statement: UpdateBase, 

173 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]], 

174 ) -> List[Dict[_DMLColumnElement, Any]]: 

175 return [ 

176 { 

177 coercions.expect(roles.DMLColumnRole, k): v 

178 for k, v in mapping.items() 

179 } 

180 for mapping in multi_kv_iterator 

181 ] 

182 

183 @classmethod 

184 def _get_crud_kv_pairs( 

185 cls, 

186 statement: UpdateBase, 

187 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]], 

188 needs_to_be_cacheable: bool, 

189 ) -> List[Tuple[_DMLColumnElement, Any]]: 

190 return [ 

191 ( 

192 coercions.expect(roles.DMLColumnRole, k), 

193 ( 

194 v 

195 if not needs_to_be_cacheable 

196 else coercions.expect( 

197 roles.ExpressionElementRole, 

198 v, 

199 type_=NullType(), 

200 is_crud=True, 

201 ) 

202 ), 

203 ) 

204 for k, v in kv_iterator 

205 ] 

206 

207 def _make_extra_froms( 

208 self, statement: DMLWhereBase 

209 ) -> Tuple[FromClause, List[FromClause]]: 

210 froms: List[FromClause] = [] 

211 

212 all_tables = list(sql_util.tables_from_leftmost(statement.table)) 

213 primary_table = all_tables[0] 

214 seen = {primary_table} 

215 

216 consider = statement._where_criteria 

217 if self._dict_parameters: 

218 consider += tuple(self._dict_parameters.values()) 

219 

220 for crit in consider: 

221 for item in _from_objects(crit): 

222 if not seen.intersection(item._cloned_set): 

223 froms.append(item) 

224 seen.update(item._cloned_set) 

225 

226 froms.extend(all_tables[1:]) 

227 return primary_table, froms 

228 

229 def _process_values(self, statement: ValuesBase) -> None: 

230 if self._no_parameters: 

231 self._dict_parameters = statement._values 

232 self._no_parameters = False 

233 

234 def _process_select_values(self, statement: ValuesBase) -> None: 

235 assert statement._select_names is not None 

236 parameters: MutableMapping[_DMLColumnElement, Any] = { 

237 name: Null() for name in statement._select_names 

238 } 

239 

240 if self._no_parameters: 

241 self._no_parameters = False 

242 self._dict_parameters = parameters 

243 else: 

244 # this condition normally not reachable as the Insert 

245 # does not allow this construction to occur 

246 assert False, "This statement already has parameters" 

247 

248 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn: 

249 raise exc.InvalidRequestError( 

250 "%s construct does not support " 

251 "multiple parameter sets." % statement.__visit_name__.upper() 

252 ) 

253 

254 def _cant_mix_formats_error(self) -> NoReturn: 

255 raise exc.InvalidRequestError( 

256 "Can't mix single and multiple VALUES " 

257 "formats in one INSERT statement; one style appends to a " 

258 "list while the other replaces values, so the intent is " 

259 "ambiguous." 

260 ) 

261 

262 

263@CompileState.plugin_for("default", "insert") 

264class InsertDMLState(DMLState): 

265 isinsert = True 

266 

267 include_table_with_column_exprs = False 

268 

269 _has_multi_parameters = False 

270 

271 def __init__( 

272 self, 

273 statement: Insert, 

274 compiler: SQLCompiler, 

275 disable_implicit_returning: bool = False, 

276 **kw: Any, 

277 ): 

278 self.statement = statement 

279 self._primary_table = statement.table 

280 

281 if disable_implicit_returning: 

282 self._supports_implicit_returning = False 

283 

284 self.isinsert = True 

285 if statement._select_names: 

286 self._process_select_values(statement) 

287 if statement._values is not None: 

288 self._process_values(statement) 

289 if statement._multi_values: 

290 self._process_multi_values(statement) 

291 

292 @util.memoized_property 

293 def _insert_col_keys(self) -> List[str]: 

294 # this is also done in crud.py -> _key_getters_for_crud_column 

295 return [ 

296 coercions.expect(roles.DMLColumnRole, col, as_key=True) 

297 for col in self._dict_parameters or () 

298 ] 

299 

300 def _process_values(self, statement: ValuesBase) -> None: 

301 if self._no_parameters: 

302 self._has_multi_parameters = False 

303 self._dict_parameters = statement._values 

304 self._no_parameters = False 

305 elif self._has_multi_parameters: 

306 self._cant_mix_formats_error() 

307 

308 def _process_multi_values(self, statement: ValuesBase) -> None: 

309 for parameters in statement._multi_values: 

310 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [ 

311 ( 

312 { 

313 c.key: value 

314 for c, value in zip(statement.table.c, parameter_set) 

315 } 

316 if isinstance(parameter_set, collections_abc.Sequence) 

317 else parameter_set 

318 ) 

319 for parameter_set in parameters 

320 ] 

321 

322 if self._no_parameters: 

323 self._no_parameters = False 

324 self._has_multi_parameters = True 

325 self._multi_parameters = multi_parameters 

326 self._dict_parameters = self._multi_parameters[0] 

327 elif not self._has_multi_parameters: 

328 self._cant_mix_formats_error() 

329 else: 

330 assert self._multi_parameters 

331 self._multi_parameters.extend(multi_parameters) 

332 

333 

334@CompileState.plugin_for("default", "update") 

335class UpdateDMLState(DMLState): 

336 isupdate = True 

337 

338 include_table_with_column_exprs = False 

339 

340 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any): 

341 self.statement = statement 

342 

343 self.isupdate = True 

344 if statement._ordered_values is not None: 

345 self._process_ordered_values(statement) 

346 elif statement._values is not None: 

347 self._process_values(statement) 

348 elif statement._multi_values: 

349 self._no_multi_values_supported(statement) 

350 t, ef = self._make_extra_froms(statement) 

351 self._primary_table = t 

352 self._extra_froms = ef 

353 

354 self.is_multitable = mt = ef 

355 self.include_table_with_column_exprs = bool( 

356 mt and compiler.render_table_with_column_in_update_from 

357 ) 

358 

359 def _process_ordered_values(self, statement: ValuesBase) -> None: 

360 parameters = statement._ordered_values 

361 

362 if self._no_parameters: 

363 self._no_parameters = False 

364 assert parameters is not None 

365 self._dict_parameters = dict(parameters) 

366 self._ordered_values = parameters 

367 self._parameter_ordering = [key for key, value in parameters] 

368 else: 

369 raise exc.InvalidRequestError( 

370 "Can only invoke ordered_values() once, and not mixed " 

371 "with any other values() call" 

372 ) 

373 

374 

375@CompileState.plugin_for("default", "delete") 

376class DeleteDMLState(DMLState): 

377 isdelete = True 

378 

379 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any): 

380 self.statement = statement 

381 

382 self.isdelete = True 

383 t, ef = self._make_extra_froms(statement) 

384 self._primary_table = t 

385 self._extra_froms = ef 

386 self.is_multitable = ef 

387 

388 

389class UpdateBase( 

390 roles.DMLRole, 

391 HasCTE, 

392 HasCompileState, 

393 DialectKWArgs, 

394 HasPrefixes, 

395 Generative, 

396 ExecutableReturnsRows, 

397 ClauseElement, 

398): 

399 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements.""" 

400 

401 __visit_name__ = "update_base" 

402 

403 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = ( 

404 util.EMPTY_DICT 

405 ) 

406 named_with_column = False 

407 

408 _label_style: SelectLabelStyle = ( 

409 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY 

410 ) 

411 table: _DMLTableElement 

412 

413 _return_defaults = False 

414 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = ( 

415 None 

416 ) 

417 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None 

418 _returning: Tuple[_ColumnsClauseElement, ...] = () 

419 

420 is_dml = True 

421 

422 def _generate_fromclause_column_proxies( 

423 self, 

424 fromclause: FromClause, 

425 columns: ColumnCollection[str, KeyedColumnElement[Any]], 

426 primary_key: ColumnSet, 

427 foreign_keys: Set[KeyedColumnElement[Any]], 

428 ) -> None: 

429 columns._populate_separate_keys( 

430 col._make_proxy( 

431 fromclause, primary_key=primary_key, foreign_keys=foreign_keys 

432 ) 

433 for col in self._all_selected_columns 

434 if is_column_element(col) 

435 ) 

436 

437 def params(self, *arg: Any, **kw: Any) -> NoReturn: 

438 """Set the parameters for the statement. 

439 

440 This method raises ``NotImplementedError`` on the base class, 

441 and is overridden by :class:`.ValuesBase` to provide the 

442 SET/VALUES clause of UPDATE and INSERT. 

443 

444 """ 

445 raise NotImplementedError( 

446 "params() is not supported for INSERT/UPDATE/DELETE statements." 

447 " To set the values for an INSERT or UPDATE statement, use" 

448 " stmt.values(**parameters)." 

449 ) 

450 

451 @_generative 

452 def with_dialect_options(self, **opt: Any) -> Self: 

453 """Add dialect options to this INSERT/UPDATE/DELETE object. 

454 

455 e.g.:: 

456 

457 upd = table.update().dialect_options(mysql_limit=10) 

458 

459 .. versionadded: 1.4 - this method supersedes the dialect options 

460 associated with the constructor. 

461 

462 

463 """ 

464 self._validate_dialect_kwargs(opt) 

465 return self 

466 

467 @_generative 

468 def return_defaults( 

469 self, 

470 *cols: _DMLColumnArgument, 

471 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None, 

472 sort_by_parameter_order: bool = False, 

473 ) -> Self: 

474 """Make use of a :term:`RETURNING` clause for the purpose 

475 of fetching server-side expressions and defaults, for supporting 

476 backends only. 

477 

478 .. deepalchemy:: 

479 

480 The :meth:`.UpdateBase.return_defaults` method is used by the ORM 

481 for its internal work in fetching newly generated primary key 

482 and server default values, in particular to provide the underyling 

483 implementation of the :paramref:`_orm.Mapper.eager_defaults` 

484 ORM feature as well as to allow RETURNING support with bulk 

485 ORM inserts. Its behavior is fairly idiosyncratic 

486 and is not really intended for general use. End users should 

487 stick with using :meth:`.UpdateBase.returning` in order to 

488 add RETURNING clauses to their INSERT, UPDATE and DELETE 

489 statements. 

490 

491 Normally, a single row INSERT statement will automatically populate the 

492 :attr:`.CursorResult.inserted_primary_key` attribute when executed, 

493 which stores the primary key of the row that was just inserted in the 

494 form of a :class:`.Row` object with column names as named tuple keys 

495 (and the :attr:`.Row._mapping` view fully populated as well). The 

496 dialect in use chooses the strategy to use in order to populate this 

497 data; if it was generated using server-side defaults and / or SQL 

498 expressions, dialect-specific approaches such as ``cursor.lastrowid`` 

499 or ``RETURNING`` are typically used to acquire the new primary key 

500 value. 

501 

502 However, when the statement is modified by calling 

503 :meth:`.UpdateBase.return_defaults` before executing the statement, 

504 additional behaviors take place **only** for backends that support 

505 RETURNING and for :class:`.Table` objects that maintain the 

506 :paramref:`.Table.implicit_returning` parameter at its default value of 

507 ``True``. In these cases, when the :class:`.CursorResult` is returned 

508 from the statement's execution, not only will 

509 :attr:`.CursorResult.inserted_primary_key` be populated as always, the 

510 :attr:`.CursorResult.returned_defaults` attribute will also be 

511 populated with a :class:`.Row` named-tuple representing the full range 

512 of server generated 

513 values from that single row, including values for any columns that 

514 specify :paramref:`_schema.Column.server_default` or which make use of 

515 :paramref:`_schema.Column.default` using a SQL expression. 

516 

517 When invoking INSERT statements with multiple rows using 

518 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the 

519 :meth:`.UpdateBase.return_defaults` modifier will have the effect of 

520 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and 

521 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being 

522 fully populated with lists of :class:`.Row` objects representing newly 

523 inserted primary key values as well as newly inserted server generated 

524 values for each row inserted. The 

525 :attr:`.CursorResult.inserted_primary_key` and 

526 :attr:`.CursorResult.returned_defaults` attributes will also continue 

527 to be populated with the first row of these two collections. 

528 

529 If the backend does not support RETURNING or the :class:`.Table` in use 

530 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING 

531 clause is added and no additional data is fetched, however the 

532 INSERT, UPDATE or DELETE statement proceeds normally. 

533 

534 E.g.:: 

535 

536 stmt = table.insert().values(data="newdata").return_defaults() 

537 

538 result = connection.execute(stmt) 

539 

540 server_created_at = result.returned_defaults["created_at"] 

541 

542 When used against an UPDATE statement 

543 :meth:`.UpdateBase.return_defaults` instead looks for columns that 

544 include :paramref:`_schema.Column.onupdate` or 

545 :paramref:`_schema.Column.server_onupdate` parameters assigned, when 

546 constructing the columns that will be included in the RETURNING clause 

547 by default if explicit columns were not specified. When used against a 

548 DELETE statement, no columns are included in RETURNING by default, they 

549 instead must be specified explicitly as there are no columns that 

550 normally change values when a DELETE statement proceeds. 

551 

552 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported 

553 for DELETE statements also and has been moved from 

554 :class:`.ValuesBase` to :class:`.UpdateBase`. 

555 

556 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive 

557 against the :meth:`.UpdateBase.returning` method and errors will be 

558 raised during the SQL compilation process if both are used at the same 

559 time on one statement. The RETURNING clause of the INSERT, UPDATE or 

560 DELETE statement is therefore controlled by only one of these methods 

561 at a time. 

562 

563 The :meth:`.UpdateBase.return_defaults` method differs from 

564 :meth:`.UpdateBase.returning` in these ways: 

565 

566 1. :meth:`.UpdateBase.return_defaults` method causes the 

567 :attr:`.CursorResult.returned_defaults` collection to be populated 

568 with the first row from the RETURNING result. This attribute is not 

569 populated when using :meth:`.UpdateBase.returning`. 

570 

571 2. :meth:`.UpdateBase.return_defaults` is compatible with existing 

572 logic used to fetch auto-generated primary key values that are then 

573 populated into the :attr:`.CursorResult.inserted_primary_key` 

574 attribute. By contrast, using :meth:`.UpdateBase.returning` will 

575 have the effect of the :attr:`.CursorResult.inserted_primary_key` 

576 attribute being left unpopulated. 

577 

578 3. :meth:`.UpdateBase.return_defaults` can be called against any 

579 backend. Backends that don't support RETURNING will skip the usage 

580 of the feature, rather than raising an exception, *unless* 

581 ``supplemental_cols`` is passed. The return value 

582 of :attr:`_engine.CursorResult.returned_defaults` will be ``None`` 

583 for backends that don't support RETURNING or for which the target 

584 :class:`.Table` sets :paramref:`.Table.implicit_returning` to 

585 ``False``. 

586 

587 4. An INSERT statement invoked with executemany() is supported if the 

588 backend database driver supports the 

589 :ref:`insertmanyvalues <engine_insertmanyvalues>` 

590 feature which is now supported by most SQLAlchemy-included backends. 

591 When executemany is used, the 

592 :attr:`_engine.CursorResult.returned_defaults_rows` and 

593 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors 

594 will return the inserted defaults and primary keys. 

595 

596 .. versionadded:: 1.4 Added 

597 :attr:`_engine.CursorResult.returned_defaults_rows` and 

598 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors. 

599 In version 2.0, the underlying implementation which fetches and 

600 populates the data for these attributes was generalized to be 

601 supported by most backends, whereas in 1.4 they were only 

602 supported by the ``psycopg2`` driver. 

603 

604 

605 :param cols: optional list of column key names or 

606 :class:`_schema.Column` that acts as a filter for those columns that 

607 will be fetched. 

608 :param supplemental_cols: optional list of RETURNING expressions, 

609 in the same form as one would pass to the 

610 :meth:`.UpdateBase.returning` method. When present, the additional 

611 columns will be included in the RETURNING clause, and the 

612 :class:`.CursorResult` object will be "rewound" when returned, so 

613 that methods like :meth:`.CursorResult.all` will return new rows 

614 mostly as though the statement used :meth:`.UpdateBase.returning` 

615 directly. However, unlike when using :meth:`.UpdateBase.returning` 

616 directly, the **order of the columns is undefined**, so can only be 

617 targeted using names or :attr:`.Row._mapping` keys; they cannot 

618 reliably be targeted positionally. 

619 

620 .. versionadded:: 2.0 

621 

622 :param sort_by_parameter_order: for a batch INSERT that is being 

623 executed against multiple parameter sets, organize the results of 

624 RETURNING so that the returned rows correspond to the order of 

625 parameter sets passed in. This applies only to an :term:`executemany` 

626 execution for supporting dialects and typically makes use of the 

627 :term:`insertmanyvalues` feature. 

628 

629 .. versionadded:: 2.0.10 

630 

631 .. seealso:: 

632 

633 :ref:`engine_insertmanyvalues_returning_order` - background on 

634 sorting of RETURNING rows for bulk INSERT 

635 

636 .. seealso:: 

637 

638 :meth:`.UpdateBase.returning` 

639 

640 :attr:`_engine.CursorResult.returned_defaults` 

641 

642 :attr:`_engine.CursorResult.returned_defaults_rows` 

643 

644 :attr:`_engine.CursorResult.inserted_primary_key` 

645 

646 :attr:`_engine.CursorResult.inserted_primary_key_rows` 

647 

648 """ 

649 

650 if self._return_defaults: 

651 # note _return_defaults_columns = () means return all columns, 

652 # so if we have been here before, only update collection if there 

653 # are columns in the collection 

654 if self._return_defaults_columns and cols: 

655 self._return_defaults_columns = tuple( 

656 util.OrderedSet(self._return_defaults_columns).union( 

657 coercions.expect(roles.ColumnsClauseRole, c) 

658 for c in cols 

659 ) 

660 ) 

661 else: 

662 # set for all columns 

663 self._return_defaults_columns = () 

664 else: 

665 self._return_defaults_columns = tuple( 

666 coercions.expect(roles.ColumnsClauseRole, c) for c in cols 

667 ) 

668 self._return_defaults = True 

669 if sort_by_parameter_order: 

670 if not self.is_insert: 

671 raise exc.ArgumentError( 

672 "The 'sort_by_parameter_order' argument to " 

673 "return_defaults() only applies to INSERT statements" 

674 ) 

675 self._sort_by_parameter_order = True 

676 if supplemental_cols: 

677 # uniquifying while also maintaining order (the maintain of order 

678 # is for test suites but also for vertical splicing 

679 supplemental_col_tup = ( 

680 coercions.expect(roles.ColumnsClauseRole, c) 

681 for c in supplemental_cols 

682 ) 

683 

684 if self._supplemental_returning is None: 

685 self._supplemental_returning = tuple( 

686 util.unique_list(supplemental_col_tup) 

687 ) 

688 else: 

689 self._supplemental_returning = tuple( 

690 util.unique_list( 

691 self._supplemental_returning 

692 + tuple(supplemental_col_tup) 

693 ) 

694 ) 

695 

696 return self 

697 

698 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool: 

699 """Return ``True`` if this :class:`.ReturnsRows` is 

700 'derived' from the given :class:`.FromClause`. 

701 

702 Since these are DMLs, we dont want such statements ever being adapted 

703 so we return False for derives. 

704 

705 """ 

706 return False 

707 

708 @_generative 

709 def returning( 

710 self, 

711 *cols: _ColumnsClauseArgument[Any], 

712 sort_by_parameter_order: bool = False, 

713 **__kw: Any, 

714 ) -> UpdateBase: 

715 r"""Add a :term:`RETURNING` or equivalent clause to this statement. 

716 

717 e.g.: 

718 

719 .. sourcecode:: pycon+sql 

720 

721 >>> stmt = ( 

722 ... table.update() 

723 ... .where(table.c.data == "value") 

724 ... .values(status="X") 

725 ... .returning(table.c.server_flag, table.c.updated_timestamp) 

726 ... ) 

727 >>> print(stmt) 

728 {printsql}UPDATE some_table SET status=:status 

729 WHERE some_table.data = :data_1 

730 RETURNING some_table.server_flag, some_table.updated_timestamp 

731 

732 The method may be invoked multiple times to add new entries to the 

733 list of expressions to be returned. 

734 

735 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to 

736 add new entries to the list of expressions to be returned. 

737 

738 The given collection of column expressions should be derived from the 

739 table that is the target of the INSERT, UPDATE, or DELETE. While 

740 :class:`_schema.Column` objects are typical, the elements can also be 

741 expressions: 

742 

743 .. sourcecode:: pycon+sql 

744 

745 >>> stmt = table.insert().returning( 

746 ... (table.c.first_name + " " + table.c.last_name).label("fullname") 

747 ... ) 

748 >>> print(stmt) 

749 {printsql}INSERT INTO some_table (first_name, last_name) 

750 VALUES (:first_name, :last_name) 

751 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname 

752 

753 Upon compilation, a RETURNING clause, or database equivalent, 

754 will be rendered within the statement. For INSERT and UPDATE, 

755 the values are the newly inserted/updated values. For DELETE, 

756 the values are those of the rows which were deleted. 

757 

758 Upon execution, the values of the columns to be returned are made 

759 available via the result set and can be iterated using 

760 :meth:`_engine.CursorResult.fetchone` and similar. 

761 For DBAPIs which do not 

762 natively support returning values (i.e. cx_oracle), SQLAlchemy will 

763 approximate this behavior at the result level so that a reasonable 

764 amount of behavioral neutrality is provided. 

765 

766 Note that not all databases/DBAPIs 

767 support RETURNING. For those backends with no support, 

768 an exception is raised upon compilation and/or execution. 

769 For those who do support it, the functionality across backends 

770 varies greatly, including restrictions on executemany() 

771 and other statements which return multiple rows. Please 

772 read the documentation notes for the database in use in 

773 order to determine the availability of RETURNING. 

774 

775 :param \*cols: series of columns, SQL expressions, or whole tables 

776 entities to be returned. 

777 :param sort_by_parameter_order: for a batch INSERT that is being 

778 executed against multiple parameter sets, organize the results of 

779 RETURNING so that the returned rows correspond to the order of 

780 parameter sets passed in. This applies only to an :term:`executemany` 

781 execution for supporting dialects and typically makes use of the 

782 :term:`insertmanyvalues` feature. 

783 

784 .. versionadded:: 2.0.10 

785 

786 .. seealso:: 

787 

788 :ref:`engine_insertmanyvalues_returning_order` - background on 

789 sorting of RETURNING rows for bulk INSERT (Core level discussion) 

790 

791 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of 

792 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion) 

793 

794 .. seealso:: 

795 

796 :meth:`.UpdateBase.return_defaults` - an alternative method tailored 

797 towards efficient fetching of server-side defaults and triggers 

798 for single-row INSERTs or UPDATEs. 

799 

800 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial` 

801 

802 """ # noqa: E501 

803 if __kw: 

804 raise _unexpected_kw("UpdateBase.returning()", __kw) 

805 if self._return_defaults: 

806 raise exc.InvalidRequestError( 

807 "return_defaults() is already configured on this statement" 

808 ) 

809 self._returning += tuple( 

810 coercions.expect(roles.ColumnsClauseRole, c) for c in cols 

811 ) 

812 if sort_by_parameter_order: 

813 if not self.is_insert: 

814 raise exc.ArgumentError( 

815 "The 'sort_by_parameter_order' argument to returning() " 

816 "only applies to INSERT statements" 

817 ) 

818 self._sort_by_parameter_order = True 

819 return self 

820 

821 def corresponding_column( 

822 self, column: KeyedColumnElement[Any], require_embedded: bool = False 

823 ) -> Optional[ColumnElement[Any]]: 

824 return self.exported_columns.corresponding_column( 

825 column, require_embedded=require_embedded 

826 ) 

827 

828 @util.ro_memoized_property 

829 def _all_selected_columns(self) -> _SelectIterable: 

830 return [c for c in _select_iterables(self._returning)] 

831 

832 @util.ro_memoized_property 

833 def exported_columns( 

834 self, 

835 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]: 

836 """Return the RETURNING columns as a column collection for this 

837 statement. 

838 

839 .. versionadded:: 1.4 

840 

841 """ 

842 return ColumnCollection( 

843 (c.key, c) 

844 for c in self._all_selected_columns 

845 if is_column_element(c) 

846 ).as_readonly() 

847 

848 @_generative 

849 def with_hint( 

850 self, 

851 text: str, 

852 selectable: Optional[_DMLTableArgument] = None, 

853 dialect_name: str = "*", 

854 ) -> Self: 

855 """Add a table hint for a single table to this 

856 INSERT/UPDATE/DELETE statement. 

857 

858 .. note:: 

859 

860 :meth:`.UpdateBase.with_hint` currently applies only to 

861 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use 

862 :meth:`.UpdateBase.prefix_with`. 

863 

864 The text of the hint is rendered in the appropriate 

865 location for the database backend in use, relative 

866 to the :class:`_schema.Table` that is the subject of this 

867 statement, or optionally to that of the given 

868 :class:`_schema.Table` passed as the ``selectable`` argument. 

869 

870 The ``dialect_name`` option will limit the rendering of a particular 

871 hint to a particular backend. Such as, to add a hint 

872 that only takes effect for SQL Server:: 

873 

874 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql") 

875 

876 :param text: Text of the hint. 

877 :param selectable: optional :class:`_schema.Table` that specifies 

878 an element of the FROM clause within an UPDATE or DELETE 

879 to be the subject of the hint - applies only to certain backends. 

880 :param dialect_name: defaults to ``*``, if specified as the name 

881 of a particular dialect, will apply these hints only when 

882 that dialect is in use. 

883 """ 

884 if selectable is None: 

885 selectable = self.table 

886 else: 

887 selectable = coercions.expect(roles.DMLTableRole, selectable) 

888 self._hints = self._hints.union({(selectable, dialect_name): text}) 

889 return self 

890 

891 @property 

892 def entity_description(self) -> Dict[str, Any]: 

893 """Return a :term:`plugin-enabled` description of the table and/or 

894 entity which this DML construct is operating against. 

895 

896 This attribute is generally useful when using the ORM, as an 

897 extended structure which includes information about mapped 

898 entities is returned. The section :ref:`queryguide_inspection` 

899 contains more background. 

900 

901 For a Core statement, the structure returned by this accessor 

902 is derived from the :attr:`.UpdateBase.table` attribute, and 

903 refers to the :class:`.Table` being inserted, updated, or deleted:: 

904 

905 >>> stmt = insert(user_table) 

906 >>> stmt.entity_description 

907 { 

908 "name": "user_table", 

909 "table": Table("user_table", ...) 

910 } 

911 

912 .. versionadded:: 1.4.33 

913 

914 .. seealso:: 

915 

916 :attr:`.UpdateBase.returning_column_descriptions` 

917 

918 :attr:`.Select.column_descriptions` - entity information for 

919 a :func:`.select` construct 

920 

921 :ref:`queryguide_inspection` - ORM background 

922 

923 """ 

924 meth = DMLState.get_plugin_class(self).get_entity_description 

925 return meth(self) 

926 

927 @property 

928 def returning_column_descriptions(self) -> List[Dict[str, Any]]: 

929 """Return a :term:`plugin-enabled` description of the columns 

930 which this DML construct is RETURNING against, in other words 

931 the expressions established as part of :meth:`.UpdateBase.returning`. 

932 

933 This attribute is generally useful when using the ORM, as an 

934 extended structure which includes information about mapped 

935 entities is returned. The section :ref:`queryguide_inspection` 

936 contains more background. 

937 

938 For a Core statement, the structure returned by this accessor is 

939 derived from the same objects that are returned by the 

940 :attr:`.UpdateBase.exported_columns` accessor:: 

941 

942 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name) 

943 >>> stmt.entity_description 

944 [ 

945 { 

946 "name": "id", 

947 "type": Integer, 

948 "expr": Column("id", Integer(), table=<user>, ...) 

949 }, 

950 { 

951 "name": "name", 

952 "type": String(), 

953 "expr": Column("name", String(), table=<user>, ...) 

954 }, 

955 ] 

956 

957 .. versionadded:: 1.4.33 

958 

959 .. seealso:: 

960 

961 :attr:`.UpdateBase.entity_description` 

962 

963 :attr:`.Select.column_descriptions` - entity information for 

964 a :func:`.select` construct 

965 

966 :ref:`queryguide_inspection` - ORM background 

967 

968 """ # noqa: E501 

969 meth = DMLState.get_plugin_class( 

970 self 

971 ).get_returning_column_descriptions 

972 return meth(self) 

973 

974 

975class ValuesBase(UpdateBase): 

976 """Supplies support for :meth:`.ValuesBase.values` to 

977 INSERT and UPDATE constructs.""" 

978 

979 __visit_name__ = "values_base" 

980 

981 _supports_multi_parameters = False 

982 

983 select: Optional[Select[Any]] = None 

984 """SELECT statement for INSERT .. FROM SELECT""" 

985 

986 _post_values_clause: Optional[ClauseElement] = None 

987 """used by extensions to Insert etc. to add additional syntacitcal 

988 constructs, e.g. ON CONFLICT etc.""" 

989 

990 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None 

991 _multi_values: Tuple[ 

992 Union[ 

993 Sequence[Dict[_DMLColumnElement, Any]], 

994 Sequence[Sequence[Any]], 

995 ], 

996 ..., 

997 ] = () 

998 

999 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None 

1000 

1001 _select_names: Optional[List[str]] = None 

1002 _inline: bool = False 

1003 

1004 def __init__(self, table: _DMLTableArgument):