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