Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/dml.py: 48%
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 MutableMapping
22from typing import NoReturn
23from typing import Optional
24from typing import overload
25from typing import Sequence
26from typing import Set
27from typing import Tuple
28from typing import Type
29from typing import TYPE_CHECKING
30from typing import TypeVar
31from typing import Union
33from . import coercions
34from . import roles
35from . import util as sql_util
36from ._typing import _TP
37from ._typing import _unexpected_kw
38from ._typing import is_column_element
39from ._typing import is_named_from_clause
40from .base import _entity_namespace_key
41from .base import _exclusive_against
42from .base import _from_objects
43from .base import _generative
44from .base import _select_iterables
45from .base import ColumnCollection
46from .base import ColumnSet
47from .base import CompileState
48from .base import DialectKWArgs
49from .base import Executable
50from .base import Generative
51from .base import HasCompileState
52from .elements import BooleanClauseList
53from .elements import ClauseElement
54from .elements import ColumnClause
55from .elements import ColumnElement
56from .elements import Null
57from .selectable import Alias
58from .selectable import ExecutableReturnsRows
59from .selectable import FromClause
60from .selectable import HasCTE
61from .selectable import HasPrefixes
62from .selectable import Join
63from .selectable import SelectLabelStyle
64from .selectable import TableClause
65from .selectable import TypedReturnsRows
66from .sqltypes import NullType
67from .visitors import InternalTraversal
68from .. import exc
69from .. import util
70from ..util.typing import Self
71from ..util.typing import TypeGuard
73if TYPE_CHECKING:
74 from ._typing import _ColumnExpressionArgument
75 from ._typing import _ColumnsClauseArgument
76 from ._typing import _DMLColumnArgument
77 from ._typing import _DMLColumnKeyMapping
78 from ._typing import _DMLTableArgument
79 from ._typing import _T0 # noqa
80 from ._typing import _T1 # noqa
81 from ._typing import _T2 # noqa
82 from ._typing import _T3 # noqa
83 from ._typing import _T4 # noqa
84 from ._typing import _T5 # noqa
85 from ._typing import _T6 # noqa
86 from ._typing import _T7 # noqa
87 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa
88 from .base import ReadOnlyColumnCollection
89 from .compiler import SQLCompiler
90 from .elements import KeyedColumnElement
91 from .selectable import _ColumnsClauseElement
92 from .selectable import _SelectIterable
93 from .selectable import Select
94 from .selectable import Selectable
96 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ...
98 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ...
100 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ...
102else:
103 isupdate = operator.attrgetter("isupdate")
104 isdelete = operator.attrgetter("isdelete")
105 isinsert = operator.attrgetter("isinsert")
108_T = TypeVar("_T", bound=Any)
110_DMLColumnElement = Union[str, ColumnClause[Any]]
111_DMLTableElement = Union[TableClause, Alias, Join]
114class DMLState(CompileState):
115 _no_parameters = True
116 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None
117 _multi_parameters: Optional[
118 List[MutableMapping[_DMLColumnElement, Any]]
119 ] = None
120 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None
121 _parameter_ordering: Optional[List[_DMLColumnElement]] = None
122 _primary_table: FromClause
123 _supports_implicit_returning = True
125 isupdate = False
126 isdelete = False
127 isinsert = False
129 statement: UpdateBase
131 def __init__(
132 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any
133 ):
134 raise NotImplementedError()
136 @classmethod
137 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]:
138 return {
139 "name": (
140 statement.table.name
141 if is_named_from_clause(statement.table)
142 else None
143 ),
144 "table": statement.table,
145 }
147 @classmethod
148 def get_returning_column_descriptions(
149 cls, statement: UpdateBase
150 ) -> List[Dict[str, Any]]:
151 return [
152 {
153 "name": c.key,
154 "type": c.type,
155 "expr": c,
156 }
157 for c in statement._all_selected_columns
158 ]
160 @property
161 def dml_table(self) -> _DMLTableElement:
162 return self.statement.table
164 if TYPE_CHECKING:
166 @classmethod
167 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ...
169 @classmethod
170 def _get_multi_crud_kv_pairs(
171 cls,
172 statement: UpdateBase,
173 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]],
174 ) -> List[Dict[_DMLColumnElement, Any]]:
175 return [
176 {
177 coercions.expect(roles.DMLColumnRole, k): v
178 for k, v in mapping.items()
179 }
180 for mapping in multi_kv_iterator
181 ]
183 @classmethod
184 def _get_crud_kv_pairs(
185 cls,
186 statement: UpdateBase,
187 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]],
188 needs_to_be_cacheable: bool,
189 ) -> List[Tuple[_DMLColumnElement, Any]]:
190 return [
191 (
192 coercions.expect(roles.DMLColumnRole, k),
193 (
194 v
195 if not needs_to_be_cacheable
196 else coercions.expect(
197 roles.ExpressionElementRole,
198 v,
199 type_=NullType(),
200 is_crud=True,
201 )
202 ),
203 )
204 for k, v in kv_iterator
205 ]
207 def _make_extra_froms(
208 self, statement: DMLWhereBase
209 ) -> Tuple[FromClause, List[FromClause]]:
210 froms: List[FromClause] = []
212 all_tables = list(sql_util.tables_from_leftmost(statement.table))
213 primary_table = all_tables[0]
214 seen = {primary_table}
216 consider = statement._where_criteria
217 if self._dict_parameters:
218 consider += tuple(self._dict_parameters.values())
220 for crit in consider:
221 for item in _from_objects(crit):
222 if not seen.intersection(item._cloned_set):
223 froms.append(item)
224 seen.update(item._cloned_set)
226 froms.extend(all_tables[1:])
227 return primary_table, froms
229 def _process_values(self, statement: ValuesBase) -> None:
230 if self._no_parameters:
231 self._dict_parameters = statement._values
232 self._no_parameters = False
234 def _process_select_values(self, statement: ValuesBase) -> None:
235 assert statement._select_names is not None
236 parameters: MutableMapping[_DMLColumnElement, Any] = {
237 name: Null() for name in statement._select_names
238 }
240 if self._no_parameters:
241 self._no_parameters = False
242 self._dict_parameters = parameters
243 else:
244 # this condition normally not reachable as the Insert
245 # does not allow this construction to occur
246 assert False, "This statement already has parameters"
248 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn:
249 raise exc.InvalidRequestError(
250 "%s construct does not support "
251 "multiple parameter sets." % statement.__visit_name__.upper()
252 )
254 def _cant_mix_formats_error(self) -> NoReturn:
255 raise exc.InvalidRequestError(
256 "Can't mix single and multiple VALUES "
257 "formats in one INSERT statement; one style appends to a "
258 "list while the other replaces values, so the intent is "
259 "ambiguous."
260 )
263@CompileState.plugin_for("default", "insert")
264class InsertDMLState(DMLState):
265 isinsert = True
267 include_table_with_column_exprs = False
269 _has_multi_parameters = False
271 def __init__(
272 self,
273 statement: Insert,
274 compiler: SQLCompiler,
275 disable_implicit_returning: bool = False,
276 **kw: Any,
277 ):
278 self.statement = statement
279 self._primary_table = statement.table
281 if disable_implicit_returning:
282 self._supports_implicit_returning = False
284 self.isinsert = True
285 if statement._select_names:
286 self._process_select_values(statement)
287 if statement._values is not None:
288 self._process_values(statement)
289 if statement._multi_values:
290 self._process_multi_values(statement)
292 @util.memoized_property
293 def _insert_col_keys(self) -> List[str]:
294 # this is also done in crud.py -> _key_getters_for_crud_column
295 return [
296 coercions.expect(roles.DMLColumnRole, col, as_key=True)
297 for col in self._dict_parameters or ()
298 ]
300 def _process_values(self, statement: ValuesBase) -> None:
301 if self._no_parameters:
302 self._has_multi_parameters = False
303 self._dict_parameters = statement._values
304 self._no_parameters = False
305 elif self._has_multi_parameters:
306 self._cant_mix_formats_error()
308 def _process_multi_values(self, statement: ValuesBase) -> None:
309 for parameters in statement._multi_values:
310 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [
311 (
312 {
313 c.key: value
314 for c, value in zip(statement.table.c, parameter_set)
315 }
316 if isinstance(parameter_set, collections_abc.Sequence)
317 else parameter_set
318 )
319 for parameter_set in parameters
320 ]
322 if self._no_parameters:
323 self._no_parameters = False
324 self._has_multi_parameters = True
325 self._multi_parameters = multi_parameters
326 self._dict_parameters = self._multi_parameters[0]
327 elif not self._has_multi_parameters:
328 self._cant_mix_formats_error()
329 else:
330 assert self._multi_parameters
331 self._multi_parameters.extend(multi_parameters)
334@CompileState.plugin_for("default", "update")
335class UpdateDMLState(DMLState):
336 isupdate = True
338 include_table_with_column_exprs = False
340 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any):
341 self.statement = statement
343 self.isupdate = True
344 if statement._ordered_values is not None:
345 self._process_ordered_values(statement)
346 elif statement._values is not None:
347 self._process_values(statement)
348 elif statement._multi_values:
349 self._no_multi_values_supported(statement)
350 t, ef = self._make_extra_froms(statement)
351 self._primary_table = t
352 self._extra_froms = ef
354 self.is_multitable = mt = ef
355 self.include_table_with_column_exprs = bool(
356 mt and compiler.render_table_with_column_in_update_from
357 )
359 def _process_ordered_values(self, statement: ValuesBase) -> None:
360 parameters = statement._ordered_values
362 if self._no_parameters:
363 self._no_parameters = False
364 assert parameters is not None
365 self._dict_parameters = dict(parameters)
366 self._ordered_values = parameters
367 self._parameter_ordering = [key for key, value in parameters]
368 else:
369 raise exc.InvalidRequestError(
370 "Can only invoke ordered_values() once, and not mixed "
371 "with any other values() call"
372 )
375@CompileState.plugin_for("default", "delete")
376class DeleteDMLState(DMLState):
377 isdelete = True
379 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any):
380 self.statement = statement
382 self.isdelete = True
383 t, ef = self._make_extra_froms(statement)
384 self._primary_table = t
385 self._extra_froms = ef
386 self.is_multitable = ef
389class UpdateBase(
390 roles.DMLRole,
391 HasCTE,
392 HasCompileState,
393 DialectKWArgs,
394 HasPrefixes,
395 Generative,
396 ExecutableReturnsRows,
397 ClauseElement,
398):
399 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
401 __visit_name__ = "update_base"
403 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = (
404 util.EMPTY_DICT
405 )
406 named_with_column = False
408 _label_style: SelectLabelStyle = (
409 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY
410 )
411 table: _DMLTableElement
413 _return_defaults = False
414 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = (
415 None
416 )
417 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None
418 _returning: Tuple[_ColumnsClauseElement, ...] = ()
420 is_dml = True
422 def _generate_fromclause_column_proxies(
423 self,
424 fromclause: FromClause,
425 columns: ColumnCollection[str, KeyedColumnElement[Any]],
426 primary_key: ColumnSet,
427 foreign_keys: Set[KeyedColumnElement[Any]],
428 ) -> None:
429 prox = [
430 c._make_proxy(
431 fromclause,
432 key=proxy_key,
433 name=required_label_name,
434 name_is_truncatable=True,
435 primary_key=primary_key,
436 foreign_keys=foreign_keys,
437 )
438 for (
439 required_label_name,
440 proxy_key,
441 fallback_label_name,
442 c,
443 repeated,
444 ) in (self._generate_columns_plus_names(False))
445 if is_column_element(c)
446 ]
448 columns._populate_separate_keys(prox)
450 def params(self, *arg: Any, **kw: Any) -> NoReturn:
451 """Set the parameters for the statement.
453 This method raises ``NotImplementedError`` on the base class,
454 and is overridden by :class:`.ValuesBase` to provide the
455 SET/VALUES clause of UPDATE and INSERT.
457 """
458 raise NotImplementedError(
459 "params() is not supported for INSERT/UPDATE/DELETE statements."
460 " To set the values for an INSERT or UPDATE statement, use"
461 " stmt.values(**parameters)."
462 )
464 @_generative
465 def with_dialect_options(self, **opt: Any) -> Self:
466 """Add dialect options to this INSERT/UPDATE/DELETE object.
468 e.g.::
470 upd = table.update().dialect_options(mysql_limit=10)
472 .. versionadded: 1.4 - this method supersedes the dialect options
473 associated with the constructor.
476 """
477 self._validate_dialect_kwargs(opt)
478 return self
480 @_generative
481 def return_defaults(
482 self,
483 *cols: _DMLColumnArgument,
484 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None,
485 sort_by_parameter_order: bool = False,
486 ) -> Self:
487 """Make use of a :term:`RETURNING` clause for the purpose
488 of fetching server-side expressions and defaults, for supporting
489 backends only.
491 .. deepalchemy::
493 The :meth:`.UpdateBase.return_defaults` method is used by the ORM
494 for its internal work in fetching newly generated primary key
495 and server default values, in particular to provide the underlying
496 implementation of the :paramref:`_orm.Mapper.eager_defaults`
497 ORM feature as well as to allow RETURNING support with bulk
498 ORM inserts. Its behavior is fairly idiosyncratic
499 and is not really intended for general use. End users should
500 stick with using :meth:`.UpdateBase.returning` in order to
501 add RETURNING clauses to their INSERT, UPDATE and DELETE
502 statements.
504 Normally, a single row INSERT statement will automatically populate the
505 :attr:`.CursorResult.inserted_primary_key` attribute when executed,
506 which stores the primary key of the row that was just inserted in the
507 form of a :class:`.Row` object with column names as named tuple keys
508 (and the :attr:`.Row._mapping` view fully populated as well). The
509 dialect in use chooses the strategy to use in order to populate this
510 data; if it was generated using server-side defaults and / or SQL
511 expressions, dialect-specific approaches such as ``cursor.lastrowid``
512 or ``RETURNING`` are typically used to acquire the new primary key
513 value.
515 However, when the statement is modified by calling
516 :meth:`.UpdateBase.return_defaults` before executing the statement,
517 additional behaviors take place **only** for backends that support
518 RETURNING and for :class:`.Table` objects that maintain the
519 :paramref:`.Table.implicit_returning` parameter at its default value of
520 ``True``. In these cases, when the :class:`.CursorResult` is returned
521 from the statement's execution, not only will
522 :attr:`.CursorResult.inserted_primary_key` be populated as always, the
523 :attr:`.CursorResult.returned_defaults` attribute will also be
524 populated with a :class:`.Row` named-tuple representing the full range
525 of server generated
526 values from that single row, including values for any columns that
527 specify :paramref:`_schema.Column.server_default` or which make use of
528 :paramref:`_schema.Column.default` using a SQL expression.
530 When invoking INSERT statements with multiple rows using
531 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the
532 :meth:`.UpdateBase.return_defaults` modifier will have the effect of
533 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and
534 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being
535 fully populated with lists of :class:`.Row` objects representing newly
536 inserted primary key values as well as newly inserted server generated
537 values for each row inserted. The
538 :attr:`.CursorResult.inserted_primary_key` and
539 :attr:`.CursorResult.returned_defaults` attributes will also continue
540 to be populated with the first row of these two collections.
542 If the backend does not support RETURNING or the :class:`.Table` in use
543 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING
544 clause is added and no additional data is fetched, however the
545 INSERT, UPDATE or DELETE statement proceeds normally.
547 E.g.::
549 stmt = table.insert().values(data="newdata").return_defaults()
551 result = connection.execute(stmt)
553 server_created_at = result.returned_defaults["created_at"]
555 When used against an UPDATE statement
556 :meth:`.UpdateBase.return_defaults` instead looks for columns that
557 include :paramref:`_schema.Column.onupdate` or
558 :paramref:`_schema.Column.server_onupdate` parameters assigned, when
559 constructing the columns that will be included in the RETURNING clause
560 by default if explicit columns were not specified. When used against a
561 DELETE statement, no columns are included in RETURNING by default, they
562 instead must be specified explicitly as there are no columns that
563 normally change values when a DELETE statement proceeds.
565 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported
566 for DELETE statements also and has been moved from
567 :class:`.ValuesBase` to :class:`.UpdateBase`.
569 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive
570 against the :meth:`.UpdateBase.returning` method and errors will be
571 raised during the SQL compilation process if both are used at the same
572 time on one statement. The RETURNING clause of the INSERT, UPDATE or
573 DELETE statement is therefore controlled by only one of these methods
574 at a time.
576 The :meth:`.UpdateBase.return_defaults` method differs from
577 :meth:`.UpdateBase.returning` in these ways:
579 1. :meth:`.UpdateBase.return_defaults` method causes the
580 :attr:`.CursorResult.returned_defaults` collection to be populated
581 with the first row from the RETURNING result. This attribute is not
582 populated when using :meth:`.UpdateBase.returning`.
584 2. :meth:`.UpdateBase.return_defaults` is compatible with existing
585 logic used to fetch auto-generated primary key values that are then
586 populated into the :attr:`.CursorResult.inserted_primary_key`
587 attribute. By contrast, using :meth:`.UpdateBase.returning` will
588 have the effect of the :attr:`.CursorResult.inserted_primary_key`
589 attribute being left unpopulated.
591 3. :meth:`.UpdateBase.return_defaults` can be called against any
592 backend. Backends that don't support RETURNING will skip the usage
593 of the feature, rather than raising an exception, *unless*
594 ``supplemental_cols`` is passed. The return value
595 of :attr:`_engine.CursorResult.returned_defaults` will be ``None``
596 for backends that don't support RETURNING or for which the target
597 :class:`.Table` sets :paramref:`.Table.implicit_returning` to
598 ``False``.
600 4. An INSERT statement invoked with executemany() is supported if the
601 backend database driver supports the
602 :ref:`insertmanyvalues <engine_insertmanyvalues>`
603 feature which is now supported by most SQLAlchemy-included backends.
604 When executemany is used, the
605 :attr:`_engine.CursorResult.returned_defaults_rows` and
606 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors
607 will return the inserted defaults and primary keys.
609 .. versionadded:: 1.4 Added
610 :attr:`_engine.CursorResult.returned_defaults_rows` and
611 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors.
612 In version 2.0, the underlying implementation which fetches and
613 populates the data for these attributes was generalized to be
614 supported by most backends, whereas in 1.4 they were only
615 supported by the ``psycopg2`` driver.
618 :param cols: optional list of column key names or
619 :class:`_schema.Column` that acts as a filter for those columns that
620 will be fetched.
621 :param supplemental_cols: optional list of RETURNING expressions,
622 in the same form as one would pass to the
623 :meth:`.UpdateBase.returning` method. When present, the additional
624 columns will be included in the RETURNING clause, and the
625 :class:`.CursorResult` object will be "rewound" when returned, so
626 that methods like :meth:`.CursorResult.all` will return new rows
627 mostly as though the statement used :meth:`.UpdateBase.returning`
628 directly. However, unlike when using :meth:`.UpdateBase.returning`
629 directly, the **order of the columns is undefined**, so can only be
630 targeted using names or :attr:`.Row._mapping` keys; they cannot
631 reliably be targeted positionally.
633 .. versionadded:: 2.0
635 :param sort_by_parameter_order: for a batch INSERT that is being
636 executed against multiple parameter sets, organize the results of
637 RETURNING so that the returned rows correspond to the order of
638 parameter sets passed in. This applies only to an :term:`executemany`
639 execution for supporting dialects and typically makes use of the
640 :term:`insertmanyvalues` feature.
642 .. versionadded:: 2.0.10
644 .. seealso::
646 :ref:`engine_insertmanyvalues_returning_order` - background on
647 sorting of RETURNING rows for bulk INSERT
649 .. seealso::
651 :meth:`.UpdateBase.returning`
653 :attr:`_engine.CursorResult.returned_defaults`
655 :attr:`_engine.CursorResult.returned_defaults_rows`
657 :attr:`_engine.CursorResult.inserted_primary_key`
659 :attr:`_engine.CursorResult.inserted_primary_key_rows`
661 """
663 if self._return_defaults:
664 # note _return_defaults_columns = () means return all columns,
665 # so if we have been here before, only update collection if there
666 # are columns in the collection
667 if self._return_defaults_columns and cols:
668 self._return_defaults_columns = tuple(
669 util.OrderedSet(self._return_defaults_columns).union(
670 coercions.expect(roles.ColumnsClauseRole, c)
671 for c in cols
672 )
673 )
674 else:
675 # set for all columns
676 self._return_defaults_columns = ()
677 else:
678 self._return_defaults_columns = tuple(
679 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
680 )
681 self._return_defaults = True
682 if sort_by_parameter_order:
683 if not self.is_insert:
684 raise exc.ArgumentError(
685 "The 'sort_by_parameter_order' argument to "
686 "return_defaults() only applies to INSERT statements"
687 )
688 self._sort_by_parameter_order = True
689 if supplemental_cols:
690 # uniquifying while also maintaining order (the maintain of order
691 # is for test suites but also for vertical splicing
692 supplemental_col_tup = (
693 coercions.expect(roles.ColumnsClauseRole, c)
694 for c in supplemental_cols
695 )
697 if self._supplemental_returning is None:
698 self._supplemental_returning = tuple(
699 util.unique_list(supplemental_col_tup)
700 )
701 else:
702 self._supplemental_returning = tuple(
703 util.unique_list(
704 self._supplemental_returning
705 + tuple(supplemental_col_tup)
706 )
707 )
709 return self
711 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
712 """Return ``True`` if this :class:`.ReturnsRows` is
713 'derived' from the given :class:`.FromClause`.
715 Since these are DMLs, we dont want such statements ever being adapted
716 so we return False for derives.
718 """
719 return False
721 @_generative
722 def returning(
723 self,
724 *cols: _ColumnsClauseArgument[Any],
725 sort_by_parameter_order: bool = False,
726 **__kw: Any,
727 ) -> UpdateBase:
728 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
730 e.g.:
732 .. sourcecode:: pycon+sql
734 >>> stmt = (
735 ... table.update()
736 ... .where(table.c.data == "value")
737 ... .values(status="X")
738 ... .returning(table.c.server_flag, table.c.updated_timestamp)
739 ... )
740 >>> print(stmt)
741 {printsql}UPDATE some_table SET status=:status
742 WHERE some_table.data = :data_1
743 RETURNING some_table.server_flag, some_table.updated_timestamp
745 The method may be invoked multiple times to add new entries to the
746 list of expressions to be returned.
748 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to
749 add new entries to the list of expressions to be returned.
751 The given collection of column expressions should be derived from the
752 table that is the target of the INSERT, UPDATE, or DELETE. While
753 :class:`_schema.Column` objects are typical, the elements can also be
754 expressions:
756 .. sourcecode:: pycon+sql
758 >>> stmt = table.insert().returning(
759 ... (table.c.first_name + " " + table.c.last_name).label("fullname")
760 ... )
761 >>> print(stmt)
762 {printsql}INSERT INTO some_table (first_name, last_name)
763 VALUES (:first_name, :last_name)
764 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname
766 Upon compilation, a RETURNING clause, or database equivalent,
767 will be rendered within the statement. For INSERT and UPDATE,
768 the values are the newly inserted/updated values. For DELETE,
769 the values are those of the rows which were deleted.
771 Upon execution, the values of the columns to be returned are made
772 available via the result set and can be iterated using
773 :meth:`_engine.CursorResult.fetchone` and similar.
774 For DBAPIs which do not
775 natively support returning values (i.e. cx_oracle), SQLAlchemy will
776 approximate this behavior at the result level so that a reasonable
777 amount of behavioral neutrality is provided.
779 Note that not all databases/DBAPIs
780 support RETURNING. For those backends with no support,
781 an exception is raised upon compilation and/or execution.
782 For those who do support it, the functionality across backends
783 varies greatly, including restrictions on executemany()
784 and other statements which return multiple rows. Please
785 read the documentation notes for the database in use in
786 order to determine the availability of RETURNING.
788 :param \*cols: series of columns, SQL expressions, or whole tables
789 entities to be returned.
790 :param sort_by_parameter_order: for a batch INSERT that is being
791 executed against multiple parameter sets, organize the results of
792 RETURNING so that the returned rows correspond to the order of
793 parameter sets passed in. This applies only to an :term:`executemany`
794 execution for supporting dialects and typically makes use of the
795 :term:`insertmanyvalues` feature.
797 .. versionadded:: 2.0.10
799 .. seealso::
801 :ref:`engine_insertmanyvalues_returning_order` - background on
802 sorting of RETURNING rows for bulk INSERT (Core level discussion)
804 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of
805 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion)
807 .. seealso::
809 :meth:`.UpdateBase.return_defaults` - an alternative method tailored
810 towards efficient fetching of server-side defaults and triggers
811 for single-row INSERTs or UPDATEs.
813 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`
815 """ # noqa: E501
816 if __kw:
817 raise _unexpected_kw("UpdateBase.returning()", __kw)
818 if self._return_defaults:
819 raise exc.InvalidRequestError(
820 "return_defaults() is already configured on this statement"
821 )
822 self._returning += tuple(
823 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
824 )
825 if sort_by_parameter_order:
826 if not self.is_insert:
827 raise exc.ArgumentError(
828 "The 'sort_by_parameter_order' argument to returning() "
829 "only applies to INSERT statements"
830 )
831 self._sort_by_parameter_order = True
832 return self
834 def corresponding_column(
835 self, column: KeyedColumnElement[Any], require_embedded: bool = False
836 ) -> Optional[ColumnElement[Any]]:
837 return self.exported_columns.corresponding_column(
838 column, require_embedded=require_embedded
839 )
841 @util.ro_memoized_property
842 def _all_selected_columns(self) -> _SelectIterable:
843 return [c for c in _select_iterables(self._returning)]
845 @util.ro_memoized_property
846 def exported_columns(
847 self,
848 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]:
849 """Return the RETURNING columns as a column collection for this
850 statement.
852 .. versionadded:: 1.4
854 """
855 return ColumnCollection(
856 (c.key, c)
857 for c in self._all_selected_columns
858 if is_column_element(c)
859 ).as_readonly()
861 @_generative
862 def with_hint(
863 self,
864 text: str,
865 selectable: Optional[_DMLTableArgument] = None,
866 dialect_name: str = "*",
867 ) -> Self:
868 """Add a table hint for a single table to this
869 INSERT/UPDATE/DELETE statement.
871 .. note::
873 :meth:`.UpdateBase.with_hint` currently applies only to
874 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
875 :meth:`.UpdateBase.prefix_with`.
877 The text of the hint is rendered in the appropriate
878 location for the database backend in use, relative
879 to the :class:`_schema.Table` that is the subject of this
880 statement, or optionally to that of the given
881 :class:`_schema.Table` passed as the ``selectable`` argument.
883 The ``dialect_name`` option will limit the rendering of a particular
884 hint to a particular backend. Such as, to add a hint
885 that only takes effect for SQL Server::
887 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
889 :param text: Text of the hint.
890 :param selectable: optional :class:`_schema.Table` that specifies
891 an element of the FROM clause within an UPDATE or DELETE
892 to be the subject of the hint - applies only to certain backends.
893 :param dialect_name: defaults to ``*``, if specified as the name
894 of a particular dialect, will apply these hints only when
895 that dialect is in use.
896 """
897 if selectable is None:
898 selectable = self.table
899 else:
900 selectable = coercions.expect(roles.DMLTableRole, selectable)
901 self._hints = self._hints.union({(selectable, dialect_name): text})
902 return self
904 @property
905 def entity_description(self) -> Dict[str, Any]:
906 """Return a :term:`plugin-enabled` description of the table and/or
907 entity which this DML construct is operating against.
909 This attribute is generally useful when using the ORM, as an
910 extended structure which includes information about mapped
911 entities is returned. The section :ref:`queryguide_inspection`
912 contains more background.
914 For a Core statement, the structure returned by this accessor
915 is derived from the :attr:`.UpdateBase.table` attribute, and
916 refers to the :class:`.Table` being inserted, updated, or deleted::
918 >>> stmt = insert(user_table)
919 >>> stmt.entity_description
920 {
921 "name": "user_table",
922 "table": Table("user_table", ...)
923 }
925 .. versionadded:: 1.4.33
927 .. seealso::
929 :attr:`.UpdateBase.returning_column_descriptions`
931 :attr:`.Select.column_descriptions` - entity information for
932 a :func:`.select` construct
934 :ref:`queryguide_inspection` - ORM background
936 """
937 meth = DMLState.get_plugin_class(self).get_entity_description
938 return meth(self)
940 @property
941 def returning_column_descriptions(self) -> List[Dict[str, Any]]:
942 """Return a :term:`plugin-enabled` description of the columns
943 which this DML construct is RETURNING against, in other words
944 the expressions established as part of :meth:`.UpdateBase.returning`.
946 This attribute is generally useful when using the ORM, as an
947 extended structure which includes information about mapped
948 entities is returned. The section :ref:`queryguide_inspection`
949 contains more background.
951 For a Core statement, the structure returned by this accessor is
952 derived from the same objects that are returned by the
953 :attr:`.UpdateBase.exported_columns` accessor::
955 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name)
956 >>> stmt.entity_description
957 [
958 {
959 "name": "id",
960 "type": Integer,
961 "expr": Column("id", Integer(), table=<user>, ...)
962 },
963 {
964 "name": "name",
965 "type": String(),
966 "expr": Column("name", String(), table=<user>, ...)
967 },
968 ]
970 .. versionadded:: 1.4.33
972 .. seealso::
974 :attr:`.UpdateBase.entity_description`
976 :attr:`.Select.column_descriptions` - entity information for
977 a :func:`.select` construct
979 :ref:`queryguide_inspection` - ORM background
981 """ # noqa: E501
982 meth = DMLState.get_plugin_class(
983 self
984 ).get_returning_column_descriptions
985 return meth(self)
988class ValuesBase(UpdateBase):
989 """Supplies support for :meth:`.ValuesBase.values` to
990 INSERT and UPDATE constructs."""
992 __visit_name__ = "values_base"
994 _supports_multi_parameters = False
996 select: Optional[Select[Any]] = None
997 """SELECT statement for INSERT .. FROM SELECT"""
999 _post_values_clause: Optional[ClauseElement] = None
1000 """used by extensions to Insert etc. to add additional syntacitcal
1001 constructs, e.g. ON CONFLICT etc."""
1003 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None
1004 _multi_values: Tuple[
1005 Union[
1006 Sequence[Dict[_DMLColumnElement, Any]],
1007 Sequence[Sequence[Any]],
1008 ],
1009 ...,
1010 ] = ()
1012 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None
1014 _select_names: Optional[List[str]] = None
1015 _inline: bool = False
1017 def __init__(self, table: _DMLTableArgument):
1018 self.table = coercions.expect(
1019 roles.DMLTableRole, table, apply_propagate_attrs=self
1020 )
1022 @_generative
1023 @_exclusive_against(
1024 "_select_names",
1025 "_ordered_values",
1026 msgs={
1027 "_select_names": "This construct already inserts from a SELECT",
1028 "_ordered_values": "This statement already has ordered "
1029 "values present",
1030 },
1031 )
1032 def values(
1033 self,
1034 *args: Union[
1035 _DMLColumnKeyMapping[Any],
1036 Sequence[Any],
1037 ],
1038 **kwargs: Any,
1039 ) -> Self:
1040 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
1041 clause for an UPDATE.
1043 Note that the :class:`_expression.Insert` and
1044 :class:`_expression.Update`
1045 constructs support
1046 per-execution time formatting of the VALUES and/or SET clauses,
1047 based on the arguments passed to :meth:`_engine.Connection.execute`.
1048 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
1049 particular set of parameters into the statement.
1051 Multiple calls to :meth:`.ValuesBase.values` will produce a new
1052 construct, each one with the parameter list modified to include
1053 the new parameters sent. In the typical case of a single
1054 dictionary of parameters, the newly passed keys will replace
1055 the same keys in the previous construct. In the case of a list-based
1056 "multiple values" construct, each new list of values is extended
1057 onto the existing list of values.
1059 :param \**kwargs: key value pairs representing the string key
1060 of a :class:`_schema.Column`
1061 mapped to the value to be rendered into the
1062 VALUES or SET clause::
1064 users.insert().values(name="some name")
1066 users.update().where(users.c.id == 5).values(name="some name")
1068 :param \*args: As an alternative to passing key/value parameters,
1069 a dictionary, tuple, or list of dictionaries or tuples can be passed
1070 as a single positional argument in order to form the VALUES or
1071 SET clause of the statement. The forms that are accepted vary
1072 based on whether this is an :class:`_expression.Insert` or an
1073 :class:`_expression.Update` construct.
1075 For either an :class:`_expression.Insert` or
1076 :class:`_expression.Update`
1077 construct, a single dictionary can be passed, which works the same as
1078 that of the kwargs form::
1080 users.insert().values({"name": "some name"})
1082 users.update().values({"name": "some new name"})
1084 Also for either form but more typically for the
1085 :class:`_expression.Insert` construct, a tuple that contains an
1086 entry for every column in the table is also accepted::
1088 users.insert().values((5, "some name"))
1090 The :class:`_expression.Insert` construct also supports being
1091 passed a list of dictionaries or full-table-tuples, which on the
1092 server will render the less common SQL syntax of "multiple values" -
1093 this syntax is supported on backends such as SQLite, PostgreSQL,
1094 MySQL, but not necessarily others::
1096 users.insert().values(
1097 [
1098 {"name": "some name"},
1099 {"name": "some other name"},
1100 {"name": "yet another name"},
1101 ]
1102 )
1104 The above form would render a multiple VALUES statement similar to:
1106 .. sourcecode:: sql
1108 INSERT INTO users (name) VALUES
1109 (:name_1),
1110 (:name_2),
1111 (:name_3)
1113 It is essential to note that **passing multiple values is
1114 NOT the same as using traditional executemany() form**. The above
1115 syntax is a **special** syntax not typically used. To emit an
1116 INSERT statement against multiple rows, the normal method is
1117 to pass a multiple values list to the
1118 :meth:`_engine.Connection.execute`
1119 method, which is supported by all database backends and is generally
1120 more efficient for a very large number of parameters.
1122 .. seealso::
1124 :ref:`tutorial_multiple_parameters` - an introduction to
1125 the traditional Core method of multiple parameter set
1126 invocation for INSERTs and other statements.
1128 :ref:`tutorial_core_insert_values_clause` - Insert tutorial
1129 detailing alternatives to the multiple values syntax.
1131 The UPDATE construct also supports rendering the SET parameters
1132 in a specific order. For this feature refer to the
1133 :meth:`_expression.Update.ordered_values` method.
1135 .. seealso::
1137 :meth:`_expression.Update.ordered_values`
1140 """
1141 if args:
1142 # positional case. this is currently expensive. we don't
1143 # yet have positional-only args so we have to check the length.
1144 # then we need to check multiparams vs. single dictionary.
1145 # since the parameter format is needed in order to determine
1146 # a cache key, we need to determine this up front.
1147 arg = args[0]
1149 if kwargs:
1150 raise exc.ArgumentError(
1151 "Can't pass positional and kwargs to values() "
1152 "simultaneously"
1153 )
1154 elif len(args) > 1:
1155 raise exc.ArgumentError(
1156 "Only a single dictionary/tuple or list of "
1157 "dictionaries/tuples is accepted positionally."
1158 )
1160 elif isinstance(arg, collections_abc.Sequence):
1161 if arg and isinstance(arg[0], dict):
1162 multi_kv_generator = DMLState.get_plugin_class(
1163 self
1164 )._get_multi_crud_kv_pairs
1165 self._multi_values += (multi_kv_generator(self, arg),)
1166 return self
1168 if arg and isinstance(arg[0], (list, tuple)):
1169 self._multi_values += (arg,)
1170 return self
1172 if TYPE_CHECKING:
1173 # crud.py raises during compilation if this is not the
1174 # case
1175 assert isinstance(self, Insert)
1177 # tuple values
1178 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1180 else:
1181 # kwarg path. this is the most common path for non-multi-params
1182 # so this is fairly quick.
1183 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1184 if args:
1185 raise exc.ArgumentError(
1186 "Only a single dictionary/tuple or list of "
1187 "dictionaries/tuples is accepted positionally."
1188 )
1190 # for top level values(), convert literals to anonymous bound
1191 # parameters at statement construction time, so that these values can
1192 # participate in the cache key process like any other ClauseElement.
1193 # crud.py now intercepts bound parameters with unique=True from here
1194 # and ensures they get the "crud"-style name when rendered.
1196 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1197 coerced_arg = dict(kv_generator(self, arg.items(), True))
1198 if self._values:
1199 self._values = self._values.union(coerced_arg)
1200 else:
1201 self._values = util.immutabledict(coerced_arg)
1202 return self
1205class Insert(ValuesBase):
1206 """Represent an INSERT construct.
1208 The :class:`_expression.Insert` object is created using the
1209 :func:`_expression.insert()` function.
1211 """
1213 __visit_name__ = "insert"
1215 _supports_multi_parameters = True
1217 select = None
1218 include_insert_from_select_defaults = False
1220 _sort_by_parameter_order: bool = False
1222 is_insert = True
1224 table: TableClause
1226 _traverse_internals = (
1227 [
1228 ("table", InternalTraversal.dp_clauseelement),
1229 ("_inline", InternalTraversal.dp_boolean),
1230 ("_select_names", InternalTraversal.dp_string_list),
1231 ("_values", InternalTraversal.dp_dml_values),
1232 ("_multi_values", InternalTraversal.dp_dml_multi_values),
1233 ("select", InternalTraversal.dp_clauseelement),
1234 ("_post_values_clause", InternalTraversal.dp_clauseelement),
1235 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1236 ("_hints", InternalTraversal.dp_table_hint_list),
1237 ("_return_defaults", InternalTraversal.dp_boolean),
1238 (
1239 "_return_defaults_columns",
1240 InternalTraversal.dp_clauseelement_tuple,
1241 ),
1242 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1243 ]
1244 + HasPrefixes._has_prefixes_traverse_internals
1245 + DialectKWArgs._dialect_kwargs_traverse_internals
1246 + Executable._executable_traverse_internals
1247 + HasCTE._has_ctes_traverse_internals
1248 )
1250 def __init__(self, table: _DMLTableArgument):
1251 super().__init__(table)
1253 @_generative
1254 def inline(self) -> Self:
1255 """Make this :class:`_expression.Insert` construct "inline" .
1257 When set, no attempt will be made to retrieve the
1258 SQL-generated default values to be provided within the statement;
1259 in particular,
1260 this allows SQL expressions to be rendered 'inline' within the
1261 statement without the need to pre-execute them beforehand; for
1262 backends that support "returning", this turns off the "implicit
1263 returning" feature for the statement.
1266 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1267 parameter
1268 is now superseded by the :meth:`_expression.Insert.inline` method.
1270 """
1271 self._inline = True
1272 return self
1274 @_generative
1275 def from_select(
1276 self,
1277 names: Sequence[_DMLColumnArgument],
1278 select: Selectable,
1279 include_defaults: bool = True,
1280 ) -> Self:
1281 """Return a new :class:`_expression.Insert` construct which represents
1282 an ``INSERT...FROM SELECT`` statement.
1284 e.g.::
1286 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1287 ins = table2.insert().from_select(["a", "b"], sel)
1289 :param names: a sequence of string column names or
1290 :class:`_schema.Column`
1291 objects representing the target columns.
1292 :param select: a :func:`_expression.select` construct,
1293 :class:`_expression.FromClause`
1294 or other construct which resolves into a
1295 :class:`_expression.FromClause`,
1296 such as an ORM :class:`_query.Query` object, etc. The order of
1297 columns returned from this FROM clause should correspond to the
1298 order of columns sent as the ``names`` parameter; while this
1299 is not checked before passing along to the database, the database
1300 would normally raise an exception if these column lists don't
1301 correspond.
1302 :param include_defaults: if True, non-server default values and
1303 SQL expressions as specified on :class:`_schema.Column` objects
1304 (as documented in :ref:`metadata_defaults_toplevel`) not
1305 otherwise specified in the list of names will be rendered
1306 into the INSERT and SELECT statements, so that these values are also
1307 included in the data to be inserted.
1309 .. note:: A Python-side default that uses a Python callable function
1310 will only be invoked **once** for the whole statement, and **not
1311 per row**.
1313 """
1315 if self._values:
1316 raise exc.InvalidRequestError(
1317 "This construct already inserts value expressions"
1318 )
1320 self._select_names = [
1321 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1322 for name in names
1323 ]
1324 self._inline = True
1325 self.include_insert_from_select_defaults = include_defaults
1326 self.select = coercions.expect(roles.DMLSelectRole, select)
1327 return self
1329 if TYPE_CHECKING:
1330 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1332 # code within this block is **programmatically,
1333 # statically generated** by tools/generate_tuple_map_overloads.py
1335 @overload
1336 def returning(
1337 self, __ent0: _TCCA[_T0], *, sort_by_parameter_order: bool = False
1338 ) -> ReturningInsert[Tuple[_T0]]: ...
1340 @overload
1341 def returning(
1342 self,
1343 __ent0: _TCCA[_T0],
1344 __ent1: _TCCA[_T1],
1345 *,
1346 sort_by_parameter_order: bool = False,
1347 ) -> ReturningInsert[Tuple[_T0, _T1]]: ...
1349 @overload
1350 def returning(
1351 self,
1352 __ent0: _TCCA[_T0],
1353 __ent1: _TCCA[_T1],
1354 __ent2: _TCCA[_T2],
1355 *,
1356 sort_by_parameter_order: bool = False,
1357 ) -> ReturningInsert[Tuple[_T0, _T1, _T2]]: ...
1359 @overload
1360 def returning(
1361 self,
1362 __ent0: _TCCA[_T0],
1363 __ent1: _TCCA[_T1],
1364 __ent2: _TCCA[_T2],
1365 __ent3: _TCCA[_T3],
1366 *,
1367 sort_by_parameter_order: bool = False,
1368 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3]]: ...
1370 @overload
1371 def returning(
1372 self,
1373 __ent0: _TCCA[_T0],
1374 __ent1: _TCCA[_T1],
1375 __ent2: _TCCA[_T2],
1376 __ent3: _TCCA[_T3],
1377 __ent4: _TCCA[_T4],
1378 *,
1379 sort_by_parameter_order: bool = False,
1380 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1382 @overload
1383 def returning(
1384 self,
1385 __ent0: _TCCA[_T0],
1386 __ent1: _TCCA[_T1],
1387 __ent2: _TCCA[_T2],
1388 __ent3: _TCCA[_T3],
1389 __ent4: _TCCA[_T4],
1390 __ent5: _TCCA[_T5],
1391 *,
1392 sort_by_parameter_order: bool = False,
1393 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1395 @overload
1396 def returning(
1397 self,
1398 __ent0: _TCCA[_T0],
1399 __ent1: _TCCA[_T1],
1400 __ent2: _TCCA[_T2],
1401 __ent3: _TCCA[_T3],
1402 __ent4: _TCCA[_T4],
1403 __ent5: _TCCA[_T5],
1404 __ent6: _TCCA[_T6],
1405 *,
1406 sort_by_parameter_order: bool = False,
1407 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1409 @overload
1410 def returning(
1411 self,
1412 __ent0: _TCCA[_T0],
1413 __ent1: _TCCA[_T1],
1414 __ent2: _TCCA[_T2],
1415 __ent3: _TCCA[_T3],
1416 __ent4: _TCCA[_T4],
1417 __ent5: _TCCA[_T5],
1418 __ent6: _TCCA[_T6],
1419 __ent7: _TCCA[_T7],
1420 *,
1421 sort_by_parameter_order: bool = False,
1422 ) -> ReturningInsert[
1423 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1424 ]: ...
1426 # END OVERLOADED FUNCTIONS self.returning
1428 @overload
1429 def returning(
1430 self,
1431 *cols: _ColumnsClauseArgument[Any],
1432 sort_by_parameter_order: bool = False,
1433 **__kw: Any,
1434 ) -> ReturningInsert[Any]: ...
1436 def returning(
1437 self,
1438 *cols: _ColumnsClauseArgument[Any],
1439 sort_by_parameter_order: bool = False,
1440 **__kw: Any,
1441 ) -> ReturningInsert[Any]: ...
1444class ReturningInsert(Insert, TypedReturnsRows[_TP]):
1445 """Typing-only class that establishes a generic type form of
1446 :class:`.Insert` which tracks returned column types.
1448 This datatype is delivered when calling the
1449 :meth:`.Insert.returning` method.
1451 .. versionadded:: 2.0
1453 """
1456class DMLWhereBase:
1457 table: _DMLTableElement
1458 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1460 @_generative
1461 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1462 """Return a new construct with the given expression(s) added to
1463 its WHERE clause, joined to the existing clause via AND, if any.
1465 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1466 support multiple-table forms, including database-specific
1467 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1468 don't have multiple-table support, a backend agnostic approach
1469 to using multiple tables is to make use of correlated subqueries.
1470 See the linked tutorial sections below for examples.
1472 .. seealso::
1474 :ref:`tutorial_correlated_updates`
1476 :ref:`tutorial_update_from`
1478 :ref:`tutorial_multi_table_deletes`
1480 """
1482 for criterion in whereclause:
1483 where_criteria: ColumnElement[Any] = coercions.expect(
1484 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1485 )
1486 self._where_criteria += (where_criteria,)
1487 return self
1489 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1490 """A synonym for the :meth:`_dml.DMLWhereBase.where` method.
1492 .. versionadded:: 1.4
1494 """
1496 return self.where(*criteria)
1498 def _filter_by_zero(self) -> _DMLTableElement:
1499 return self.table
1501 def filter_by(self, **kwargs: Any) -> Self:
1502 r"""apply the given filtering criterion as a WHERE clause
1503 to this select.
1505 """
1506 from_entity = self._filter_by_zero()
1508 clauses = [
1509 _entity_namespace_key(from_entity, key) == value
1510 for key, value in kwargs.items()
1511 ]
1512 return self.filter(*clauses)
1514 @property
1515 def whereclause(self) -> Optional[ColumnElement[Any]]:
1516 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1517 statement.
1519 This assembles the current collection of WHERE criteria
1520 into a single :class:`_expression.BooleanClauseList` construct.
1523 .. versionadded:: 1.4
1525 """
1527 return BooleanClauseList._construct_for_whereclause(
1528 self._where_criteria
1529 )
1532class Update(DMLWhereBase, ValuesBase):
1533 """Represent an Update construct.
1535 The :class:`_expression.Update` object is created using the
1536 :func:`_expression.update()` function.
1538 """
1540 __visit_name__ = "update"
1542 is_update = True
1544 _traverse_internals = (
1545 [
1546 ("table", InternalTraversal.dp_clauseelement),
1547 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1548 ("_inline", InternalTraversal.dp_boolean),
1549 ("_ordered_values", InternalTraversal.dp_dml_ordered_values),
1550 ("_values", InternalTraversal.dp_dml_values),
1551 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1552 ("_hints", InternalTraversal.dp_table_hint_list),
1553 ("_return_defaults", InternalTraversal.dp_boolean),
1554 (
1555 "_return_defaults_columns",
1556 InternalTraversal.dp_clauseelement_tuple,
1557 ),
1558 ]
1559 + HasPrefixes._has_prefixes_traverse_internals
1560 + DialectKWArgs._dialect_kwargs_traverse_internals
1561 + Executable._executable_traverse_internals
1562 + HasCTE._has_ctes_traverse_internals
1563 )
1565 def __init__(self, table: _DMLTableArgument):
1566 super().__init__(table)
1568 @_generative
1569 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1570 """Specify the VALUES clause of this UPDATE statement with an explicit
1571 parameter ordering that will be maintained in the SET clause of the
1572 resulting UPDATE statement.
1574 E.g.::
1576 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1578 .. seealso::
1580 :ref:`tutorial_parameter_ordered_updates` - full example of the
1581 :meth:`_expression.Update.ordered_values` method.
1583 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1584 method
1585 supersedes the
1586 :paramref:`_expression.update.preserve_parameter_order`
1587 parameter, which will be removed in SQLAlchemy 2.0.
1589 """ # noqa: E501
1590 if self._values:
1591 raise exc.ArgumentError(
1592 "This statement already has values present"
1593 )
1594 elif self._ordered_values:
1595 raise exc.ArgumentError(
1596 "This statement already has ordered values present"
1597 )
1599 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1600 self._ordered_values = kv_generator(self, args, True)
1601 return self
1603 @_generative
1604 def inline(self) -> Self:
1605 """Make this :class:`_expression.Update` construct "inline" .
1607 When set, SQL defaults present on :class:`_schema.Column`
1608 objects via the
1609 ``default`` keyword will be compiled 'inline' into the statement and
1610 not pre-executed. This means that their values will not be available
1611 in the dictionary returned from
1612 :meth:`_engine.CursorResult.last_updated_params`.
1614 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1615 parameter
1616 is now superseded by the :meth:`_expression.Update.inline` method.
1618 """
1619 self._inline = True
1620 return self
1622 if TYPE_CHECKING:
1623 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1625 # code within this block is **programmatically,
1626 # statically generated** by tools/generate_tuple_map_overloads.py
1628 @overload
1629 def returning(
1630 self, __ent0: _TCCA[_T0]
1631 ) -> ReturningUpdate[Tuple[_T0]]: ...
1633 @overload
1634 def returning(
1635 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1636 ) -> ReturningUpdate[Tuple[_T0, _T1]]: ...
1638 @overload
1639 def returning(
1640 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1641 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2]]: ...
1643 @overload
1644 def returning(
1645 self,
1646 __ent0: _TCCA[_T0],
1647 __ent1: _TCCA[_T1],
1648 __ent2: _TCCA[_T2],
1649 __ent3: _TCCA[_T3],
1650 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3]]: ...
1652 @overload
1653 def returning(
1654 self,
1655 __ent0: _TCCA[_T0],
1656 __ent1: _TCCA[_T1],
1657 __ent2: _TCCA[_T2],
1658 __ent3: _TCCA[_T3],
1659 __ent4: _TCCA[_T4],
1660 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1662 @overload
1663 def returning(
1664 self,
1665 __ent0: _TCCA[_T0],
1666 __ent1: _TCCA[_T1],
1667 __ent2: _TCCA[_T2],
1668 __ent3: _TCCA[_T3],
1669 __ent4: _TCCA[_T4],
1670 __ent5: _TCCA[_T5],
1671 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1673 @overload
1674 def returning(
1675 self,
1676 __ent0: _TCCA[_T0],
1677 __ent1: _TCCA[_T1],
1678 __ent2: _TCCA[_T2],
1679 __ent3: _TCCA[_T3],
1680 __ent4: _TCCA[_T4],
1681 __ent5: _TCCA[_T5],
1682 __ent6: _TCCA[_T6],
1683 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1685 @overload
1686 def returning(
1687 self,
1688 __ent0: _TCCA[_T0],
1689 __ent1: _TCCA[_T1],
1690 __ent2: _TCCA[_T2],
1691 __ent3: _TCCA[_T3],
1692 __ent4: _TCCA[_T4],
1693 __ent5: _TCCA[_T5],
1694 __ent6: _TCCA[_T6],
1695 __ent7: _TCCA[_T7],
1696 ) -> ReturningUpdate[
1697 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1698 ]: ...
1700 # END OVERLOADED FUNCTIONS self.returning
1702 @overload
1703 def returning(
1704 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1705 ) -> ReturningUpdate[Any]: ...
1707 def returning(
1708 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1709 ) -> ReturningUpdate[Any]: ...
1712class ReturningUpdate(Update, TypedReturnsRows[_TP]):
1713 """Typing-only class that establishes a generic type form of
1714 :class:`.Update` which tracks returned column types.
1716 This datatype is delivered when calling the
1717 :meth:`.Update.returning` method.
1719 .. versionadded:: 2.0
1721 """
1724class Delete(DMLWhereBase, UpdateBase):
1725 """Represent a DELETE construct.
1727 The :class:`_expression.Delete` object is created using the
1728 :func:`_expression.delete()` function.
1730 """
1732 __visit_name__ = "delete"
1734 is_delete = True
1736 _traverse_internals = (
1737 [
1738 ("table", InternalTraversal.dp_clauseelement),
1739 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1740 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1741 ("_hints", InternalTraversal.dp_table_hint_list),
1742 ]
1743 + HasPrefixes._has_prefixes_traverse_internals
1744 + DialectKWArgs._dialect_kwargs_traverse_internals
1745 + Executable._executable_traverse_internals
1746 + HasCTE._has_ctes_traverse_internals
1747 )
1749 def __init__(self, table: _DMLTableArgument):
1750 self.table = coercions.expect(
1751 roles.DMLTableRole, table, apply_propagate_attrs=self
1752 )
1754 if TYPE_CHECKING:
1755 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1757 # code within this block is **programmatically,
1758 # statically generated** by tools/generate_tuple_map_overloads.py
1760 @overload
1761 def returning(
1762 self, __ent0: _TCCA[_T0]
1763 ) -> ReturningDelete[Tuple[_T0]]: ...
1765 @overload
1766 def returning(
1767 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1768 ) -> ReturningDelete[Tuple[_T0, _T1]]: ...
1770 @overload
1771 def returning(
1772 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1773 ) -> ReturningDelete[Tuple[_T0, _T1, _T2]]: ...
1775 @overload
1776 def returning(
1777 self,
1778 __ent0: _TCCA[_T0],
1779 __ent1: _TCCA[_T1],
1780 __ent2: _TCCA[_T2],
1781 __ent3: _TCCA[_T3],
1782 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3]]: ...
1784 @overload
1785 def returning(
1786 self,
1787 __ent0: _TCCA[_T0],
1788 __ent1: _TCCA[_T1],
1789 __ent2: _TCCA[_T2],
1790 __ent3: _TCCA[_T3],
1791 __ent4: _TCCA[_T4],
1792 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1794 @overload
1795 def returning(
1796 self,
1797 __ent0: _TCCA[_T0],
1798 __ent1: _TCCA[_T1],
1799 __ent2: _TCCA[_T2],
1800 __ent3: _TCCA[_T3],
1801 __ent4: _TCCA[_T4],
1802 __ent5: _TCCA[_T5],
1803 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1805 @overload
1806 def returning(
1807 self,
1808 __ent0: _TCCA[_T0],
1809 __ent1: _TCCA[_T1],
1810 __ent2: _TCCA[_T2],
1811 __ent3: _TCCA[_T3],
1812 __ent4: _TCCA[_T4],
1813 __ent5: _TCCA[_T5],
1814 __ent6: _TCCA[_T6],
1815 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1817 @overload
1818 def returning(
1819 self,
1820 __ent0: _TCCA[_T0],
1821 __ent1: _TCCA[_T1],
1822 __ent2: _TCCA[_T2],
1823 __ent3: _TCCA[_T3],
1824 __ent4: _TCCA[_T4],
1825 __ent5: _TCCA[_T5],
1826 __ent6: _TCCA[_T6],
1827 __ent7: _TCCA[_T7],
1828 ) -> ReturningDelete[
1829 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1830 ]: ...
1832 # END OVERLOADED FUNCTIONS self.returning
1834 @overload
1835 def returning(
1836 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1837 ) -> ReturningDelete[Any]: ...
1839 def returning(
1840 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1841 ) -> ReturningDelete[Any]: ...
1844class ReturningDelete(Update, TypedReturnsRows[_TP]):
1845 """Typing-only class that establishes a generic type form of
1846 :class:`.Delete` which tracks returned column types.
1848 This datatype is delivered when calling the
1849 :meth:`.Delete.returning` method.
1851 .. versionadded:: 2.0
1853 """