Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/dml.py: 48%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/dml.py
2# Copyright (C) 2009-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7"""
8Provide :class:`_expression.Insert`, :class:`_expression.Update` and
9:class:`_expression.Delete`.
11"""
13from __future__ import annotations
15import collections.abc as collections_abc
16import operator
17from typing import Any
18from typing import cast
19from typing import Dict
20from typing import Iterable
21from typing import List
22from typing import 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 (
1267 "_supplemental_returning",
1268 InternalTraversal.dp_clauseelement_tuple,
1269 ),
1270 ("_sort_by_parameter_order", InternalTraversal.dp_boolean),
1271 ]
1272 + HasPrefixes._has_prefixes_traverse_internals
1273 + DialectKWArgs._dialect_kwargs_traverse_internals
1274 + ExecutableStatement._executable_traverse_internals
1275 + HasCTE._has_ctes_traverse_internals
1276 )
1278 _position_map = util.immutabledict(
1279 {
1280 "post_values": "_post_values_clause",
1281 }
1282 )
1284 _post_values_clause: Optional[ClauseElement] = None
1285 """extension point for a ClauseElement that will be compiled directly
1286 after the VALUES portion of the :class:`.Insert` statement
1288 """
1290 def __init__(self, table: _DMLTableArgument):
1291 super().__init__(table)
1293 def _apply_syntax_extension_to_self(
1294 self, extension: SyntaxExtension
1295 ) -> None:
1296 extension.apply_to_insert(self)
1298 @_generative
1299 def inline(self) -> Self:
1300 """Make this :class:`_expression.Insert` construct "inline" .
1302 When set, no attempt will be made to retrieve the
1303 SQL-generated default values to be provided within the statement;
1304 in particular,
1305 this allows SQL expressions to be rendered 'inline' within the
1306 statement without the need to pre-execute them beforehand; for
1307 backends that support "returning", this turns off the "implicit
1308 returning" feature for the statement.
1311 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline`
1312 parameter
1313 is now superseded by the :meth:`_expression.Insert.inline` method.
1315 """
1316 self._inline = True
1317 return self
1319 @_generative
1320 def from_select(
1321 self,
1322 names: Sequence[_DMLColumnArgument],
1323 select: Selectable,
1324 include_defaults: bool = True,
1325 ) -> Self:
1326 """Return a new :class:`_expression.Insert` construct which represents
1327 an ``INSERT...FROM SELECT`` statement.
1329 e.g.::
1331 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5)
1332 ins = table2.insert().from_select(["a", "b"], sel)
1334 :param names: a sequence of string column names or
1335 :class:`_schema.Column`
1336 objects representing the target columns.
1337 :param select: a :func:`_expression.select` construct,
1338 :class:`_expression.FromClause`
1339 or other construct which resolves into a
1340 :class:`_expression.FromClause`,
1341 such as an ORM :class:`_query.Query` object, etc. The order of
1342 columns returned from this FROM clause should correspond to the
1343 order of columns sent as the ``names`` parameter; while this
1344 is not checked before passing along to the database, the database
1345 would normally raise an exception if these column lists don't
1346 correspond.
1347 :param include_defaults: if True, non-server default values and
1348 SQL expressions as specified on :class:`_schema.Column` objects
1349 (as documented in :ref:`metadata_defaults_toplevel`) not
1350 otherwise specified in the list of names will be rendered
1351 into the INSERT and SELECT statements, so that these values are also
1352 included in the data to be inserted.
1354 .. note:: A Python-side default that uses a Python callable function
1355 will only be invoked **once** for the whole statement, and **not
1356 per row**.
1358 """
1360 if self._values:
1361 raise exc.InvalidRequestError(
1362 "This construct already inserts value expressions"
1363 )
1365 self._select_names = [
1366 coercions.expect(roles.DMLColumnRole, name, as_key=True)
1367 for name in names
1368 ]
1369 self._inline = True
1370 self.include_insert_from_select_defaults = include_defaults
1371 self.select = coercions.expect(roles.DMLSelectRole, select)
1372 return self
1374 if TYPE_CHECKING:
1375 # START OVERLOADED FUNCTIONS self.returning ReturningInsert 1-8 ", *, sort_by_parameter_order: bool = False" # noqa: E501
1377 # code within this block is **programmatically,
1378 # statically generated** by tools/generate_tuple_map_overloads.py
1380 @overload
1381 def returning(
1382 self,
1383 __ent0: _TCCA[_T0],
1384 /,
1385 *,
1386 sort_by_parameter_order: bool = False,
1387 ) -> ReturningInsert[_T0]: ...
1389 @overload
1390 def returning(
1391 self,
1392 __ent0: _TCCA[_T0],
1393 __ent1: _TCCA[_T1],
1394 /,
1395 *,
1396 sort_by_parameter_order: bool = False,
1397 ) -> ReturningInsert[_T0, _T1]: ...
1399 @overload
1400 def returning(
1401 self,
1402 __ent0: _TCCA[_T0],
1403 __ent1: _TCCA[_T1],
1404 __ent2: _TCCA[_T2],
1405 /,
1406 *,
1407 sort_by_parameter_order: bool = False,
1408 ) -> ReturningInsert[_T0, _T1, _T2]: ...
1410 @overload
1411 def returning(
1412 self,
1413 __ent0: _TCCA[_T0],
1414 __ent1: _TCCA[_T1],
1415 __ent2: _TCCA[_T2],
1416 __ent3: _TCCA[_T3],
1417 /,
1418 *,
1419 sort_by_parameter_order: bool = False,
1420 ) -> ReturningInsert[_T0, _T1, _T2, _T3]: ...
1422 @overload
1423 def returning(
1424 self,
1425 __ent0: _TCCA[_T0],
1426 __ent1: _TCCA[_T1],
1427 __ent2: _TCCA[_T2],
1428 __ent3: _TCCA[_T3],
1429 __ent4: _TCCA[_T4],
1430 /,
1431 *,
1432 sort_by_parameter_order: bool = False,
1433 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4]: ...
1435 @overload
1436 def returning(
1437 self,
1438 __ent0: _TCCA[_T0],
1439 __ent1: _TCCA[_T1],
1440 __ent2: _TCCA[_T2],
1441 __ent3: _TCCA[_T3],
1442 __ent4: _TCCA[_T4],
1443 __ent5: _TCCA[_T5],
1444 /,
1445 *,
1446 sort_by_parameter_order: bool = False,
1447 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1449 @overload
1450 def returning(
1451 self,
1452 __ent0: _TCCA[_T0],
1453 __ent1: _TCCA[_T1],
1454 __ent2: _TCCA[_T2],
1455 __ent3: _TCCA[_T3],
1456 __ent4: _TCCA[_T4],
1457 __ent5: _TCCA[_T5],
1458 __ent6: _TCCA[_T6],
1459 /,
1460 *,
1461 sort_by_parameter_order: bool = False,
1462 ) -> ReturningInsert[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1464 @overload
1465 def returning(
1466 self,
1467 __ent0: _TCCA[_T0],
1468 __ent1: _TCCA[_T1],
1469 __ent2: _TCCA[_T2],
1470 __ent3: _TCCA[_T3],
1471 __ent4: _TCCA[_T4],
1472 __ent5: _TCCA[_T5],
1473 __ent6: _TCCA[_T6],
1474 __ent7: _TCCA[_T7],
1475 /,
1476 *entities: _ColumnsClauseArgument[Any],
1477 sort_by_parameter_order: bool = False,
1478 ) -> ReturningInsert[
1479 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1480 ]: ...
1482 # END OVERLOADED FUNCTIONS self.returning
1484 @overload
1485 def returning(
1486 self,
1487 *cols: _ColumnsClauseArgument[Any],
1488 sort_by_parameter_order: bool = False,
1489 **__kw: Any,
1490 ) -> ReturningInsert[Any]: ...
1492 def returning(
1493 self,
1494 *cols: _ColumnsClauseArgument[Any],
1495 sort_by_parameter_order: bool = False,
1496 **__kw: Any,
1497 ) -> ReturningInsert[Any]: ...
1500class ReturningInsert(Insert, TypedReturnsRows[Unpack[_Ts]]):
1501 """Typing-only class that establishes a generic type form of
1502 :class:`.Insert` which tracks returned column types.
1504 This datatype is delivered when calling the
1505 :meth:`.Insert.returning` method.
1507 .. versionadded:: 2.0
1509 """
1512# note: if not for MRO issues, this class should extend
1513# from HasSyntaxExtensions[Literal["post_criteria"]]
1514class DMLWhereBase:
1515 table: _DMLTableElement
1516 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
1518 _post_criteria_clause: Optional[ClauseElement] = None
1519 """used by extensions to Update/Delete etc. to add additional syntacitcal
1520 constructs, e.g. LIMIT etc.
1522 .. versionadded:: 2.1
1524 """
1526 # can't put position_map here either without HasSyntaxExtensions
1527 # _position_map = util.immutabledict(
1528 # {"post_criteria": "_post_criteria_clause"}
1529 # )
1531 @_generative
1532 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
1533 """Return a new construct with the given expression(s) added to
1534 its WHERE clause, joined to the existing clause via AND, if any.
1536 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
1537 support multiple-table forms, including database-specific
1538 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
1539 don't have multiple-table support, a backend agnostic approach
1540 to using multiple tables is to make use of correlated subqueries.
1541 See the linked tutorial sections below for examples.
1543 .. seealso::
1545 :ref:`tutorial_correlated_updates`
1547 :ref:`tutorial_update_from`
1549 :ref:`tutorial_multi_table_deletes`
1551 """
1553 for criterion in whereclause:
1554 where_criteria: ColumnElement[Any] = coercions.expect(
1555 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1556 )
1557 self._where_criteria += (where_criteria,)
1558 return self
1560 def filter(self, *criteria: roles.ExpressionElementRole[Any]) -> Self:
1561 """A synonym for the :meth:`.where` method.
1563 .. versionadded:: 1.4
1565 """
1567 return self.where(*criteria)
1569 def filter_by(self, **kwargs: Any) -> Self:
1570 r"""Apply the given filtering criterion as a WHERE clause
1571 to this DML statement, using keyword expressions.
1573 E.g.::
1575 stmt = update(User).filter_by(name="some name").values(fullname="New Name")
1577 Multiple criteria may be specified as comma separated; the effect
1578 is that they will be joined together using the :func:`.and_`
1579 function::
1581 stmt = delete(User).filter_by(name="some name", id=5)
1583 The keyword expressions are extracted by searching across **all
1584 entities present in the FROM clause** of the statement.
1586 .. versionchanged:: 2.1
1588 :meth:`.DMLWhereBase.filter_by` now searches across all FROM clause
1589 entities, consistent with :meth:`_sql.Select.filter_by`.
1591 .. seealso::
1593 :meth:`.where` - filter on SQL expressions.
1595 :meth:`_sql.Select.filter_by`
1597 """ # noqa: E501
1599 entities: set[Any]
1601 if not isinstance(self.table, TableClause):
1602 entities = set(
1603 sql_util.find_tables(
1604 self.table, check_columns=False, include_joins=False
1605 )
1606 )
1607 else:
1608 entities = {self.table}
1610 if self.whereclause is not None:
1611 entities.update(self.whereclause._from_objects)
1613 clauses = [
1614 _entity_namespace_key_search_all(entities, key) == value
1615 for key, value in kwargs.items()
1616 ]
1617 return self.filter(*clauses)
1619 @property
1620 def whereclause(self) -> Optional[ColumnElement[Any]]:
1621 """Return the completed WHERE clause for this :class:`.DMLWhereBase`
1622 statement.
1624 This assembles the current collection of WHERE criteria
1625 into a single :class:`_expression.BooleanClauseList` construct.
1628 .. versionadded:: 1.4
1630 """
1632 return BooleanClauseList._construct_for_whereclause(
1633 self._where_criteria
1634 )
1637class Update(
1638 DMLWhereBase, ValuesBase, HasSyntaxExtensions[Literal["post_criteria"]]
1639):
1640 """Represent an Update construct.
1642 The :class:`_expression.Update` object is created using the
1643 :func:`_expression.update()` function.
1645 Available extension points:
1647 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1649 """
1651 __visit_name__ = "update"
1653 is_update = True
1655 _traverse_internals = (
1656 [
1657 ("table", InternalTraversal.dp_clauseelement),
1658 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1659 ("_inline", InternalTraversal.dp_boolean),
1660 ("_maintain_values_ordering", InternalTraversal.dp_boolean),
1661 ("_values", InternalTraversal.dp_dml_values),
1662 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1663 ("_hints", InternalTraversal.dp_table_hint_list),
1664 ("_return_defaults", InternalTraversal.dp_boolean),
1665 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1666 (
1667 "_return_defaults_columns",
1668 InternalTraversal.dp_clauseelement_tuple,
1669 ),
1670 (
1671 "_supplemental_returning",
1672 InternalTraversal.dp_clauseelement_tuple,
1673 ),
1674 ]
1675 + HasPrefixes._has_prefixes_traverse_internals
1676 + DialectKWArgs._dialect_kwargs_traverse_internals
1677 + ExecutableStatement._executable_traverse_internals
1678 + HasCTE._has_ctes_traverse_internals
1679 )
1681 _position_map = util.immutabledict(
1682 {"post_criteria": "_post_criteria_clause"}
1683 )
1685 def __init__(self, table: _DMLTableArgument):
1686 super().__init__(table)
1688 def ordered_values(self, *args: Tuple[_DMLColumnArgument, Any]) -> Self:
1689 """Specify the VALUES clause of this UPDATE statement with an explicit
1690 parameter ordering that will be maintained in the SET clause of the
1691 resulting UPDATE statement.
1693 E.g.::
1695 stmt = table.update().ordered_values(("name", "ed"), ("ident", "foo"))
1697 .. seealso::
1699 :ref:`tutorial_parameter_ordered_updates` - full example of the
1700 :meth:`_expression.Update.ordered_values` method.
1702 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
1703 method
1704 supersedes the
1705 :paramref:`_expression.update.preserve_parameter_order`
1706 parameter, which will be removed in SQLAlchemy 2.0.
1708 """ # noqa: E501
1709 if self._values:
1710 raise exc.ArgumentError(
1711 "This statement already has "
1712 f"{'ordered ' if self._maintain_values_ordering else ''}"
1713 "values present"
1714 )
1716 self = self.values(dict(args))
1717 self._maintain_values_ordering = True
1718 return self
1720 @_generative
1721 def inline(self) -> Self:
1722 """Make this :class:`_expression.Update` construct "inline" .
1724 When set, SQL defaults present on :class:`_schema.Column`
1725 objects via the
1726 ``default`` keyword will be compiled 'inline' into the statement and
1727 not pre-executed. This means that their values will not be available
1728 in the dictionary returned from
1729 :meth:`_engine.CursorResult.last_updated_params`.
1731 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
1732 parameter
1733 is now superseded by the :meth:`_expression.Update.inline` method.
1735 """
1736 self._inline = True
1737 return self
1739 def _apply_syntax_extension_to_self(
1740 self, extension: SyntaxExtension
1741 ) -> None:
1742 extension.apply_to_update(self)
1744 if TYPE_CHECKING:
1745 # START OVERLOADED FUNCTIONS self.returning ReturningUpdate 1-8
1747 # code within this block is **programmatically,
1748 # statically generated** by tools/generate_tuple_map_overloads.py
1750 @overload
1751 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningUpdate[_T0]: ...
1753 @overload
1754 def returning(
1755 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1756 ) -> ReturningUpdate[_T0, _T1]: ...
1758 @overload
1759 def returning(
1760 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1761 ) -> ReturningUpdate[_T0, _T1, _T2]: ...
1763 @overload
1764 def returning(
1765 self,
1766 __ent0: _TCCA[_T0],
1767 __ent1: _TCCA[_T1],
1768 __ent2: _TCCA[_T2],
1769 __ent3: _TCCA[_T3],
1770 /,
1771 ) -> ReturningUpdate[_T0, _T1, _T2, _T3]: ...
1773 @overload
1774 def returning(
1775 self,
1776 __ent0: _TCCA[_T0],
1777 __ent1: _TCCA[_T1],
1778 __ent2: _TCCA[_T2],
1779 __ent3: _TCCA[_T3],
1780 __ent4: _TCCA[_T4],
1781 /,
1782 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4]: ...
1784 @overload
1785 def returning(
1786 self,
1787 __ent0: _TCCA[_T0],
1788 __ent1: _TCCA[_T1],
1789 __ent2: _TCCA[_T2],
1790 __ent3: _TCCA[_T3],
1791 __ent4: _TCCA[_T4],
1792 __ent5: _TCCA[_T5],
1793 /,
1794 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1796 @overload
1797 def returning(
1798 self,
1799 __ent0: _TCCA[_T0],
1800 __ent1: _TCCA[_T1],
1801 __ent2: _TCCA[_T2],
1802 __ent3: _TCCA[_T3],
1803 __ent4: _TCCA[_T4],
1804 __ent5: _TCCA[_T5],
1805 __ent6: _TCCA[_T6],
1806 /,
1807 ) -> ReturningUpdate[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1809 @overload
1810 def returning(
1811 self,
1812 __ent0: _TCCA[_T0],
1813 __ent1: _TCCA[_T1],
1814 __ent2: _TCCA[_T2],
1815 __ent3: _TCCA[_T3],
1816 __ent4: _TCCA[_T4],
1817 __ent5: _TCCA[_T5],
1818 __ent6: _TCCA[_T6],
1819 __ent7: _TCCA[_T7],
1820 /,
1821 *entities: _ColumnsClauseArgument[Any],
1822 ) -> ReturningUpdate[
1823 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1824 ]: ...
1826 # END OVERLOADED FUNCTIONS self.returning
1828 @overload
1829 def returning(
1830 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1831 ) -> ReturningUpdate[Any]: ...
1833 def returning(
1834 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
1835 ) -> ReturningUpdate[Any]: ...
1838class ReturningUpdate(Update, TypedReturnsRows[Unpack[_Ts]]):
1839 """Typing-only class that establishes a generic type form of
1840 :class:`.Update` which tracks returned column types.
1842 This datatype is delivered when calling the
1843 :meth:`.Update.returning` method.
1845 .. versionadded:: 2.0
1847 """
1850class Delete(
1851 DMLWhereBase, UpdateBase, HasSyntaxExtensions[Literal["post_criteria"]]
1852):
1853 """Represent a DELETE construct.
1855 The :class:`_expression.Delete` object is created using the
1856 :func:`_expression.delete()` function.
1858 Available extension points:
1860 * ``post_criteria``: applies additional logic after the ``WHERE`` clause.
1862 """
1864 __visit_name__ = "delete"
1866 is_delete = True
1868 _traverse_internals = (
1869 [
1870 ("table", InternalTraversal.dp_clauseelement),
1871 ("_extra_froms", InternalTraversal.dp_clauseelement_tuple),
1872 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
1873 ("_returning", InternalTraversal.dp_clauseelement_tuple),
1874 ("_hints", InternalTraversal.dp_table_hint_list),
1875 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),
1876 ]
1877 + HasPrefixes._has_prefixes_traverse_internals
1878 + DialectKWArgs._dialect_kwargs_traverse_internals
1879 + ExecutableStatement._executable_traverse_internals
1880 + HasCTE._has_ctes_traverse_internals
1881 )
1883 _position_map = util.immutabledict(
1884 {"post_criteria": "_post_criteria_clause"}
1885 )
1887 _extra_froms: Tuple[FromClause, ...] = ()
1889 def __init__(self, table: _DMLTableArgument):
1890 self.table = coercions.expect(
1891 roles.DMLTableRole, table, apply_propagate_attrs=self
1892 )
1894 @_generative
1895 def using(self, *froms: _FromClauseArgument) -> Self:
1896 r"""Add one or more explicit ``USING`` expressions to this DELETE.
1898 This method may be used for backend-specific multiple-table DELETE
1899 forms where the secondary FROM expression needs to be stated
1900 explicitly, such as MySQL's ``DELETE FROM table USING <join>`` form.
1902 .. versionadded:: 2.1
1904 .. seealso::
1906 :ref:`tutorial_multi_table_deletes`
1908 """
1910 self._extra_froms += tuple(
1911 coercions.expect(
1912 roles.FromClauseRole, from_, apply_propagate_attrs=self
1913 )
1914 for from_ in froms
1915 )
1916 return self
1918 def _apply_syntax_extension_to_self(
1919 self, extension: SyntaxExtension
1920 ) -> None:
1921 extension.apply_to_delete(self)
1923 if TYPE_CHECKING:
1924 # START OVERLOADED FUNCTIONS self.returning ReturningDelete 1-8
1926 # code within this block is **programmatically,
1927 # statically generated** by tools/generate_tuple_map_overloads.py
1929 @overload
1930 def returning(self, __ent0: _TCCA[_T0], /) -> ReturningDelete[_T0]: ...
1932 @overload
1933 def returning(
1934 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1935 ) -> ReturningDelete[_T0, _T1]: ...
1937 @overload
1938 def returning(
1939 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1940 ) -> ReturningDelete[_T0, _T1, _T2]: ...
1942 @overload
1943 def returning(
1944 self,
1945 __ent0: _TCCA[_T0],
1946 __ent1: _TCCA[_T1],
1947 __ent2: _TCCA[_T2],
1948 __ent3: _TCCA[_T3],
1949 /,
1950 ) -> ReturningDelete[_T0, _T1, _T2, _T3]: ...
1952 @overload
1953 def returning(
1954 self,
1955 __ent0: _TCCA[_T0],
1956 __ent1: _TCCA[_T1],
1957 __ent2: _TCCA[_T2],
1958 __ent3: _TCCA[_T3],
1959 __ent4: _TCCA[_T4],
1960 /,
1961 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4]: ...
1963 @overload
1964 def returning(
1965 self,
1966 __ent0: _TCCA[_T0],
1967 __ent1: _TCCA[_T1],
1968 __ent2: _TCCA[_T2],
1969 __ent3: _TCCA[_T3],
1970 __ent4: _TCCA[_T4],
1971 __ent5: _TCCA[_T5],
1972 /,
1973 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1975 @overload
1976 def returning(
1977 self,
1978 __ent0: _TCCA[_T0],
1979 __ent1: _TCCA[_T1],
1980 __ent2: _TCCA[_T2],
1981 __ent3: _TCCA[_T3],
1982 __ent4: _TCCA[_T4],
1983 __ent5: _TCCA[_T5],
1984 __ent6: _TCCA[_T6],
1985 /,
1986 ) -> ReturningDelete[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1988 @overload
1989 def returning(
1990 self,
1991 __ent0: _TCCA[_T0],
1992 __ent1: _TCCA[_T1],
1993 __ent2: _TCCA[_T2],
1994 __ent3: _TCCA[_T3],
1995 __ent4: _TCCA[_T4],
1996 __ent5: _TCCA[_T5],
1997 __ent6: _TCCA[_T6],
1998 __ent7: _TCCA[_T7],
1999 /,
2000 *entities: _ColumnsClauseArgument[Any],
2001 ) -> ReturningDelete[
2002 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
2003 ]: ...
2005 # END OVERLOADED FUNCTIONS self.returning
2007 @overload
2008 def returning(
2009 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
2010 ) -> ReturningDelete[Unpack[TupleAny]]: ...
2012 def returning(
2013 self, *cols: _ColumnsClauseArgument[Any], **__kw: Any
2014 ) -> ReturningDelete[Unpack[TupleAny]]: ...
2017class ReturningDelete(Update, TypedReturnsRows[Unpack[_Ts]]):
2018 """Typing-only class that establishes a generic type form of
2019 :class:`.Delete` which tracks returned column types.
2021 This datatype is delivered when calling the
2022 :meth:`.Delete.returning` method.
2024 .. versionadded:: 2.0
2026 """