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"""
13from __future__ import annotations
15import collections.abc as collections_abc
16import operator
17from typing import Any
18from typing import cast
19from typing import Dict
20from typing import Iterable
21from typing import List
22from typing import MutableMapping
23from typing import NoReturn
24from typing import Optional
25from typing import overload
26from typing import Sequence
27from typing import Set
28from typing import Tuple
29from typing import Type
30from typing import TYPE_CHECKING
31from typing import TypeVar
32from typing import Union
34from . import coercions
35from . import roles
36from . import util as sql_util
37from ._typing import _TP
38from ._typing import _unexpected_kw
39from ._typing import is_column_element
40from ._typing import is_named_from_clause
41from .base import _entity_namespace_key
42from .base import _exclusive_against
43from .base import _from_objects
44from .base import _generative
45from .base import _select_iterables
46from .base import ColumnCollection
47from .base import ColumnSet
48from .base import CompileState
49from .base import DialectKWArgs
50from .base import Executable
51from .base import Generative
52from .base import HasCompileState
53from .elements import BooleanClauseList
54from .elements import ClauseElement
55from .elements import ColumnClause
56from .elements import ColumnElement
57from .elements import Null
58from .selectable import Alias
59from .selectable import ExecutableReturnsRows
60from .selectable import FromClause
61from .selectable import HasCTE
62from .selectable import HasPrefixes
63from .selectable import Join
64from .selectable import SelectLabelStyle
65from .selectable import TableClause
66from .selectable import TypedReturnsRows
67from .sqltypes import NullType
68from .visitors import InternalTraversal
69from .. import exc
70from .. import util
71from ..util.typing import Self
72from ..util.typing import TypeGuard
74if TYPE_CHECKING:
75 from ._typing import _ColumnExpressionArgument
76 from ._typing import _ColumnsClauseArgument
77 from ._typing import _DMLColumnArgument
78 from ._typing import _DMLColumnKeyMapping
79 from ._typing import _DMLTableArgument
80 from ._typing import _T0 # noqa
81 from ._typing import _T1 # noqa
82 from ._typing import _T2 # noqa
83 from ._typing import _T3 # noqa
84 from ._typing import _T4 # noqa
85 from ._typing import _T5 # noqa
86 from ._typing import _T6 # noqa
87 from ._typing import _T7 # noqa
88 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa
89 from .base import ReadOnlyColumnCollection
90 from .compiler import SQLCompiler
91 from .elements import KeyedColumnElement
92 from .selectable import _ColumnsClauseElement
93 from .selectable import _SelectIterable
94 from .selectable import Select
95 from .selectable import Selectable
97 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ...
99 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ...
101 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ...
103else:
104 isupdate = operator.attrgetter("isupdate")
105 isdelete = operator.attrgetter("isdelete")
106 isinsert = operator.attrgetter("isinsert")
109_T = TypeVar("_T", bound=Any)
111_DMLColumnElement = Union[str, ColumnClause[Any]]
112_DMLTableElement = Union[TableClause, Alias, Join]
115class DMLState(CompileState):
116 _no_parameters = True
117 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None
118 _multi_parameters: Optional[
119 List[MutableMapping[_DMLColumnElement, Any]]
120 ] = None
121 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None
122 _parameter_ordering: Optional[List[_DMLColumnElement]] = None
123 _primary_table: FromClause
124 _supports_implicit_returning = True
126 isupdate = False
127 isdelete = False
128 isinsert = False
130 statement: UpdateBase
132 def __init__(
133 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any
134 ):
135 raise NotImplementedError()
137 @classmethod
138 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]:
139 return {
140 "name": (
141 statement.table.name
142 if is_named_from_clause(statement.table)
143 else None
144 ),
145 "table": statement.table,
146 }
148 @classmethod
149 def get_returning_column_descriptions(
150 cls, statement: UpdateBase
151 ) -> List[Dict[str, Any]]:
152 return [
153 {
154 "name": c.key,
155 "type": c.type,
156 "expr": c,
157 }
158 for c in statement._all_selected_columns
159 ]
161 @property
162 def dml_table(self) -> _DMLTableElement:
163 return self.statement.table
165 if TYPE_CHECKING:
167 @classmethod
168 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ...
170 @classmethod
171 def _get_multi_crud_kv_pairs(
172 cls,
173 statement: UpdateBase,
174 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]],
175 ) -> List[Dict[_DMLColumnElement, Any]]:
176 return [
177 {
178 coercions.expect(roles.DMLColumnRole, k): v
179 for k, v in mapping.items()
180 }
181 for mapping in multi_kv_iterator
182 ]
184 @classmethod
185 def _get_crud_kv_pairs(
186 cls,
187 statement: UpdateBase,
188 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]],
189 needs_to_be_cacheable: bool,
190 ) -> List[Tuple[_DMLColumnElement, Any]]:
191 return [
192 (
193 coercions.expect(roles.DMLColumnRole, k),
194 (
195 v
196 if not needs_to_be_cacheable
197 else coercions.expect(
198 roles.ExpressionElementRole,
199 v,
200 type_=NullType(),
201 is_crud=True,
202 )
203 ),
204 )
205 for k, v in kv_iterator
206 ]
208 def _make_extra_froms(
209 self, statement: DMLWhereBase
210 ) -> Tuple[FromClause, List[FromClause]]:
211 froms: List[FromClause] = []
213 all_tables = list(sql_util.tables_from_leftmost(statement.table))
214 primary_table = all_tables[0]
215 seen = {primary_table}
217 consider = statement._where_criteria
218 if self._dict_parameters:
219 consider += tuple(self._dict_parameters.values())
221 for crit in consider:
222 for item in _from_objects(crit):
223 if not seen.intersection(item._cloned_set):
224 froms.append(item)
225 seen.update(item._cloned_set)
227 froms.extend(all_tables[1:])
228 return primary_table, froms
230 def _process_values(self, statement: ValuesBase) -> None:
231 if self._no_parameters:
232 self._dict_parameters = statement._values
233 self._no_parameters = False
235 def _process_select_values(self, statement: ValuesBase) -> None:
236 assert statement._select_names is not None
237 parameters: MutableMapping[_DMLColumnElement, Any] = {
238 name: Null() for name in statement._select_names
239 }
241 if self._no_parameters:
242 self._no_parameters = False
243 self._dict_parameters = parameters
244 else:
245 # this condition normally not reachable as the Insert
246 # does not allow this construction to occur
247 assert False, "This statement already has parameters"
249 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn:
250 raise exc.InvalidRequestError(
251 "%s construct does not support "
252 "multiple parameter sets." % statement.__visit_name__.upper()
253 )
255 def _cant_mix_formats_error(self) -> NoReturn:
256 raise exc.InvalidRequestError(
257 "Can't mix single and multiple VALUES "
258 "formats in one INSERT statement; one style appends to a "
259 "list while the other replaces values, so the intent is "
260 "ambiguous."
261 )
264@CompileState.plugin_for("default", "insert")
265class InsertDMLState(DMLState):
266 isinsert = True
268 include_table_with_column_exprs = False
270 _has_multi_parameters = False
272 def __init__(
273 self,
274 statement: Insert,
275 compiler: SQLCompiler,
276 disable_implicit_returning: bool = False,
277 **kw: Any,
278 ):
279 self.statement = statement
280 self._primary_table = statement.table
282 if disable_implicit_returning:
283 self._supports_implicit_returning = False
285 self.isinsert = True
286 if statement._select_names:
287 self._process_select_values(statement)
288 if statement._values is not None:
289 self._process_values(statement)
290 if statement._multi_values:
291 self._process_multi_values(statement)
293 @util.memoized_property
294 def _insert_col_keys(self) -> List[str]:
295 # this is also done in crud.py -> _key_getters_for_crud_column
296 return [
297 coercions.expect(roles.DMLColumnRole, col, as_key=True)
298 for col in self._dict_parameters or ()
299 ]
301 def _process_values(self, statement: ValuesBase) -> None:
302 if self._no_parameters:
303 self._has_multi_parameters = False
304 self._dict_parameters = statement._values
305 self._no_parameters = False
306 elif self._has_multi_parameters:
307 self._cant_mix_formats_error()
309 def _process_multi_values(self, statement: ValuesBase) -> None:
310 for parameters in statement._multi_values:
311 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [
312 (
313 {
314 c.key: value
315 for c, value in zip(statement.table.c, parameter_set)
316 }
317 if isinstance(parameter_set, collections_abc.Sequence)
318 else parameter_set
319 )
320 for parameter_set in parameters
321 ]
323 if self._no_parameters:
324 self._no_parameters = False
325 self._has_multi_parameters = True
326 self._multi_parameters = multi_parameters
327 self._dict_parameters = self._multi_parameters[0]
328 elif not self._has_multi_parameters:
329 self._cant_mix_formats_error()
330 else:
331 assert self._multi_parameters
332 self._multi_parameters.extend(multi_parameters)
335@CompileState.plugin_for("default", "update")
336class UpdateDMLState(DMLState):
337 isupdate = True
339 include_table_with_column_exprs = False
341 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any):
342 self.statement = statement
344 self.isupdate = True
345 if statement._ordered_values is not None:
346 self._process_ordered_values(statement)
347 elif statement._values is not None:
348 self._process_values(statement)
349 elif statement._multi_values:
350 self._no_multi_values_supported(statement)
351 t, ef = self._make_extra_froms(statement)
352 self._primary_table = t
353 self._extra_froms = ef
355 self.is_multitable = mt = ef
356 self.include_table_with_column_exprs = bool(
357 mt and compiler.render_table_with_column_in_update_from
358 )
360 def _process_ordered_values(self, statement: ValuesBase) -> None:
361 parameters = statement._ordered_values
363 if self._no_parameters:
364 self._no_parameters = False
365 assert parameters is not None
366 self._dict_parameters = dict(parameters)
367 self._ordered_values = parameters
368 self._parameter_ordering = [key for key, value in parameters]
369 else:
370 raise exc.InvalidRequestError(
371 "Can only invoke ordered_values() once, and not mixed "
372 "with any other values() call"
373 )
376@CompileState.plugin_for("default", "delete")
377class DeleteDMLState(DMLState):
378 isdelete = True
380 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any):
381 self.statement = statement
383 self.isdelete = True
384 t, ef = self._make_extra_froms(statement)
385 self._primary_table = t
386 self._extra_froms = ef
387 self.is_multitable = ef
390class UpdateBase(
391 roles.DMLRole,
392 HasCTE,
393 HasCompileState,
394 DialectKWArgs,
395 HasPrefixes,
396 Generative,
397 ExecutableReturnsRows,
398 ClauseElement,
399):
400 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
402 __visit_name__ = "update_base"
404 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = (
405 util.EMPTY_DICT
406 )
407 named_with_column = False
409 _label_style: SelectLabelStyle = (
410 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY
411 )
412 table: _DMLTableElement
414 _return_defaults = False
415 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = (
416 None
417 )
418 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None
419 _returning: Tuple[_ColumnsClauseElement, ...] = ()
421 is_dml = True
423 def _generate_fromclause_column_proxies(
424 self,
425 fromclause: FromClause,
426 columns: ColumnCollection[str, KeyedColumnElement[Any]],
427 primary_key: ColumnSet,
428 foreign_keys: Set[KeyedColumnElement[Any]],
429 ) -> None:
430 prox = [
431 c._make_proxy(
432 fromclause,
433 key=proxy_key,
434 name=required_label_name,
435 name_is_truncatable=True,
436 primary_key=primary_key,
437 foreign_keys=foreign_keys,
438 )
439 for (
440 required_label_name,
441 proxy_key,
442 fallback_label_name,
443 c,
444 repeated,
445 ) in (self._generate_columns_plus_names(False))
446 if is_column_element(c)
447 ]
449 columns._populate_separate_keys(prox)
451 def params(self, *arg: Any, **kw: Any) -> NoReturn:
452 """Set the parameters for the statement.
454 This method raises ``NotImplementedError`` on the base class,
455 and is overridden by :class:`.ValuesBase` to provide the
456 SET/VALUES clause of UPDATE and INSERT.
458 """
459 raise NotImplementedError(
460 "params() is not supported for INSERT/UPDATE/DELETE statements."
461 " To set the values for an INSERT or UPDATE statement, use"
462 " stmt.values(**parameters)."
463 )
465 @_generative
466 def with_dialect_options(self, **opt: Any) -> Self:
467 """Add dialect options to this INSERT/UPDATE/DELETE object.
469 e.g.::
471 upd = table.update().dialect_options(mysql_limit=10)
473 .. versionadded: 1.4 - this method supersedes the dialect options
474 associated with the constructor.
477 """
478 self._validate_dialect_kwargs(opt)
479 return self
481 @_generative
482 def return_defaults(
483 self,
484 *cols: _DMLColumnArgument,
485 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None,
486 sort_by_parameter_order: bool = False,
487 ) -> Self:
488 """Make use of a :term:`RETURNING` clause for the purpose
489 of fetching server-side expressions and defaults, for supporting
490 backends only.
492 .. deepalchemy::
494 The :meth:`.UpdateBase.return_defaults` method is used by the ORM
495 for its internal work in fetching newly generated primary key
496 and server default values, in particular to provide the underlying
497 implementation of the :paramref:`_orm.Mapper.eager_defaults`
498 ORM feature as well as to allow RETURNING support with bulk
499 ORM inserts. Its behavior is fairly idiosyncratic
500 and is not really intended for general use. End users should
501 stick with using :meth:`.UpdateBase.returning` in order to
502 add RETURNING clauses to their INSERT, UPDATE and DELETE
503 statements.
505 Normally, a single row INSERT statement will automatically populate the
506 :attr:`.CursorResult.inserted_primary_key` attribute when executed,
507 which stores the primary key of the row that was just inserted in the
508 form of a :class:`.Row` object with column names as named tuple keys
509 (and the :attr:`.Row._mapping` view fully populated as well). The
510 dialect in use chooses the strategy to use in order to populate this
511 data; if it was generated using server-side defaults and / or SQL
512 expressions, dialect-specific approaches such as ``cursor.lastrowid``
513 or ``RETURNING`` are typically used to acquire the new primary key
514 value.
516 However, when the statement is modified by calling
517 :meth:`.UpdateBase.return_defaults` before executing the statement,
518 additional behaviors take place **only** for backends that support
519 RETURNING and for :class:`.Table` objects that maintain the
520 :paramref:`.Table.implicit_returning` parameter at its default value of
521 ``True``. In these cases, when the :class:`.CursorResult` is returned
522 from the statement's execution, not only will
523 :attr:`.CursorResult.inserted_primary_key` be populated as always, the
524 :attr:`.CursorResult.returned_defaults` attribute will also be
525 populated with a :class:`.Row` named-tuple representing the full range
526 of server generated
527 values from that single row, including values for any columns that
528 specify :paramref:`_schema.Column.server_default` or which make use of
529 :paramref:`_schema.Column.default` using a SQL expression.
531 When invoking INSERT statements with multiple rows using
532 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the
533 :meth:`.UpdateBase.return_defaults` modifier will have the effect of
534 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and
535 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being
536 fully populated with lists of :class:`.Row` objects representing newly
537 inserted primary key values as well as newly inserted server generated
538 values for each row inserted. The
539 :attr:`.CursorResult.inserted_primary_key` and
540 :attr:`.CursorResult.returned_defaults` attributes will also continue
541 to be populated with the first row of these two collections.
543 If the backend does not support RETURNING or the :class:`.Table` in use
544 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING
545 clause is added and no additional data is fetched, however the
546 INSERT, UPDATE or DELETE statement proceeds normally.
548 E.g.::
550 stmt = table.insert().values(data="newdata").return_defaults()
552 result = connection.execute(stmt)
554 server_created_at = result.returned_defaults["created_at"]
556 When used against an UPDATE statement
557 :meth:`.UpdateBase.return_defaults` instead looks for columns that
558 include :paramref:`_schema.Column.onupdate` or
559 :paramref:`_schema.Column.server_onupdate` parameters assigned, when
560 constructing the columns that will be included in the RETURNING clause
561 by default if explicit columns were not specified. When used against a
562 DELETE statement, no columns are included in RETURNING by default, they
563 instead must be specified explicitly as there are no columns that
564 normally change values when a DELETE statement proceeds.
566 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported
567 for DELETE statements also and has been moved from
568 :class:`.ValuesBase` to :class:`.UpdateBase`.
570 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive
571 against the :meth:`.UpdateBase.returning` method and errors will be
572 raised during the SQL compilation process if both are used at the same
573 time on one statement. The RETURNING clause of the INSERT, UPDATE or
574 DELETE statement is therefore controlled by only one of these methods
575 at a time.
577 The :meth:`.UpdateBase.return_defaults` method differs from
578 :meth:`.UpdateBase.returning` in these ways:
580 1. :meth:`.UpdateBase.return_defaults` method causes the
581 :attr:`.CursorResult.returned_defaults` collection to be populated
582 with the first row from the RETURNING result. This attribute is not
583 populated when using :meth:`.UpdateBase.returning`.
585 2. :meth:`.UpdateBase.return_defaults` is compatible with existing
586 logic used to fetch auto-generated primary key values that are then
587 populated into the :attr:`.CursorResult.inserted_primary_key`
588 attribute. By contrast, using :meth:`.UpdateBase.returning` will
589 have the effect of the :attr:`.CursorResult.inserted_primary_key`
590 attribute being left unpopulated.
592 3. :meth:`.UpdateBase.return_defaults` can be called against any
593 backend. Backends that don't support RETURNING will skip the usage
594 of the feature, rather than raising an exception, *unless*
595 ``supplemental_cols`` is passed. The return value
596 of :attr:`_engine.CursorResult.returned_defaults` will be ``None``
597 for backends that don't support RETURNING or for which the target
598 :class:`.Table` sets :paramref:`.Table.implicit_returning` to
599 ``False``.
601 4. An INSERT statement invoked with executemany() is supported if the
602 backend database driver supports the
603 :ref:`insertmanyvalues <engine_insertmanyvalues>`
604 feature which is now supported by most SQLAlchemy-included backends.
605 When executemany is used, the
606 :attr:`_engine.CursorResult.returned_defaults_rows` and
607 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors
608 will return the inserted defaults and primary keys.
610 .. versionadded:: 1.4 Added
611 :attr:`_engine.CursorResult.returned_defaults_rows` and
612 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors.
613 In version 2.0, the underlying implementation which fetches and
614 populates the data for these attributes was generalized to be
615 supported by most backends, whereas in 1.4 they were only
616 supported by the ``psycopg2`` driver.
619 :param cols: optional list of column key names or
620 :class:`_schema.Column` that acts as a filter for those columns that
621 will be fetched.
622 :param supplemental_cols: optional list of RETURNING expressions,
623 in the same form as one would pass to the
624 :meth:`.UpdateBase.returning` method. When present, the additional
625 columns will be included in the RETURNING clause, and the
626 :class:`.CursorResult` object will be "rewound" when returned, so
627 that methods like :meth:`.CursorResult.all` will return new rows
628 mostly as though the statement used :meth:`.UpdateBase.returning`
629 directly. However, unlike when using :meth:`.UpdateBase.returning`
630 directly, the **order of the columns is undefined**, so can only be
631 targeted using names or :attr:`.Row._mapping` keys; they cannot
632 reliably be targeted positionally.
634 .. versionadded:: 2.0
636 :param sort_by_parameter_order: for a batch INSERT that is being
637 executed against multiple parameter sets, organize the results of
638 RETURNING so that the returned rows correspond to the order of
639 parameter sets passed in. This applies only to an :term:`executemany`
640 execution for supporting dialects and typically makes use of the
641 :term:`insertmanyvalues` feature.
643 .. versionadded:: 2.0.10
645 .. seealso::
647 :ref:`engine_insertmanyvalues_returning_order` - background on
648 sorting of RETURNING rows for bulk INSERT
650 .. seealso::
652 :meth:`.UpdateBase.returning`
654 :attr:`_engine.CursorResult.returned_defaults`
656 :attr:`_engine.CursorResult.returned_defaults_rows`
658 :attr:`_engine.CursorResult.inserted_primary_key`
660 :attr:`_engine.CursorResult.inserted_primary_key_rows`
662 """
664 if self._return_defaults:
665 # note _return_defaults_columns = () means return all columns,
666 # so if we have been here before, only update collection if there
667 # are columns in the collection
668 if self._return_defaults_columns and cols:
669 self._return_defaults_columns = tuple(
670 util.OrderedSet(self._return_defaults_columns).union(
671 coercions.expect(roles.ColumnsClauseRole, c)
672 for c in cols
673 )
674 )
675 else:
676 # set for all columns
677 self._return_defaults_columns = ()
678 else:
679 self._return_defaults_columns = tuple(
680 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
681 )
682 self._return_defaults = True
683 if sort_by_parameter_order:
684 if not self.is_insert:
685 raise exc.ArgumentError(
686 "The 'sort_by_parameter_order' argument to "
687 "return_defaults() only applies to INSERT statements"
688 )
689 self._sort_by_parameter_order = True
690 if supplemental_cols:
691 # uniquifying while also maintaining order (the maintain of order
692 # is for test suites but also for vertical splicing
693 supplemental_col_tup = (
694 coercions.expect(roles.ColumnsClauseRole, c)
695 for c in supplemental_cols
696 )
698 if self._supplemental_returning is None:
699 self._supplemental_returning = tuple(
700 util.unique_list(supplemental_col_tup)
701 )
702 else:
703 self._supplemental_returning = tuple(
704 util.unique_list(
705 self._supplemental_returning
706 + tuple(supplemental_col_tup)
707 )
708 )
710 return self
712 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
713 """Return ``True`` if this :class:`.ReturnsRows` is
714 'derived' from the given :class:`.FromClause`.
716 Since these are DMLs, we dont want such statements ever being adapted
717 so we return False for derives.
719 """
720 return False
722 @_generative
723 def returning(
724 self,
725 *cols: _ColumnsClauseArgument[Any],
726 sort_by_parameter_order: bool = False,
727 **__kw: Any,
728 ) -> UpdateBase:
729 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
731 e.g.:
733 .. sourcecode:: pycon+sql
735 >>> stmt = (
736 ... table.update()
737 ... .where(table.c.data == "value")
738 ... .values(status="X")
739 ... .returning(table.c.server_flag, table.c.updated_timestamp)
740 ... )
741 >>> print(stmt)
742 {printsql}UPDATE some_table SET status=:status
743 WHERE some_table.data = :data_1
744 RETURNING some_table.server_flag, some_table.updated_timestamp
746 The method may be invoked multiple times to add new entries to the
747 list of expressions to be returned.
749 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to
750 add new entries to the list of expressions to be returned.
752 The given collection of column expressions should be derived from the
753 table that is the target of the INSERT, UPDATE, or DELETE. While
754 :class:`_schema.Column` objects are typical, the elements can also be
755 expressions:
757 .. sourcecode:: pycon+sql
759 >>> stmt = table.insert().returning(
760 ... (table.c.first_name + " " + table.c.last_name).label("fullname")
761 ... )
762 >>> print(stmt)
763 {printsql}INSERT INTO some_table (first_name, last_name)
764 VALUES (:first_name, :last_name)
765 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname
767 Upon compilation, a RETURNING clause, or database equivalent,
768 will be rendered within the statement. For INSERT and UPDATE,
769 the values are the newly inserted/updated values. For DELETE,
770 the values are those of the rows which were deleted.
772 Upon execution, the values of the columns to be returned are made
773 available via the result set and can be iterated using
774 :meth:`_engine.CursorResult.fetchone` and similar.
775 For DBAPIs which do not
776 natively support returning values (i.e. cx_oracle), SQLAlchemy will
777 approximate this behavior at the result level so that a reasonable
778 amount of behavioral neutrality is provided.
780 Note that not all databases/DBAPIs
781 support RETURNING. For those backends with no support,
782 an exception is raised upon compilation and/or execution.
783 For those who do support it, the functionality across backends
784 varies greatly, including restrictions on executemany()
785 and other statements which return multiple rows. Please
786 read the documentation notes for the database in use in
787 order to determine the availability of RETURNING.
789 :param \*cols: series of columns, SQL expressions, or whole tables
790 entities to be returned.
791 :param sort_by_parameter_order: for a batch INSERT that is being
792 executed against multiple parameter sets, organize the results of
793 RETURNING so that the returned rows correspond to the order of
794 parameter sets passed in. This applies only to an :term:`executemany`
795 execution for supporting dialects and typically makes use of the
796 :term:`insertmanyvalues` feature.
798 .. versionadded:: 2.0.10
800 .. seealso::
802 :ref:`engine_insertmanyvalues_returning_order` - background on
803 sorting of RETURNING rows for bulk INSERT (Core level discussion)
805 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of
806 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion)
808 .. seealso::
810 :meth:`.UpdateBase.return_defaults` - an alternative method tailored
811 towards efficient fetching of server-side defaults and triggers
812 for single-row INSERTs or UPDATEs.
814 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`
816 """ # noqa: E501
817 if __kw:
818 raise _unexpected_kw("UpdateBase.returning()", __kw)
819 if self._return_defaults:
820 raise exc.InvalidRequestError(
821 "return_defaults() is already configured on this statement"
822 )
823 self._returning += tuple(
824 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
825 )
826 if sort_by_parameter_order:
827 if not self.is_insert:
828 raise exc.ArgumentError(
829 "The 'sort_by_parameter_order' argument to returning() "
830 "only applies to INSERT statements"
831 )
832 self._sort_by_parameter_order = True
833 return self
835 def corresponding_column(
836 self, column: KeyedColumnElement[Any], require_embedded: bool = False
837 ) -> Optional[ColumnElement[Any]]:
838 return self.exported_columns.corresponding_column(
839 column, require_embedded=require_embedded
840 )
842 @util.ro_memoized_property
843 def _all_selected_columns(self) -> _SelectIterable:
844 return [c for c in _select_iterables(self._returning)]
846 @util.ro_memoized_property
847 def exported_columns(
848 self,
849 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]:
850 """Return the RETURNING columns as a column collection for this
851 statement.
853 .. versionadded:: 1.4
855 """
856 return ColumnCollection(
857 (c.key, c)
858 for c in self._all_selected_columns
859 if is_column_element(c)
860 ).as_readonly()
862 @_generative
863 def with_hint(
864 self,
865 text: str,
866 selectable: Optional[_DMLTableArgument] = None,
867 dialect_name: str = "*",
868 ) -> Self:
869 """Add a table hint for a single table to this
870 INSERT/UPDATE/DELETE statement.
872 .. note::
874 :meth:`.UpdateBase.with_hint` currently applies only to
875 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
876 :meth:`.UpdateBase.prefix_with`.
878 The text of the hint is rendered in the appropriate
879 location for the database backend in use, relative
880 to the :class:`_schema.Table` that is the subject of this
881 statement, or optionally to that of the given
882 :class:`_schema.Table` passed as the ``selectable`` argument.
884 The ``dialect_name`` option will limit the rendering of a particular
885 hint to a particular backend. Such as, to add a hint
886 that only takes effect for SQL Server::
888 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
890 :param text: Text of the hint.
891 :param selectable: optional :class:`_schema.Table` that specifies
892 an element of the FROM clause within an UPDATE or DELETE
893 to be the subject of the hint - applies only to certain backends.
894 :param dialect_name: defaults to ``*``, if specified as the name
895 of a particular dialect, will apply these hints only when
896 that dialect is in use.
897 """
898 if selectable is None:
899 selectable = self.table
900 else:
901 selectable = coercions.expect(roles.DMLTableRole, selectable)
902 self._hints = self._hints.union({(selectable, dialect_name): text})
903 return self
905 @property
906 def entity_description(self) -> Dict[str, Any]:
907 """Return a :term:`plugin-enabled` description of the table and/or
908 entity which this DML construct is operating against.
910 This attribute is generally useful when using the ORM, as an
911 extended structure which includes information about mapped
912 entities is returned. The section :ref:`queryguide_inspection`
913 contains more background.
915 For a Core statement, the structure returned by this accessor
916 is derived from the :attr:`.UpdateBase.table` attribute, and
917 refers to the :class:`.Table` being inserted, updated, or deleted::
919 >>> stmt = insert(user_table)
920 >>> stmt.entity_description
921 {
922 "name": "user_table",
923 "table": Table("user_table", ...)
924 }
926 .. versionadded:: 1.4.33
928 .. seealso::
930 :attr:`.UpdateBase.returning_column_descriptions`
932 :attr:`.Select.column_descriptions` - entity information for
933 a :func:`.select` construct
935 :ref:`queryguide_inspection` - ORM background
937 """
938 meth = DMLState.get_plugin_class(self).get_entity_description
939 return meth(self)
941 @property
942 def returning_column_descriptions(self) -> List[Dict[str, Any]]:
943 """Return a :term:`plugin-enabled` description of the columns
944 which this DML construct is RETURNING against, in other words
945 the expressions established as part of :meth:`.UpdateBase.returning`.
947 This attribute is generally useful when using the ORM, as an
948 extended structure which includes information about mapped
949 entities is returned. The section :ref:`queryguide_inspection`
950 contains more background.
952 For a Core statement, the structure returned by this accessor is
953 derived from the same objects that are returned by the
954 :attr:`.UpdateBase.exported_columns` accessor::
956 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name)
957 >>> stmt.entity_description
958 [
959 {
960 "name": "id",
961 "type": Integer,
962 "expr": Column("id", Integer(), table=<user>, ...)
963 },
964 {
965 "name": "name",
966 "type": String(),
967 "expr": Column("name", String(), table=<user>, ...)
968 },
969 ]
971 .. versionadded:: 1.4.33
973 .. seealso::
975 :attr:`.UpdateBase.entity_description`
977 :attr:`.Select.column_descriptions` - entity information for
978 a :func:`.select` construct
980 :ref:`queryguide_inspection` - ORM background
982 """ # noqa: E501
983 meth = DMLState.get_plugin_class(
984 self
985 ).get_returning_column_descriptions
986 return meth(self)
989class ValuesBase(UpdateBase):
990 """Supplies support for :meth:`.ValuesBase.values` to
991 INSERT and UPDATE constructs."""
993 __visit_name__ = "values_base"
995 _supports_multi_parameters = False
997 select: Optional[Select[Any]] = None
998 """SELECT statement for INSERT .. FROM SELECT"""
1000 _post_values_clause: Optional[ClauseElement] = None
1001 """used by extensions to Insert etc. to add additional syntacitcal
1002 constructs, e.g. ON CONFLICT etc."""
1004 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None
1005 _multi_values: Tuple[
1006 Union[
1007 Sequence[Dict[_DMLColumnElement, Any]],
1008 Sequence[Sequence[Any]],
1009 ],
1010 ...,
1011 ] = ()
1013 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None
1015 _select_names: Optional[List[str]] = None
1016 _inline: bool = False
1018 def __init__(self, table: _DMLTableArgument):
1019 self.table = coercions.expect(
1020 roles.DMLTableRole, table, apply_propagate_attrs=self
1021 )
1023 @_generative
1024 @_exclusive_against(
1025 "_select_names",
1026 "_ordered_values",
1027 msgs={
1028 "_select_names": "This construct already inserts from a SELECT",
1029 "_ordered_values": "This statement already has ordered "
1030 "values present",
1031 },
1032 )
1033 def values(
1034 self,
1035 *args: Union[
1036 _DMLColumnKeyMapping[Any],
1037 Sequence[Any],
1038 ],
1039 **kwargs: Any,
1040 ) -> Self:
1041 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
1042 clause for an UPDATE.
1044 Note that the :class:`_expression.Insert` and
1045 :class:`_expression.Update`
1046 constructs support
1047 per-execution time formatting of the VALUES and/or SET clauses,
1048 based on the arguments passed to :meth:`_engine.Connection.execute`.
1049 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
1050 particular set of parameters into the statement.
1052 Multiple calls to :meth:`.ValuesBase.values` will produce a new
1053 construct, each one with the parameter list modified to include
1054 the new parameters sent. In the typical case of a single
1055 dictionary of parameters, the newly passed keys will replace
1056 the same keys in the previous construct. In the case of a list-based
1057 "multiple values" construct, each new list of values is extended
1058 onto the existing list of values.
1060 :param \**kwargs: key value pairs representing the string key
1061 of a :class:`_schema.Column`
1062 mapped to the value to be rendered into the
1063 VALUES or SET clause::
1065 users.insert().values(name="some name")
1067 users.update().where(users.c.id == 5).values(name="some name")
1069 :param \*args: As an alternative to passing key/value parameters,
1070 a dictionary, tuple, or list of dictionaries or tuples can be passed
1071 as a single positional argument in order to form the VALUES or
1072 SET clause of the statement. The forms that are accepted vary
1073 based on whether this is an :class:`_expression.Insert` or an
1074 :class:`_expression.Update` construct.
1076 For either an :class:`_expression.Insert` or
1077 :class:`_expression.Update`
1078 construct, a single dictionary can be passed, which works the same as
1079 that of the kwargs form::
1081 users.insert().values({"name": "some name"})
1083 users.update().values({"name": "some new name"})
1085 Also for either form but more typically for the
1086 :class:`_expression.Insert` construct, a tuple that contains an
1087 entry for every column in the table is also accepted::
1089 users.insert().values((5, "some name"))
1091 The :class:`_expression.Insert` construct also supports being
1092 passed a list of dictionaries or full-table-tuples, which on the
1093 server will render the less common SQL syntax of "multiple values" -
1094 this syntax is supported on backends such as SQLite, PostgreSQL,
1095 MySQL, but not necessarily others::
1097 users.insert().values(
1098 [
1099 {"name": "some name"},
1100 {"name": "some other name"},
1101 {"name": "yet another name"},
1102 ]
1103 )
1105 The above form would render a multiple VALUES statement similar to:
1107 .. sourcecode:: sql
1109 INSERT INTO users (name) VALUES
1110 (:name_1),
1111 (:name_2),
1112 (:name_3)
1114 It is essential to note that **passing multiple values is
1115 NOT the same as using traditional executemany() form**. The above
1116 syntax is a **special** syntax not typically used. To emit an
1117 INSERT statement against multiple rows, the normal method is
1118 to pass a multiple values list to the
1119 :meth:`_engine.Connection.execute`
1120 method, which is supported by all database backends and is generally
1121 more efficient for a very large number of parameters.
1123 .. seealso::
1125 :ref:`tutorial_multiple_parameters` - an introduction to
1126 the traditional Core method of multiple parameter set
1127 invocation for INSERTs and other statements.
1129 :ref:`tutorial_core_insert_values_clause` - Insert tutorial
1130 detailing alternatives to the multiple values syntax.
1132 The UPDATE construct also supports rendering the SET parameters
1133 in a specific order. For this feature refer to the
1134 :meth:`_expression.Update.ordered_values` method.
1136 .. seealso::
1138 :meth:`_expression.Update.ordered_values`
1141 """
1142 if args:
1143 # positional case. this is currently expensive. we don't
1144 # yet have positional-only args so we have to check the length.
1145 # then we need to check multiparams vs. single dictionary.
1146 # since the parameter format is needed in order to determine
1147 # a cache key, we need to determine this up front.
1148 arg = args[0]
1150 if kwargs:
1151 raise exc.ArgumentError(
1152 "Can't pass positional and kwargs to values() "
1153 "simultaneously"
1154 )
1155 elif len(args) > 1:
1156 raise exc.ArgumentError(
1157 "Only a single dictionary/tuple or list of "
1158 "dictionaries/tuples is accepted positionally."
1159 )
1161 elif isinstance(arg, collections_abc.Sequence):
1162 if arg and isinstance(arg[0], dict):
1163 multi_kv_generator = DMLState.get_plugin_class(
1164 self
1165 )._get_multi_crud_kv_pairs
1166 self._multi_values += (multi_kv_generator(self, arg),)
1167 return self
1169 if arg and isinstance(arg[0], (list, tuple)):
1170 self._multi_values += (arg,)
1171 return self
1173 if TYPE_CHECKING:
1174 # crud.py raises during compilation if this is not the
1175 # case
1176 assert isinstance(self, Insert)
1178 # tuple values
1179 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1181 else:
1182 # kwarg path. this is the most common path for non-multi-params
1183 # so this is fairly quick.
1184 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1185 if args:
1186 raise exc.ArgumentError(
1187 "Only a single dictionary/tuple or list of "
1188 "dictionaries/tuples is accepted positionally."
1189 )
1191 # for top level values(), convert literals to anonymous bound
1192 # parameters at statement construction time, so that these values can
1193 # participate in the cache key process like any other ClauseElement.
1194 # crud.py now intercepts bound parameters with unique=True from here
1195 # and ensures they get the "crud"-style name when rendered.
1197 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1198 coerced_arg = dict(kv_generator(self, arg.items(), True))
1199 if self._values:
1200 self._values = self._values.union(coerced_arg)
1201 else:
1202 self._values = util.immutabledict(coerced_arg)
1203 return self
1206class Insert(ValuesBase):
1207 """Represent an INSERT construct.
1209 The :class:`_expression.Insert` object is created using the
1210 :func:`_expression.insert()` function.
1212 """
1214 __visit_name__ = "insert"
1216 _supports_multi_parameters = True
1218 select = None
1219 include_insert_from_select_defaults = False
1221 _sort_by_parameter_order: bool = False
1223 is_insert = True
1225 table: TableClause
1227 _traverse_internals = (
1228 [
1229 ("table", InternalTraversal.dp_clauseelement),
1230 ("_inline", InternalTraversal.dp_boolean),
1231 ("_select_names", InternalTraversal.dp_string_list),
1232 ("_values", InternalTraversal.dp_dml_values),
1233 ("_multi_values", InternalTraversal.dp_dml_multi_values),
1234 ("select", InternalTraversal.dp_clauseelement),
1235 ("_post_values_clause", InternalTraversal.dp_clauseelement),
1236 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1237 ("_hints", InternalTraversal.dp_table_hint_list),
1238 ("_return_defaults", InternalTraversal.dp_boolean),
1239 (
1240 "_return_defaults_columns",
1241 InternalTraversal.dp_clauseelement_tuple,
1242 ),
1243 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1244 ]
1245 + HasPrefixes._has_prefixes_traverse_internals
1246 + DialectKWArgs._dialect_kwargs_traverse_internals
1247 + Executable._executable_traverse_internals
1248 + HasCTE._has_ctes_traverse_internals
1249 )
1251 def __init__(self, table: _DMLTableArgument):
1252 super().__init__(table)
1254 @_generative
1255 def inline(self) -> Self:
1256 """Make this :class:`_expression.Insert` construct "inline" .
1258 When set, no attempt will be made to retrieve the
1259 SQL-generated default values to be provided within the statement;
1260 in particular,
1261 this allows SQL expressions to be rendered 'inline' within the
1262 statement without the need to pre-execute them beforehand; for
1263 backends that support "returning", this turns off the "implicit
1264 returning" feature for the statement.
1267 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1268 parameter
1269 is now superseded by the :meth:`_expression.Insert.inline` method.
1271 """
1272 self._inline = True
1273 return self
1275 @_generative
1276 def from_select(
1277 self,
1278 names: Sequence[_DMLColumnArgument],
1279 select: Selectable,
1280 include_defaults: bool = True,
1281 ) -> Self:
1282 """Return a new :class:`_expression.Insert` construct which represents
1283 an ``INSERT...FROM SELECT`` statement.
1285 e.g.::
1287 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1288 ins = table2.insert().from_select(["a", "b"], sel)
1290 :param names: a sequence of string column names or
1291 :class:`_schema.Column`
1292 objects representing the target columns.
1293 :param select: a :func:`_expression.select` construct,
1294 :class:`_expression.FromClause`
1295 or other construct which resolves into a
1296 :class:`_expression.FromClause`,
1297 such as an ORM :class:`_query.Query` object, etc. The order of
1298 columns returned from this FROM clause should correspond to the
1299 order of columns sent as the ``names`` parameter; while this
1300 is not checked before passing along to the database, the database
1301 would normally raise an exception if these column lists don't
1302 correspond.
1303 :param include_defaults: if True, non-server default values and
1304 SQL expressions as specified on :class:`_schema.Column` objects
1305 (as documented in :ref:`metadata_defaults_toplevel`) not
1306 otherwise specified in the list of names will be rendered
1307 into the INSERT and SELECT statements, so that these values are also
1308 included in the data to be inserted.
1310 .. note:: A Python-side default that uses a Python callable function
1311 will only be invoked **once** for the whole statement, and **not
1312 per row**.
1314 """
1316 if self._values:
1317 raise exc.InvalidRequestError(
1318 "This construct already inserts value expressions"
1319 )
1321 self._select_names = [
1322 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1323 for name in names
1324 ]
1325 self._inline = True
1326 self.include_insert_from_select_defaults = include_defaults
1327 self.select = coercions.expect(roles.DMLSelectRole, select)
1328 return self
1330 if TYPE_CHECKING:
1331 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1333 # code within this block is **programmatically,
1334 # statically generated** by tools/generate_tuple_map_overloads.py
1336 @overload
1337 def returning(
1338 self, __ent0: _TCCA[_T0], *, sort_by_parameter_order: bool = False
1339 ) -> ReturningInsert[Tuple[_T0]]: ...
1341 @overload
1342 def returning(
1343 self,
1344 __ent0: _TCCA[_T0],
1345 __ent1: _TCCA[_T1],
1346 *,
1347 sort_by_parameter_order: bool = False,
1348 ) -> ReturningInsert[Tuple[_T0, _T1]]: ...
1350 @overload
1351 def returning(
1352 self,
1353 __ent0: _TCCA[_T0],
1354 __ent1: _TCCA[_T1],
1355 __ent2: _TCCA[_T2],
1356 *,
1357 sort_by_parameter_order: bool = False,
1358 ) -> ReturningInsert[Tuple[_T0, _T1, _T2]]: ...
1360 @overload
1361 def returning(
1362 self,
1363 __ent0: _TCCA[_T0],
1364 __ent1: _TCCA[_T1],
1365 __ent2: _TCCA[_T2],
1366 __ent3: _TCCA[_T3],
1367 *,
1368 sort_by_parameter_order: bool = False,
1369 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3]]: ...
1371 @overload
1372 def returning(
1373 self,
1374 __ent0: _TCCA[_T0],
1375 __ent1: _TCCA[_T1],
1376 __ent2: _TCCA[_T2],
1377 __ent3: _TCCA[_T3],
1378 __ent4: _TCCA[_T4],
1379 *,
1380 sort_by_parameter_order: bool = False,
1381 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1383 @overload
1384 def returning(
1385 self,
1386 __ent0: _TCCA[_T0],
1387 __ent1: _TCCA[_T1],
1388 __ent2: _TCCA[_T2],
1389 __ent3: _TCCA[_T3],
1390 __ent4: _TCCA[_T4],
1391 __ent5: _TCCA[_T5],
1392 *,
1393 sort_by_parameter_order: bool = False,
1394 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1396 @overload
1397 def returning(
1398 self,
1399 __ent0: _TCCA[_T0],
1400 __ent1: _TCCA[_T1],
1401 __ent2: _TCCA[_T2],
1402 __ent3: _TCCA[_T3],
1403 __ent4: _TCCA[_T4],
1404 __ent5: _TCCA[_T5],
1405 __ent6: _TCCA[_T6],
1406 *,
1407 sort_by_parameter_order: bool = False,
1408 ) -> ReturningInsert[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1410 @overload
1411 def returning(
1412 self,
1413 __ent0: _TCCA[_T0],
1414 __ent1: _TCCA[_T1],
1415 __ent2: _TCCA[_T2],
1416 __ent3: _TCCA[_T3],
1417 __ent4: _TCCA[_T4],
1418 __ent5: _TCCA[_T5],
1419 __ent6: _TCCA[_T6],
1420 __ent7: _TCCA[_T7],
1421 *,
1422 sort_by_parameter_order: bool = False,
1423 ) -> ReturningInsert[
1424 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1425 ]: ...
1427 # END OVERLOADED FUNCTIONS self.returning
1429 @overload
1430 def returning(
1431 self,
1432 *cols: _ColumnsClauseArgument[Any],
1433 sort_by_parameter_order: bool = False,
1434 **__kw: Any,
1435 ) -> ReturningInsert[Any]: ...
1437 def returning(
1438 self,
1439 *cols: _ColumnsClauseArgument[Any],
1440 sort_by_parameter_order: bool = False,
1441 **__kw: Any,
1442 ) -> ReturningInsert[Any]: ...
1445class ReturningInsert(Insert, TypedReturnsRows[_TP]):
1446 """Typing-only class that establishes a generic type form of
1447 :class:`.Insert` which tracks returned column types.
1449 This datatype is delivered when calling the
1450 :meth:`.Insert.returning` method.
1452 .. versionadded:: 2.0
1454 """
1457class DMLWhereBase:
1458 table: _DMLTableElement
1459 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1461 @_generative
1462 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1463 """Return a new construct with the given expression(s) added to
1464 its WHERE clause, joined to the existing clause via AND, if any.
1466 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1467 support multiple-table forms, including database-specific
1468 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1469 don't have multiple-table support, a backend agnostic approach
1470 to using multiple tables is to make use of correlated subqueries.
1471 See the linked tutorial sections below for examples.
1473 .. seealso::
1475 :ref:`tutorial_correlated_updates`
1477 :ref:`tutorial_update_from`
1479 :ref:`tutorial_multi_table_deletes`
1481 """
1483 for criterion in whereclause:
1484 where_criteria: ColumnElement[Any] = coercions.expect(
1485 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1486 )
1487 self._where_criteria += (where_criteria,)
1488 return self
1490 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1491 """A synonym for the :meth:`_dml.DMLWhereBase.where` method.
1493 .. versionadded:: 1.4
1495 """
1497 return self.where(*criteria)
1499 def _filter_by_zero(self) -> _DMLTableElement:
1500 return self.table
1502 def filter_by(self, **kwargs: Any) -> Self:
1503 r"""apply the given filtering criterion as a WHERE clause
1504 to this select.
1506 """
1507 from_entity = self._filter_by_zero()
1509 clauses = [
1510 _entity_namespace_key(from_entity, key) == value
1511 for key, value in kwargs.items()
1512 ]
1513 return self.filter(*clauses)
1515 @property
1516 def whereclause(self) -> Optional[ColumnElement[Any]]:
1517 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1518 statement.
1520 This assembles the current collection of WHERE criteria
1521 into a single :class:`_expression.BooleanClauseList` construct.
1524 .. versionadded:: 1.4
1526 """
1528 return BooleanClauseList._construct_for_whereclause(
1529 self._where_criteria
1530 )
1533class Update(DMLWhereBase, ValuesBase):
1534 """Represent an Update construct.
1536 The :class:`_expression.Update` object is created using the
1537 :func:`_expression.update()` function.
1539 """
1541 __visit_name__ = "update"
1543 is_update = True
1545 _traverse_internals = (
1546 [
1547 ("table", InternalTraversal.dp_clauseelement),
1548 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1549 ("_inline", InternalTraversal.dp_boolean),
1550 ("_ordered_values", InternalTraversal.dp_dml_ordered_values),
1551 ("_values", InternalTraversal.dp_dml_values),
1552 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1553 ("_hints", InternalTraversal.dp_table_hint_list),
1554 ("_return_defaults", InternalTraversal.dp_boolean),
1555 (
1556 "_return_defaults_columns",
1557 InternalTraversal.dp_clauseelement_tuple,
1558 ),
1559 ]
1560 + HasPrefixes._has_prefixes_traverse_internals
1561 + DialectKWArgs._dialect_kwargs_traverse_internals
1562 + Executable._executable_traverse_internals
1563 + HasCTE._has_ctes_traverse_internals
1564 )
1566 def __init__(self, table: _DMLTableArgument):
1567 super().__init__(table)
1569 @_generative
1570 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1571 """Specify the VALUES clause of this UPDATE statement with an explicit
1572 parameter ordering that will be maintained in the SET clause of the
1573 resulting UPDATE statement.
1575 E.g.::
1577 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1579 .. seealso::
1581 :ref:`tutorial_parameter_ordered_updates` - full example of the
1582 :meth:`_expression.Update.ordered_values` method.
1584 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1585 method
1586 supersedes the
1587 :paramref:`_expression.update.preserve_parameter_order`
1588 parameter, which will be removed in SQLAlchemy 2.0.
1590 """ # noqa: E501
1591 if self._values:
1592 raise exc.ArgumentError(
1593 "This statement already has values present"
1594 )
1595 elif self._ordered_values:
1596 raise exc.ArgumentError(
1597 "This statement already has ordered values present"
1598 )
1600 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1601 self._ordered_values = kv_generator(self, args, True)
1602 return self
1604 @_generative
1605 def inline(self) -> Self:
1606 """Make this :class:`_expression.Update` construct "inline" .
1608 When set, SQL defaults present on :class:`_schema.Column`
1609 objects via the
1610 ``default`` keyword will be compiled 'inline' into the statement and
1611 not pre-executed. This means that their values will not be available
1612 in the dictionary returned from
1613 :meth:`_engine.CursorResult.last_updated_params`.
1615 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1616 parameter
1617 is now superseded by the :meth:`_expression.Update.inline` method.
1619 """
1620 self._inline = True
1621 return self
1623 if TYPE_CHECKING:
1624 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1626 # code within this block is **programmatically,
1627 # statically generated** by tools/generate_tuple_map_overloads.py
1629 @overload
1630 def returning(
1631 self, __ent0: _TCCA[_T0]
1632 ) -> ReturningUpdate[Tuple[_T0]]: ...
1634 @overload
1635 def returning(
1636 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1637 ) -> ReturningUpdate[Tuple[_T0, _T1]]: ...
1639 @overload
1640 def returning(
1641 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1642 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2]]: ...
1644 @overload
1645 def returning(
1646 self,
1647 __ent0: _TCCA[_T0],
1648 __ent1: _TCCA[_T1],
1649 __ent2: _TCCA[_T2],
1650 __ent3: _TCCA[_T3],
1651 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3]]: ...
1653 @overload
1654 def returning(
1655 self,
1656 __ent0: _TCCA[_T0],
1657 __ent1: _TCCA[_T1],
1658 __ent2: _TCCA[_T2],
1659 __ent3: _TCCA[_T3],
1660 __ent4: _TCCA[_T4],
1661 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1663 @overload
1664 def returning(
1665 self,
1666 __ent0: _TCCA[_T0],
1667 __ent1: _TCCA[_T1],
1668 __ent2: _TCCA[_T2],
1669 __ent3: _TCCA[_T3],
1670 __ent4: _TCCA[_T4],
1671 __ent5: _TCCA[_T5],
1672 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1674 @overload
1675 def returning(
1676 self,
1677 __ent0: _TCCA[_T0],
1678 __ent1: _TCCA[_T1],
1679 __ent2: _TCCA[_T2],
1680 __ent3: _TCCA[_T3],
1681 __ent4: _TCCA[_T4],
1682 __ent5: _TCCA[_T5],
1683 __ent6: _TCCA[_T6],
1684 ) -> ReturningUpdate[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1686 @overload
1687 def returning(
1688 self,
1689 __ent0: _TCCA[_T0],
1690 __ent1: _TCCA[_T1],
1691 __ent2: _TCCA[_T2],
1692 __ent3: _TCCA[_T3],
1693 __ent4: _TCCA[_T4],
1694 __ent5: _TCCA[_T5],
1695 __ent6: _TCCA[_T6],
1696 __ent7: _TCCA[_T7],
1697 ) -> ReturningUpdate[
1698 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1699 ]: ...
1701 # END OVERLOADED FUNCTIONS self.returning
1703 @overload
1704 def returning(
1705 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1706 ) -> ReturningUpdate[Any]: ...
1708 def returning(
1709 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1710 ) -> ReturningUpdate[Any]: ...
1713class ReturningUpdate(Update, TypedReturnsRows[_TP]):
1714 """Typing-only class that establishes a generic type form of
1715 :class:`.Update` which tracks returned column types.
1717 This datatype is delivered when calling the
1718 :meth:`.Update.returning` method.
1720 .. versionadded:: 2.0
1722 """
1725class Delete(DMLWhereBase, UpdateBase):
1726 """Represent a DELETE construct.
1728 The :class:`_expression.Delete` object is created using the
1729 :func:`_expression.delete()` function.
1731 """
1733 __visit_name__ = "delete"
1735 is_delete = True
1737 _traverse_internals = (
1738 [
1739 ("table", InternalTraversal.dp_clauseelement),
1740 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1741 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1742 ("_hints", InternalTraversal.dp_table_hint_list),
1743 ]
1744 + HasPrefixes._has_prefixes_traverse_internals
1745 + DialectKWArgs._dialect_kwargs_traverse_internals
1746 + Executable._executable_traverse_internals
1747 + HasCTE._has_ctes_traverse_internals
1748 )
1750 def __init__(self, table: _DMLTableArgument):
1751 self.table = coercions.expect(
1752 roles.DMLTableRole, table, apply_propagate_attrs=self
1753 )
1755 if TYPE_CHECKING:
1756 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1758 # code within this block is **programmatically,
1759 # statically generated** by tools/generate_tuple_map_overloads.py
1761 @overload
1762 def returning(
1763 self, __ent0: _TCCA[_T0]
1764 ) -> ReturningDelete[Tuple[_T0]]: ...
1766 @overload
1767 def returning(
1768 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1769 ) -> ReturningDelete[Tuple[_T0, _T1]]: ...
1771 @overload
1772 def returning(
1773 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1774 ) -> ReturningDelete[Tuple[_T0, _T1, _T2]]: ...
1776 @overload
1777 def returning(
1778 self,
1779 __ent0: _TCCA[_T0],
1780 __ent1: _TCCA[_T1],
1781 __ent2: _TCCA[_T2],
1782 __ent3: _TCCA[_T3],
1783 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3]]: ...
1785 @overload
1786 def returning(
1787 self,
1788 __ent0: _TCCA[_T0],
1789 __ent1: _TCCA[_T1],
1790 __ent2: _TCCA[_T2],
1791 __ent3: _TCCA[_T3],
1792 __ent4: _TCCA[_T4],
1793 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1795 @overload
1796 def returning(
1797 self,
1798 __ent0: _TCCA[_T0],
1799 __ent1: _TCCA[_T1],
1800 __ent2: _TCCA[_T2],
1801 __ent3: _TCCA[_T3],
1802 __ent4: _TCCA[_T4],
1803 __ent5: _TCCA[_T5],
1804 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1806 @overload
1807 def returning(
1808 self,
1809 __ent0: _TCCA[_T0],
1810 __ent1: _TCCA[_T1],
1811 __ent2: _TCCA[_T2],
1812 __ent3: _TCCA[_T3],
1813 __ent4: _TCCA[_T4],
1814 __ent5: _TCCA[_T5],
1815 __ent6: _TCCA[_T6],
1816 ) -> ReturningDelete[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1818 @overload
1819 def returning(
1820 self,
1821 __ent0: _TCCA[_T0],
1822 __ent1: _TCCA[_T1],
1823 __ent2: _TCCA[_T2],
1824 __ent3: _TCCA[_T3],
1825 __ent4: _TCCA[_T4],
1826 __ent5: _TCCA[_T5],
1827 __ent6: _TCCA[_T6],
1828 __ent7: _TCCA[_T7],
1829 ) -> ReturningDelete[
1830 Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]
1831 ]: ...
1833 # END OVERLOADED FUNCTIONS self.returning
1835 @overload
1836 def returning(
1837 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1838 ) -> ReturningDelete[Any]: ...
1840 def returning(
1841 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1842 ) -> ReturningDelete[Any]: ...
1845class ReturningDelete(Update, TypedReturnsRows[_TP]):
1846 """Typing-only class that establishes a generic type form of
1847 :class:`.Delete` which tracks returned column types.
1849 This datatype is delivered when calling the
1850 :meth:`.Delete.returning` method.
1852 .. versionadded:: 2.0
1854 """