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 """