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

Shortcuts on this page

r m x   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

492 statements  

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 TypeVar 

32from typing import Union 

33 

34from . import coercions 

35from . import roles 

36from . import util as sql_util 

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 .base import HasSyntaxExtensions 

53from .base import SyntaxExtension 

54from .elements import BooleanClauseList 

55from .elements import ClauseElement 

56from .elements import ColumnClause 

57from .elements import ColumnElement 

58from .elements import Null 

59from .selectable import Alias 

60from .selectable import ExecutableReturnsRows 

61from .selectable import FromClause 

62from .selectable import HasCTE 

63from .selectable import HasPrefixes 

64from .selectable import Join 

65from .selectable import SelectLabelStyle 

66from .selectable import TableClause 

67from .selectable import TypedReturnsRows 

68from .sqltypes import NullType 

69from .visitors import InternalTraversal 

70from .. import exc 

71from .. import util 

72from ..util.typing import Self 

73from ..util.typing import TupleAny 

74from ..util.typing import TypeGuard 

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 columns._populate_separate_keys( 

434 col._make_proxy( 

435 fromclause, primary_key=primary_key, foreign_keys=foreign_keys 

436 ) 

437 for col in self._all_selected_columns 

438 if is_column_element(col) 

439 ) 

440 

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

442 """Set the parameters for the statement. 

443 

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

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

446 SET/VALUES clause of UPDATE and INSERT. 

447 

448 """ 

449 raise NotImplementedError( 

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

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

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

453 ) 

454 

455 @_generative 

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

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

458 

459 e.g.:: 

460 

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

462 

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

464 associated with the constructor. 

465 

466 

467 """ 

468 self._validate_dialect_kwargs(opt) 

469 return self 

470 

471 @_generative 

472 def return_defaults( 

473 self, 

474 *cols: _DMLColumnArgument, 

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

476 sort_by_parameter_order: bool = False, 

477 ) -> Self: 

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

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

480 backends only. 

481 

482 .. deepalchemy:: 

483 

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

485 for its internal work in fetching newly generated primary key 

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

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

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

489 ORM inserts. Its behavior is fairly idiosyncratic 

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

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

492 add RETURNING clauses to their INSERT, UPDATE and DELETE 

493 statements. 

494 

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

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

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

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

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

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

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

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

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

504 value. 

505 

506 However, when the statement is modified by calling 

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

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

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

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

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

512 from the statement's execution, not only will 

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

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

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

516 of server generated 

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

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

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

520 

521 When invoking INSERT statements with multiple rows using 

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

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

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

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

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

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

528 values for each row inserted. The 

529 :attr:`.CursorResult.inserted_primary_key` and 

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

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

532 

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

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

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

536 INSERT, UPDATE or DELETE statement proceeds normally. 

537 

538 E.g.:: 

539 

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

541 

542 result = connection.execute(stmt) 

543 

544 server_created_at = result.returned_defaults["created_at"] 

545 

546 When used against an UPDATE statement 

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

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

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

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

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

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

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

554 normally change values when a DELETE statement proceeds. 

555 

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

557 for DELETE statements also and has been moved from 

558 :class:`.ValuesBase` to :class:`.UpdateBase`. 

559 

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

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

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

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

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

565 at a time. 

566 

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

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

569 

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

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

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

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

574 

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

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

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

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

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

580 attribute being left unpopulated. 

581 

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

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

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

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

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

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

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

589 ``False``. 

590 

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

592 backend database driver supports the 

593 :ref:`insertmanyvalues <engine_insertmanyvalues>` 

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

595 When executemany is used, the 

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

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

598 will return the inserted defaults and primary keys. 

599 

600 .. versionadded:: 1.4 Added 

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

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

603 In version 2.0, the underlying implementation which fetches and 

604 populates the data for these attributes was generalized to be 

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

606 supported by the ``psycopg2`` driver. 

607 

608 

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

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

611 will be fetched. 

612 :param supplemental_cols: optional list of RETURNING expressions, 

613 in the same form as one would pass to the 

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

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

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

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

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

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

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

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

622 reliably be targeted positionally. 

623 

624 .. versionadded:: 2.0 

625 

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

627 executed against multiple parameter sets, organize the results of 

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

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

630 execution for supporting dialects and typically makes use of the 

631 :term:`insertmanyvalues` feature. 

632 

633 .. versionadded:: 2.0.10 

634 

635 .. seealso:: 

636 

637 :ref:`engine_insertmanyvalues_returning_order` - background on 

638 sorting of RETURNING rows for bulk INSERT 

639 

640 .. seealso:: 

641 

642 :meth:`.UpdateBase.returning` 

643 

644 :attr:`_engine.CursorResult.returned_defaults` 

645 

646 :attr:`_engine.CursorResult.returned_defaults_rows` 

647 

648 :attr:`_engine.CursorResult.inserted_primary_key` 

649 

650 :attr:`_engine.CursorResult.inserted_primary_key_rows` 

651 

652 """ 

653 

654 if self._return_defaults: 

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

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

657 # are columns in the collection 

658 if self._return_defaults_columns and cols: 

659 self._return_defaults_columns = tuple( 

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

661 coercions.expect(roles.ColumnsClauseRole, c) 

662 for c in cols 

663 ) 

664 ) 

665 else: 

666 # set for all columns 

667 self._return_defaults_columns = () 

668 else: 

669 self._return_defaults_columns = tuple( 

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

671 ) 

672 self._return_defaults = True 

673 if sort_by_parameter_order: 

674 if not self.is_insert: 

675 raise exc.ArgumentError( 

676 "The 'sort_by_parameter_order' argument to " 

677 "return_defaults() only applies to INSERT statements" 

678 ) 

679 self._sort_by_parameter_order = True 

680 if supplemental_cols: 

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

682 # is for test suites but also for vertical splicing 

683 supplemental_col_tup = ( 

684 coercions.expect(roles.ColumnsClauseRole, c) 

685 for c in supplemental_cols 

686 ) 

687 

688 if self._supplemental_returning is None: 

689 self._supplemental_returning = tuple( 

690 util.unique_list(supplemental_col_tup) 

691 ) 

692 else: 

693 self._supplemental_returning = tuple( 

694 util.unique_list( 

695 self._supplemental_returning 

696 + tuple(supplemental_col_tup) 

697 ) 

698 ) 

699 

700 return self 

701 

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

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

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

705 

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

707 so we return False for derives. 

708 

709 """ 

710 return False 

711 

712 @_generative 

713 def returning( 

714 self, 

715 *cols: _ColumnsClauseArgument[Any], 

716 sort_by_parameter_order: bool = False, 

717 **__kw: Any, 

718 ) -> UpdateBase: 

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

720 

721 e.g.: 

722 

723 .. sourcecode:: pycon+sql 

724 

725 >>> stmt = ( 

726 ... table.update() 

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

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

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

730 ... ) 

731 >>> print(stmt) 

732 {printsql}UPDATE some_table SET status=:status 

733 WHERE some_table.data = :data_1 

734 RETURNING some_table.server_flag, some_table.updated_timestamp 

735 

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

737 list of expressions to be returned. 

738 

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

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

741 

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

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

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

745 expressions: 

746 

747 .. sourcecode:: pycon+sql 

748 

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

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

751 ... ) 

752 >>> print(stmt) 

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

754 VALUES (:first_name, :last_name) 

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

756 

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

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

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

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

761 

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

763 available via the result set and can be iterated using 

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

765 For DBAPIs which do not 

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

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

768 amount of behavioral neutrality is provided. 

769 

770 Note that not all databases/DBAPIs 

771 support RETURNING. For those backends with no support, 

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

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

774 varies greatly, including restrictions on executemany() 

775 and other statements which return multiple rows. Please 

776 read the documentation notes for the database in use in 

777 order to determine the availability of RETURNING. 

778 

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

780 entities to be returned. 

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

782 executed against multiple parameter sets, organize the results of 

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

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

785 execution for supporting dialects and typically makes use of the 

786 :term:`insertmanyvalues` feature. 

787 

788 .. versionadded:: 2.0.10 

789 

790 .. seealso:: 

791 

792 :ref:`engine_insertmanyvalues_returning_order` - background on 

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

794 

795 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of 

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

797 

798 .. seealso:: 

799 

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

801 towards efficient fetching of server-side defaults and triggers 

802 for single-row INSERTs or UPDATEs. 

803 

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

805 

806 """ # noqa: E501 

807 if __kw: 

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

809 if self._return_defaults: 

810 raise exc.InvalidRequestError( 

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

812 ) 

813 self._returning += tuple( 

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

815 ) 

816 if sort_by_parameter_order: 

817 if not self.is_insert: 

818 raise exc.ArgumentError( 

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

820 "only applies to INSERT statements" 

821 ) 

822 self._sort_by_parameter_order = True 

823 return self 

824 

825 def corresponding_column( 

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

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

828 return self.exported_columns.corresponding_column( 

829 column, require_embedded=require_embedded 

830 ) 

831 

832 @util.ro_memoized_property 

833 def _all_selected_columns(self) -> _SelectIterable: 

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

835 

836 @util.ro_memoized_property 

837 def exported_columns( 

838 self, 

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

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

841 statement. 

842 

843 .. versionadded:: 1.4 

844 

845 """ 

846 return ColumnCollection( 

847 (c.key, c) 

848 for c in self._all_selected_columns 

849 if is_column_element(c) 

850 ).as_readonly() 

851 

852 @_generative 

853 def with_hint( 

854 self, 

855 text: str, 

856 selectable: Optional[_DMLTableArgument] = None, 

857 dialect_name: str = "*", 

858 ) -> Self: 

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

860 INSERT/UPDATE/DELETE statement. 

861 

862 .. note:: 

863 

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

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

866 :meth:`.UpdateBase.prefix_with`. 

867 

868 The text of the hint is rendered in the appropriate 

869 location for the database backend in use, relative 

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

871 statement, or optionally to that of the given 

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

873 

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

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

876 that only takes effect for SQL Server:: 

877 

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

879 

880 :param text: Text of the hint. 

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

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

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

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

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

886 that dialect is in use. 

887 """ 

888 if selectable is None: 

889 selectable = self.table 

890 else: 

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

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

893 return self 

894 

895 @property 

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

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

898 entity which this DML construct is operating against. 

899 

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

901 extended structure which includes information about mapped 

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

903 contains more background. 

904 

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

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

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

908 

909 >>> stmt = insert(user_table) 

910 >>> stmt.entity_description 

911 { 

912 "name": "user_table", 

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

914 } 

915 

916 .. versionadded:: 1.4.33 

917 

918 .. seealso:: 

919 

920 :attr:`.UpdateBase.returning_column_descriptions` 

921 

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

923 a :func:`.select` construct 

924 

925 :ref:`queryguide_inspection` - ORM background 

926 

927 """ 

928 meth = DMLState.get_plugin_class(self).get_entity_description 

929 return meth(self) 

930 

931 @property 

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

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

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

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

936 

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

938 extended structure which includes information about mapped 

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

940 contains more background. 

941 

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

943 derived from the same objects that are returned by the 

944 :attr:`.UpdateBase.exported_columns` accessor:: 

945 

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

947 >>> stmt.entity_description 

948 [ 

949 { 

950 "name": "id", 

951 "type": Integer, 

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

953 }, 

954 { 

955 "name": "name", 

956 "type": String(), 

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

958 }, 

959 ] 

960 

961 .. versionadded:: 1.4.33 

962 

963 .. seealso:: 

964 

965 :attr:`.UpdateBase.entity_description` 

966 

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

968 a :func:`.select` construct 

969 

970 :ref:`queryguide_inspection` - ORM background 

971 

972 """ # noqa: E501 

973 meth = DMLState.get_plugin_class( 

974 self 

975 ).get_returning_column_descriptions 

976 return meth(self) 

977 

978 

979class ValuesBase(UpdateBase): 

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

981 INSERT and UPDATE constructs.""" 

982 

983 __visit_name__ = "values_base" 

984 

985 _supports_multi_parameters = False 

986 

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

988 """SELECT statement for INSERT .. FROM SELECT""" 

989 

990 _post_values_clause: Optional[ClauseElement] = None 

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

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

993 

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

995 _multi_values: Tuple[ 

996 Union[ 

997 Sequence[Dict[_DMLColumnElement, Any]], 

998 Sequence[Sequence[Any]], 

999 ], 

1000 ..., 

1001 ] = () 

1002 

1003 _maintain_values_ordering: bool = False 

1004 

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

1006 _inline: bool = False 

1007 

1008 def __init__(self, table: _DMLTableArgument): 

1009 self.table = coercions.expect( 

1010 roles.DMLTableRole, table, apply_propagate_attrs=self 

1011 ) 

1012 

1013 @_generative 

1014 @_exclusive_against( 

1015 "_select_names", 

1016 "_maintain_values_ordering", 

1017 msgs={ 

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

1019 "_maintain_values_ordering": "This statement already has ordered " 

1020 "values present", 

1021 }, 

1022 defaults={"_maintain_values_ordering": False}, 

1023 ) 

1024 def values( 

1025 self, 

1026 *args: Union[ 

1027 _DMLColumnKeyMapping[Any], 

1028 Sequence[Any], 

1029 ], 

1030 **kwargs: Any, 

1031 ) -> Self: 

1032 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET 

1033 clause for an UPDATE. 

1034 

1035 Note that the :class:`_expression.Insert` and 

1036 :class:`_expression.Update` 

1037 constructs support 

1038 per-execution time formatting of the VALUES and/or SET clauses, 

1039 based on the arguments passed to :meth:`_engine.Connection.execute`. 

1040 However, the :meth:`.ValuesBase.values` method can be used to "fix" a 

1041 particular set of parameters into the statement. 

1042 

1043 Multiple calls to :meth:`.ValuesBase.values` will produce a new 

1044 construct, each one with the parameter list modified to include 

1045 the new parameters sent. In the typical case of a single 

1046 dictionary of parameters, the newly passed keys will replace 

1047 the same keys in the previous construct. In the case of a list-based 

1048 "multiple values" construct, each new list of values is extended 

1049 onto the existing list of values. 

1050 

1051 :param \**kwargs: key value pairs representing the string key 

1052 of a :class:`_schema.Column` 

1053 mapped to the value to be rendered into the 

1054 VALUES or SET clause:: 

1055 

1056 users.insert().values(name="some name") 

1057 

1058 users.update().where(users.c.id == 5).values(name="some name") 

1059 

1060 :param \*args: As an alternative to passing key/value parameters, 

1061 a dictionary, tuple, or list of dictionaries or tuples can be passed 

1062 as a single positional argument in order to form the VALUES or 

1063 SET clause of the statement. The forms that are accepted vary 

1064 based on whether this is an :class:`_expression.Insert` or an 

1065 :class:`_expression.Update` construct. 

1066 

1067 For either an :class:`_expression.Insert` or 

1068 :class:`_expression.Update` 

1069 construct, a single dictionary can be passed, which works the same as 

1070 that of the kwargs form:: 

1071 

1072 users.insert().values({"name": "some name"}) 

1073 

1074 users.update().values({"name": "some new name"}) 

1075 

1076 Also for either form but more typically for the 

1077 :class:`_expression.Insert` construct, a tuple that contains an 

1078 entry for every column in the table is also accepted:: 

1079 

1080 users.insert().values((5, "some name")) 

1081 

1082 The :class:`_expression.Insert` construct also supports being 

1083 passed a list of dictionaries or full-table-tuples, which on the 

1084 server will render the less common SQL syntax of "multiple values" - 

1085 this syntax is supported on backends such as SQLite, PostgreSQL, 

1086 MySQL, but not necessarily others:: 

1087 

1088 users.insert().values( 

1089 [ 

1090 {"name": "some name"}, 

1091 {"name": "some other name"}, 

1092 {"name": "yet another name"}, 

1093 ] 

1094 ) 

1095 

1096 The above form would render a multiple VALUES statement similar to: 

1097 

1098 .. sourcecode:: sql 

1099 

1100 INSERT INTO users (name) VALUES 

1101 (:name_1), 

1102 (:name_2), 

1103 (:name_3) 

1104 

1105 It is essential to note that **passing multiple values is 

1106 NOT the same as using traditional executemany() form**. The above 

1107 syntax is a **special** syntax not typically used. To emit an 

1108 INSERT statement against multiple rows, the normal method is 

1109 to pass a multiple values list to the 

1110 :meth:`_engine.Connection.execute` 

1111 method, which is supported by all database backends and is generally 

1112 more efficient for a very large number of parameters. 

1113 

1114 .. seealso:: 

1115 

1116 :ref:`tutorial_multiple_parameters` - an introduction to 

1117 the traditional Core method of multiple parameter set 

1118 invocation for INSERTs and other statements. 

1119 

1120 The UPDATE construct also supports rendering the SET parameters 

1121 in a specific order. For this feature refer to the 

1122 :meth:`_expression.Update.ordered_values` method. 

1123 

1124 .. seealso:: 

1125 

1126 :meth:`_expression.Update.ordered_values` 

1127 

1128 

1129 """ 

1130 if args: 

1131 # positional case. this is currently expensive. we don't 

1132 # yet have positional-only args so we have to check the length. 

1133 # then we need to check multiparams vs. single dictionary. 

1134 # since the parameter format is needed in order to determine 

1135 # a cache key, we need to determine this up front. 

1136 arg = args[0] 

1137 

1138 if kwargs: 

1139 raise exc.ArgumentError( 

1140 "Can't pass positional and kwargs to values() " 

1141 "simultaneously" 

1142 ) 

1143 elif len(args) > 1: 

1144 raise exc.ArgumentError( 

1145 "Only a single dictionary/tuple or list of " 

1146 "dictionaries/tuples is accepted positionally." 

1147 ) 

1148 

1149 elif isinstance(arg, collections_abc.Sequence): 

1150 if arg and isinstance(arg[0], dict): 

1151 multi_kv_generator = DMLState.get_plugin_class( 

1152 self 

1153 )._get_multi_crud_kv_pairs 

1154 self._multi_values += (multi_kv_generator(self, arg),) 

1155 return self 

1156 

1157 if arg and isinstance(arg[0], (list, tuple)): 

1158 self._multi_values += (arg,) 

1159 return self 

1160 

1161 if TYPE_CHECKING: 

1162 # crud.py raises during compilation if this is not the 

1163 # case 

1164 assert isinstance(self, Insert) 

1165 

1166 # tuple values 

1167 arg = {c.key: value for c, value in zip(self.table.c, arg)} 

1168 

1169 else: 

1170 # kwarg path. this is the most common path for non-multi-params 

1171 # so this is fairly quick. 

1172 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs) 

1173 if args: 

1174 raise exc.ArgumentError( 

1175 "Only a single dictionary/tuple or list of " 

1176 "dictionaries/tuples is accepted positionally." 

1177 ) 

1178 

1179 # for top level values(), convert literals to anonymous bound 

1180 # parameters at statement construction time, so that these values can 

1181 # participate in the cache key process like any other ClauseElement. 

1182 # crud.py now intercepts bound parameters with unique=True from here 

1183 # and ensures they get the "crud"-style name when rendered. 

1184 

1185 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs 

1186 coerced_arg = dict(kv_generator(self, arg.items(), True)) 

1187 if self._values: 

1188 self._values = self._values.union(coerced_arg) 

1189 else: 

1190 self._values = util.immutabledict(coerced_arg) 

1191 return self 

1192 

1193 

1194class Insert(ValuesBase, HasSyntaxExtensions[Literal["post_values"]]): 

1195 """Represent an INSERT construct. 

1196 

1197 The :class:`_expression.Insert` object is created using the 

1198 :func:`_expression.insert()` function. 

1199 

1200 Available extension points: 

1201 

1202 * ``post_values``: applies additional logic after the ``VALUES`` clause. 

1203 

1204 """ 

1205 

1206 __visit_name__ = "insert" 

1207 

1208 _supports_multi_parameters = True 

1209 

1210 select = None 

1211 include_insert_from_select_defaults = False 

1212 

1213 _sort_by_parameter_order: bool = False 

1214 

1215 is_insert = True 

1216 

1217 table: TableClause 

1218 

1219 _traverse_internals = ( 

1220 [ 

1221 ("table", InternalTraversal.dp_clauseelement), 

1222 ("_inline", InternalTraversal.dp_boolean), 

1223 ("_select_names", InternalTraversal.dp_string_list), 

1224 ("_values", InternalTraversal.dp_dml_values), 

1225 ("_multi_values", InternalTraversal.dp_dml_multi_values), 

1226 ("select", InternalTraversal.dp_clauseelement), 

1227 ("_post_values_clause", InternalTraversal.dp_clauseelement), 

1228 ("_returning", InternalTraversal.dp_clauseelement_tuple), 

1229 ("_hints", InternalTraversal.dp_table_hint_list), 

1230 ("_return_defaults", InternalTraversal.dp_boolean), 

1231 ( 

1232 "_return_defaults_columns", 

1233 InternalTraversal.dp_clauseelement_tuple, 

1234 ), 

1235 ("_sort_by_parameter_order", InternalTraversal.dp_boolean), 

1236 ] 

1237 + HasPrefixes._has_prefixes_traverse_internals 

1238 + DialectKWArgs._dialect_kwargs_traverse_internals 

1239 + Executable._executable_traverse_internals 

1240 + HasCTE._has_ctes_traverse_internals 

1241 ) 

1242 

1243 _position_map = util.immutabledict( 

1244 { 

1245 "post_values": "_post_values_clause", 

1246 } 

1247 ) 

1248 

1249 _post_values_clause: Optional[ClauseElement] = None 

1250 """extension point for a ClauseElement that will be compiled directly 

1251 after the VALUES portion of the :class:`.Insert` statement 

1252 

1253 """ 

1254 

1255 def __init__(self, table: _DMLTableArgument): 

1256 super().__init__(table) 

1257 

1258 def _apply_syntax_extension_to_self( 

1259 self, extension: SyntaxExtension 

1260 ) -> None: 

1261 extension.apply_to_insert(self) 

1262 

1263 @_generative 

1264 def inline(self) -> Self: 

1265 """Make this :class:`_expression.Insert` construct "inline" . 

1266 

1267 When set, no attempt will be made to retrieve the 

1268 SQL-generated default values to be provided within the statement; 

1269 in particular, 

1270 this allows SQL expressions to be rendered 'inline' within the 

1271 statement without the need to pre-execute them beforehand; for 

1272 backends that support "returning", this turns off the "implicit 

1273 returning" feature for the statement. 

1274 

1275 

1276 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline` 

1277 parameter 

1278 is now superseded by the :meth:`_expression.Insert.inline` method. 

1279 

1280 """ 

1281 self._inline = True 

1282 return self 

1283 

1284 @_generative 

1285 def from_select( 

1286 self, 

1287 names: Sequence[_DMLColumnArgument], 

1288 select: Selectable, 

1289 include_defaults: bool = True, 

1290 ) -> Self: 

1291 """Return a new :class:`_expression.Insert` construct which represents 

1292 an ``INSERT...FROM SELECT`` statement. 

1293 

1294 e.g.:: 

1295 

1296 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5) 

1297 ins = table2.insert().from_select(["a", "b"], sel) 

1298 

1299 :param names: a sequence of string column names or 

1300 :class:`_schema.Column` 

1301 objects representing the target columns. 

1302 :param select: a :func:`_expression.select` construct, 

1303 :class:`_expression.FromClause` 

1304 or other construct which resolves into a 

1305 :class:`_expression.FromClause`, 

1306 such as an ORM :class:`_query.Query` object, etc. The order of 

1307 columns returned from this FROM clause should correspond to the 

1308 order of columns sent as the ``names`` parameter; while this 

1309 is not checked before passing along to the database, the database 

1310 would normally raise an exception if these column lists don't 

1311 correspond. 

1312 :param include_defaults: if True, non-server default values and 

1313 SQL expressions as specified on :class:`_schema.Column` objects 

1314 (as documented in :ref:`metadata_defaults_toplevel`) not 

1315 otherwise specified in the list of names will be rendered 

1316 into the INSERT and SELECT statements, so that these values are also 

1317 included in the data to be inserted. 

1318 

1319 .. note:: A Python-side default that uses a Python callable function 

1320 will only be invoked **once** for the whole statement, and **not 

1321 per row**. 

1322 

1323 """ 

1324 

1325 if self._values: 

1326 raise exc.InvalidRequestError( 

1327 "This construct already inserts value expressions" 

1328 ) 

1329 

1330 self._select_names = [ 

1331 coercions.expect(roles.DMLColumnRole, name, as_key=True) 

1332 for name in names 

1333 ] 

1334 self._inline = True 

1335 self.include_insert_from_select_defaults = include_defaults 

1336 self.select = coercions.expect(roles.DMLSelectRole, select) 

1337 return self 

1338 

1339 if TYPE_CHECKING: 

1340 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501 

1341 

1342 # code within this block is **programmatically, 

1343 # statically generated** by tools/generate_tuple_map_overloads.py 

1344 

1345 @overload 

1346 def returning( 

1347 self, 

1348 __ent0: _TCCA[_T0], 

1349 /, 

1350 *, 

1351 sort_by_parameter_order: bool = False, 

1352 ) -> ReturningInsert[_T0]: ... 

1353 

1354 @overload 

1355 def returning( 

1356 self, 

1357 __ent0: _TCCA[_T0], 

1358 __ent1: _TCCA[_T1], 

1359 /, 

1360 *, 

1361 sort_by_parameter_order: bool = False, 

1362 ) -> ReturningInsert[_T0, _T1]: ... 

1363 

1364 @overload 

1365 def returning( 

1366 self, 

1367 __ent0: _TCCA[_T0], 

1368 __ent1: _TCCA[_T1], 

1369 __ent2: _TCCA[_T2], 

1370 /, 

1371 *, 

1372 sort_by_parameter_order: bool = False, 

1373 ) -> ReturningInsert[_T0, _T1, _T2]: ... 

1374 

1375 @overload 

1376 def returning( 

1377 self, 

1378 __ent0: _TCCA[_T0], 

1379 __ent1: _TCCA[_T1], 

1380 __ent2: _TCCA[_T2], 

1381 __ent3: _TCCA[_T3], 

1382 /, 

1383 *, 

1384 sort_by_parameter_order: bool = False, 

1385 ) -> ReturningInsert[_T0, _T1, _T2, _T3]: ... 

1386 

1387 @overload 

1388 def returning( 

1389 self, 

1390 __ent0: _TCCA[_T0], 

1391 __ent1: _TCCA[_T1], 

1392 __ent2: _TCCA[_T2], 

1393 __ent3: _TCCA[_T3], 

1394 __ent4: _TCCA[_T4], 

1395 /, 

1396 *, 

1397 sort_by_parameter_order: bool = False, 

1398 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4]: ... 

1399 

1400 @overload 

1401 def returning( 

1402 self, 

1403 __ent0: _TCCA[_T0], 

1404 __ent1: _TCCA[_T1], 

1405 __ent2: _TCCA[_T2], 

1406 __ent3: _TCCA[_T3], 

1407 __ent4: _TCCA[_T4], 

1408 __ent5: _TCCA[_T5], 

1409 /, 

1410 *, 

1411 sort_by_parameter_order: bool = False, 

1412 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5]: ... 

1413 

1414 @overload 

1415 def returning( 

1416 self, 

1417 __ent0: _TCCA[_T0], 

1418 __ent1: _TCCA[_T1], 

1419 __ent2: _TCCA[_T2], 

1420 __ent3: _TCCA[_T3], 

1421 __ent4: _TCCA[_T4], 

1422 __ent5: _TCCA[_T5], 

1423 __ent6: _TCCA[_T6], 

1424 /, 

1425 *, 

1426 sort_by_parameter_order: bool = False, 

1427 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ... 

1428 

1429 @overload 

1430 def returning( 

1431 self, 

1432 __ent0: _TCCA[_T0], 

1433 __ent1: _TCCA[_T1], 

1434 __ent2: _TCCA[_T2], 

1435 __ent3: _TCCA[_T3], 

1436 __ent4: _TCCA[_T4], 

1437 __ent5: _TCCA[_T5], 

1438 __ent6: _TCCA[_T6], 

1439 __ent7: _TCCA[_T7], 

1440 /, 

1441 *entities: _ColumnsClauseArgument[Any], 

1442 sort_by_parameter_order: bool = False, 

1443 ) -> ReturningInsert[ 

1444 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny] 

1445 ]: ... 

1446 

1447 # END OVERLOADED FUNCTIONS self.returning 

1448 

1449 @overload 

1450 def returning( 

1451 self, 

1452 *cols: _ColumnsClauseArgument[Any], 

1453 sort_by_parameter_order: bool = False, 

1454 **__kw: Any, 

1455 ) -> ReturningInsert[Any]: ... 

1456 

1457 def returning( 

1458 self, 

1459 *cols: _ColumnsClauseArgument[Any], 

1460 sort_by_parameter_order: bool = False, 

1461 **__kw: Any, 

1462 ) -> ReturningInsert[Any]: ... 

1463 

1464 

1465class ReturningInsert(Insert, TypedReturnsRows[Unpack[_Ts]]): 

1466 """Typing-only class that establishes a generic type form of 

1467 :class:`.Insert` which tracks returned column types. 

1468 

1469 This datatype is delivered when calling the 

1470 :meth:`.Insert.returning` method. 

1471 

1472 .. versionadded:: 2.0 

1473 

1474 """ 

1475 

1476 

1477# note: if not for MRO issues, this class should extend 

1478# from HasSyntaxExtensions[Literal["post_criteria"]] 

1479class DMLWhereBase: 

1480 table: _DMLTableElement 

1481 _where_criteria: Tuple[ColumnElement[Any], ...] = () 

1482 

1483 _post_criteria_clause: Optional[ClauseElement] = None 

1484 """used by extensions to Update/Delete etc. to add additional syntacitcal 

1485 constructs, e.g. LIMIT etc. 

1486 

1487 .. versionadded:: 2.1 

1488 

1489 """ 

1490 

1491 # can't put position_map here either without HasSyntaxExtensions 

1492 # _position_map = util.immutabledict( 

1493 # {"post_criteria": "_post_criteria_clause"} 

1494 # ) 

1495 

1496 @_generative 

1497 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self: 

1498 """Return a new construct with the given expression(s) added to 

1499 its WHERE clause, joined to the existing clause via AND, if any. 

1500 

1501 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where` 

1502 support multiple-table forms, including database-specific 

1503 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that 

1504 don't have multiple-table support, a backend agnostic approach 

1505 to using multiple tables is to make use of correlated subqueries. 

1506 See the linked tutorial sections below for examples. 

1507 

1508 .. seealso:: 

1509 

1510 :ref:`tutorial_correlated_updates` 

1511 

1512 :ref:`tutorial_update_from` 

1513 

1514 :ref:`tutorial_multi_table_deletes` 

1515 

1516 """ 

1517 

1518 for criterion in whereclause: 

1519 where_criteria: ColumnElement[Any] = coercions.expect( 

1520 roles.WhereHavingRole, criterion, apply_propagate_attrs=self 

1521 ) 

1522 self._where_criteria += (where_criteria,) 

1523 return self 

1524 

1525 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self: 

1526 """A synonym for the :meth:`_dml.DMLWhereBase.where` method. 

1527 

1528 .. versionadded:: 1.4 

1529 

1530 """ 

1531 

1532 return self.where(*criteria) 

1533 

1534 def _filter_by_zero(self) -> _DMLTableElement: 

1535 return self.table 

1536 

1537 def filter_by(self, **kwargs: Any) -> Self: 

1538 r"""apply the given filtering criterion as a WHERE clause 

1539 to this select. 

1540 

1541 """ 

1542 from_entity = self._filter_by_zero() 

1543 

1544 clauses = [ 

1545 _entity_namespace_key(from_entity, key) == value 

1546 for key, value in kwargs.items() 

1547 ] 

1548 return self.filter(*clauses) 

1549 

1550 @property 

1551 def whereclause(self) -> Optional[ColumnElement[Any]]: 

1552 """Return the completed WHERE clause for this :class:`.DMLWhereBase` 

1553 statement. 

1554 

1555 This assembles the current collection of WHERE criteria 

1556 into a single :class:`_expression.BooleanClauseList` construct. 

1557 

1558 

1559 .. versionadded:: 1.4 

1560 

1561 """ 

1562 

1563 return BooleanClauseList._construct_for_whereclause( 

1564 self._where_criteria 

1565 ) 

1566 

1567 

1568class Update( 

1569 DMLWhereBase, ValuesBase, HasSyntaxExtensions[Literal["post_criteria"]] 

1570): 

1571 """Represent an Update construct. 

1572 

1573 The :class:`_expression.Update` object is created using the 

1574 :func:`_expression.update()` function. 

1575 

1576 Available extension points: 

1577 

1578 * ``post_criteria``: applies additional logic after the ``WHERE`` clause. 

1579 

1580 """ 

1581 

1582 __visit_name__ = "update" 

1583 

1584 is_update = True 

1585 

1586 _traverse_internals = ( 

1587 [ 

1588 ("table", InternalTraversal.dp_clauseelement), 

1589 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple), 

1590 ("_inline", InternalTraversal.dp_boolean), 

1591 ("_maintain_values_ordering", InternalTraversal.dp_boolean), 

1592 ("_values", InternalTraversal.dp_dml_values), 

1593 ("_returning", InternalTraversal.dp_clauseelement_tuple), 

1594 ("_hints", InternalTraversal.dp_table_hint_list), 

1595 ("_return_defaults", InternalTraversal.dp_boolean), 

1596 ("_post_criteria_clause", InternalTraversal.dp_clauseelement), 

1597 ( 

1598 "_return_defaults_columns", 

1599 InternalTraversal.dp_clauseelement_tuple, 

1600 ), 

1601 ] 

1602 + HasPrefixes._has_prefixes_traverse_internals 

1603 + DialectKWArgs._dialect_kwargs_traverse_internals 

1604 + Executable._executable_traverse_internals 

1605 + HasCTE._has_ctes_traverse_internals 

1606 ) 

1607 

1608 _position_map = util.immutabledict( 

1609 {"post_criteria": "_post_criteria_clause"} 

1610 ) 

1611 

1612 def __init__(self, table: _DMLTableArgument): 

1613 super().__init__(table) 

1614 

1615 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self: 

1616 """Specify the VALUES clause of this UPDATE statement with an explicit 

1617 parameter ordering that will be maintained in the SET clause of the 

1618 resulting UPDATE statement. 

1619 

1620 E.g.:: 

1621 

1622 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo")) 

1623 

1624 .. seealso:: 

1625 

1626 :ref:`tutorial_parameter_ordered_updates` - full example of the 

1627 :meth:`_expression.Update.ordered_values` method. 

1628 

1629 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values` 

1630 method 

1631 supersedes the 

1632 :paramref:`_expression.update.preserve_parameter_order` 

1633 parameter, which will be removed in SQLAlchemy 2.0. 

1634 

1635 """ # noqa: E501 

1636 if self._values: 

1637 raise exc.ArgumentError( 

1638 "This statement already has " 

1639 f"{'ordered ' if self._maintain_values_ordering else ''}" 

1640 "values present" 

1641 ) 

1642 

1643 self = self.values(dict(args)) 

1644 self._maintain_values_ordering = True 

1645 return self 

1646 

1647 @_generative 

1648 def inline(self) -> Self: 

1649 """Make this :class:`_expression.Update` construct "inline" . 

1650 

1651 When set, SQL defaults present on :class:`_schema.Column` 

1652 objects via the 

1653 ``default`` keyword will be compiled 'inline' into the statement and 

1654 not pre-executed. This means that their values will not be available 

1655 in the dictionary returned from 

1656 :meth:`_engine.CursorResult.last_updated_params`. 

1657 

1658 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline` 

1659 parameter 

1660 is now superseded by the :meth:`_expression.Update.inline` method. 

1661 

1662 """ 

1663 self._inline = True 

1664 return self 

1665 

1666 def _apply_syntax_extension_to_self( 

1667 self, extension: SyntaxExtension 

1668 ) -> None: 

1669 extension.apply_to_update(self) 

1670 

1671 if TYPE_CHECKING: 

1672 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8 

1673 

1674 # code within this block is **programmatically, 

1675 # statically generated** by tools/generate_tuple_map_overloads.py 

1676 

1677 @overload 

1678 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningUpdate[_T0]: ... 

1679 

1680 @overload 

1681 def returning( 

1682 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], / 

1683 ) -> ReturningUpdate[_T0, _T1]: ... 

1684 

1685 @overload 

1686 def returning( 

1687 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], / 

1688 ) -> ReturningUpdate[_T0, _T1, _T2]: ... 

1689 

1690 @overload 

1691 def returning( 

1692 self, 

1693 __ent0: _TCCA[_T0], 

1694 __ent1: _TCCA[_T1], 

1695 __ent2: _TCCA[_T2], 

1696 __ent3: _TCCA[_T3], 

1697 /, 

1698 ) -> ReturningUpdate[_T0, _T1, _T2, _T3]: ... 

1699 

1700 @overload 

1701 def returning( 

1702 self, 

1703 __ent0: _TCCA[_T0], 

1704 __ent1: _TCCA[_T1], 

1705 __ent2: _TCCA[_T2], 

1706 __ent3: _TCCA[_T3], 

1707 __ent4: _TCCA[_T4], 

1708 /, 

1709 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4]: ... 

1710 

1711 @overload 

1712 def returning( 

1713 self, 

1714 __ent0: _TCCA[_T0], 

1715 __ent1: _TCCA[_T1], 

1716 __ent2: _TCCA[_T2], 

1717 __ent3: _TCCA[_T3], 

1718 __ent4: _TCCA[_T4], 

1719 __ent5: _TCCA[_T5], 

1720 /, 

1721 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5]: ... 

1722 

1723 @overload 

1724 def returning( 

1725 self, 

1726 __ent0: _TCCA[_T0], 

1727 __ent1: _TCCA[_T1], 

1728 __ent2: _TCCA[_T2], 

1729 __ent3: _TCCA[_T3], 

1730 __ent4: _TCCA[_T4], 

1731 __ent5: _TCCA[_T5], 

1732 __ent6: _TCCA[_T6], 

1733 /, 

1734 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ... 

1735 

1736 @overload 

1737 def returning( 

1738 self, 

1739 __ent0: _TCCA[_T0], 

1740 __ent1: _TCCA[_T1], 

1741 __ent2: _TCCA[_T2], 

1742 __ent3: _TCCA[_T3], 

1743 __ent4: _TCCA[_T4], 

1744 __ent5: _TCCA[_T5], 

1745 __ent6: _TCCA[_T6], 

1746 __ent7: _TCCA[_T7], 

1747 /, 

1748 *entities: _ColumnsClauseArgument[Any], 

1749 ) -> ReturningUpdate[ 

1750 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny] 

1751 ]: ... 

1752 

1753 # END OVERLOADED FUNCTIONS self.returning 

1754 

1755 @overload 

1756 def returning( 

1757 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any 

1758 ) -> ReturningUpdate[Any]: ... 

1759 

1760 def returning( 

1761 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any 

1762 ) -> ReturningUpdate[Any]: ... 

1763 

1764 

1765class ReturningUpdate(Update, TypedReturnsRows[Unpack[_Ts]]): 

1766 """Typing-only class that establishes a generic type form of 

1767 :class:`.Update` which tracks returned column types. 

1768 

1769 This datatype is delivered when calling the 

1770 :meth:`.Update.returning` method. 

1771 

1772 .. versionadded:: 2.0 

1773 

1774 """ 

1775 

1776 

1777class Delete( 

1778 DMLWhereBase, UpdateBase, HasSyntaxExtensions[Literal["post_criteria"]] 

1779): 

1780 """Represent a DELETE construct. 

1781 

1782 The :class:`_expression.Delete` object is created using the 

1783 :func:`_expression.delete()` function. 

1784 

1785 Available extension points: 

1786 

1787 * ``post_criteria``: applies additional logic after the ``WHERE`` clause. 

1788 

1789 """ 

1790 

1791 __visit_name__ = "delete" 

1792 

1793 is_delete = True 

1794 

1795 _traverse_internals = ( 

1796 [ 

1797 ("table", InternalTraversal.dp_clauseelement), 

1798 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple), 

1799 ("_returning", InternalTraversal.dp_clauseelement_tuple), 

1800 ("_hints", InternalTraversal.dp_table_hint_list), 

1801 ("_post_criteria_clause", InternalTraversal.dp_clauseelement), 

1802 ] 

1803 + HasPrefixes._has_prefixes_traverse_internals 

1804 + DialectKWArgs._dialect_kwargs_traverse_internals 

1805 + Executable._executable_traverse_internals 

1806 + HasCTE._has_ctes_traverse_internals 

1807 ) 

1808 

1809 _position_map = util.immutabledict( 

1810 {"post_criteria": "_post_criteria_clause"} 

1811 ) 

1812 

1813 def __init__(self, table: _DMLTableArgument): 

1814 self.table = coercions.expect( 

1815 roles.DMLTableRole, table, apply_propagate_attrs=self 

1816 ) 

1817 

1818 def _apply_syntax_extension_to_self( 

1819 self, extension: SyntaxExtension 

1820 ) -> None: 

1821 extension.apply_to_delete(self) 

1822 

1823 if TYPE_CHECKING: 

1824 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8 

1825 

1826 # code within this block is **programmatically, 

1827 # statically generated** by tools/generate_tuple_map_overloads.py 

1828 

1829 @overload 

1830 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningDelete[_T0]: ... 

1831 

1832 @overload 

1833 def returning( 

1834 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], / 

1835 ) -> ReturningDelete[_T0, _T1]: ... 

1836 

1837 @overload 

1838 def returning( 

1839 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], / 

1840 ) -> ReturningDelete[_T0, _T1, _T2]: ... 

1841 

1842 @overload 

1843 def returning( 

1844 self, 

1845 __ent0: _TCCA[_T0], 

1846 __ent1: _TCCA[_T1], 

1847 __ent2: _TCCA[_T2], 

1848 __ent3: _TCCA[_T3], 

1849 /, 

1850 ) -> ReturningDelete[_T0, _T1, _T2, _T3]: ... 

1851 

1852 @overload 

1853 def returning( 

1854 self, 

1855 __ent0: _TCCA[_T0], 

1856 __ent1: _TCCA[_T1], 

1857 __ent2: _TCCA[_T2], 

1858 __ent3: _TCCA[_T3], 

1859 __ent4: _TCCA[_T4], 

1860 /, 

1861 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4]: ... 

1862 

1863 @overload 

1864 def returning( 

1865 self, 

1866 __ent0: _TCCA[_T0], 

1867 __ent1: _TCCA[_T1], 

1868 __ent2: _TCCA[_T2], 

1869 __ent3: _TCCA[_T3], 

1870 __ent4: _TCCA[_T4], 

1871 __ent5: _TCCA[_T5], 

1872 /, 

1873 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5]: ... 

1874 

1875 @overload 

1876 def returning( 

1877 self, 

1878 __ent0: _TCCA[_T0], 

1879 __ent1: _TCCA[_T1], 

1880 __ent2: _TCCA[_T2], 

1881 __ent3: _TCCA[_T3], 

1882 __ent4: _TCCA[_T4], 

1883 __ent5: _TCCA[_T5], 

1884 __ent6: _TCCA[_T6], 

1885 /, 

1886 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ... 

1887 

1888 @overload 

1889 def returning( 

1890 self, 

1891 __ent0: _TCCA[_T0], 

1892 __ent1: _TCCA[_T1], 

1893 __ent2: _TCCA[_T2], 

1894 __ent3: _TCCA[_T3], 

1895 __ent4: _TCCA[_T4], 

1896 __ent5: _TCCA[_T5], 

1897 __ent6: _TCCA[_T6], 

1898 __ent7: _TCCA[_T7], 

1899 /, 

1900 *entities: _ColumnsClauseArgument[Any], 

1901 ) -> ReturningDelete[ 

1902 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny] 

1903 ]: ... 

1904 

1905 # END OVERLOADED FUNCTIONS self.returning 

1906 

1907 @overload 

1908 def returning( 

1909 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any 

1910 ) -> ReturningDelete[Unpack[TupleAny]]: ... 

1911 

1912 def returning( 

1913 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any 

1914 ) -> ReturningDelete[Unpack[TupleAny]]: ... 

1915 

1916 

1917class ReturningDelete(Update, TypedReturnsRows[Unpack[_Ts]]): 

1918 """Typing-only class that establishes a generic type form of 

1919 :class:`.Delete` which tracks returned column types. 

1920 

1921 This datatype is delivered when calling the 

1922 :meth:`.Delete.returning` method. 

1923 

1924 .. versionadded:: 2.0 

1925 

1926 """