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