Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/dml.py: 44%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/dml.py
2# Copyright (C) 2009-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7"""
8Provide :class:`_expression.Insert`, :class:`_expression.Update` and
9:class:`_expression.Delete`.
11"""
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 Literal
23from typing import MutableMapping
24from typing import NoReturn
25from typing import Optional
26from typing import overload
27from typing import Sequence
28from typing import Set
29from typing import Tuple
30from typing import Type
31from typing import TYPE_CHECKING
32from typing import TypeGuard
33from typing import TypeVar
34from typing import Union
36from . import coercions
37from . import roles
38from . import util as sql_util
39from ._typing import _unexpected_kw
40from ._typing import is_column_element
41from ._typing import is_named_from_clause
42from .base import _entity_namespace_key_search_all
43from .base import _exclusive_against
44from .base import _from_objects
45from .base import _generative
46from .base import _select_iterables
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 .base import WriteableColumnCollection
57from .elements import BooleanClauseList
58from .elements import ClauseElement
59from .elements import ColumnClause
60from .elements import ColumnElement
61from .elements import Null
62from .selectable import Alias
63from .selectable import ExecutableReturnsRows
64from .selectable import FromClause
65from .selectable import HasCTE
66from .selectable import HasPrefixes
67from .selectable import Join
68from .selectable import SelectLabelStyle
69from .selectable import TableClause
70from .selectable import TypedReturnsRows
71from .sqltypes import NullType
72from .visitors import InternalTraversal
73from .. import exc
74from .. import util
75from ..util.typing import Self
76from ..util.typing import TupleAny
77from ..util.typing import TypeVarTuple
78from ..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 _FromClauseArgument
87 from ._typing import _T0 # noqa
88 from ._typing import _T1 # noqa
89 from ._typing import _T2 # noqa
90 from ._typing import _T3 # noqa
91 from ._typing import _T4 # noqa
92 from ._typing import _T5 # noqa
93 from ._typing import _T6 # noqa
94 from ._typing import _T7 # noqa
95 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa
96 from .base import ReadOnlyColumnCollection
97 from .compiler import SQLCompiler
98 from .elements import KeyedColumnElement
99 from .selectable import _ColumnsClauseElement
100 from .selectable import _SelectIterable
101 from .selectable import Select
102 from .selectable import Selectable
104 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ...
106 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ...
108 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ...
110else:
111 isupdate = operator.attrgetter("isupdate")
112 isdelete = operator.attrgetter("isdelete")
113 isinsert = operator.attrgetter("isinsert")
116_T = TypeVar("_T", bound=Any)
117_Ts = TypeVarTuple("_Ts")
119_DMLColumnElement = Union[str, ColumnClause[Any]]
120_DMLTableElement = Union[TableClause, Alias, Join]
123class DMLState(CompileState):
124 _no_parameters = True
125 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None
126 _multi_parameters: Optional[
127 List[MutableMapping[_DMLColumnElement, Any]]
128 ] = None
129 _maintain_values_ordering: bool = False
130 _primary_table: FromClause
131 _supports_implicit_returning = True
133 isupdate = False
134 isdelete = False
135 isinsert = False
137 statement: UpdateBase
139 def __init__(
140 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any
141 ):
142 raise NotImplementedError()
144 @classmethod
145 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]:
146 return {
147 "name": (
148 statement.table.name
149 if is_named_from_clause(statement.table)
150 else None
151 ),
152 "table": statement.table,
153 }
155 @classmethod
156 def get_returning_column_descriptions(
157 cls, statement: UpdateBase
158 ) -> List[Dict[str, Any]]:
159 return [
160 {
161 "name": c.key,
162 "type": c.type,
163 "expr": c,
164 }
165 for c in statement._all_selected_columns
166 ]
168 @property
169 def dml_table(self) -> _DMLTableElement:
170 return self.statement.table
172 if TYPE_CHECKING:
174 @classmethod
175 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ...
177 @classmethod
178 def _get_multi_crud_kv_pairs(
179 cls,
180 statement: UpdateBase,
181 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]],
182 ) -> List[Dict[_DMLColumnElement, Any]]:
183 return [
184 {
185 coercions.expect(roles.DMLColumnRole, k): v
186 for k, v in mapping.items()
187 }
188 for mapping in multi_kv_iterator
189 ]
191 @classmethod
192 def _get_crud_kv_pairs(
193 cls,
194 statement: UpdateBase,
195 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]],
196 needs_to_be_cacheable: bool,
197 ) -> List[Tuple[_DMLColumnElement, Any]]:
198 return [
199 (
200 coercions.expect(roles.DMLColumnRole, k),
201 (
202 v
203 if not needs_to_be_cacheable
204 else coercions.expect(
205 roles.ExpressionElementRole,
206 v,
207 type_=NullType(),
208 is_crud=True,
209 )
210 ),
211 )
212 for k, v in kv_iterator
213 ]
215 def _make_extra_froms(
216 self,
217 statement: DMLWhereBase,
218 explicit_froms: Sequence[FromClause] = (),
219 ) -> Tuple[FromClause, List[FromClause]]:
220 froms: List[FromClause] = []
222 all_tables = list(sql_util.tables_from_leftmost(statement.table))
223 primary_table = all_tables[0]
224 seen = {primary_table}
226 def _consider_from(
227 from_: FromClause, include_surface_selectables: bool = False
228 ) -> None:
229 if not seen.intersection(from_._cloned_set):
230 froms.append(from_)
231 seen.update(from_._cloned_set)
232 if include_surface_selectables:
233 for elem in sql_util.surface_selectables_only(from_):
234 seen.update(elem._cloned_set)
236 for from_ in explicit_froms:
237 _consider_from(from_, include_surface_selectables=True)
239 consider = statement._where_criteria
240 if self._dict_parameters:
241 consider += tuple(self._dict_parameters.values())
243 for crit in consider:
244 for item in _from_objects(crit):
245 _consider_from(item)
247 froms.extend(all_tables[1:])
248 return primary_table, froms
250 def _process_values(self, statement: ValuesBase) -> None:
251 if self._no_parameters:
252 self._dict_parameters = statement._values
253 self._no_parameters = False
255 def _process_select_values(self, statement: ValuesBase) -> None:
256 assert statement._select_names is not None
257 parameters: MutableMapping[_DMLColumnElement, Any] = {
258 name: Null() for name in statement._select_names
259 }
261 if self._no_parameters:
262 self._no_parameters = False
263 self._dict_parameters = parameters
264 else:
265 # this condition normally not reachable as the Insert
266 # does not allow this construction to occur
267 assert False, "This statement already has parameters"
269 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn:
270 raise exc.InvalidRequestError(
271 "%s construct does not support "
272 "multiple parameter sets." % statement.__visit_name__.upper()
273 )
275 def _cant_mix_formats_error(self) -> NoReturn:
276 raise exc.InvalidRequestError(
277 "Can't mix single and multiple VALUES "
278 "formats in one INSERT statement; one style appends to a "
279 "list while the other replaces values, so the intent is "
280 "ambiguous."
281 )
284@CompileState.plugin_for("default", "insert")
285class InsertDMLState(DMLState):
286 isinsert = True
288 include_table_with_column_exprs = False
290 _has_multi_parameters = False
292 def __init__(
293 self,
294 statement: Insert,
295 compiler: SQLCompiler,
296 disable_implicit_returning: bool = False,
297 **kw: Any,
298 ):
299 self.statement = statement
300 self._primary_table = statement.table
302 if disable_implicit_returning:
303 self._supports_implicit_returning = False
305 self.isinsert = True
306 if statement._select_names:
307 self._process_select_values(statement)
308 if statement._values is not None:
309 self._process_values(statement)
310 if statement._multi_values:
311 self._process_multi_values(statement)
313 @util.memoized_property
314 def _insert_col_keys(self) -> List[str]:
315 # this is also done in crud.py -> _key_getters_for_crud_column
316 return [
317 coercions.expect(roles.DMLColumnRole, col, as_key=True)
318 for col in self._dict_parameters or ()
319 ]
321 def _process_values(self, statement: ValuesBase) -> None:
322 if self._no_parameters:
323 self._has_multi_parameters = False
324 self._dict_parameters = statement._values
325 self._no_parameters = False
326 elif self._has_multi_parameters:
327 self._cant_mix_formats_error()
329 def _process_multi_values(self, statement: ValuesBase) -> None:
330 for parameters in statement._multi_values:
331 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [
332 (
333 {
334 c.key: value
335 for c, value in zip(statement.table.c, parameter_set)
336 }
337 if isinstance(parameter_set, collections_abc.Sequence)
338 else parameter_set
339 )
340 for parameter_set in parameters
341 ]
343 if self._no_parameters:
344 self._no_parameters = False
345 self._has_multi_parameters = True
346 self._multi_parameters = multi_parameters
347 self._dict_parameters = self._multi_parameters[0]
348 elif not self._has_multi_parameters:
349 self._cant_mix_formats_error()
350 else:
351 assert self._multi_parameters
352 self._multi_parameters.extend(multi_parameters)
355@CompileState.plugin_for("default", "update")
356class UpdateDMLState(DMLState):
357 isupdate = True
359 include_table_with_column_exprs = False
361 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any):
362 self.statement = statement
364 self.isupdate = True
365 if statement._maintain_values_ordering:
366 self._process_ordered_values(statement)
367 elif statement._values is not None:
368 self._process_values(statement)
369 elif statement._multi_values:
370 self._no_multi_values_supported(statement)
371 t, ef = self._make_extra_froms(statement)
372 self._primary_table = t
373 self._extra_froms = ef
375 self.is_multitable = mt = ef
376 self.include_table_with_column_exprs = bool(
377 mt and compiler.render_table_with_column_in_update_from
378 )
380 def _process_ordered_values(self, statement: ValuesBase) -> None:
381 parameters = statement._values
382 if self._no_parameters:
383 self._no_parameters = False
384 assert parameters is not None
385 self._dict_parameters = dict(parameters)
386 self._maintain_values_ordering = True
387 else:
388 raise exc.InvalidRequestError(
389 "Can only invoke ordered_values() once, and not mixed "
390 "with any other values() call"
391 )
394@CompileState.plugin_for("default", "delete")
395class DeleteDMLState(DMLState):
396 isdelete = True
398 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any):
399 self.statement = statement
401 self.isdelete = True
402 t, ef = self._make_extra_froms(statement, statement._extra_froms)
403 self._primary_table = t
404 self._extra_froms = ef
405 self.is_multitable = ef
408class UpdateBase(
409 roles.DMLRole,
410 HasCTE,
411 HasCompileState,
412 DialectKWArgs,
413 HasPrefixes,
414 Generative,
415 ExecutableReturnsRows,
416 ClauseElement,
417):
418 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
420 __visit_name__ = "update_base"
422 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = (
423 util.EMPTY_DICT
424 )
425 named_with_column = False
427 _label_style: SelectLabelStyle = (
428 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY
429 )
430 table: _DMLTableElement
432 _return_defaults = False
433 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = (
434 None
435 )
436 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None
437 _returning: Tuple[_ColumnsClauseElement, ...] = ()
439 is_dml = True
441 def _generate_fromclause_column_proxies(
442 self,
443 fromclause: FromClause,
444 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],
445 primary_key: ColumnSet,
446 foreign_keys: Set[KeyedColumnElement[Any]],
447 ) -> None:
448 prox = [
449 c._make_proxy(
450 fromclause,
451 key=proxy_key,
452 name=required_label_name,
453 name_is_truncatable=True,
454 primary_key=primary_key,
455 foreign_keys=foreign_keys,
456 )
457 for (
458 required_label_name,
459 proxy_key,
460 fallback_label_name,
461 c,
462 repeated,
463 ) in (self._generate_columns_plus_names(False))
464 if is_column_element(c)
465 ]
467 columns._populate_separate_keys(prox)
469 def params(self, *arg: Any, **kw: Any) -> NoReturn:
470 """Set the parameters for the statement.
472 This method raises ``NotImplementedError`` on the base class,
473 and is overridden by :class:`.ValuesBase` to provide the
474 SET/VALUES clause of UPDATE and INSERT.
476 """
477 raise NotImplementedError(
478 "params() is not supported for INSERT/UPDATE/DELETE statements."
479 " To set the values for an INSERT or UPDATE statement, use"
480 " stmt.values(**parameters)."
481 )
483 @_generative
484 def with_dialect_options(self, **opt: Any) -> Self:
485 """Add dialect options to this INSERT/UPDATE/DELETE object.
487 e.g.::
489 upd = table.update().dialect_options(mysql_limit=10)
491 .. versionadded:: 1.4 - this method supersedes the dialect options
492 associated with the constructor.
495 """
496 self._validate_dialect_kwargs(opt)
497 return self
499 @_generative
500 def return_defaults(
501 self,
502 *cols: _DMLColumnArgument,
503 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None,
504 sort_by_parameter_order: bool = False,
505 ) -> Self:
506 """Make use of a :term:`RETURNING` clause for the purpose
507 of fetching server-side expressions and defaults, for supporting
508 backends only.
510 .. deepalchemy::
512 The :meth:`.UpdateBase.return_defaults` method is used by the ORM
513 for its internal work in fetching newly generated primary key
514 and server default values, in particular to provide the underlying
515 implementation of the :paramref:`_orm.Mapper.eager_defaults`
516 ORM feature as well as to allow RETURNING support with bulk
517 ORM inserts. Its behavior is fairly idiosyncratic
518 and is not really intended for general use. End users should
519 stick with using :meth:`.UpdateBase.returning` in order to
520 add RETURNING clauses to their INSERT, UPDATE and DELETE
521 statements.
523 Normally, a single row INSERT statement will automatically populate the
524 :attr:`.CursorResult.inserted_primary_key` attribute when executed,
525 which stores the primary key of the row that was just inserted in the
526 form of a :class:`.Row` object with column names as named tuple keys
527 (and the :attr:`.Row._mapping` view fully populated as well). The
528 dialect in use chooses the strategy to use in order to populate this
529 data; if it was generated using server-side defaults and / or SQL
530 expressions, dialect-specific approaches such as ``cursor.lastrowid``
531 or ``RETURNING`` are typically used to acquire the new primary key
532 value.
534 However, when the statement is modified by calling
535 :meth:`.UpdateBase.return_defaults` before executing the statement,
536 additional behaviors take place **only** for backends that support
537 RETURNING and for :class:`.Table` objects that maintain the
538 :paramref:`.Table.implicit_returning` parameter at its default value of
539 ``True``. In these cases, when the :class:`.CursorResult` is returned
540 from the statement's execution, not only will
541 :attr:`.CursorResult.inserted_primary_key` be populated as always, the
542 :attr:`.CursorResult.returned_defaults` attribute will also be
543 populated with a :class:`.Row` named-tuple representing the full range
544 of server generated
545 values from that single row, including values for any columns that
546 specify :paramref:`_schema.Column.server_default` or which make use of
547 :paramref:`_schema.Column.default` using a SQL expression.
549 When invoking INSERT statements with multiple rows using
550 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the
551 :meth:`.UpdateBase.return_defaults` modifier will have the effect of
552 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and
553 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being
554 fully populated with lists of :class:`.Row` objects representing newly
555 inserted primary key values as well as newly inserted server generated
556 values for each row inserted. The
557 :attr:`.CursorResult.inserted_primary_key` and
558 :attr:`.CursorResult.returned_defaults` attributes will also continue
559 to be populated with the first row of these two collections.
561 If the backend does not support RETURNING or the :class:`.Table` in use
562 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING
563 clause is added and no additional data is fetched, however the
564 INSERT, UPDATE or DELETE statement proceeds normally.
566 E.g.::
568 stmt = table.insert().values(data="newdata").return_defaults()
570 result = connection.execute(stmt)
572 server_created_at = result.returned_defaults["created_at"]
574 When used against an UPDATE statement
575 :meth:`.UpdateBase.return_defaults` instead looks for columns that
576 include :paramref:`_schema.Column.onupdate` or
577 :paramref:`_schema.Column.server_onupdate` parameters assigned, when
578 constructing the columns that will be included in the RETURNING clause
579 by default if explicit columns were not specified. When used against a
580 DELETE statement, no columns are included in RETURNING by default, they
581 instead must be specified explicitly as there are no columns that
582 normally change values when a DELETE statement proceeds.
584 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported
585 for DELETE statements also and has been moved from
586 :class:`.ValuesBase` to :class:`.UpdateBase`.
588 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive
589 against the :meth:`.UpdateBase.returning` method and errors will be
590 raised during the SQL compilation process if both are used at the same
591 time on one statement. The RETURNING clause of the INSERT, UPDATE or
592 DELETE statement is therefore controlled by only one of these methods
593 at a time.
595 The :meth:`.UpdateBase.return_defaults` method differs from
596 :meth:`.UpdateBase.returning` in these ways:
598 1. :meth:`.UpdateBase.return_defaults` method causes the
599 :attr:`.CursorResult.returned_defaults` collection to be populated
600 with the first row from the RETURNING result. This attribute is not
601 populated when using :meth:`.UpdateBase.returning`.
603 2. :meth:`.UpdateBase.return_defaults` is compatible with existing
604 logic used to fetch auto-generated primary key values that are then
605 populated into the :attr:`.CursorResult.inserted_primary_key`
606 attribute. By contrast, using :meth:`.UpdateBase.returning` will
607 have the effect of the :attr:`.CursorResult.inserted_primary_key`
608 attribute being left unpopulated.
610 3. :meth:`.UpdateBase.return_defaults` can be called against any
611 backend. Backends that don't support RETURNING will skip the usage
612 of the feature, rather than raising an exception, *unless*
613 ``supplemental_cols`` is passed. The return value
614 of :attr:`_engine.CursorResult.returned_defaults` will be ``None``
615 for backends that don't support RETURNING or for which the target
616 :class:`.Table` sets :paramref:`.Table.implicit_returning` to
617 ``False``.
619 4. An INSERT statement invoked with executemany() is supported if the
620 backend database driver supports the
621 :ref:`insertmanyvalues <engine_insertmanyvalues>`
622 feature which is now supported by most SQLAlchemy-included backends.
623 When executemany is used, the
624 :attr:`_engine.CursorResult.returned_defaults_rows` and
625 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors
626 will return the inserted defaults and primary keys.
628 .. versionadded:: 1.4 Added
629 :attr:`_engine.CursorResult.returned_defaults_rows` and
630 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors.
631 In version 2.0, the underlying implementation which fetches and
632 populates the data for these attributes was generalized to be
633 supported by most backends, whereas in 1.4 they were only
634 supported by the ``psycopg2`` driver.
637 :param cols: optional list of column key names or
638 :class:`_schema.Column` that acts as a filter for those columns that
639 will be fetched.
640 :param supplemental_cols: optional list of RETURNING expressions,
641 in the same form as one would pass to the
642 :meth:`.UpdateBase.returning` method. When present, the additional
643 columns will be included in the RETURNING clause, and the
644 :class:`.CursorResult` object will be "rewound" when returned, so
645 that methods like :meth:`.CursorResult.all` will return new rows
646 mostly as though the statement used :meth:`.UpdateBase.returning`
647 directly. However, unlike when using :meth:`.UpdateBase.returning`
648 directly, the **order of the columns is undefined**, so can only be
649 targeted using names or :attr:`.Row._mapping` keys; they cannot
650 reliably be targeted positionally.
652 .. versionadded:: 2.0
654 :param sort_by_parameter_order: for a batch INSERT that is being
655 executed against multiple parameter sets, organize the results of
656 RETURNING so that the returned rows correspond to the order of
657 parameter sets passed in. This applies only to an :term:`executemany`
658 execution for supporting dialects and typically makes use of the
659 :term:`insertmanyvalues` feature.
661 .. versionadded:: 2.0.10
663 .. seealso::
665 :ref:`engine_insertmanyvalues_returning_order` - background on
666 sorting of RETURNING rows for bulk INSERT
668 .. seealso::
670 :meth:`.UpdateBase.returning`
672 :attr:`_engine.CursorResult.returned_defaults`
674 :attr:`_engine.CursorResult.returned_defaults_rows`
676 :attr:`_engine.CursorResult.inserted_primary_key`
678 :attr:`_engine.CursorResult.inserted_primary_key_rows`
680 """
682 if self._return_defaults:
683 # note _return_defaults_columns = () means return all columns,
684 # so if we have been here before, only update collection if there
685 # are columns in the collection
686 if self._return_defaults_columns and cols:
687 self._return_defaults_columns = tuple(
688 util.OrderedSet(self._return_defaults_columns).union(
689 coercions.expect(roles.ColumnsClauseRole, c)
690 for c in cols
691 )
692 )
693 else:
694 # set for all columns
695 self._return_defaults_columns = ()
696 else:
697 self._return_defaults_columns = tuple(
698 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
699 )
700 self._return_defaults = True
701 if sort_by_parameter_order:
702 if not self.is_insert:
703 raise exc.ArgumentError(
704 "The 'sort_by_parameter_order' argument to "
705 "return_defaults() only applies to INSERT statements"
706 )
707 self._sort_by_parameter_order = True
708 if supplemental_cols:
709 # uniquifying while also maintaining order (the maintain of order
710 # is for test suites but also for vertical splicing
711 supplemental_col_tup = (
712 coercions.expect(roles.ColumnsClauseRole, c)
713 for c in supplemental_cols
714 )
716 if self._supplemental_returning is None:
717 self._supplemental_returning = tuple(
718 util.unique_list(supplemental_col_tup)
719 )
720 else:
721 self._supplemental_returning = tuple(
722 util.unique_list(
723 self._supplemental_returning
724 + tuple(supplemental_col_tup)
725 )
726 )
728 return self
730 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
731 """Return ``True`` if this :class:`.ReturnsRows` is
732 'derived' from the given :class:`.FromClause`.
734 Since these are DMLs, we dont want such statements ever being adapted
735 so we return False for derives.
737 """
738 return False
740 @_generative
741 def returning(
742 self,
743 *cols: _ColumnsClauseArgument[Any],
744 sort_by_parameter_order: bool = False,
745 **__kw: Any,
746 ) -> UpdateBase:
747 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
749 e.g.:
751 .. sourcecode:: pycon+sql
753 >>> stmt = (
754 ... table.update()
755 ... .where(table.c.data == "value")
756 ... .values(status="X")
757 ... .returning(table.c.server_flag, table.c.updated_timestamp)
758 ... )
759 >>> print(stmt)
760 {printsql}UPDATE some_table SET status=:status
761 WHERE some_table.data = :data_1
762 RETURNING some_table.server_flag, some_table.updated_timestamp
764 The method may be invoked multiple times to add new entries to the
765 list of expressions to be returned.
767 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to
768 add new entries to the list of expressions to be returned.
770 The given collection of column expressions should be derived from the
771 table that is the target of the INSERT, UPDATE, or DELETE. While
772 :class:`_schema.Column` objects are typical, the elements can also be
773 expressions:
775 .. sourcecode:: pycon+sql
777 >>> stmt = table.insert().returning(
778 ... (table.c.first_name + " " + table.c.last_name).label("fullname")
779 ... )
780 >>> print(stmt)
781 {printsql}INSERT INTO some_table (first_name, last_name)
782 VALUES (:first_name, :last_name)
783 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname
785 Upon compilation, a RETURNING clause, or database equivalent,
786 will be rendered within the statement. For INSERT and UPDATE,
787 the values are the newly inserted/updated values. For DELETE,
788 the values are those of the rows which were deleted.
790 Upon execution, the values of the columns to be returned are made
791 available via the result set and can be iterated using
792 :meth:`_engine.CursorResult.fetchone` and similar.
793 For DBAPIs which do not
794 natively support returning values (i.e. cx_oracle), SQLAlchemy will
795 approximate this behavior at the result level so that a reasonable
796 amount of behavioral neutrality is provided.
798 Note that not all databases/DBAPIs
799 support RETURNING. For those backends with no support,
800 an exception is raised upon compilation and/or execution.
801 For those who do support it, the functionality across backends
802 varies greatly, including restrictions on executemany()
803 and other statements which return multiple rows. Please
804 read the documentation notes for the database in use in
805 order to determine the availability of RETURNING.
807 :param \*cols: series of columns, SQL expressions, or whole tables
808 entities to be returned.
809 :param sort_by_parameter_order: for a batch INSERT that is being
810 executed against multiple parameter sets, organize the results of
811 RETURNING so that the returned rows correspond to the order of
812 parameter sets passed in. This applies only to an :term:`executemany`
813 execution for supporting dialects and typically makes use of the
814 :term:`insertmanyvalues` feature.
816 .. versionadded:: 2.0.10
818 .. seealso::
820 :ref:`engine_insertmanyvalues_returning_order` - background on
821 sorting of RETURNING rows for bulk INSERT (Core level discussion)
823 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of
824 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion)
826 .. seealso::
828 :meth:`.UpdateBase.return_defaults` - an alternative method tailored
829 towards efficient fetching of server-side defaults and triggers
830 for single-row INSERTs or UPDATEs.
832 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`
834 """ # noqa: E501
835 if __kw:
836 raise _unexpected_kw("UpdateBase.returning()", __kw)
837 if self._return_defaults:
838 raise exc.InvalidRequestError(
839 "return_defaults() is already configured on this statement"
840 )
841 self._returning += tuple(
842 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
843 )
844 if sort_by_parameter_order:
845 if not self.is_insert:
846 raise exc.ArgumentError(
847 "The 'sort_by_parameter_order' argument to returning() "
848 "only applies to INSERT statements"
849 )
850 self._sort_by_parameter_order = True
851 return self
853 def corresponding_column(
854 self, column: KeyedColumnElement[Any], require_embedded: bool = False
855 ) -> Optional[ColumnElement[Any]]:
856 return self.exported_columns.corresponding_column(
857 column, require_embedded=require_embedded
858 )
860 @util.ro_memoized_property
861 def _all_selected_columns(self) -> _SelectIterable:
862 return [c for c in _select_iterables(self._returning)]
864 @util.ro_memoized_property
865 def exported_columns(
866 self,
867 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]:
868 """Return the RETURNING columns as a column collection for this
869 statement.
871 .. versionadded:: 1.4
873 """
874 return WriteableColumnCollection(
875 (c.key, c)
876 for c in self._all_selected_columns
877 if is_column_element(c)
878 ).as_readonly()
880 @_generative
881 def with_hint(
882 self,
883 text: str,
884 selectable: Optional[_DMLTableArgument] = None,
885 dialect_name: str = "*",
886 ) -> Self:
887 """Add a table hint for a single table to this
888 INSERT/UPDATE/DELETE statement.
890 .. note::
892 :meth:`.UpdateBase.with_hint` currently applies only to
893 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
894 :meth:`.UpdateBase.prefix_with`.
896 The text of the hint is rendered in the appropriate
897 location for the database backend in use, relative
898 to the :class:`_schema.Table` that is the subject of this
899 statement, or optionally to that of the given
900 :class:`_schema.Table` passed as the ``selectable`` argument.
902 The ``dialect_name`` option will limit the rendering of a particular
903 hint to a particular backend. Such as, to add a hint
904 that only takes effect for SQL Server::
906 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
908 :param text: Text of the hint.
909 :param selectable: optional :class:`_schema.Table` that specifies
910 an element of the FROM clause within an UPDATE or DELETE
911 to be the subject of the hint - applies only to certain backends.
912 :param dialect_name: defaults to ``*``, if specified as the name
913 of a particular dialect, will apply these hints only when
914 that dialect is in use.
915 """
916 if selectable is None:
917 selectable = self.table
918 else:
919 selectable = coercions.expect(roles.DMLTableRole, selectable)
920 self._hints = self._hints.union({(selectable, dialect_name): text})
921 return self
923 @property
924 def entity_description(self) -> Dict[str, Any]:
925 """Return a :term:`plugin-enabled` description of the table and/or
926 entity which this DML construct is operating against.
928 This attribute is generally useful when using the ORM, as an
929 extended structure which includes information about mapped
930 entities is returned. The section :ref:`queryguide_inspection`
931 contains more background.
933 For a Core statement, the structure returned by this accessor
934 is derived from the :attr:`.UpdateBase.table` attribute, and
935 refers to the :class:`.Table` being inserted, updated, or deleted::
937 >>> stmt = insert(user_table)
938 >>> stmt.entity_description
939 {
940 "name": "user_table",
941 "table": Table("user_table", ...)
942 }
944 .. versionadded:: 1.4.33
946 .. seealso::
948 :attr:`.UpdateBase.returning_column_descriptions`
950 :attr:`.Select.column_descriptions` - entity information for
951 a :func:`.select` construct
953 :ref:`queryguide_inspection` - ORM background
955 """
956 meth = DMLState.get_plugin_class(self).get_entity_description
957 return meth(self)
959 @property
960 def returning_column_descriptions(self) -> List[Dict[str, Any]]:
961 """Return a :term:`plugin-enabled` description of the columns
962 which this DML construct is RETURNING against, in other words
963 the expressions established as part of :meth:`.UpdateBase.returning`.
965 This attribute is generally useful when using the ORM, as an
966 extended structure which includes information about mapped
967 entities is returned. The section :ref:`queryguide_inspection`
968 contains more background.
970 For a Core statement, the structure returned by this accessor is
971 derived from the same objects that are returned by the
972 :attr:`.UpdateBase.exported_columns` accessor::
974 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name)
975 >>> stmt.entity_description
976 [
977 {
978 "name": "id",
979 "type": Integer,
980 "expr": Column("id", Integer(), table=<user>, ...)
981 },
982 {
983 "name": "name",
984 "type": String(),
985 "expr": Column("name", String(), table=<user>, ...)
986 },
987 ]
989 .. versionadded:: 1.4.33
991 .. seealso::
993 :attr:`.UpdateBase.entity_description`
995 :attr:`.Select.column_descriptions` - entity information for
996 a :func:`.select` construct
998 :ref:`queryguide_inspection` - ORM background
1000 """ # noqa: E501
1001 meth = DMLState.get_plugin_class(
1002 self
1003 ).get_returning_column_descriptions
1004 return meth(self)
1007class ValuesBase(UpdateBase):
1008 """Supplies support for :meth:`.ValuesBase.values` to
1009 INSERT and UPDATE constructs."""
1011 __visit_name__ = "values_base"
1013 _supports_multi_parameters = False
1015 select: Optional[Select[Unpack[TupleAny]]] = None
1016 """SELECT statement for INSERT .. FROM SELECT"""
1018 _post_values_clause: Optional[ClauseElement] = None
1019 """used by extensions to Insert etc. to add additional syntactical
1020 constructs, e.g. ON CONFLICT etc."""
1022 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None
1023 _multi_values: Tuple[
1024 Union[
1025 Sequence[Dict[_DMLColumnElement, Any]],
1026 Sequence[Sequence[Any]],
1027 ],
1028 ...,
1029 ] = ()
1031 _maintain_values_ordering: bool = False
1033 _select_names: Optional[List[str]] = None
1034 _inline: bool = False
1036 def __init__(self, table: _DMLTableArgument):
1037 self.table = coercions.expect(
1038 roles.DMLTableRole, table, apply_propagate_attrs=self
1039 )
1041 @_generative
1042 @_exclusive_against(
1043 "_select_names",
1044 "_maintain_values_ordering",
1045 msgs={
1046 "_select_names": "This construct already inserts from a SELECT",
1047 "_maintain_values_ordering": "This statement already has ordered "
1048 "values present",
1049 },
1050 defaults={"_maintain_values_ordering": False},
1051 )
1052 def values(
1053 self,
1054 *args: Union[
1055 _DMLColumnKeyMapping[Any],
1056 Sequence[Any],
1057 ],
1058 **kwargs: Any,
1059 ) -> Self:
1060 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
1061 clause for an UPDATE.
1063 Note that the :class:`_expression.Insert` and
1064 :class:`_expression.Update`
1065 constructs support
1066 per-execution time formatting of the VALUES and/or SET clauses,
1067 based on the arguments passed to :meth:`_engine.Connection.execute`.
1068 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
1069 particular set of parameters into the statement.
1071 Multiple calls to :meth:`.ValuesBase.values` will produce a new
1072 construct, each one with the parameter list modified to include
1073 the new parameters sent. In the typical case of a single
1074 dictionary of parameters, the newly passed keys will replace
1075 the same keys in the previous construct. In the case of a list-based
1076 "multiple values" construct, each new list of values is extended
1077 onto the existing list of values.
1079 :param \**kwargs: key value pairs representing the string key
1080 of a :class:`_schema.Column`
1081 mapped to the value to be rendered into the
1082 VALUES or SET clause::
1084 users.insert().values(name="some name")
1086 users.update().where(users.c.id == 5).values(name="some name")
1088 :param \*args: As an alternative to passing key/value parameters,
1089 a dictionary, tuple, or list of dictionaries or tuples can be passed
1090 as a single positional argument in order to form the VALUES or
1091 SET clause of the statement. The forms that are accepted vary
1092 based on whether this is an :class:`_expression.Insert` or an
1093 :class:`_expression.Update` construct.
1095 For either an :class:`_expression.Insert` or
1096 :class:`_expression.Update`
1097 construct, a single dictionary can be passed, which works the same as
1098 that of the kwargs form::
1100 users.insert().values({"name": "some name"})
1102 users.update().values({"name": "some new name"})
1104 Also for either form but more typically for the
1105 :class:`_expression.Insert` construct, a tuple that contains an
1106 entry for every column in the table is also accepted::
1108 users.insert().values((5, "some name"))
1110 The :class:`_expression.Insert` construct also supports being
1111 passed a list of dictionaries or full-table-tuples, which on the
1112 server will render the less common SQL syntax of "multiple values" -
1113 this syntax is supported on backends such as SQLite, PostgreSQL,
1114 MySQL, but not necessarily others::
1116 users.insert().values(
1117 [
1118 {"name": "some name"},
1119 {"name": "some other name"},
1120 {"name": "yet another name"},
1121 ]
1122 )
1124 The above form would render a multiple VALUES statement similar to:
1126 .. sourcecode:: sql
1128 INSERT INTO users (name) VALUES
1129 (:name_1),
1130 (:name_2),
1131 (:name_3)
1133 It is essential to note that **passing multiple values is
1134 NOT the same as using traditional executemany() form**. The above
1135 syntax is a **special** syntax not typically used. To emit an
1136 INSERT statement against multiple rows, the normal method is
1137 to pass a multiple values list to the
1138 :meth:`_engine.Connection.execute`
1139 method, which is supported by all database backends and is generally
1140 more efficient for a very large number of parameters.
1142 .. seealso::
1144 :ref:`tutorial_multiple_parameters` - an introduction to
1145 the traditional Core method of multiple parameter set
1146 invocation for INSERTs and other statements.
1148 :ref:`tutorial_core_insert_values_clause` - Insert tutorial
1149 detailing alternatives to the multiple values syntax.
1151 The UPDATE construct also supports rendering the SET parameters
1152 in a specific order. For this feature refer to the
1153 :meth:`_expression.Update.ordered_values` method.
1155 .. seealso::
1157 :meth:`_expression.Update.ordered_values`
1160 """
1161 if args:
1162 # positional case. this is currently expensive. we don't
1163 # yet have positional-only args so we have to check the length.
1164 # then we need to check multiparams vs. single dictionary.
1165 # since the parameter format is needed in order to determine
1166 # a cache key, we need to determine this up front.
1167 arg = args[0]
1169 if kwargs:
1170 raise exc.ArgumentError(
1171 "Can't pass positional and kwargs to values() "
1172 "simultaneously"
1173 )
1174 elif len(args) > 1:
1175 raise exc.ArgumentError(
1176 "Only a single dictionary/tuple or list of "
1177 "dictionaries/tuples is accepted positionally."
1178 )
1180 elif isinstance(arg, collections_abc.Sequence):
1181 if arg and isinstance(arg[0], dict):
1182 multi_kv_generator = DMLState.get_plugin_class(
1183 self
1184 )._get_multi_crud_kv_pairs
1185 self._multi_values += (multi_kv_generator(self, arg),)
1186 return self
1188 if arg and isinstance(arg[0], (list, tuple)):
1189 self._multi_values += (arg,)
1190 return self
1192 if TYPE_CHECKING:
1193 # crud.py raises during compilation if this is not the
1194 # case
1195 assert isinstance(self, Insert)
1197 # tuple values
1198 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1200 else:
1201 # kwarg path. this is the most common path for non-multi-params
1202 # so this is fairly quick.
1203 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1204 if args:
1205 raise exc.ArgumentError(
1206 "Only a single dictionary/tuple or list of "
1207 "dictionaries/tuples is accepted positionally."
1208 )
1210 # for top level values(), convert literals to anonymous bound
1211 # parameters at statement construction time, so that these values can
1212 # participate in the cache key process like any other ClauseElement.
1213 # crud.py now intercepts bound parameters with unique=True from here
1214 # and ensures they get the "crud"-style name when rendered.
1216 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1217 coerced_arg = dict(kv_generator(self, arg.items(), True))
1218 if self._values:
1219 self._values = self._values.union(coerced_arg)
1220 else:
1221 self._values = util.immutabledict(coerced_arg)
1222 return self
1225class Insert(ValuesBase, HasSyntaxExtensions[Literal["post_values"]]):
1226 """Represent an INSERT construct.
1228 The :class:`_expression.Insert` object is created using the
1229 :func:`_expression.insert()` function.
1231 Available extension points:
1233 * ``post_values``: applies additional logic after the ``VALUES`` clause.
1235 """
1237 __visit_name__ = "insert"
1239 _supports_multi_parameters = True
1241 select = None
1242 include_insert_from_select_defaults = False
1244 _sort_by_parameter_order: bool = False
1246 is_insert = True
1248 table: TableClause
1250 _traverse_internals = (
1251 [
1252 ("table", InternalTraversal.dp_clauseelement),
1253 ("_inline", InternalTraversal.dp_boolean),
1254 ("_select_names", InternalTraversal.dp_string_list),
1255 ("_values", InternalTraversal.dp_dml_values),
1256 ("_multi_values", InternalTraversal.dp_dml_multi_values),
1257 ("select", InternalTraversal.dp_clauseelement),
1258 ("_post_values_clause", InternalTraversal.dp_clauseelement),
1259 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1260 ("_hints", InternalTraversal.dp_table_hint_list),
1261 ("_return_defaults", InternalTraversal.dp_boolean),
1262 (
1263 "_return_defaults_columns",
1264 InternalTraversal.dp_clauseelement_tuple,
1265 ),
1266 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1267 ]
1268 + HasPrefixes._has_prefixes_traverse_internals
1269 + DialectKWArgs._dialect_kwargs_traverse_internals
1270 + ExecutableStatement._executable_traverse_internals
1271 + HasCTE._has_ctes_traverse_internals
1272 )
1274 _position_map = util.immutabledict(
1275 {
1276 "post_values": "_post_values_clause",
1277 }
1278 )
1280 _post_values_clause: Optional[ClauseElement] = None
1281 """extension point for a ClauseElement that will be compiled directly
1282 after the VALUES portion of the :class:`.Insert` statement
1284 """
1286 def __init__(self, table: _DMLTableArgument):
1287 super().__init__(table)
1289 def _apply_syntax_extension_to_self(
1290 self, extension: SyntaxExtension
1291 ) -> None:
1292 extension.apply_to_insert(self)
1294 @_generative
1295 def inline(self) -> Self:
1296 """Make this :class:`_expression.Insert` construct "inline" .
1298 When set, no attempt will be made to retrieve the
1299 SQL-generated default values to be provided within the statement;
1300 in particular,
1301 this allows SQL expressions to be rendered 'inline' within the
1302 statement without the need to pre-execute them beforehand; for
1303 backends that support "returning", this turns off the "implicit
1304 returning" feature for the statement.
1307 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1308 parameter
1309 is now superseded by the :meth:`_expression.Insert.inline` method.
1311 """
1312 self._inline = True
1313 return self
1315 @_generative
1316 def from_select(
1317 self,
1318 names: Sequence[_DMLColumnArgument],
1319 select: Selectable,
1320 include_defaults: bool = True,
1321 ) -> Self:
1322 """Return a new :class:`_expression.Insert` construct which represents
1323 an ``INSERT...FROM SELECT`` statement.
1325 e.g.::
1327 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1328 ins = table2.insert().from_select(["a", "b"], sel)
1330 :param names: a sequence of string column names or
1331 :class:`_schema.Column`
1332 objects representing the target columns.
1333 :param select: a :func:`_expression.select` construct,
1334 :class:`_expression.FromClause`
1335 or other construct which resolves into a
1336 :class:`_expression.FromClause`,
1337 such as an ORM :class:`_query.Query` object, etc. The order of
1338 columns returned from this FROM clause should correspond to the
1339 order of columns sent as the ``names`` parameter; while this
1340 is not checked before passing along to the database, the database
1341 would normally raise an exception if these column lists don't
1342 correspond.
1343 :param include_defaults: if True, non-server default values and
1344 SQL expressions as specified on :class:`_schema.Column` objects
1345 (as documented in :ref:`metadata_defaults_toplevel`) not
1346 otherwise specified in the list of names will be rendered
1347 into the INSERT and SELECT statements, so that these values are also
1348 included in the data to be inserted.
1350 .. note:: A Python-side default that uses a Python callable function
1351 will only be invoked **once** for the whole statement, and **not
1352 per row**.
1354 """
1356 if self._values:
1357 raise exc.InvalidRequestError(
1358 "This construct already inserts value expressions"
1359 )
1361 self._select_names = [
1362 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1363 for name in names
1364 ]
1365 self._inline = True
1366 self.include_insert_from_select_defaults = include_defaults
1367 self.select = coercions.expect(roles.DMLSelectRole, select)
1368 return self
1370 if TYPE_CHECKING:
1371 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1373 # code within this block is **programmatically,
1374 # statically generated** by tools/generate_tuple_map_overloads.py
1376 @overload
1377 def returning(
1378 self,
1379 __ent0: _TCCA[_T0],
1380 /,
1381 *,
1382 sort_by_parameter_order: bool = False,
1383 ) -> ReturningInsert[_T0]: ...
1385 @overload
1386 def returning(
1387 self,
1388 __ent0: _TCCA[_T0],
1389 __ent1: _TCCA[_T1],
1390 /,
1391 *,
1392 sort_by_parameter_order: bool = False,
1393 ) -> ReturningInsert[_T0, _T1]: ...
1395 @overload
1396 def returning(
1397 self,
1398 __ent0: _TCCA[_T0],
1399 __ent1: _TCCA[_T1],
1400 __ent2: _TCCA[_T2],
1401 /,
1402 *,
1403 sort_by_parameter_order: bool = False,
1404 ) -> ReturningInsert[_T0, _T1, _T2]: ...
1406 @overload
1407 def returning(
1408 self,
1409 __ent0: _TCCA[_T0],
1410 __ent1: _TCCA[_T1],
1411 __ent2: _TCCA[_T2],
1412 __ent3: _TCCA[_T3],
1413 /,
1414 *,
1415 sort_by_parameter_order: bool = False,
1416 ) -> ReturningInsert[_T0, _T1, _T2, _T3]: ...
1418 @overload
1419 def returning(
1420 self,
1421 __ent0: _TCCA[_T0],
1422 __ent1: _TCCA[_T1],
1423 __ent2: _TCCA[_T2],
1424 __ent3: _TCCA[_T3],
1425 __ent4: _TCCA[_T4],
1426 /,
1427 *,
1428 sort_by_parameter_order: bool = False,
1429 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4]: ...
1431 @overload
1432 def returning(
1433 self,
1434 __ent0: _TCCA[_T0],
1435 __ent1: _TCCA[_T1],
1436 __ent2: _TCCA[_T2],
1437 __ent3: _TCCA[_T3],
1438 __ent4: _TCCA[_T4],
1439 __ent5: _TCCA[_T5],
1440 /,
1441 *,
1442 sort_by_parameter_order: bool = False,
1443 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1445 @overload
1446 def returning(
1447 self,
1448 __ent0: _TCCA[_T0],
1449 __ent1: _TCCA[_T1],
1450 __ent2: _TCCA[_T2],
1451 __ent3: _TCCA[_T3],
1452 __ent4: _TCCA[_T4],
1453 __ent5: _TCCA[_T5],
1454 __ent6: _TCCA[_T6],
1455 /,
1456 *,
1457 sort_by_parameter_order: bool = False,
1458 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1460 @overload
1461 def returning(
1462 self,
1463 __ent0: _TCCA[_T0],
1464 __ent1: _TCCA[_T1],
1465 __ent2: _TCCA[_T2],
1466 __ent3: _TCCA[_T3],
1467 __ent4: _TCCA[_T4],
1468 __ent5: _TCCA[_T5],
1469 __ent6: _TCCA[_T6],
1470 __ent7: _TCCA[_T7],
1471 /,
1472 *entities: _ColumnsClauseArgument[Any],
1473 sort_by_parameter_order: bool = False,
1474 ) -> ReturningInsert[
1475 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1476 ]: ...
1478 # END OVERLOADED FUNCTIONS self.returning
1480 @overload
1481 def returning(
1482 self,
1483 *cols: _ColumnsClauseArgument[Any],
1484 sort_by_parameter_order: bool = False,
1485 **__kw: Any,
1486 ) -> ReturningInsert[Any]: ...
1488 def returning(
1489 self,
1490 *cols: _ColumnsClauseArgument[Any],
1491 sort_by_parameter_order: bool = False,
1492 **__kw: Any,
1493 ) -> ReturningInsert[Any]: ...
1496class ReturningInsert(Insert, TypedReturnsRows[Unpack[_Ts]]):
1497 """Typing-only class that establishes a generic type form of
1498 :class:`.Insert` which tracks returned column types.
1500 This datatype is delivered when calling the
1501 :meth:`.Insert.returning` method.
1503 .. versionadded:: 2.0
1505 """
1508# note: if not for MRO issues, this class should extend
1509# from HasSyntaxExtensions[Literal["post_criteria"]]
1510class DMLWhereBase:
1511 table: _DMLTableElement
1512 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1514 _post_criteria_clause: Optional[ClauseElement] = None
1515 """used by extensions to Update/Delete etc. to add additional syntacitcal
1516 constructs, e.g. LIMIT etc.
1518 .. versionadded:: 2.1
1520 """
1522 # can't put position_map here either without HasSyntaxExtensions
1523 # _position_map = util.immutabledict(
1524 # {"post_criteria": "_post_criteria_clause"}
1525 # )
1527 @_generative
1528 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1529 """Return a new construct with the given expression(s) added to
1530 its WHERE clause, joined to the existing clause via AND, if any.
1532 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1533 support multiple-table forms, including database-specific
1534 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1535 don't have multiple-table support, a backend agnostic approach
1536 to using multiple tables is to make use of correlated subqueries.
1537 See the linked tutorial sections below for examples.
1539 .. seealso::
1541 :ref:`tutorial_correlated_updates`
1543 :ref:`tutorial_update_from`
1545 :ref:`tutorial_multi_table_deletes`
1547 """
1549 for criterion in whereclause:
1550 where_criteria: ColumnElement[Any] = coercions.expect(
1551 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1552 )
1553 self._where_criteria += (where_criteria,)
1554 return self
1556 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1557 """A synonym for the :meth:`.where` method.
1559 .. versionadded:: 1.4
1561 """
1563 return self.where(*criteria)
1565 def filter_by(self, **kwargs: Any) -> Self:
1566 r"""Apply the given filtering criterion as a WHERE clause
1567 to this DML statement, using keyword expressions.
1569 E.g.::
1571 stmt = update(User).filter_by(name="some name").values(fullname="New Name")
1573 Multiple criteria may be specified as comma separated; the effect
1574 is that they will be joined together using the :func:`.and_`
1575 function::
1577 stmt = delete(User).filter_by(name="some name", id=5)
1579 The keyword expressions are extracted by searching across **all
1580 entities present in the FROM clause** of the statement.
1582 .. versionchanged:: 2.1
1584 :meth:`.DMLWhereBase.filter_by` now searches across all FROM clause
1585 entities, consistent with :meth:`_sql.Select.filter_by`.
1587 .. seealso::
1589 :meth:`.where` - filter on SQL expressions.
1591 :meth:`_sql.Select.filter_by`
1593 """ # noqa: E501
1595 entities: set[Any]
1597 if not isinstance(self.table, TableClause):
1598 entities = set(
1599 sql_util.find_tables(
1600 self.table, check_columns=False, include_joins=False
1601 )
1602 )
1603 else:
1604 entities = {self.table}
1606 if self.whereclause is not None:
1607 entities.update(self.whereclause._from_objects)
1609 clauses = [
1610 _entity_namespace_key_search_all(entities, key) == value
1611 for key, value in kwargs.items()
1612 ]
1613 return self.filter(*clauses)
1615 @property
1616 def whereclause(self) -> Optional[ColumnElement[Any]]:
1617 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1618 statement.
1620 This assembles the current collection of WHERE criteria
1621 into a single :class:`_expression.BooleanClauseList` construct.
1624 .. versionadded:: 1.4
1626 """
1628 return BooleanClauseList._construct_for_whereclause(
1629 self._where_criteria
1630 )
1633class Update(
1634 DMLWhereBase, ValuesBase, HasSyntaxExtensions[Literal["post_criteria"]]
1635):
1636 """Represent an Update construct.
1638 The :class:`_expression.Update` object is created using the
1639 :func:`_expression.update()` function.
1641 Available extension points:
1643 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1645 """
1647 __visit_name__ = "update"
1649 is_update = True
1651 _traverse_internals = (
1652 [
1653 ("table", InternalTraversal.dp_clauseelement),
1654 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1655 ("_inline", InternalTraversal.dp_boolean),
1656 ("_maintain_values_ordering", InternalTraversal.dp_boolean),
1657 ("_values", InternalTraversal.dp_dml_values),
1658 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1659 ("_hints", InternalTraversal.dp_table_hint_list),
1660 ("_return_defaults", InternalTraversal.dp_boolean),
1661 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1662 (
1663 "_return_defaults_columns",
1664 InternalTraversal.dp_clauseelement_tuple,
1665 ),
1666 ]
1667 + HasPrefixes._has_prefixes_traverse_internals
1668 + DialectKWArgs._dialect_kwargs_traverse_internals
1669 + ExecutableStatement._executable_traverse_internals
1670 + HasCTE._has_ctes_traverse_internals
1671 )
1673 _position_map = util.immutabledict(
1674 {"post_criteria": "_post_criteria_clause"}
1675 )
1677 def __init__(self, table: _DMLTableArgument):
1678 super().__init__(table)
1680 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1681 """Specify the VALUES clause of this UPDATE statement with an explicit
1682 parameter ordering that will be maintained in the SET clause of the
1683 resulting UPDATE statement.
1685 E.g.::
1687 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1689 .. seealso::
1691 :ref:`tutorial_parameter_ordered_updates` - full example of the
1692 :meth:`_expression.Update.ordered_values` method.
1694 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1695 method
1696 supersedes the
1697 :paramref:`_expression.update.preserve_parameter_order`
1698 parameter, which will be removed in SQLAlchemy 2.0.
1700 """ # noqa: E501
1701 if self._values:
1702 raise exc.ArgumentError(
1703 "This statement already has "
1704 f"{'ordered ' if self._maintain_values_ordering else ''}"
1705 "values present"
1706 )
1708 self = self.values(dict(args))
1709 self._maintain_values_ordering = True
1710 return self
1712 @_generative
1713 def inline(self) -> Self:
1714 """Make this :class:`_expression.Update` construct "inline" .
1716 When set, SQL defaults present on :class:`_schema.Column`
1717 objects via the
1718 ``default`` keyword will be compiled 'inline' into the statement and
1719 not pre-executed. This means that their values will not be available
1720 in the dictionary returned from
1721 :meth:`_engine.CursorResult.last_updated_params`.
1723 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1724 parameter
1725 is now superseded by the :meth:`_expression.Update.inline` method.
1727 """
1728 self._inline = True
1729 return self
1731 def _apply_syntax_extension_to_self(
1732 self, extension: SyntaxExtension
1733 ) -> None:
1734 extension.apply_to_update(self)
1736 if TYPE_CHECKING:
1737 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1739 # code within this block is **programmatically,
1740 # statically generated** by tools/generate_tuple_map_overloads.py
1742 @overload
1743 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningUpdate[_T0]: ...
1745 @overload
1746 def returning(
1747 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1748 ) -> ReturningUpdate[_T0, _T1]: ...
1750 @overload
1751 def returning(
1752 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1753 ) -> ReturningUpdate[_T0, _T1, _T2]: ...
1755 @overload
1756 def returning(
1757 self,
1758 __ent0: _TCCA[_T0],
1759 __ent1: _TCCA[_T1],
1760 __ent2: _TCCA[_T2],
1761 __ent3: _TCCA[_T3],
1762 /,
1763 ) -> ReturningUpdate[_T0, _T1, _T2, _T3]: ...
1765 @overload
1766 def returning(
1767 self,
1768 __ent0: _TCCA[_T0],
1769 __ent1: _TCCA[_T1],
1770 __ent2: _TCCA[_T2],
1771 __ent3: _TCCA[_T3],
1772 __ent4: _TCCA[_T4],
1773 /,
1774 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4]: ...
1776 @overload
1777 def returning(
1778 self,
1779 __ent0: _TCCA[_T0],
1780 __ent1: _TCCA[_T1],
1781 __ent2: _TCCA[_T2],
1782 __ent3: _TCCA[_T3],
1783 __ent4: _TCCA[_T4],
1784 __ent5: _TCCA[_T5],
1785 /,
1786 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1788 @overload
1789 def returning(
1790 self,
1791 __ent0: _TCCA[_T0],
1792 __ent1: _TCCA[_T1],
1793 __ent2: _TCCA[_T2],
1794 __ent3: _TCCA[_T3],
1795 __ent4: _TCCA[_T4],
1796 __ent5: _TCCA[_T5],
1797 __ent6: _TCCA[_T6],
1798 /,
1799 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1801 @overload
1802 def returning(
1803 self,
1804 __ent0: _TCCA[_T0],
1805 __ent1: _TCCA[_T1],
1806 __ent2: _TCCA[_T2],
1807 __ent3: _TCCA[_T3],
1808 __ent4: _TCCA[_T4],
1809 __ent5: _TCCA[_T5],
1810 __ent6: _TCCA[_T6],
1811 __ent7: _TCCA[_T7],
1812 /,
1813 *entities: _ColumnsClauseArgument[Any],
1814 ) -> ReturningUpdate[
1815 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1816 ]: ...
1818 # END OVERLOADED FUNCTIONS self.returning
1820 @overload
1821 def returning(
1822 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1823 ) -> ReturningUpdate[Any]: ...
1825 def returning(
1826 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1827 ) -> ReturningUpdate[Any]: ...
1830class ReturningUpdate(Update, TypedReturnsRows[Unpack[_Ts]]):
1831 """Typing-only class that establishes a generic type form of
1832 :class:`.Update` which tracks returned column types.
1834 This datatype is delivered when calling the
1835 :meth:`.Update.returning` method.
1837 .. versionadded:: 2.0
1839 """
1842class Delete(
1843 DMLWhereBase, UpdateBase, HasSyntaxExtensions[Literal["post_criteria"]]
1844):
1845 """Represent a DELETE construct.
1847 The :class:`_expression.Delete` object is created using the
1848 :func:`_expression.delete()` function.
1850 Available extension points:
1852 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1854 """
1856 __visit_name__ = "delete"
1858 is_delete = True
1860 _traverse_internals = (
1861 [
1862 ("table", InternalTraversal.dp_clauseelement),
1863 ("_extra_froms", InternalTraversal.dp_clauseelement_tuple),
1864 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1865 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1866 ("_hints", InternalTraversal.dp_table_hint_list),
1867 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1868 ]
1869 + HasPrefixes._has_prefixes_traverse_internals
1870 + DialectKWArgs._dialect_kwargs_traverse_internals
1871 + ExecutableStatement._executable_traverse_internals
1872 + HasCTE._has_ctes_traverse_internals
1873 )
1875 _position_map = util.immutabledict(
1876 {"post_criteria": "_post_criteria_clause"}
1877 )
1879 _extra_froms: Tuple[FromClause, ...] = ()
1881 def __init__(self, table: _DMLTableArgument):
1882 self.table = coercions.expect(
1883 roles.DMLTableRole, table, apply_propagate_attrs=self
1884 )
1886 @_generative
1887 def using(self, *froms: _FromClauseArgument) -> Self:
1888 r"""Add one or more explicit ``USING`` expressions to this DELETE.
1890 This method may be used for backend-specific multiple-table DELETE
1891 forms where the secondary FROM expression needs to be stated
1892 explicitly, such as MySQL's ``DELETE FROM table USING <join>`` form.
1894 .. versionadded:: 2.1
1896 .. seealso::
1898 :ref:`tutorial_multi_table_deletes`
1900 """
1902 self._extra_froms += tuple(
1903 coercions.expect(
1904 roles.FromClauseRole, from_, apply_propagate_attrs=self
1905 )
1906 for from_ in froms
1907 )
1908 return self
1910 def _apply_syntax_extension_to_self(
1911 self, extension: SyntaxExtension
1912 ) -> None:
1913 extension.apply_to_delete(self)
1915 if TYPE_CHECKING:
1916 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1918 # code within this block is **programmatically,
1919 # statically generated** by tools/generate_tuple_map_overloads.py
1921 @overload
1922 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningDelete[_T0]: ...
1924 @overload
1925 def returning(
1926 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1927 ) -> ReturningDelete[_T0, _T1]: ...
1929 @overload
1930 def returning(
1931 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1932 ) -> ReturningDelete[_T0, _T1, _T2]: ...
1934 @overload
1935 def returning(
1936 self,
1937 __ent0: _TCCA[_T0],
1938 __ent1: _TCCA[_T1],
1939 __ent2: _TCCA[_T2],
1940 __ent3: _TCCA[_T3],
1941 /,
1942 ) -> ReturningDelete[_T0, _T1, _T2, _T3]: ...
1944 @overload
1945 def returning(
1946 self,
1947 __ent0: _TCCA[_T0],
1948 __ent1: _TCCA[_T1],
1949 __ent2: _TCCA[_T2],
1950 __ent3: _TCCA[_T3],
1951 __ent4: _TCCA[_T4],
1952 /,
1953 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4]: ...
1955 @overload
1956 def returning(
1957 self,
1958 __ent0: _TCCA[_T0],
1959 __ent1: _TCCA[_T1],
1960 __ent2: _TCCA[_T2],
1961 __ent3: _TCCA[_T3],
1962 __ent4: _TCCA[_T4],
1963 __ent5: _TCCA[_T5],
1964 /,
1965 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1967 @overload
1968 def returning(
1969 self,
1970 __ent0: _TCCA[_T0],
1971 __ent1: _TCCA[_T1],
1972 __ent2: _TCCA[_T2],
1973 __ent3: _TCCA[_T3],
1974 __ent4: _TCCA[_T4],
1975 __ent5: _TCCA[_T5],
1976 __ent6: _TCCA[_T6],
1977 /,
1978 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1980 @overload
1981 def returning(
1982 self,
1983 __ent0: _TCCA[_T0],
1984 __ent1: _TCCA[_T1],
1985 __ent2: _TCCA[_T2],
1986 __ent3: _TCCA[_T3],
1987 __ent4: _TCCA[_T4],
1988 __ent5: _TCCA[_T5],
1989 __ent6: _TCCA[_T6],
1990 __ent7: _TCCA[_T7],
1991 /,
1992 *entities: _ColumnsClauseArgument[Any],
1993 ) -> ReturningDelete[
1994 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1995 ]: ...
1997 # END OVERLOADED FUNCTIONS self.returning
1999 @overload
2000 def returning(
2001 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
2002 ) -> ReturningDelete[Unpack[TupleAny]]: ...
2004 def returning(
2005 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
2006 ) -> ReturningDelete[Unpack[TupleAny]]: ...
2009class ReturningDelete(Update, TypedReturnsRows[Unpack[_Ts]]):
2010 """Typing-only class that establishes a generic type form of
2011 :class:`.Delete` which tracks returned column types.
2013 This datatype is delivered when calling the
2014 :meth:`.Delete.returning` method.
2016 .. versionadded:: 2.0
2018 """