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-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7"""
8Provide :class:`_expression.Insert`, :class:`_expression.Update` and
9:class:`_expression.Delete`.
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 underyling
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 The UPDATE construct also supports rendering the SET parameters
1129 in a specific order. For this feature refer to the
1130 :meth:`_expression.Update.ordered_values` method.
1132 .. seealso::
1134 :meth:`_expression.Update.ordered_values`
1137 """
1138 if args:
1139 # positional case. this is currently expensive. we don't
1140 # yet have positional-only args so we have to check the length.
1141 # then we need to check multiparams vs. single dictionary.
1142 # since the parameter format is needed in order to determine
1143 # a cache key, we need to determine this up front.
1144 arg = args[0]
1146 if kwargs:
1147 raise exc.ArgumentError(
1148 "Can't pass positional and kwargs to values() "
1149 "simultaneously"
1150 )
1151 elif len(args) > 1:
1152 raise exc.ArgumentError(
1153 "Only a single dictionary/tuple or list of "
1154 "dictionaries/tuples is accepted positionally."
1155 )
1157 elif isinstance(arg, collections_abc.Sequence):
1158 if arg and isinstance(arg[0], dict):
1159 multi_kv_generator = DMLState.get_plugin_class(
1160 self
1161 )._get_multi_crud_kv_pairs
1162 self._multi_values += (multi_kv_generator(self, arg),)
1163 return self
1165 if arg and isinstance(arg[0], (list, tuple)):
1166 self._multi_values += (arg,)
1167 return self
1169 if TYPE_CHECKING:
1170 # crud.py raises during compilation if this is not the
1171 # case
1172 assert isinstance(self, Insert)
1174 # tuple values
1175 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1177 else:
1178 # kwarg path. this is the most common path for non-multi-params
1179 # so this is fairly quick.
1180 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1181 if args:
1182 raise exc.ArgumentError(
1183 "Only a single dictionary/tuple or list of "
1184 "dictionaries/tuples is accepted positionally."
1185 )
1187 # for top level values(), convert literals to anonymous bound
1188 # parameters at statement construction time, so that these values can
1189 # participate in the cache key process like any other ClauseElement.
1190 # crud.py now intercepts bound parameters with unique=True from here
1191 # and ensures they get the "crud"-style name when rendered.
1193 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1194 coerced_arg = dict(kv_generator(self, arg.items(), True))
1195 if self._values:
1196 self._values = self._values.union(coerced_arg)
1197 else:
1198 self._values = util.immutabledict(coerced_arg)
1199 return self
1202class Insert(ValuesBase):
1203 """Represent an INSERT construct.
1205 The :class:`_expression.Insert` object is created using the
1206 :func:`_expression.insert()` function.
1208 """
1210 __visit_name__ = "insert"
1212 _supports_multi_parameters = True
1214 select = None
1215 include_insert_from_select_defaults = False
1217 _sort_by_parameter_order: bool = False
1219 is_insert = True
1221 table: TableClause
1223 _traverse_internals = (
1224 [
1225 ("table", InternalTraversal.dp_clauseelement),
1226 ("_inline", InternalTraversal.dp_boolean),
1227 ("_select_names", InternalTraversal.dp_string_list),
1228 ("_values", InternalTraversal.dp_dml_values),
1229 ("_multi_values", InternalTraversal.dp_dml_multi_values),
1230 ("select", InternalTraversal.dp_clauseelement),
1231 ("_post_values_clause", InternalTraversal.dp_clauseelement),
1232 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1233 ("_hints", InternalTraversal.dp_table_hint_list),
1234 ("_return_defaults", InternalTraversal.dp_boolean),
1235 (
1236 "_return_defaults_columns",
1237 InternalTraversal.dp_clauseelement_tuple,
1238 ),
1239 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1240 ]
1241 + HasPrefixes._has_prefixes_traverse_internals
1242 + DialectKWArgs._dialect_kwargs_traverse_internals
1243 + Executable._executable_traverse_internals
1244 + HasCTE._has_ctes_traverse_internals
1245 )
1247 def __init__(self, table: _DMLTableArgument):
1248 super().__init__(table)
1250 @_generative
1251 def inline(self) -> Self:
1252 """Make this :class:`_expression.Insert` construct "inline" .
1254 When set, no attempt will be made to retrieve the
1255 SQL-generated default values to be provided within the statement;
1256 in particular,
1257 this allows SQL expressions to be rendered 'inline' within the
1258 statement without the need to pre-execute them beforehand; for
1259 backends that support "returning", this turns off the "implicit
1260 returning" feature for the statement.
1263 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1264 parameter
1265 is now superseded by the :meth:`_expression.Insert.inline` method.
1267 """
1268 self._inline = True
1269 return self
1271 @_generative
1272 def from_select(
1273 self,
1274 names: Sequence[_DMLColumnArgument],
1275 select: Selectable,
1276 include_defaults: bool = True,
1277 ) -> Self:
1278 """Return a new :class:`_expression.Insert` construct which represents
1279 an ``INSERT...FROM SELECT`` statement.
1281 e.g.::
1283 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1284 ins = table2.insert().from_select(["a", "b"], sel)
1286 :param names: a sequence of string column names or
1287 :class:`_schema.Column`
1288 objects representing the target columns.
1289 :param select: a :func:`_expression.select` construct,
1290 :class:`_expression.FromClause`
1291 or other construct which resolves into a
1292 :class:`_expression.FromClause`,
1293 such as an ORM :class:`_query.Query` object, etc. The order of
1294 columns returned from this FROM clause should correspond to the
1295 order of columns sent as the ``names`` parameter; while this
1296 is not checked before passing along to the database, the database
1297 would normally raise an exception if these column lists don't
1298 correspond.
1299 :param include_defaults: if True, non-server default values and
1300 SQL expressions as specified on :class:`_schema.Column` objects
1301 (as documented in :ref:`metadata_defaults_toplevel`) not
1302 otherwise specified in the list of names will be rendered
1303 into the INSERT and SELECT statements, so that these values are also
1304 included in the data to be inserted.
1306 .. note:: A Python-side default that uses a Python callable function
1307 will only be invoked **once** for the whole statement, and **not
1308 per row**.
1310 """
1312 if self._values:
1313 raise exc.InvalidRequestError(
1314 "This construct already inserts value expressions"
1315 )
1317 self._select_names = [
1318 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1319 for name in names
1320 ]
1321 self._inline = True
1322 self.include_insert_from_select_defaults = include_defaults
1323 self.select = coercions.expect(roles.DMLSelectRole, select)
1324 return self
1326 if TYPE_CHECKING:
1327 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1329 # code within this block is **programmatically,
1330 # statically generated** by tools/generate_tuple_map_overloads.py
1332 @overload
1333 def returning(
1334 self, __ent0: _TCCA[_T0], *, sort_by_parameter_order: bool = False
1335 ) -> ReturningInsert[Tuple[_T0]]: ...
1337 @overload
1338 def returning(
1339 self,
1340 __ent0: _TCCA[_T0],
1341 __ent1: _TCCA[_T1],
1342 *,
1343 sort_by_parameter_order: bool = False,
1344 ) -> ReturningInsert[Tuple[_T0, _T1]]: ...
1346 @overload
1347 def returning(
1348 self,
1349 __ent0: _TCCA[_T0],
1350 __ent1: _TCCA[_T1],
1351 __ent2: _TCCA[_T2],
1352 *,
1353 sort_by_parameter_order: bool = False,
1354 ) -> ReturningInsert[Tuple[_T0, _T1, _T2]]: ...
1356 @overload
1357 def returning(
1358 self,
1359 __ent0: _TCCA[_T0],
1360 __ent1: _TCCA[_T1],
1361 __ent2: _TCCA[_T2],
1362 __ent3: _TCCA[_T3],
1363 *,
1364 sort_by_parameter_order: bool = False,
1365 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3]]: ...
1367 @overload
1368 def returning(
1369 self,
1370 __ent0: _TCCA[_T0],
1371 __ent1: _TCCA[_T1],
1372 __ent2: _TCCA[_T2],
1373 __ent3: _TCCA[_T3],
1374 __ent4: _TCCA[_T4],
1375 *,
1376 sort_by_parameter_order: bool = False,
1377 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1379 @overload
1380 def returning(
1381 self,
1382 __ent0: _TCCA[_T0],
1383 __ent1: _TCCA[_T1],
1384 __ent2: _TCCA[_T2],
1385 __ent3: _TCCA[_T3],
1386 __ent4: _TCCA[_T4],
1387 __ent5: _TCCA[_T5],
1388 *,
1389 sort_by_parameter_order: bool = False,
1390 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1392 @overload
1393 def returning(
1394 self,
1395 __ent0: _TCCA[_T0],
1396 __ent1: _TCCA[_T1],
1397 __ent2: _TCCA[_T2],
1398 __ent3: _TCCA[_T3],
1399 __ent4: _TCCA[_T4],
1400 __ent5: _TCCA[_T5],
1401 __ent6: _TCCA[_T6],
1402 *,
1403 sort_by_parameter_order: bool = False,
1404 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1406 @overload
1407 def returning(
1408 self,
1409 __ent0: _TCCA[_T0],
1410 __ent1: _TCCA[_T1],
1411 __ent2: _TCCA[_T2],
1412 __ent3: _TCCA[_T3],
1413 __ent4: _TCCA[_T4],
1414 __ent5: _TCCA[_T5],
1415 __ent6: _TCCA[_T6],
1416 __ent7: _TCCA[_T7],
1417 *,
1418 sort_by_parameter_order: bool = False,
1419 ) -> ReturningInsert[
1420 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1421 ]: ...
1423 # END OVERLOADED FUNCTIONS self.returning
1425 @overload
1426 def returning(
1427 self,
1428 *cols: _ColumnsClauseArgument[Any],
1429 sort_by_parameter_order: bool = False,
1430 **__kw: Any,
1431 ) -> ReturningInsert[Any]: ...
1433 def returning(
1434 self,
1435 *cols: _ColumnsClauseArgument[Any],
1436 sort_by_parameter_order: bool = False,
1437 **__kw: Any,
1438 ) -> ReturningInsert[Any]: ...
1441class ReturningInsert(Insert, TypedReturnsRows[_TP]):
1442 """Typing-only class that establishes a generic type form of
1443 :class:`.Insert` which tracks returned column types.
1445 This datatype is delivered when calling the
1446 :meth:`.Insert.returning` method.
1448 .. versionadded:: 2.0
1450 """
1453class DMLWhereBase:
1454 table: _DMLTableElement
1455 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1457 @_generative
1458 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1459 """Return a new construct with the given expression(s) added to
1460 its WHERE clause, joined to the existing clause via AND, if any.
1462 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1463 support multiple-table forms, including database-specific
1464 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1465 don't have multiple-table support, a backend agnostic approach
1466 to using multiple tables is to make use of correlated subqueries.
1467 See the linked tutorial sections below for examples.
1469 .. seealso::
1471 :ref:`tutorial_correlated_updates`
1473 :ref:`tutorial_update_from`
1475 :ref:`tutorial_multi_table_deletes`
1477 """
1479 for criterion in whereclause:
1480 where_criteria: ColumnElement[Any] = coercions.expect(
1481 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1482 )
1483 self._where_criteria += (where_criteria,)
1484 return self
1486 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1487 """A synonym for the :meth:`_dml.DMLWhereBase.where` method.
1489 .. versionadded:: 1.4
1491 """
1493 return self.where(*criteria)
1495 def _filter_by_zero(self) -> _DMLTableElement:
1496 return self.table
1498 def filter_by(self, **kwargs: Any) -> Self:
1499 r"""apply the given filtering criterion as a WHERE clause
1500 to this select.
1502 """
1503 from_entity = self._filter_by_zero()
1505 clauses = [
1506 _entity_namespace_key(from_entity, key) == value
1507 for key, value in kwargs.items()
1508 ]
1509 return self.filter(*clauses)
1511 @property
1512 def whereclause(self) -> Optional[ColumnElement[Any]]:
1513 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1514 statement.
1516 This assembles the current collection of WHERE criteria
1517 into a single :class:`_expression.BooleanClauseList` construct.
1520 .. versionadded:: 1.4
1522 """
1524 return BooleanClauseList._construct_for_whereclause(
1525 self._where_criteria
1526 )
1529class Update(DMLWhereBase, ValuesBase):
1530 """Represent an Update construct.
1532 The :class:`_expression.Update` object is created using the
1533 :func:`_expression.update()` function.
1535 """
1537 __visit_name__ = "update"
1539 is_update = True
1541 _traverse_internals = (
1542 [
1543 ("table", InternalTraversal.dp_clauseelement),
1544 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1545 ("_inline", InternalTraversal.dp_boolean),
1546 ("_ordered_values", InternalTraversal.dp_dml_ordered_values),
1547 ("_values", InternalTraversal.dp_dml_values),
1548 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1549 ("_hints", InternalTraversal.dp_table_hint_list),
1550 ("_return_defaults", InternalTraversal.dp_boolean),
1551 (
1552 "_return_defaults_columns",
1553 InternalTraversal.dp_clauseelement_tuple,
1554 ),
1555 ]
1556 + HasPrefixes._has_prefixes_traverse_internals
1557 + DialectKWArgs._dialect_kwargs_traverse_internals
1558 + Executable._executable_traverse_internals
1559 + HasCTE._has_ctes_traverse_internals
1560 )
1562 def __init__(self, table: _DMLTableArgument):
1563 super().__init__(table)
1565 @_generative
1566 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1567 """Specify the VALUES clause of this UPDATE statement with an explicit
1568 parameter ordering that will be maintained in the SET clause of the
1569 resulting UPDATE statement.
1571 E.g.::
1573 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1575 .. seealso::
1577 :ref:`tutorial_parameter_ordered_updates` - full example of the
1578 :meth:`_expression.Update.ordered_values` method.
1580 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1581 method
1582 supersedes the
1583 :paramref:`_expression.update.preserve_parameter_order`
1584 parameter, which will be removed in SQLAlchemy 2.0.
1586 """ # noqa: E501
1587 if self._values:
1588 raise exc.ArgumentError(
1589 "This statement already has values present"
1590 )
1591 elif self._ordered_values:
1592 raise exc.ArgumentError(
1593 "This statement already has ordered values present"
1594 )
1596 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1597 self._ordered_values = kv_generator(self, args, True)
1598 return self
1600 @_generative
1601 def inline(self) -> Self:
1602 """Make this :class:`_expression.Update` construct "inline" .
1604 When set, SQL defaults present on :class:`_schema.Column`
1605 objects via the
1606 ``default`` keyword will be compiled 'inline' into the statement and
1607 not pre-executed. This means that their values will not be available
1608 in the dictionary returned from
1609 :meth:`_engine.CursorResult.last_updated_params`.
1611 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1612 parameter
1613 is now superseded by the :meth:`_expression.Update.inline` method.
1615 """
1616 self._inline = True
1617 return self
1619 if TYPE_CHECKING:
1620 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1622 # code within this block is **programmatically,
1623 # statically generated** by tools/generate_tuple_map_overloads.py
1625 @overload
1626 def returning(
1627 self, __ent0: _TCCA[_T0]
1628 ) -> ReturningUpdate[Tuple[_T0]]: ...
1630 @overload
1631 def returning(
1632 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1633 ) -> ReturningUpdate[Tuple[_T0, _T1]]: ...
1635 @overload
1636 def returning(
1637 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1638 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2]]: ...
1640 @overload
1641 def returning(
1642 self,
1643 __ent0: _TCCA[_T0],
1644 __ent1: _TCCA[_T1],
1645 __ent2: _TCCA[_T2],
1646 __ent3: _TCCA[_T3],
1647 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3]]: ...
1649 @overload
1650 def returning(
1651 self,
1652 __ent0: _TCCA[_T0],
1653 __ent1: _TCCA[_T1],
1654 __ent2: _TCCA[_T2],
1655 __ent3: _TCCA[_T3],
1656 __ent4: _TCCA[_T4],
1657 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1659 @overload
1660 def returning(
1661 self,
1662 __ent0: _TCCA[_T0],
1663 __ent1: _TCCA[_T1],
1664 __ent2: _TCCA[_T2],
1665 __ent3: _TCCA[_T3],
1666 __ent4: _TCCA[_T4],
1667 __ent5: _TCCA[_T5],
1668 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1670 @overload
1671 def returning(
1672 self,
1673 __ent0: _TCCA[_T0],
1674 __ent1: _TCCA[_T1],
1675 __ent2: _TCCA[_T2],
1676 __ent3: _TCCA[_T3],
1677 __ent4: _TCCA[_T4],
1678 __ent5: _TCCA[_T5],
1679 __ent6: _TCCA[_T6],
1680 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1682 @overload
1683 def returning(
1684 self,
1685 __ent0: _TCCA[_T0],
1686 __ent1: _TCCA[_T1],
1687 __ent2: _TCCA[_T2],
1688 __ent3: _TCCA[_T3],
1689 __ent4: _TCCA[_T4],
1690 __ent5: _TCCA[_T5],
1691 __ent6: _TCCA[_T6],
1692 __ent7: _TCCA[_T7],
1693 ) -> ReturningUpdate[
1694 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1695 ]: ...
1697 # END OVERLOADED FUNCTIONS self.returning
1699 @overload
1700 def returning(
1701 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1702 ) -> ReturningUpdate[Any]: ...
1704 def returning(
1705 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1706 ) -> ReturningUpdate[Any]: ...
1709class ReturningUpdate(Update, TypedReturnsRows[_TP]):
1710 """Typing-only class that establishes a generic type form of
1711 :class:`.Update` which tracks returned column types.
1713 This datatype is delivered when calling the
1714 :meth:`.Update.returning` method.
1716 .. versionadded:: 2.0
1718 """
1721class Delete(DMLWhereBase, UpdateBase):
1722 """Represent a DELETE construct.
1724 The :class:`_expression.Delete` object is created using the
1725 :func:`_expression.delete()` function.
1727 """
1729 __visit_name__ = "delete"
1731 is_delete = True
1733 _traverse_internals = (
1734 [
1735 ("table", InternalTraversal.dp_clauseelement),
1736 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1737 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1738 ("_hints", InternalTraversal.dp_table_hint_list),
1739 ]
1740 + HasPrefixes._has_prefixes_traverse_internals
1741 + DialectKWArgs._dialect_kwargs_traverse_internals
1742 + Executable._executable_traverse_internals
1743 + HasCTE._has_ctes_traverse_internals
1744 )
1746 def __init__(self, table: _DMLTableArgument):
1747 self.table = coercions.expect(
1748 roles.DMLTableRole, table, apply_propagate_attrs=self
1749 )
1751 if TYPE_CHECKING:
1752 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1754 # code within this block is **programmatically,
1755 # statically generated** by tools/generate_tuple_map_overloads.py
1757 @overload
1758 def returning(
1759 self, __ent0: _TCCA[_T0]
1760 ) -> ReturningDelete[Tuple[_T0]]: ...
1762 @overload
1763 def returning(
1764 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1765 ) -> ReturningDelete[Tuple[_T0, _T1]]: ...
1767 @overload
1768 def returning(
1769 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1770 ) -> ReturningDelete[Tuple[_T0, _T1, _T2]]: ...
1772 @overload
1773 def returning(
1774 self,
1775 __ent0: _TCCA[_T0],
1776 __ent1: _TCCA[_T1],
1777 __ent2: _TCCA[_T2],
1778 __ent3: _TCCA[_T3],
1779 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3]]: ...
1781 @overload
1782 def returning(
1783 self,
1784 __ent0: _TCCA[_T0],
1785 __ent1: _TCCA[_T1],
1786 __ent2: _TCCA[_T2],
1787 __ent3: _TCCA[_T3],
1788 __ent4: _TCCA[_T4],
1789 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1791 @overload
1792 def returning(
1793 self,
1794 __ent0: _TCCA[_T0],
1795 __ent1: _TCCA[_T1],
1796 __ent2: _TCCA[_T2],
1797 __ent3: _TCCA[_T3],
1798 __ent4: _TCCA[_T4],
1799 __ent5: _TCCA[_T5],
1800 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1802 @overload
1803 def returning(
1804 self,
1805 __ent0: _TCCA[_T0],
1806 __ent1: _TCCA[_T1],
1807 __ent2: _TCCA[_T2],
1808 __ent3: _TCCA[_T3],
1809 __ent4: _TCCA[_T4],
1810 __ent5: _TCCA[_T5],
1811 __ent6: _TCCA[_T6],
1812 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1814 @overload
1815 def returning(
1816 self,
1817 __ent0: _TCCA[_T0],
1818 __ent1: _TCCA[_T1],
1819 __ent2: _TCCA[_T2],
1820 __ent3: _TCCA[_T3],
1821 __ent4: _TCCA[_T4],
1822 __ent5: _TCCA[_T5],
1823 __ent6: _TCCA[_T6],
1824 __ent7: _TCCA[_T7],
1825 ) -> ReturningDelete[
1826 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1827 ]: ...
1829 # END OVERLOADED FUNCTIONS self.returning
1831 @overload
1832 def returning(
1833 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1834 ) -> ReturningDelete[Any]: ...
1836 def returning(
1837 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1838 ) -> ReturningDelete[Any]: ...
1841class ReturningDelete(Update, TypedReturnsRows[_TP]):
1842 """Typing-only class that establishes a generic type form of
1843 :class:`.Delete` which tracks returned column types.
1845 This datatype is delivered when calling the
1846 :meth:`.Delete.returning` method.
1848 .. versionadded:: 2.0
1850 """