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"""
12from __future__ import annotations
14import collections.abc as collections_abc
15import operator
16from typing import Any
17from typing import cast
18from typing import Dict
19from typing import Iterable
20from typing import List
21from typing import Literal
22from typing import MutableMapping
23from typing import NoReturn
24from typing import Optional
25from typing import overload
26from typing import Sequence
27from typing import Set
28from typing import Tuple
29from typing import Type
30from typing import TYPE_CHECKING
31from typing import TypeGuard
32from typing import TypeVar
33from typing import Union
35from . import coercions
36from . import roles
37from . import util as sql_util
38from ._typing import _unexpected_kw
39from ._typing import is_column_element
40from ._typing import is_named_from_clause
41from .base import _entity_namespace_key_search_all
42from .base import _exclusive_against
43from .base import _from_objects
44from .base import _generative
45from .base import _select_iterables
46from .base import ColumnSet
47from .base import CompileState
48from .base import DialectKWArgs
49from .base import Executable
50from .base import ExecutableStatement
51from .base import Generative
52from .base import HasCompileState
53from .base import HasSyntaxExtensions
54from .base import SyntaxExtension
55from .base import WriteableColumnCollection
56from .elements import BooleanClauseList
57from .elements import ClauseElement
58from .elements import ColumnClause
59from .elements import ColumnElement
60from .elements import Null
61from .selectable import Alias
62from .selectable import ExecutableReturnsRows
63from .selectable import FromClause
64from .selectable import HasCTE
65from .selectable import HasPrefixes
66from .selectable import Join
67from .selectable import SelectLabelStyle
68from .selectable import TableClause
69from .selectable import TypedReturnsRows
70from .sqltypes import NullType
71from .visitors import InternalTraversal
72from .. import exc
73from .. import util
74from ..util.typing import Self
75from ..util.typing import TupleAny
76from ..util.typing import TypeVarTuple
77from ..util.typing import Unpack
80if TYPE_CHECKING:
81 from ._typing import _ColumnExpressionArgument
82 from ._typing import _ColumnsClauseArgument
83 from ._typing import _DMLColumnArgument
84 from ._typing import _DMLColumnKeyMapping
85 from ._typing import _DMLTableArgument
86 from ._typing import _T0 # noqa
87 from ._typing import _T1 # noqa
88 from ._typing import _T2 # noqa
89 from ._typing import _T3 # noqa
90 from ._typing import _T4 # noqa
91 from ._typing import _T5 # noqa
92 from ._typing import _T6 # noqa
93 from ._typing import _T7 # noqa
94 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa
95 from .base import ReadOnlyColumnCollection
96 from .compiler import SQLCompiler
97 from .elements import KeyedColumnElement
98 from .selectable import _ColumnsClauseElement
99 from .selectable import _SelectIterable
100 from .selectable import Select
101 from .selectable import Selectable
103 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ...
105 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ...
107 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ...
109else:
110 isupdate = operator.attrgetter("isupdate")
111 isdelete = operator.attrgetter("isdelete")
112 isinsert = operator.attrgetter("isinsert")
115_T = TypeVar("_T", bound=Any)
116_Ts = TypeVarTuple("_Ts")
118_DMLColumnElement = Union[str, ColumnClause[Any]]
119_DMLTableElement = Union[TableClause, Alias, Join]
122class DMLState(CompileState):
123 _no_parameters = True
124 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None
125 _multi_parameters: Optional[
126 List[MutableMapping[_DMLColumnElement, Any]]
127 ] = None
128 _maintain_values_ordering: bool = False
129 _primary_table: FromClause
130 _supports_implicit_returning = True
132 isupdate = False
133 isdelete = False
134 isinsert = False
136 statement: UpdateBase
138 def __init__(
139 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any
140 ):
141 raise NotImplementedError()
143 @classmethod
144 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]:
145 return {
146 "name": (
147 statement.table.name
148 if is_named_from_clause(statement.table)
149 else None
150 ),
151 "table": statement.table,
152 }
154 @classmethod
155 def get_returning_column_descriptions(
156 cls, statement: UpdateBase
157 ) -> List[Dict[str, Any]]:
158 return [
159 {
160 "name": c.key,
161 "type": c.type,
162 "expr": c,
163 }
164 for c in statement._all_selected_columns
165 ]
167 @property
168 def dml_table(self) -> _DMLTableElement:
169 return self.statement.table
171 if TYPE_CHECKING:
173 @classmethod
174 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ...
176 @classmethod
177 def _get_multi_crud_kv_pairs(
178 cls,
179 statement: UpdateBase,
180 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]],
181 ) -> List[Dict[_DMLColumnElement, Any]]:
182 return [
183 {
184 coercions.expect(roles.DMLColumnRole, k): v
185 for k, v in mapping.items()
186 }
187 for mapping in multi_kv_iterator
188 ]
190 @classmethod
191 def _get_crud_kv_pairs(
192 cls,
193 statement: UpdateBase,
194 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]],
195 needs_to_be_cacheable: bool,
196 ) -> List[Tuple[_DMLColumnElement, Any]]:
197 return [
198 (
199 coercions.expect(roles.DMLColumnRole, k),
200 (
201 v
202 if not needs_to_be_cacheable
203 else coercions.expect(
204 roles.ExpressionElementRole,
205 v,
206 type_=NullType(),
207 is_crud=True,
208 )
209 ),
210 )
211 for k, v in kv_iterator
212 ]
214 def _make_extra_froms(
215 self, statement: DMLWhereBase
216 ) -> Tuple[FromClause, List[FromClause]]:
217 froms: List[FromClause] = []
219 all_tables = list(sql_util.tables_from_leftmost(statement.table))
220 primary_table = all_tables[0]
221 seen = {primary_table}
223 consider = statement._where_criteria
224 if self._dict_parameters:
225 consider += tuple(self._dict_parameters.values())
227 for crit in consider:
228 for item in _from_objects(crit):
229 if not seen.intersection(item._cloned_set):
230 froms.append(item)
231 seen.update(item._cloned_set)
233 froms.extend(all_tables[1:])
234 return primary_table, froms
236 def _process_values(self, statement: ValuesBase) -> None:
237 if self._no_parameters:
238 self._dict_parameters = statement._values
239 self._no_parameters = False
241 def _process_select_values(self, statement: ValuesBase) -> None:
242 assert statement._select_names is not None
243 parameters: MutableMapping[_DMLColumnElement, Any] = {
244 name: Null() for name in statement._select_names
245 }
247 if self._no_parameters:
248 self._no_parameters = False
249 self._dict_parameters = parameters
250 else:
251 # this condition normally not reachable as the Insert
252 # does not allow this construction to occur
253 assert False, "This statement already has parameters"
255 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn:
256 raise exc.InvalidRequestError(
257 "%s construct does not support "
258 "multiple parameter sets." % statement.__visit_name__.upper()
259 )
261 def _cant_mix_formats_error(self) -> NoReturn:
262 raise exc.InvalidRequestError(
263 "Can't mix single and multiple VALUES "
264 "formats in one INSERT statement; one style appends to a "
265 "list while the other replaces values, so the intent is "
266 "ambiguous."
267 )
270@CompileState.plugin_for("default", "insert")
271class InsertDMLState(DMLState):
272 isinsert = True
274 include_table_with_column_exprs = False
276 _has_multi_parameters = False
278 def __init__(
279 self,
280 statement: Insert,
281 compiler: SQLCompiler,
282 disable_implicit_returning: bool = False,
283 **kw: Any,
284 ):
285 self.statement = statement
286 self._primary_table = statement.table
288 if disable_implicit_returning:
289 self._supports_implicit_returning = False
291 self.isinsert = True
292 if statement._select_names:
293 self._process_select_values(statement)
294 if statement._values is not None:
295 self._process_values(statement)
296 if statement._multi_values:
297 self._process_multi_values(statement)
299 @util.memoized_property
300 def _insert_col_keys(self) -> List[str]:
301 # this is also done in crud.py -> _key_getters_for_crud_column
302 return [
303 coercions.expect(roles.DMLColumnRole, col, as_key=True)
304 for col in self._dict_parameters or ()
305 ]
307 def _process_values(self, statement: ValuesBase) -> None:
308 if self._no_parameters:
309 self._has_multi_parameters = False
310 self._dict_parameters = statement._values
311 self._no_parameters = False
312 elif self._has_multi_parameters:
313 self._cant_mix_formats_error()
315 def _process_multi_values(self, statement: ValuesBase) -> None:
316 for parameters in statement._multi_values:
317 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [
318 (
319 {
320 c.key: value
321 for c, value in zip(statement.table.c, parameter_set)
322 }
323 if isinstance(parameter_set, collections_abc.Sequence)
324 else parameter_set
325 )
326 for parameter_set in parameters
327 ]
329 if self._no_parameters:
330 self._no_parameters = False
331 self._has_multi_parameters = True
332 self._multi_parameters = multi_parameters
333 self._dict_parameters = self._multi_parameters[0]
334 elif not self._has_multi_parameters:
335 self._cant_mix_formats_error()
336 else:
337 assert self._multi_parameters
338 self._multi_parameters.extend(multi_parameters)
341@CompileState.plugin_for("default", "update")
342class UpdateDMLState(DMLState):
343 isupdate = True
345 include_table_with_column_exprs = False
347 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any):
348 self.statement = statement
350 self.isupdate = True
351 if statement._maintain_values_ordering:
352 self._process_ordered_values(statement)
353 elif statement._values is not None:
354 self._process_values(statement)
355 elif statement._multi_values:
356 self._no_multi_values_supported(statement)
357 t, ef = self._make_extra_froms(statement)
358 self._primary_table = t
359 self._extra_froms = ef
361 self.is_multitable = mt = ef
362 self.include_table_with_column_exprs = bool(
363 mt and compiler.render_table_with_column_in_update_from
364 )
366 def _process_ordered_values(self, statement: ValuesBase) -> None:
367 parameters = statement._values
368 if self._no_parameters:
369 self._no_parameters = False
370 assert parameters is not None
371 self._dict_parameters = dict(parameters)
372 self._maintain_values_ordering = True
373 else:
374 raise exc.InvalidRequestError(
375 "Can only invoke ordered_values() once, and not mixed "
376 "with any other values() call"
377 )
380@CompileState.plugin_for("default", "delete")
381class DeleteDMLState(DMLState):
382 isdelete = True
384 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any):
385 self.statement = statement
387 self.isdelete = True
388 t, ef = self._make_extra_froms(statement)
389 self._primary_table = t
390 self._extra_froms = ef
391 self.is_multitable = ef
394class UpdateBase(
395 roles.DMLRole,
396 HasCTE,
397 HasCompileState,
398 DialectKWArgs,
399 HasPrefixes,
400 Generative,
401 ExecutableReturnsRows,
402 ClauseElement,
403):
404 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
406 __visit_name__ = "update_base"
408 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = (
409 util.EMPTY_DICT
410 )
411 named_with_column = False
413 _label_style: SelectLabelStyle = (
414 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY
415 )
416 table: _DMLTableElement
418 _return_defaults = False
419 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = (
420 None
421 )
422 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None
423 _returning: Tuple[_ColumnsClauseElement, ...] = ()
425 is_dml = True
427 def _generate_fromclause_column_proxies(
428 self,
429 fromclause: FromClause,
430 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],
431 primary_key: ColumnSet,
432 foreign_keys: Set[KeyedColumnElement[Any]],
433 ) -> None:
434 prox = [
435 c._make_proxy(
436 fromclause,
437 key=proxy_key,
438 name=required_label_name,
439 name_is_truncatable=True,
440 primary_key=primary_key,
441 foreign_keys=foreign_keys,
442 )
443 for (
444 required_label_name,
445 proxy_key,
446 fallback_label_name,
447 c,
448 repeated,
449 ) in (self._generate_columns_plus_names(False))
450 if is_column_element(c)
451 ]
453 columns._populate_separate_keys(prox)
455 def params(self, *arg: Any, **kw: Any) -> NoReturn:
456 """Set the parameters for the statement.
458 This method raises ``NotImplementedError`` on the base class,
459 and is overridden by :class:`.ValuesBase` to provide the
460 SET/VALUES clause of UPDATE and INSERT.
462 """
463 raise NotImplementedError(
464 "params() is not supported for INSERT/UPDATE/DELETE statements."
465 " To set the values for an INSERT or UPDATE statement, use"
466 " stmt.values(**parameters)."
467 )
469 @_generative
470 def with_dialect_options(self, **opt: Any) -> Self:
471 """Add dialect options to this INSERT/UPDATE/DELETE object.
473 e.g.::
475 upd = table.update().dialect_options(mysql_limit=10)
477 .. versionadded:: 1.4 - this method supersedes the dialect options
478 associated with the constructor.
481 """
482 self._validate_dialect_kwargs(opt)
483 return self
485 @_generative
486 def return_defaults(
487 self,
488 *cols: _DMLColumnArgument,
489 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None,
490 sort_by_parameter_order: bool = False,
491 ) -> Self:
492 """Make use of a :term:`RETURNING` clause for the purpose
493 of fetching server-side expressions and defaults, for supporting
494 backends only.
496 .. deepalchemy::
498 The :meth:`.UpdateBase.return_defaults` method is used by the ORM
499 for its internal work in fetching newly generated primary key
500 and server default values, in particular to provide the underlying
501 implementation of the :paramref:`_orm.Mapper.eager_defaults`
502 ORM feature as well as to allow RETURNING support with bulk
503 ORM inserts. Its behavior is fairly idiosyncratic
504 and is not really intended for general use. End users should
505 stick with using :meth:`.UpdateBase.returning` in order to
506 add RETURNING clauses to their INSERT, UPDATE and DELETE
507 statements.
509 Normally, a single row INSERT statement will automatically populate the
510 :attr:`.CursorResult.inserted_primary_key` attribute when executed,
511 which stores the primary key of the row that was just inserted in the
512 form of a :class:`.Row` object with column names as named tuple keys
513 (and the :attr:`.Row._mapping` view fully populated as well). The
514 dialect in use chooses the strategy to use in order to populate this
515 data; if it was generated using server-side defaults and / or SQL
516 expressions, dialect-specific approaches such as ``cursor.lastrowid``
517 or ``RETURNING`` are typically used to acquire the new primary key
518 value.
520 However, when the statement is modified by calling
521 :meth:`.UpdateBase.return_defaults` before executing the statement,
522 additional behaviors take place **only** for backends that support
523 RETURNING and for :class:`.Table` objects that maintain the
524 :paramref:`.Table.implicit_returning` parameter at its default value of
525 ``True``. In these cases, when the :class:`.CursorResult` is returned
526 from the statement's execution, not only will
527 :attr:`.CursorResult.inserted_primary_key` be populated as always, the
528 :attr:`.CursorResult.returned_defaults` attribute will also be
529 populated with a :class:`.Row` named-tuple representing the full range
530 of server generated
531 values from that single row, including values for any columns that
532 specify :paramref:`_schema.Column.server_default` or which make use of
533 :paramref:`_schema.Column.default` using a SQL expression.
535 When invoking INSERT statements with multiple rows using
536 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the
537 :meth:`.UpdateBase.return_defaults` modifier will have the effect of
538 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and
539 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being
540 fully populated with lists of :class:`.Row` objects representing newly
541 inserted primary key values as well as newly inserted server generated
542 values for each row inserted. The
543 :attr:`.CursorResult.inserted_primary_key` and
544 :attr:`.CursorResult.returned_defaults` attributes will also continue
545 to be populated with the first row of these two collections.
547 If the backend does not support RETURNING or the :class:`.Table` in use
548 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING
549 clause is added and no additional data is fetched, however the
550 INSERT, UPDATE or DELETE statement proceeds normally.
552 E.g.::
554 stmt = table.insert().values(data="newdata").return_defaults()
556 result = connection.execute(stmt)
558 server_created_at = result.returned_defaults["created_at"]
560 When used against an UPDATE statement
561 :meth:`.UpdateBase.return_defaults` instead looks for columns that
562 include :paramref:`_schema.Column.onupdate` or
563 :paramref:`_schema.Column.server_onupdate` parameters assigned, when
564 constructing the columns that will be included in the RETURNING clause
565 by default if explicit columns were not specified. When used against a
566 DELETE statement, no columns are included in RETURNING by default, they
567 instead must be specified explicitly as there are no columns that
568 normally change values when a DELETE statement proceeds.
570 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported
571 for DELETE statements also and has been moved from
572 :class:`.ValuesBase` to :class:`.UpdateBase`.
574 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive
575 against the :meth:`.UpdateBase.returning` method and errors will be
576 raised during the SQL compilation process if both are used at the same
577 time on one statement. The RETURNING clause of the INSERT, UPDATE or
578 DELETE statement is therefore controlled by only one of these methods
579 at a time.
581 The :meth:`.UpdateBase.return_defaults` method differs from
582 :meth:`.UpdateBase.returning` in these ways:
584 1. :meth:`.UpdateBase.return_defaults` method causes the
585 :attr:`.CursorResult.returned_defaults` collection to be populated
586 with the first row from the RETURNING result. This attribute is not
587 populated when using :meth:`.UpdateBase.returning`.
589 2. :meth:`.UpdateBase.return_defaults` is compatible with existing
590 logic used to fetch auto-generated primary key values that are then
591 populated into the :attr:`.CursorResult.inserted_primary_key`
592 attribute. By contrast, using :meth:`.UpdateBase.returning` will
593 have the effect of the :attr:`.CursorResult.inserted_primary_key`
594 attribute being left unpopulated.
596 3. :meth:`.UpdateBase.return_defaults` can be called against any
597 backend. Backends that don't support RETURNING will skip the usage
598 of the feature, rather than raising an exception, *unless*
599 ``supplemental_cols`` is passed. The return value
600 of :attr:`_engine.CursorResult.returned_defaults` will be ``None``
601 for backends that don't support RETURNING or for which the target
602 :class:`.Table` sets :paramref:`.Table.implicit_returning` to
603 ``False``.
605 4. An INSERT statement invoked with executemany() is supported if the
606 backend database driver supports the
607 :ref:`insertmanyvalues <engine_insertmanyvalues>`
608 feature which is now supported by most SQLAlchemy-included backends.
609 When executemany is used, the
610 :attr:`_engine.CursorResult.returned_defaults_rows` and
611 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors
612 will return the inserted defaults and primary keys.
614 .. versionadded:: 1.4 Added
615 :attr:`_engine.CursorResult.returned_defaults_rows` and
616 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors.
617 In version 2.0, the underlying implementation which fetches and
618 populates the data for these attributes was generalized to be
619 supported by most backends, whereas in 1.4 they were only
620 supported by the ``psycopg2`` driver.
623 :param cols: optional list of column key names or
624 :class:`_schema.Column` that acts as a filter for those columns that
625 will be fetched.
626 :param supplemental_cols: optional list of RETURNING expressions,
627 in the same form as one would pass to the
628 :meth:`.UpdateBase.returning` method. When present, the additional
629 columns will be included in the RETURNING clause, and the
630 :class:`.CursorResult` object will be "rewound" when returned, so
631 that methods like :meth:`.CursorResult.all` will return new rows
632 mostly as though the statement used :meth:`.UpdateBase.returning`
633 directly. However, unlike when using :meth:`.UpdateBase.returning`
634 directly, the **order of the columns is undefined**, so can only be
635 targeted using names or :attr:`.Row._mapping` keys; they cannot
636 reliably be targeted positionally.
638 .. versionadded:: 2.0
640 :param sort_by_parameter_order: for a batch INSERT that is being
641 executed against multiple parameter sets, organize the results of
642 RETURNING so that the returned rows correspond to the order of
643 parameter sets passed in. This applies only to an :term:`executemany`
644 execution for supporting dialects and typically makes use of the
645 :term:`insertmanyvalues` feature.
647 .. versionadded:: 2.0.10
649 .. seealso::
651 :ref:`engine_insertmanyvalues_returning_order` - background on
652 sorting of RETURNING rows for bulk INSERT
654 .. seealso::
656 :meth:`.UpdateBase.returning`
658 :attr:`_engine.CursorResult.returned_defaults`
660 :attr:`_engine.CursorResult.returned_defaults_rows`
662 :attr:`_engine.CursorResult.inserted_primary_key`
664 :attr:`_engine.CursorResult.inserted_primary_key_rows`
666 """
668 if self._return_defaults:
669 # note _return_defaults_columns = () means return all columns,
670 # so if we have been here before, only update collection if there
671 # are columns in the collection
672 if self._return_defaults_columns and cols:
673 self._return_defaults_columns = tuple(
674 util.OrderedSet(self._return_defaults_columns).union(
675 coercions.expect(roles.ColumnsClauseRole, c)
676 for c in cols
677 )
678 )
679 else:
680 # set for all columns
681 self._return_defaults_columns = ()
682 else:
683 self._return_defaults_columns = tuple(
684 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
685 )
686 self._return_defaults = True
687 if sort_by_parameter_order:
688 if not self.is_insert:
689 raise exc.ArgumentError(
690 "The 'sort_by_parameter_order' argument to "
691 "return_defaults() only applies to INSERT statements"
692 )
693 self._sort_by_parameter_order = True
694 if supplemental_cols:
695 # uniquifying while also maintaining order (the maintain of order
696 # is for test suites but also for vertical splicing
697 supplemental_col_tup = (
698 coercions.expect(roles.ColumnsClauseRole, c)
699 for c in supplemental_cols
700 )
702 if self._supplemental_returning is None:
703 self._supplemental_returning = tuple(
704 util.unique_list(supplemental_col_tup)
705 )
706 else:
707 self._supplemental_returning = tuple(
708 util.unique_list(
709 self._supplemental_returning
710 + tuple(supplemental_col_tup)
711 )
712 )
714 return self
716 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
717 """Return ``True`` if this :class:`.ReturnsRows` is
718 'derived' from the given :class:`.FromClause`.
720 Since these are DMLs, we dont want such statements ever being adapted
721 so we return False for derives.
723 """
724 return False
726 @_generative
727 def returning(
728 self,
729 *cols: _ColumnsClauseArgument[Any],
730 sort_by_parameter_order: bool = False,
731 **__kw: Any,
732 ) -> UpdateBase:
733 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
735 e.g.:
737 .. sourcecode:: pycon+sql
739 >>> stmt = (
740 ... table.update()
741 ... .where(table.c.data == "value")
742 ... .values(status="X")
743 ... .returning(table.c.server_flag, table.c.updated_timestamp)
744 ... )
745 >>> print(stmt)
746 {printsql}UPDATE some_table SET status=:status
747 WHERE some_table.data = :data_1
748 RETURNING some_table.server_flag, some_table.updated_timestamp
750 The method may be invoked multiple times to add new entries to the
751 list of expressions to be returned.
753 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to
754 add new entries to the list of expressions to be returned.
756 The given collection of column expressions should be derived from the
757 table that is the target of the INSERT, UPDATE, or DELETE. While
758 :class:`_schema.Column` objects are typical, the elements can also be
759 expressions:
761 .. sourcecode:: pycon+sql
763 >>> stmt = table.insert().returning(
764 ... (table.c.first_name + " " + table.c.last_name).label("fullname")
765 ... )
766 >>> print(stmt)
767 {printsql}INSERT INTO some_table (first_name, last_name)
768 VALUES (:first_name, :last_name)
769 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname
771 Upon compilation, a RETURNING clause, or database equivalent,
772 will be rendered within the statement. For INSERT and UPDATE,
773 the values are the newly inserted/updated values. For DELETE,
774 the values are those of the rows which were deleted.
776 Upon execution, the values of the columns to be returned are made
777 available via the result set and can be iterated using
778 :meth:`_engine.CursorResult.fetchone` and similar.
779 For DBAPIs which do not
780 natively support returning values (i.e. cx_oracle), SQLAlchemy will
781 approximate this behavior at the result level so that a reasonable
782 amount of behavioral neutrality is provided.
784 Note that not all databases/DBAPIs
785 support RETURNING. For those backends with no support,
786 an exception is raised upon compilation and/or execution.
787 For those who do support it, the functionality across backends
788 varies greatly, including restrictions on executemany()
789 and other statements which return multiple rows. Please
790 read the documentation notes for the database in use in
791 order to determine the availability of RETURNING.
793 :param \*cols: series of columns, SQL expressions, or whole tables
794 entities to be returned.
795 :param sort_by_parameter_order: for a batch INSERT that is being
796 executed against multiple parameter sets, organize the results of
797 RETURNING so that the returned rows correspond to the order of
798 parameter sets passed in. This applies only to an :term:`executemany`
799 execution for supporting dialects and typically makes use of the
800 :term:`insertmanyvalues` feature.
802 .. versionadded:: 2.0.10
804 .. seealso::
806 :ref:`engine_insertmanyvalues_returning_order` - background on
807 sorting of RETURNING rows for bulk INSERT (Core level discussion)
809 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of
810 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion)
812 .. seealso::
814 :meth:`.UpdateBase.return_defaults` - an alternative method tailored
815 towards efficient fetching of server-side defaults and triggers
816 for single-row INSERTs or UPDATEs.
818 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`
820 """ # noqa: E501
821 if __kw:
822 raise _unexpected_kw("UpdateBase.returning()", __kw)
823 if self._return_defaults:
824 raise exc.InvalidRequestError(
825 "return_defaults() is already configured on this statement"
826 )
827 self._returning += tuple(
828 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
829 )
830 if sort_by_parameter_order:
831 if not self.is_insert:
832 raise exc.ArgumentError(
833 "The 'sort_by_parameter_order' argument to returning() "
834 "only applies to INSERT statements"
835 )
836 self._sort_by_parameter_order = True
837 return self
839 def corresponding_column(
840 self, column: KeyedColumnElement[Any], require_embedded: bool = False
841 ) -> Optional[ColumnElement[Any]]:
842 return self.exported_columns.corresponding_column(
843 column, require_embedded=require_embedded
844 )
846 @util.ro_memoized_property
847 def _all_selected_columns(self) -> _SelectIterable:
848 return [c for c in _select_iterables(self._returning)]
850 @util.ro_memoized_property
851 def exported_columns(
852 self,
853 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]:
854 """Return the RETURNING columns as a column collection for this
855 statement.
857 .. versionadded:: 1.4
859 """
860 return WriteableColumnCollection(
861 (c.key, c)
862 for c in self._all_selected_columns
863 if is_column_element(c)
864 ).as_readonly()
866 @_generative
867 def with_hint(
868 self,
869 text: str,
870 selectable: Optional[_DMLTableArgument] = None,
871 dialect_name: str = "*",
872 ) -> Self:
873 """Add a table hint for a single table to this
874 INSERT/UPDATE/DELETE statement.
876 .. note::
878 :meth:`.UpdateBase.with_hint` currently applies only to
879 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
880 :meth:`.UpdateBase.prefix_with`.
882 The text of the hint is rendered in the appropriate
883 location for the database backend in use, relative
884 to the :class:`_schema.Table` that is the subject of this
885 statement, or optionally to that of the given
886 :class:`_schema.Table` passed as the ``selectable`` argument.
888 The ``dialect_name`` option will limit the rendering of a particular
889 hint to a particular backend. Such as, to add a hint
890 that only takes effect for SQL Server::
892 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
894 :param text: Text of the hint.
895 :param selectable: optional :class:`_schema.Table` that specifies
896 an element of the FROM clause within an UPDATE or DELETE
897 to be the subject of the hint - applies only to certain backends.
898 :param dialect_name: defaults to ``*``, if specified as the name
899 of a particular dialect, will apply these hints only when
900 that dialect is in use.
901 """
902 if selectable is None:
903 selectable = self.table
904 else:
905 selectable = coercions.expect(roles.DMLTableRole, selectable)
906 self._hints = self._hints.union({(selectable, dialect_name): text})
907 return self
909 @property
910 def entity_description(self) -> Dict[str, Any]:
911 """Return a :term:`plugin-enabled` description of the table and/or
912 entity which this DML construct is operating against.
914 This attribute is generally useful when using the ORM, as an
915 extended structure which includes information about mapped
916 entities is returned. The section :ref:`queryguide_inspection`
917 contains more background.
919 For a Core statement, the structure returned by this accessor
920 is derived from the :attr:`.UpdateBase.table` attribute, and
921 refers to the :class:`.Table` being inserted, updated, or deleted::
923 >>> stmt = insert(user_table)
924 >>> stmt.entity_description
925 {
926 "name": "user_table",
927 "table": Table("user_table", ...)
928 }
930 .. versionadded:: 1.4.33
932 .. seealso::
934 :attr:`.UpdateBase.returning_column_descriptions`
936 :attr:`.Select.column_descriptions` - entity information for
937 a :func:`.select` construct
939 :ref:`queryguide_inspection` - ORM background
941 """
942 meth = DMLState.get_plugin_class(self).get_entity_description
943 return meth(self)
945 @property
946 def returning_column_descriptions(self) -> List[Dict[str, Any]]:
947 """Return a :term:`plugin-enabled` description of the columns
948 which this DML construct is RETURNING against, in other words
949 the expressions established as part of :meth:`.UpdateBase.returning`.
951 This attribute is generally useful when using the ORM, as an
952 extended structure which includes information about mapped
953 entities is returned. The section :ref:`queryguide_inspection`
954 contains more background.
956 For a Core statement, the structure returned by this accessor is
957 derived from the same objects that are returned by the
958 :attr:`.UpdateBase.exported_columns` accessor::
960 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name)
961 >>> stmt.entity_description
962 [
963 {
964 "name": "id",
965 "type": Integer,
966 "expr": Column("id", Integer(), table=<user>, ...)
967 },
968 {
969 "name": "name",
970 "type": String(),
971 "expr": Column("name", String(), table=<user>, ...)
972 },
973 ]
975 .. versionadded:: 1.4.33
977 .. seealso::
979 :attr:`.UpdateBase.entity_description`
981 :attr:`.Select.column_descriptions` - entity information for
982 a :func:`.select` construct
984 :ref:`queryguide_inspection` - ORM background
986 """ # noqa: E501
987 meth = DMLState.get_plugin_class(
988 self
989 ).get_returning_column_descriptions
990 return meth(self)
993class ValuesBase(UpdateBase):
994 """Supplies support for :meth:`.ValuesBase.values` to
995 INSERT and UPDATE constructs."""
997 __visit_name__ = "values_base"
999 _supports_multi_parameters = False
1001 select: Optional[Select[Unpack[TupleAny]]] = None
1002 """SELECT statement for INSERT .. FROM SELECT"""
1004 _post_values_clause: Optional[ClauseElement] = None
1005 """used by extensions to Insert etc. to add additional syntactical
1006 constructs, e.g. ON CONFLICT etc."""
1008 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None
1009 _multi_values: Tuple[
1010 Union[
1011 Sequence[Dict[_DMLColumnElement, Any]],
1012 Sequence[Sequence[Any]],
1013 ],
1014 ...,
1015 ] = ()
1017 _maintain_values_ordering: bool = False
1019 _select_names: Optional[List[str]] = None
1020 _inline: bool = False
1022 def __init__(self, table: _DMLTableArgument):
1023 self.table = coercions.expect(
1024 roles.DMLTableRole, table, apply_propagate_attrs=self
1025 )
1027 @_generative
1028 @_exclusive_against(
1029 "_select_names",
1030 "_maintain_values_ordering",
1031 msgs={
1032 "_select_names": "This construct already inserts from a SELECT",
1033 "_maintain_values_ordering": "This statement already has ordered "
1034 "values present",
1035 },
1036 defaults={"_maintain_values_ordering": False},
1037 )
1038 def values(
1039 self,
1040 *args: Union[
1041 _DMLColumnKeyMapping[Any],
1042 Sequence[Any],
1043 ],
1044 **kwargs: Any,
1045 ) -> Self:
1046 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
1047 clause for an UPDATE.
1049 Note that the :class:`_expression.Insert` and
1050 :class:`_expression.Update`
1051 constructs support
1052 per-execution time formatting of the VALUES and/or SET clauses,
1053 based on the arguments passed to :meth:`_engine.Connection.execute`.
1054 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
1055 particular set of parameters into the statement.
1057 Multiple calls to :meth:`.ValuesBase.values` will produce a new
1058 construct, each one with the parameter list modified to include
1059 the new parameters sent. In the typical case of a single
1060 dictionary of parameters, the newly passed keys will replace
1061 the same keys in the previous construct. In the case of a list-based
1062 "multiple values" construct, each new list of values is extended
1063 onto the existing list of values.
1065 :param \**kwargs: key value pairs representing the string key
1066 of a :class:`_schema.Column`
1067 mapped to the value to be rendered into the
1068 VALUES or SET clause::
1070 users.insert().values(name="some name")
1072 users.update().where(users.c.id == 5).values(name="some name")
1074 :param \*args: As an alternative to passing key/value parameters,
1075 a dictionary, tuple, or list of dictionaries or tuples can be passed
1076 as a single positional argument in order to form the VALUES or
1077 SET clause of the statement. The forms that are accepted vary
1078 based on whether this is an :class:`_expression.Insert` or an
1079 :class:`_expression.Update` construct.
1081 For either an :class:`_expression.Insert` or
1082 :class:`_expression.Update`
1083 construct, a single dictionary can be passed, which works the same as
1084 that of the kwargs form::
1086 users.insert().values({"name": "some name"})
1088 users.update().values({"name": "some new name"})
1090 Also for either form but more typically for the
1091 :class:`_expression.Insert` construct, a tuple that contains an
1092 entry for every column in the table is also accepted::
1094 users.insert().values((5, "some name"))
1096 The :class:`_expression.Insert` construct also supports being
1097 passed a list of dictionaries or full-table-tuples, which on the
1098 server will render the less common SQL syntax of "multiple values" -
1099 this syntax is supported on backends such as SQLite, PostgreSQL,
1100 MySQL, but not necessarily others::
1102 users.insert().values(
1103 [
1104 {"name": "some name"},
1105 {"name": "some other name"},
1106 {"name": "yet another name"},
1107 ]
1108 )
1110 The above form would render a multiple VALUES statement similar to:
1112 .. sourcecode:: sql
1114 INSERT INTO users (name) VALUES
1115 (:name_1),
1116 (:name_2),
1117 (:name_3)
1119 It is essential to note that **passing multiple values is
1120 NOT the same as using traditional executemany() form**. The above
1121 syntax is a **special** syntax not typically used. To emit an
1122 INSERT statement against multiple rows, the normal method is
1123 to pass a multiple values list to the
1124 :meth:`_engine.Connection.execute`
1125 method, which is supported by all database backends and is generally
1126 more efficient for a very large number of parameters.
1128 .. seealso::
1130 :ref:`tutorial_multiple_parameters` - an introduction to
1131 the traditional Core method of multiple parameter set
1132 invocation for INSERTs and other statements.
1134 :ref:`tutorial_core_insert_values_clause` - Insert tutorial
1135 detailing alternatives to the multiple values syntax.
1137 The UPDATE construct also supports rendering the SET parameters
1138 in a specific order. For this feature refer to the
1139 :meth:`_expression.Update.ordered_values` method.
1141 .. seealso::
1143 :meth:`_expression.Update.ordered_values`
1146 """
1147 if args:
1148 # positional case. this is currently expensive. we don't
1149 # yet have positional-only args so we have to check the length.
1150 # then we need to check multiparams vs. single dictionary.
1151 # since the parameter format is needed in order to determine
1152 # a cache key, we need to determine this up front.
1153 arg = args[0]
1155 if kwargs:
1156 raise exc.ArgumentError(
1157 "Can't pass positional and kwargs to values() "
1158 "simultaneously"
1159 )
1160 elif len(args) > 1:
1161 raise exc.ArgumentError(
1162 "Only a single dictionary/tuple or list of "
1163 "dictionaries/tuples is accepted positionally."
1164 )
1166 elif isinstance(arg, collections_abc.Sequence):
1167 if arg and isinstance(arg[0], dict):
1168 multi_kv_generator = DMLState.get_plugin_class(
1169 self
1170 )._get_multi_crud_kv_pairs
1171 self._multi_values += (multi_kv_generator(self, arg),)
1172 return self
1174 if arg and isinstance(arg[0], (list, tuple)):
1175 self._multi_values += (arg,)
1176 return self
1178 if TYPE_CHECKING:
1179 # crud.py raises during compilation if this is not the
1180 # case
1181 assert isinstance(self, Insert)
1183 # tuple values
1184 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1186 else:
1187 # kwarg path. this is the most common path for non-multi-params
1188 # so this is fairly quick.
1189 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1190 if args:
1191 raise exc.ArgumentError(
1192 "Only a single dictionary/tuple or list of "
1193 "dictionaries/tuples is accepted positionally."
1194 )
1196 # for top level values(), convert literals to anonymous bound
1197 # parameters at statement construction time, so that these values can
1198 # participate in the cache key process like any other ClauseElement.
1199 # crud.py now intercepts bound parameters with unique=True from here
1200 # and ensures they get the "crud"-style name when rendered.
1202 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1203 coerced_arg = dict(kv_generator(self, arg.items(), True))
1204 if self._values:
1205 self._values = self._values.union(coerced_arg)
1206 else:
1207 self._values = util.immutabledict(coerced_arg)
1208 return self
1211class Insert(ValuesBase, HasSyntaxExtensions[Literal["post_values"]]):
1212 """Represent an INSERT construct.
1214 The :class:`_expression.Insert` object is created using the
1215 :func:`_expression.insert()` function.
1217 Available extension points:
1219 * ``post_values``: applies additional logic after the ``VALUES`` clause.
1221 """
1223 __visit_name__ = "insert"
1225 _supports_multi_parameters = True
1227 select = None
1228 include_insert_from_select_defaults = False
1230 _sort_by_parameter_order: bool = False
1232 is_insert = True
1234 table: TableClause
1236 _traverse_internals = (
1237 [
1238 ("table", InternalTraversal.dp_clauseelement),
1239 ("_inline", InternalTraversal.dp_boolean),
1240 ("_select_names", InternalTraversal.dp_string_list),
1241 ("_values", InternalTraversal.dp_dml_values),
1242 ("_multi_values", InternalTraversal.dp_dml_multi_values),
1243 ("select", InternalTraversal.dp_clauseelement),
1244 ("_post_values_clause", InternalTraversal.dp_clauseelement),
1245 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1246 ("_hints", InternalTraversal.dp_table_hint_list),
1247 ("_return_defaults", InternalTraversal.dp_boolean),
1248 (
1249 "_return_defaults_columns",
1250 InternalTraversal.dp_clauseelement_tuple,
1251 ),
1252 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1253 ]
1254 + HasPrefixes._has_prefixes_traverse_internals
1255 + DialectKWArgs._dialect_kwargs_traverse_internals
1256 + ExecutableStatement._executable_traverse_internals
1257 + HasCTE._has_ctes_traverse_internals
1258 )
1260 _position_map = util.immutabledict(
1261 {
1262 "post_values": "_post_values_clause",
1263 }
1264 )
1266 _post_values_clause: Optional[ClauseElement] = None
1267 """extension point for a ClauseElement that will be compiled directly
1268 after the VALUES portion of the :class:`.Insert` statement
1270 """
1272 def __init__(self, table: _DMLTableArgument):
1273 super().__init__(table)
1275 def _apply_syntax_extension_to_self(
1276 self, extension: SyntaxExtension
1277 ) -> None:
1278 extension.apply_to_insert(self)
1280 @_generative
1281 def inline(self) -> Self:
1282 """Make this :class:`_expression.Insert` construct "inline" .
1284 When set, no attempt will be made to retrieve the
1285 SQL-generated default values to be provided within the statement;
1286 in particular,
1287 this allows SQL expressions to be rendered 'inline' within the
1288 statement without the need to pre-execute them beforehand; for
1289 backends that support "returning", this turns off the "implicit
1290 returning" feature for the statement.
1293 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1294 parameter
1295 is now superseded by the :meth:`_expression.Insert.inline` method.
1297 """
1298 self._inline = True
1299 return self
1301 @_generative
1302 def from_select(
1303 self,
1304 names: Sequence[_DMLColumnArgument],
1305 select: Selectable,
1306 include_defaults: bool = True,
1307 ) -> Self:
1308 """Return a new :class:`_expression.Insert` construct which represents
1309 an ``INSERT...FROM SELECT`` statement.
1311 e.g.::
1313 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1314 ins = table2.insert().from_select(["a", "b"], sel)
1316 :param names: a sequence of string column names or
1317 :class:`_schema.Column`
1318 objects representing the target columns.
1319 :param select: a :func:`_expression.select` construct,
1320 :class:`_expression.FromClause`
1321 or other construct which resolves into a
1322 :class:`_expression.FromClause`,
1323 such as an ORM :class:`_query.Query` object, etc. The order of
1324 columns returned from this FROM clause should correspond to the
1325 order of columns sent as the ``names`` parameter; while this
1326 is not checked before passing along to the database, the database
1327 would normally raise an exception if these column lists don't
1328 correspond.
1329 :param include_defaults: if True, non-server default values and
1330 SQL expressions as specified on :class:`_schema.Column` objects
1331 (as documented in :ref:`metadata_defaults_toplevel`) not
1332 otherwise specified in the list of names will be rendered
1333 into the INSERT and SELECT statements, so that these values are also
1334 included in the data to be inserted.
1336 .. note:: A Python-side default that uses a Python callable function
1337 will only be invoked **once** for the whole statement, and **not
1338 per row**.
1340 """
1342 if self._values:
1343 raise exc.InvalidRequestError(
1344 "This construct already inserts value expressions"
1345 )
1347 self._select_names = [
1348 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1349 for name in names
1350 ]
1351 self._inline = True
1352 self.include_insert_from_select_defaults = include_defaults
1353 self.select = coercions.expect(roles.DMLSelectRole, select)
1354 return self
1356 if TYPE_CHECKING:
1357 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1359 # code within this block is **programmatically,
1360 # statically generated** by tools/generate_tuple_map_overloads.py
1362 @overload
1363 def returning(
1364 self,
1365 __ent0: _TCCA[_T0],
1366 /,
1367 *,
1368 sort_by_parameter_order: bool = False,
1369 ) -> ReturningInsert[_T0]: ...
1371 @overload
1372 def returning(
1373 self,
1374 __ent0: _TCCA[_T0],
1375 __ent1: _TCCA[_T1],
1376 /,
1377 *,
1378 sort_by_parameter_order: bool = False,
1379 ) -> ReturningInsert[_T0, _T1]: ...
1381 @overload
1382 def returning(
1383 self,
1384 __ent0: _TCCA[_T0],
1385 __ent1: _TCCA[_T1],
1386 __ent2: _TCCA[_T2],
1387 /,
1388 *,
1389 sort_by_parameter_order: bool = False,
1390 ) -> ReturningInsert[_T0, _T1, _T2]: ...
1392 @overload
1393 def returning(
1394 self,
1395 __ent0: _TCCA[_T0],
1396 __ent1: _TCCA[_T1],
1397 __ent2: _TCCA[_T2],
1398 __ent3: _TCCA[_T3],
1399 /,
1400 *,
1401 sort_by_parameter_order: bool = False,
1402 ) -> ReturningInsert[_T0, _T1, _T2, _T3]: ...
1404 @overload
1405 def returning(
1406 self,
1407 __ent0: _TCCA[_T0],
1408 __ent1: _TCCA[_T1],
1409 __ent2: _TCCA[_T2],
1410 __ent3: _TCCA[_T3],
1411 __ent4: _TCCA[_T4],
1412 /,
1413 *,
1414 sort_by_parameter_order: bool = False,
1415 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4]: ...
1417 @overload
1418 def returning(
1419 self,
1420 __ent0: _TCCA[_T0],
1421 __ent1: _TCCA[_T1],
1422 __ent2: _TCCA[_T2],
1423 __ent3: _TCCA[_T3],
1424 __ent4: _TCCA[_T4],
1425 __ent5: _TCCA[_T5],
1426 /,
1427 *,
1428 sort_by_parameter_order: bool = False,
1429 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5]: ...
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 __ent5: _TCCA[_T5],
1440 __ent6: _TCCA[_T6],
1441 /,
1442 *,
1443 sort_by_parameter_order: bool = False,
1444 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1446 @overload
1447 def returning(
1448 self,
1449 __ent0: _TCCA[_T0],
1450 __ent1: _TCCA[_T1],
1451 __ent2: _TCCA[_T2],
1452 __ent3: _TCCA[_T3],
1453 __ent4: _TCCA[_T4],
1454 __ent5: _TCCA[_T5],
1455 __ent6: _TCCA[_T6],
1456 __ent7: _TCCA[_T7],
1457 /,
1458 *entities: _ColumnsClauseArgument[Any],
1459 sort_by_parameter_order: bool = False,
1460 ) -> ReturningInsert[
1461 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1462 ]: ...
1464 # END OVERLOADED FUNCTIONS self.returning
1466 @overload
1467 def returning(
1468 self,
1469 *cols: _ColumnsClauseArgument[Any],
1470 sort_by_parameter_order: bool = False,
1471 **__kw: Any,
1472 ) -> ReturningInsert[Any]: ...
1474 def returning(
1475 self,
1476 *cols: _ColumnsClauseArgument[Any],
1477 sort_by_parameter_order: bool = False,
1478 **__kw: Any,
1479 ) -> ReturningInsert[Any]: ...
1482class ReturningInsert(Insert, TypedReturnsRows[Unpack[_Ts]]):
1483 """Typing-only class that establishes a generic type form of
1484 :class:`.Insert` which tracks returned column types.
1486 This datatype is delivered when calling the
1487 :meth:`.Insert.returning` method.
1489 .. versionadded:: 2.0
1491 """
1494# note: if not for MRO issues, this class should extend
1495# from HasSyntaxExtensions[Literal["post_criteria"]]
1496class DMLWhereBase:
1497 table: _DMLTableElement
1498 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1500 _post_criteria_clause: Optional[ClauseElement] = None
1501 """used by extensions to Update/Delete etc. to add additional syntacitcal
1502 constructs, e.g. LIMIT etc.
1504 .. versionadded:: 2.1
1506 """
1508 # can't put position_map here either without HasSyntaxExtensions
1509 # _position_map = util.immutabledict(
1510 # {"post_criteria": "_post_criteria_clause"}
1511 # )
1513 @_generative
1514 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1515 """Return a new construct with the given expression(s) added to
1516 its WHERE clause, joined to the existing clause via AND, if any.
1518 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1519 support multiple-table forms, including database-specific
1520 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1521 don't have multiple-table support, a backend agnostic approach
1522 to using multiple tables is to make use of correlated subqueries.
1523 See the linked tutorial sections below for examples.
1525 .. seealso::
1527 :ref:`tutorial_correlated_updates`
1529 :ref:`tutorial_update_from`
1531 :ref:`tutorial_multi_table_deletes`
1533 """
1535 for criterion in whereclause:
1536 where_criteria: ColumnElement[Any] = coercions.expect(
1537 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1538 )
1539 self._where_criteria += (where_criteria,)
1540 return self
1542 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1543 """A synonym for the :meth:`.where` method.
1545 .. versionadded:: 1.4
1547 """
1549 return self.where(*criteria)
1551 def filter_by(self, **kwargs: Any) -> Self:
1552 r"""Apply the given filtering criterion as a WHERE clause
1553 to this DML statement, using keyword expressions.
1555 E.g.::
1557 stmt = update(User).filter_by(name="some name").values(fullname="New Name")
1559 Multiple criteria may be specified as comma separated; the effect
1560 is that they will be joined together using the :func:`.and_`
1561 function::
1563 stmt = delete(User).filter_by(name="some name", id=5)
1565 The keyword expressions are extracted by searching across **all
1566 entities present in the FROM clause** of the statement.
1568 .. versionchanged:: 2.1
1570 :meth:`.DMLWhereBase.filter_by` now searches across all FROM clause
1571 entities, consistent with :meth:`_sql.Select.filter_by`.
1573 .. seealso::
1575 :meth:`.where` - filter on SQL expressions.
1577 :meth:`_sql.Select.filter_by`
1579 """ # noqa: E501
1581 entities: set[Any]
1583 if not isinstance(self.table, TableClause):
1584 entities = set(
1585 sql_util.find_tables(
1586 self.table, check_columns=False, include_joins=False
1587 )
1588 )
1589 else:
1590 entities = {self.table}
1592 if self.whereclause is not None:
1593 entities.update(self.whereclause._from_objects)
1595 clauses = [
1596 _entity_namespace_key_search_all(entities, key) == value
1597 for key, value in kwargs.items()
1598 ]
1599 return self.filter(*clauses)
1601 @property
1602 def whereclause(self) -> Optional[ColumnElement[Any]]:
1603 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1604 statement.
1606 This assembles the current collection of WHERE criteria
1607 into a single :class:`_expression.BooleanClauseList` construct.
1610 .. versionadded:: 1.4
1612 """
1614 return BooleanClauseList._construct_for_whereclause(
1615 self._where_criteria
1616 )
1619class Update(
1620 DMLWhereBase, ValuesBase, HasSyntaxExtensions[Literal["post_criteria"]]
1621):
1622 """Represent an Update construct.
1624 The :class:`_expression.Update` object is created using the
1625 :func:`_expression.update()` function.
1627 Available extension points:
1629 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1631 """
1633 __visit_name__ = "update"
1635 is_update = True
1637 _traverse_internals = (
1638 [
1639 ("table", InternalTraversal.dp_clauseelement),
1640 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1641 ("_inline", InternalTraversal.dp_boolean),
1642 ("_maintain_values_ordering", InternalTraversal.dp_boolean),
1643 ("_values", InternalTraversal.dp_dml_values),
1644 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1645 ("_hints", InternalTraversal.dp_table_hint_list),
1646 ("_return_defaults", InternalTraversal.dp_boolean),
1647 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1648 (
1649 "_return_defaults_columns",
1650 InternalTraversal.dp_clauseelement_tuple,
1651 ),
1652 ]
1653 + HasPrefixes._has_prefixes_traverse_internals
1654 + DialectKWArgs._dialect_kwargs_traverse_internals
1655 + ExecutableStatement._executable_traverse_internals
1656 + HasCTE._has_ctes_traverse_internals
1657 )
1659 _position_map = util.immutabledict(
1660 {"post_criteria": "_post_criteria_clause"}
1661 )
1663 def __init__(self, table: _DMLTableArgument):
1664 super().__init__(table)
1666 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1667 """Specify the VALUES clause of this UPDATE statement with an explicit
1668 parameter ordering that will be maintained in the SET clause of the
1669 resulting UPDATE statement.
1671 E.g.::
1673 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1675 .. seealso::
1677 :ref:`tutorial_parameter_ordered_updates` - full example of the
1678 :meth:`_expression.Update.ordered_values` method.
1680 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1681 method
1682 supersedes the
1683 :paramref:`_expression.update.preserve_parameter_order`
1684 parameter, which will be removed in SQLAlchemy 2.0.
1686 """ # noqa: E501
1687 if self._values:
1688 raise exc.ArgumentError(
1689 "This statement already has "
1690 f"{'ordered ' if self._maintain_values_ordering else ''}"
1691 "values present"
1692 )
1694 self = self.values(dict(args))
1695 self._maintain_values_ordering = True
1696 return self
1698 @_generative
1699 def inline(self) -> Self:
1700 """Make this :class:`_expression.Update` construct "inline" .
1702 When set, SQL defaults present on :class:`_schema.Column`
1703 objects via the
1704 ``default`` keyword will be compiled 'inline' into the statement and
1705 not pre-executed. This means that their values will not be available
1706 in the dictionary returned from
1707 :meth:`_engine.CursorResult.last_updated_params`.
1709 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1710 parameter
1711 is now superseded by the :meth:`_expression.Update.inline` method.
1713 """
1714 self._inline = True
1715 return self
1717 def _apply_syntax_extension_to_self(
1718 self, extension: SyntaxExtension
1719 ) -> None:
1720 extension.apply_to_update(self)
1722 if TYPE_CHECKING:
1723 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1725 # code within this block is **programmatically,
1726 # statically generated** by tools/generate_tuple_map_overloads.py
1728 @overload
1729 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningUpdate[_T0]: ...
1731 @overload
1732 def returning(
1733 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1734 ) -> ReturningUpdate[_T0, _T1]: ...
1736 @overload
1737 def returning(
1738 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1739 ) -> ReturningUpdate[_T0, _T1, _T2]: ...
1741 @overload
1742 def returning(
1743 self,
1744 __ent0: _TCCA[_T0],
1745 __ent1: _TCCA[_T1],
1746 __ent2: _TCCA[_T2],
1747 __ent3: _TCCA[_T3],
1748 /,
1749 ) -> ReturningUpdate[_T0, _T1, _T2, _T3]: ...
1751 @overload
1752 def returning(
1753 self,
1754 __ent0: _TCCA[_T0],
1755 __ent1: _TCCA[_T1],
1756 __ent2: _TCCA[_T2],
1757 __ent3: _TCCA[_T3],
1758 __ent4: _TCCA[_T4],
1759 /,
1760 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4]: ...
1762 @overload
1763 def returning(
1764 self,
1765 __ent0: _TCCA[_T0],
1766 __ent1: _TCCA[_T1],
1767 __ent2: _TCCA[_T2],
1768 __ent3: _TCCA[_T3],
1769 __ent4: _TCCA[_T4],
1770 __ent5: _TCCA[_T5],
1771 /,
1772 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5]: ...
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 __ent5: _TCCA[_T5],
1783 __ent6: _TCCA[_T6],
1784 /,
1785 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1787 @overload
1788 def returning(
1789 self,
1790 __ent0: _TCCA[_T0],
1791 __ent1: _TCCA[_T1],
1792 __ent2: _TCCA[_T2],
1793 __ent3: _TCCA[_T3],
1794 __ent4: _TCCA[_T4],
1795 __ent5: _TCCA[_T5],
1796 __ent6: _TCCA[_T6],
1797 __ent7: _TCCA[_T7],
1798 /,
1799 *entities: _ColumnsClauseArgument[Any],
1800 ) -> ReturningUpdate[
1801 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1802 ]: ...
1804 # END OVERLOADED FUNCTIONS self.returning
1806 @overload
1807 def returning(
1808 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1809 ) -> ReturningUpdate[Any]: ...
1811 def returning(
1812 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1813 ) -> ReturningUpdate[Any]: ...
1816class ReturningUpdate(Update, TypedReturnsRows[Unpack[_Ts]]):
1817 """Typing-only class that establishes a generic type form of
1818 :class:`.Update` which tracks returned column types.
1820 This datatype is delivered when calling the
1821 :meth:`.Update.returning` method.
1823 .. versionadded:: 2.0
1825 """
1828class Delete(
1829 DMLWhereBase, UpdateBase, HasSyntaxExtensions[Literal["post_criteria"]]
1830):
1831 """Represent a DELETE construct.
1833 The :class:`_expression.Delete` object is created using the
1834 :func:`_expression.delete()` function.
1836 Available extension points:
1838 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1840 """
1842 __visit_name__ = "delete"
1844 is_delete = True
1846 _traverse_internals = (
1847 [
1848 ("table", InternalTraversal.dp_clauseelement),
1849 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1850 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1851 ("_hints", InternalTraversal.dp_table_hint_list),
1852 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1853 ]
1854 + HasPrefixes._has_prefixes_traverse_internals
1855 + DialectKWArgs._dialect_kwargs_traverse_internals
1856 + ExecutableStatement._executable_traverse_internals
1857 + HasCTE._has_ctes_traverse_internals
1858 )
1860 _position_map = util.immutabledict(
1861 {"post_criteria": "_post_criteria_clause"}
1862 )
1864 def __init__(self, table: _DMLTableArgument):
1865 self.table = coercions.expect(
1866 roles.DMLTableRole, table, apply_propagate_attrs=self
1867 )
1869 def _apply_syntax_extension_to_self(
1870 self, extension: SyntaxExtension
1871 ) -> None:
1872 extension.apply_to_delete(self)
1874 if TYPE_CHECKING:
1875 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1877 # code within this block is **programmatically,
1878 # statically generated** by tools/generate_tuple_map_overloads.py
1880 @overload
1881 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningDelete[_T0]: ...
1883 @overload
1884 def returning(
1885 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1886 ) -> ReturningDelete[_T0, _T1]: ...
1888 @overload
1889 def returning(
1890 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1891 ) -> ReturningDelete[_T0, _T1, _T2]: ...
1893 @overload
1894 def returning(
1895 self,
1896 __ent0: _TCCA[_T0],
1897 __ent1: _TCCA[_T1],
1898 __ent2: _TCCA[_T2],
1899 __ent3: _TCCA[_T3],
1900 /,
1901 ) -> ReturningDelete[_T0, _T1, _T2, _T3]: ...
1903 @overload
1904 def returning(
1905 self,
1906 __ent0: _TCCA[_T0],
1907 __ent1: _TCCA[_T1],
1908 __ent2: _TCCA[_T2],
1909 __ent3: _TCCA[_T3],
1910 __ent4: _TCCA[_T4],
1911 /,
1912 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4]: ...
1914 @overload
1915 def returning(
1916 self,
1917 __ent0: _TCCA[_T0],
1918 __ent1: _TCCA[_T1],
1919 __ent2: _TCCA[_T2],
1920 __ent3: _TCCA[_T3],
1921 __ent4: _TCCA[_T4],
1922 __ent5: _TCCA[_T5],
1923 /,
1924 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1926 @overload
1927 def returning(
1928 self,
1929 __ent0: _TCCA[_T0],
1930 __ent1: _TCCA[_T1],
1931 __ent2: _TCCA[_T2],
1932 __ent3: _TCCA[_T3],
1933 __ent4: _TCCA[_T4],
1934 __ent5: _TCCA[_T5],
1935 __ent6: _TCCA[_T6],
1936 /,
1937 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1939 @overload
1940 def returning(
1941 self,
1942 __ent0: _TCCA[_T0],
1943 __ent1: _TCCA[_T1],
1944 __ent2: _TCCA[_T2],
1945 __ent3: _TCCA[_T3],
1946 __ent4: _TCCA[_T4],
1947 __ent5: _TCCA[_T5],
1948 __ent6: _TCCA[_T6],
1949 __ent7: _TCCA[_T7],
1950 /,
1951 *entities: _ColumnsClauseArgument[Any],
1952 ) -> ReturningDelete[
1953 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1954 ]: ...
1956 # END OVERLOADED FUNCTIONS self.returning
1958 @overload
1959 def returning(
1960 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1961 ) -> ReturningDelete[Unpack[TupleAny]]: ...
1963 def returning(
1964 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1965 ) -> ReturningDelete[Unpack[TupleAny]]: ...
1968class ReturningDelete(Update, TypedReturnsRows[Unpack[_Ts]]):
1969 """Typing-only class that establishes a generic type form of
1970 :class:`.Delete` which tracks returned column types.
1972 This datatype is delivered when calling the
1973 :meth:`.Delete.returning` method.
1975 .. versionadded:: 2.0
1977 """