codekingpro/portable-devtools
115k
1# sql/crud.py
2# Copyright (C) 2005-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# 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: _CrudParamSequence
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 = 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[_CrudParamElement] = []
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 raise exc.CompileError(
336 "Unconsumed column names: %s"
337 % (", ".join("%s" % (c,) for c in check))
338 )
339
340 is_default_metavalue_only = False
341
342 if (
343 _compile_state_isinsert(compile_state)
344 and compile_state._has_multi_parameters
345 ):
346 # is a multiparams, is not an insert from a select
347 assert not stmt._select_names
348 multi_extended_values = _extend_values_for_multiparams(
349 compiler,
350 stmt,
351 compile_state,
352 cast(
353 "Sequence[_CrudParamElementStr]",
354 values,
355 ),
356 cast("Callable[..., str]", _column_as_key),
357 kw,
358 )
359 return _CrudParams(values, multi_extended_values)
360 elif (
361 not values
362 and compiler.for_executemany
363 and compiler.dialect.supports_default_metavalue
364 ):
365 # convert an "INSERT DEFAULT VALUES"
366 # into INSERT (firstcol) VALUES (DEFAULT) which can be turned
367 # into an in-place multi values. This supports
368 # insert_executemany_returning mode :)
369 values = [
370 (
371 _as_dml_column(stmt.table.columns[0]),
372 compiler.preparer.format_column(stmt.table.columns[0]),
373 compiler.dialect.default_metavalue_token,
374 (),
375 )
376 ]
377 is_default_metavalue_only = True
378
379 return _CrudParams(
380 values,
381 [],
382 is_default_metavalue_only=is_default_metavalue_only,
383 use_insertmanyvalues=use_insertmanyvalues,
384 use_sentinel_columns=use_sentinel_columns,
385 )
386
387
388@overload
389def _create_bind_param(
390 compiler: SQLCompiler,
391 col: ColumnElement[Any],
392 value: Any,
393 process: Literal[True] = ...,
394 required: bool = False,
395 name: Optional[str] = None,
396 **kw: Any,
397) -> str: ...
398
399
400@overload
401def _create_bind_param(
402 compiler: SQLCompiler,
403 col: ColumnElement[Any],
404 value: Any,
405 **kw: Any,
406) -> str: ...
407
408
409def _create_bind_param(
410 compiler: SQLCompiler,
411 col: ColumnElement[Any],
412 value: Any,
413 process: bool = True,
414 required: bool = False,
415 name: Optional[str] = None,
416 **kw: Any,
417) -> Union[str, elements.BindParameter[Any]]:
418 if name is None:
419 name = col.key
420 bindparam = elements.BindParameter(
421 name, value, type_=col.type, required=required
422 )
423 bindparam._is_crud = True
424 if process:
425 return bindparam._compiler_dispatch(compiler, **kw)
426 else:
427 return bindparam
428
429
430def _handle_values_anonymous_param(compiler, col, value, name, **kw):
431 # the insert() and update() constructs as of 1.4 will now produce anonymous
432 # bindparam() objects in the values() collections up front when given plain
433 # literal values. This is so that cache key behaviors, which need to
434 # produce bound parameters in deterministic order without invoking any
435 # compilation here, can be applied to these constructs when they include
436 # values() (but not yet multi-values, which are not included in caching
437 # right now).
438 #
439 # in order to produce the desired "crud" style name for these parameters,
440 # which will also be targetable in engine/default.py through the usual
441 # conventions, apply our desired name to these unique parameters by
442 # populating the compiler truncated names cache with the desired name,
443 # rather than having
444 # compiler.visit_bindparam()->compiler._truncated_identifier make up a
445 # name. Saves on call counts also.
446
447 # for INSERT/UPDATE that's a CTE, we don't need names to match to
448 # external parameters and these would also conflict in the case where
449 # multiple insert/update are combined together using CTEs
450 is_cte = "visiting_cte" in kw
451
452 if (
453 not is_cte
454 and value.unique
455 and isinstance(value.key, elements._truncated_label)
456 ):
457 compiler.truncated_names[("bindparam", value.key)] = name
458
459 if value.type._isnull:
460 # either unique parameter, or other bound parameters that were
461 # passed in directly
462 # set type to that of the column unconditionally
463 value = value._with_binary_element_type(col.type)
464
465 return value._compiler_dispatch(compiler, **kw)
466
467
468def _key_getters_for_crud_column(
469 compiler: SQLCompiler, stmt: ValuesBase, compile_state: DMLState
470) -> Tuple[
471 Callable[[Union[str, ColumnClause[Any]]], Union[str, Tuple[str, str]]],
472 Callable[[ColumnClause[Any]], Union[str, Tuple[str, str]]],
473 _BindNameForColProtocol,
474]:
475 if dml.isupdate(compile_state) and compile_state._extra_froms:
476 # when extra tables are present, refer to the columns
477 # in those extra tables as table-qualified, including in
478 # dictionaries and when rendering bind param names.
479 # the "main" table of the statement remains unqualified,
480 # allowing the most compatibility with a non-multi-table
481 # statement.
482 _et = set(compile_state._extra_froms)
483
484 c_key_role = functools.partial(
485 coercions.expect_as_key, roles.DMLColumnRole
486 )
487
488 def _column_as_key(
489 key: Union[ColumnClause[Any], str]
490 ) -> Union[str, Tuple[str, str]]:
491 str_key = c_key_role(key)
492 if hasattr(key, "table") and key.table in _et:
493 return (key.table.name, str_key) # type: ignore
494 else:
495 return str_key
496
497 def _getattr_col_key(
498 col: ColumnClause[Any],
499 ) -> Union[str, Tuple[str, str]]:
500 if col.table in _et:
501 return (col.table.name, col.key) # type: ignore
502 else:
503 return col.key
504
505 def _col_bind_name(col: ColumnClause[Any]) -> str:
506 if col.table in _et:
507 if TYPE_CHECKING:
508 assert isinstance(col.table, TableClause)
509 return "%s_%s" % (col.table.name, col.key)
510 else:
511 return col.key
512
513 else:
514 _column_as_key = functools.partial(
515 coercions.expect_as_key, roles.DMLColumnRole
516 )
517 _getattr_col_key = _col_bind_name = operator.attrgetter("key") # type: ignore # noqa: E501
518
519 return _column_as_key, _getattr_col_key, _col_bind_name
520
521
522def _scan_insert_from_select_cols(
523 compiler,
524 stmt,
525 compile_state,
526 parameters,
527 _getattr_col_key,
528 _column_as_key,
529 _col_bind_name,
530 check_columns,
531 values,
532 toplevel,
533 kw,
534):
535 cols = [stmt.table.c[_column_as_key(name)] for name in stmt._select_names]
536
537 assert compiler.stack[-1]["selectable"] is stmt
538
539 compiler.stack[-1]["insert_from_select"] = stmt.select
540
541 add_select_cols: List[_CrudParamElementSQLExpr] = []
542 if stmt.include_insert_from_select_defaults:
543 col_set = set(cols)
544 for col in stmt.table.columns:
545 # omit columns that were not in the SELECT statement.
546 # this will omit columns marked as omit_from_statements naturally,
547 # as long as that col was not explicit in the SELECT.
548 # if an omit_from_statements col has a "default" on it, then
549 # we need to include it, as these defaults should still fire off.
550 # but, if it has that default and it's the "sentinel" default,
551 # we don't do sentinel default operations for insert_from_select
552 # here so we again omit it.
553 if (
554 col not in col_set
555 and col.default
556 and not col.default.is_sentinel
557 ):
558 cols.append(col)
559
560 for c in cols:
561 col_key = _getattr_col_key(c)
562 if col_key in parameters and col_key not in check_columns:
563 parameters.pop(col_key)
564 values.append((c, compiler.preparer.format_column(c), None, ()))
565 else:
566 _append_param_insert_select_hasdefault(
567 compiler, stmt, c, add_select_cols, kw
568 )
569
570 if add_select_cols:
571 values.extend(add_select_cols)
572 ins_from_select = compiler.stack[-1]["insert_from_select"]
573 if not isinstance(ins_from_select, Select):
574 raise exc.CompileError(
575 f"Can't extend statement for INSERT..FROM SELECT to include "
576 f"additional default-holding column(s) "
577 f"""{
578 ', '.join(repr(key) for _, key, _, _ in add_select_cols)
579 }. Convert the selectable to a subquery() first, or pass """
580 "include_defaults=False to Insert.from_select() to skip these "
581 "columns."
582 )
583 ins_from_select = ins_from_select._generate()
584 # copy raw_columns
585 ins_from_select._raw_columns = list(ins_from_select._raw_columns) + [
586 expr for _, _, expr, _ in add_select_cols
587 ]
588 compiler.stack[-1]["insert_from_select"] = ins_from_select
589
590
591def _scan_cols(
592 compiler,
593 stmt,
594 compile_state,
595 parameters,
596 _getattr_col_key,
597 _column_as_key,
598 _col_bind_name,
599 check_columns,
600 values,
601 toplevel,
602 kw,
603):
604 (
605 need_pks,
606 implicit_returning,
607 implicit_return_defaults,
608 postfetch_lastrowid,
609 use_insertmanyvalues,
610 use_sentinel_columns,
611 ) = _get_returning_modifiers(compiler, stmt, compile_state, toplevel)
612
613 assert compile_state.isupdate or compile_state.isinsert
614
615 if compile_state._parameter_ordering:
616 parameter_ordering = [
617 _column_as_key(key) for key in compile_state._parameter_ordering
618 ]
619 ordered_keys = set(parameter_ordering)
620 cols = [
621 stmt.table.c[key]
622 for key in parameter_ordering
623 if isinstance(key, str) and key in stmt.table.c
624 ] + [c for c in stmt.table.c if c.key not in ordered_keys]
625
626 else:
627 cols = stmt.table.columns
628
629 isinsert = _compile_state_isinsert(compile_state)
630 if isinsert and not compile_state._has_multi_parameters:
631 # new rules for #7998. fetch lastrowid or implicit returning
632 # for autoincrement column even if parameter is NULL, for DBs that
633 # override NULL param for primary key (sqlite, mysql/mariadb)
634 autoincrement_col = stmt.table._autoincrement_column
635 insert_null_pk_still_autoincrements = (
636 compiler.dialect.insert_null_pk_still_autoincrements
637 )
638 else:
639 autoincrement_col = insert_null_pk_still_autoincrements = None
640
641 if stmt._supplemental_returning:
642 supplemental_returning = set(stmt._supplemental_returning)
643 else:
644 supplemental_returning = set()
645
646 compiler_implicit_returning = compiler.implicit_returning
647
648 # TODO - see TODO(return_defaults_columns) below
649 # cols_in_params = set()
650
651 for c in cols:
652 # scan through every column in the target table
653
654 col_key = _getattr_col_key(c)
655
656 if col_key in parameters and col_key not in check_columns:
657 # parameter is present for the column. use that.
658
659 _append_param_parameter(
660 compiler,
661 stmt,
662 compile_state,
663 c,
664 col_key,
665 parameters,
666 _col_bind_name,
667 implicit_returning,
668 implicit_return_defaults,
669 postfetch_lastrowid,
670 values,
671 autoincrement_col,
672 insert_null_pk_still_autoincrements,
673 kw,
674 )
675
676 # TODO - see TODO(return_defaults_columns) below
677 # cols_in_params.add(c)
678
679 elif isinsert:
680 # no parameter is present and it's an insert.
681
682 if c.primary_key and need_pks:
683 # it's a primary key column, it will need to be generated by a
684 # default generator of some kind, and the statement expects
685 # inserted_primary_key to be available.
686
687 if implicit_returning:
688 # we can use RETURNING, find out how to invoke this
689 # column and get the value where RETURNING is an option.
690 # we can inline server-side functions in this case.
691
692 _append_param_insert_pk_returning(
693 compiler, stmt, c, values, kw
694 )
695 else:
696 # otherwise, find out how to invoke this column
697 # and get its value where RETURNING is not an option.
698 # if we have to invoke a server-side function, we need
699 # to pre-execute it. or if this is a straight
700 # autoincrement column and the dialect supports it
701 # we can use cursor.lastrowid.
702
703 _append_param_insert_pk_no_returning(
704 compiler, stmt, c, values, kw
705 )
706
707 elif c.default is not None:
708 # column has a default, but it's not a pk column, or it is but
709 # we don't need to get the pk back.
710 if not c.default.is_sentinel or (
711 use_sentinel_columns is not None
712 ):
713 _append_param_insert_hasdefault(
714 compiler, stmt, c, implicit_return_defaults, values, kw
715 )
716
717 elif c.server_default is not None:
718 # column has a DDL-level default, and is either not a pk
719 # column or we don't need the pk.
720 if implicit_return_defaults and c in implicit_return_defaults:
721 compiler_implicit_returning.append(c)
722 elif not c.primary_key:
723 compiler.postfetch.append(c)
724
725 elif implicit_return_defaults and c in implicit_return_defaults:
726 compiler_implicit_returning.append(c)
727
728 elif (
729 c.primary_key
730 and c is not stmt.table._autoincrement_column
731 and not c.nullable
732 ):
733 _warn_pk_with_no_anticipated_value(c)
734
735 elif compile_state.isupdate:
736 # no parameter is present and it's an insert.
737
738 _append_param_update(
739 compiler,
740 compile_state,
741 stmt,
742 c,
743 implicit_return_defaults,
744 values,
745 kw,
746 )
747
748 # adding supplemental cols to implicit_returning in table
749 # order so that order is maintained between multiple INSERT
750 # statements which may have different parameters included, but all
751 # have the same RETURNING clause
752 if (
753 c in supplemental_returning
754 and c not in compiler_implicit_returning
755 ):
756 compiler_implicit_returning.append(c)
757
758 if supplemental_returning:
759 # we should have gotten every col into implicit_returning,
760 # however supplemental returning can also have SQL functions etc.
761 # in it
762 remaining_supplemental = supplemental_returning.difference(
763 compiler_implicit_returning
764 )
765 compiler_implicit_returning.extend(
766 c
767 for c in stmt._supplemental_returning
768 if c in remaining_supplemental
769 )
770
771 # TODO(return_defaults_columns): there can still be more columns in
772 # _return_defaults_columns in the case that they are from something like an
773 # aliased of the table. we can add them here, however this breaks other ORM
774 # things. so this is for another day. see
775 # test/orm/dml/test_update_delete_where.py -> test_update_from_alias
776
777 # if stmt._return_defaults_columns:
778 # compiler_implicit_returning.extend(
779 # set(stmt._return_defaults_columns)
780 # .difference(compiler_implicit_returning)
781 # .difference(cols_in_params)
782 # )
783
784 return (use_insertmanyvalues, use_sentinel_columns)
785
786
787def _setup_delete_return_defaults(
788 compiler,
789 stmt,
790 compile_state,
791 parameters,
792 _getattr_col_key,
793 _column_as_key,
794 _col_bind_name,
795 check_columns,
796 values,
797 toplevel,
798 kw,
799):
800 (_, _, implicit_return_defaults, *_) = _get_returning_modifiers(
801 compiler, stmt, compile_state, toplevel
802 )
803
804 if not implicit_return_defaults:
805 return
806
807 if stmt._return_defaults_columns:
808 compiler.implicit_returning.extend(implicit_return_defaults)
809
810 if stmt._supplemental_returning:
811 ir_set = set(compiler.implicit_returning)
812 compiler.implicit_returning.extend(
813 c for c in stmt._supplemental_returning if c not in ir_set
814 )
815
816
817def _append_param_parameter(
818 compiler,
819 stmt,
820 compile_state,
821 c,
822 col_key,
823 parameters,
824 _col_bind_name,
825 implicit_returning,
826 implicit_return_defaults,
827 postfetch_lastrowid,
828 values,
829 autoincrement_col,
830 insert_null_pk_still_autoincrements,
831 kw,
832):
833 value = parameters.pop(col_key)
834
835 col_value = compiler.preparer.format_column(
836 c, use_table=compile_state.include_table_with_column_exprs
837 )
838
839 accumulated_bind_names: Set[str] = set()
840
841 if coercions._is_literal(value):
842 if (
843 insert_null_pk_still_autoincrements
844 and c.primary_key
845 and c is autoincrement_col
846 ):
847 # support use case for #7998, fetch autoincrement cols
848 # even if value was given.
849
850 if postfetch_lastrowid:
851 compiler.postfetch_lastrowid = True
852 elif implicit_returning:
853 compiler.implicit_returning.append(c)
854
855 value = _create_bind_param(
856 compiler,
857 c,
858 value,
859 required=value is REQUIRED,
860 name=(
861 _col_bind_name(c)
862 if not _compile_state_isinsert(compile_state)
863 or not compile_state._has_multi_parameters
864 else "%s_m0" % _col_bind_name(c)
865 ),
866 accumulate_bind_names=accumulated_bind_names,
867 **kw,
868 )
869 elif value._is_bind_parameter:
870 if (
871 insert_null_pk_still_autoincrements
872 and value.value is None
873 and c.primary_key
874 and c is autoincrement_col
875 ):
876 # support use case for #7998, fetch autoincrement cols
877 # even if value was given
878 if implicit_returning:
879 compiler.implicit_returning.append(c)
880 elif compiler.dialect.postfetch_lastrowid:
881 compiler.postfetch_lastrowid = True
882
883 value = _handle_values_anonymous_param(
884 compiler,
885 c,
886 value,
887 name=(
888 _col_bind_name(c)
889 if not _compile_state_isinsert(compile_state)
890 or not compile_state._has_multi_parameters
891 else "%s_m0" % _col_bind_name(c)
892 ),
893 accumulate_bind_names=accumulated_bind_names,
894 **kw,
895 )
896 else:
897 # value is a SQL expression
898 value = compiler.process(
899 value.self_group(),
900 accumulate_bind_names=accumulated_bind_names,
901 **kw,
902 )
903
904 if compile_state.isupdate:
905 if implicit_return_defaults and c in implicit_return_defaults:
906 compiler.implicit_returning.append(c)
907
908 else:
909 compiler.postfetch.append(c)
910 else:
911 if c.primary_key:
912 if implicit_returning:
913 compiler.implicit_returning.append(c)
914 elif compiler.dialect.postfetch_lastrowid:
915 compiler.postfetch_lastrowid = True
916
917 elif implicit_return_defaults and (c in implicit_return_defaults):
918 compiler.implicit_returning.append(c)
919
920 else:
921 # postfetch specifically means, "we can SELECT the row we just
922 # inserted by primary key to get back the server generated
923 # defaults". so by definition this can't be used to get the
924 # primary key value back, because we need to have it ahead of
925 # time.
926
927 compiler.postfetch.append(c)
928
929 values.append((c, col_value, value, accumulated_bind_names))
930
931
932def _append_param_insert_pk_returning(compiler, stmt, c, values, kw):
933 """Create a primary key expression in the INSERT statement where
934 we want to populate result.inserted_primary_key and RETURNING
935 is available.
936
937 """
938 if c.default is not None:
939 if c.default.is_sequence:
940 if compiler.dialect.supports_sequences and (
941 not c.default.optional
942 or not compiler.dialect.sequences_optional
943 ):
944 accumulated_bind_names: Set[str] = set()
945 values.append(
946 (
947 c,
948 compiler.preparer.format_column(c),
949 compiler.process(
950 c.default,
951 accumulate_bind_names=accumulated_bind_names,
952 **kw,
953 ),
954 accumulated_bind_names,
955 )
956 )
957 compiler.implicit_returning.append(c)
958 elif c.default.is_clause_element:
959 accumulated_bind_names = set()
960 values.append(
961 (
962 c,
963 compiler.preparer.format_column(c),
964 compiler.process(
965 c.default.arg.self_group(),
966 accumulate_bind_names=accumulated_bind_names,
967 **kw,
968 ),
969 accumulated_bind_names,
970 )
971 )
972 compiler.implicit_returning.append(c)
973 else:
974 # client side default. OK we can't use RETURNING, need to
975 # do a "prefetch", which in fact fetches the default value
976 # on the Python side
977 values.append(
978 (
979 c,
980 compiler.preparer.format_column(c),
981 _create_insert_prefetch_bind_param(compiler, c, **kw),
982 (c.key,),
983 )
984 )
985 elif c is stmt.table._autoincrement_column or c.server_default is not None:
986 compiler.implicit_returning.append(c)
987 elif not c.nullable:
988 # no .default, no .server_default, not autoincrement, we have
989 # no indication this primary key column will have any value
990 _warn_pk_with_no_anticipated_value(c)
991
992
993def _append_param_insert_pk_no_returning(compiler, stmt, c, values, kw):
994 """Create a primary key expression in the INSERT statement where
995 we want to populate result.inserted_primary_key and we cannot use
996 RETURNING.
997
998 Depending on the kind of default here we may create a bound parameter
999 in the INSERT statement and pre-execute a default generation function,
1000 or we may use cursor.lastrowid if supported by the dialect.
1001
1002
1003 """
1004
1005 if (
1006 # column has a Python-side default
1007 c.default is not None
1008 and (
1009 # and it either is not a sequence, or it is and we support
1010 # sequences and want to invoke it
1011 not c.default.is_sequence
1012 or (
1013 compiler.dialect.supports_sequences
1014 and (
1015 not c.default.optional
1016 or not compiler.dialect.sequences_optional
1017 )
1018 )
1019 )
1020 ) or (
1021 # column is the "autoincrement column"
1022 c is stmt.table._autoincrement_column
1023 and (
1024 # dialect can't use cursor.lastrowid
1025 not compiler.dialect.postfetch_lastrowid
1026 and (
1027 # column has a Sequence and we support those
1028 (
1029 c.default is not None
1030 and c.default.is_sequence
1031 and compiler.dialect.supports_sequences
1032 )
1033 or
1034 # column has no default on it, but dialect can run the
1035 # "autoincrement" mechanism explicitly, e.g. PostgreSQL
1036 # SERIAL we know the sequence name
1037 (
1038 c.default is None
1039 and compiler.dialect.preexecute_autoincrement_sequences
1040 )
1041 )
1042 )
1043 ):
1044 # do a pre-execute of the default
1045 values.append(
1046 (
1047 c,
1048 compiler.preparer.format_column(c),
1049 _create_insert_prefetch_bind_param(compiler, c, **kw),
1050 (c.key,),
1051 )
1052 )
1053 elif (
1054 c.default is None
1055 and c.server_default is None
1056 and not c.nullable
1057 and c is not stmt.table._autoincrement_column
1058 ):
1059 # no .default, no .server_default, not autoincrement, we have
1060 # no indication this primary key column will have any value
1061 _warn_pk_with_no_anticipated_value(c)
1062 elif compiler.dialect.postfetch_lastrowid:
1063 # finally, where it seems like there will be a generated primary key
1064 # value and we haven't set up any other way to fetch it, and the
1065 # dialect supports cursor.lastrowid, switch on the lastrowid flag so
1066 # that the DefaultExecutionContext calls upon cursor.lastrowid
1067 compiler.postfetch_lastrowid = True
1068
1069
1070def _append_param_insert_hasdefault(
1071 compiler, stmt, c, implicit_return_defaults, values, kw
1072):
1073 if c.default.is_sequence:
1074 if compiler.dialect.supports_sequences and (
1075 not c.default.optional or not compiler.dialect.sequences_optional
1076 ):
1077 accumulated_bind_names: Set[str] = set()
1078 values.append(
1079 (
1080 c,
1081 compiler.preparer.format_column(c),
1082 compiler.process(
1083 c.default,
1084 accumulate_bind_names=accumulated_bind_names,
1085 **kw,
1086 ),
1087 accumulated_bind_names,
1088 )
1089 )
1090 if implicit_return_defaults and c in implicit_return_defaults:
1091 compiler.implicit_returning.append(c)
1092 elif not c.primary_key:
1093 compiler.postfetch.append(c)
1094 elif c.default.is_clause_element:
1095 accumulated_bind_names = set()
1096 values.append(
1097 (
1098 c,
1099 compiler.preparer.format_column(c),
1100 compiler.process(
1101 c.default.arg.self_group(),
1102 accumulate_bind_names=accumulated_bind_names,
1103 **kw,
1104 ),
1105 accumulated_bind_names,
1106 )
1107 )
1108
1109 if implicit_return_defaults and c in implicit_return_defaults:
1110 compiler.implicit_returning.append(c)
1111 elif not c.primary_key:
1112 # don't add primary key column to postfetch
1113 compiler.postfetch.append(c)
1114 else:
1115 values.append(
1116 (
1117 c,
1118 compiler.preparer.format_column(c),
1119 _create_insert_prefetch_bind_param(compiler, c, **kw),
1120 (c.key,),
1121 )
1122 )
1123
1124
1125def _append_param_insert_select_hasdefault(
1126 compiler: SQLCompiler,
1127 stmt: ValuesBase,
1128 c: ColumnClause[Any],
1129 values: List[_CrudParamElementSQLExpr],
1130 kw: Dict[str, Any],
1131) -> None:
1132 if default_is_sequence(c.default):
1133 if compiler.dialect.supports_sequences and (
1134 not c.default.optional or not compiler.dialect.sequences_optional
1135 ):
1136 values.append(
1137 (
1138 c,
1139 compiler.preparer.format_column(c),
1140 c.default.next_value(),
1141 (),
1142 )
1143 )
1144 elif default_is_clause_element(c.default):
1145 values.append(
1146 (
1147 c,
1148 compiler.preparer.format_column(c),
1149 c.default.arg.self_group(),
1150 (),
1151 )
1152 )
1153 else:
1154 values.append(
1155 (
1156 c,
1157 compiler.preparer.format_column(c),
1158 _create_insert_prefetch_bind_param(
1159 compiler, c, process=False, **kw
1160 ),
1161 (c.key,),
1162 )
1163 )
1164
1165
1166def _append_param_update(
1167 compiler, compile_state, stmt, c, implicit_return_defaults, values, kw
1168):
1169 include_table = compile_state.include_table_with_column_exprs
1170 if c.onupdate is not None and not c.onupdate.is_sequence:
1171 if c.onupdate.is_clause_element:
1172 values.append(
1173 (
1174 c,
1175 compiler.preparer.format_column(
1176 c,
1177 use_table=include_table,
1178 ),
1179 compiler.process(c.onupdate.arg.self_group(), **kw),
1180 (),
1181 )
1182 )
1183 if implicit_return_defaults and c in implicit_return_defaults:
1184 compiler.implicit_returning.append(c)
1185 else:
1186 compiler.postfetch.append(c)
1187 else:
1188 values.append(
1189 (
1190 c,
1191 compiler.preparer.format_column(
1192 c,
1193 use_table=include_table,
1194 ),
1195 _create_update_prefetch_bind_param(compiler, c, **kw),
1196 (c.key,),
1197 )
1198 )
1199 elif c.server_onupdate is not None:
1200 if implicit_return_defaults and c in implicit_return_defaults: