Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/dml.py: 44%
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
1# sql/dml.py
2# Copyright (C) 2009-2026 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`.
11"""
13from __future__ import annotations
15import collections.abc as collections_abc
16import operator
17from typing import Any
18from typing import cast
19from typing import Dict
20from typing import Iterable
21from typing import List
22from typing import Literal
23from typing import MutableMapping
24from typing import NoReturn
25from typing import Optional
26from typing import overload
27from typing import Sequence
28from typing import Set
29from typing import Tuple
30from typing import Type
31from typing import TYPE_CHECKING
32from typing import TypeGuard
33from typing import TypeVar
34from typing import Union
36from . import coercions
37from . import roles
38from . import util as sql_util
39from ._typing import _unexpected_kw
40from ._typing import is_column_element
41from ._typing import is_named_from_clause
42from .base import _entity_namespace_key_search_all
43from .base import _exclusive_against
44from .base import _from_objects
45from .base import _generative
46from .base import _select_iterables
47from .base import ColumnSet
48from .base import CompileState
49from .base import DialectKWArgs
50from .base import Executable
51from .base import ExecutableStatement
52from .base import Generative
53from .base import HasCompileState
54from .base import HasSyntaxExtensions
55from .base import SyntaxExtension
56from .base import WriteableColumnCollection
57from .elements import BooleanClauseList
58from .elements import ClauseElement
59from .elements import ColumnClause
60from .elements import ColumnElement
61from .elements import Null
62from .selectable import Alias
63from .selectable import ExecutableReturnsRows
64from .selectable import FromClause
65from .selectable import HasCTE
66from .selectable import HasPrefixes
67from .selectable import Join
68from .selectable import SelectLabelStyle
69from .selectable import TableClause
70from .selectable import TypedReturnsRows
71from .sqltypes import NullType
72from .visitors import _TraverseInternalsType
73from .visitors import InternalTraversal
74from .. import exc
75from .. import util
76from ..util.typing import Self
77from ..util.typing import TupleAny
78from ..util.typing import TypeVarTuple
79from ..util.typing import Unpack
81if TYPE_CHECKING:
82 from ._typing import _ColumnExpressionArgument
83 from ._typing import _ColumnsClauseArgument
84 from ._typing import _DMLColumnArgument
85 from ._typing import _DMLColumnKeyMapping
86 from ._typing import _DMLTableArgument
87 from ._typing import _FromClauseArgument
88 from ._typing import _T0 # noqa
89 from ._typing import _T1 # noqa
90 from ._typing import _T2 # noqa
91 from ._typing import _T3 # noqa
92 from ._typing import _T4 # noqa
93 from ._typing import _T5 # noqa
94 from ._typing import _T6 # noqa
95 from ._typing import _T7 # noqa
96 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa
97 from .base import ReadOnlyColumnCollection
98 from .compiler import SQLCompiler
99 from .elements import KeyedColumnElement
100 from .selectable import _ColumnsClauseElement
101 from .selectable import _SelectIterable
102 from .selectable import Select
103 from .selectable import Selectable
105 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ...
107 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ...
109 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ...
111else:
112 isupdate = operator.attrgetter("isupdate")
113 isdelete = operator.attrgetter("isdelete")
114 isinsert = operator.attrgetter("isinsert")
117_T = TypeVar("_T", bound=Any)
118_Ts = TypeVarTuple("_Ts")
120_DMLColumnElement = Union[str, ColumnClause[Any]]
121_DMLTableElement = Union[TableClause, Alias, Join]
124class DMLState(CompileState):
125 _no_parameters = True
126 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None
127 _multi_parameters: Optional[
128 List[MutableMapping[_DMLColumnElement, Any]]
129 ] = None
130 _maintain_values_ordering: bool = False
131 _primary_table: FromClause
132 _supports_implicit_returning = True
134 isupdate = False
135 isdelete = False
136 isinsert = False
138 statement: UpdateBase
140 def __init__(
141 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any
142 ):
143 raise NotImplementedError()
145 @classmethod
146 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]:
147 return {
148 "name": (
149 statement.table.name
150 if is_named_from_clause(statement.table)
151 else None
152 ),
153 "table": statement.table,
154 }
156 @classmethod
157 def get_returning_column_descriptions(
158 cls, statement: UpdateBase
159 ) -> List[Dict[str, Any]]:
160 return [
161 {
162 "name": c.key,
163 "type": c.type,
164 "expr": c,
165 }
166 for c in statement._all_selected_columns
167 ]
169 @property
170 def dml_table(self) -> _DMLTableElement:
171 return self.statement.table
173 if TYPE_CHECKING:
175 @classmethod
176 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ...
178 @classmethod
179 def _get_multi_crud_kv_pairs(
180 cls,
181 statement: UpdateBase,
182 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]],
183 ) -> List[Dict[_DMLColumnElement, Any]]:
184 return [
185 {
186 coercions.expect(roles.DMLColumnRole, k): v
187 for k, v in mapping.items()
188 }
189 for mapping in multi_kv_iterator
190 ]
192 @classmethod
193 def _get_crud_kv_pairs(
194 cls,
195 statement: UpdateBase,
196 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]],
197 needs_to_be_cacheable: bool,
198 ) -> List[Tuple[_DMLColumnElement, Any]]:
199 return [
200 (
201 coercions.expect(roles.DMLColumnRole, k),
202 (
203 v
204 if not needs_to_be_cacheable
205 else coercions.expect(
206 roles.ExpressionElementRole,
207 v,
208 type_=NullType(),
209 is_crud=True,
210 )
211 ),
212 )
213 for k, v in kv_iterator
214 ]
216 def _make_extra_froms(
217 self,
218 statement: DMLWhereBase,
219 explicit_froms: Sequence[FromClause] = (),
220 ) -> Tuple[FromClause, List[FromClause]]:
221 froms: List[FromClause] = []
223 all_tables = list(sql_util.tables_from_leftmost(statement.table))
224 primary_table = all_tables[0]
225 seen = {primary_table}
227 def _consider_from(
228 from_: FromClause, include_surface_selectables: bool = False
229 ) -> None:
230 if not seen.intersection(from_._cloned_set):
231 froms.append(from_)
232 seen.update(from_._cloned_set)
233 if include_surface_selectables:
234 for elem in sql_util.surface_selectables_only(from_):
235 seen.update(elem._cloned_set)
237 for from_ in explicit_froms:
238 _consider_from(from_, include_surface_selectables=True)
240 consider = statement._where_criteria
241 if self._dict_parameters:
242 consider += tuple(self._dict_parameters.values())
244 for crit in consider:
245 for item in _from_objects(crit):
246 _consider_from(item)
248 froms.extend(all_tables[1:])
249 return primary_table, froms
251 def _process_values(self, statement: ValuesBase) -> None:
252 if self._no_parameters:
253 self._dict_parameters = statement._values
254 self._no_parameters = False
256 def _process_select_values(self, statement: ValuesBase) -> None:
257 assert statement._select_names is not None
258 parameters: MutableMapping[_DMLColumnElement, Any] = {
259 name: Null() for name in statement._select_names
260 }
262 if self._no_parameters:
263 self._no_parameters = False
264 self._dict_parameters = parameters
265 else:
266 # this condition normally not reachable as the Insert
267 # does not allow this construction to occur
268 assert False, "This statement already has parameters"
270 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn:
271 raise exc.InvalidRequestError(
272 "%s construct does not support "
273 "multiple parameter sets." % statement.__visit_name__.upper()
274 )
276 def _cant_mix_formats_error(self) -> NoReturn:
277 raise exc.InvalidRequestError(
278 "Can't mix single and multiple VALUES "
279 "formats in one INSERT statement; one style appends to a "
280 "list while the other replaces values, so the intent is "
281 "ambiguous."
282 )
285@CompileState.plugin_for("default", "insert")
286class InsertDMLState(DMLState):
287 isinsert = True
289 include_table_with_column_exprs = False
291 _has_multi_parameters = False
293 def __init__(
294 self,
295 statement: Insert,
296 compiler: SQLCompiler,
297 disable_implicit_returning: bool = False,
298 **kw: Any,
299 ):
300 self.statement = statement
301 self._primary_table = statement.table
303 if disable_implicit_returning:
304 self._supports_implicit_returning = False
306 self.isinsert = True
307 if statement._select_names:
308 self._process_select_values(statement)
309 if statement._values is not None:
310 self._process_values(statement)
311 if statement._multi_values:
312 self._process_multi_values(statement)
314 @util.memoized_property
315 def _insert_col_keys(self) -> List[str]:
316 # this is also done in crud.py -> _key_getters_for_crud_column
317 return [
318 coercions.expect(roles.DMLColumnRole, col, as_key=True)
319 for col in self._dict_parameters or ()
320 ]
322 def _process_values(self, statement: ValuesBase) -> None:
323 if self._no_parameters:
324 self._has_multi_parameters = False
325 self._dict_parameters = statement._values
326 self._no_parameters = False
327 elif self._has_multi_parameters:
328 self._cant_mix_formats_error()
330 def _process_multi_values(self, statement: ValuesBase) -> None:
331 for parameters in statement._multi_values:
332 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [
333 (
334 {
335 c.key: value
336 for c, value in zip(statement.table.c, parameter_set)
337 }
338 if isinstance(parameter_set, collections_abc.Sequence)
339 else parameter_set
340 )
341 for parameter_set in parameters
342 ]
344 if self._no_parameters:
345 self._no_parameters = False
346 self._has_multi_parameters = True
347 self._multi_parameters = multi_parameters
348 self._dict_parameters = self._multi_parameters[0]
349 elif not self._has_multi_parameters:
350 self._cant_mix_formats_error()
351 else:
352 assert self._multi_parameters
353 self._multi_parameters.extend(multi_parameters)
356@CompileState.plugin_for("default", "update")
357class UpdateDMLState(DMLState):
358 isupdate = True
360 include_table_with_column_exprs = False
362 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any):
363 self.statement = statement
365 self.isupdate = True
366 if statement._maintain_values_ordering:
367 self._process_ordered_values(statement)
368 elif statement._values is not None:
369 self._process_values(statement)
370 elif statement._multi_values:
371 self._no_multi_values_supported(statement)
372 t, ef = self._make_extra_froms(statement)
373 self._primary_table = t
374 self._extra_froms = ef
376 self.is_multitable = mt = ef
377 self.include_table_with_column_exprs = bool(
378 mt and compiler.render_table_with_column_in_update_from
379 )
381 def _process_ordered_values(self, statement: ValuesBase) -> None:
382 parameters = statement._values
383 if self._no_parameters:
384 self._no_parameters = False
385 assert parameters is not None
386 self._dict_parameters = dict(parameters)
387 self._maintain_values_ordering = True
388 else:
389 raise exc.InvalidRequestError(
390 "Can only invoke ordered_values() once, and not mixed "
391 "with any other values() call"
392 )
395@CompileState.plugin_for("default", "delete")
396class DeleteDMLState(DMLState):
397 isdelete = True
399 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any):
400 self.statement = statement
402 self.isdelete = True
403 t, ef = self._make_extra_froms(statement, statement._extra_froms)
404 self._primary_table = t
405 self._extra_froms = ef
406 self.is_multitable = ef
409class UpdateBase(
410 roles.DMLRole,
411 HasCTE,
412 HasCompileState,
413 DialectKWArgs,
414 HasPrefixes,
415 Generative,
416 ExecutableReturnsRows,
417 ClauseElement,
418):
419 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
421 __visit_name__ = "update_base"
423 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = (
424 util.EMPTY_DICT
425 )
426 named_with_column = False
428 _label_style: SelectLabelStyle = (
429 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY
430 )
431 table: _DMLTableElement
433 _return_defaults = False
434 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = (
435 None
436 )
437 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None
438 _returning: Tuple[_ColumnsClauseElement, ...] = ()
440 _return_defaults_traverse_internals: _TraverseInternalsType = [
441 ("_return_defaults", InternalTraversal.dp_boolean),
442 (
443 "_return_defaults_columns",
444 InternalTraversal.dp_clauseelement_tuple,
445 ),
446 (
447 "_supplemental_returning",
448 InternalTraversal.dp_clauseelement_tuple,
449 ),
450 ]
452 is_dml = True
454 def _generate_fromclause_column_proxies(
455 self,
456 fromclause: FromClause,
457 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],
458 primary_key: ColumnSet,
459 foreign_keys: Set[KeyedColumnElement[Any]],
460 ) -> None:
461 prox = [
462 c._make_proxy(
463 fromclause,
464 key=proxy_key,
465 name=required_label_name,
466 name_is_truncatable=True,
467 primary_key=primary_key,
468 foreign_keys=foreign_keys,
469 )
470 for (
471 required_label_name,
472 proxy_key,
473 fallback_label_name,
474 c,
475 repeated,
476 ) in (self._generate_columns_plus_names(False))
477 if is_column_element(c)
478 ]
480 columns._populate_separate_keys(prox)
482 def params(self, *arg: Any, **kw: Any) -> NoReturn:
483 """Set the parameters for the statement.
485 This method raises ``NotImplementedError`` on the base class,
486 and is overridden by :class:`.ValuesBase` to provide the
487 SET/VALUES clause of UPDATE and INSERT.
489 """
490 raise NotImplementedError(
491 "params() is not supported for INSERT/UPDATE/DELETE statements."
492 " To set the values for an INSERT or UPDATE statement, use"
493 " stmt.values(**parameters)."
494 )
496 @_generative
497 def with_dialect_options(self, **opt: Any) -> Self:
498 """Add dialect options to this INSERT/UPDATE/DELETE object.
500 e.g.::
502 upd = table.update().dialect_options(mysql_limit=10)
504 .. versionadded:: 1.4 - this method supersedes the dialect options
505 associated with the constructor.
508 """
509 self._validate_dialect_kwargs(opt)
510 return self
512 @_generative
513 def return_defaults(
514 self,
515 *cols: _DMLColumnArgument,
516 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None,
517 sort_by_parameter_order: bool = False,
518 ) -> Self:
519 """Make use of a :term:`RETURNING` clause for the purpose
520 of fetching server-side expressions and defaults, for supporting
521 backends only.
523 .. deepalchemy::
525 The :meth:`.UpdateBase.return_defaults` method is used by the ORM
526 for its internal work in fetching newly generated primary key
527 and server default values, in particular to provide the underlying
528 implementation of the :paramref:`_orm.Mapper.eager_defaults`
529 ORM feature as well as to allow RETURNING support with bulk
530 ORM inserts. Its behavior is fairly idiosyncratic
531 and is not really intended for general use. End users should
532 stick with using :meth:`.UpdateBase.returning` in order to
533 add RETURNING clauses to their INSERT, UPDATE and DELETE
534 statements.
536 Normally, a single row INSERT statement will automatically populate the
537 :attr:`.CursorResult.inserted_primary_key` attribute when executed,
538 which stores the primary key of the row that was just inserted in the
539 form of a :class:`.Row` object with column names as named tuple keys
540 (and the :attr:`.Row._mapping` view fully populated as well). The
541 dialect in use chooses the strategy to use in order to populate this
542 data; if it was generated using server-side defaults and / or SQL
543 expressions, dialect-specific approaches such as ``cursor.lastrowid``
544 or ``RETURNING`` are typically used to acquire the new primary key
545 value.
547 However, when the statement is modified by calling
548 :meth:`.UpdateBase.return_defaults` before executing the statement,
549 additional behaviors take place **only** for backends that support
550 RETURNING and for :class:`.Table` objects that maintain the
551 :paramref:`.Table.implicit_returning` parameter at its default value of
552 ``True``. In these cases, when the :class:`.CursorResult` is returned
553 from the statement's execution, not only will
554 :attr:`.CursorResult.inserted_primary_key` be populated as always, the
555 :attr:`.CursorResult.returned_defaults` attribute will also be
556 populated with a :class:`.Row` named-tuple representing the full range
557 of server generated
558 values from that single row, including values for any columns that
559 specify :paramref:`_schema.Column.server_default` or which make use of
560 :paramref:`_schema.Column.default` using a SQL expression.
562 When invoking INSERT statements with multiple rows using
563 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the
564 :meth:`.UpdateBase.return_defaults` modifier will have the effect of
565 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and
566 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being
567 fully populated with lists of :class:`.Row` objects representing newly
568 inserted primary key values as well as newly inserted server generated
569 values for each row inserted. The
570 :attr:`.CursorResult.inserted_primary_key` and
571 :attr:`.CursorResult.returned_defaults` attributes will also continue
572 to be populated with the first row of these two collections.
574 If the backend does not support RETURNING or the :class:`.Table` in use
575 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING
576 clause is added and no additional data is fetched, however the
577 INSERT, UPDATE or DELETE statement proceeds normally.
579 E.g.::
581 stmt = table.insert().values(data="newdata").return_defaults()
583 result = connection.execute(stmt)
585 server_created_at = result.returned_defaults["created_at"]
587 When used against an UPDATE statement
588 :meth:`.UpdateBase.return_defaults` instead looks for columns that
589 include :paramref:`_schema.Column.onupdate` or
590 :paramref:`_schema.Column.server_onupdate` parameters assigned, when
591 constructing the columns that will be included in the RETURNING clause
592 by default if explicit columns were not specified. When used against a
593 DELETE statement, no columns are included in RETURNING by default, they
594 instead must be specified explicitly as there are no columns that
595 normally change values when a DELETE statement proceeds.
597 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported
598 for DELETE statements also and has been moved from
599 :class:`.ValuesBase` to :class:`.UpdateBase`.
601 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive
602 against the :meth:`.UpdateBase.returning` method and errors will be
603 raised during the SQL compilation process if both are used at the same
604 time on one statement. The RETURNING clause of the INSERT, UPDATE or
605 DELETE statement is therefore controlled by only one of these methods
606 at a time.
608 The :meth:`.UpdateBase.return_defaults` method differs from
609 :meth:`.UpdateBase.returning` in these ways:
611 1. :meth:`.UpdateBase.return_defaults` method causes the
612 :attr:`.CursorResult.returned_defaults` collection to be populated
613 with the first row from the RETURNING result. This attribute is not
614 populated when using :meth:`.UpdateBase.returning`.
616 2. :meth:`.UpdateBase.return_defaults` is compatible with existing
617 logic used to fetch auto-generated primary key values that are then
618 populated into the :attr:`.CursorResult.inserted_primary_key`
619 attribute. By contrast, using :meth:`.UpdateBase.returning` will
620 have the effect of the :attr:`.CursorResult.inserted_primary_key`
621 attribute being left unpopulated.
623 3. :meth:`.UpdateBase.return_defaults` can be called against any
624 backend. Backends that don't support RETURNING will skip the usage
625 of the feature, rather than raising an exception, *unless*
626 ``supplemental_cols`` is passed. The return value
627 of :attr:`_engine.CursorResult.returned_defaults` will be ``None``
628 for backends that don't support RETURNING or for which the target
629 :class:`.Table` sets :paramref:`.Table.implicit_returning` to
630 ``False``.
632 4. An INSERT statement invoked with executemany() is supported if the
633 backend database driver supports the
634 :ref:`insertmanyvalues <engine_insertmanyvalues>`
635 feature which is now supported by most SQLAlchemy-included backends.
636 When executemany is used, the
637 :attr:`_engine.CursorResult.returned_defaults_rows` and
638 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors
639 will return the inserted defaults and primary keys.
641 .. versionadded:: 1.4 Added
642 :attr:`_engine.CursorResult.returned_defaults_rows` and
643 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors.
644 In version 2.0, the underlying implementation which fetches and
645 populates the data for these attributes was generalized to be
646 supported by most backends, whereas in 1.4 they were only
647 supported by the ``psycopg2`` driver.
650 :param cols: optional list of column key names or
651 :class:`_schema.Column` that acts as a filter for those columns that
652 will be fetched.
653 :param supplemental_cols: optional list of RETURNING expressions,
654 in the same form as one would pass to the
655 :meth:`.UpdateBase.returning` method. When present, the additional
656 columns will be included in the RETURNING clause, and the
657 :class:`.CursorResult` object will be "rewound" when returned, so
658 that methods like :meth:`.CursorResult.all` will return new rows
659 mostly as though the statement used :meth:`.UpdateBase.returning`
660 directly. However, unlike when using :meth:`.UpdateBase.returning`
661 directly, the **order of the columns is undefined**, so can only be
662 targeted using names or :attr:`.Row._mapping` keys; they cannot
663 reliably be targeted positionally.
665 .. versionadded:: 2.0
667 :param sort_by_parameter_order: for a batch INSERT that is being
668 executed against multiple parameter sets, organize the results of
669 RETURNING so that the returned rows correspond to the order of
670 parameter sets passed in. This applies only to an :term:`executemany`
671 execution for supporting dialects and typically makes use of the
672 :term:`insertmanyvalues` feature.
674 .. versionadded:: 2.0.10
676 .. seealso::
678 :ref:`engine_insertmanyvalues_returning_order` - background on
679 sorting of RETURNING rows for bulk INSERT
681 .. seealso::
683 :meth:`.UpdateBase.returning`
685 :attr:`_engine.CursorResult.returned_defaults`
687 :attr:`_engine.CursorResult.returned_defaults_rows`
689 :attr:`_engine.CursorResult.inserted_primary_key`
691 :attr:`_engine.CursorResult.inserted_primary_key_rows`
693 """
695 if self._return_defaults:
696 # note _return_defaults_columns = () means return all columns,
697 # so if we have been here before, only update collection if there
698 # are columns in the collection
699 if self._return_defaults_columns and cols:
700 self._return_defaults_columns = tuple(
701 util.OrderedSet(self._return_defaults_columns).union(
702 coercions.expect(roles.ColumnsClauseRole, c)
703 for c in cols
704 )
705 )
706 else:
707 # set for all columns
708 self._return_defaults_columns = ()
709 else:
710 self._return_defaults_columns = tuple(
711 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
712 )
713 self._return_defaults = True
714 if sort_by_parameter_order:
715 if not self.is_insert:
716 raise exc.ArgumentError(
717 "The 'sort_by_parameter_order' argument to "
718 "return_defaults() only applies to INSERT statements"
719 )
720 self._sort_by_parameter_order = True
721 if supplemental_cols:
722 # uniquifying while also maintaining order (the maintain of order
723 # is for test suites but also for vertical splicing
724 supplemental_col_tup = (
725 coercions.expect(roles.ColumnsClauseRole, c)
726 for c in supplemental_cols
727 )
729 if self._supplemental_returning is None:
730 self._supplemental_returning = tuple(
731 util.unique_list(supplemental_col_tup)
732 )
733 else:
734 self._supplemental_returning = tuple(
735 util.unique_list(
736 self._supplemental_returning
737 + tuple(supplemental_col_tup)
738 )
739 )
741 return self
743 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
744 """Return ``True`` if this :class:`.ReturnsRows` is
745 'derived' from the given :class:`.FromClause`.
747 Since these are DMLs, we dont want such statements ever being adapted
748 so we return False for derives.
750 """
751 return False
753 @_generative
754 def returning(
755 self,
756 *cols: _ColumnsClauseArgument[Any],
757 sort_by_parameter_order: bool = False,
758 **__kw: Any,
759 ) -> UpdateBase:
760 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
762 e.g.:
764 .. sourcecode:: pycon+sql
766 >>> stmt = (
767 ... table.update()
768 ... .where(table.c.data == "value")
769 ... .values(status="X")
770 ... .returning(table.c.server_flag, table.c.updated_timestamp)
771 ... )
772 >>> print(stmt)
773 {printsql}UPDATE some_table SET status=:status
774 WHERE some_table.data = :data_1
775 RETURNING some_table.server_flag, some_table.updated_timestamp
777 The method may be invoked multiple times to add new entries to the
778 list of expressions to be returned.
780 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to
781 add new entries to the list of expressions to be returned.
783 The given collection of column expressions should be derived from the
784 table that is the target of the INSERT, UPDATE, or DELETE. While
785 :class:`_schema.Column` objects are typical, the elements can also be
786 expressions:
788 .. sourcecode:: pycon+sql
790 >>> stmt = table.insert().returning(
791 ... (table.c.first_name + " " + table.c.last_name).label("fullname")
792 ... )
793 >>> print(stmt)
794 {printsql}INSERT INTO some_table (first_name, last_name)
795 VALUES (:first_name, :last_name)
796 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname
798 Upon compilation, a RETURNING clause, or database equivalent,
799 will be rendered within the statement. For INSERT and UPDATE,
800 the values are the newly inserted/updated values. For DELETE,
801 the values are those of the rows which were deleted.
803 Upon execution, the values of the columns to be returned are made
804 available via the result set and can be iterated using
805 :meth:`_engine.CursorResult.fetchone` and similar.
806 For DBAPIs which do not
807 natively support returning values (i.e. cx_oracle), SQLAlchemy will
808 approximate this behavior at the result level so that a reasonable
809 amount of behavioral neutrality is provided.
811 Note that not all databases/DBAPIs
812 support RETURNING. For those backends with no support,
813 an exception is raised upon compilation and/or execution.
814 For those who do support it, the functionality across backends
815 varies greatly, including restrictions on executemany()
816 and other statements which return multiple rows. Please
817 read the documentation notes for the database in use in
818 order to determine the availability of RETURNING.
820 :param \*cols: series of columns, SQL expressions, or whole tables
821 entities to be returned.
822 :param sort_by_parameter_order: for a batch INSERT that is being
823 executed against multiple parameter sets, organize the results of
824 RETURNING so that the returned rows correspond to the order of
825 parameter sets passed in. This applies only to an :term:`executemany`
826 execution for supporting dialects and typically makes use of the
827 :term:`insertmanyvalues` feature.
829 .. versionadded:: 2.0.10
831 .. seealso::
833 :ref:`engine_insertmanyvalues_returning_order` - background on
834 sorting of RETURNING rows for bulk INSERT (Core level discussion)
836 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of
837 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion)
839 .. seealso::
841 :meth:`.UpdateBase.return_defaults` - an alternative method tailored
842 towards efficient fetching of server-side defaults and triggers
843 for single-row INSERTs or UPDATEs.
845 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`
847 """ # noqa: E501
848 if __kw:
849 raise _unexpected_kw("UpdateBase.returning()", __kw)
850 if self._return_defaults:
851 raise exc.InvalidRequestError(
852 "return_defaults() is already configured on this statement"
853 )
854 self._returning += tuple(
855 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
856 )
857 if sort_by_parameter_order:
858 if not self.is_insert:
859 raise exc.ArgumentError(
860 "The 'sort_by_parameter_order' argument to returning() "
861 "only applies to INSERT statements"
862 )
863 self._sort_by_parameter_order = True
864 return self
866 def corresponding_column(
867 self, column: KeyedColumnElement[Any], require_embedded: bool = False
868 ) -> Optional[ColumnElement[Any]]:
869 return self.exported_columns.corresponding_column(
870 column, require_embedded=require_embedded
871 )
873 @util.ro_memoized_property
874 def _all_selected_columns(self) -> _SelectIterable:
875 return [c for c in _select_iterables(self._returning)]
877 @util.ro_memoized_property
878 def exported_columns(
879 self,
880 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]:
881 """Return the RETURNING columns as a column collection for this
882 statement.
884 .. versionadded:: 1.4
886 """
887 return WriteableColumnCollection(
888 (c.key, c)
889 for c in self._all_selected_columns
890 if is_column_element(c)
891 ).as_readonly()
893 @_generative
894 def with_hint(
895 self,
896 text: str,
897 selectable: Optional[_DMLTableArgument] = None,
898 dialect_name: str = "*",
899 ) -> Self:
900 """Add a table hint for a single table to this
901 INSERT/UPDATE/DELETE statement.
903 .. note::
905 :meth:`.UpdateBase.with_hint` currently applies only to
906 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
907 :meth:`.UpdateBase.prefix_with`.
909 The text of the hint is rendered in the appropriate
910 location for the database backend in use, relative
911 to the :class:`_schema.Table` that is the subject of this
912 statement, or optionally to that of the given
913 :class:`_schema.Table` passed as the ``selectable`` argument.
915 The ``dialect_name`` option will limit the rendering of a particular
916 hint to a particular backend. Such as, to add a hint
917 that only takes effect for SQL Server::
919 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
921 :param text: Text of the hint.
922 :param selectable: optional :class:`_schema.Table` that specifies
923 an element of the FROM clause within an UPDATE or DELETE
924 to be the subject of the hint - applies only to certain backends.
925 :param dialect_name: defaults to ``*``, if specified as the name
926 of a particular dialect, will apply these hints only when
927 that dialect is in use.
928 """
929 if selectable is None:
930 selectable = self.table
931 else:
932 selectable = coercions.expect(roles.DMLTableRole, selectable)
933 self._hints = self._hints.union({(selectable, dialect_name): text})
934 return self
936 @property
937 def entity_description(self) -> Dict[str, Any]:
938 """Return a :term:`plugin-enabled` description of the table and/or
939 entity which this DML construct is operating against.
941 This attribute is generally useful when using the ORM, as an
942 extended structure which includes information about mapped
943 entities is returned. The section :ref:`queryguide_inspection`
944 contains more background.
946 For a Core statement, the structure returned by this accessor
947 is derived from the :attr:`.UpdateBase.table` attribute, and
948 refers to the :class:`.Table` being inserted, updated, or deleted::
950 >>> stmt = insert(user_table)
951 >>> stmt.entity_description
952 {
953 "name": "user_table",
954 "table": Table("user_table", ...)
955 }
957 .. versionadded:: 1.4.33
959 .. seealso::
961 :attr:`.UpdateBase.returning_column_descriptions`
963 :attr:`.Select.column_descriptions` - entity information for
964 a :func:`.select` construct
966 :ref:`queryguide_inspection` - ORM background
968 """
969 meth = DMLState.get_plugin_class(self).get_entity_description
970 return meth(self)
972 @property
973 def returning_column_descriptions(self) -> List[Dict[str, Any]]:
974 """Return a :term:`plugin-enabled` description of the columns
975 which this DML construct is RETURNING against, in other words
976 the expressions established as part of :meth:`.UpdateBase.returning`.
978 This attribute is generally useful when using the ORM, as an
979 extended structure which includes information about mapped
980 entities is returned. The section :ref:`queryguide_inspection`
981 contains more background.
983 For a Core statement, the structure returned by this accessor is
984 derived from the same objects that are returned by the
985 :attr:`.UpdateBase.exported_columns` accessor::
987 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name)
988 >>> stmt.entity_description
989 [
990 {
991 "name": "id",
992 "type": Integer,
993 "expr": Column("id", Integer(), table=<user>, ...)
994 },
995 {
996 "name": "name",
997 "type": String(),
998 "expr": Column("name", String(), table=<user>, ...)
999 },
1000 ]
1002 .. versionadded:: 1.4.33
1004 .. seealso::
1006 :attr:`.UpdateBase.entity_description`
1008 :attr:`.Select.column_descriptions` - entity information for
1009 a :func:`.select` construct
1011 :ref:`queryguide_inspection` - ORM background
1013 """ # noqa: E501
1014 meth = DMLState.get_plugin_class(
1015 self
1016 ).get_returning_column_descriptions
1017 return meth(self)
1020class ValuesBase(UpdateBase):
1021 """Supplies support for :meth:`.ValuesBase.values` to
1022 INSERT and UPDATE constructs."""
1024 __visit_name__ = "values_base"
1026 _supports_multi_parameters = False
1028 select: Optional[Select[Unpack[TupleAny]]] = None
1029 """SELECT statement for INSERT .. FROM SELECT"""
1031 _post_values_clause: Optional[ClauseElement] = None
1032 """used by extensions to Insert etc. to add additional syntactical
1033 constructs, e.g. ON CONFLICT etc."""
1035 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None
1036 _multi_values: Tuple[
1037 Union[
1038 Sequence[Dict[_DMLColumnElement, Any]],
1039 Sequence[Sequence[Any]],
1040 ],
1041 ...,
1042 ] = ()
1044 _maintain_values_ordering: bool = False
1046 _select_names: Optional[List[str]] = None
1047 _inline: bool = False
1049 def __init__(self, table: _DMLTableArgument):
1050 self.table = coercions.expect(
1051 roles.DMLTableRole, table, apply_propagate_attrs=self
1052 )
1054 @_generative
1055 @_exclusive_against(
1056 "_select_names",
1057 "_maintain_values_ordering",
1058 msgs={
1059 "_select_names": "This construct already inserts from a SELECT",
1060 "_maintain_values_ordering": "This statement already has ordered "
1061 "values present",
1062 },
1063 defaults={"_maintain_values_ordering": False},
1064 )
1065 def values(
1066 self,
1067 *args: Union[
1068 _DMLColumnKeyMapping[Any],
1069 Sequence[Any],
1070 ],
1071 **kwargs: Any,
1072 ) -> Self:
1073 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
1074 clause for an UPDATE.
1076 Note that the :class:`_expression.Insert` and
1077 :class:`_expression.Update`
1078 constructs support
1079 per-execution time formatting of the VALUES and/or SET clauses,
1080 based on the arguments passed to :meth:`_engine.Connection.execute`.
1081 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
1082 particular set of parameters into the statement.
1084 Multiple calls to :meth:`.ValuesBase.values` will produce a new
1085 construct, each one with the parameter list modified to include
1086 the new parameters sent. In the typical case of a single
1087 dictionary of parameters, the newly passed keys will replace
1088 the same keys in the previous construct. In the case of a list-based
1089 "multiple values" construct, each new list of values is extended
1090 onto the existing list of values.
1092 :param \**kwargs: key value pairs representing the string key
1093 of a :class:`_schema.Column`
1094 mapped to the value to be rendered into the
1095 VALUES or SET clause::
1097 users.insert().values(name="some name")
1099 users.update().where(users.c.id == 5).values(name="some name")
1101 :param \*args: As an alternative to passing key/value parameters,
1102 a dictionary, tuple, or list of dictionaries or tuples can be passed
1103 as a single positional argument in order to form the VALUES or
1104 SET clause of the statement. The forms that are accepted vary
1105 based on whether this is an :class:`_expression.Insert` or an
1106 :class:`_expression.Update` construct.
1108 For either an :class:`_expression.Insert` or
1109 :class:`_expression.Update`
1110 construct, a single dictionary can be passed, which works the same as
1111 that of the kwargs form::
1113 users.insert().values({"name": "some name"})
1115 users.update().values({"name": "some new name"})
1117 Also for either form but more typically for the
1118 :class:`_expression.Insert` construct, a tuple that contains an
1119 entry for every column in the table is also accepted::
1121 users.insert().values((5, "some name"))
1123 The :class:`_expression.Insert` construct also supports being
1124 passed a list of dictionaries or full-table-tuples, which on the
1125 server will render the less common SQL syntax of "multiple values" -
1126 this syntax is supported on backends such as SQLite, PostgreSQL,
1127 MySQL, but not necessarily others::
1129 users.insert().values(
1130 [
1131 {"name": "some name"},
1132 {"name": "some other name"},
1133 {"name": "yet another name"},
1134 ]
1135 )
1137 The above form would render a multiple VALUES statement similar to:
1139 .. sourcecode:: sql
1141 INSERT INTO users (name) VALUES
1142 (:name_1),
1143 (:name_2),
1144 (:name_3)
1146 It is essential to note that **passing multiple values is
1147 NOT the same as using traditional executemany() form**. The above
1148 syntax is a **special** syntax not typically used. To emit an
1149 INSERT statement against multiple rows, the normal method is
1150 to pass a multiple values list to the
1151 :meth:`_engine.Connection.execute`
1152 method, which is supported by all database backends and is generally
1153 more efficient for a very large number of parameters.
1155 .. seealso::
1157 :ref:`tutorial_multiple_parameters` - an introduction to
1158 the traditional Core method of multiple parameter set
1159 invocation for INSERTs and other statements.
1161 :ref:`tutorial_core_insert_values_clause` - Insert tutorial
1162 detailing alternatives to the multiple values syntax.
1164 The UPDATE construct also supports rendering the SET parameters
1165 in a specific order. For this feature refer to the
1166 :meth:`_expression.Update.ordered_values` method.
1168 .. seealso::
1170 :meth:`_expression.Update.ordered_values`
1173 """
1174 if args:
1175 # positional case. this is currently expensive. we don't
1176 # yet have positional-only args so we have to check the length.
1177 # then we need to check multiparams vs. single dictionary.
1178 # since the parameter format is needed in order to determine
1179 # a cache key, we need to determine this up front.
1180 arg = args[0]
1182 if kwargs:
1183 raise exc.ArgumentError(
1184 "Can't pass positional and kwargs to values() "
1185 "simultaneously"
1186 )
1187 elif len(args) > 1:
1188 raise exc.ArgumentError(
1189 "Only a single dictionary/tuple or list of "
1190 "dictionaries/tuples is accepted positionally."
1191 )
1193 elif isinstance(arg, collections_abc.Sequence):
1194 if arg and isinstance(arg[0], dict):
1195 multi_kv_generator = DMLState.get_plugin_class(
1196 self
1197 )._get_multi_crud_kv_pairs
1198 self._multi_values += (multi_kv_generator(self, arg),)
1199 return self
1201 if arg and isinstance(arg[0], (list, tuple)):
1202 self._multi_values += (arg,)
1203 return self
1205 if TYPE_CHECKING:
1206 # crud.py raises during compilation if this is not the
1207 # case
1208 assert isinstance(self, Insert)
1210 # tuple values
1211 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1213 else:
1214 # kwarg path. this is the most common path for non-multi-params
1215 # so this is fairly quick.
1216 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1217 if args:
1218 raise exc.ArgumentError(
1219 "Only a single dictionary/tuple or list of "
1220 "dictionaries/tuples is accepted positionally."
1221 )
1223 # for top level values(), convert literals to anonymous bound
1224 # parameters at statement construction time, so that these values can
1225 # participate in the cache key process like any other ClauseElement.
1226 # crud.py now intercepts bound parameters with unique=True from here
1227 # and ensures they get the "crud"-style name when rendered.
1229 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1230 coerced_arg = dict(kv_generator(self, arg.items(), True))
1231 if self._values:
1232 self._values = self._values.union(coerced_arg)
1233 else:
1234 self._values = util.immutabledict(coerced_arg)
1235 return self
1238class Insert(ValuesBase, HasSyntaxExtensions[Literal["post_values"]]):
1239 """Represent an INSERT construct.
1241 The :class:`_expression.Insert` object is created using the
1242 :func:`_expression.insert()` function.
1244 Available extension points:
1246 * ``post_values``: applies additional logic after the ``VALUES`` clause.
1248 """
1250 __visit_name__ = "insert"
1252 _supports_multi_parameters = True
1254 select = None
1255 include_insert_from_select_defaults = False
1257 _sort_by_parameter_order: bool = False
1259 is_insert = True
1261 table: TableClause
1263 _traverse_internals = (
1264 [
1265 ("table", InternalTraversal.dp_clauseelement),
1266 ("_inline", InternalTraversal.dp_boolean),
1267 ("_select_names", InternalTraversal.dp_string_list),
1268 ("_values", InternalTraversal.dp_dml_values),
1269 ("_multi_values", InternalTraversal.dp_dml_multi_values),
1270 ("select", InternalTraversal.dp_clauseelement),
1271 ("_post_values_clause", InternalTraversal.dp_clauseelement),
1272 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1273 ("_hints", InternalTraversal.dp_table_hint_list),
1274 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1275 (
1276 "include_insert_from_select_defaults",
1277 InternalTraversal.dp_boolean,
1278 ),
1279 ]
1280 + UpdateBase._return_defaults_traverse_internals
1281 + HasPrefixes._has_prefixes_traverse_internals
1282 + DialectKWArgs._dialect_kwargs_traverse_internals
1283 + ExecutableStatement._executable_traverse_internals
1284 + HasCTE._has_ctes_traverse_internals
1285 )
1287 _position_map = util.immutabledict(
1288 {
1289 "post_values": "_post_values_clause",
1290 }
1291 )
1293 _post_values_clause: Optional[ClauseElement] = None
1294 """extension point for a ClauseElement that will be compiled directly
1295 after the VALUES portion of the :class:`.Insert` statement
1297 """
1299 def __init__(self, table: _DMLTableArgument):
1300 super().__init__(table)
1302 def _apply_syntax_extension_to_self(
1303 self, extension: SyntaxExtension
1304 ) -> None:
1305 extension.apply_to_insert(self)
1307 @_generative
1308 def inline(self) -> Self:
1309 """Make this :class:`_expression.Insert` construct "inline" .
1311 When set, no attempt will be made to retrieve the
1312 SQL-generated default values to be provided within the statement;
1313 in particular,
1314 this allows SQL expressions to be rendered 'inline' within the
1315 statement without the need to pre-execute them beforehand; for
1316 backends that support "returning", this turns off the "implicit
1317 returning" feature for the statement.
1320 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1321 parameter
1322 is now superseded by the :meth:`_expression.Insert.inline` method.
1324 """
1325 self._inline = True
1326 return self
1328 @_generative
1329 def from_select(
1330 self,
1331 names: Sequence[_DMLColumnArgument],
1332 select: Selectable,
1333 include_defaults: bool = True,
1334 ) -> Self:
1335 """Return a new :class:`_expression.Insert` construct which represents
1336 an ``INSERT...FROM SELECT`` statement.
1338 e.g.::
1340 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1341 ins = table2.insert().from_select(["a", "b"], sel)
1343 :param names: a sequence of string column names or
1344 :class:`_schema.Column`
1345 objects representing the target columns.
1346 :param select: a :func:`_expression.select` construct,
1347 :class:`_expression.FromClause`
1348 or other construct which resolves into a
1349 :class:`_expression.FromClause`,
1350 such as an ORM :class:`_query.Query` object, etc. The order of
1351 columns returned from this FROM clause should correspond to the
1352 order of columns sent as the ``names`` parameter; while this
1353 is not checked before passing along to the database, the database
1354 would normally raise an exception if these column lists don't
1355 correspond.
1356 :param include_defaults: if True, non-server default values and
1357 SQL expressions as specified on :class:`_schema.Column` objects
1358 (as documented in :ref:`metadata_defaults_toplevel`) not
1359 otherwise specified in the list of names will be rendered
1360 into the INSERT and SELECT statements, so that these values are also
1361 included in the data to be inserted.
1363 .. note:: A Python-side default that uses a Python callable function
1364 will only be invoked **once** for the whole statement, and **not
1365 per row**.
1367 """
1369 if self._values:
1370 raise exc.InvalidRequestError(
1371 "This construct already inserts value expressions"
1372 )
1374 self._select_names = [
1375 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1376 for name in names
1377 ]
1378 self._inline = True
1379 self.include_insert_from_select_defaults = include_defaults
1380 self.select = coercions.expect(roles.DMLSelectRole, select)
1381 return self
1383 if TYPE_CHECKING:
1384 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1386 # code within this block is **programmatically,
1387 # statically generated** by tools/generate_tuple_map_overloads.py
1389 @overload
1390 def returning(
1391 self,
1392 __ent0: _TCCA[_T0],
1393 /,
1394 *,
1395 sort_by_parameter_order: bool = False,
1396 ) -> ReturningInsert[_T0]: ...
1398 @overload
1399 def returning(
1400 self,
1401 __ent0: _TCCA[_T0],
1402 __ent1: _TCCA[_T1],
1403 /,
1404 *,
1405 sort_by_parameter_order: bool = False,
1406 ) -> ReturningInsert[_T0, _T1]: ...
1408 @overload
1409 def returning(
1410 self,
1411 __ent0: _TCCA[_T0],
1412 __ent1: _TCCA[_T1],
1413 __ent2: _TCCA[_T2],
1414 /,
1415 *,
1416 sort_by_parameter_order: bool = False,
1417 ) -> ReturningInsert[_T0, _T1, _T2]: ...
1419 @overload
1420 def returning(
1421 self,
1422 __ent0: _TCCA[_T0],
1423 __ent1: _TCCA[_T1],
1424 __ent2: _TCCA[_T2],
1425 __ent3: _TCCA[_T3],
1426 /,
1427 *,
1428 sort_by_parameter_order: bool = False,
1429 ) -> ReturningInsert[_T0, _T1, _T2, _T3]: ...
1431 @overload
1432 def returning(
1433 self,
1434 __ent0: _TCCA[_T0],
1435 __ent1: _TCCA[_T1],
1436 __ent2: _TCCA[_T2],
1437 __ent3: _TCCA[_T3],
1438 __ent4: _TCCA[_T4],
1439 /,
1440 *,
1441 sort_by_parameter_order: bool = False,
1442 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4]: ...
1444 @overload
1445 def returning(
1446 self,
1447 __ent0: _TCCA[_T0],
1448 __ent1: _TCCA[_T1],
1449 __ent2: _TCCA[_T2],
1450 __ent3: _TCCA[_T3],
1451 __ent4: _TCCA[_T4],
1452 __ent5: _TCCA[_T5],
1453 /,
1454 *,
1455 sort_by_parameter_order: bool = False,
1456 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1458 @overload
1459 def returning(
1460 self,
1461 __ent0: _TCCA[_T0],
1462 __ent1: _TCCA[_T1],
1463 __ent2: _TCCA[_T2],
1464 __ent3: _TCCA[_T3],
1465 __ent4: _TCCA[_T4],
1466 __ent5: _TCCA[_T5],
1467 __ent6: _TCCA[_T6],
1468 /,
1469 *,
1470 sort_by_parameter_order: bool = False,
1471 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1473 @overload
1474 def returning(
1475 self,
1476 __ent0: _TCCA[_T0],
1477 __ent1: _TCCA[_T1],
1478 __ent2: _TCCA[_T2],
1479 __ent3: _TCCA[_T3],
1480 __ent4: _TCCA[_T4],
1481 __ent5: _TCCA[_T5],
1482 __ent6: _TCCA[_T6],
1483 __ent7: _TCCA[_T7],
1484 /,
1485 *entities: _ColumnsClauseArgument[Any],
1486 sort_by_parameter_order: bool = False,
1487 ) -> ReturningInsert[
1488 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1489 ]: ...
1491 # END OVERLOADED FUNCTIONS self.returning
1493 @overload
1494 def returning(
1495 self,
1496 *cols: _ColumnsClauseArgument[Any],
1497 sort_by_parameter_order: bool = False,
1498 **__kw: Any,
1499 ) -> ReturningInsert[Any]: ...
1501 def returning(
1502 self,
1503 *cols: _ColumnsClauseArgument[Any],
1504 sort_by_parameter_order: bool = False,
1505 **__kw: Any,
1506 ) -> ReturningInsert[Any]: ...
1509class ReturningInsert(Insert, TypedReturnsRows[Unpack[_Ts]]):
1510 """Typing-only class that establishes a generic type form of
1511 :class:`.Insert` which tracks returned column types.
1513 This datatype is delivered when calling the
1514 :meth:`.Insert.returning` method.
1516 .. versionadded:: 2.0
1518 """
1521# note: if not for MRO issues, this class should extend
1522# from HasSyntaxExtensions[Literal["post_criteria"]]
1523class DMLWhereBase:
1524 table: _DMLTableElement
1525 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1527 _post_criteria_clause: Optional[ClauseElement] = None
1528 """used by extensions to Update/Delete etc. to add additional syntacitcal
1529 constructs, e.g. LIMIT etc.
1531 .. versionadded:: 2.1
1533 """
1535 # can't put position_map here either without HasSyntaxExtensions
1536 # _position_map = util.immutabledict(
1537 # {"post_criteria": "_post_criteria_clause"}
1538 # )
1540 @_generative
1541 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1542 """Return a new construct with the given expression(s) added to
1543 its WHERE clause, joined to the existing clause via AND, if any.
1545 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1546 support multiple-table forms, including database-specific
1547 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1548 don't have multiple-table support, a backend agnostic approach
1549 to using multiple tables is to make use of correlated subqueries.
1550 See the linked tutorial sections below for examples.
1552 .. seealso::
1554 :ref:`tutorial_correlated_updates`
1556 :ref:`tutorial_update_from`
1558 :ref:`tutorial_multi_table_deletes`
1560 """
1562 for criterion in whereclause:
1563 where_criteria: ColumnElement[Any] = coercions.expect(
1564 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1565 )
1566 self._where_criteria += (where_criteria,)
1567 return self
1569 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1570 """A synonym for the :meth:`.where` method.
1572 .. versionadded:: 1.4
1574 """
1576 return self.where(*criteria)
1578 def filter_by(self, **kwargs: Any) -> Self:
1579 r"""Apply the given filtering criterion as a WHERE clause
1580 to this DML statement, using keyword expressions.
1582 E.g.::
1584 stmt = update(User).filter_by(name="some name").values(fullname="New Name")
1586 Multiple criteria may be specified as comma separated; the effect
1587 is that they will be joined together using the :func:`.and_`
1588 function::
1590 stmt = delete(User).filter_by(name="some name", id=5)
1592 The keyword expressions are extracted by searching across **all
1593 entities present in the FROM clause** of the statement.
1595 .. versionchanged:: 2.1
1597 :meth:`.DMLWhereBase.filter_by` now searches across all FROM clause
1598 entities, consistent with :meth:`_sql.Select.filter_by`.
1600 .. seealso::
1602 :meth:`.where` - filter on SQL expressions.
1604 :meth:`_sql.Select.filter_by`
1606 """ # noqa: E501
1608 entities: set[Any]
1610 if not isinstance(self.table, TableClause):
1611 entities = set(
1612 sql_util.find_tables(
1613 self.table, check_columns=False, include_joins=False
1614 )
1615 )
1616 else:
1617 entities = {self.table}
1619 if self.whereclause is not None:
1620 entities.update(self.whereclause._from_objects)
1622 clauses = [
1623 _entity_namespace_key_search_all(entities, key) == value
1624 for key, value in kwargs.items()
1625 ]
1626 return self.filter(*clauses)
1628 @property
1629 def whereclause(self) -> Optional[ColumnElement[Any]]:
1630 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1631 statement.
1633 This assembles the current collection of WHERE criteria
1634 into a single :class:`_expression.BooleanClauseList` construct.
1637 .. versionadded:: 1.4
1639 """
1641 return BooleanClauseList._construct_for_whereclause(
1642 self._where_criteria
1643 )
1646class Update(
1647 DMLWhereBase, ValuesBase, HasSyntaxExtensions[Literal["post_criteria"]]
1648):
1649 """Represent an Update construct.
1651 The :class:`_expression.Update` object is created using the
1652 :func:`_expression.update()` function.
1654 Available extension points:
1656 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1658 """
1660 __visit_name__ = "update"
1662 is_update = True
1664 _traverse_internals = (
1665 [
1666 ("table", InternalTraversal.dp_clauseelement),
1667 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1668 ("_inline", InternalTraversal.dp_boolean),
1669 ("_maintain_values_ordering", InternalTraversal.dp_boolean),
1670 ("_values", InternalTraversal.dp_dml_values),
1671 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1672 ("_hints", InternalTraversal.dp_table_hint_list),
1673 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1674 ]
1675 + UpdateBase._return_defaults_traverse_internals
1676 + HasPrefixes._has_prefixes_traverse_internals
1677 + DialectKWArgs._dialect_kwargs_traverse_internals
1678 + ExecutableStatement._executable_traverse_internals
1679 + HasCTE._has_ctes_traverse_internals
1680 )
1682 _position_map = util.immutabledict(
1683 {"post_criteria": "_post_criteria_clause"}
1684 )
1686 def __init__(self, table: _DMLTableArgument):
1687 super().__init__(table)
1689 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1690 """Specify the VALUES clause of this UPDATE statement with an explicit
1691 parameter ordering that will be maintained in the SET clause of the
1692 resulting UPDATE statement.
1694 E.g.::
1696 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1698 .. seealso::
1700 :ref:`tutorial_parameter_ordered_updates` - full example of the
1701 :meth:`_expression.Update.ordered_values` method.
1703 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1704 method
1705 supersedes the
1706 :paramref:`_expression.update.preserve_parameter_order`
1707 parameter, which will be removed in SQLAlchemy 2.0.
1709 """ # noqa: E501
1710 if self._values:
1711 raise exc.ArgumentError(
1712 "This statement already has "
1713 f"{'ordered ' if self._maintain_values_ordering else ''}"
1714 "values present"
1715 )
1717 self = self.values(dict(args))
1718 self._maintain_values_ordering = True
1719 return self
1721 @_generative
1722 def inline(self) -> Self:
1723 """Make this :class:`_expression.Update` construct "inline" .
1725 When set, SQL defaults present on :class:`_schema.Column`
1726 objects via the
1727 ``default`` keyword will be compiled 'inline' into the statement and
1728 not pre-executed. This means that their values will not be available
1729 in the dictionary returned from
1730 :meth:`_engine.CursorResult.last_updated_params`.
1732 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1733 parameter
1734 is now superseded by the :meth:`_expression.Update.inline` method.
1736 """
1737 self._inline = True
1738 return self
1740 def _apply_syntax_extension_to_self(
1741 self, extension: SyntaxExtension
1742 ) -> None:
1743 extension.apply_to_update(self)
1745 if TYPE_CHECKING:
1746 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1748 # code within this block is **programmatically,
1749 # statically generated** by tools/generate_tuple_map_overloads.py
1751 @overload
1752 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningUpdate[_T0]: ...
1754 @overload
1755 def returning(
1756 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1757 ) -> ReturningUpdate[_T0, _T1]: ...
1759 @overload
1760 def returning(
1761 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1762 ) -> ReturningUpdate[_T0, _T1, _T2]: ...
1764 @overload
1765 def returning(
1766 self,
1767 __ent0: _TCCA[_T0],
1768 __ent1: _TCCA[_T1],
1769 __ent2: _TCCA[_T2],
1770 __ent3: _TCCA[_T3],
1771 /,
1772 ) -> ReturningUpdate[_T0, _T1, _T2, _T3]: ...
1774 @overload
1775 def returning(
1776 self,
1777 __ent0: _TCCA[_T0],
1778 __ent1: _TCCA[_T1],
1779 __ent2: _TCCA[_T2],
1780 __ent3: _TCCA[_T3],
1781 __ent4: _TCCA[_T4],
1782 /,
1783 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4]: ...
1785 @overload
1786 def returning(
1787 self,
1788 __ent0: _TCCA[_T0],
1789 __ent1: _TCCA[_T1],
1790 __ent2: _TCCA[_T2],
1791 __ent3: _TCCA[_T3],
1792 __ent4: _TCCA[_T4],
1793 __ent5: _TCCA[_T5],
1794 /,
1795 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1797 @overload
1798 def returning(
1799 self,
1800 __ent0: _TCCA[_T0],
1801 __ent1: _TCCA[_T1],
1802 __ent2: _TCCA[_T2],
1803 __ent3: _TCCA[_T3],
1804 __ent4: _TCCA[_T4],
1805 __ent5: _TCCA[_T5],
1806 __ent6: _TCCA[_T6],
1807 /,
1808 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1810 @overload
1811 def returning(
1812 self,
1813 __ent0: _TCCA[_T0],
1814 __ent1: _TCCA[_T1],
1815 __ent2: _TCCA[_T2],
1816 __ent3: _TCCA[_T3],
1817 __ent4: _TCCA[_T4],
1818 __ent5: _TCCA[_T5],
1819 __ent6: _TCCA[_T6],
1820 __ent7: _TCCA[_T7],
1821 /,
1822 *entities: _ColumnsClauseArgument[Any],
1823 ) -> ReturningUpdate[
1824 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1825 ]: ...
1827 # END OVERLOADED FUNCTIONS self.returning
1829 @overload
1830 def returning(
1831 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1832 ) -> ReturningUpdate[Any]: ...
1834 def returning(
1835 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1836 ) -> ReturningUpdate[Any]: ...
1839class ReturningUpdate(Update, TypedReturnsRows[Unpack[_Ts]]):
1840 """Typing-only class that establishes a generic type form of
1841 :class:`.Update` which tracks returned column types.
1843 This datatype is delivered when calling the
1844 :meth:`.Update.returning` method.
1846 .. versionadded:: 2.0
1848 """
1851class Delete(
1852 DMLWhereBase, UpdateBase, HasSyntaxExtensions[Literal["post_criteria"]]
1853):
1854 """Represent a DELETE construct.
1856 The :class:`_expression.Delete` object is created using the
1857 :func:`_expression.delete()` function.
1859 Available extension points:
1861 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1863 """
1865 __visit_name__ = "delete"
1867 is_delete = True
1869 _traverse_internals = (
1870 [
1871 ("table", InternalTraversal.dp_clauseelement),
1872 ("_extra_froms", InternalTraversal.dp_clauseelement_tuple),
1873 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1874 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1875 ("_hints", InternalTraversal.dp_table_hint_list),
1876 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1877 ]
1878 + UpdateBase._return_defaults_traverse_internals
1879 + HasPrefixes._has_prefixes_traverse_internals
1880 + DialectKWArgs._dialect_kwargs_traverse_internals
1881 + ExecutableStatement._executable_traverse_internals
1882 + HasCTE._has_ctes_traverse_internals
1883 )
1885 _position_map = util.immutabledict(
1886 {"post_criteria": "_post_criteria_clause"}
1887 )
1889 _extra_froms: Tuple[FromClause, ...] = ()
1891 def __init__(self, table: _DMLTableArgument):
1892 self.table = coercions.expect(
1893 roles.DMLTableRole, table, apply_propagate_attrs=self
1894 )
1896 @_generative
1897 def using(self, *froms: _FromClauseArgument) -> Self:
1898 r"""Add one or more explicit ``USING`` expressions to this DELETE.
1900 This method may be used for backend-specific multiple-table DELETE
1901 forms where the secondary FROM expression needs to be stated
1902 explicitly, such as MySQL's ``DELETE FROM table USING <join>`` form.
1904 .. versionadded:: 2.1
1906 .. seealso::
1908 :ref:`tutorial_multi_table_deletes`
1910 """
1912 self._extra_froms += tuple(
1913 coercions.expect(
1914 roles.FromClauseRole, from_, apply_propagate_attrs=self
1915 )
1916 for from_ in froms
1917 )
1918 return self
1920 def _apply_syntax_extension_to_self(
1921 self, extension: SyntaxExtension
1922 ) -> None:
1923 extension.apply_to_delete(self)
1925 if TYPE_CHECKING:
1926 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1928 # code within this block is **programmatically,
1929 # statically generated** by tools/generate_tuple_map_overloads.py
1931 @overload
1932 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningDelete[_T0]: ...
1934 @overload
1935 def returning(
1936 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1937 ) -> ReturningDelete[_T0, _T1]: ...
1939 @overload
1940 def returning(
1941 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1942 ) -> ReturningDelete[_T0, _T1, _T2]: ...
1944 @overload
1945 def returning(
1946 self,
1947 __ent0: _TCCA[_T0],
1948 __ent1: _TCCA[_T1],
1949 __ent2: _TCCA[_T2],
1950 __ent3: _TCCA[_T3],
1951 /,
1952 ) -> ReturningDelete[_T0, _T1, _T2, _T3]: ...
1954 @overload
1955 def returning(
1956 self,
1957 __ent0: _TCCA[_T0],
1958 __ent1: _TCCA[_T1],
1959 __ent2: _TCCA[_T2],
1960 __ent3: _TCCA[_T3],
1961 __ent4: _TCCA[_T4],
1962 /,
1963 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4]: ...
1965 @overload
1966 def returning(
1967 self,
1968 __ent0: _TCCA[_T0],
1969 __ent1: _TCCA[_T1],
1970 __ent2: _TCCA[_T2],
1971 __ent3: _TCCA[_T3],
1972 __ent4: _TCCA[_T4],
1973 __ent5: _TCCA[_T5],
1974 /,
1975 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1977 @overload
1978 def returning(
1979 self,
1980 __ent0: _TCCA[_T0],
1981 __ent1: _TCCA[_T1],
1982 __ent2: _TCCA[_T2],
1983 __ent3: _TCCA[_T3],
1984 __ent4: _TCCA[_T4],
1985 __ent5: _TCCA[_T5],
1986 __ent6: _TCCA[_T6],
1987 /,
1988 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1990 @overload
1991 def returning(
1992 self,
1993 __ent0: _TCCA[_T0],
1994 __ent1: _TCCA[_T1],
1995 __ent2: _TCCA[_T2],
1996 __ent3: _TCCA[_T3],
1997 __ent4: _TCCA[_T4],
1998 __ent5: _TCCA[_T5],
1999 __ent6: _TCCA[_T6],
2000 __ent7: _TCCA[_T7],
2001 /,
2002 *entities: _ColumnsClauseArgument[Any],
2003 ) -> ReturningDelete[
2004 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
2005 ]: ...
2007 # END OVERLOADED FUNCTIONS self.returning
2009 @overload
2010 def returning(
2011 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
2012 ) -> ReturningDelete[Unpack[TupleAny]]: ...
2014 def returning(
2015 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
2016 ) -> ReturningDelete[Unpack[TupleAny]]: ...
2019class ReturningDelete(Update, TypedReturnsRows[Unpack[_Ts]]):
2020 """Typing-only class that establishes a generic type form of
2021 :class:`.Delete` which tracks returned column types.
2023 This datatype is delivered when calling the
2024 :meth:`.Delete.returning` method.
2026 .. versionadded:: 2.0
2028 """