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

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 Literal 

22from typing import MutableMapping 

23from typing import NoReturn 

24from typing import Optional 

25from typing import overload 

26from typing import Sequence 

27from typing import Set 

28from typing import Tuple 

29from typing import Type 

30from typing import TYPE_CHECKING 

31from typing import TypeGuard 

32from typing import TypeVar 

33from typing import Union 

34 

35from . import coercions 

36from . import roles 

37from . import util as sql_util 

38from ._typing import _unexpected_kw 

39from ._typing import is_column_element 

40from ._typing import is_named_from_clause 

41from .base import _entity_namespace_key 

42from .base import _exclusive_against 

43from .base import _from_objects 

44from .base import _generative 

45from .base import _select_iterables 

46from .base import ColumnCollection 

47from .base import ColumnSet 

48from .base import CompileState 

49from .base import DialectKWArgs 

50from .base import Executable 

51from .base import Generative 

52from .base import HasCompileState 

53from .base import HasSyntaxExtensions 

54from .base import SyntaxExtension 

55from .elements import BooleanClauseList 

56from .elements import ClauseElement 

57from .elements import ColumnClause 

58from .elements import ColumnElement 

59from .elements import Null 

60from .selectable import Alias 

61from .selectable import ExecutableReturnsRows 

62from .selectable import FromClause 

63from .selectable import HasCTE 

64from .selectable import HasPrefixes 

65from .selectable import Join 

66from .selectable import SelectLabelStyle 

67from .selectable import TableClause 

68from .selectable import TypedReturnsRows 

69from .sqltypes import NullType 

70from .visitors import InternalTraversal 

71from .. import exc 

72from .. import util 

73from ..util.typing import Self 

74from ..util.typing import TupleAny 

75from ..util.typing import TypeVarTuple 

76from ..util.typing import Unpack 

77 

78 

79if TYPE_CHECKING: 

80 from ._typing import _ColumnExpressionArgument 

81 from ._typing import _ColumnsClauseArgument 

82 from ._typing import _DMLColumnArgument 

83 from ._typing import _DMLColumnKeyMapping 

84 from ._typing import _DMLTableArgument 

85 from ._typing import _T0 # noqa 

86 from ._typing import _T1 # noqa 

87 from ._typing import _T2 # noqa 

88 from ._typing import _T3 # noqa 

89 from ._typing import _T4 # noqa 

90 from ._typing import _T5 # noqa 

91 from ._typing import _T6 # noqa 

92 from ._typing import _T7 # noqa 

93 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa 

94 from .base import ReadOnlyColumnCollection 

95 from .compiler import SQLCompiler 

96 from .elements import KeyedColumnElement 

97 from .selectable import _ColumnsClauseElement 

98 from .selectable import _SelectIterable 

99 from .selectable import Select 

100 from .selectable import Selectable 

101 

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

103 

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

105 

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

107 

108else: 

109 isupdate = operator.attrgetter("isupdate") 

110 isdelete = operator.attrgetter("isdelete") 

111 isinsert = operator.attrgetter("isinsert") 

112 

113 

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

115_Ts = TypeVarTuple("_Ts") 

116 

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

118_DMLTableElement = Union[TableClause, Alias, Join] 

119 

120 

121class DMLState(CompileState): 

122 _no_parameters = True 

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

124 _multi_parameters: Optional[ 

125 List[MutableMapping[_DMLColumnElement, Any]] 

126 ] = None 

127 _maintain_values_ordering: bool = False 

128 _primary_table: FromClause 

129 _supports_implicit_returning = True 

130 

131 isupdate = False 

132 isdelete = False 

133 isinsert = False 

134 

135 statement: UpdateBase 

136 

137 def __init__( 

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

139 ): 

140 raise NotImplementedError() 

141 

142 @classmethod 

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

144 return { 

145 "name": ( 

146 statement.table.name 

147 if is_named_from_clause(statement.table) 

148 else None 

149 ), 

150 "table": statement.table, 

151 } 

152 

153 @classmethod 

154 def get_returning_column_descriptions( 

155 cls, statement: UpdateBase 

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

157 return [ 

158 { 

159 "name": c.key, 

160 "type": c.type, 

161 "expr": c, 

162 } 

163 for c in statement._all_selected_columns 

164 ] 

165 

166 @property 

167 def dml_table(self) -> _DMLTableElement: 

168 return self.statement.table 

169 

170 if TYPE_CHECKING: 

171 

172 @classmethod 

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

174 

175 @classmethod 

176 def _get_multi_crud_kv_pairs( 

177 cls, 

178 statement: UpdateBase, 

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

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

181 return [ 

182 { 

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

184 for k, v in mapping.items() 

185 } 

186 for mapping in multi_kv_iterator 

187 ] 

188 

189 @classmethod 

190 def _get_crud_kv_pairs( 

191 cls, 

192 statement: UpdateBase, 

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

194 needs_to_be_cacheable: bool, 

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

196 return [ 

197 ( 

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

199 ( 

200 v 

201 if not needs_to_be_cacheable 

202 else coercions.expect( 

203 roles.ExpressionElementRole, 

204 v, 

205 type_=NullType(), 

206 is_crud=True, 

207 ) 

208 ), 

209 ) 

210 for k, v in kv_iterator 

211 ] 

212 

213 def _make_extra_froms( 

214 self, statement: DMLWhereBase 

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

216 froms: List[FromClause] = [] 

217 

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

219 primary_table = all_tables[0] 

220 seen = {primary_table} 

221 

222 consider = statement._where_criteria 

223 if self._dict_parameters: 

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

225 

226 for crit in consider: 

227 for item in _from_objects(crit): 

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

229 froms.append(item) 

230 seen.update(item._cloned_set) 

231 

232 froms.extend(all_tables[1:]) 

233 return primary_table, froms 

234 

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

236 if self._no_parameters: 

237 self._dict_parameters = statement._values 

238 self._no_parameters = False 

239 

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

241 assert statement._select_names is not None 

242 parameters: MutableMapping[_DMLColumnElement, Any] = { 

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

244 } 

245 

246 if self._no_parameters: 

247 self._no_parameters = False 

248 self._dict_parameters = parameters 

249 else: 

250 # this condition normally not reachable as the Insert 

251 # does not allow this construction to occur 

252 assert False, "This statement already has parameters" 

253 

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

255 raise exc.InvalidRequestError( 

256 "%s construct does not support " 

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

258 ) 

259 

260 def _cant_mix_formats_error(self) -> NoReturn: 

261 raise exc.InvalidRequestError( 

262 "Can't mix single and multiple VALUES " 

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

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

265 "ambiguous." 

266 ) 

267 

268 

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

270class InsertDMLState(DMLState): 

271 isinsert = True 

272 

273 include_table_with_column_exprs = False 

274 

275 _has_multi_parameters = False 

276 

277 def __init__( 

278 self, 

279 statement: Insert, 

280 compiler: SQLCompiler, 

281 disable_implicit_returning: bool = False, 

282 **kw: Any, 

283 ): 

284 self.statement = statement 

285 self._primary_table = statement.table 

286 

287 if disable_implicit_returning: 

288 self._supports_implicit_returning = False 

289 

290 self.isinsert = True 

291 if statement._select_names: 

292 self._process_select_values(statement) 

293 if statement._values is not None: 

294 self._process_values(statement) 

295 if statement._multi_values: 

296 self._process_multi_values(statement) 

297 

298 @util.memoized_property 

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

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

301 return [ 

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

303 for col in self._dict_parameters or () 

304 ] 

305 

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

307 if self._no_parameters: 

308 self._has_multi_parameters = False 

309 self._dict_parameters = statement._values 

310 self._no_parameters = False 

311 elif self._has_multi_parameters: 

312 self._cant_mix_formats_error() 

313 

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

315 for parameters in statement._multi_values: 

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

317 ( 

318 { 

319 c.key: value 

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

321 } 

322 if isinstance(parameter_set, collections_abc.Sequence) 

323 else parameter_set 

324 ) 

325 for parameter_set in parameters 

326 ] 

327 

328 if self._no_parameters: 

329 self._no_parameters = False 

330 self._has_multi_parameters = True 

331 self._multi_parameters = multi_parameters 

332 self._dict_parameters = self._multi_parameters[0] 

333 elif not self._has_multi_parameters: 

334 self._cant_mix_formats_error() 

335 else: 

336 assert self._multi_parameters 

337 self._multi_parameters.extend(multi_parameters) 

338 

339 

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

341class UpdateDMLState(DMLState): 

342 isupdate = True 

343 

344 include_table_with_column_exprs = False 

345 

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

347 self.statement = statement 

348 

349 self.isupdate = True 

350 if statement._maintain_values_ordering: 

351 self._process_ordered_values(statement) 

352 elif statement._values is not None: 

353 self._process_values(statement) 

354 elif statement._multi_values: 

355 self._no_multi_values_supported(statement) 

356 t, ef = self._make_extra_froms(statement) 

357 self._primary_table = t 

358 self._extra_froms = ef 

359 

360 self.is_multitable = mt = ef 

361 self.include_table_with_column_exprs = bool( 

362 mt and compiler.render_table_with_column_in_update_from 

363 ) 

364 

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

366 parameters = statement._values 

367 if self._no_parameters: 

368 self._no_parameters = False 

369 assert parameters is not None 

370 self._dict_parameters = dict(parameters) 

371 self._maintain_values_ordering = True 

372 else: 

373 raise exc.InvalidRequestError( 

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

375 "with any other values() call" 

376 ) 

377 

378 

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

380class DeleteDMLState(DMLState): 

381 isdelete = True 

382 

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

384 self.statement = statement 

385 

386 self.isdelete = True 

387 t, ef = self._make_extra_froms(statement) 

388 self._primary_table = t 

389 self._extra_froms = ef 

390 self.is_multitable = ef 

391 

392 

393class UpdateBase( 

394 roles.DMLRole, 

395 HasCTE, 

396 HasCompileState, 

397 DialectKWArgs, 

398 HasPrefixes, 

399 Generative, 

400 ExecutableReturnsRows, 

401 ClauseElement, 

402): 

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

404 

405 __visit_name__ = "update_base" 

406 

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

408 util.EMPTY_DICT 

409 ) 

410 named_with_column = False 

411 

412 _label_style: SelectLabelStyle = ( 

413 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY 

414 ) 

415 table: _DMLTableElement 

416 

417 _return_defaults = False 

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

419 None 

420 ) 

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

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

423 

424 is_dml = True 

425 

426 def _generate_fromclause_column_proxies( 

427 self, 

428 fromclause: FromClause, 

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

430 primary_key: ColumnSet, 

431 foreign_keys: Set[KeyedColumnElement[Any]], 

432 ) -> None: 

433 prox = [ 

434 c._make_proxy( 

435 fromclause, 

436 key=proxy_key, 

437 name=required_label_name, 

438 name_is_truncatable=True, 

439 primary_key=primary_key, 

440 foreign_keys=foreign_keys, 

441 ) 

442 for ( 

443 required_label_name, 

444 proxy_key, 

445 fallback_label_name, 

446 c, 

447 repeated, 

448 ) in (self._generate_columns_plus_names(False)) 

449 if is_column_element(c) 

450 ] 

451 

452 columns._populate_separate_keys(prox) 

453 

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

455 """Set the parameters for the statement. 

456 

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

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

459 SET/VALUES clause of UPDATE and INSERT. 

460 

461 """ 

462 raise NotImplementedError( 

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

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

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

466 ) 

467 

468 @_generative 

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

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

471 

472 e.g.:: 

473 

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

475 

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

477 associated with the constructor. 

478 

479 

480 """ 

481 self._validate_dialect_kwargs(opt) 

482 return self 

483 

484 @_generative 

485 def return_defaults( 

486 self, 

487 *cols: _DMLColumnArgument, 

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

489 sort_by_parameter_order: bool = False, 

490 ) -> Self: 

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

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

493 backends only. 

494 

495 .. deepalchemy:: 

496 

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

498 for its internal work in fetching newly generated primary key 

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

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

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

502 ORM inserts. Its behavior is fairly idiosyncratic 

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

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

505 add RETURNING clauses to their INSERT, UPDATE and DELETE 

506 statements. 

507 

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

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

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

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

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

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

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

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

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

517 value. 

518 

519 However, when the statement is modified by calling 

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

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

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

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

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

525 from the statement's execution, not only will 

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

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

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

529 of server generated 

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

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

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

533 

534 When invoking INSERT statements with multiple rows using 

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

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

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

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

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

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

541 values for each row inserted. The 

542 :attr:`.CursorResult.inserted_primary_key` and 

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

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

545 

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

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

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

549 INSERT, UPDATE or DELETE statement proceeds normally. 

550 

551 E.g.:: 

552 

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

554 

555 result = connection.execute(stmt) 

556 

557 server_created_at = result.returned_defaults["created_at"] 

558 

559 When used against an UPDATE statement 

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

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

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

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

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

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

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

567 normally change values when a DELETE statement proceeds. 

568 

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

570 for DELETE statements also and has been moved from 

571 :class:`.ValuesBase` to :class:`.UpdateBase`. 

572 

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

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

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

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

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

578 at a time. 

579 

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

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

582 

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

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

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

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

587 

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

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

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

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

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

593 attribute being left unpopulated. 

594 

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

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

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

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

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

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

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

602 ``False``. 

603 

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

605 backend database driver supports the 

606 :ref:`insertmanyvalues <engine_insertmanyvalues>` 

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

608 When executemany is used, the 

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

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

611 will return the inserted defaults and primary keys. 

612 

613 .. versionadded:: 1.4 Added 

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

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

616 In version 2.0, the underlying implementation which fetches and 

617 populates the data for these attributes was generalized to be 

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

619 supported by the ``psycopg2`` driver. 

620 

621 

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

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

624 will be fetched. 

625 :param supplemental_cols: optional list of RETURNING expressions, 

626 in the same form as one would pass to the 

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

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

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

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

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

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

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

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

635 reliably be targeted positionally. 

636 

637 .. versionadded:: 2.0 

638 

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

640 executed against multiple parameter sets, organize the results of 

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

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

643 execution for supporting dialects and typically makes use of the 

644 :term:`insertmanyvalues` feature. 

645 

646 .. versionadded:: 2.0.10 

647 

648 .. seealso:: 

649 

650 :ref:`engine_insertmanyvalues_returning_order` - background on 

651 sorting of RETURNING rows for bulk INSERT 

652 

653 .. seealso:: 

654 

655 :meth:`.UpdateBase.returning` 

656 

657 :attr:`_engine.CursorResult.returned_defaults` 

658 

659 :attr:`_engine.CursorResult.returned_defaults_rows` 

660 

661 :attr:`_engine.CursorResult.inserted_primary_key` 

662 

663 :attr:`_engine.CursorResult.inserted_primary_key_rows` 

664 

665 """ 

666 

667 if self._return_defaults: 

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

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

670 # are columns in the collection 

671 if self._return_defaults_columns and cols: 

672 self._return_defaults_columns = tuple( 

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

674 coercions.expect(roles.ColumnsClauseRole, c) 

675 for c in cols 

676 ) 

677 ) 

678 else: 

679 # set for all columns 

680 self._return_defaults_columns = () 

681 else: 

682 self._return_defaults_columns = tuple( 

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

684 ) 

685 self._return_defaults = True 

686 if sort_by_parameter_order: 

687 if not self.is_insert: 

688 raise exc.ArgumentError( 

689 "The 'sort_by_parameter_order' argument to " 

690 "return_defaults() only applies to INSERT statements" 

691 ) 

692 self._sort_by_parameter_order = True 

693 if supplemental_cols: 

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

695 # is for test suites but also for vertical splicing 

696 supplemental_col_tup = ( 

697 coercions.expect(roles.ColumnsClauseRole, c) 

698 for c in supplemental_cols 

699 ) 

700 

701 if self._supplemental_returning is None: 

702 self._supplemental_returning = tuple( 

703 util.unique_list(supplemental_col_tup) 

704 ) 

705 else: 

706 self._supplemental_returning = tuple( 

707 util.unique_list( 

708 self._supplemental_returning 

709 + tuple(supplemental_col_tup) 

710 ) 

711 ) 

712 

713 return self 

714 

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

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

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

718 

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

720 so we return False for derives. 

721 

722 """ 

723 return False 

724 

725 @_generative 

726 def returning( 

727 self, 

728 *cols: _ColumnsClauseArgument[Any], 

729 sort_by_parameter_order: bool = False, 

730 **__kw: Any, 

731 ) -> UpdateBase: 

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

733 

734 e.g.: 

735 

736 .. sourcecode:: pycon+sql 

737 

738 >>> stmt = ( 

739 ... table.update() 

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

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

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

743 ... ) 

744 >>> print(stmt) 

745 {printsql}UPDATE some_table SET status=:status 

746 WHERE some_table.data = :data_1 

747 RETURNING some_table.server_flag, some_table.updated_timestamp 

748 

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

750 list of expressions to be returned. 

751 

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

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

754 

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

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

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

758 expressions: 

759 

760 .. sourcecode:: pycon+sql 

761 

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

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

764 ... ) 

765 >>> print(stmt) 

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

767 VALUES (:first_name, :last_name) 

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

769 

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

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

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

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

774 

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

776 available via the result set and can be iterated using 

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

778 For DBAPIs which do not 

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

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

781 amount of behavioral neutrality is provided. 

782 

783 Note that not all databases/DBAPIs 

784 support RETURNING. For those backends with no support, 

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

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

787 varies greatly, including restrictions on executemany() 

788 and other statements which return multiple rows. Please 

789 read the documentation notes for the database in use in 

790 order to determine the availability of RETURNING. 

791 

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

793 entities to be returned. 

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

795 executed against multiple parameter sets, organize the results of 

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

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

798 execution for supporting dialects and typically makes use of the 

799 :term:`insertmanyvalues` feature. 

800 

801 .. versionadded:: 2.0.10 

802 

803 .. seealso:: 

804 

805 :ref:`engine_insertmanyvalues_returning_order` - background on 

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

807 

808 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of 

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

810 

811 .. seealso:: 

812 

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

814 towards efficient fetching of server-side defaults and triggers 

815 for single-row INSERTs or UPDATEs. 

816 

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

818 

819 """ # noqa: E501 

820 if __kw: 

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

822 if self._return_defaults: 

823 raise exc.InvalidRequestError( 

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

825 ) 

826 self._returning += tuple( 

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

828 ) 

829 if sort_by_parameter_order: 

830 if not self.is_insert: 

831 raise exc.ArgumentError( 

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

833 "only applies to INSERT statements" 

834 ) 

835 self._sort_by_parameter_order = True 

836 return self 

837 

838 def corresponding_column( 

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

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

841 return self.exported_columns.corresponding_column( 

842 column, require_embedded=require_embedded 

843 ) 

844 

845 @util.ro_memoized_property 

846 def _all_selected_columns(self) -> _SelectIterable: 

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

848 

849 @util.ro_memoized_property 

850 def exported_columns( 

851 self, 

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

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

854 statement. 

855 

856 .. versionadded:: 1.4 

857 

858 """ 

859 return ColumnCollection( 

860 (c.key, c) 

861 for c in self._all_selected_columns 

862 if is_column_element(c) 

863 ).as_readonly() 

864 

865 @_generative 

866 def with_hint( 

867 self, 

868 text: str, 

869 selectable: Optional[_DMLTableArgument] = None, 

870 dialect_name: str = "*", 

871 ) -> Self: 

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

873 INSERT/UPDATE/DELETE statement. 

874 

875 .. note:: 

876 

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

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

879 :meth:`.UpdateBase.prefix_with`. 

880 

881 The text of the hint is rendered in the appropriate 

882 location for the database backend in use, relative 

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

884 statement, or optionally to that of the given 

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

886 

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

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

889 that only takes effect for SQL Server:: 

890 

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

892 

893 :param text: Text of the hint. 

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

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

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

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

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

899 that dialect is in use. 

900 """ 

901 if selectable is None: 

902 selectable = self.table 

903 else: 

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

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

906 return self 

907 

908 @property 

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

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

911 entity which this DML construct is operating against. 

912 

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

914 extended structure which includes information about mapped 

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

916 contains more background. 

917 

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

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

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

921 

922 >>> stmt = insert(user_table) 

923 >>> stmt.entity_description 

924 { 

925 "name": "user_table", 

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

927 } 

928 

929 .. versionadded:: 1.4.33 

930 

931 .. seealso:: 

932 

933 :attr:`.UpdateBase.returning_column_descriptions` 

934 

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

936 a :func:`.select` construct 

937 

938 :ref:`queryguide_inspection` - ORM background 

939 

940 """ 

941 meth = DMLState.get_plugin_class(self).get_entity_description 

942 return meth(self) 

943 

944 @property 

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

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

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

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

949 

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

951 extended structure which includes information about mapped 

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

953 contains more background. 

954 

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

956 derived from the same objects that are returned by the 

957 :attr:`.UpdateBase.exported_columns` accessor:: 

958 

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

960 >>> stmt.entity_description 

961 [ 

962 { 

963 "name": "id", 

964 "type": Integer, 

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

966 }, 

967 { 

968 "name": "name", 

969 "type": String(), 

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

971 }, 

972 ] 

973 

974 .. versionadded:: 1.4.33 

975 

976 .. seealso:: 

977 

978 :attr:`.UpdateBase.entity_description` 

979 

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

981 a :func:`.select` construct 

982 

983 :ref:`queryguide_inspection` - ORM background 

984 

985 """ # noqa: E501 

986 meth = DMLState.get_plugin_class( 

987 self 

988 ).get_returning_column_descriptions 

989 return meth(self) 

990 

991 

992class ValuesBase(UpdateBase): 

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

994 INSERT and UPDATE constructs.""" 

995 

996 __visit_name__ = "values_base" 

997 

998 _supports_multi_parameters = False 

999 

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

1001 """SELECT statement for INSERT .. FROM SELECT""" 

1002 

1003 _post_values_clause: Optional[ClauseElement] = None 

1004 """used by extensions to Insert etc. to add additional syntactical 

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

1006 

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

1008 _multi_values: Tuple[ 

1009 Union[