codekingpro/portable-devtools
115k
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`.
10
11"""
12from __future__ import annotations
13
14import collections.abc as collections_abc
15import operator
16from typing import Any
17from typing import cast
18from typing import Dict
19from typing import Iterable
20from typing import List
21from typing import MutableMapping
22from typing import NoReturn
23from typing import Optional
24from typing import overload
25from typing import Sequence
26from typing import Set
27from typing import Tuple
28from typing import Type
29from typing import TYPE_CHECKING
30from typing import TypeVar
31from typing import Union
32
33from . import coercions
34from . import roles
35from . import util as sql_util
36from ._typing import _TP
37from ._typing import _unexpected_kw
38from ._typing import is_column_element
39from ._typing import is_named_from_clause
40from .base import _entity_namespace_key
41from .base import _exclusive_against
42from .base import _from_objects
43from .base import _generative
44from .base import _select_iterables
45from .base import ColumnCollection
46from .base import ColumnSet
47from .base import CompileState
48from .base import DialectKWArgs
49from .base import Executable
50from .base import Generative
51from .base import HasCompileState
52from .elements import BooleanClauseList
53from .elements import ClauseElement
54from .elements import ColumnClause
55from .elements import ColumnElement
56from .elements import Null
57from .selectable import Alias
58from .selectable import ExecutableReturnsRows
59from .selectable import FromClause
60from .selectable import HasCTE
61from .selectable import HasPrefixes
62from .selectable import Join
63from .selectable import SelectLabelStyle
64from .selectable import TableClause
65from .selectable import TypedReturnsRows
66from .sqltypes import NullType
67from .visitors import InternalTraversal
68from .. import exc
69from .. import util
70from ..util.typing import Self
71from ..util.typing import TypeGuard
72
73if TYPE_CHECKING:
74 from ._typing import _ColumnExpressionArgument
75 from ._typing import _ColumnsClauseArgument
76 from ._typing import _DMLColumnArgument
77 from ._typing import _DMLColumnKeyMapping
78 from ._typing import _DMLTableArgument
79 from ._typing import _T0 # noqa
80 from ._typing import _T1 # noqa
81 from ._typing import _T2 # noqa
82 from ._typing import _T3 # noqa
83 from ._typing import _T4 # noqa
84 from ._typing import _T5 # noqa
85 from ._typing import _T6 # noqa
86 from ._typing import _T7 # noqa
87 from ._typing import _TypedColumnClauseArgument as _TCCA # noqa
88 from .base import ReadOnlyColumnCollection
89 from .compiler import SQLCompiler
90 from .elements import KeyedColumnElement
91 from .selectable import _ColumnsClauseElement
92 from .selectable import _SelectIterable
93 from .selectable import Select
94 from .selectable import Selectable
95
96 def isupdate(dml: DMLState) -> TypeGuard[UpdateDMLState]: ...
97
98 def isdelete(dml: DMLState) -> TypeGuard[DeleteDMLState]: ...
99
100 def isinsert(dml: DMLState) -> TypeGuard[InsertDMLState]: ...
101
102else:
103 isupdate = operator.attrgetter("isupdate")
104 isdelete = operator.attrgetter("isdelete")
105 isinsert = operator.attrgetter("isinsert")
106
107
108_T = TypeVar("_T", bound=Any)
109
110_DMLColumnElement = Union[str, ColumnClause[Any]]
111_DMLTableElement = Union[TableClause, Alias, Join]
112
113
114class DMLState(CompileState):
115 _no_parameters = True
116 _dict_parameters: Optional[MutableMapping[_DMLColumnElement, Any]] = None
117 _multi_parameters: Optional[
118 List[MutableMapping[_DMLColumnElement, Any]]
119 ] = None
120 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None
121 _parameter_ordering: Optional[List[_DMLColumnElement]] = None
122 _primary_table: FromClause
123 _supports_implicit_returning = True
124
125 isupdate = False
126 isdelete = False
127 isinsert = False
128
129 statement: UpdateBase
130
131 def __init__(
132 self, statement: UpdateBase, compiler: SQLCompiler, **kw: Any
133 ):
134 raise NotImplementedError()
135
136 @classmethod
137 def get_entity_description(cls, statement: UpdateBase) -> Dict[str, Any]:
138 return {
139 "name": (
140 statement.table.name
141 if is_named_from_clause(statement.table)
142 else None
143 ),
144 "table": statement.table,
145 }
146
147 @classmethod
148 def get_returning_column_descriptions(
149 cls, statement: UpdateBase
150 ) -> List[Dict[str, Any]]:
151 return [
152 {
153 "name": c.key,
154 "type": c.type,
155 "expr": c,
156 }
157 for c in statement._all_selected_columns
158 ]
159
160 @property
161 def dml_table(self) -> _DMLTableElement:
162 return self.statement.table
163
164 if TYPE_CHECKING:
165
166 @classmethod
167 def get_plugin_class(cls, statement: Executable) -> Type[DMLState]: ...
168
169 @classmethod
170 def _get_multi_crud_kv_pairs(
171 cls,
172 statement: UpdateBase,
173 multi_kv_iterator: Iterable[Dict[_DMLColumnArgument, Any]],
174 ) -> List[Dict[_DMLColumnElement, Any]]:
175 return [
176 {
177 coercions.expect(roles.DMLColumnRole, k): v
178 for k, v in mapping.items()
179 }
180 for mapping in multi_kv_iterator
181 ]
182
183 @classmethod
184 def _get_crud_kv_pairs(
185 cls,
186 statement: UpdateBase,
187 kv_iterator: Iterable[Tuple[_DMLColumnArgument, Any]],
188 needs_to_be_cacheable: bool,
189 ) -> List[Tuple[_DMLColumnElement, Any]]:
190 return [
191 (
192 coercions.expect(roles.DMLColumnRole, k),
193 (
194 v
195 if not needs_to_be_cacheable
196 else coercions.expect(
197 roles.ExpressionElementRole,
198 v,
199 type_=NullType(),
200 is_crud=True,
201 )
202 ),
203 )
204 for k, v in kv_iterator
205 ]
206
207 def _make_extra_froms(
208 self, statement: DMLWhereBase
209 ) -> Tuple[FromClause, List[FromClause]]:
210 froms: List[FromClause] = []
211
212 all_tables = list(sql_util.tables_from_leftmost(statement.table))
213 primary_table = all_tables[0]
214 seen = {primary_table}
215
216 consider = statement._where_criteria
217 if self._dict_parameters:
218 consider += tuple(self._dict_parameters.values())
219
220 for crit in consider:
221 for item in _from_objects(crit):
222 if not seen.intersection(item._cloned_set):
223 froms.append(item)
224 seen.update(item._cloned_set)
225
226 froms.extend(all_tables[1:])
227 return primary_table, froms
228
229 def _process_values(self, statement: ValuesBase) -> None:
230 if self._no_parameters:
231 self._dict_parameters = statement._values
232 self._no_parameters = False
233
234 def _process_select_values(self, statement: ValuesBase) -> None:
235 assert statement._select_names is not None
236 parameters: MutableMapping[_DMLColumnElement, Any] = {
237 name: Null() for name in statement._select_names
238 }
239
240 if self._no_parameters:
241 self._no_parameters = False
242 self._dict_parameters = parameters
243 else:
244 # this condition normally not reachable as the Insert
245 # does not allow this construction to occur
246 assert False, "This statement already has parameters"
247
248 def _no_multi_values_supported(self, statement: ValuesBase) -> NoReturn:
249 raise exc.InvalidRequestError(
250 "%s construct does not support "
251 "multiple parameter sets." % statement.__visit_name__.upper()
252 )
253
254 def _cant_mix_formats_error(self) -> NoReturn:
255 raise exc.InvalidRequestError(
256 "Can't mix single and multiple VALUES "
257 "formats in one INSERT statement; one style appends to a "
258 "list while the other replaces values, so the intent is "
259 "ambiguous."
260 )
261
262
263@CompileState.plugin_for("default", "insert")
264class InsertDMLState(DMLState):
265 isinsert = True
266
267 include_table_with_column_exprs = False
268
269 _has_multi_parameters = False
270
271 def __init__(
272 self,
273 statement: Insert,
274 compiler: SQLCompiler,
275 disable_implicit_returning: bool = False,
276 **kw: Any,
277 ):
278 self.statement = statement
279 self._primary_table = statement.table
280
281 if disable_implicit_returning:
282 self._supports_implicit_returning = False
283
284 self.isinsert = True
285 if statement._select_names:
286 self._process_select_values(statement)
287 if statement._values is not None:
288 self._process_values(statement)
289 if statement._multi_values:
290 self._process_multi_values(statement)
291
292 @util.memoized_property
293 def _insert_col_keys(self) -> List[str]:
294 # this is also done in crud.py -> _key_getters_for_crud_column
295 return [
296 coercions.expect(roles.DMLColumnRole, col, as_key=True)
297 for col in self._dict_parameters or ()
298 ]
299
300 def _process_values(self, statement: ValuesBase) -> None:
301 if self._no_parameters:
302 self._has_multi_parameters = False
303 self._dict_parameters = statement._values
304 self._no_parameters = False
305 elif self._has_multi_parameters:
306 self._cant_mix_formats_error()
307
308 def _process_multi_values(self, statement: ValuesBase) -> None:
309 for parameters in statement._multi_values:
310 multi_parameters: List[MutableMapping[_DMLColumnElement, Any]] = [
311 (
312 {
313 c.key: value
314 for c, value in zip(statement.table.c, parameter_set)
315 }
316 if isinstance(parameter_set, collections_abc.Sequence)
317 else parameter_set
318 )
319 for parameter_set in parameters
320 ]
321
322 if self._no_parameters:
323 self._no_parameters = False
324 self._has_multi_parameters = True
325 self._multi_parameters = multi_parameters
326 self._dict_parameters = self._multi_parameters[0]
327 elif not self._has_multi_parameters:
328 self._cant_mix_formats_error()
329 else:
330 assert self._multi_parameters
331 self._multi_parameters.extend(multi_parameters)
332
333
334@CompileState.plugin_for("default", "update")
335class UpdateDMLState(DMLState):
336 isupdate = True
337
338 include_table_with_column_exprs = False
339
340 def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any):
341 self.statement = statement
342
343 self.isupdate = True
344 if statement._ordered_values is not None:
345 self._process_ordered_values(statement)
346 elif statement._values is not None:
347 self._process_values(statement)
348 elif statement._multi_values:
349 self._no_multi_values_supported(statement)
350 t, ef = self._make_extra_froms(statement)
351 self._primary_table = t
352 self._extra_froms = ef
353
354 self.is_multitable = mt = ef
355 self.include_table_with_column_exprs = bool(
356 mt and compiler.render_table_with_column_in_update_from
357 )
358
359 def _process_ordered_values(self, statement: ValuesBase) -> None:
360 parameters = statement._ordered_values
361
362 if self._no_parameters:
363 self._no_parameters = False
364 assert parameters is not None
365 self._dict_parameters = dict(parameters)
366 self._ordered_values = parameters
367 self._parameter_ordering = [key for key, value in parameters]
368 else:
369 raise exc.InvalidRequestError(
370 "Can only invoke ordered_values() once, and not mixed "
371 "with any other values() call"
372 )
373
374
375@CompileState.plugin_for("default", "delete")
376class DeleteDMLState(DMLState):
377 isdelete = True
378
379 def __init__(self, statement: Delete, compiler: SQLCompiler, **kw: Any):
380 self.statement = statement
381
382 self.isdelete = True
383 t, ef = self._make_extra_froms(statement)
384 self._primary_table = t
385 self._extra_froms = ef
386 self.is_multitable = ef
387
388
389class UpdateBase(
390 roles.DMLRole,
391 HasCTE,
392 HasCompileState,
393 DialectKWArgs,
394 HasPrefixes,
395 Generative,
396 ExecutableReturnsRows,
397 ClauseElement,
398):
399 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
400
401 __visit_name__ = "update_base"
402
403 _hints: util.immutabledict[Tuple[_DMLTableElement, str], str] = (
404 util.EMPTY_DICT
405 )
406 named_with_column = False
407
408 _label_style: SelectLabelStyle = (
409 SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY
410 )
411 table: _DMLTableElement
412
413 _return_defaults = False
414 _return_defaults_columns: Optional[Tuple[_ColumnsClauseElement, ...]] = (
415 None
416 )
417 _supplemental_returning: Optional[Tuple[_ColumnsClauseElement, ...]] = None
418 _returning: Tuple[_ColumnsClauseElement, ...] = ()
419
420 is_dml = True
421
422 def _generate_fromclause_column_proxies(
423 self,
424 fromclause: FromClause,
425 columns: ColumnCollection[str, KeyedColumnElement[Any]],
426 primary_key: ColumnSet,
427 foreign_keys: Set[KeyedColumnElement[Any]],
428 ) -> None:
429 prox = [
430 c._make_proxy(
431 fromclause,
432 key=proxy_key,
433 name=required_label_name,
434 name_is_truncatable=True,
435 primary_key=primary_key,
436 foreign_keys=foreign_keys,
437 )
438 for (
439 required_label_name,
440 proxy_key,
441 fallback_label_name,
442 c,
443 repeated,
444 ) in (self._generate_columns_plus_names(False))
445 if is_column_element(c)
446 ]
447
448 columns._populate_separate_keys(prox)
449
450 def params(self, *arg: Any, **kw: Any) -> NoReturn:
451 """Set the parameters for the statement.
452
453 This method raises ``NotImplementedError`` on the base class,
454 and is overridden by :class:`.ValuesBase` to provide the
455 SET/VALUES clause of UPDATE and INSERT.
456
457 """
458 raise NotImplementedError(
459 "params() is not supported for INSERT/UPDATE/DELETE statements."
460 " To set the values for an INSERT or UPDATE statement, use"
461 " stmt.values(**parameters)."
462 )
463
464 @_generative
465 def with_dialect_options(self, **opt: Any) -> Self:
466 """Add dialect options to this INSERT/UPDATE/DELETE object.
467
468 e.g.::
469
470 upd = table.update().dialect_options(mysql_limit=10)
471
472 .. versionadded: 1.4 - this method supersedes the dialect options
473 associated with the constructor.
474
475
476 """
477 self._validate_dialect_kwargs(opt)
478 return self
479
480 @_generative
481 def return_defaults(
482 self,
483 *cols: _DMLColumnArgument,
484 supplemental_cols: Optional[Iterable[_DMLColumnArgument]] = None,
485 sort_by_parameter_order: bool = False,
486 ) -> Self:
487 """Make use of a :term:`RETURNING` clause for the purpose
488 of fetching server-side expressions and defaults, for supporting
489 backends only.
490
491 .. deepalchemy::
492
493 The :meth:`.UpdateBase.return_defaults` method is used by the ORM
494 for its internal work in fetching newly generated primary key
495 and server default values, in particular to provide the underlying
496 implementation of the :paramref:`_orm.Mapper.eager_defaults`
497 ORM feature as well as to allow RETURNING support with bulk
498 ORM inserts. Its behavior is fairly idiosyncratic
499 and is not really intended for general use. End users should
500 stick with using :meth:`.UpdateBase.returning` in order to
501 add RETURNING clauses to their INSERT, UPDATE and DELETE
502 statements.
503
504 Normally, a single row INSERT statement will automatically populate the
505 :attr:`.CursorResult.inserted_primary_key` attribute when executed,
506 which stores the primary key of the row that was just inserted in the
507 form of a :class:`.Row` object with column names as named tuple keys
508 (and the :attr:`.Row._mapping` view fully populated as well). The
509 dialect in use chooses the strategy to use in order to populate this
510 data; if it was generated using server-side defaults and / or SQL
511 expressions, dialect-specific approaches such as ``cursor.lastrowid``
512 or ``RETURNING`` are typically used to acquire the new primary key
513 value.
514
515 However, when the statement is modified by calling
516 :meth:`.UpdateBase.return_defaults` before executing the statement,
517 additional behaviors take place **only** for backends that support
518 RETURNING and for :class:`.Table` objects that maintain the
519 :paramref:`.Table.implicit_returning` parameter at its default value of
520 ``True``. In these cases, when the :class:`.CursorResult` is returned
521 from the statement's execution, not only will
522 :attr:`.CursorResult.inserted_primary_key` be populated as always, the
523 :attr:`.CursorResult.returned_defaults` attribute will also be
524 populated with a :class:`.Row` named-tuple representing the full range
525 of server generated
526 values from that single row, including values for any columns that
527 specify :paramref:`_schema.Column.server_default` or which make use of
528 :paramref:`_schema.Column.default` using a SQL expression.
529
530 When invoking INSERT statements with multiple rows using
531 :ref:`insertmanyvalues <engine_insertmanyvalues>`, the
532 :meth:`.UpdateBase.return_defaults` modifier will have the effect of
533 the :attr:`_engine.CursorResult.inserted_primary_key_rows` and
534 :attr:`_engine.CursorResult.returned_defaults_rows` attributes being
535 fully populated with lists of :class:`.Row` objects representing newly
536 inserted primary key values as well as newly inserted server generated
537 values for each row inserted. The
538 :attr:`.CursorResult.inserted_primary_key` and
539 :attr:`.CursorResult.returned_defaults` attributes will also continue
540 to be populated with the first row of these two collections.
541
542 If the backend does not support RETURNING or the :class:`.Table` in use
543 has disabled :paramref:`.Table.implicit_returning`, then no RETURNING
544 clause is added and no additional data is fetched, however the
545 INSERT, UPDATE or DELETE statement proceeds normally.
546
547 E.g.::
548
549 stmt = table.insert().values(data="newdata").return_defaults()
550
551 result = connection.execute(stmt)
552
553 server_created_at = result.returned_defaults["created_at"]
554
555 When used against an UPDATE statement
556 :meth:`.UpdateBase.return_defaults` instead looks for columns that
557 include :paramref:`_schema.Column.onupdate` or
558 :paramref:`_schema.Column.server_onupdate` parameters assigned, when
559 constructing the columns that will be included in the RETURNING clause
560 by default if explicit columns were not specified. When used against a
561 DELETE statement, no columns are included in RETURNING by default, they
562 instead must be specified explicitly as there are no columns that
563 normally change values when a DELETE statement proceeds.
564
565 .. versionadded:: 2.0 :meth:`.UpdateBase.return_defaults` is supported
566 for DELETE statements also and has been moved from
567 :class:`.ValuesBase` to :class:`.UpdateBase`.
568
569 The :meth:`.UpdateBase.return_defaults` method is mutually exclusive
570 against the :meth:`.UpdateBase.returning` method and errors will be
571 raised during the SQL compilation process if both are used at the same
572 time on one statement. The RETURNING clause of the INSERT, UPDATE or
573 DELETE statement is therefore controlled by only one of these methods
574 at a time.
575
576 The :meth:`.UpdateBase.return_defaults` method differs from
577 :meth:`.UpdateBase.returning` in these ways:
578
579 1. :meth:`.UpdateBase.return_defaults` method causes the
580 :attr:`.CursorResult.returned_defaults` collection to be populated
581 with the first row from the RETURNING result. This attribute is not
582 populated when using :meth:`.UpdateBase.returning`.
583
584 2. :meth:`.UpdateBase.return_defaults` is compatible with existing
585 logic used to fetch auto-generated primary key values that are then
586 populated into the :attr:`.CursorResult.inserted_primary_key`
587 attribute. By contrast, using :meth:`.UpdateBase.returning` will
588 have the effect of the :attr:`.CursorResult.inserted_primary_key`
589 attribute being left unpopulated.
590
591 3. :meth:`.UpdateBase.return_defaults` can be called against any
592 backend. Backends that don't support RETURNING will skip the usage
593 of the feature, rather than raising an exception, *unless*
594 ``supplemental_cols`` is passed. The return value
595 of :attr:`_engine.CursorResult.returned_defaults` will be ``None``
596 for backends that don't support RETURNING or for which the target
597 :class:`.Table` sets :paramref:`.Table.implicit_returning` to
598 ``False``.
599
600 4. An INSERT statement invoked with executemany() is supported if the
601 backend database driver supports the
602 :ref:`insertmanyvalues <engine_insertmanyvalues>`
603 feature which is now supported by most SQLAlchemy-included backends.
604 When executemany is used, the
605 :attr:`_engine.CursorResult.returned_defaults_rows` and
606 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors
607 will return the inserted defaults and primary keys.
608
609 .. versionadded:: 1.4 Added
610 :attr:`_engine.CursorResult.returned_defaults_rows` and
611 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors.
612 In version 2.0, the underlying implementation which fetches and
613 populates the data for these attributes was generalized to be
614 supported by most backends, whereas in 1.4 they were only
615 supported by the ``psycopg2`` driver.
616
617
618 :param cols: optional list of column key names or
619 :class:`_schema.Column` that acts as a filter for those columns that
620 will be fetched.
621 :param supplemental_cols: optional list of RETURNING expressions,
622 in the same form as one would pass to the
623 :meth:`.UpdateBase.returning` method. When present, the additional
624 columns will be included in the RETURNING clause, and the
625 :class:`.CursorResult` object will be "rewound" when returned, so
626 that methods like :meth:`.CursorResult.all` will return new rows
627 mostly as though the statement used :meth:`.UpdateBase.returning`
628 directly. However, unlike when using :meth:`.UpdateBase.returning`
629 directly, the **order of the columns is undefined**, so can only be
630 targeted using names or :attr:`.Row._mapping` keys; they cannot
631 reliably be targeted positionally.
632
633 .. versionadded:: 2.0
634
635 :param sort_by_parameter_order: for a batch INSERT that is being
636 executed against multiple parameter sets, organize the results of
637 RETURNING so that the returned rows correspond to the order of
638 parameter sets passed in. This applies only to an :term:`executemany`
639 execution for supporting dialects and typically makes use of the
640 :term:`insertmanyvalues` feature.
641
642 .. versionadded:: 2.0.10
643
644 .. seealso::
645
646 :ref:`engine_insertmanyvalues_returning_order` - background on
647 sorting of RETURNING rows for bulk INSERT
648
649 .. seealso::
650
651 :meth:`.UpdateBase.returning`
652
653 :attr:`_engine.CursorResult.returned_defaults`
654
655 :attr:`_engine.CursorResult.returned_defaults_rows`
656
657 :attr:`_engine.CursorResult.inserted_primary_key`
658
659 :attr:`_engine.CursorResult.inserted_primary_key_rows`
660
661 """
662
663 if self._return_defaults:
664 # note _return_defaults_columns = () means return all columns,
665 # so if we have been here before, only update collection if there
666 # are columns in the collection
667 if self._return_defaults_columns and cols:
668 self._return_defaults_columns = tuple(
669 util.OrderedSet(self._return_defaults_columns).union(
670 coercions.expect(roles.ColumnsClauseRole, c)
671 for c in cols
672 )
673 )
674 else:
675 # set for all columns
676 self._return_defaults_columns = ()
677 else:
678 self._return_defaults_columns = tuple(
679 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
680 )
681 self._return_defaults = True
682 if sort_by_parameter_order:
683 if not self.is_insert:
684 raise exc.ArgumentError(
685 "The 'sort_by_parameter_order' argument to "
686 "return_defaults() only applies to INSERT statements"
687 )
688 self._sort_by_parameter_order = True
689 if supplemental_cols:
690 # uniquifying while also maintaining order (the maintain of order
691 # is for test suites but also for vertical splicing
692 supplemental_col_tup = (
693 coercions.expect(roles.ColumnsClauseRole, c)
694 for c in supplemental_cols
695 )
696
697 if self._supplemental_returning is None:
698 self._supplemental_returning = tuple(
699 util.unique_list(supplemental_col_tup)
700 )
701 else:
702 self._supplemental_returning = tuple(
703 util.unique_list(
704 self._supplemental_returning
705 + tuple(supplemental_col_tup)
706 )
707 )
708
709 return self
710
711 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
712 """Return ``True`` if this :class:`.ReturnsRows` is
713 'derived' from the given :class:`.FromClause`.
714
715 Since these are DMLs, we dont want such statements ever being adapted
716 so we return False for derives.
717
718 """
719 return False
720
721 @_generative
722 def returning(
723 self,
724 *cols: _ColumnsClauseArgument[Any],
725 sort_by_parameter_order: bool = False,
726 **__kw: Any,
727 ) -> UpdateBase:
728 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
729
730 e.g.:
731
732 .. sourcecode:: pycon+sql
733
734 >>> stmt = (
735 ... table.update()
736 ... .where(table.c.data == "value")
737 ... .values(status="X")
738 ... .returning(table.c.server_flag, table.c.updated_timestamp)
739 ... )
740 >>> print(stmt)
741 {printsql}UPDATE some_table SET status=:status
742 WHERE some_table.data = :data_1
743 RETURNING some_table.server_flag, some_table.updated_timestamp
744
745 The method may be invoked multiple times to add new entries to the
746 list of expressions to be returned.
747
748 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to
749 add new entries to the list of expressions to be returned.
750
751 The given collection of column expressions should be derived from the
752 table that is the target of the INSERT, UPDATE, or DELETE. While
753 :class:`_schema.Column` objects are typical, the elements can also be
754 expressions:
755
756 .. sourcecode:: pycon+sql
757
758 >>> stmt = table.insert().returning(
759 ... (table.c.first_name + " " + table.c.last_name).label("fullname")
760 ... )
761 >>> print(stmt)
762 {printsql}INSERT INTO some_table (first_name, last_name)
763 VALUES (:first_name, :last_name)
764 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname
765
766 Upon compilation, a RETURNING clause, or database equivalent,
767 will be rendered within the statement. For INSERT and UPDATE,
768 the values are the newly inserted/updated values. For DELETE,
769 the values are those of the rows which were deleted.
770
771 Upon execution, the values of the columns to be returned are made
772 available via the result set and can be iterated using
773 :meth:`_engine.CursorResult.fetchone` and similar.
774 For DBAPIs which do not
775 natively support returning values (i.e. cx_oracle), SQLAlchemy will
776 approximate this behavior at the result level so that a reasonable
777 amount of behavioral neutrality is provided.
778
779 Note that not all databases/DBAPIs
780 support RETURNING. For those backends with no support,
781 an exception is raised upon compilation and/or execution.
782 For those who do support it, the functionality across backends
783 varies greatly, including restrictions on executemany()
784 and other statements which return multiple rows. Please
785 read the documentation notes for the database in use in
786 order to determine the availability of RETURNING.
787
788 :param \*cols: series of columns, SQL expressions, or whole tables
789 entities to be returned.
790 :param sort_by_parameter_order: for a batch INSERT that is being
791 executed against multiple parameter sets, organize the results of
792 RETURNING so that the returned rows correspond to the order of
793 parameter sets passed in. This applies only to an :term:`executemany`
794 execution for supporting dialects and typically makes use of the
795 :term:`insertmanyvalues` feature.
796
797 .. versionadded:: 2.0.10
798
799 .. seealso::
800
801 :ref:`engine_insertmanyvalues_returning_order` - background on
802 sorting of RETURNING rows for bulk INSERT (Core level discussion)
803
804 :ref:`orm_queryguide_bulk_insert_returning_ordered` - example of
805 use with :ref:`orm_queryguide_bulk_insert` (ORM level discussion)
806
807 .. seealso::
808
809 :meth:`.UpdateBase.return_defaults` - an alternative method tailored
810 towards efficient fetching of server-side defaults and triggers
811 for single-row INSERTs or UPDATEs.
812
813 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`
814
815 """ # noqa: E501
816 if __kw:
817 raise _unexpected_kw("UpdateBase.returning()", __kw)
818 if self._return_defaults:
819 raise exc.InvalidRequestError(
820 "return_defaults() is already configured on this statement"
821 )
822 self._returning += tuple(
823 coercions.expect(roles.ColumnsClauseRole, c) for c in cols
824 )
825 if sort_by_parameter_order:
826 if not self.is_insert:
827 raise exc.ArgumentError(
828 "The 'sort_by_parameter_order' argument to returning() "
829 "only applies to INSERT statements"
830 )
831 self._sort_by_parameter_order = True
832 return self
833
834 def corresponding_column(
835 self, column: KeyedColumnElement[Any], require_embedded: bool = False
836 ) -> Optional[ColumnElement[Any]]:
837 return self.exported_columns.corresponding_column(
838 column, require_embedded=require_embedded
839 )
840
841 @util.ro_memoized_property
842 def _all_selected_columns(self) -> _SelectIterable:
843 return [c for c in _select_iterables(self._returning)]
844
845 @util.ro_memoized_property
846 def exported_columns(
847 self,
848 ) -> ReadOnlyColumnCollection[Optional[str], ColumnElement[Any]]:
849 """Return the RETURNING columns as a column collection for this
850 statement.
851
852 .. versionadded:: 1.4
853
854 """
855 return ColumnCollection(
856 (c.key, c)
857 for c in self._all_selected_columns
858 if is_column_element(c)
859 ).as_readonly()
860
861 @_generative
862 def with_hint(
863 self,
864 text: str,
865 selectable: Optional[_DMLTableArgument] = None,
866 dialect_name: str = "*",
867 ) -> Self:
868 """Add a table hint for a single table to this
869 INSERT/UPDATE/DELETE statement.
870
871 .. note::
872
873 :meth:`.UpdateBase.with_hint` currently applies only to
874 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
875 :meth:`.UpdateBase.prefix_with`.
876
877 The text of the hint is rendered in the appropriate
878 location for the database backend in use, relative
879 to the :class:`_schema.Table` that is the subject of this
880 statement, or optionally to that of the given
881 :class:`_schema.Table` passed as the ``selectable`` argument.
882
883 The ``dialect_name`` option will limit the rendering of a particular
884 hint to a particular backend. Such as, to add a hint
885 that only takes effect for SQL Server::
886
887 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
888
889 :param text: Text of the hint.
890 :param selectable: optional :class:`_schema.Table` that specifies
891 an element of the FROM clause within an UPDATE or DELETE
892 to be the subject of the hint - applies only to certain backends.
893 :param dialect_name: defaults to ``*``, if specified as the name
894 of a particular dialect, will apply these hints only when
895 that dialect is in use.
896 """
897 if selectable is None:
898 selectable = self.table
899 else:
900 selectable = coercions.expect(roles.DMLTableRole, selectable)
901 self._hints = self._hints.union({(selectable, dialect_name): text})
902 return self
903
904 @property
905 def entity_description(self) -> Dict[str, Any]:
906 """Return a :term:`plugin-enabled` description of the table and/or
907 entity which this DML construct is operating against.
908
909 This attribute is generally useful when using the ORM, as an
910 extended structure which includes information about mapped
911 entities is returned. The section :ref:`queryguide_inspection`
912 contains more background.
913
914 For a Core statement, the structure returned by this accessor
915 is derived from the :attr:`.UpdateBase.table` attribute, and
916 refers to the :class:`.Table` being inserted, updated, or deleted::
917
918 >>> stmt = insert(user_table)
919 >>> stmt.entity_description
920 {
921 "name": "user_table",
922 "table": Table("user_table", ...)
923 }
924
925 .. versionadded:: 1.4.33
926
927 .. seealso::
928
929 :attr:`.UpdateBase.returning_column_descriptions`
930
931 :attr:`.Select.column_descriptions` - entity information for
932 a :func:`.select` construct
933
934 :ref:`queryguide_inspection` - ORM background
935
936 """
937 meth = DMLState.get_plugin_class(self).get_entity_description
938 return meth(self)
939
940 @property
941 def returning_column_descriptions(self) -> List[Dict[str, Any]]:
942 """Return a :term:`plugin-enabled` description of the columns
943 which this DML construct is RETURNING against, in other words
944 the expressions established as part of :meth:`.UpdateBase.returning`.
945
946 This attribute is generally useful when using the ORM, as an
947 extended structure which includes information about mapped
948 entities is returned. The section :ref:`queryguide_inspection`
949 contains more background.
950
951 For a Core statement, the structure returned by this accessor is
952 derived from the same objects that are returned by the
953 :attr:`.UpdateBase.exported_columns` accessor::
954
955 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name)
956 >>> stmt.entity_description
957 [
958 {
959 "name": "id",
960 "type": Integer,
961 "expr": Column("id", Integer(), table=<user>, ...)
962 },
963 {
964 "name": "name",
965 "type": String(),
966 "expr": Column("name", String(), table=<user>, ...)
967 },
968 ]
969
970 .. versionadded:: 1.4.33
971
972 .. seealso::
973
974 :attr:`.UpdateBase.entity_description`
975
976 :attr:`.Select.column_descriptions` - entity information for
977 a :func:`.select` construct
978
979 :ref:`queryguide_inspection` - ORM background
980
981 """ # noqa: E501
982 meth = DMLState.get_plugin_class(
983 self
984 ).get_returning_column_descriptions
985 return meth(self)
986
987
988class ValuesBase(UpdateBase):
989 """Supplies support for :meth:`.ValuesBase.values` to
990 INSERT and UPDATE constructs."""
991
992 __visit_name__ = "values_base"
993
994 _supports_multi_parameters = False
995
996 select: Optional[Select[Any]] = None
997 """SELECT statement for INSERT .. FROM SELECT"""
998
999 _post_values_clause: Optional[ClauseElement] = None
1000 """used by extensions to Insert etc. to add additional syntacitcal
1001 constructs, e.g. ON CONFLICT etc."""
1002
1003 _values: Optional[util.immutabledict[_DMLColumnElement, Any]] = None
1004 _multi_values: Tuple[
1005 Union[
1006 Sequence[Dict[_DMLColumnElement, Any]],
1007 Sequence[Sequence[Any]],
1008 ],
1009 ...,
1010 ] = ()
1011
1012 _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None
1013
1014 _select_names: Optional[List[str]] = None
1015 _inline: bool = False
1016
1017 def __init__(self, table: _DMLTableArgument):
1018 self.table = coercions.expect(
1019 roles.DMLTableRole, table, apply_propagate_attrs=self
1020 )
1021
1022 @_generative
1023 @_exclusive_against(
1024 "_select_names",
1025 "_ordered_values",
1026 msgs={
1027 "_select_names": "This construct already inserts from a SELECT",
1028 "_ordered_values": "This statement already has ordered "
1029 "values present",
1030 },
1031 )
1032 def values(
1033 self,
1034 *args: Union[
1035 _DMLColumnKeyMapping[Any],
1036 Sequence[Any],
1037 ],
1038 **kwargs: Any,
1039 ) -> Self:
1040 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
1041 clause for an UPDATE.
1042
1043 Note that the :class:`_expression.Insert` and
1044 :class:`_expression.Update`
1045 constructs support
1046 per-execution time formatting of the VALUES and/or SET clauses,
1047 based on the arguments passed to :meth:`_engine.Connection.execute`.
1048 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
1049 particular set of parameters into the statement.
1050
1051 Multiple calls to :meth:`.ValuesBase.values` will produce a new
1052 construct, each one with the parameter list modified to include
1053 the new parameters sent. In the typical case of a single
1054 dictionary of parameters, the newly passed keys will replace
1055 the same keys in the previous construct. In the case of a list-based
1056 "multiple values" construct, each new list of values is extended
1057 onto the existing list of values.
1058
1059 :param \**kwargs: key value pairs representing the string key
1060 of a :class:`_schema.Column`
1061 mapped to the value to be rendered into the
1062 VALUES or SET clause::
1063
1064 users.insert().values(name="some name")
1065
1066 users.update().where(users.c.id == 5).values(name="some name")
1067
1068 :param \*args: As an alternative to passing key/value parameters,
1069 a dictionary, tuple, or list of dictionaries or tuples can be passed
1070 as a single positional argument in order to form the VALUES or
1071 SET clause of the statement. The forms that are accepted vary
1072 based on whether this is an :class:`_expression.Insert` or an
1073 :class:`_expression.Update` construct.
1074
1075 For either an :class:`_expression.Insert` or
1076 :class:`_expression.Update`
1077 construct, a single dictionary can be passed, which works the same as
1078 that of the kwargs form::
1079
1080 users.insert().values({"name": "some name"})
1081
1082 users.update().values({"name": "some new name"})
1083
1084 Also for either form but more typically for the
1085 :class:`_expression.Insert` construct, a tuple that contains an
1086 entry for every column in the table is also accepted::
1087
1088 users.insert().values((5, "some name"))
1089
1090 The :class:`_expression.Insert` construct also supports being
1091 passed a list of dictionaries or full-table-tuples, which on the
1092 server will render the less common SQL syntax of "multiple values" -
1093 this syntax is supported on backends such as SQLite, PostgreSQL,
1094 MySQL, but not necessarily others::
1095
1096 users.insert().values(
1097 [
1098 {"name": "some name"},
1099 {"name": "some other name"},
1100 {"name": "yet another name"},
1101 ]
1102 )
1103
1104 The above form would render a multiple VALUES statement similar to:
1105
1106 .. sourcecode:: sql
1107
1108 INSERT INTO users (name) VALUES
1109 (:name_1),
1110 (:name_2),
1111 (:name_3)
1112
1113 It is essential to note that **passing multiple values is
1114 NOT the same as using traditional executemany() form**. The above
1115 syntax is a **special** syntax not typically used. To emit an
1116 INSERT statement against multiple rows, the normal method is
1117 to pass a multiple values list to the
1118 :meth:`_engine.Connection.execute`
1119 method, which is supported by all database backends and is generally
1120 more efficient for a very large number of parameters.
1121
1122 .. seealso::
1123
1124 :ref:`tutorial_multiple_parameters` - an introduction to
1125 the traditional Core method of multiple parameter set
1126 invocation for INSERTs and other statements.
1127
1128 :ref:`tutorial_core_insert_values_clause` - Insert tutorial
1129 detailing alternatives to the multiple values syntax.
1130
1131 The UPDATE construct also supports rendering the SET parameters
1132 in a specific order. For this feature refer to the
1133 :meth:`_expression.Update.ordered_values` method.
1134
1135 .. seealso::
1136
1137 :meth:`_expression.Update.ordered_values`
1138
1139
1140 """
1141 if args:
1142 # positional case. this is currently expensive. we don't
1143 # yet have positional-only args so we have to check the length.
1144 # then we need to check multiparams vs. single dictionary.
1145 # since the parameter format is needed in order to determine
1146 # a cache key, we need to determine this up front.
1147 arg = args[0]
1148
1149 if kwargs:
1150 raise exc.ArgumentError(
1151 "Can't pass positional and kwargs to values() "
1152 "simultaneously"
1153 )
1154 elif len(args) > 1:
1155 raise exc.ArgumentError(
1156 "Only a single dictionary/tuple or list of "
1157 "dictionaries/tuples is accepted positionally."
1158 )
1159
1160 elif isinstance(arg, collections_abc.Sequence):
1161 if arg and isinstance(arg[0], dict):
1162 multi_kv_generator = DMLState.get_plugin_class(
1163 self
1164 )._get_multi_crud_kv_pairs
1165 self._multi_values += (multi_kv_generator(self, arg),)
1166 return self
1167
1168 if arg and isinstance(arg[0], (list, tuple)):
1169 self._multi_values += (arg,)
1170 return self
1171
1172 if TYPE_CHECKING:
1173 # crud.py raises during compilation if this is not the
1174 # case
1175 assert isinstance(self, Insert)
1176
1177 # tuple values
1178 arg = {c.key: value for c, value in zip(self.table.c, arg)}
1179
1180 else:
1181 # kwarg path. this is the most common path for non-multi-params
1182 # so this is fairly quick.
1183 arg = cast("Dict[_DMLColumnArgument, Any]", kwargs)
1184 if args:
1185 raise exc.ArgumentError(
1186 "Only a single dictionary/tuple or list of "
1187 "dictionaries/tuples is accepted positionally."
1188 )
1189
1190 # for top level values(), convert literals to anonymous bound
1191 # parameters at statement construction time, so that these values can
1192 # participate in the cache key process like any other ClauseElement.
1193 # crud.py now intercepts bound parameters with unique=True from here
1194 # and ensures they get the "crud"-style name when rendered.
1195
1196 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs
1197 coerced_arg = dict(kv_generator(self, arg.items(), True))
1198 if self._values:
1199 self._values = self._values.union(coerced_arg)
1200 else: