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

1# sql/dml.py 

2# Copyright (C) 2009-2024 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 Tuple 

27from typing import Type 

28from typing import TYPE_CHECKING 

29from typing import TypeVar 

30from typing import Union 

31 

32from . import coercions 

33from . import roles 

34from . import util as sql_util 

35from ._typing import _unexpected_kw 

36from ._typing import is_column_element 

37from ._typing import is_named_from_clause 

38from .base import _entity_namespace_key 

39from .base import _exclusive_against 

40from .base import _from_objects 

41from .base import _generative 

42from .base import _select_iterables 

43from .base import ColumnCollection 

44from .base import CompileState 

45from .base import DialectKWArgs 

46from .base import Executable 

47from .base import Generative 

48from .base import HasCompileState 

49from .elements import BooleanClauseList 

50from .elements import ClauseElement 

51from .elements import ColumnClause 

52from .elements import ColumnElement 

53from .elements import Null 

54from .selectable import Alias 

55from .selectable import ExecutableReturnsRows 

56from .selectable import FromClause 

57from .selectable import HasCTE 

58from .selectable import HasPrefixes 

59from .selectable import Join 

60from .selectable import SelectLabelStyle 

61from .selectable import TableClause 

62from .selectable import TypedReturnsRows 

63from .sqltypes import NullType 

64from .visitors import InternalTraversal 

65from .. import exc 

66from .. import util 

67from ..util.typing import Self 

68from ..util.typing import TupleAny 

69from ..util.typing import TypeGuard 

70from ..util.typing import TypeVarTuple 

71from ..util.typing import Unpack 

72 

73 

74if TYPE_CHECKING: 

75 from ._typing import _ColumnExpressionArgument 

76 from ._typing import _ColumnsClauseArgument 

77 from ._typing import _DMLColumnArgument 

78 from ._typing import _DMLColumnKeyMapping 

79 from ._typing import _DMLTableArgument 

80 from ._typing import _T0 # noqa 

81 from ._typing import _T1 # noqa 

82 from ._typing import _T2 # noqa 

83 from ._typing import _T3 # noqa 

84 from ._typing import _T4 # noqa 

85 from ._typing import _T5 # noqa 

86 from ._typing import _T6 # noqa 

87 from ._typing import _T7 # noqa 

88 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa 

89 from .base import ReadOnlyColumnCollection 

90 from .compiler import SQLCompiler 

91 from .elements import KeyedColumnElement 

92 from .selectable import _ColumnsClauseElement 

93 from .selectable import _SelectIterable 

94 from .selectable import Select 

95 from .selectable import Selectable 

96 

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

98 

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

100 

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

102 

103else: 

104 isupdate = operator.attrgetter("isupdate") 

105 isdelete = operator.attrgetter("isdelete") 

106 isinsert = operator.attrgetter("isinsert") 

107 

108 

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

110_Ts = TypeVarTuple("_Ts") 

111 

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

113_DMLTableElement = Union[TableClause, Alias, Join] 

114 

115 

116class DMLState(CompileState): 

117 _no_parameters = True 

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

119 _multi_parameters: Optional[ 

120 List[MutableMapping[_DMLColumnElement, Any]] 

121 ] = None 

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

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

124 _primary_table: FromClause 

125 _supports_implicit_returning = True 

126 

127 isupdate = False 

128 isdelete = False 

129 isinsert = False 

130 

131 statement: UpdateBase 

132 

133 def __init__( 

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

135 ): 

136 raise NotImplementedError() 

137 

138 @classmethod 

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

140 return { 

141 "name": ( 

142 statement.table.name 

143 if is_named_from_clause(statement.table) 

144 else None 

145 ), 

146 "table": statement.table, 

147 } 

148 

149 @classmethod 

150 def get_returning_column_descriptions( 

151 cls, statement: UpdateBase 

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

153 return [ 

154 { 

155 "name": c.key, 

156 "type": c.type, 

157 "expr": c, 

158 } 

159 for c in statement._all_selected_columns 

160 ] 

161 

162 @property 

163 def dml_table(self) -> _DMLTableElement: 

164 return self.statement.table 

165 

166 if TYPE_CHECKING: 

167 

168 @classmethod 

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

170 

171 @classmethod 

172 def _get_multi_crud_kv_pairs( 

173 cls, 

174 statement: UpdateBase, 

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

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

177 return [ 

178 { 

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

180 for k, v in mapping.items() 

181 } 

182 for mapping in multi_kv_iterator 

183 ] 

184 

185 @classmethod 

186 def _get_crud_kv_pairs( 

187 cls, 

188 statement: UpdateBase, 

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

190 needs_to_be_cacheable: bool, 

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

192 return [ 

193 ( 

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

195 ( 

196 v 

197 if not needs_to_be_cacheable 

198 else coercions.expect( 

199 roles.ExpressionElementRole, 

200 v, 

201 type_=NullType(), 

202 is_crud=True, 

203 ) 

204 ), 

205 ) 

206 for k, v in kv_iterator 

207 ] 

208 

209 def _make_extra_froms( 

210 self, statement: DMLWhereBase 

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

212 froms: List[FromClause] = [] 

213 

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

215 primary_table = all_tables[0] 

216 seen = {primary_table} 

217 

218 consider = statement._where_criteria 

219 if self._dict_parameters: 

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

221 

222 for crit in consider: 

223 for item in _from_objects(crit): 

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

225 froms.append(item) 

226 seen.update(item._cloned_set) 

227 

228 froms.extend(all_tables[1:]) 

229 return primary_table, froms 

230 

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

232 if self._no_parameters: 

233 self._dict_parameters = statement._values 

234 self._no_parameters = False 

235 

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

237 assert statement._select_names is not None 

238 parameters: MutableMapping[_DMLColumnElement, Any] = { 

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

240 } 

241 

242 if self._no_parameters: 

243 self._no_parameters = False 

244 self._dict_parameters = parameters 

245 else: 

246 # this condition normally not reachable as the Insert 

247 # does not allow this construction to occur 

248 assert False, "This statement already has parameters" 

249 

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

251 raise exc.InvalidRequestError( 

252 "%s construct does not support " 

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

254 ) 

255 

256 def _cant_mix_formats_error(self) -> NoReturn: 

257 raise exc.InvalidRequestError( 

258 "Can't mix single and multiple VALUES " 

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

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

261 "ambiguous." 

262 ) 

263 

264 

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

266class InsertDMLState(DMLState): 

267 isinsert = True 

268 

269 include_table_with_column_exprs = False 

270 

271 _has_multi_parameters = False 

272 

273 def __init__( 

274 self, 

275 statement: Insert, 

276 compiler: SQLCompiler, 

277 disable_implicit_returning: bool = False, 

278 **kw: Any, 

279 ): 

280 self.statement = statement 

281 self._primary_table = statement.table 

282 

283 if disable_implicit_returning: 

284 self._supports_implicit_returning = False 

285 

286 self.isinsert = True 

287 if statement._select_names: 

288 self._process_select_values(statement) 

289 if statement._values is not None: 

290 self._process_values(statement) 

291 if statement._multi_values: 

292 self._process_multi_values(statement) 

293 

294 @util.memoized_property 

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

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

297 return [ 

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

299 for col in self._dict_parameters or () 

300 ] 

301 

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

303 if self._no_parameters: 

304 self._has_multi_parameters = False 

305 self._dict_parameters = statement._values 

306 self._no_parameters = False 

307 elif self._has_multi_parameters: 

308 self._cant_mix_formats_error() 

309 

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

311 for parameters in statement._multi_values: 

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

313 ( 

314 { 

315 c.key: value 

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

317 } 

318 if isinstance(parameter_set, collections_abc.Sequence) 

319 else parameter_set 

320 ) 

321 for parameter_set in parameters 

322 ] 

323 

324 if self._no_parameters: 

325 self._no_parameters = False 

326 self._has_multi_parameters = True 

327 self._multi_parameters = multi_parameters 

328 self._dict_parameters = self._multi_parameters[0] 

329 elif not self._has_multi_parameters: 

330 self._cant_mix_formats_error() 

331 else: 

332 assert self._multi_parameters 

333 self._multi_parameters.extend(multi_parameters) 

334 

335 

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

337class UpdateDMLState(DMLState): 

338 isupdate = True 

339 

340 include_table_with_column_exprs = False 

341 

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

343 self.statement = statement 

344 

345 self.isupdate = True 

346 if statement._ordered_values is not None: 

347 self._process_ordered_values(statement) 

348 elif statement._values is not None: 

349 self._process_values(statement) 

350 elif statement._multi_values: 

351 self._no_multi_values_supported(statement) 

352 t, ef = self._make_extra_froms(statement) 

353 self._primary_table = t 

354 self._extra_froms = ef 

355 

356 self.is_multitable = mt = ef 

357 self.include_table_with_column_exprs = bool( 

358 mt and compiler.render_table_with_column_in_update_from 

359 ) 

360 

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

362 parameters = statement._ordered_values 

363 

364 if self._no_parameters: 

365 self._no_parameters = False 

366 assert parameters is not None 

367 self._dict_parameters = dict(parameters) 

368 self._ordered_values = parameters 

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

370 else: 

371 raise exc.InvalidRequestError( 

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

373 "with any other values() call" 

374 ) 

375 

376 

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

378class DeleteDMLState(DMLState): 

379 isdelete = True 

380 

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

382 self.statement = statement 

383 

384 self.isdelete = True 

385 t, ef = self._make_extra_froms(statement) 

386 self._primary_table = t 

387 self._extra_froms = ef 

388 self.is_multitable = ef 

389 

390 

391class UpdateBase( 

392 roles.DMLRole, 

393 HasCTE, 

394 HasCompileState, 

395 DialectKWArgs, 

396 HasPrefixes, 

397 Generative, 

398 ExecutableReturnsRows, 

399 ClauseElement, 

400): 

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

402 

403 __visit_name__ = "update_base" 

404 

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

406 util.EMPTY_DICT 

407 ) 

408 named_with_column = False 

409 

410 _label_style: SelectLabelStyle = ( 

411 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY 

412 ) 

413 table: _DMLTableElement 

414 

415 _return_defaults = False 

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

417 None 

418 ) 

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

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

421 

422 is_dml = True 

423 

424 def _generate_fromclause_column_proxies( 

425 self, fromclause: FromClause 

426 ) -> None: 

427 fromclause._columns._populate_separate_keys( 

428 col._make_proxy(fromclause) 

429 for col in self._all_selected_columns 

430 if is_column_element(col) 

431 ) 

432 

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

434 """Set the parameters for the statement. 

435 

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

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

438 SET/VALUES clause of UPDATE and INSERT. 

439 

440 """ 

441 raise NotImplementedError( 

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

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

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

445 ) 

446 

447 @_generative 

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

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

450 

451 e.g.:: 

452 

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

454 

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

456 associated with the constructor. 

457 

458 

459 """ 

460 self._validate_dialect_kwargs(opt) 

461 return self 

462 

463 @_generative 

464 def return_defaults( 

465 self, 

466 *cols: _DMLColumnArgument, 

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

468 sort_by_parameter_order: bool = False, 

469 ) -> Self: 

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

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

472 backends only. 

473 

474 .. deepalchemy:: 

475 

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

477 for its internal work in fetching newly generated primary key 

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

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

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

481 ORM inserts. Its behavior is fairly idiosyncratic 

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

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

484 add RETURNING clauses to their INSERT, UPDATE and DELETE 

485 statements. 

486 

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

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

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

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

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

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

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

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

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

496 value. 

497 

498 However, when the statement is modified by calling 

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

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

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

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

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

504 from the statement's execution, not only will 

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

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

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

508 of server generated 

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

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

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

512 

513 When invoking INSERT statements with multiple rows using 

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

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

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

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

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

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

520 values for each row inserted. The 

521 :attr:`.CursorResult.inserted_primary_key` and 

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

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

524 

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

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

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

528 INSERT, UPDATE or DELETE statement proceeds normally. 

529 

530 E.g.:: 

531 

532 stmt = table.insert().values(data='newdata').return_defaults() 

533 

534 result = connection.execute(stmt) 

535 

536 server_created_at = result.returned_defaults['created_at'] 

537 

538 When used against an UPDATE statement 

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

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

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

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

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

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

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

546 normally change values when a DELETE statement proceeds. 

547 

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

549 for DELETE statements also and has been moved from 

550 :class:`.ValuesBase` to :class:`.UpdateBase`. 

551 

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

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

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

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

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

557 at a time. 

558 

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

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

561 

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

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

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

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

566 

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

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

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

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

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

572 attribute being left unpopulated. 

573 

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

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

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

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

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

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

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

581 ``False``. 

582 

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

584 backend database driver supports the 

585 :ref:`insertmanyvalues <engine_insertmanyvalues>` 

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

587 When executemany is used, the 

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

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

590 will return the inserted defaults and primary keys. 

591 

592 .. versionadded:: 1.4 Added 

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

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

595 In version 2.0, the underlying implementation which fetches and 

596 populates the data for these attributes was generalized to be 

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

598 supported by the ``psycopg2`` driver. 

599 

600 

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

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

603 will be fetched. 

604 :param supplemental_cols: optional list of RETURNING expressions, 

605 in the same form as one would pass to the 

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

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

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

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

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

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

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

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

614 reliably be targeted positionally. 

615 

616 .. versionadded:: 2.0 

617 

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

619 executed against multiple parameter sets, organize the results of 

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

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

622 execution for supporting dialects and typically makes use of the 

623 :term:`insertmanyvalues` feature. 

624 

625 .. versionadded:: 2.0.10 

626 

627 .. seealso:: 

628 

629 :ref:`engine_insertmanyvalues_returning_order` - background on 

630 sorting of RETURNING rows for bulk INSERT 

631 

632 .. seealso:: 

633 

634 :meth:`.UpdateBase.returning` 

635 

636 :attr:`_engine.CursorResult.returned_defaults` 

637 

638 :attr:`_engine.CursorResult.returned_defaults_rows` 

639 

640 :attr:`_engine.CursorResult.inserted_primary_key` 

641 

642 :attr:`_engine.CursorResult.inserted_primary_key_rows` 

643 

644 """ 

645 

646 if self._return_defaults: 

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

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

649 # are columns in the collection 

650 if self._return_defaults_columns and cols: 

651 self._return_defaults_columns = tuple( 

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

653 coercions.expect(roles.ColumnsClauseRole, c) 

654 for c in cols 

655 ) 

656 ) 

657 else: 

658 # set for all columns 

659 self._return_defaults_columns = () 

660 else: 

661 self._return_defaults_columns = tuple( 

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

663 ) 

664 self._return_defaults = True 

665 if sort_by_parameter_order: 

666 if not self.is_insert: 

667 raise exc.ArgumentError( 

668 "The 'sort_by_parameter_order' argument to " 

669 "return_defaults() only applies to INSERT statements" 

670 ) 

671 self._sort_by_parameter_order = True 

672 if supplemental_cols: 

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

674 # is for test suites but also for vertical splicing 

675 supplemental_col_tup = ( 

676 coercions.expect(roles.ColumnsClauseRole, c) 

677 for c in supplemental_cols 

678 ) 

679 

680 if self._supplemental_returning is None: 

681 self._supplemental_returning = tuple( 

682 util.unique_list(supplemental_col_tup) 

683 ) 

684 else: 

685 self._supplemental_returning = tuple( 

686 util.unique_list( 

687 self._supplemental_returning 

688 + tuple(supplemental_col_tup) 

689 ) 

690 ) 

691 

692 return self 

693 

694 @_generative 

695 def returning( 

696 self, 

697 *cols: _ColumnsClauseArgument[Any], 

698 sort_by_parameter_order: bool = False, 

699 **__kw: Any, 

700 ) -> UpdateBase: 

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

702 

703 e.g.: 

704 

705 .. sourcecode:: pycon+sql 

706 

707 >>> stmt = ( 

708 ... table.update() 

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

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

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

712 ... ) 

713 >>> print(stmt) 

714 {printsql}UPDATE some_table SET status=:status 

715 WHERE some_table.data = :data_1 

716 RETURNING some_table.server_flag, some_table.updated_timestamp 

717 

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

719 list of expressions to be returned. 

720 

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

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

723 

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

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

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

727 expressions: 

728 

729 .. sourcecode:: pycon+sql 

730 

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

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

733 ... ) 

734 >>> print(stmt) 

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

736 VALUES (:first_name, :last_name) 

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

738 

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

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

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

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

743 

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

745 available via the result set and can be iterated using 

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

747 For DBAPIs which do not 

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

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

750 amount of behavioral neutrality is provided. 

751 

752 Note that not all databases/DBAPIs 

753 support RETURNING. For those backends with no support, 

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

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

756 varies greatly, including restrictions on executemany() 

757 and other statements which return multiple rows. Please 

758 read the documentation notes for the database in use in 

759 order to determine the availability of RETURNING. 

760 

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

762 entities to be returned. 

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

764 executed against multiple parameter sets, organize the results of 

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

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

767 execution for supporting dialects and typically makes use of the 

768 :term:`insertmanyvalues` feature. 

769 

770 .. versionadded:: 2.0.10 

771 

772 .. seealso:: 

773 

774 :ref:`engine_insertmanyvalues_returning_order` - background on 

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

776 

777 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of 

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

779 

780 .. seealso:: 

781 

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

783 towards efficient fetching of server-side defaults and triggers 

784 for single-row INSERTs or UPDATEs. 

785 

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

787 

788 """ # noqa: E501 

789 if __kw: 

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

791 if self._return_defaults: 

792 raise exc.InvalidRequestError( 

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

794 ) 

795 self._returning += tuple( 

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

797 ) 

798 if sort_by_parameter_order: 

799 if not self.is_insert: 

800 raise exc.ArgumentError( 

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

802 "only applies to INSERT statements" 

803 ) 

804 self._sort_by_parameter_order = True 

805 return self 

806 

807 def corresponding_column( 

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

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

810 return self.exported_columns.corresponding_column( 

811 column, require_embedded=require_embedded 

812 ) 

813 

814 @util.ro_memoized_property 

815 def _all_selected_columns(self) -> _SelectIterable: 

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

817 

818 @util.ro_memoized_property 

819 def exported_columns( 

820 self, 

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

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

823 statement. 

824 

825 .. versionadded:: 1.4 

826 

827 """ 

828 return ColumnCollection( 

829 (c.key, c) 

830 for c in self._all_selected_columns 

831 if is_column_element(c) 

832 ).as_readonly() 

833 

834 @_generative 

835 def with_hint( 

836 self, 

837 text: str, 

838 selectable: Optional[_DMLTableArgument] = None, 

839 dialect_name: str = "*", 

840 ) -> Self: 

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

842 INSERT/UPDATE/DELETE statement. 

843 

844 .. note:: 

845 

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

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

848 :meth:`.UpdateBase.prefix_with`. 

849 

850 The text of the hint is rendered in the appropriate 

851 location for the database backend in use, relative 

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

853 statement, or optionally to that of the given 

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

855 

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

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

858 that only takes effect for SQL Server:: 

859 

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

861 

862 :param text: Text of the hint. 

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

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

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

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

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

868 that dialect is in use. 

869 """ 

870 if selectable is None: 

871 selectable = self.table 

872 else: 

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

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

875 return self 

876 

877 @property 

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

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

880 entity which this DML construct is operating against. 

881 

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

883 extended structure which includes information about mapped 

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

885 contains more background. 

886 

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

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

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

890 

891 >>> stmt = insert(user_table) 

892 >>> stmt.entity_description 

893 { 

894 "name": "user_table", 

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

896 } 

897 

898 .. versionadded:: 1.4.33 

899 

900 .. seealso:: 

901 

902 :attr:`.UpdateBase.returning_column_descriptions` 

903 

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

905 a :func:`.select` construct 

906 

907 :ref:`queryguide_inspection` - ORM background 

908 

909 """ 

910 meth = DMLState.get_plugin_class(self).get_entity_description 

911 return meth(self) 

912 

913 @property 

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

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

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

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

918 

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

920 extended structure which includes information about mapped 

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

922 contains more background. 

923 

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

925 derived from the same objects that are returned by the 

926 :attr:`.UpdateBase.exported_columns` accessor:: 

927 

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

929 >>> stmt.entity_description 

930 [ 

931 { 

932 "name": "id", 

933 "type": Integer, 

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

935 }, 

936 { 

937 "name": "name", 

938 "type": String(), 

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

940 }, 

941 ] 

942 

943 .. versionadded:: 1.4.33 

944 

945 .. seealso:: 

946 

947 :attr:`.UpdateBase.entity_description` 

948 

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

950 a :func:`.select` construct 

951 

952 :ref:`queryguide_inspection` - ORM background 

953 

954 """ # noqa: E501 

955 meth = DMLState.get_plugin_class( 

956 self 

957 ).get_returning_column_descriptions 

958 return meth(self) 

959 

960 

961class ValuesBase(UpdateBase): 

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

963 INSERT and UPDATE constructs.""" 

964 

965 __visit_name__ = "values_base" 

966 

967 _supports_multi_parameters = False 

968 

969 select: Optional[Select[Unpack[TupleAny]]] = None 

970 """SELECT statement for INSERT .. FROM SELECT""" 

971 

972 _post_values_clause: Optional[ClauseElement] = None 

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

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

975 

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

977 _multi_values: Tuple[ 

978 Union[ 

979 Sequence[Dict[_DMLColumnElement, Any]], 

980 Sequence[Sequence[Any]], 

981 ], 

982 ..., 

983 ] = () 

984 

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

986 

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

988 _inline: bool = False 

989 

990 def __init__(self, table: _DMLTableArgument): 

991 self.table = coercions.expect( 

992 roles.DMLTableRole, table, apply_propagate_attrs=self 

993 ) 

994 

995 @_generative 

996 @_exclusive_against( 

997 "_select_names", 

998 "_ordered_values", 

999 msgs={ 

1000 "_select_names": "This construct already inserts from a SELECT", 

1001 "_ordered_values": "This statement already has ordered " 

1002 "values present",