codekingpro/portable-devtools
115k
1# sql/crud.py
2# Copyright (C) 2005-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# mypy: allow-untyped-defs, allow-untyped-calls
8
9"""Functions used by compiler.py to determine the parameters rendered
10within INSERT and UPDATE statements.
11
12"""
13from __future__ import annotations
14
15import functools
16import operator
17from typing import Any
18from typing import Callable
19from typing import cast
20from typing import Dict
21from typing import Iterable
22from typing import List
23from typing import MutableMapping
24from typing import NamedTuple
25from typing import Optional
26from typing import overload
27from typing import Sequence
28from typing import Set
29from typing import Tuple
30from typing import TYPE_CHECKING
31from typing import Union
32
33from . import coercions
34from . import dml
35from . import elements
36from . import roles
37from .base import _DefaultDescriptionTuple
38from .dml import isinsert as _compile_state_isinsert
39from .elements import ColumnClause
40from .schema import default_is_clause_element
41from .schema import default_is_sequence
42from .selectable import Select
43from .selectable import TableClause
44from .. import exc
45from .. import util
46from ..util.typing import Literal
47
48if TYPE_CHECKING:
49 from .compiler import _BindNameForColProtocol
50 from .compiler import SQLCompiler
51 from .dml import _DMLColumnElement
52 from .dml import DMLState
53 from .dml import ValuesBase
54 from .elements import ColumnElement
55 from .elements import KeyedColumnElement
56 from .schema import _SQLExprDefault
57 from .schema import Column
58
59REQUIRED = util.symbol(
60 "REQUIRED",
61 """
62Placeholder for the value within a :class:`.BindParameter`
63which is required to be present when the statement is passed
64to :meth:`_engine.Connection.execute`.
65
66This symbol is typically used when a :func:`_expression.insert`
67or :func:`_expression.update` statement is compiled without parameter
68values present.
69
70""",
71)
72
73
74def _as_dml_column(c: ColumnElement[Any]) -> ColumnClause[Any]:
75 if not isinstance(c, ColumnClause):
76 raise exc.CompileError(
77 f"Can't create DML statement against column expression {c!r}"
78 )
79 return c
80
81
82_CrudParamElement = Tuple[
83 "ColumnElement[Any]",
84 str, # column name
85 Optional[
86 Union[str, "_SQLExprDefault"]
87 ], # bound parameter string or SQL expression to apply
88 Iterable[str],
89]
90_CrudParamElementStr = Tuple[
91 "KeyedColumnElement[Any]",
92 str, # column name
93 str, # bound parameter string
94 Iterable[str],
95]
96_CrudParamElementSQLExpr = Tuple[
97 "ColumnClause[Any]",
98 str,
99 "_SQLExprDefault", # SQL expression to apply
100 Iterable[str],
101]
102
103_CrudParamSequence = List[_CrudParamElement]
104
105
106class _CrudParams(NamedTuple):
107 single_params: List[_CrudParamElementStr]
108 all_multi_params: List[Sequence[_CrudParamElementStr]]
109 is_default_metavalue_only: bool = False
110 use_insertmanyvalues: bool = False
111 use_sentinel_columns: Optional[Sequence[Column[Any]]] = None
112
113
114def _get_crud_params(
115 compiler: SQLCompiler,
116 stmt: ValuesBase,
117 compile_state: DMLState,
118 toplevel: bool,
119 **kw: Any,
120) -> _CrudParams:
121 """create a set of tuples representing column/string pairs for use
122 in an INSERT or UPDATE statement.
123
124 Also generates the Compiled object's postfetch, prefetch, and
125 returning column collections, used for default handling and ultimately
126 populating the CursorResult's prefetch_cols() and postfetch_cols()
127 collections.
128
129 """
130
131 # note: the _get_crud_params() system was written with the notion in mind
132 # that INSERT, UPDATE, DELETE are always the top level statement and
133 # that there is only one of them. With the addition of CTEs that can
134 # make use of DML, this assumption is no longer accurate; the DML
135 # statement is not necessarily the top-level "row returning" thing
136 # and it is also theoretically possible (fortunately nobody has asked yet)
137 # to have a single statement with multiple DMLs inside of it via CTEs.
138
139 # the current _get_crud_params() design doesn't accommodate these cases
140 # right now. It "just works" for a CTE that has a single DML inside of
141 # it, and for a CTE with multiple DML, it's not clear what would happen.
142
143 # overall, the "compiler.XYZ" collections here would need to be in a
144 # per-DML structure of some kind, and DefaultDialect would need to
145 # navigate these collections on a per-statement basis, with additional
146 # emphasis on the "toplevel returning data" statement. However we
147 # still need to run through _get_crud_params() for all DML as we have
148 # Python / SQL generated column defaults that need to be rendered.
149
150 # if there is user need for this kind of thing, it's likely a post 2.0
151 # kind of change as it would require deep changes to DefaultDialect
152 # as well as here.
153
154 compiler.postfetch = []
155 compiler.insert_prefetch = []
156 compiler.update_prefetch = []
157 compiler.implicit_returning = []
158
159 visiting_cte = kw.get("visiting_cte", None)
160 if visiting_cte is not None:
161 # for insert -> CTE -> insert, don't populate an incoming
162 # _crud_accumulate_bind_names collection; the INSERT we process here
163 # will not be inline within the VALUES of the enclosing INSERT as the
164 # CTE is placed on the outside. See issue #9173
165 kw.pop("accumulate_bind_names", None)
166 assert (
167 "accumulate_bind_names" not in kw
168 ), "Don't know how to handle insert within insert without a CTE"
169
170 # getters - these are normally just column.key,
171 # but in the case of mysql multi-table update, the rules for
172 # .key must conditionally take tablename into account
173 (
174 _column_as_key,
175 _getattr_col_key,
176 _col_bind_name,
177 ) = _key_getters_for_crud_column(compiler, stmt, compile_state)
178
179 compiler._get_bind_name_for_col = _col_bind_name
180
181 if stmt._returning and stmt._return_defaults:
182 raise exc.CompileError(
183 "Can't compile statement that includes returning() and "
184 "return_defaults() simultaneously"
185 )
186
187 if compile_state.isdelete:
188 _setup_delete_return_defaults(
189 compiler,
190 stmt,
191 compile_state,
192 (),
193 _getattr_col_key,
194 _column_as_key,
195 _col_bind_name,
196 (),
197 (),
198 toplevel,
199 kw,
200 )
201 return _CrudParams([], [])
202
203 # no parameters in the statement, no parameters in the
204 # compiled params - return binds for all columns
205 if compiler.column_keys is None and compile_state._no_parameters:
206 return _CrudParams(
207 [
208 (
209 c,
210 compiler.preparer.format_column(c),
211 _create_bind_param(compiler, c, None, required=True),
212 (c.key,),
213 )
214 for c in stmt.table.columns
215 if not c._omit_from_statements
216 ],
217 [],
218 )
219
220 stmt_parameter_tuples: Optional[
221 List[Tuple[Union[str, ColumnClause[Any]], Any]]
222 ]
223 spd: Optional[MutableMapping[_DMLColumnElement, Any]]
224
225 if (
226 _compile_state_isinsert(compile_state)
227 and compile_state._has_multi_parameters
228 ):
229 mp = compile_state._multi_parameters
230 assert mp is not None
231 spd = mp[0]
232 stmt_parameter_tuples = list(spd.items())
233 spd_str_key = {_column_as_key(key) for key in spd}
234 elif compile_state._ordered_values:
235 spd = compile_state._dict_parameters
236 stmt_parameter_tuples = compile_state._ordered_values
237 assert spd is not None
238 spd_str_key = {_column_as_key(key) for key in spd}
239 elif compile_state._dict_parameters:
240 spd = compile_state._dict_parameters
241 stmt_parameter_tuples = list(spd.items())
242 spd_str_key = {_column_as_key(key) for key in spd}
243 else:
244 stmt_parameter_tuples = spd_str_key = None
245
246 # if we have statement parameters - set defaults in the
247 # compiled params
248 if compiler.column_keys is None:
249 parameters = {}
250 elif stmt_parameter_tuples:
251 assert spd_str_key is not None
252 parameters = {
253 _column_as_key(key): REQUIRED
254 for key in compiler.column_keys
255 if key not in spd_str_key
256 }
257 else:
258 parameters = {
259 _column_as_key(key): REQUIRED for key in compiler.column_keys
260 }
261
262 # create a list of column assignment clauses as tuples
263 values: List[_CrudParamElementStr] = []
264
265 if stmt_parameter_tuples is not None:
266 _get_stmt_parameter_tuples_params(
267 compiler,
268 compile_state,
269 parameters,
270 stmt_parameter_tuples,
271 _column_as_key,
272 values,
273 kw,
274 )
275
276 check_columns: Dict[str, ColumnClause[Any]] = {}
277
278 # special logic that only occurs for multi-table UPDATE
279 # statements
280 if dml.isupdate(compile_state) and compile_state.is_multitable:
281 _get_update_multitable_params(
282 compiler,
283 stmt,
284 compile_state,
285 stmt_parameter_tuples,
286 check_columns,
287 _col_bind_name,
288 _getattr_col_key,
289 values,
290 kw,
291 )
292
293 if _compile_state_isinsert(compile_state) and stmt._select_names:
294 # is an insert from select, is not a multiparams
295
296 assert not compile_state._has_multi_parameters
297
298 _scan_insert_from_select_cols(
299 compiler,
300 stmt,
301 compile_state,
302 parameters,
303 _getattr_col_key,
304 _column_as_key,
305 _col_bind_name,
306 check_columns,
307 values,
308 toplevel,
309 kw,
310 )
311 use_insertmanyvalues = False
312 use_sentinel_columns = None
313 else:
314 use_insertmanyvalues, use_sentinel_columns = _scan_cols(
315 compiler,
316 stmt,
317 compile_state,
318 parameters,
319 _getattr_col_key,
320 _column_as_key,
321 _col_bind_name,
322 check_columns,
323 values,
324 toplevel,
325 kw,
326 )
327
328 if parameters and stmt_parameter_tuples:
329 check = (
330 set(parameters)
331 .intersection(_column_as_key(k) for k, v in stmt_parameter_tuples)
332 .difference(check_columns)
333 )
334 if check:
335
336 if dml.isupdate(compile_state):
337 tables_mentioned = set(
338 c.table
339 for c, v in stmt_parameter_tuples
340 if isinstance(c, ColumnClause) and c.table is not None
341 ).difference([compile_state.dml_table])
342
343 multi_not_in_from = tables_mentioned.difference(
344 compile_state._extra_froms
345 )
346
347 if tables_mentioned and (
348 not compile_state.is_multitable
349 or not compiler.render_table_with_column_in_update_from
350 ):
351 if not compiler.render_table_with_column_in_update_from:
352 preamble = (
353 "Backend does not support additional "
354 "tables in the SET clause"
355 )
356 else:
357 preamble = (
358 "Statement is not a multi-table UPDATE statement"
359 )
360
361 raise exc.CompileError(
362 f"{preamble}; cannot "
363 f"""include columns from table(s) {
364 ", ".join(f"'{t.description}'"
365 for t in tables_mentioned)
366 } in SET clause"""
367 )
368
369 elif multi_not_in_from:
370 assert compiler.render_table_with_column_in_update_from
371 raise exc.CompileError(
372 f"Multi-table UPDATE statement does not include "
373 "table(s) "
374 f"""{
375 ", ".join(
376 f"'{t.description}'" for
377 t in multi_not_in_from)
378 }"""
379 )
380
381 raise exc.CompileError(
382 "Unconsumed column names: %s"
383 % (", ".join("%s" % (c,) for c in check))
384 )
385
386 is_default_metavalue_only = False
387
388 if (
389 _compile_state_isinsert(compile_state)
390 and compile_state._has_multi_parameters
391 ):
392 # is a multiparams, is not an insert from a select
393 assert not stmt._select_names
394 multi_extended_values = _extend_values_for_multiparams(
395 compiler,
396 stmt,
397 compile_state,
398 cast(
399 "Sequence[_CrudParamElementStr]",
400 values,
401 ),
402 cast("Callable[..., str]", _column_as_key),
403 kw,
404 )
405 return _CrudParams(values, multi_extended_values)
406 elif (
407 not values
408 and compiler.for_executemany
409 and compiler.dialect.supports_default_metavalue
410 ):
411 # convert an "INSERT DEFAULT VALUES"
412 # into INSERT (firstcol) VALUES (DEFAULT) which can be turned
413 # into an in-place multi values. This supports
414 # insert_executemany_returning mode :)
415 values = [
416 (
417 _as_dml_column(stmt.table.columns[0]),
418 compiler.preparer.format_column(stmt.table.columns[0]),
419 compiler.dialect.default_metavalue_token,
420 (),
421 )
422 ]
423 is_default_metavalue_only = True
424
425 return _CrudParams(
426 values,
427 [],
428 is_default_metavalue_only=is_default_metavalue_only,
429 use_insertmanyvalues=use_insertmanyvalues,
430 use_sentinel_columns=use_sentinel_columns,
431 )
432
433
434@overload
435def _create_bind_param(
436 compiler: SQLCompiler,
437 col: ColumnElement[Any],
438 value: Any,
439 process: Literal[True] = ...,
440 required: bool = False,
441 name: Optional[str] = None,
442 force_anonymous: bool = False,
443 **kw: Any,
444) -> str: ...
445
446
447@overload
448def _create_bind_param(
449 compiler: SQLCompiler,
450 col: ColumnElement[Any],
451 value: Any,
452 **kw: Any,
453) -> str: ...
454
455
456def _create_bind_param(
457 compiler: SQLCompiler,
458 col: ColumnElement[Any],
459 value: Any,
460 process: bool = True,
461 required: bool = False,
462 name: Optional[str] = None,
463 force_anonymous: bool = False,
464 **kw: Any,
465) -> Union[str, elements.BindParameter[Any]]:
466 if force_anonymous:
467 name = None
468 elif name is None:
469 name = col.key
470
471 bindparam = elements.BindParameter(
472 name, value, type_=col.type, required=required
473 )
474 bindparam._is_crud = True
475 if process:
476 return bindparam._compiler_dispatch(compiler, **kw)
477 else:
478 return bindparam
479
480
481def _handle_values_anonymous_param(compiler, col, value, name, **kw):
482 # the insert() and update() constructs as of 1.4 will now produce anonymous
483 # bindparam() objects in the values() collections up front when given plain
484 # literal values. This is so that cache key behaviors, which need to
485 # produce bound parameters in deterministic order without invoking any
486 # compilation here, can be applied to these constructs when they include
487 # values() (but not yet multi-values, which are not included in caching
488 # right now).
489 #
490 # in order to produce the desired "crud" style name for these parameters,
491 # which will also be targetable in engine/default.py through the usual
492 # conventions, apply our desired name to these unique parameters by
493 # populating the compiler truncated names cache with the desired name,
494 # rather than having
495 # compiler.visit_bindparam()->compiler._truncated_identifier make up a
496 # name. Saves on call counts also.
497
498 # for INSERT/UPDATE that's a CTE, we don't need names to match to
499 # external parameters and these would also conflict in the case where
500 # multiple insert/update are combined together using CTEs
501 is_cte = "visiting_cte" in kw
502
503 if (
504 not is_cte
505 and value.unique
506 and isinstance(value.key, elements._truncated_label)
507 ):
508 compiler.truncated_names[("bindparam", value.key)] = name
509
510 if value.type._isnull:
511 # either unique parameter, or other bound parameters that were
512 # passed in directly
513 # set type to that of the column unconditionally
514 value = value._with_binary_element_type(col.type)
515
516 return value._compiler_dispatch(compiler, **kw)
517
518
519def _key_getters_for_crud_column(
520 compiler: SQLCompiler, stmt: ValuesBase, compile_state: DMLState
521) -> Tuple[
522 Callable[[Union[str, ColumnClause[Any]]], Union[str, Tuple[str, str]]],
523 Callable[[ColumnClause[Any]], Union[str, Tuple[str, str]]],
524 _BindNameForColProtocol,
525]:
526 if dml.isupdate(compile_state) and compile_state._extra_froms:
527 # when extra tables are present, refer to the columns
528 # in those extra tables as table-qualified, including in
529 # dictionaries and when rendering bind param names.
530 # the "main" table of the statement remains unqualified,
531 # allowing the most compatibility with a non-multi-table
532 # statement.
533 _et = set(compile_state._extra_froms)
534
535 c_key_role = functools.partial(
536 coercions.expect_as_key, roles.DMLColumnRole
537 )
538
539 def _column_as_key(
540 key: Union[ColumnClause[Any], str],
541 ) -> Union[str, Tuple[str, str]]:
542 str_key = c_key_role(key)
543 if hasattr(key, "table") and key.table in _et:
544 return (key.table.name, str_key) # type: ignore
545 else:
546 return str_key
547
548 def _getattr_col_key(
549 col: ColumnClause[Any],
550 ) -> Union[str, Tuple[str, str]]:
551 if col.table in _et:
552 return (col.table.name, col.key) # type: ignore
553 else:
554 return col.key
555
556 def _col_bind_name(col: ColumnClause[Any]) -> str:
557 if col.table in _et:
558 if TYPE_CHECKING:
559 assert isinstance(col.table, TableClause)
560 return "%s_%s" % (col.table.name, col.key)
561 else:
562 return col.key
563
564 else:
565 _column_as_key = functools.partial(
566 coercions.expect_as_key, roles.DMLColumnRole
567 )
568 _getattr_col_key = _col_bind_name = operator.attrgetter("key") # type: ignore # noqa: E501
569
570 return _column_as_key, _getattr_col_key, _col_bind_name
571
572
573def _scan_insert_from_select_cols(
574 compiler,
575 stmt,
576 compile_state,
577 parameters,
578 _getattr_col_key,
579 _column_as_key,
580 _col_bind_name,
581 check_columns,
582 values,
583 toplevel,
584 kw,
585):
586 cols = [stmt.table.c[_column_as_key(name)] for name in stmt._select_names]
587
588 assert compiler.stack[-1]["selectable"] is stmt
589
590 compiler.stack[-1]["insert_from_select"] = stmt.select
591
592 add_select_cols: List[_CrudParamElementSQLExpr] = []
593 if stmt.include_insert_from_select_defaults:
594 col_set = set(cols)
595 for col in stmt.table.columns:
596 # omit columns that were not in the SELECT statement.
597 # this will omit columns marked as omit_from_statements naturally,
598 # as long as that col was not explicit in the SELECT.
599 # if an omit_from_statements col has a "default" on it, then
600 # we need to include it, as these defaults should still fire off.
601 # but, if it has that default and it's the "sentinel" default,
602 # we don't do sentinel default operations for insert_from_select
603 # here so we again omit it.
604 if (
605 col not in col_set
606 and col.default
607 and not col.default.is_sentinel
608 ):
609 cols.append(col)
610
611 for c in cols:
612 col_key = _getattr_col_key(c)
613 if col_key in parameters and col_key not in check_columns:
614 parameters.pop(col_key)
615 values.append((c, compiler.preparer.format_column(c), None, ()))
616 else:
617 _append_param_insert_select_hasdefault(
618 compiler, stmt, c, add_select_cols, kw
619 )
620
621 if add_select_cols:
622 values.extend(add_select_cols)
623 ins_from_select = compiler.stack[-1]["insert_from_select"]
624 if not isinstance(ins_from_select, Select):
625 raise exc.CompileError(
626 f"Can't extend statement for INSERT..FROM SELECT to include "
627 f"additional default-holding column(s) "
628 f"""{
629 ', '.join(repr(key) for _, key, _, _ in add_select_cols)
630 }. Convert the selectable to a subquery() first, or pass """
631 "include_defaults=False to Insert.from_select() to skip these "
632 "columns."
633 )
634 ins_from_select = ins_from_select._generate()
635 # copy raw_columns
636 ins_from_select._raw_columns = list(ins_from_select._raw_columns) + [
637 expr for _, _, expr, _ in add_select_cols
638 ]
639 compiler.stack[-1]["insert_from_select"] = ins_from_select
640
641
642def _scan_cols(
643 compiler,
644 stmt,
645 compile_state,
646 parameters,
647 _getattr_col_key,
648 _column_as_key,
649 _col_bind_name,
650 check_columns,
651 values,
652 toplevel,
653 kw,
654):
655 (
656 need_pks,
657 implicit_returning,
658 implicit_return_defaults,
659 postfetch_lastrowid,
660 use_insertmanyvalues,
661 use_sentinel_columns,
662 ) = _get_returning_modifiers(compiler, stmt, compile_state, toplevel)
663
664 assert compile_state.isupdate or compile_state.isinsert
665
666 if compile_state._parameter_ordering:
667 parameter_ordering = [
668 _column_as_key(key) for key in compile_state._parameter_ordering
669 ]
670 ordered_keys = set(parameter_ordering)
671 cols = [
672 stmt.table.c[key]
673 for key in parameter_ordering
674 if isinstance(key, str) and key in stmt.table.c
675 ] + [c for c in stmt.table.c if c.key not in ordered_keys]
676
677 else:
678 cols = stmt.table.columns
679
680 isinsert = _compile_state_isinsert(compile_state)
681 if isinsert and not compile_state._has_multi_parameters:
682 # new rules for #7998. fetch lastrowid or implicit returning
683 # for autoincrement column even if parameter is NULL, for DBs that
684 # override NULL param for primary key (sqlite, mysql/mariadb)
685 autoincrement_col = stmt.table._autoincrement_column
686 insert_null_pk_still_autoincrements = (
687 compiler.dialect.insert_null_pk_still_autoincrements
688 )
689 else:
690 autoincrement_col = insert_null_pk_still_autoincrements = None
691
692 if stmt._supplemental_returning:
693 supplemental_returning = set(stmt._supplemental_returning)
694 else:
695 supplemental_returning = set()
696
697 compiler_implicit_returning = compiler.implicit_returning
698
699 # TODO - see TODO(return_defaults_columns) below
700 # cols_in_params = set()
701
702 for c in cols:
703 # scan through every column in the target table
704
705 col_key = _getattr_col_key(c)
706
707 if col_key in parameters and col_key not in check_columns:
708 # parameter is present for the column. use that.
709
710 _append_param_parameter(
711 compiler,
712 stmt,
713 compile_state,
714 c,
715 col_key,
716 parameters,
717 _col_bind_name,
718 implicit_returning,
719 implicit_return_defaults,
720 postfetch_lastrowid,
721 values,
722 autoincrement_col,
723 insert_null_pk_still_autoincrements,
724 kw,
725 )
726
727 # TODO - see TODO(return_defaults_columns) below
728 # cols_in_params.add(c)
729
730 elif isinsert:
731 # no parameter is present and it's an insert.
732
733 if c.primary_key and need_pks:
734 # it's a primary key column, it will need to be generated by a
735 # default generator of some kind, and the statement expects
736 # inserted_primary_key to be available.
737
738 if implicit_returning:
739 # we can use RETURNING, find out how to invoke this
740 # column and get the value where RETURNING is an option.
741 # we can inline server-side functions in this case.
742
743 _append_param_insert_pk_returning(
744 compiler, stmt, c, values, kw
745 )
746 else:
747 # otherwise, find out how to invoke this column
748 # and get its value where RETURNING is not an option.
749 # if we have to invoke a server-side function, we need
750 # to pre-execute it. or if this is a straight
751 # autoincrement column and the dialect supports it
752 # we can use cursor.lastrowid.
753
754 _append_param_insert_pk_no_returning(
755 compiler, stmt, c, values, kw
756 )
757
758 elif c.default is not None:
759 # column has a default, but it's not a pk column, or it is but
760 # we don't need to get the pk back.
761 if not c.default.is_sentinel or (
762 use_sentinel_columns is not None
763 ):
764 _append_param_insert_hasdefault(
765 compiler, stmt, c, implicit_return_defaults, values, kw
766 )
767
768 elif c.server_default is not None:
769 # column has a DDL-level default, and is either not a pk
770 # column or we don't need the pk.
771 if implicit_return_defaults and c in implicit_return_defaults:
772 compiler_implicit_returning.append(c)
773 elif not c.primary_key:
774 compiler.postfetch.append(c)
775
776 elif implicit_return_defaults and c in implicit_return_defaults:
777 compiler_implicit_returning.append(c)
778
779 elif (
780 c.primary_key
781 and c is not stmt.table._autoincrement_column
782 and not c.nullable
783 ):
784 _warn_pk_with_no_anticipated_value(c)
785
786 elif compile_state.isupdate:
787 # no parameter is present and it's an insert.
788
789 _append_param_update(
790 compiler,
791 compile_state,
792 stmt,
793 c,
794 implicit_return_defaults,
795 values,
796 kw,
797 )
798
799 # adding supplemental cols to implicit_returning in table
800 # order so that order is maintained between multiple INSERT
801 # statements which may have different parameters included, but all
802 # have the same RETURNING clause
803 if (
804 c in supplemental_returning
805 and c not in compiler_implicit_returning
806 ):
807 compiler_implicit_returning.append(c)
808
809 if supplemental_returning:
810 # we should have gotten every col into implicit_returning,
811 # however supplemental returning can also have SQL functions etc.
812 # in it
813 remaining_supplemental = supplemental_returning.difference(
814 compiler_implicit_returning
815 )
816 compiler_implicit_returning.extend(
817 c
818 for c in stmt._supplemental_returning
819 if c in remaining_supplemental
820 )
821
822 # TODO(return_defaults_columns): there can still be more columns in
823 # _return_defaults_columns in the case that they are from something like an
824 # aliased of the table. we can add them here, however this breaks other ORM
825 # things. so this is for another day. see
826 # test/orm/dml/test_update_delete_where.py -> test_update_from_alias
827
828 # if stmt._return_defaults_columns:
829 # compiler_implicit_returning.extend(
830 # set(stmt._return_defaults_columns)
831 # .difference(compiler_implicit_returning)
832 # .difference(cols_in_params)
833 # )
834
835 return (use_insertmanyvalues, use_sentinel_columns)
836
837
838def _setup_delete_return_defaults(
839 compiler,
840 stmt,
841 compile_state,
842 parameters,
843 _getattr_col_key,
844 _column_as_key,
845 _col_bind_name,
846 check_columns,
847 values,
848 toplevel,
849 kw,
850):
851 (_, _, implicit_return_defaults, *_) = _get_returning_modifiers(
852 compiler, stmt, compile_state, toplevel
853 )
854
855 if not implicit_return_defaults:
856 return
857
858 if stmt._return_defaults_columns:
859 compiler.implicit_returning.extend(implicit_return_defaults)
860
861 if stmt._supplemental_returning:
862 ir_set = set(compiler.implicit_returning)
863 compiler.implicit_returning.extend(
864 c for c in stmt._supplemental_returning if c not in ir_set
865 )
866
867
868def _append_param_parameter(
869 compiler,
870 stmt,
871 compile_state,
872 c,
873 col_key,
874 parameters,
875 _col_bind_name,
876 implicit_returning,
877 implicit_return_defaults,
878 postfetch_lastrowid,
879 values,
880 autoincrement_col,
881 insert_null_pk_still_autoincrements,
882 kw,
883):
884 value = parameters.pop(col_key)
885
886 has_visiting_cte = kw.get("visiting_cte") is not None
887 col_value = compiler.preparer.format_column(
888 c, use_table=compile_state.include_table_with_column_exprs
889 )
890
891 accumulated_bind_names: Set[str] = set()
892
893 if coercions._is_literal(value):
894 if (
895 insert_null_pk_still_autoincrements
896 and c.primary_key
897 and c is autoincrement_col
898 ):
899 # support use case for #7998, fetch autoincrement cols
900 # even if value was given.
901
902 if postfetch_lastrowid:
903 compiler.postfetch_lastrowid = True
904 elif implicit_returning:
905 compiler.implicit_returning.append(c)
906
907 value = _create_bind_param(
908 compiler,
909 c,
910 value,
911 required=value is REQUIRED,
912 name=(
913 _col_bind_name(c)
914 if not _compile_state_isinsert(compile_state)
915 or not compile_state._has_multi_parameters
916 else "%s_m0" % _col_bind_name(c)
917 ),
918 accumulate_bind_names=accumulated_bind_names,
919 force_anonymous=has_visiting_cte,
920 **kw,
921 )
922 elif value._is_bind_parameter:
923 if (
924 insert_null_pk_still_autoincrements
925 and value.value is None
926 and c.primary_key
927 and c is autoincrement_col
928 ):
929 # support use case for #7998, fetch autoincrement cols
930 # even if value was given
931 if implicit_returning:
932 compiler.implicit_returning.append(c)
933 elif compiler.dialect.postfetch_lastrowid:
934 compiler.postfetch_lastrowid = True
935
936 value = _handle_values_anonymous_param(
937 compiler,
938 c,
939 value,
940 name=(
941 _col_bind_name(c)
942 if not _compile_state_isinsert(compile_state)
943 or not compile_state._has_multi_parameters
944 else "%s_m0" % _col_bind_name(c)
945 ),
946 accumulate_bind_names=accumulated_bind_names,
947 **kw,
948 )
949 else:
950 # value is a SQL expression
951 value = compiler.process(
952 value.self_group(),
953 accumulate_bind_names=accumulated_bind_names,
954 **kw,
955 )
956
957 if compile_state.isupdate:
958 if implicit_return_defaults and c in implicit_return_defaults:
959 compiler.implicit_returning.append(c)
960
961 else:
962 compiler.postfetch.append(c)
963 else:
964 if c.primary_key:
965 if implicit_returning:
966 compiler.implicit_returning.append(c)
967 elif compiler.dialect.postfetch_lastrowid:
968 compiler.postfetch_lastrowid = True
969
970 elif implicit_return_defaults and (c in implicit_return_defaults):
971 compiler.implicit_returning.append(c)
972
973 else:
974 # postfetch specifically means, "we can SELECT the row we just
975 # inserted by primary key to get back the server generated
976 # defaults". so by definition this can't be used to get the
977 # primary key value back, because we need to have it ahead of
978 # time.
979
980 compiler.postfetch.append(c)
981
982 values.append((c, col_value, value, accumulated_bind_names))
983
984
985def _append_param_insert_pk_returning(compiler, stmt, c, values, kw):
986 """Create a primary key expression in the INSERT statement where
987 we want to populate result.inserted_primary_key and RETURNING
988 is available.
989
990 """
991 if c.default is not None:
992 if c.default.is_sequence:
993 if compiler.dialect.supports_sequences and (
994 not c.default.optional
995 or not compiler.dialect.sequences_optional
996 ):
997 accumulated_bind_names: Set[str] = set()
998 values.append(
999 (
1000 c,
1001 compiler.preparer.format_column(c),
1002 compiler.process(
1003 c.default,
1004 accumulate_bind_names=accumulated_bind_names,
1005 **kw,
1006 ),
1007 accumulated_bind_names,
1008 )
1009 )
1010 compiler.implicit_returning.append(c)
1011 elif c.default.is_clause_element:
1012 accumulated_bind_names = set()
1013 values.append(
1014 (
1015 c,
1016 compiler.preparer.format_column(c),
1017 compiler.process(
1018 c.default.arg.self_group(),
1019 accumulate_bind_names=accumulated_bind_names,
1020 **kw,
1021 ),
1022 accumulated_bind_names,
1023 )
1024 )
1025 compiler.implicit_returning.append(c)
1026 else:
1027 # client side default. OK we can't use RETURNING, need to
1028 # do a "prefetch", which in fact fetches the default value
1029 # on the Python side
1030 values.append(
1031 (
1032 c,
1033 compiler.preparer.format_column(c),
1034 _create_insert_prefetch_bind_param(compiler, c, **kw),
1035 (c.key,),
1036 )
1037 )
1038 elif c is stmt.table._autoincrement_column or c.server_default is not None:
1039 compiler.implicit_returning.append(c)
1040 elif not c.nullable:
1041 # no .default, no .server_default, not autoincrement, we have
1042 # no indication this primary key column will have any value
1043 _warn_pk_with_no_anticipated_value(c)
1044
1045
1046def _append_param_insert_pk_no_returning(compiler, stmt, c, values, kw):
1047 """Create a primary key expression in the INSERT statement where
1048 we want to populate result.inserted_primary_key and we cannot use
1049 RETURNING.
1050
1051 Depending on the kind of default here we may create a bound parameter
1052 in the INSERT statement and pre-execute a default generation function,
1053 or we may use cursor.lastrowid if supported by the dialect.
1054
1055
1056 """
1057
1058 if (
1059 # column has a Python-side default
1060 c.default is not None
1061 and (
1062 # and it either is not a sequence, or it is and we support
1063 # sequences and want to invoke it
1064 not c.default.is_sequence
1065 or (
1066 compiler.dialect.supports_sequences
1067 and (
1068 not c.default.optional
1069 or not compiler.dialect.sequences_optional
1070 )
1071 )
1072 )
1073 ) or (
1074 # column is the "autoincrement column"
1075 c is stmt.table._autoincrement_column
1076 and (
1077 # dialect can't use cursor.lastrowid
1078 not compiler.dialect.postfetch_lastrowid
1079 and (
1080 # column has a Sequence and we support those
1081 (
1082 c.default is not None
1083 and c.default.is_sequence
1084 and compiler.dialect.supports_sequences
1085 )
1086 or
1087 # column has no default on it, but dialect can run the
1088 # "autoincrement" mechanism explicitly, e.g. PostgreSQL
1089 # SERIAL we know the sequence name
1090 (
1091 c.default is None
1092 and compiler.dialect.preexecute_autoincrement_sequences
1093 )
1094 )
1095 )
1096 ):
1097 # do a pre-execute of the default
1098 values.append(
1099 (
1100 c,
1101 compiler.preparer.format_column(c),
1102 _create_insert_prefetch_bind_param(compiler, c, **kw),
1103 (c.key,),
1104 )
1105 )
1106 elif (
1107 c.default is None
1108 and c.server_default is None
1109 and not c.nullable
1110 and c is not stmt.table._autoincrement_column
1111 ):
1112 # no .default, no .server_default, not autoincrement, we have
1113 # no indication this primary key column will have any value
1114 _warn_pk_with_no_anticipated_value(c)
1115 elif compiler.dialect.postfetch_lastrowid:
1116 # finally, where it seems like there will be a generated primary key
1117 # value and we haven't set up any other way to fetch it, and the
1118 # dialect supports cursor.lastrowid, switch on the lastrowid flag so
1119 # that the DefaultExecutionContext calls upon cursor.lastrowid
1120 compiler.postfetch_lastrowid = True
1121
1122
1123def _append_param_insert_hasdefault(
1124 compiler, stmt, c, implicit_return_defaults, values, kw
1125):
1126 if c.default.is_sequence:
1127 if compiler.dialect.supports_sequences and (
1128 not c.default.optional or not compiler.dialect.sequences_optional
1129 ):
1130 accumulated_bind_names: Set[str] = set()
1131 values.append(
1132 (
1133 c,
1134 compiler.preparer.format_column(c),
1135 compiler.process(
1136 c.default,
1137 accumulate_bind_names=accumulated_bind_names,
1138 **kw,
1139 ),
1140 accumulated_bind_names,
1141 )
1142 )
1143 if implicit_return_defaults and c in implicit_return_defaults:
1144 compiler.implicit_returning.append(c)
1145 elif not c.primary_key:
1146 compiler.postfetch.append(c)
1147 elif c.default.is_clause_element:
1148 accumulated_bind_names = set()
1149 values.append(
1150 (
1151 c,
1152 compiler.preparer.format_column(c),
1153 compiler.process(
1154 c.default.arg.self_group(),
1155 accumulate_bind_names=accumulated_bind_names,
1156 **kw,
1157 ),
1158 accumulated_bind_names,
1159 )
1160 )
1161
1162 if implicit_return_defaults and c in implicit_return_defaults:
1163 compiler.implicit_returning.append(c)
1164 elif not c.primary_key:
1165 # don't add primary key column to postfetch
1166 compiler.postfetch.append(c)
1167 else:
1168 values.append(
1169 (
1170 c,
1171 compiler.preparer.format_column(c),
1172 _create_insert_prefetch_bind_param(compiler, c, **kw),
1173 (c.key,),
1174 )
1175 )
1176
1177
1178def _append_param_insert_select_hasdefault(
1179 compiler: SQLCompiler,
1180 stmt: ValuesBase,
1181 c: ColumnClause[Any],
1182 values: List[_CrudParamElementSQLExpr],
1183 kw: Dict[str, Any],
1184) -> None:
1185 if default_is_sequence(c.default):
1186 if compiler.dialect.supports_sequences and (
1187 not c.default.optional or not compiler.dialect.sequences_optional
1188 ):
1189 values.append(
1190 (
1191 c,
1192 compiler.preparer.format_column(c),
1193 c.default.next_value(),
1194 (),
1195 )
1196 )
1197 elif default_is_clause_element(c.default):
1198 values.append(
1199 (
1200 c,