Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/compiler.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
9"""Base SQL and DDL compiler implementations.
11Classes provided include:
13:class:`.compiler.SQLCompiler` - renders SQL
14strings
16:class:`.compiler.DDLCompiler` - renders DDL
17(data definition language) strings
19:class:`.compiler.GenericTypeCompiler` - renders
20type specification strings.
22To generate user-defined SQL strings, see
23:doc:`/ext/compiler`.
25"""
27from __future__ import annotations
29import collections
30import collections.abc as collections_abc
31import contextlib
32from enum import IntEnum
33import functools
34import itertools
35import operator
36import re
37from time import perf_counter
38import typing
39from typing import Any
40from typing import Callable
41from typing import cast
42from typing import ClassVar
43from typing import Dict
44from typing import Final
45from typing import FrozenSet
46from typing import Iterable
47from typing import Iterator
48from typing import List
49from typing import Literal
50from typing import Mapping
51from typing import MutableMapping
52from typing import NamedTuple
53from typing import NoReturn
54from typing import Optional
55from typing import Pattern
56from typing import Protocol
57from typing import Sequence
58from typing import Set
59from typing import Tuple
60from typing import Type
61from typing import TYPE_CHECKING
62from typing import TypedDict
63from typing import Union
65from . import base
66from . import coercions
67from . import crud
68from . import elements
69from . import functions
70from . import operators
71from . import roles
72from . import schema
73from . import selectable
74from . import sqltypes
75from . import util as sql_util
76from ._typing import is_column_element
77from ._typing import is_dml
78from .base import _de_clone
79from .base import _from_objects
80from .base import _NONE_NAME
81from .base import _SentinelDefaultCharacterization
82from .base import NO_ARG
83from .elements import quoted_name
84from .sqltypes import TupleType
85from .visitors import prefix_anon_map
86from .. import exc
87from .. import util
88from ..util import FastIntFlag
89from ..util.typing import Self
90from ..util.typing import TupleAny
91from ..util.typing import Unpack
93if typing.TYPE_CHECKING:
94 from .annotation import _AnnotationDict
95 from .base import _AmbiguousTableNameMap
96 from .base import CompileState
97 from .base import Executable
98 from .base import ExecutableStatement
99 from .cache_key import CacheKey
100 from .ddl import _TableViaSelect
101 from .ddl import CreateTableAs
102 from .ddl import CreateView
103 from .ddl import ExecutableDDLElement
104 from .dml import Delete
105 from .dml import Insert
106 from .dml import Update
107 from .dml import UpdateBase
108 from .dml import UpdateDMLState
109 from .dml import ValuesBase
110 from .elements import _truncated_label
111 from .elements import BinaryExpression
112 from .elements import BindParameter
113 from .elements import ClauseElement
114 from .elements import ColumnClause
115 from .elements import ColumnElement
116 from .elements import False_
117 from .elements import Label
118 from .elements import Null
119 from .elements import True_
120 from .functions import Function
121 from .schema import CheckConstraint
122 from .schema import Column
123 from .schema import Constraint
124 from .schema import ForeignKeyConstraint
125 from .schema import IdentityOptions
126 from .schema import Index
127 from .schema import PrimaryKeyConstraint
128 from .schema import Table
129 from .schema import UniqueConstraint
130 from .selectable import _ColumnsClauseElement
131 from .selectable import AliasedReturnsRows
132 from .selectable import CompoundSelectState
133 from .selectable import CTE
134 from .selectable import FromClause
135 from .selectable import NamedFromClause
136 from .selectable import ReturnsRows
137 from .selectable import Select
138 from .selectable import SelectState
139 from .type_api import _BindProcessorType
140 from .type_api import TypeDecorator
141 from .type_api import TypeEngine
142 from .type_api import UserDefinedType
143 from .visitors import Visitable
144 from ..engine.cursor import CursorResultMetaData
145 from ..engine.interfaces import _CoreSingleExecuteParams
146 from ..engine.interfaces import _DBAPIAnyExecuteParams
147 from ..engine.interfaces import _DBAPIMultiExecuteParams
148 from ..engine.interfaces import _DBAPISingleExecuteParams
149 from ..engine.interfaces import _ExecuteOptions
150 from ..engine.interfaces import _GenericSetInputSizesType
151 from ..engine.interfaces import _MutableCoreSingleExecuteParams
152 from ..engine.interfaces import Dialect
153 from ..engine.interfaces import SchemaTranslateMapType
156_FromHintsType = Dict["FromClause", str]
158RESERVED_WORDS = {
159 "all",
160 "analyse",
161 "analyze",
162 "and",
163 "any",
164 "array",
165 "as",
166 "asc",
167 "asymmetric",
168 "authorization",
169 "between",
170 "binary",
171 "both",
172 "case",
173 "cast",
174 "check",
175 "collate",
176 "column",
177 "constraint",
178 "create",
179 "cross",
180 "current_date",
181 "current_role",
182 "current_time",
183 "current_timestamp",
184 "current_user",
185 "default",
186 "deferrable",
187 "desc",
188 "distinct",
189 "do",
190 "else",
191 "end",
192 "except",
193 "false",
194 "for",
195 "foreign",
196 "freeze",
197 "from",
198 "full",
199 "grant",
200 "group",
201 "having",
202 "ilike",
203 "in",
204 "initially",
205 "inner",
206 "intersect",
207 "into",
208 "is",
209 "isnull",
210 "join",
211 "leading",
212 "left",
213 "like",
214 "limit",
215 "localtime",
216 "localtimestamp",
217 "natural",
218 "new",
219 "not",
220 "notnull",
221 "null",
222 "off",
223 "offset",
224 "old",
225 "on",
226 "only",
227 "or",
228 "order",
229 "outer",
230 "overlaps",
231 "placing",
232 "primary",
233 "references",
234 "right",
235 "select",
236 "session_user",
237 "set",
238 "similar",
239 "some",
240 "symmetric",
241 "table",
242 "then",
243 "to",
244 "trailing",
245 "true",
246 "union",
247 "unique",
248 "user",
249 "using",
250 "verbose",
251 "when",
252 "where",
253}
255LEGAL_CHARACTERS = re.compile(r"^[A-Z0-9_$]+$", re.I)
256LEGAL_CHARACTERS_PLUS_SPACE = re.compile(r"^[A-Z0-9_ $]+$", re.I)
257ILLEGAL_INITIAL_CHARACTERS = {str(x) for x in range(0, 10)}.union(["$"])
259FK_ON_DELETE = re.compile(
260 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I
261)
262FK_ON_UPDATE = re.compile(
263 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I
264)
265FK_INITIALLY = re.compile(r"^(?:DEFERRED|IMMEDIATE)$", re.I)
266_WINDOW_EXCLUDE_RE = re.compile(
267 r"^(?:CURRENT ROW|GROUP|TIES|NO OTHERS)$", re.I
268)
269BIND_PARAMS = re.compile(r"(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])", re.UNICODE)
270BIND_PARAMS_ESC = re.compile(r"\x5c(:[\w\$]*)(?![:\w\$])", re.UNICODE)
272_pyformat_template = "%%(%(name)s)s"
273BIND_TEMPLATES = {
274 "pyformat": _pyformat_template,
275 "qmark": "?",
276 "format": "%%s",
277 "numeric": ":[_POSITION]",
278 "numeric_dollar": "$[_POSITION]",
279 "named": ":%(name)s",
280}
283OPERATORS = {
284 # binary
285 operators.and_: " AND ",
286 operators.or_: " OR ",
287 operators.add: " + ",
288 operators.mul: " * ",
289 operators.sub: " - ",
290 operators.mod: " % ",
291 operators.neg: "-",
292 operators.lt: " < ",
293 operators.le: " <= ",
294 operators.ne: " != ",
295 operators.gt: " > ",
296 operators.ge: " >= ",
297 operators.eq: " = ",
298 operators.is_distinct_from: " IS DISTINCT FROM ",
299 operators.is_not_distinct_from: " IS NOT DISTINCT FROM ",
300 operators.concat_op: " || ",
301 operators.match_op: " MATCH ",
302 operators.not_match_op: " NOT MATCH ",
303 operators.in_op: " IN ",
304 operators.not_in_op: " NOT IN ",
305 operators.comma_op: ", ",
306 operators.from_: " FROM ",
307 operators.as_: " AS ",
308 operators.is_: " IS ",
309 operators.is_not: " IS NOT ",
310 operators.collate: " COLLATE ",
311 # unary
312 operators.exists: "EXISTS ",
313 operators.distinct_op: "DISTINCT ",
314 operators.inv: "NOT ",
315 operators.any_op: "ANY ",
316 operators.all_op: "ALL ",
317 # modifiers
318 operators.desc_op: " DESC",
319 operators.asc_op: " ASC",
320 operators.nulls_first_op: " NULLS FIRST",
321 operators.nulls_last_op: " NULLS LAST",
322 # bitwise
323 operators.bitwise_xor_op: " ^ ",
324 operators.bitwise_or_op: " | ",
325 operators.bitwise_and_op: " & ",
326 operators.bitwise_not_op: "~",
327 operators.bitwise_lshift_op: " << ",
328 operators.bitwise_rshift_op: " >> ",
329}
331FUNCTIONS: Dict[Type[Function[Any]], str] = {
332 functions.coalesce: "coalesce",
333 functions.current_date: "CURRENT_DATE",
334 functions.current_time: "CURRENT_TIME",
335 functions.current_timestamp: "CURRENT_TIMESTAMP",
336 functions.current_user: "CURRENT_USER",
337 functions.localtime: "LOCALTIME",
338 functions.localtimestamp: "LOCALTIMESTAMP",
339 functions.random: "random",
340 functions.sysdate: "sysdate",
341 functions.session_user: "SESSION_USER",
342 functions.user: "USER",
343 functions.cube: "CUBE",
344 functions.rollup: "ROLLUP",
345 functions.grouping_sets: "GROUPING SETS",
346}
349EXTRACT_MAP = {
350 "month": "month",
351 "day": "day",
352 "year": "year",
353 "second": "second",
354 "hour": "hour",
355 "doy": "doy",
356 "minute": "minute",
357 "quarter": "quarter",
358 "dow": "dow",
359 "week": "week",
360 "epoch": "epoch",
361 "milliseconds": "milliseconds",
362 "microseconds": "microseconds",
363 "timezone_hour": "timezone_hour",
364 "timezone_minute": "timezone_minute",
365}
367COMPOUND_KEYWORDS = {
368 selectable._CompoundSelectKeyword.UNION: "UNION",
369 selectable._CompoundSelectKeyword.UNION_ALL: "UNION ALL",
370 selectable._CompoundSelectKeyword.EXCEPT: "EXCEPT",
371 selectable._CompoundSelectKeyword.EXCEPT_ALL: "EXCEPT ALL",
372 selectable._CompoundSelectKeyword.INTERSECT: "INTERSECT",
373 selectable._CompoundSelectKeyword.INTERSECT_ALL: "INTERSECT ALL",
374}
377class ResultColumnsEntry(NamedTuple):
378 """Tracks a column expression that is expected to be represented
379 in the result rows for this statement.
381 This normally refers to the columns clause of a SELECT statement
382 but may also refer to a RETURNING clause, as well as for dialect-specific
383 emulations.
385 """
387 keyname: str
388 """string name that's expected in cursor.description"""
390 name: str
391 """column name, may be labeled"""
393 objects: Tuple[Any, ...]
394 """sequence of objects that should be able to locate this column
395 in a RowMapping. This is typically string names and aliases
396 as well as Column objects.
398 """
400 type: TypeEngine[Any]
401 """Datatype to be associated with this column. This is where
402 the "result processing" logic directly links the compiled statement
403 to the rows that come back from the cursor.
405 """
408class _ResultMapAppender(Protocol):
409 def __call__(
410 self,
411 keyname: str,
412 name: str,
413 objects: Sequence[Any],
414 type_: TypeEngine[Any],
415 ) -> None: ...
418# integer indexes into ResultColumnsEntry used by cursor.py.
419# some profiling showed integer access faster than named tuple
420RM_RENDERED_NAME: Literal[0] = 0
421RM_NAME: Literal[1] = 1
422RM_OBJECTS: Literal[2] = 2
423RM_TYPE: Literal[3] = 3
426class _BaseCompilerStackEntry(TypedDict):
427 asfrom_froms: Set[FromClause]
428 correlate_froms: Set[FromClause]
429 selectable: ReturnsRows
432class _CompilerStackEntry(_BaseCompilerStackEntry, total=False):
433 compile_state: CompileState
434 need_result_map_for_nested: bool
435 need_result_map_for_compound: bool
436 select_0: ReturnsRows
437 insert_from_select: Select[Unpack[TupleAny]]
440class ExpandedState(NamedTuple):
441 """represents state to use when producing "expanded" and
442 "post compile" bound parameters for a statement.
444 "expanded" parameters are parameters that are generated at
445 statement execution time to suit a number of parameters passed, the most
446 prominent example being the individual elements inside of an IN expression.
448 "post compile" parameters are parameters where the SQL literal value
449 will be rendered into the SQL statement at execution time, rather than
450 being passed as separate parameters to the driver.
452 To create an :class:`.ExpandedState` instance, use the
453 :meth:`.SQLCompiler.construct_expanded_state` method on any
454 :class:`.SQLCompiler` instance.
456 """
458 statement: str
459 """String SQL statement with parameters fully expanded"""
461 parameters: _CoreSingleExecuteParams
462 """Parameter dictionary with parameters fully expanded.
464 For a statement that uses named parameters, this dictionary will map
465 exactly to the names in the statement. For a statement that uses
466 positional parameters, the :attr:`.ExpandedState.positional_parameters`
467 will yield a tuple with the positional parameter set.
469 """
471 processors: Mapping[str, _BindProcessorType[Any]]
472 """mapping of bound value processors"""
474 positiontup: Optional[Sequence[str]]
475 """Sequence of string names indicating the order of positional
476 parameters"""
478 parameter_expansion: Mapping[str, List[str]]
479 """Mapping representing the intermediary link from original parameter
480 name to list of "expanded" parameter names, for those parameters that
481 were expanded."""
483 @property
484 def positional_parameters(self) -> Tuple[Any, ...]:
485 """Tuple of positional parameters, for statements that were compiled
486 using a positional paramstyle.
488 """
489 if self.positiontup is None:
490 raise exc.InvalidRequestError(
491 "statement does not use a positional paramstyle"
492 )
493 return tuple(self.parameters[key] for key in self.positiontup)
495 @property
496 def additional_parameters(self) -> _CoreSingleExecuteParams:
497 """synonym for :attr:`.ExpandedState.parameters`."""
498 return self.parameters
501class _InsertManyValues(NamedTuple):
502 """represents state to use for executing an "insertmanyvalues" statement.
504 The primary consumers of this object are the
505 :meth:`.SQLCompiler._deliver_insertmanyvalues_batches` and
506 :meth:`.DefaultDialect._deliver_insertmanyvalues_batches` methods.
508 .. versionadded:: 2.0
510 """
512 is_default_expr: bool
513 """if True, the statement is of the form
514 ``INSERT INTO TABLE DEFAULT VALUES``, and can't be rewritten as a "batch"
516 """
518 single_values_expr: str
519 """The rendered "values" clause of the INSERT statement.
521 This is typically the parenthesized section e.g. "(?, ?, ?)" or similar.
522 The insertmanyvalues logic uses this string as a search and replace
523 target.
525 """
527 insert_crud_params: List[crud._CrudParamElementStr]
528 """List of Column / bind names etc. used while rewriting the statement"""
530 num_positional_params_counted: int
531 """the number of bound parameters in a single-row statement.
533 This count may be larger or smaller than the actual number of columns
534 targeted in the INSERT, as it accommodates for SQL expressions
535 in the values list that may have zero or more parameters embedded
536 within them.
538 This count is part of what's used to organize rewritten parameter lists
539 when batching.
541 """
543 sort_by_parameter_order: bool = False
544 """if the deterministic_returnined_order parameter were used on the
545 insert.
547 All of the attributes following this will only be used if this is True.
549 """
551 includes_upsert_behaviors: bool = False
552 """if True, we have to accommodate for upsert behaviors.
554 This will in some cases downgrade "insertmanyvalues" that requests
555 deterministic ordering.
557 """
559 sentinel_columns: Optional[Sequence[Column[Any]]] = None
560 """List of sentinel columns that were located.
562 This list is only here if the INSERT asked for
563 sort_by_parameter_order=True,
564 and dialect-appropriate sentinel columns were located.
566 .. versionadded:: 2.0.10
568 """
570 num_sentinel_columns: int = 0
571 """how many sentinel columns are in the above list, if any.
573 This is the same as
574 ``len(sentinel_columns) if sentinel_columns is not None else 0``
576 """
578 sentinel_param_keys: Optional[Sequence[str]] = None
579 """parameter str keys in each param dictionary / tuple
580 that would link to the client side "sentinel" values for that row, which
581 we can use to match up parameter sets to result rows.
583 This is only present if sentinel_columns is present and the INSERT
584 statement actually refers to client side values for these sentinel
585 columns.
587 .. versionadded:: 2.0.10
589 .. versionchanged:: 2.0.29 - the sequence is now string dictionary keys
590 only, used against the "compiled parameteters" collection before
591 the parameters were converted by bound parameter processors
593 """
595 implicit_sentinel: bool = False
596 """if True, we have exactly one sentinel column and it uses a server side
597 value, currently has to generate an incrementing integer value.
599 The dialect in question would have asserted that it supports receiving
600 these values back and sorting on that value as a means of guaranteeing
601 correlation with the incoming parameter list.
603 .. versionadded:: 2.0.10
605 """
607 has_upsert_bound_parameters: bool = False
608 """if True, the upsert SET clause contains bound parameters that will
609 receive their values from the parameters dict (i.e., parametrized
610 bindparams where value is None and callable is None).
612 This means we can't batch multiple rows in a single statement, since
613 each row would need different values in the SET clause but there's only
614 one SET clause per statement. See issue #13130.
616 .. versionadded:: 2.0.37
618 """
620 embed_values_counter: bool = False
621 """Whether to embed an incrementing integer counter in each parameter
622 set within the VALUES clause as parameters are batched over.
624 This is only used for a specific INSERT..SELECT..VALUES..RETURNING syntax
625 where a subquery is used to produce value tuples. Current support
626 includes PostgreSQL, Microsoft SQL Server.
628 .. versionadded:: 2.0.10
630 """
633class _InsertManyValuesBatch(NamedTuple):
634 """represents an individual batch SQL statement for insertmanyvalues.
636 This is passed through the
637 :meth:`.SQLCompiler._deliver_insertmanyvalues_batches` and
638 :meth:`.DefaultDialect._deliver_insertmanyvalues_batches` methods out
639 to the :class:`.Connection` within the
640 :meth:`.Connection._exec_insertmany_context` method.
642 .. versionadded:: 2.0.10
644 """
646 replaced_statement: str
647 replaced_parameters: _DBAPIAnyExecuteParams
648 processed_setinputsizes: Optional[_GenericSetInputSizesType]
649 batch: Sequence[_DBAPISingleExecuteParams]
650 sentinel_values: Sequence[Tuple[Any, ...]]
651 current_batch_size: int
652 batchnum: int
653 total_batches: int
654 rows_sorted: bool
655 is_downgraded: bool
658class InsertmanyvaluesSentinelOpts(FastIntFlag):
659 """bitflag enum indicating styles of PK defaults
660 which can work as implicit sentinel columns
662 """
664 NOT_SUPPORTED = 1
665 AUTOINCREMENT = 2
666 IDENTITY = 4
667 SEQUENCE = 8
668 MONOTONIC_FUNCTION = 16
670 ANY_AUTOINCREMENT = (
671 AUTOINCREMENT | IDENTITY | SEQUENCE | MONOTONIC_FUNCTION
672 )
673 _SUPPORTED_OR_NOT = NOT_SUPPORTED | ANY_AUTOINCREMENT
675 USE_INSERT_FROM_SELECT = 32
676 RENDER_SELECT_COL_CASTS = 64
679class AggregateOrderByStyle(IntEnum):
680 """Describes backend database's capabilities with ORDER BY for aggregate
681 functions
683 .. versionadded:: 2.1
685 """
687 NONE = 0
688 """database has no ORDER BY for aggregate functions"""
690 INLINE = 1
691 """ORDER BY is rendered inside the function's argument list, typically as
692 the last element"""
694 WITHIN_GROUP = 2
695 """the WITHIN GROUP (ORDER BY ...) phrase is used for all aggregate
696 functions (not just the ordered set ones)"""
699class CompilerState(IntEnum):
700 COMPILING = 0
701 """statement is present, compilation phase in progress"""
703 STRING_APPLIED = 1
704 """statement is present, string form of the statement has been applied.
706 Additional processors by subclasses may still be pending.
708 """
710 NO_STATEMENT = 2
711 """compiler does not have a statement to compile, is used
712 for method access"""
715class Linting(IntEnum):
716 """represent preferences for the 'SQL linting' feature.
718 this feature currently includes support for flagging cartesian products
719 in SQL statements.
721 """
723 NO_LINTING = 0
724 "Disable all linting."
726 COLLECT_CARTESIAN_PRODUCTS = 1
727 """Collect data on FROMs and cartesian products and gather into
728 'self.from_linter'"""
730 WARN_LINTING = 2
731 "Emit warnings for linters that find problems"
733 FROM_LINTING = COLLECT_CARTESIAN_PRODUCTS | WARN_LINTING
734 """Warn for cartesian products; combines COLLECT_CARTESIAN_PRODUCTS
735 and WARN_LINTING"""
738NO_LINTING, COLLECT_CARTESIAN_PRODUCTS, WARN_LINTING, FROM_LINTING = tuple(
739 Linting
740)
743class FromLinter(collections.namedtuple("FromLinter", ["froms", "edges"])):
744 """represents current state for the "cartesian product" detection
745 feature."""
747 def lint(self, start=None):
748 froms = self.froms
749 if not froms:
750 return None, None
752 edges = set(self.edges)
753 the_rest = set(froms)
755 if start is not None:
756 start_with = start
757 the_rest.remove(start_with)
758 else:
759 start_with = the_rest.pop()
761 stack = collections.deque([start_with])
763 while stack and the_rest:
764 node = stack.popleft()
765 the_rest.discard(node)
767 # comparison of nodes in edges here is based on hash equality, as
768 # there are "annotated" elements that match the non-annotated ones.
769 # to remove the need for in-python hash() calls, use native
770 # containment routines (e.g. "node in edge", "edge.index(node)")
771 to_remove = {edge for edge in edges if node in edge}
773 # appendleft the node in each edge that is not
774 # the one that matched.
775 stack.extendleft(edge[not edge.index(node)] for edge in to_remove)
776 edges.difference_update(to_remove)
778 # FROMS left over? boom
779 if the_rest:
780 return the_rest, start_with
781 else:
782 return None, None
784 def warn(self, stmt_type="SELECT"):
785 the_rest, start_with = self.lint()
787 # FROMS left over? boom
788 if the_rest:
789 froms = the_rest
790 if froms:
791 template = (
792 "{stmt_type} statement has a cartesian product between "
793 "FROM element(s) {froms} and "
794 'FROM element "{start}". Apply join condition(s) '
795 "between each element to resolve."
796 )
797 froms_str = ", ".join(
798 f'"{self.froms[from_]}"' for from_ in froms
799 )
800 message = template.format(
801 stmt_type=stmt_type,
802 froms=froms_str,
803 start=self.froms[start_with],
804 )
806 util.warn(message)
809class Compiled:
810 """Represent a compiled SQL or DDL expression.
812 The ``__str__`` method of the ``Compiled`` object should produce
813 the actual text of the statement. ``Compiled`` objects are
814 specific to their underlying database dialect, and also may
815 or may not be specific to the columns referenced within a
816 particular set of bind parameters. In no case should the
817 ``Compiled`` object be dependent on the actual values of those
818 bind parameters, even though it may reference those values as
819 defaults.
820 """
822 statement: Optional[ClauseElement] = None
823 "The statement to compile."
824 string: str = ""
825 "The string representation of the ``statement``"
827 state: CompilerState
828 """description of the compiler's state"""
830 is_sql = False
831 is_ddl = False
833 _cached_metadata: Optional[CursorResultMetaData] = None
835 _result_columns: Optional[List[ResultColumnsEntry]] = None
837 schema_translate_map: Optional[SchemaTranslateMapType] = None
839 execution_options: _ExecuteOptions = util.EMPTY_DICT
840 """
841 Execution options propagated from the statement. In some cases,
842 sub-elements of the statement can modify these.
843 """
845 preparer: IdentifierPreparer
847 _annotations: _AnnotationDict = util.EMPTY_DICT
849 compile_state: Optional[CompileState] = None
850 """Optional :class:`.CompileState` object that maintains additional
851 state used by the compiler.
853 Major executable objects such as :class:`_expression.Insert`,
854 :class:`_expression.Update`, :class:`_expression.Delete`,
855 :class:`_expression.Select` will generate this
856 state when compiled in order to calculate additional information about the
857 object. For the top level object that is to be executed, the state can be
858 stored here where it can also have applicability towards result set
859 processing.
861 .. versionadded:: 1.4
863 """
865 dml_compile_state: Optional[CompileState] = None
866 """Optional :class:`.CompileState` assigned at the same point that
867 .isinsert, .isupdate, or .isdelete is assigned.
869 This will normally be the same object as .compile_state, with the
870 exception of cases like the :class:`.ORMFromStatementCompileState`
871 object.
873 .. versionadded:: 1.4.40
875 """
877 cache_key: Optional[CacheKey] = None
878 """The :class:`.CacheKey` that was generated ahead of creating this
879 :class:`.Compiled` object.
881 This is used for routines that need access to the original
882 :class:`.CacheKey` instance generated when the :class:`.Compiled`
883 instance was first cached, typically in order to reconcile
884 the original list of :class:`.BindParameter` objects with a
885 per-statement list that's generated on each call.
887 """
889 _gen_time: float
890 """Generation time of this :class:`.Compiled`, used for reporting
891 cache stats."""
893 def __init__(
894 self,
895 dialect: Dialect,
896 statement: Optional[ClauseElement],
897 schema_translate_map: Optional[SchemaTranslateMapType] = None,
898 render_schema_translate: bool = False,
899 compile_kwargs: Mapping[str, Any] = util.immutabledict(),
900 ):
901 """Construct a new :class:`.Compiled` object.
903 :param dialect: :class:`.Dialect` to compile against.
905 :param statement: :class:`_expression.ClauseElement` to be compiled.
907 :param schema_translate_map: dictionary of schema names to be
908 translated when forming the resultant SQL
910 .. seealso::
912 :ref:`schema_translating`
914 :param compile_kwargs: additional kwargs that will be
915 passed to the initial call to :meth:`.Compiled.process`.
918 """
919 self.dialect = dialect
920 self.preparer = self.dialect.identifier_preparer
921 if schema_translate_map:
922 self.schema_translate_map = schema_translate_map
923 self.preparer = self.preparer._with_schema_translate(
924 schema_translate_map
925 )
927 if statement is not None:
928 self.state = CompilerState.COMPILING
929 self.statement = statement
930 self.can_execute = statement.supports_execution
931 self._annotations = statement._annotations
932 if self.can_execute:
933 if TYPE_CHECKING:
934 assert isinstance(statement, Executable)
935 self.execution_options = statement._execution_options
936 self.string = self.process(self.statement, **compile_kwargs)
938 if render_schema_translate:
939 assert schema_translate_map is not None
940 self.string = self.preparer._render_schema_translates(
941 self.string, schema_translate_map
942 )
944 self.state = CompilerState.STRING_APPLIED
945 else:
946 self.state = CompilerState.NO_STATEMENT
948 self._gen_time = perf_counter()
950 def __init_subclass__(cls) -> None:
951 cls._init_compiler_cls()
952 return super().__init_subclass__()
954 @classmethod
955 def _init_compiler_cls(cls):
956 pass
958 def visit_unsupported_compilation(self, element, err, **kw):
959 raise exc.UnsupportedCompilationError(self, type(element)) from err
961 @property
962 def sql_compiler(self) -> SQLCompiler:
963 """Return a Compiled that is capable of processing SQL expressions.
965 If this compiler is one, it would likely just return 'self'.
967 """
969 raise NotImplementedError()
971 def process(self, obj: Visitable, **kwargs: Any) -> str:
972 return obj._compiler_dispatch(self, **kwargs)
974 def __str__(self) -> str:
975 """Return the string text of the generated SQL or DDL."""
977 if self.state is CompilerState.STRING_APPLIED:
978 return self.string
979 else:
980 return ""
982 def construct_params(
983 self,
984 params: Optional[_CoreSingleExecuteParams] = None,
985 extracted_parameters: Optional[Sequence[BindParameter[Any]]] = None,
986 escape_names: bool = True,
987 ) -> Optional[_MutableCoreSingleExecuteParams]:
988 """Return the bind params for this compiled object.
990 :param params: a dict of string/object pairs whose values will
991 override bind values compiled in to the
992 statement.
993 """
995 raise NotImplementedError()
997 @property
998 def params(self):
999 """Return the bind params for this compiled object."""
1000 return self.construct_params()
1003class TypeCompiler(util.EnsureKWArg):
1004 """Produces DDL specification for TypeEngine objects."""
1006 ensure_kwarg = r"visit_\w+"
1008 def __init__(self, dialect: Dialect):
1009 self.dialect = dialect
1011 def process(self, type_: TypeEngine[Any], **kw: Any) -> str:
1012 if (
1013 type_._variant_mapping
1014 and self.dialect.name in type_._variant_mapping
1015 ):
1016 type_ = type_._variant_mapping[self.dialect.name]
1017 return type_._compiler_dispatch(self, **kw)
1019 def visit_unsupported_compilation(
1020 self, element: Any, err: Exception, **kw: Any
1021 ) -> NoReturn:
1022 raise exc.UnsupportedCompilationError(self, element) from err
1025# this was a Visitable, but to allow accurate detection of
1026# column elements this is actually a column element
1027class _CompileLabel(
1028 roles.BinaryElementRole[Any], elements.CompilerColumnElement
1029):
1030 """lightweight label object which acts as an expression.Label."""
1032 __visit_name__ = "label"
1033 __slots__ = "element", "name", "_alt_names"
1035 def __init__(self, col, name, alt_names=()):
1036 self.element = col
1037 self.name = name
1038 self._alt_names = (col,) + alt_names
1040 @property
1041 def proxy_set(self):
1042 return self.element.proxy_set
1044 @property
1045 def type(self):
1046 return self.element.type
1048 def self_group(self, **kw):
1049 return self
1052class aggregate_orderby_inline(
1053 roles.BinaryElementRole[Any], elements.CompilerColumnElement
1054):
1055 """produce ORDER BY inside of function argument lists"""
1057 __visit_name__ = "aggregate_orderby_inline"
1058 __slots__ = "element", "aggregate_order_by"
1060 def __init__(self, element, orderby):
1061 self.element = element
1062 self.aggregate_order_by = orderby
1064 def __iter__(self):
1065 return iter(self.element)
1067 @property
1068 def proxy_set(self):
1069 return self.element.proxy_set
1071 @property
1072 def type(self):
1073 return self.element.type
1075 def self_group(self, **kw):
1076 return self
1078 def _with_binary_element_type(self, type_):
1079 return aggregate_orderby_inline(
1080 self.element._with_binary_element_type(type_),
1081 self.aggregate_order_by,
1082 )
1085class ilike_case_insensitive(
1086 roles.BinaryElementRole[Any], elements.CompilerColumnElement
1087):
1088 """produce a wrapping element for a case-insensitive portion of
1089 an ILIKE construct.
1091 The construct usually renders the ``lower()`` function, but on
1092 PostgreSQL will pass silently with the assumption that "ILIKE"
1093 is being used.
1095 .. versionadded:: 2.0
1097 """
1099 __visit_name__ = "ilike_case_insensitive_operand"
1100 __slots__ = "element", "comparator"
1102 def __init__(self, element):
1103 self.element = element
1104 self.comparator = element.comparator
1106 @property
1107 def proxy_set(self):
1108 return self.element.proxy_set
1110 @property
1111 def type(self):
1112 return self.element.type
1114 def self_group(self, **kw):
1115 return self
1117 def _with_binary_element_type(self, type_):
1118 return ilike_case_insensitive(
1119 self.element._with_binary_element_type(type_)
1120 )
1123class SQLCompiler(Compiled):
1124 """Default implementation of :class:`.Compiled`.
1126 Compiles :class:`_expression.ClauseElement` objects into SQL strings.
1128 """
1130 extract_map = EXTRACT_MAP
1132 bindname_escape_characters: ClassVar[Mapping[str, str]] = (
1133 util.immutabledict(
1134 {
1135 "%": "P",
1136 "(": "A",
1137 ")": "Z",
1138 ":": "C",
1139 ".": "_",
1140 "[": "_",
1141 "]": "_",
1142 " ": "_",
1143 }
1144 )
1145 )
1146 """A mapping (e.g. dict or similar) containing a lookup of
1147 characters keyed to replacement characters which will be applied to all
1148 'bind names' used in SQL statements as a form of 'escaping'; the given
1149 characters are replaced entirely with the 'replacement' character when
1150 rendered in the SQL statement, and a similar translation is performed
1151 on the incoming names used in parameter dictionaries passed to methods
1152 like :meth:`_engine.Connection.execute`.
1154 This allows bound parameter names used in :func:`_sql.bindparam` and
1155 other constructs to have any arbitrary characters present without any
1156 concern for characters that aren't allowed at all on the target database.
1158 Third party dialects can establish their own dictionary here to replace the
1159 default mapping, which will ensure that the particular characters in the
1160 mapping will never appear in a bound parameter name.
1162 The dictionary is evaluated at **class creation time**, so cannot be
1163 modified at runtime; it must be present on the class when the class
1164 is first declared.
1166 Note that for dialects that have additional bound parameter rules such
1167 as additional restrictions on leading characters, the
1168 :meth:`_sql.SQLCompiler.bindparam_string` method may need to be augmented.
1169 See the cx_Oracle compiler for an example of this.
1171 .. versionadded:: 2.0.0rc1
1173 """
1175 _bind_translate_re: ClassVar[Pattern[str]]
1176 _bind_translate_chars: ClassVar[Mapping[str, str]]
1178 is_sql = True
1180 compound_keywords = COMPOUND_KEYWORDS
1182 isdelete: bool = False
1183 isinsert: bool = False
1184 isupdate: bool = False
1185 """class-level defaults which can be set at the instance
1186 level to define if this Compiled instance represents
1187 INSERT/UPDATE/DELETE
1188 """
1190 postfetch: Optional[List[Column[Any]]]
1191 """list of columns that can be post-fetched after INSERT or UPDATE to
1192 receive server-updated values"""
1194 insert_prefetch: Sequence[Column[Any]] = ()
1195 """list of columns for which default values should be evaluated before
1196 an INSERT takes place"""
1198 update_prefetch: Sequence[Column[Any]] = ()
1199 """list of columns for which onupdate default values should be evaluated
1200 before an UPDATE takes place"""
1202 implicit_returning: Optional[Sequence[ColumnElement[Any]]] = None
1203 """list of "implicit" returning columns for a toplevel INSERT or UPDATE
1204 statement, used to receive newly generated values of columns.
1206 .. versionadded:: 2.0 ``implicit_returning`` replaces the previous
1207 ``returning`` collection, which was not a generalized RETURNING
1208 collection and instead was in fact specific to the "implicit returning"
1209 feature.
1211 """
1213 isplaintext: bool = False
1215 binds: Dict[str, BindParameter[Any]]
1216 """a dictionary of bind parameter keys to BindParameter instances."""
1218 bind_names: Dict[BindParameter[Any], str]
1219 """a dictionary of BindParameter instances to "compiled" names
1220 that are actually present in the generated SQL"""
1222 stack: List[_CompilerStackEntry]
1223 """major statements such as SELECT, INSERT, UPDATE, DELETE are
1224 tracked in this stack using an entry format."""
1226 returning_precedes_values: bool = False
1227 """set to True classwide to generate RETURNING
1228 clauses before the VALUES or WHERE clause (i.e. MSSQL)
1229 """
1231 render_table_with_column_in_update_from: bool = False
1232 """set to True classwide to indicate the SET clause
1233 in a multi-table UPDATE statement should qualify
1234 columns with the table name (i.e. MySQL only)
1235 """
1237 ansi_bind_rules: bool = False
1238 """SQL 92 doesn't allow bind parameters to be used
1239 in the columns clause of a SELECT, nor does it allow
1240 ambiguous expressions like "? = ?". A compiler
1241 subclass can set this flag to False if the target
1242 driver/DB enforces this
1243 """
1245 bindtemplate: str
1246 """template to render bound parameters based on paramstyle."""
1248 compilation_bindtemplate: str
1249 """template used by compiler to render parameters before positional
1250 paramstyle application"""
1252 _numeric_binds_identifier_char: str
1253 """Character that's used to as the identifier of a numerical bind param.
1254 For example if this char is set to ``$``, numerical binds will be rendered
1255 in the form ``$1, $2, $3``.
1256 """
1258 _result_columns: List[ResultColumnsEntry]
1259 """relates label names in the final SQL to a tuple of local
1260 column/label name, ColumnElement object (if any) and
1261 TypeEngine. CursorResult uses this for type processing and
1262 column targeting"""
1264 _textual_ordered_columns: bool = False
1265 """tell the result object that the column names as rendered are important,
1266 but they are also "ordered" vs. what is in the compiled object here.
1268 As of 1.4.42 this condition is only present when the statement is a
1269 TextualSelect, e.g. text("....").columns(...), where it is required
1270 that the columns are considered positionally and not by name.
1272 """
1274 _ad_hoc_textual: bool = False
1275 """tell the result that we encountered text() or '*' constructs in the
1276 middle of the result columns, but we also have compiled columns, so
1277 if the number of columns in cursor.description does not match how many
1278 expressions we have, that means we can't rely on positional at all and
1279 should match on name.
1281 """
1283 _ordered_columns: bool = True
1284 """
1285 if False, means we can't be sure the list of entries
1286 in _result_columns is actually the rendered order. Usually
1287 True unless using an unordered TextualSelect.
1288 """
1290 _loose_column_name_matching: bool = False
1291 """tell the result object that the SQL statement is textual, wants to match
1292 up to Column objects, and may be using the ._tq_label in the SELECT rather
1293 than the base name.
1295 """
1297 _numeric_binds: bool = False
1298 """
1299 True if paramstyle is "numeric". This paramstyle is trickier than
1300 all the others.
1302 """
1304 _render_postcompile: bool = False
1305 """
1306 whether to render out POSTCOMPILE params during the compile phase.
1308 This attribute is used only for end-user invocation of stmt.compile();
1309 it's never used for actual statement execution, where instead the
1310 dialect internals access and render the internal postcompile structure
1311 directly.
1313 """
1315 _post_compile_expanded_state: Optional[ExpandedState] = None
1316 """When render_postcompile is used, the ``ExpandedState`` used to create
1317 the "expanded" SQL is assigned here, and then used by the ``.params``
1318 accessor and ``.construct_params()`` methods for their return values.
1320 .. versionadded:: 2.0.0rc1
1322 """
1324 _pre_expanded_string: Optional[str] = None
1325 """Stores the original string SQL before 'post_compile' is applied,
1326 for cases where 'post_compile' were used.
1328 """
1330 _pre_expanded_positiontup: Optional[List[str]] = None
1332 _insertmanyvalues: Optional[_InsertManyValues] = None
1334 _insert_crud_params: Optional[crud._CrudParamSequence] = None
1336 literal_execute_params: FrozenSet[BindParameter[Any]] = frozenset()
1337 """bindparameter objects that are rendered as literal values at statement
1338 execution time.
1340 """
1342 post_compile_params: FrozenSet[BindParameter[Any]] = frozenset()
1343 """bindparameter objects that are rendered as bound parameter placeholders
1344 at statement execution time.
1346 """
1348 escaped_bind_names: util.immutabledict[str, str] = util.EMPTY_DICT
1349 """Late escaping of bound parameter names that has to be converted
1350 to the original name when looking in the parameter dictionary.
1352 """
1354 has_out_parameters = False
1355 """if True, there are bindparam() objects that have the isoutparam
1356 flag set."""
1358 postfetch_lastrowid = False
1359 """if True, and this in insert, use cursor.lastrowid to populate
1360 result.inserted_primary_key. """
1362 _cache_key_bind_match: Optional[
1363 Tuple[
1364 Dict[
1365 BindParameter[Any],
1366 List[BindParameter[Any]],
1367 ],
1368 Dict[
1369 str,
1370 BindParameter[Any],
1371 ],
1372 ]
1373 ] = None
1374 """a mapping that will relate the BindParameter object we compile
1375 to those that are part of the extracted collection of parameters
1376 in the cache key, if we were given a cache key.
1378 """
1380 positiontup: Optional[List[str]] = None
1381 """for a compiled construct that uses a positional paramstyle, will be
1382 a sequence of strings, indicating the names of bound parameters in order.
1384 This is used in order to render bound parameters in their correct order,
1385 and is combined with the :attr:`_sql.Compiled.params` dictionary to
1386 render parameters.
1388 This sequence always contains the unescaped name of the parameters.
1390 .. seealso::
1392 :ref:`faq_sql_expression_string` - includes a usage example for
1393 debugging use cases.
1395 """
1396 _values_bindparam: Optional[List[str]] = None
1398 _visited_bindparam: Optional[List[str]] = None
1400 inline: bool = False
1402 ctes: Optional[MutableMapping[CTE, str]]
1404 # Detect same CTE references - Dict[(level, name), cte]
1405 # Level is required for supporting nesting
1406 ctes_by_level_name: Dict[Tuple[int, str], CTE]
1408 # To retrieve key/level in ctes_by_level_name -
1409 # Dict[cte_reference, (level, cte_name, cte_opts)]
1410 level_name_by_cte: Dict[CTE, Tuple[int, str, selectable._CTEOpts]]
1412 ctes_recursive: bool
1414 _post_compile_pattern = re.compile(r"__\[POSTCOMPILE_(\S+?)(~~.+?~~)?\]")
1415 _pyformat_pattern = re.compile(r"%\(([^)]+?)\)s")
1416 _positional_pattern = re.compile(
1417 f"{_pyformat_pattern.pattern}|{_post_compile_pattern.pattern}"
1418 )
1419 _collect_params: Final[bool]
1420 _collected_params: util.immutabledict[str, Any]
1422 @classmethod
1423 def _init_compiler_cls(cls):
1424 cls._init_bind_translate()
1426 @classmethod
1427 def _init_bind_translate(cls):
1428 reg = re.escape("".join(cls.bindname_escape_characters))
1429 cls._bind_translate_re = re.compile(f"[{reg}]")
1430 cls._bind_translate_chars = cls.bindname_escape_characters
1432 def __init__(
1433 self,
1434 dialect: Dialect,
1435 statement: Optional[ClauseElement],
1436 cache_key: Optional[CacheKey] = None,
1437 column_keys: Optional[Sequence[str]] = None,
1438 for_executemany: bool = False,
1439 linting: Linting = NO_LINTING,
1440 _supporting_against: Optional[SQLCompiler] = None,
1441 **kwargs: Any,
1442 ):
1443 """Construct a new :class:`.SQLCompiler` object.
1445 :param dialect: :class:`.Dialect` to be used
1447 :param statement: :class:`_expression.ClauseElement` to be compiled
1449 :param column_keys: a list of column names to be compiled into an
1450 INSERT or UPDATE statement.
1452 :param for_executemany: whether INSERT / UPDATE statements should
1453 expect that they are to be invoked in an "executemany" style,
1454 which may impact how the statement will be expected to return the
1455 values of defaults and autoincrement / sequences and similar.
1456 Depending on the backend and driver in use, support for retrieving
1457 these values may be disabled which means SQL expressions may
1458 be rendered inline, RETURNING may not be rendered, etc.
1460 :param kwargs: additional keyword arguments to be consumed by the
1461 superclass.
1463 """
1464 self.column_keys = column_keys
1466 self.cache_key = cache_key
1468 if cache_key:
1469 cksm = {b.key: b for b in cache_key[1]}
1470 ckbm = {b: [b] for b in cache_key[1]}
1471 self._cache_key_bind_match = (ckbm, cksm)
1473 # compile INSERT/UPDATE defaults/sequences to expect executemany
1474 # style execution, which may mean no pre-execute of defaults,
1475 # or no RETURNING
1476 self.for_executemany = for_executemany
1478 self.linting = linting
1480 # a dictionary of bind parameter keys to BindParameter
1481 # instances.
1482 self.binds = {}
1484 # a dictionary of BindParameter instances to "compiled" names
1485 # that are actually present in the generated SQL
1486 self.bind_names = util.column_dict()
1488 # stack which keeps track of nested SELECT statements
1489 self.stack = []
1491 self._result_columns = []
1493 # true if the paramstyle is positional
1494 self.positional = dialect.positional
1495 if self.positional:
1496 self._numeric_binds = nb = dialect.paramstyle.startswith("numeric")
1497 if nb:
1498 self._numeric_binds_identifier_char = (
1499 "$" if dialect.paramstyle == "numeric_dollar" else ":"
1500 )
1502 self.compilation_bindtemplate = _pyformat_template
1503 else:
1504 self.compilation_bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
1506 self.ctes = None
1508 self.label_length = (
1509 dialect.label_length or dialect.max_identifier_length
1510 )
1512 # a map which tracks "anonymous" identifiers that are created on
1513 # the fly here
1514 self.anon_map = prefix_anon_map()
1516 # a map which tracks "truncated" names based on
1517 # dialect.label_length or dialect.max_identifier_length
1518 self.truncated_names: Dict[Tuple[str, str], str] = {}
1519 self._truncated_counters: Dict[str, int] = {}
1520 if not cache_key:
1521 self._collect_params = True
1522 self._collected_params = util.EMPTY_DICT
1523 else:
1524 self._collect_params = False # type: ignore[misc]
1526 Compiled.__init__(self, dialect, statement, **kwargs)
1528 if self.isinsert or self.isupdate or self.isdelete:
1529 if TYPE_CHECKING:
1530 assert isinstance(statement, UpdateBase)
1532 if self.isinsert or self.isupdate:
1533 if TYPE_CHECKING:
1534 assert isinstance(statement, ValuesBase)
1535 if statement._inline:
1536 self.inline = True
1537 elif self.for_executemany and (
1538 not self.isinsert
1539 or (
1540 self.dialect.insert_executemany_returning
1541 and statement._return_defaults
1542 )
1543 ):
1544 self.inline = True
1546 self.bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
1548 if _supporting_against:
1549 self.__dict__.update(
1550 {
1551 k: v
1552 for k, v in _supporting_against.__dict__.items()
1553 if k
1554 not in {
1555 "state",
1556 "dialect",
1557 "preparer",
1558 "positional",
1559 "_numeric_binds",
1560 "compilation_bindtemplate",
1561 "bindtemplate",
1562 }
1563 }
1564 )
1566 if self.state is CompilerState.STRING_APPLIED:
1567 if self.positional:
1568 if self._numeric_binds:
1569 self._process_numeric()
1570 else:
1571 self._process_positional()
1573 if self._render_postcompile:
1574 parameters = self.construct_params(
1575 escape_names=False,
1576 _no_postcompile=True,
1577 )
1579 self._process_parameters_for_postcompile(
1580 parameters, _populate_self=True
1581 )
1583 @property
1584 def insert_single_values_expr(self) -> Optional[str]:
1585 """When an INSERT is compiled with a single set of parameters inside
1586 a VALUES expression, the string is assigned here, where it can be
1587 used for insert batching schemes to rewrite the VALUES expression.
1589 .. versionchanged:: 2.0 This collection is no longer used by
1590 SQLAlchemy's built-in dialects, in favor of the currently
1591 internal ``_insertmanyvalues`` collection that is used only by
1592 :class:`.SQLCompiler`.
1594 """
1595 if self._insertmanyvalues is None:
1596 return None
1597 else:
1598 return self._insertmanyvalues.single_values_expr
1600 @util.ro_memoized_property
1601 def effective_returning(self) -> Optional[Sequence[ColumnElement[Any]]]:
1602 """The effective "returning" columns for INSERT, UPDATE or DELETE.
1604 This is either the so-called "implicit returning" columns which are
1605 calculated by the compiler on the fly, or those present based on what's
1606 present in ``self.statement._returning`` (expanded into individual
1607 columns using the ``._all_selected_columns`` attribute) i.e. those set
1608 explicitly using the :meth:`.UpdateBase.returning` method.
1610 .. versionadded:: 2.0
1612 """
1613 if self.implicit_returning:
1614 return self.implicit_returning
1615 elif self.statement is not None and is_dml(self.statement):
1616 return [
1617 c
1618 for c in self.statement._all_selected_columns
1619 if is_column_element(c)
1620 ]
1622 else:
1623 return None
1625 @property
1626 def returning(self):
1627 """backwards compatibility; returns the
1628 effective_returning collection.
1630 """
1631 return self.effective_returning
1633 @property
1634 def current_executable(self):
1635 """Return the current 'executable' that is being compiled.
1637 This is currently the :class:`_sql.Select`, :class:`_sql.Insert`,
1638 :class:`_sql.Update`, :class:`_sql.Delete`,
1639 :class:`_sql.CompoundSelect` object that is being compiled.
1640 Specifically it's assigned to the ``self.stack`` list of elements.
1642 When a statement like the above is being compiled, it normally
1643 is also assigned to the ``.statement`` attribute of the
1644 :class:`_sql.Compiler` object. However, all SQL constructs are
1645 ultimately nestable, and this attribute should never be consulted
1646 by a ``visit_`` method, as it is not guaranteed to be assigned
1647 nor guaranteed to correspond to the current statement being compiled.
1649 """
1650 try:
1651 return self.stack[-1]["selectable"]
1652 except IndexError as ie:
1653 raise IndexError("Compiler does not have a stack entry") from ie
1655 @property
1656 def prefetch(self):
1657 return list(self.insert_prefetch) + list(self.update_prefetch)
1659 @util.memoized_property
1660 def _global_attributes(self) -> Dict[Any, Any]:
1661 return {}
1663 def _add_to_params(self, item: ExecutableStatement) -> None:
1664 # assumes that this is called before traversing the statement
1665 # so the call happens outer to inner, meaning that existing params
1666 # take precedence
1667 if item._params:
1668 self._collected_params = item._params | self._collected_params
1670 @util.memoized_instancemethod
1671 def _init_cte_state(self) -> MutableMapping[CTE, str]:
1672 """Initialize collections related to CTEs only if
1673 a CTE is located, to save on the overhead of
1674 these collections otherwise.
1676 """
1677 # collect CTEs to tack on top of a SELECT
1678 # To store the query to print - Dict[cte, text_query]
1679 ctes: MutableMapping[CTE, str] = util.OrderedDict()
1680 self.ctes = ctes
1682 # Detect same CTE references - Dict[(level, name), cte]
1683 # Level is required for supporting nesting
1684 self.ctes_by_level_name = {}
1686 # To retrieve key/level in ctes_by_level_name -
1687 # Dict[cte_reference, (level, cte_name, cte_opts)]
1688 self.level_name_by_cte = {}
1690 self.ctes_recursive = False
1692 return ctes
1694 @contextlib.contextmanager
1695 def _nested_result(self):
1696 """special API to support the use case of 'nested result sets'"""
1697 result_columns, ordered_columns = (
1698 self._result_columns,
1699 self._ordered_columns,
1700 )
1701 self._result_columns, self._ordered_columns = [], False
1703 try:
1704 if self.stack:
1705 entry = self.stack[-1]
1706 entry["need_result_map_for_nested"] = True
1707 else:
1708 entry = None
1709 yield self._result_columns, self._ordered_columns
1710 finally:
1711 if entry:
1712 entry.pop("need_result_map_for_nested")
1713 self._result_columns, self._ordered_columns = (
1714 result_columns,
1715 ordered_columns,
1716 )
1718 def _process_positional(self):
1719 assert not self.positiontup
1720 assert self.state is CompilerState.STRING_APPLIED
1721 assert not self._numeric_binds
1723 if self.dialect.paramstyle == "format":
1724 placeholder = "%s"
1725 else:
1726 assert self.dialect.paramstyle == "qmark"
1727 placeholder = "?"
1729 positions = []
1731 def find_position(m: re.Match[str]) -> str:
1732 normal_bind = m.group(1)
1733 if normal_bind:
1734 positions.append(normal_bind)
1735 return placeholder
1736 else:
1737 # this a post-compile bind
1738 positions.append(m.group(2))
1739 return m.group(0)
1741 self.string = re.sub(
1742 self._positional_pattern, find_position, self.string
1743 )
1745 if self.escaped_bind_names:
1746 reverse_escape = {v: k for k, v in self.escaped_bind_names.items()}
1747 assert len(self.escaped_bind_names) == len(reverse_escape)
1748 self.positiontup = [
1749 reverse_escape.get(name, name) for name in positions
1750 ]
1751 else:
1752 self.positiontup = positions
1754 if self._insertmanyvalues:
1755 positions = []
1757 single_values_expr = re.sub(
1758 self._positional_pattern,
1759 find_position,
1760 self._insertmanyvalues.single_values_expr,
1761 )
1762 insert_crud_params = [
1763 (
1764 v[0],
1765 v[1],
1766 re.sub(self._positional_pattern, find_position, v[2]),
1767 v[3],
1768 )
1769 for v in self._insertmanyvalues.insert_crud_params
1770 ]
1772 self._insertmanyvalues = self._insertmanyvalues._replace(
1773 single_values_expr=single_values_expr,
1774 insert_crud_params=insert_crud_params,
1775 )
1777 def _process_numeric(self):
1778 assert self._numeric_binds
1779 assert self.state is CompilerState.STRING_APPLIED
1781 num = 1
1782 param_pos: Dict[str, str] = {}
1783 order: Iterable[str]
1784 if self._insertmanyvalues and self._values_bindparam is not None:
1785 # bindparams that are not in values are always placed first.
1786 # this avoids the need of changing them when using executemany
1787 # values () ()
1788 order = itertools.chain(
1789 (
1790 name
1791 for name in self.bind_names.values()
1792 if name not in self._values_bindparam
1793 ),
1794 self.bind_names.values(),
1795 )
1796 else:
1797 order = self.bind_names.values()
1799 for bind_name in order:
1800 if bind_name in param_pos:
1801 continue
1802 bind = self.binds[bind_name]
1803 if (
1804 bind in self.post_compile_params
1805 or bind in self.literal_execute_params
1806 ):
1807 # set to None to just mark the in positiontup, it will not
1808 # be replaced below.
1809 param_pos[bind_name] = None # type: ignore[assignment]
1810 else:
1811 ph = f"{self._numeric_binds_identifier_char}{num}"
1812 num += 1
1813 param_pos[bind_name] = ph
1815 self.next_numeric_pos = num
1817 self.positiontup = list(param_pos)
1818 if self.escaped_bind_names:
1819 len_before = len(param_pos)
1820 param_pos = {
1821 self.escaped_bind_names.get(name, name): pos
1822 for name, pos in param_pos.items()
1823 }
1824 assert len(param_pos) == len_before
1826 # Can't use format here since % chars are not escaped.
1827 self.string = self._pyformat_pattern.sub(
1828 lambda m: param_pos[m.group(1)], self.string
1829 )
1831 if self._insertmanyvalues:
1832 single_values_expr = (
1833 # format is ok here since single_values_expr includes only
1834 # place-holders
1835 self._insertmanyvalues.single_values_expr
1836 % param_pos
1837 )
1838 insert_crud_params = [
1839 (v[0], v[1], "%s", v[3])
1840 for v in self._insertmanyvalues.insert_crud_params
1841 ]
1843 self._insertmanyvalues = self._insertmanyvalues._replace(
1844 # This has the numbers (:1, :2)
1845 single_values_expr=single_values_expr,
1846 # The single binds are instead %s so they can be formatted
1847 insert_crud_params=insert_crud_params,
1848 )
1850 @util.memoized_property
1851 def _bind_processors(
1852 self,
1853 ) -> MutableMapping[
1854 str, Union[_BindProcessorType[Any], Sequence[_BindProcessorType[Any]]]
1855 ]:
1856 # mypy is not able to see the two value types as the above Union,
1857 # it just sees "object". don't know how to resolve
1858 return {
1859 key: value # type: ignore[misc]
1860 for key, value in (
1861 (
1862 self.bind_names[bindparam],
1863 (
1864 bindparam.type._cached_bind_processor(self.dialect)
1865 if not bindparam.type._is_tuple_type
1866 else tuple(
1867 elem_type._cached_bind_processor(self.dialect)
1868 for elem_type in cast(
1869 TupleType, bindparam.type
1870 ).types
1871 )
1872 ),
1873 )
1874 for bindparam in self.bind_names
1875 # literal_execute parameters are rendered into the SQL string
1876 # via their literal_processor and never bound as values, so
1877 # they do not need a bind processor. Skipping them also avoids
1878 # invoking bind-processor construction that may require the
1879 # DBAPI to be present (see asyncpg, psycopgcffi cases),
1880 # facilitating testing.
1881 if bindparam not in self.literal_execute_params
1882 )
1883 if value is not None
1884 }
1886 def is_subquery(self):
1887 return len(self.stack) > 1
1889 @property
1890 def sql_compiler(self) -> Self:
1891 return self
1893 def construct_expanded_state(
1894 self,
1895 params: Optional[_CoreSingleExecuteParams] = None,
1896 escape_names: bool = True,
1897 ) -> ExpandedState:
1898 """Return a new :class:`.ExpandedState` for a given parameter set.
1900 For queries that use "expanding" or other late-rendered parameters,
1901 this method will provide for both the finalized SQL string as well
1902 as the parameters that would be used for a particular parameter set.
1904 .. versionadded:: 2.0.0rc1
1906 """
1907 parameters = self.construct_params(
1908 params,
1909 escape_names=escape_names,
1910 _no_postcompile=True,
1911 )
1912 return self._process_parameters_for_postcompile(
1913 parameters,
1914 )
1916 def construct_params(
1917 self,
1918 params: Optional[_CoreSingleExecuteParams] = None,
1919 extracted_parameters: Optional[Sequence[BindParameter[Any]]] = None,
1920 escape_names: bool = True,
1921 _group_number: Optional[int] = None,
1922 _check: bool = True,
1923 _no_postcompile: bool = False,
1924 _collected_params: _CoreSingleExecuteParams | None = None,
1925 ) -> _MutableCoreSingleExecuteParams:
1926 """return a dictionary of bind parameter keys and values"""
1927 if _collected_params is not None:
1928 assert not self._collect_params
1929 elif self._collect_params:
1930 _collected_params = self._collected_params
1932 if _collected_params:
1933 if not params:
1934 params = _collected_params
1935 else:
1936 params = {**_collected_params, **params}
1938 if self._render_postcompile and not _no_postcompile:
1939 assert self._post_compile_expanded_state is not None
1940 if not params:
1941 return dict(self._post_compile_expanded_state.parameters)
1942 else:
1943 raise exc.InvalidRequestError(
1944 "can't construct new parameters when render_postcompile "
1945 "is used; the statement is hard-linked to the original "
1946 "parameters. Use construct_expanded_state to generate a "
1947 "new statement and parameters."
1948 )
1950 has_escaped_names = escape_names and bool(self.escaped_bind_names)
1952 if extracted_parameters:
1953 # related the bound parameters collected in the original cache key
1954 # to those collected in the incoming cache key. They will not have
1955 # matching names but they will line up positionally in the same
1956 # way. The parameters present in self.bind_names may be clones of
1957 # these original cache key params in the case of DML but the .key
1958 # will be guaranteed to match.
1959 if self.cache_key is None:
1960 raise exc.CompileError(
1961 "This compiled object has no original cache key; "
1962 "can't pass extracted_parameters to construct_params"
1963 )
1964 else:
1965 orig_extracted = self.cache_key[1]
1967 ckbm_tuple = self._cache_key_bind_match
1968 assert ckbm_tuple is not None
1969 ckbm, _ = ckbm_tuple
1970 resolved_extracted = {
1971 bind: extracted
1972 for b, extracted in zip(orig_extracted, extracted_parameters)
1973 for bind in ckbm[b]
1974 }
1975 else:
1976 resolved_extracted = None
1978 if params:
1979 pd = {}
1980 for bindparam, name in self.bind_names.items():
1981 escaped_name = (
1982 self.escaped_bind_names.get(name, name)
1983 if has_escaped_names
1984 else name
1985 )
1987 if bindparam.key in params:
1988 pd[escaped_name] = params[bindparam.key]
1989 elif name in params:
1990 pd[escaped_name] = params[name]
1992 elif _check and bindparam.required:
1993 if _group_number:
1994 raise exc.InvalidRequestError(
1995 "A value is required for bind parameter %r, "
1996 "in parameter group %d"
1997 % (bindparam.key, _group_number),
1998 code="cd3x",
1999 )
2000 else:
2001 raise exc.InvalidRequestError(
2002 "A value is required for bind parameter %r"
2003 % bindparam.key,
2004 code="cd3x",
2005 )
2006 else:
2007 if resolved_extracted:
2008 value_param = resolved_extracted.get(
2009 bindparam, bindparam
2010 )
2011 else:
2012 value_param = bindparam
2014 if bindparam.callable:
2015 pd[escaped_name] = value_param.effective_value
2016 else:
2017 pd[escaped_name] = value_param.value
2018 return pd
2019 else:
2020 pd = {}
2021 for bindparam, name in self.bind_names.items():
2022 escaped_name = (
2023 self.escaped_bind_names.get(name, name)
2024 if has_escaped_names
2025 else name
2026 )
2028 if _check and bindparam.required:
2029 if _group_number:
2030 raise exc.InvalidRequestError(
2031 "A value is required for bind parameter %r, "
2032 "in parameter group %d"
2033 % (bindparam.key, _group_number),
2034 code="cd3x",
2035 )
2036 else:
2037 raise exc.InvalidRequestError(
2038 "A value is required for bind parameter %r"
2039 % bindparam.key,
2040 code="cd3x",
2041 )
2043 if resolved_extracted:
2044 value_param = resolved_extracted.get(bindparam, bindparam)
2045 else:
2046 value_param = bindparam
2048 if bindparam.callable:
2049 pd[escaped_name] = value_param.effective_value
2050 else:
2051 pd[escaped_name] = value_param.value
2053 return pd
2055 @util.memoized_instancemethod
2056 def _get_set_input_sizes_lookup(self):
2057 dialect = self.dialect
2059 include_types = dialect.include_set_input_sizes
2060 exclude_types = dialect.exclude_set_input_sizes
2062 dbapi = dialect.dbapi
2064 def lookup_type(typ):
2065 dbtype = typ._unwrapped_dialect_impl(dialect).get_dbapi_type(dbapi)
2067 if (
2068 dbtype is not None
2069 and (exclude_types is None or dbtype not in exclude_types)
2070 and (include_types is None or dbtype in include_types)
2071 ):
2072 return dbtype
2073 else:
2074 return None
2076 inputsizes = {}
2078 literal_execute_params = self.literal_execute_params
2080 for bindparam in self.bind_names:
2081 if bindparam in literal_execute_params:
2082 continue
2084 if bindparam.type._is_tuple_type:
2085 inputsizes[bindparam] = [
2086 lookup_type(typ)
2087 for typ in cast(TupleType, bindparam.type).types
2088 ]
2089 else:
2090 inputsizes[bindparam] = lookup_type(bindparam.type)
2092 return inputsizes
2094 @property
2095 def params(self):
2096 """Return the bind param dictionary embedded into this
2097 compiled object, for those values that are present.
2099 .. seealso::
2101 :ref:`faq_sql_expression_string` - includes a usage example for
2102 debugging use cases.
2104 """
2105 return self.construct_params(_check=False)
2107 def _process_parameters_for_postcompile(
2108 self,
2109 parameters: _MutableCoreSingleExecuteParams,
2110 _populate_self: bool = False,
2111 ) -> ExpandedState:
2112 """handle special post compile parameters.
2114 These include:
2116 * "expanding" parameters -typically IN tuples that are rendered
2117 on a per-parameter basis for an otherwise fixed SQL statement string.
2119 * literal_binds compiled with the literal_execute flag. Used for
2120 things like SQL Server "TOP N" where the driver does not accommodate
2121 N as a bound parameter.
2123 """
2125 expanded_parameters = {}
2126 new_positiontup: Optional[List[str]]
2128 pre_expanded_string = self._pre_expanded_string
2129 if pre_expanded_string is None:
2130 pre_expanded_string = self.string
2132 if self.positional:
2133 new_positiontup = []
2135 pre_expanded_positiontup = self._pre_expanded_positiontup
2136 if pre_expanded_positiontup is None:
2137 pre_expanded_positiontup = self.positiontup
2139 else:
2140 new_positiontup = pre_expanded_positiontup = None
2142 processors = self._bind_processors
2143 single_processors = cast(
2144 "Mapping[str, _BindProcessorType[Any]]", processors
2145 )
2146 tuple_processors = cast(
2147 "Mapping[str, Sequence[_BindProcessorType[Any]]]", processors
2148 )
2150 new_processors: Dict[str, _BindProcessorType[Any]] = {}
2152 replacement_expressions: Dict[str, Any] = {}
2153 to_update_sets: Dict[str, Any] = {}
2155 # notes:
2156 # *unescaped* parameter names in:
2157 # self.bind_names, self.binds, self._bind_processors, self.positiontup
2158 #
2159 # *escaped* parameter names in:
2160 # construct_params(), replacement_expressions
2162 numeric_positiontup: Optional[List[str]] = None
2164 if self.positional and pre_expanded_positiontup is not None:
2165 names: Iterable[str] = pre_expanded_positiontup
2166 if self._numeric_binds:
2167 numeric_positiontup = []
2168 else:
2169 names = self.bind_names.values()
2171 ebn = self.escaped_bind_names
2172 for name in names:
2173 escaped_name = ebn.get(name, name) if ebn else name
2174 parameter = self.binds[name]
2176 if parameter in self.literal_execute_params:
2177 if escaped_name not in replacement_expressions:
2178 replacement_expressions[escaped_name] = (
2179 self.render_literal_bindparam(
2180 parameter,
2181 render_literal_value=parameters.pop(escaped_name),
2182 )
2183 )
2184 continue
2186 if parameter in self.post_compile_params:
2187 if escaped_name in replacement_expressions:
2188 to_update = to_update_sets[escaped_name]
2189 values = None
2190 else:
2191 # we are removing the parameter from parameters
2192 # because it is a list value, which is not expected by
2193 # TypeEngine objects that would otherwise be asked to
2194 # process it. the single name is being replaced with
2195 # individual numbered parameters for each value in the
2196 # param.
2197 #
2198 # note we are also inserting *escaped* parameter names
2199 # into the given dictionary. default dialect will
2200 # use these param names directly as they will not be
2201 # in the escaped_bind_names dictionary.
2202 values = parameters.pop(name)
2204 leep_res = self._literal_execute_expanding_parameter(
2205 escaped_name, parameter, values
2206 )
2207 to_update, replacement_expr = leep_res
2209 to_update_sets[escaped_name] = to_update
2210 replacement_expressions[escaped_name] = replacement_expr
2212 if not parameter.literal_execute:
2213 parameters.update(to_update)
2214 if parameter.type._is_tuple_type:
2215 assert values is not None
2216 new_processors.update(
2217 (
2218 "%s_%s_%s" % (name, i, j),
2219 tuple_processors[name][j - 1],
2220 )
2221 for i, tuple_element in enumerate(values, 1)
2222 for j, _ in enumerate(tuple_element, 1)
2223 if name in tuple_processors
2224 and tuple_processors[name][j - 1] is not None
2225 )
2226 else:
2227 new_processors.update(
2228 (key, single_processors[name])
2229 for key, _ in to_update
2230 if name in single_processors
2231 )
2232 if numeric_positiontup is not None:
2233 numeric_positiontup.extend(
2234 name for name, _ in to_update
2235 )
2236 elif new_positiontup is not None:
2237 # to_update has escaped names, but that's ok since
2238 # these are new names, that aren't in the
2239 # escaped_bind_names dict.
2240 new_positiontup.extend(name for name, _ in to_update)
2241 expanded_parameters[name] = [
2242 expand_key for expand_key, _ in to_update
2243 ]
2244 elif new_positiontup is not None:
2245 new_positiontup.append(name)
2247 def process_expanding(m):
2248 key = m.group(1)
2249 expr = replacement_expressions[key]
2251 # if POSTCOMPILE included a bind_expression, render that
2252 # around each element
2253 if m.group(2):
2254 tok = m.group(2).split("~~")
2255 be_left, be_right = tok[1], tok[3]
2256 expr = ", ".join(
2257 "%s%s%s" % (be_left, exp, be_right)
2258 for exp in expr.split(", ")
2259 )
2260 return expr
2262 statement = re.sub(
2263 self._post_compile_pattern, process_expanding, pre_expanded_string
2264 )
2266 if numeric_positiontup is not None:
2267 assert new_positiontup is not None
2268 param_pos = {
2269 key: f"{self._numeric_binds_identifier_char}{num}"
2270 for num, key in enumerate(
2271 numeric_positiontup, self.next_numeric_pos
2272 )
2273 }
2274 # Can't use format here since % chars are not escaped.
2275 statement = self._pyformat_pattern.sub(
2276 lambda m: param_pos[m.group(1)], statement
2277 )
2278 new_positiontup.extend(numeric_positiontup)
2280 expanded_state = ExpandedState(
2281 statement,
2282 parameters,
2283 new_processors,
2284 new_positiontup,
2285 expanded_parameters,
2286 )
2288 if _populate_self:
2289 # this is for the "render_postcompile" flag, which is not
2290 # otherwise used internally and is for end-user debugging and
2291 # special use cases.
2292 self._pre_expanded_string = pre_expanded_string
2293 self._pre_expanded_positiontup = pre_expanded_positiontup
2294 self.string = expanded_state.statement
2295 self.positiontup = (
2296 list(expanded_state.positiontup or ())
2297 if self.positional
2298 else None
2299 )
2300 self._post_compile_expanded_state = expanded_state
2302 return expanded_state
2304 @util.preload_module("sqlalchemy.engine.cursor")
2305 def _create_result_map(self):
2306 """utility method used for unit tests only."""
2307 cursor = util.preloaded.engine_cursor
2308 return cursor.CursorResultMetaData._create_description_match_map(
2309 self._result_columns
2310 )
2312 # assigned by crud.py for insert/update statements
2313 _get_bind_name_for_col: _BindNameForColProtocol
2315 @util.memoized_property
2316 def _within_exec_param_key_getter(self) -> Callable[[Any], str]:
2317 getter = self._get_bind_name_for_col
2318 return getter
2320 @util.memoized_property
2321 @util.preload_module("sqlalchemy.engine.result")
2322 def _inserted_primary_key_from_lastrowid_getter(self):
2323 result = util.preloaded.engine_result
2325 param_key_getter = self._within_exec_param_key_getter
2327 assert self.compile_state is not None
2328 statement = self.compile_state.statement
2330 if TYPE_CHECKING:
2331 assert isinstance(statement, Insert)
2333 table = statement.table
2335 getters = [
2336 (operator.methodcaller("get", param_key_getter(col), None), col)
2337 for col in table.primary_key
2338 ]
2340 autoinc_getter = None
2341 autoinc_col = table._autoincrement_column
2342 if autoinc_col is not None:
2343 # apply type post processors to the lastrowid
2344 lastrowid_processor = autoinc_col.type._cached_result_processor(
2345 self.dialect, None
2346 )
2347 autoinc_key = param_key_getter(autoinc_col)
2349 # if a bind value is present for the autoincrement column
2350 # in the parameters, we need to do the logic dictated by
2351 # #7998; honor a non-None user-passed parameter over lastrowid.
2352 # previously in the 1.4 series we weren't fetching lastrowid
2353 # at all if the key were present in the parameters
2354 if autoinc_key in self.binds:
2356 def _autoinc_getter(lastrowid, parameters):
2357 param_value = parameters.get(autoinc_key, lastrowid)
2358 if param_value is not None:
2359 # they supplied non-None parameter, use that.
2360 # SQLite at least is observed to return the wrong
2361 # cursor.lastrowid for INSERT..ON CONFLICT so it
2362 # can't be used in all cases
2363 return param_value
2364 else:
2365 # use lastrowid
2366 return lastrowid
2368 # work around mypy https://github.com/python/mypy/issues/14027
2369 autoinc_getter = _autoinc_getter
2371 else:
2372 lastrowid_processor = None
2374 row_fn = result.result_tuple([col.key for col in table.primary_key])
2376 def get(lastrowid, parameters):
2377 """given cursor.lastrowid value and the parameters used for INSERT,
2378 return a "row" that represents the primary key, either by
2379 using the "lastrowid" or by extracting values from the parameters
2380 that were sent along with the INSERT.
2382 """
2383 if lastrowid_processor is not None:
2384 lastrowid = lastrowid_processor(lastrowid)
2386 if lastrowid is None:
2387 return row_fn(getter(parameters) for getter, col in getters)
2388 else:
2389 return row_fn(
2390 (
2391 (
2392 autoinc_getter(lastrowid, parameters)
2393 if autoinc_getter is not None
2394 else lastrowid
2395 )
2396 if col is autoinc_col
2397 else getter(parameters)
2398 )
2399 for getter, col in getters
2400 )
2402 return get
2404 @util.memoized_property
2405 @util.preload_module("sqlalchemy.engine.result")
2406 def _inserted_primary_key_from_returning_getter(self):
2407 result = util.preloaded.engine_result
2409 assert self.compile_state is not None
2410 statement = self.compile_state.statement
2412 if TYPE_CHECKING:
2413 assert isinstance(statement, Insert)
2415 param_key_getter = self._within_exec_param_key_getter
2416 table = statement.table
2418 returning = self.implicit_returning
2419 assert returning is not None
2420 ret = {col: idx for idx, col in enumerate(returning)}
2422 getters = cast(
2423 "List[Tuple[Callable[[Any], Any], bool]]",
2424 [
2425 (
2426 (operator.itemgetter(ret[col]), True)
2427 if col in ret
2428 else (
2429 operator.methodcaller(
2430 "get", param_key_getter(col), None
2431 ),
2432 False,
2433 )
2434 )
2435 for col in table.primary_key
2436 ],
2437 )
2439 row_fn = result.result_tuple([col.key for col in table.primary_key])
2441 def get(row, parameters):
2442 return row_fn(
2443 getter(row) if use_row else getter(parameters)
2444 for getter, use_row in getters
2445 )
2447 return get
2449 def default_from(self) -> str:
2450 """Called when a SELECT statement has no froms, and no FROM clause is
2451 to be appended.
2453 Gives Oracle Database a chance to tack on a ``FROM DUAL`` to the string
2454 output.
2456 """
2457 return ""
2459 def visit_override_binds(self, override_binds, **kw):
2460 """SQL compile the nested element of an _OverrideBinds with
2461 bindparams swapped out.
2463 The _OverrideBinds is not normally expected to be compiled; it
2464 is meant to be used when an already cached statement is to be used,
2465 the compilation was already performed, and only the bound params should
2466 be swapped in at execution time.
2468 However, there are test cases that exericise this object, and
2469 additionally the ORM subquery loader is known to feed in expressions
2470 which include this construct into new queries (discovered in #11173),
2471 so it has to do the right thing at compile time as well.
2473 """
2475 # get SQL text first
2476 sqltext = override_binds.element._compiler_dispatch(self, **kw)
2478 # for a test compile that is not for caching, change binds after the
2479 # fact. note that we don't try to
2480 # swap the bindparam as we compile, because our element may be
2481 # elsewhere in the statement already (e.g. a subquery or perhaps a
2482 # CTE) and was already visited / compiled. See
2483 # test_relationship_criteria.py ->
2484 # test_selectinload_local_criteria_subquery
2485 for k in override_binds.translate:
2486 if k not in self.binds:
2487 continue
2488 bp = self.binds[k]
2490 # so this would work, just change the value of bp in place.
2491 # but we dont want to mutate things outside.
2492 # bp.value = override_binds.translate[bp.key]
2493 # continue
2495 # instead, need to replace bp with new_bp or otherwise accommodate
2496 # in all internal collections
2497 new_bp = bp._with_value(
2498 override_binds.translate[bp.key],
2499 maintain_key=True,
2500 required=False,
2501 )
2503 name = self.bind_names[bp]
2504 self.binds[k] = self.binds[name] = new_bp
2505 self.bind_names[new_bp] = name
2506 self.bind_names.pop(bp, None)
2508 if bp in self.post_compile_params:
2509 self.post_compile_params |= {new_bp}
2510 if bp in self.literal_execute_params:
2511 self.literal_execute_params |= {new_bp}
2513 ckbm_tuple = self._cache_key_bind_match
2514 if ckbm_tuple:
2515 ckbm, cksm = ckbm_tuple
2516 for bp in bp._cloned_set:
2517 if bp.key in cksm:
2518 cb = cksm[bp.key]
2519 ckbm[cb].append(new_bp)
2521 return sqltext
2523 def visit_grouping(self, grouping, asfrom=False, **kwargs):
2524 return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
2526 def visit_select_statement_grouping(self, grouping, **kwargs):
2527 return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
2529 def visit_label_reference(
2530 self, element, within_columns_clause=False, **kwargs
2531 ):
2532 if self.stack and self.dialect.supports_simple_order_by_label:
2533 try:
2534 compile_state = cast(
2535 "Union[SelectState, CompoundSelectState]",
2536 self.stack[-1]["compile_state"],
2537 )
2538 except KeyError as ke:
2539 raise exc.CompileError(
2540 "Can't resolve label reference for ORDER BY / "
2541 "GROUP BY / DISTINCT etc."
2542 ) from ke
2544 (
2545 with_cols,
2546 only_froms,
2547 only_cols,
2548 ) = compile_state._label_resolve_dict
2549 if within_columns_clause:
2550 resolve_dict = only_froms
2551 else:
2552 resolve_dict = only_cols
2554 # this can be None in the case that a _label_reference()
2555 # were subject to a replacement operation, in which case
2556 # the replacement of the Label element may have changed
2557 # to something else like a ColumnClause expression.
2558 order_by_elem = element.element._order_by_label_element
2560 if (
2561 order_by_elem is not None
2562 and order_by_elem.name in resolve_dict
2563 and order_by_elem.shares_lineage(
2564 resolve_dict[order_by_elem.name]
2565 )
2566 ):
2567 kwargs["render_label_as_label"] = (
2568 element.element._order_by_label_element
2569 )
2570 return self.process(
2571 element.element,
2572 within_columns_clause=within_columns_clause,
2573 **kwargs,
2574 )
2576 def visit_textual_label_reference(
2577 self, element, within_columns_clause=False, **kwargs
2578 ):
2579 if not self.stack:
2580 # compiling the element outside of the context of a SELECT
2581 return self.process(element._text_clause)
2583 try:
2584 compile_state = cast(
2585 "Union[SelectState, CompoundSelectState]",
2586 self.stack[-1]["compile_state"],
2587 )
2588 except KeyError as ke:
2589 coercions._no_text_coercion(
2590 element.element,
2591 extra=(
2592 "Can't resolve label reference for ORDER BY / "
2593 "GROUP BY / DISTINCT etc."
2594 ),
2595 exc_cls=exc.CompileError,
2596 err=ke,
2597 )
2599 with_cols, only_froms, only_cols = compile_state._label_resolve_dict
2600 try:
2601 if within_columns_clause:
2602 col = only_froms[element.element]
2603 else:
2604 col = with_cols[element.element]
2605 except KeyError as err:
2606 coercions._no_text_coercion(
2607 element.element,
2608 extra=(
2609 "Can't resolve label reference for ORDER BY / "
2610 "GROUP BY / DISTINCT etc."
2611 ),
2612 exc_cls=exc.CompileError,
2613 err=err,
2614 )
2615 else:
2616 kwargs["render_label_as_label"] = col
2617 return self.process(
2618 col, within_columns_clause=within_columns_clause, **kwargs
2619 )
2621 def visit_label(
2622 self,
2623 label,
2624 add_to_result_map=None,
2625 within_label_clause=False,
2626 within_columns_clause=False,
2627 render_label_as_label=None,
2628 result_map_targets=(),
2629 within_tstring=False,
2630 **kw,
2631 ):
2632 if within_tstring:
2633 raise exc.CompileError(
2634 "Using label() directly inside tstring is not supported "
2635 "as it is ambiguous how the label expression should be "
2636 "rendered without knowledge of how it's being used in SQL"
2637 )
2638 # only render labels within the columns clause
2639 # or ORDER BY clause of a select. dialect-specific compilers
2640 # can modify this behavior.
2641 render_label_with_as = (
2642 within_columns_clause and not within_label_clause
2643 )
2644 render_label_only = render_label_as_label is label
2646 if render_label_only or render_label_with_as:
2647 if isinstance(label.name, elements._truncated_label):
2648 labelname = self._truncated_identifier("colident", label.name)
2649 else:
2650 labelname = label.name
2652 if render_label_with_as:
2653 if add_to_result_map is not None:
2654 add_to_result_map(
2655 labelname,
2656 label.name,
2657 (label, labelname) + label._alt_names + result_map_targets,
2658 label.type,
2659 )
2660 return (
2661 label.element._compiler_dispatch(
2662 self,
2663 within_columns_clause=True,
2664 within_label_clause=True,
2665 **kw,
2666 )
2667 + OPERATORS[operators.as_]
2668 + self.preparer.format_label(label, labelname)
2669 )
2670 elif render_label_only:
2671 return self.preparer.format_label(label, labelname)
2672 else:
2673 return label.element._compiler_dispatch(
2674 self, within_columns_clause=False, **kw
2675 )
2677 def _fallback_column_name(self, column):
2678 raise exc.CompileError(
2679 "Cannot compile Column object until its 'name' is assigned."
2680 )
2682 def visit_lambda_element(self, element, **kw):
2683 sql_element = element._resolved
2684 return self.process(sql_element, **kw)
2686 def visit_column(
2687 self,
2688 column: ColumnClause[Any],
2689 add_to_result_map: Optional[_ResultMapAppender] = None,
2690 include_table: bool = True,
2691 result_map_targets: Tuple[Any, ...] = (),
2692 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,
2693 **kwargs: Any,
2694 ) -> str:
2695 name = orig_name = column.name
2696 if name is None:
2697 name = self._fallback_column_name(column)
2699 is_literal = column.is_literal
2700 if not is_literal and isinstance(name, elements._truncated_label):
2701 name = self._truncated_identifier("colident", name)
2703 if add_to_result_map is not None:
2704 targets = (column, name, column.key) + result_map_targets
2705 if column._tq_label:
2706 targets += (column._tq_label,)
2708 add_to_result_map(name, orig_name, targets, column.type)
2710 if is_literal:
2711 # note we are not currently accommodating for
2712 # literal_column(quoted_name('ident', True)) here
2713 name = self.escape_literal_column(name)
2714 else:
2715 name = self.preparer.quote(name)
2716 table = column.table
2717 if table is None or not include_table or not table.named_with_column:
2718 return name
2719 else:
2720 effective_schema = self.preparer.schema_for_object(table)
2722 if effective_schema:
2723 schema_prefix = (
2724 self.preparer.quote_schema(effective_schema) + "."
2725 )
2726 else:
2727 schema_prefix = ""
2729 if TYPE_CHECKING:
2730 assert isinstance(table, NamedFromClause)
2731 tablename = table.name
2733 if (
2734 not effective_schema
2735 and ambiguous_table_name_map
2736 and tablename in ambiguous_table_name_map
2737 ):
2738 tablename = ambiguous_table_name_map[tablename]
2740 if isinstance(tablename, elements._truncated_label):
2741 tablename = self._truncated_identifier("alias", tablename)
2743 return schema_prefix + self.preparer.quote(tablename) + "." + name
2745 def visit_collation(self, element, **kw):
2746 return self.preparer.format_collation(
2747 element.collation, element.collation_schema
2748 )
2750 def visit_fromclause(self, fromclause, **kwargs):
2751 return fromclause.name
2753 def visit_index(self, index, **kwargs):
2754 return index.name
2756 def visit_typeclause(self, typeclause, **kw):
2757 kw["type_expression"] = typeclause
2758 kw["identifier_preparer"] = self.preparer
2759 return self.dialect.type_compiler_instance.process(
2760 typeclause.type, **kw
2761 )
2763 def post_process_text(self, text):
2764 if self.preparer._double_percents:
2765 text = text.replace("%", "%%")
2766 return text
2768 def escape_literal_column(self, text):
2769 if self.preparer._double_percents:
2770 text = text.replace("%", "%%")
2771 return text
2773 def visit_textclause(self, textclause, add_to_result_map=None, **kw):
2774 if self._collect_params:
2775 self._add_to_params(textclause)
2777 def do_bindparam(m):
2778 name = m.group(1)
2779 if name in textclause._bindparams:
2780 return self.process(textclause._bindparams[name], **kw)
2781 else:
2782 return self.bindparam_string(name, **kw)
2784 if not self.stack:
2785 self.isplaintext = True
2787 if add_to_result_map:
2788 # text() object is present in the columns clause of a
2789 # select(). Add a no-name entry to the result map so that
2790 # row[text()] produces a result
2791 add_to_result_map(None, None, (textclause,), sqltypes.NULLTYPE)
2793 # un-escape any \:params
2794 return BIND_PARAMS_ESC.sub(
2795 lambda m: m.group(1),
2796 BIND_PARAMS.sub(
2797 do_bindparam, self.post_process_text(textclause.text)
2798 ),
2799 )
2801 def visit_tstring(self, tstring, add_to_result_map=None, **kw):
2802 if self._collect_params:
2803 self._add_to_params(tstring)
2805 if not self.stack:
2806 self.isplaintext = True
2808 if add_to_result_map:
2809 # tstring() object is present in the columns clause of a
2810 # select(). Add a no-name entry to the result map so that
2811 # row[tstring()] produces a result
2812 add_to_result_map(None, None, (tstring,), sqltypes.NULLTYPE)
2814 # Process each part and concatenate
2815 kw["within_tstring"] = True
2816 return "".join(self.process(part, **kw) for part in tstring.parts)
2818 def visit_textual_select(
2819 self, taf, compound_index=None, asfrom=False, **kw
2820 ):
2821 if self._collect_params:
2822 self._add_to_params(taf)
2823 toplevel = not self.stack
2824 entry = self._default_stack_entry if toplevel else self.stack[-1]
2826 new_entry: _CompilerStackEntry = {
2827 "correlate_froms": set(),
2828 "asfrom_froms": set(),
2829 "selectable": taf,
2830 }
2831 self.stack.append(new_entry)
2833 if taf._independent_ctes:
2834 self._dispatch_independent_ctes(taf, kw)
2836 populate_result_map = (
2837 toplevel
2838 or (
2839 compound_index == 0
2840 and entry.get("need_result_map_for_compound", False)
2841 )
2842 or entry.get("need_result_map_for_nested", False)
2843 )
2845 if populate_result_map:
2846 self._ordered_columns = self._textual_ordered_columns = (
2847 taf.positional
2848 )
2850 # enable looser result column matching when the SQL text links to
2851 # Column objects by name only
2852 self._loose_column_name_matching = not taf.positional and bool(
2853 taf.column_args
2854 )
2856 for c in taf.column_args:
2857 self.process(
2858 c,
2859 within_columns_clause=True,
2860 add_to_result_map=self._add_to_result_map,
2861 )
2863 text = self.process(taf.element, **kw)
2864 if self.ctes:
2865 nesting_level = len(self.stack) if not toplevel else None
2866 text = self._render_cte_clause(nesting_level=nesting_level) + text
2868 self.stack.pop(-1)
2870 return text
2872 def visit_null(self, expr: Null, **kw: Any) -> str:
2873 return "NULL"
2875 def visit_true(self, expr: True_, **kw: Any) -> str:
2876 if self.dialect.supports_native_boolean:
2877 return "true"
2878 else:
2879 return "1"
2881 def visit_false(self, expr: False_, **kw: Any) -> str:
2882 if self.dialect.supports_native_boolean:
2883 return "false"
2884 else:
2885 return "0"
2887 def _generate_delimited_list(self, elements, separator, **kw):
2888 return separator.join(
2889 s
2890 for s in (c._compiler_dispatch(self, **kw) for c in elements)
2891 if s
2892 )
2894 def _generate_delimited_and_list(self, clauses, **kw):
2895 lcc, clauses = elements.BooleanClauseList._process_clauses_for_boolean(
2896 operators.and_,
2897 elements.True_._singleton,
2898 elements.False_._singleton,
2899 clauses,
2900 )
2901 if lcc == 1:
2902 return clauses[0]._compiler_dispatch(self, **kw)
2903 else:
2904 separator = OPERATORS[operators.and_]
2905 return separator.join(
2906 s
2907 for s in (c._compiler_dispatch(self, **kw) for c in clauses)
2908 if s
2909 )
2911 def visit_tuple(self, clauselist, **kw):
2912 return "(%s)" % self.visit_clauselist(clauselist, **kw)
2914 def visit_element_list(self, element, **kw):
2915 return self._generate_delimited_list(element.clauses, " ", **kw)
2917 def visit_order_by_list(self, element, **kw):
2918 return self._generate_delimited_list(element.clauses, ", ", **kw)
2920 def visit_clauselist(self, clauselist, **kw):
2921 sep = clauselist.operator
2922 if sep is None:
2923 sep = " "
2924 else:
2925 sep = OPERATORS[clauselist.operator]
2927 return self._generate_delimited_list(clauselist.clauses, sep, **kw)
2929 def visit_expression_clauselist(self, clauselist, **kw):
2930 operator_ = clauselist.operator
2932 disp = self._get_operator_dispatch(
2933 operator_, "expression_clauselist", None
2934 )
2935 if disp:
2936 return disp(clauselist, operator_, **kw)
2938 try:
2939 opstring = OPERATORS[operator_]
2940 except KeyError as err:
2941 raise exc.UnsupportedCompilationError(self, operator_) from err
2942 else:
2943 kw["_in_operator_expression"] = True
2944 return self._generate_delimited_list(
2945 clauselist.clauses, opstring, **kw
2946 )
2948 def visit_case(self, clause, **kwargs):
2949 x = "CASE "
2950 if clause.value is not None:
2951 x += clause.value._compiler_dispatch(self, **kwargs) + " "
2952 for cond, result in clause.whens:
2953 x += (
2954 "WHEN "
2955 + cond._compiler_dispatch(self, **kwargs)
2956 + " THEN "
2957 + result._compiler_dispatch(self, **kwargs)
2958 + " "
2959 )
2960 if clause.else_ is not None:
2961 x += (
2962 "ELSE " + clause.else_._compiler_dispatch(self, **kwargs) + " "
2963 )
2964 x += "END"
2965 return x
2967 def visit_type_coerce(self, type_coerce, **kw):
2968 return type_coerce.typed_expression._compiler_dispatch(self, **kw)
2970 def visit_cast(self, cast, **kwargs):
2971 type_clause = cast.typeclause._compiler_dispatch(self, **kwargs)
2972 match = re.match("(.*)( COLLATE .*)", type_clause)
2973 return "CAST(%s AS %s)%s" % (
2974 cast.clause._compiler_dispatch(self, **kwargs),
2975 match.group(1) if match else type_clause,
2976 match.group(2) if match else "",
2977 )
2979 def visit_frame_clause(self, frameclause, **kw):
2981 if frameclause.lower_type is elements.FrameClauseType.UNBOUNDED:
2982 left = "UNBOUNDED PRECEDING"
2983 elif frameclause.lower_type is elements.FrameClauseType.CURRENT:
2984 left = "CURRENT ROW"
2985 else:
2986 val = self.process(frameclause.lower_bind, **kw)
2987 if frameclause.lower_type is elements.FrameClauseType.PRECEDING:
2988 left = f"{val} PRECEDING"
2989 else:
2990 left = f"{val} FOLLOWING"
2992 if frameclause.upper_type is elements.FrameClauseType.UNBOUNDED:
2993 right = "UNBOUNDED FOLLOWING"
2994 elif frameclause.upper_type is elements.FrameClauseType.CURRENT:
2995 right = "CURRENT ROW"
2996 else:
2997 val = self.process(frameclause.upper_bind, **kw)
2998 if frameclause.upper_type is elements.FrameClauseType.PRECEDING:
2999 right = f"{val} PRECEDING"
3000 else:
3001 right = f"{val} FOLLOWING"
3003 return f"{left} AND {right}"
3005 def visit_over(self, over, **kwargs):
3006 text = over.element._compiler_dispatch(self, **kwargs)
3007 if over.range_ is not None:
3008 range_ = f"RANGE BETWEEN {self.process(over.range_, **kwargs)}"
3009 elif over.rows is not None:
3010 range_ = f"ROWS BETWEEN {self.process(over.rows, **kwargs)}"
3011 elif over.groups is not None:
3012 range_ = f"GROUPS BETWEEN {self.process(over.groups, **kwargs)}"
3013 else:
3014 range_ = None
3016 if range_ is not None and over.exclude is not None:
3017 range_ += " EXCLUDE " + self.preparer.validate_sql_phrase(
3018 over.exclude, _WINDOW_EXCLUDE_RE
3019 )
3021 return "%s OVER (%s)" % (
3022 text,
3023 " ".join(
3024 [
3025 "%s BY %s"
3026 % (word, clause._compiler_dispatch(self, **kwargs))
3027 for word, clause in (
3028 ("PARTITION", over.partition_by),
3029 ("ORDER", over.order_by),
3030 )
3031 if clause is not None and len(clause)
3032 ]
3033 + ([range_] if range_ else [])
3034 ),
3035 )
3037 def visit_withingroup(self, withingroup, **kwargs):
3038 return "%s WITHIN GROUP (ORDER BY %s)" % (
3039 withingroup.element._compiler_dispatch(self, **kwargs),
3040 withingroup.order_by._compiler_dispatch(self, **kwargs),
3041 )
3043 def visit_funcfilter(self, funcfilter, **kwargs):
3044 return "%s FILTER (WHERE %s)" % (
3045 funcfilter.func._compiler_dispatch(self, **kwargs),
3046 funcfilter.criterion._compiler_dispatch(self, **kwargs),
3047 )
3049 def visit_aggregateorderby(self, aggregateorderby, **kwargs):
3050 if self.dialect.aggregate_order_by_style is AggregateOrderByStyle.NONE:
3051 raise exc.CompileError(
3052 "this dialect does not support "
3053 "ORDER BY within an aggregate function"
3054 )
3055 elif (
3056 self.dialect.aggregate_order_by_style
3057 is AggregateOrderByStyle.INLINE
3058 ):
3059 new_fn = aggregateorderby.element._clone()
3060 new_fn.clause_expr = elements.Grouping(
3061 aggregate_orderby_inline(
3062 new_fn.clause_expr.element, aggregateorderby.order_by
3063 )
3064 )
3066 return new_fn._compiler_dispatch(self, **kwargs)
3067 else:
3068 return self.visit_withingroup(aggregateorderby, **kwargs)
3070 def visit_aggregate_orderby_inline(self, element, **kw):
3071 return "%s ORDER BY %s" % (
3072 self.process(element.element, **kw),
3073 self.process(element.aggregate_order_by, **kw),
3074 )
3076 def visit_aggregate_strings_func(self, fn, *, use_function_name, **kw):
3077 # aggreagate_order_by attribute is present if visit_function
3078 # gave us a Function with aggregate_orderby_inline() as the inner
3079 # contents
3080 order_by = getattr(fn.clauses, "aggregate_order_by", None)
3082 literal_exec = dict(kw)
3083 literal_exec["literal_execute"] = True
3085 # break up the function into its components so we can apply
3086 # literal_execute to the second argument (the delimiter)
3087 cl = list(fn.clauses)
3088 expr, delimiter = cl[0:2]
3089 if (
3090 order_by is not None
3091 and self.dialect.aggregate_order_by_style
3092 is AggregateOrderByStyle.INLINE
3093 ):
3094 return (
3095 f"{use_function_name}({expr._compiler_dispatch(self, **kw)}, "
3096 f"{delimiter._compiler_dispatch(self, **literal_exec)} "
3097 f"ORDER BY {order_by._compiler_dispatch(self, **kw)})"
3098 )
3099 else:
3100 return (
3101 f"{use_function_name}({expr._compiler_dispatch(self, **kw)}, "
3102 f"{delimiter._compiler_dispatch(self, **literal_exec)})"
3103 )
3105 def visit_extract(self, extract, **kwargs):
3106 field = self.extract_map.get(extract.field, extract.field)
3107 return "EXTRACT(%s FROM %s)" % (
3108 field,
3109 extract.expr._compiler_dispatch(self, **kwargs),
3110 )
3112 def visit_scalar_function_column(self, element, **kw):
3113 compiled_fn = self.visit_function(element.fn, **kw)
3114 compiled_col = self.visit_column(element, **kw)
3115 return "(%s).%s" % (compiled_fn, compiled_col)
3117 def visit_function(
3118 self,
3119 func: Function[Any],
3120 add_to_result_map: Optional[_ResultMapAppender] = None,
3121 **kwargs: Any,
3122 ) -> str:
3123 if self._collect_params:
3124 self._add_to_params(func)
3125 if add_to_result_map is not None:
3126 add_to_result_map(func.name, func.name, (func.name,), func.type)
3128 disp = getattr(self, "visit_%s_func" % func.name.lower(), None)
3130 text: str
3132 kwargs["within_aggregate_function"] = True
3134 if disp:
3135 text = disp(func, **kwargs)
3136 else:
3137 name = FUNCTIONS.get(func._deannotate().__class__, None)
3138 if name:
3139 if func._has_args:
3140 name += "%(expr)s"
3141 else:
3142 name = func.name
3143 name = (
3144 self.preparer.quote(name)
3145 if self.preparer._requires_quotes_illegal_chars(name)
3146 or isinstance(name, elements.quoted_name)
3147 else name
3148 )
3149 name = name + "%(expr)s"
3150 text = ".".join(
3151 [
3152 (
3153 self.preparer.quote(tok)
3154 if self.preparer._requires_quotes_illegal_chars(tok)
3155 or isinstance(name, elements.quoted_name)
3156 else tok
3157 )
3158 for tok in func.packagenames
3159 ]
3160 + [name]
3161 ) % {"expr": self.function_argspec(func, **kwargs)}
3163 if func._with_ordinality:
3164 text += " WITH ORDINALITY"
3165 return text
3167 def visit_next_value_func(self, next_value, **kw):
3168 return self.visit_sequence(next_value.sequence)
3170 def visit_sequence(self, sequence, **kw):
3171 raise NotImplementedError(
3172 "Dialect '%s' does not support sequence increments."
3173 % self.dialect.name
3174 )
3176 def function_argspec(self, func: Function[Any], **kwargs: Any) -> str:
3177 return func.clause_expr._compiler_dispatch(self, **kwargs)
3179 def visit_compound_select(
3180 self, cs, asfrom=False, compound_index=None, **kwargs
3181 ):
3182 if self._collect_params:
3183 self._add_to_params(cs)
3184 toplevel = not self.stack
3186 compile_state = cs._compile_state_factory(cs, self, **kwargs)
3188 if toplevel and not self.compile_state:
3189 self.compile_state = compile_state
3191 compound_stmt = compile_state.statement
3193 entry = self._default_stack_entry if toplevel else self.stack[-1]
3194 need_result_map = toplevel or (
3195 not compound_index
3196 and entry.get("need_result_map_for_compound", False)
3197 )
3199 # indicates there is already a CompoundSelect in play
3200 if compound_index == 0:
3201 entry["select_0"] = cs
3203 self.stack.append(
3204 {
3205 "correlate_froms": entry["correlate_froms"],
3206 "asfrom_froms": entry["asfrom_froms"],
3207 "selectable": cs,
3208 "compile_state": compile_state,
3209 "need_result_map_for_compound": need_result_map,
3210 }
3211 )
3213 if compound_stmt._independent_ctes:
3214 self._dispatch_independent_ctes(compound_stmt, kwargs)
3216 keyword = self.compound_keywords[cs.keyword]
3218 text = (" " + keyword + " ").join(
3219 (
3220 c._compiler_dispatch(
3221 self, asfrom=asfrom, compound_index=i, **kwargs
3222 )
3223 for i, c in enumerate(cs.selects)
3224 )
3225 )
3227 kwargs["include_table"] = False
3228 text += self.group_by_clause(cs, **dict(asfrom=asfrom, **kwargs))
3229 text += self.order_by_clause(cs, **kwargs)
3230 if cs._has_row_limiting_clause:
3231 text += self._row_limit_clause(cs, **kwargs)
3233 if self.ctes:
3234 nesting_level = len(self.stack) if not toplevel else None
3235 text = (
3236 self._render_cte_clause(
3237 nesting_level=nesting_level,
3238 include_following_stack=True,
3239 )
3240 + text
3241 )
3243 self.stack.pop(-1)
3244 return text
3246 def _row_limit_clause(self, cs, **kwargs):
3247 if cs._fetch_clause is not None:
3248 return self.fetch_clause(cs, **kwargs)
3249 else:
3250 return self.limit_clause(cs, **kwargs)
3252 def _get_operator_dispatch(self, operator_, qualifier1, qualifier2):
3253 attrname = "visit_%s_%s%s" % (
3254 operator_.__name__,
3255 qualifier1,
3256 "_" + qualifier2 if qualifier2 else "",
3257 )
3258 return getattr(self, attrname, None)
3260 def _get_custom_operator_dispatch(self, operator_, qualifier1):
3261 attrname = "visit_%s_op_%s" % (operator_.visit_name, qualifier1)
3262 return getattr(self, attrname, None)
3264 def visit_unary(
3265 self, unary, add_to_result_map=None, result_map_targets=(), **kw
3266 ):
3267 if add_to_result_map is not None:
3268 result_map_targets += (unary,)
3269 kw["add_to_result_map"] = add_to_result_map
3270 kw["result_map_targets"] = result_map_targets
3272 if unary.operator is operators.distinct_op and not kw.get(
3273 "within_aggregate_function", False
3274 ):
3275 util.warn(
3276 "Column-expression-level unary distinct() "
3277 "should not be used outside of an aggregate "
3278 "function. For general 'SELECT DISTINCT' support"
3279 "use select().distinct()."
3280 )
3282 if unary.operator:
3283 if unary.modifier:
3284 raise exc.CompileError(
3285 "Unary expression does not support operator "
3286 "and modifier simultaneously"
3287 )
3288 disp = self._get_operator_dispatch(
3289 unary.operator, "unary", "operator"
3290 )
3291 if disp:
3292 return disp(unary, unary.operator, **kw)
3293 else:
3294 return self._generate_generic_unary_operator(
3295 unary, OPERATORS[unary.operator], **kw
3296 )
3297 elif unary.modifier:
3298 disp = self._get_operator_dispatch(
3299 unary.modifier, "unary", "modifier"
3300 )
3301 if disp:
3302 return disp(unary, unary.modifier, **kw)
3303 else:
3304 return self._generate_generic_unary_modifier(
3305 unary, OPERATORS[unary.modifier], **kw
3306 )
3307 else:
3308 raise exc.CompileError(
3309 "Unary expression has no operator or modifier"
3310 )
3312 def visit_truediv_binary(self, binary, operator, **kw):
3313 if self.dialect.div_is_floordiv:
3314 return (
3315 self.process(binary.left, **kw)
3316 + " / "
3317 # TODO: would need a fast cast again here,
3318 # unless we want to use an implicit cast like "+ 0.0"
3319 + self.process(
3320 elements.Cast(
3321 binary.right,
3322 (
3323 binary.right.type
3324 if binary.right.type._type_affinity
3325 in (sqltypes.Numeric, sqltypes.Float)
3326 else sqltypes.Numeric()
3327 ),
3328 ),
3329 **kw,
3330 )
3331 )
3332 else:
3333 return (
3334 self.process(binary.left, **kw)
3335 + " / "
3336 + self.process(binary.right, **kw)
3337 )
3339 def visit_floordiv_binary(self, binary, operator, **kw):
3340 if (
3341 self.dialect.div_is_floordiv
3342 and binary.right.type._type_affinity is sqltypes.Integer
3343 and binary.left.type._type_affinity is sqltypes.Integer
3344 ):
3345 return (
3346 self.process(binary.left, **kw)
3347 + " / "
3348 + self.process(binary.right, **kw)
3349 )
3350 else:
3351 return "FLOOR(%s)" % (
3352 self.process(binary.left, **kw)
3353 + " / "
3354 + self.process(binary.right, **kw)
3355 )
3357 def visit_is_true_unary_operator(self, element, operator, **kw):
3358 if (
3359 element._is_implicitly_boolean
3360 or self.dialect.supports_native_boolean
3361 ):
3362 return self.process(element.element, **kw)
3363 else:
3364 return "%s = 1" % self.process(element.element, **kw)
3366 def visit_is_false_unary_operator(self, element, operator, **kw):
3367 if (
3368 element._is_implicitly_boolean
3369 or self.dialect.supports_native_boolean
3370 ):
3371 return "NOT %s" % self.process(element.element, **kw)
3372 else:
3373 return "%s = 0" % self.process(element.element, **kw)
3375 def visit_not_match_op_binary(self, binary, operator, **kw):
3376 return "NOT %s" % self.visit_binary(
3377 binary, override_operator=operators.match_op
3378 )
3380 def visit_not_in_op_binary(self, binary, operator, **kw):
3381 # The brackets are required in the NOT IN operation because the empty
3382 # case is handled using the form "(col NOT IN (null) OR 1 = 1)".
3383 # The presence of the OR makes the brackets required.
3384 return "(%s)" % self._generate_generic_binary(
3385 binary, OPERATORS[operator], **kw
3386 )
3388 def visit_empty_set_op_expr(self, type_, expand_op, **kw):
3389 if expand_op is operators.not_in_op:
3390 if len(type_) > 1:
3391 return "(%s)) OR (1 = 1" % (
3392 ", ".join("NULL" for element in type_)
3393 )
3394 else:
3395 return "NULL) OR (1 = 1"
3396 elif expand_op is operators.in_op:
3397 if len(type_) > 1:
3398 return "(%s)) AND (1 != 1" % (
3399 ", ".join("NULL" for element in type_)
3400 )
3401 else:
3402 return "NULL) AND (1 != 1"
3403 else:
3404 return self.visit_empty_set_expr(type_)
3406 def visit_empty_set_expr(self, element_types, **kw):
3407 raise NotImplementedError(
3408 "Dialect '%s' does not support empty set expression."
3409 % self.dialect.name
3410 )
3412 def _literal_execute_expanding_parameter_literal_binds(
3413 self, parameter, values, bind_expression_template=None
3414 ):
3415 typ_dialect_impl = parameter.type._unwrapped_dialect_impl(self.dialect)
3417 if not values:
3418 # empty IN expression. note we don't need to use
3419 # bind_expression_template here because there are no
3420 # expressions to render.
3422 if typ_dialect_impl._is_tuple_type:
3423 replacement_expression = (
3424 "VALUES " if self.dialect.tuple_in_values else ""
3425 ) + self.visit_empty_set_op_expr(
3426 parameter.type.types, parameter.expand_op
3427 )
3429 else:
3430 replacement_expression = self.visit_empty_set_op_expr(
3431 [parameter.type], parameter.expand_op
3432 )
3434 elif typ_dialect_impl._is_tuple_type or (
3435 typ_dialect_impl._isnull
3436 and isinstance(values[0], collections_abc.Sequence)
3437 and not isinstance(values[0], (str, bytes))
3438 ):
3439 if typ_dialect_impl._has_bind_expression:
3440 raise NotImplementedError(
3441 "bind_expression() on TupleType not supported with "
3442 "literal_binds"
3443 )
3445 replacement_expression = (
3446 "VALUES " if self.dialect.tuple_in_values else ""
3447 ) + ", ".join(
3448 "(%s)"
3449 % (
3450 ", ".join(
3451 self.render_literal_value(value, param_type)
3452 for value, param_type in zip(
3453 tuple_element, parameter.type.types
3454 )
3455 )
3456 )
3457 for i, tuple_element in enumerate(values)
3458 )
3459 else:
3460 if bind_expression_template:
3461 post_compile_pattern = self._post_compile_pattern
3462 m = post_compile_pattern.search(bind_expression_template)
3463 assert m and m.group(
3464 2
3465 ), "unexpected format for expanding parameter"
3467 tok = m.group(2).split("~~")
3468 be_left, be_right = tok[1], tok[3]
3469 replacement_expression = ", ".join(
3470 "%s%s%s"
3471 % (
3472 be_left,
3473 self.render_literal_value(value, parameter.type),
3474 be_right,
3475 )
3476 for value in values
3477 )
3478 else:
3479 replacement_expression = ", ".join(
3480 self.render_literal_value(value, parameter.type)
3481 for value in values
3482 )
3484 return (), replacement_expression
3486 def _literal_execute_expanding_parameter(self, name, parameter, values):
3487 if parameter.literal_execute:
3488 return self._literal_execute_expanding_parameter_literal_binds(
3489 parameter, values
3490 )
3492 dialect = self.dialect
3493 typ_dialect_impl = parameter.type._unwrapped_dialect_impl(dialect)
3495 if self._numeric_binds:
3496 bind_template = self.compilation_bindtemplate
3497 else:
3498 bind_template = self.bindtemplate
3500 if (
3501 self.dialect._bind_typing_render_casts
3502 and typ_dialect_impl.render_bind_cast
3503 ):
3505 def _render_bindtemplate(name):
3506 return self.render_bind_cast(
3507 parameter.type,
3508 typ_dialect_impl,
3509 bind_template % {"name": name},
3510 )
3512 else:
3514 def _render_bindtemplate(name):
3515 return bind_template % {"name": name}
3517 if not values:
3518 to_update = []
3519 if typ_dialect_impl._is_tuple_type:
3520 replacement_expression = self.visit_empty_set_op_expr(
3521 parameter.type.types, parameter.expand_op
3522 )
3523 else:
3524 replacement_expression = self.visit_empty_set_op_expr(
3525 [parameter.type], parameter.expand_op
3526 )
3528 elif typ_dialect_impl._is_tuple_type or (
3529 typ_dialect_impl._isnull
3530 and isinstance(values[0], collections_abc.Sequence)
3531 and not isinstance(values[0], (str, bytes))
3532 ):
3533 assert not typ_dialect_impl._is_array
3534 to_update = [
3535 ("%s_%s_%s" % (name, i, j), value)
3536 for i, tuple_element in enumerate(values, 1)
3537 for j, value in enumerate(tuple_element, 1)
3538 ]
3540 replacement_expression = (
3541 "VALUES " if dialect.tuple_in_values else ""
3542 ) + ", ".join(
3543 "(%s)"
3544 % (
3545 ", ".join(
3546 _render_bindtemplate(
3547 to_update[i * len(tuple_element) + j][0]
3548 )
3549 for j, value in enumerate(tuple_element)
3550 )
3551 )
3552 for i, tuple_element in enumerate(values)
3553 )
3554 else:
3555 to_update = [
3556 ("%s_%s" % (name, i), value)
3557 for i, value in enumerate(values, 1)
3558 ]
3559 replacement_expression = ", ".join(
3560 _render_bindtemplate(key) for key, value in to_update
3561 )
3563 return to_update, replacement_expression
3565 def visit_binary(
3566 self,
3567 binary,
3568 override_operator=None,
3569 eager_grouping=False,
3570 from_linter=None,
3571 lateral_from_linter=None,
3572 **kw,
3573 ):
3574 if from_linter and operators.is_comparison(binary.operator):
3575 if lateral_from_linter is not None:
3576 enclosing_lateral = kw["enclosing_lateral"]
3577 lateral_from_linter.edges.update(
3578 itertools.product(
3579 _de_clone(
3580 binary.left._from_objects + [enclosing_lateral]
3581 ),
3582 _de_clone(
3583 binary.right._from_objects + [enclosing_lateral]
3584 ),
3585 )
3586 )
3587 else:
3588 from_linter.edges.update(
3589 itertools.product(
3590 _de_clone(binary.left._from_objects),
3591 _de_clone(binary.right._from_objects),
3592 )
3593 )
3595 # don't allow "? = ?" to render
3596 if (
3597 self.ansi_bind_rules
3598 and isinstance(binary.left, elements.BindParameter)
3599 and isinstance(binary.right, elements.BindParameter)
3600 ):
3601 kw["literal_execute"] = True
3603 operator_ = override_operator or binary.operator
3604 disp = self._get_operator_dispatch(operator_, "binary", None)
3605 if disp:
3606 return disp(binary, operator_, **kw)
3607 else:
3608 try:
3609 opstring = OPERATORS[operator_]
3610 except KeyError as err:
3611 raise exc.UnsupportedCompilationError(self, operator_) from err
3612 else:
3613 return self._generate_generic_binary(
3614 binary,
3615 opstring,
3616 from_linter=from_linter,
3617 lateral_from_linter=lateral_from_linter,
3618 **kw,
3619 )
3621 def visit_function_as_comparison_op_binary(self, element, operator, **kw):
3622 return self.process(element.sql_function, **kw)
3624 def visit_mod_binary(self, binary, operator, **kw):
3625 if self.preparer._double_percents:
3626 return (
3627 self.process(binary.left, **kw)
3628 + " %% "
3629 + self.process(binary.right, **kw)
3630 )
3631 else:
3632 return (
3633 self.process(binary.left, **kw)
3634 + " % "
3635 + self.process(binary.right, **kw)
3636 )
3638 def visit_custom_op_binary(self, element, operator, **kw):
3639 if operator.visit_name:
3640 disp = self._get_custom_operator_dispatch(operator, "binary")
3641 if disp:
3642 return disp(element, operator, **kw)
3644 kw["eager_grouping"] = operator.eager_grouping
3645 return self._generate_generic_binary(
3646 element,
3647 " " + self.escape_literal_column(operator.opstring) + " ",
3648 **kw,
3649 )
3651 def visit_custom_op_unary_operator(self, element, operator, **kw):
3652 if operator.visit_name:
3653 disp = self._get_custom_operator_dispatch(operator, "unary")
3654 if disp:
3655 return disp(element, operator, **kw)
3657 return self._generate_generic_unary_operator(
3658 element, self.escape_literal_column(operator.opstring) + " ", **kw
3659 )
3661 def visit_custom_op_unary_modifier(self, element, operator, **kw):
3662 if operator.visit_name:
3663 disp = self._get_custom_operator_dispatch(operator, "unary")
3664 if disp:
3665 return disp(element, operator, **kw)
3667 return self._generate_generic_unary_modifier(
3668 element, " " + self.escape_literal_column(operator.opstring), **kw
3669 )
3671 def _generate_generic_binary(
3672 self,
3673 binary: BinaryExpression[Any],
3674 opstring: str,
3675 eager_grouping: bool = False,
3676 **kw: Any,
3677 ) -> str:
3678 _in_operator_expression = kw.get("_in_operator_expression", False)
3680 kw["_in_operator_expression"] = True
3681 kw["_binary_op"] = binary.operator
3682 text = (
3683 binary.left._compiler_dispatch(
3684 self, eager_grouping=eager_grouping, **kw
3685 )
3686 + opstring
3687 + binary.right._compiler_dispatch(
3688 self, eager_grouping=eager_grouping, **kw
3689 )
3690 )
3692 if _in_operator_expression and eager_grouping:
3693 text = "(%s)" % text
3694 return text
3696 def _generate_generic_unary_operator(self, unary, opstring, **kw):
3697 return opstring + unary.element._compiler_dispatch(self, **kw)
3699 def _generate_generic_unary_modifier(self, unary, opstring, **kw):
3700 return unary.element._compiler_dispatch(self, **kw) + opstring
3702 @util.memoized_property
3703 def _like_percent_literal(self):
3704 return elements.literal_column("'%'", type_=sqltypes.STRINGTYPE)
3706 def visit_ilike_case_insensitive_operand(self, element, **kw):
3707 return f"lower({element.element._compiler_dispatch(self, **kw)})"
3709 def visit_contains_op_binary(self, binary, operator, **kw):
3710 binary = binary._clone()
3711 percent = self._like_percent_literal
3712 binary.right = percent.concat(binary.right).concat(percent)
3713 return self.visit_like_op_binary(binary, operator, **kw)
3715 def visit_not_contains_op_binary(self, binary, operator, **kw):
3716 binary = binary._clone()
3717 percent = self._like_percent_literal
3718 binary.right = percent.concat(binary.right).concat(percent)
3719 return self.visit_not_like_op_binary(binary, operator, **kw)
3721 def visit_icontains_op_binary(self, binary, operator, **kw):
3722 binary = binary._clone()
3723 percent = self._like_percent_literal
3724 binary.left = ilike_case_insensitive(binary.left)
3725 binary.right = percent.concat(
3726 ilike_case_insensitive(binary.right)
3727 ).concat(percent)
3728 return self.visit_ilike_op_binary(binary, operator, **kw)
3730 def visit_not_icontains_op_binary(self, binary, operator, **kw):
3731 binary = binary._clone()
3732 percent = self._like_percent_literal
3733 binary.left = ilike_case_insensitive(binary.left)
3734 binary.right = percent.concat(
3735 ilike_case_insensitive(binary.right)
3736 ).concat(percent)
3737 return self.visit_not_ilike_op_binary(binary, operator, **kw)
3739 def visit_startswith_op_binary(self, binary, operator, **kw):
3740 binary = binary._clone()
3741 percent = self._like_percent_literal
3742 binary.right = percent._rconcat(binary.right)
3743 return self.visit_like_op_binary(binary, operator, **kw)
3745 def visit_not_startswith_op_binary(self, binary, operator, **kw):
3746 binary = binary._clone()
3747 percent = self._like_percent_literal
3748 binary.right = percent._rconcat(binary.right)
3749 return self.visit_not_like_op_binary(binary, operator, **kw)
3751 def visit_istartswith_op_binary(self, binary, operator, **kw):
3752 binary = binary._clone()
3753 percent = self._like_percent_literal
3754 binary.left = ilike_case_insensitive(binary.left)
3755 binary.right = percent._rconcat(ilike_case_insensitive(binary.right))
3756 return self.visit_ilike_op_binary(binary, operator, **kw)
3758 def visit_not_istartswith_op_binary(self, binary, operator, **kw):
3759 binary = binary._clone()
3760 percent = self._like_percent_literal
3761 binary.left = ilike_case_insensitive(binary.left)
3762 binary.right = percent._rconcat(ilike_case_insensitive(binary.right))
3763 return self.visit_not_ilike_op_binary(binary, operator, **kw)
3765 def visit_endswith_op_binary(self, binary, operator, **kw):
3766 binary = binary._clone()
3767 percent = self._like_percent_literal
3768 binary.right = percent.concat(binary.right)
3769 return self.visit_like_op_binary(binary, operator, **kw)
3771 def visit_not_endswith_op_binary(self, binary, operator, **kw):
3772 binary = binary._clone()
3773 percent = self._like_percent_literal
3774 binary.right = percent.concat(binary.right)
3775 return self.visit_not_like_op_binary(binary, operator, **kw)
3777 def visit_iendswith_op_binary(self, binary, operator, **kw):
3778 binary = binary._clone()
3779 percent = self._like_percent_literal
3780 binary.left = ilike_case_insensitive(binary.left)
3781 binary.right = percent.concat(ilike_case_insensitive(binary.right))
3782 return self.visit_ilike_op_binary(binary, operator, **kw)
3784 def visit_not_iendswith_op_binary(self, binary, operator, **kw):
3785 binary = binary._clone()
3786 percent = self._like_percent_literal
3787 binary.left = ilike_case_insensitive(binary.left)
3788 binary.right = percent.concat(ilike_case_insensitive(binary.right))
3789 return self.visit_not_ilike_op_binary(binary, operator, **kw)
3791 def visit_like_op_binary(self, binary, operator, **kw):
3792 escape = binary.modifiers.get("escape", None)
3794 return "%s LIKE %s" % (
3795 binary.left._compiler_dispatch(self, **kw),
3796 binary.right._compiler_dispatch(self, **kw),
3797 ) + (
3798 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
3799 if escape is not None
3800 else ""
3801 )
3803 def visit_not_like_op_binary(self, binary, operator, **kw):
3804 escape = binary.modifiers.get("escape", None)
3805 return "%s NOT LIKE %s" % (
3806 binary.left._compiler_dispatch(self, **kw),
3807 binary.right._compiler_dispatch(self, **kw),
3808 ) + (
3809 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
3810 if escape is not None
3811 else ""
3812 )
3814 def visit_ilike_op_binary(self, binary, operator, **kw):
3815 if operator is operators.ilike_op:
3816 binary = binary._clone()
3817 binary.left = ilike_case_insensitive(binary.left)
3818 binary.right = ilike_case_insensitive(binary.right)
3819 # else we assume ilower() has been applied
3821 return self.visit_like_op_binary(binary, operator, **kw)
3823 def visit_not_ilike_op_binary(self, binary, operator, **kw):
3824 if operator is operators.not_ilike_op:
3825 binary = binary._clone()
3826 binary.left = ilike_case_insensitive(binary.left)
3827 binary.right = ilike_case_insensitive(binary.right)
3828 # else we assume ilower() has been applied
3830 return self.visit_not_like_op_binary(binary, operator, **kw)
3832 def visit_between_op_binary(self, binary, operator, **kw):
3833 symmetric = binary.modifiers.get("symmetric", False)
3834 return self._generate_generic_binary(
3835 binary, " BETWEEN SYMMETRIC " if symmetric else " BETWEEN ", **kw
3836 )
3838 def visit_not_between_op_binary(self, binary, operator, **kw):
3839 symmetric = binary.modifiers.get("symmetric", False)
3840 return self._generate_generic_binary(
3841 binary,
3842 " NOT BETWEEN SYMMETRIC " if symmetric else " NOT BETWEEN ",
3843 **kw,
3844 )
3846 def visit_regexp_match_op_binary(
3847 self, binary: BinaryExpression[Any], operator: Any, **kw: Any
3848 ) -> str:
3849 raise exc.CompileError(
3850 "%s dialect does not support regular expressions"
3851 % self.dialect.name
3852 )
3854 def visit_not_regexp_match_op_binary(
3855 self, binary: BinaryExpression[Any], operator: Any, **kw: Any
3856 ) -> str:
3857 raise exc.CompileError(
3858 "%s dialect does not support regular expressions"
3859 % self.dialect.name
3860 )
3862 def visit_regexp_replace_op_binary(
3863 self, binary: BinaryExpression[Any], operator: Any, **kw: Any
3864 ) -> str:
3865 raise exc.CompileError(
3866 "%s dialect does not support regular expression replacements"
3867 % self.dialect.name
3868 )
3870 def visit_dmltargetcopy(self, element, *, bindmarkers=None, **kw):
3871 if bindmarkers is None:
3872 raise exc.CompileError(
3873 "DML target objects may only be used with "
3874 "compiled INSERT or UPDATE statements"
3875 )
3877 bindmarkers[element.column.key] = element
3878 return f"__BINDMARKER_~~{element.column.key}~~"
3880 def visit_bindparam(
3881 self,
3882 bindparam,
3883 within_columns_clause=False,
3884 literal_binds=False,
3885 skip_bind_expression=False,
3886 literal_execute=False,
3887 render_postcompile=False,
3888 is_upsert_set=False,
3889 **kwargs,
3890 ):
3891 # Detect parametrized bindparams in upsert SET clause for issue #13130
3892 if (
3893 is_upsert_set
3894 and bindparam.value is None
3895 and bindparam.callable is None
3896 and self._insertmanyvalues is not None
3897 ):
3898 self._insertmanyvalues = self._insertmanyvalues._replace(
3899 has_upsert_bound_parameters=True
3900 )
3902 if not skip_bind_expression:
3903 impl = bindparam.type.dialect_impl(self.dialect)
3904 if impl._has_bind_expression:
3905 bind_expression = impl.bind_expression(bindparam)
3906 wrapped = self.process(
3907 bind_expression,
3908 skip_bind_expression=True,
3909 within_columns_clause=within_columns_clause,
3910 literal_binds=literal_binds and not bindparam.expanding,
3911 literal_execute=literal_execute,
3912 render_postcompile=render_postcompile,
3913 **kwargs,
3914 )
3915 if bindparam.expanding:
3916 # for postcompile w/ expanding, move the "wrapped" part
3917 # of this into the inside
3919 m = re.match(
3920 r"^(.*)\(__\[POSTCOMPILE_(\S+?)\]\)(.*)$", wrapped
3921 )
3922 assert m, "unexpected format for expanding parameter"
3923 wrapped = "(__[POSTCOMPILE_%s~~%s~~REPL~~%s~~])" % (
3924 m.group(2),
3925 m.group(1),
3926 m.group(3),
3927 )
3929 if literal_binds:
3930 ret = self.render_literal_bindparam(
3931 bindparam,
3932 within_columns_clause=True,
3933 bind_expression_template=wrapped,
3934 **kwargs,
3935 )
3936 return f"({ret})"
3938 return wrapped
3940 if not literal_binds:
3941 literal_execute = (
3942 literal_execute
3943 or bindparam.literal_execute
3944 or (within_columns_clause and self.ansi_bind_rules)
3945 )
3946 post_compile = literal_execute or bindparam.expanding
3947 else:
3948 post_compile = False
3950 if literal_binds:
3951 ret = self.render_literal_bindparam(
3952 bindparam, within_columns_clause=True, **kwargs
3953 )
3954 if bindparam.expanding:
3955 ret = f"({ret})"
3956 return ret
3958 name = self._truncate_bindparam(bindparam)
3960 if name in self.binds:
3961 existing = self.binds[name]
3962 if existing is not bindparam:
3963 if (
3964 (existing.unique or bindparam.unique)
3965 and not existing.proxy_set.intersection(
3966 bindparam.proxy_set
3967 )
3968 and not existing._cloned_set.intersection(
3969 bindparam._cloned_set
3970 )
3971 ):
3972 raise exc.CompileError(
3973 "Bind parameter '%s' conflicts with "
3974 "unique bind parameter of the same name" % name
3975 )
3976 elif existing.expanding != bindparam.expanding:
3977 raise exc.CompileError(
3978 "Can't reuse bound parameter name '%s' in both "
3979 "'expanding' (e.g. within an IN expression) and "
3980 "non-expanding contexts. If this parameter is to "
3981 "receive a list/array value, set 'expanding=True' on "
3982 "it for expressions that aren't IN, otherwise use "
3983 "a different parameter name." % (name,)
3984 )
3985 elif existing._is_crud or bindparam._is_crud:
3986 if existing._is_crud and bindparam._is_crud:
3987 # TODO: this condition is not well understood.
3988 # see tests in test/sql/test_update.py
3989 raise exc.CompileError(
3990 "Encountered unsupported case when compiling an "
3991 "INSERT or UPDATE statement. If this is a "
3992 "multi-table "
3993 "UPDATE statement, please provide string-named "
3994 "arguments to the "
3995 "values() method with distinct names; support for "
3996 "multi-table UPDATE statements that "
3997 "target multiple tables for UPDATE is very "
3998 "limited",
3999 )
4000 else:
4001 raise exc.CompileError(
4002 f"bindparam() name '{bindparam.key}' is reserved "
4003 "for automatic usage in the VALUES or SET "
4004 "clause of this "
4005 "insert/update statement. Please use a "
4006 "name other than column name when using "
4007 "bindparam() "
4008 "with insert() or update() (for example, "
4009 f"'b_{bindparam.key}')."
4010 )
4012 self.binds[bindparam.key] = self.binds[name] = bindparam
4014 # if we are given a cache key that we're going to match against,
4015 # relate the bindparam here to one that is most likely present
4016 # in the "extracted params" portion of the cache key. this is used
4017 # to set up a positional mapping that is used to determine the
4018 # correct parameters for a subsequent use of this compiled with
4019 # a different set of parameter values. here, we accommodate for
4020 # parameters that may have been cloned both before and after the cache
4021 # key was been generated.
4022 ckbm_tuple = self._cache_key_bind_match
4024 if ckbm_tuple:
4025 ckbm, cksm = ckbm_tuple
4026 for bp in bindparam._cloned_set:
4027 if bp.key in cksm:
4028 cb = cksm[bp.key]
4029 ckbm[cb].append(bindparam)
4031 if bindparam.isoutparam:
4032 self.has_out_parameters = True
4034 if post_compile:
4035 if render_postcompile:
4036 self._render_postcompile = True
4038 if literal_execute:
4039 self.literal_execute_params |= {bindparam}
4040 else:
4041 self.post_compile_params |= {bindparam}
4043 ret = self.bindparam_string(
4044 name,
4045 post_compile=post_compile,
4046 expanding=bindparam.expanding,
4047 bindparam_type=bindparam.type,
4048 **kwargs,
4049 )
4051 if bindparam.expanding:
4052 ret = f"({ret})"
4054 return ret
4056 def render_bind_cast(self, type_, dbapi_type, sqltext):
4057 raise NotImplementedError()
4059 def render_literal_bindparam(
4060 self,
4061 bindparam,
4062 render_literal_value=NO_ARG,
4063 bind_expression_template=None,
4064 **kw,
4065 ):
4066 if render_literal_value is not NO_ARG:
4067 value = render_literal_value
4068 else:
4069 if bindparam.value is None and bindparam.callable is None:
4070 op = kw.get("_binary_op", None)
4071 if op and op not in (operators.is_, operators.is_not):
4072 util.warn_limited(
4073 "Bound parameter '%s' rendering literal NULL in a SQL "
4074 "expression; comparisons to NULL should not use "
4075 "operators outside of 'is' or 'is not'",
4076 (bindparam.key,),
4077 )
4078 return self.process(sqltypes.NULLTYPE, **kw)
4079 value = bindparam.effective_value
4081 if bindparam.expanding:
4082 leep = self._literal_execute_expanding_parameter_literal_binds
4083 to_update, replacement_expr = leep(
4084 bindparam,
4085 value,
4086 bind_expression_template=bind_expression_template,
4087 )
4088 return replacement_expr
4089 else:
4090 return self.render_literal_value(value, bindparam.type)
4092 def render_literal_value(
4093 self, value: Any, type_: sqltypes.TypeEngine[Any]
4094 ) -> str:
4095 """Render the value of a bind parameter as a quoted literal.
4097 This is used for statement sections that do not accept bind parameters
4098 on the target driver/database.
4100 This should be implemented by subclasses using the quoting services
4101 of the DBAPI.
4103 """
4105 if value is None and not type_.should_evaluate_none:
4106 # issue #10535 - handle NULL in the compiler without placing
4107 # this onto each type, except for "evaluate None" types
4108 # (e.g. JSON)
4109 return self.process(elements.Null._instance())
4111 processor = type_._cached_literal_processor(self.dialect)
4112 if processor:
4113 try:
4114 return processor(value)
4115 except Exception as e:
4116 raise exc.CompileError(
4117 f"Could not render literal value "
4118 f'"{sql_util._repr_single_value(value)}" '
4119 f"with datatype "
4120 f"{type_}; see parent stack trace for "
4121 "more detail."
4122 ) from e
4124 else:
4125 raise exc.CompileError(
4126 f"No literal value renderer is available for literal value "
4127 f'"{sql_util._repr_single_value(value)}" '
4128 f"with datatype {type_}"
4129 )
4131 def _truncate_bindparam(self, bindparam):
4132 if bindparam in self.bind_names:
4133 return self.bind_names[bindparam]
4135 bind_name = bindparam.key
4136 if isinstance(bind_name, elements._truncated_label):
4137 bind_name = self._truncated_identifier("bindparam", bind_name)
4139 # add to bind_names for translation
4140 self.bind_names[bindparam] = bind_name
4142 return bind_name
4144 def _truncated_identifier(
4145 self, ident_class: str, name: _truncated_label
4146 ) -> str:
4147 if (ident_class, name) in self.truncated_names:
4148 return self.truncated_names[(ident_class, name)]
4150 anonname = name.apply_map(self.anon_map)
4152 if len(anonname) > self.label_length - 6:
4153 counter = self._truncated_counters.get(ident_class, 1)
4154 truncname = (
4155 anonname[0 : max(self.label_length - 6, 0)]
4156 + "_"
4157 + hex(counter)[2:]
4158 )
4159 self._truncated_counters[ident_class] = counter + 1
4160 else:
4161 truncname = anonname
4162 self.truncated_names[(ident_class, name)] = truncname
4163 return truncname
4165 def _anonymize(self, name: str) -> str:
4166 return name % self.anon_map
4168 def bindparam_string(
4169 self,
4170 name: str,
4171 post_compile: bool = False,
4172 expanding: bool = False,
4173 escaped_from: Optional[str] = None,
4174 bindparam_type: Optional[TypeEngine[Any]] = None,
4175 accumulate_bind_names: Optional[Set[str]] = None,
4176 visited_bindparam: Optional[List[str]] = None,
4177 **kw: Any,
4178 ) -> str:
4179 # TODO: accumulate_bind_names is passed by crud.py to gather
4180 # names on a per-value basis, visited_bindparam is passed by
4181 # visit_insert() to collect all parameters in the statement.
4182 # see if this gathering can be simplified somehow
4183 if accumulate_bind_names is not None:
4184 accumulate_bind_names.add(name)
4185 if visited_bindparam is not None:
4186 visited_bindparam.append(name)
4188 if not escaped_from:
4189 if self._bind_translate_re.search(name):
4190 # not quite the translate use case as we want to
4191 # also get a quick boolean if we even found
4192 # unusual characters in the name
4193 new_name = self._bind_translate_re.sub(
4194 lambda m: self._bind_translate_chars[m.group(0)],
4195 name,
4196 )
4197 escaped_from = name
4198 name = new_name
4200 if escaped_from:
4201 self.escaped_bind_names = self.escaped_bind_names.union(
4202 {escaped_from: name}
4203 )
4204 if post_compile:
4205 ret = "__[POSTCOMPILE_%s]" % name
4206 if expanding:
4207 # for expanding, bound parameters or literal values will be
4208 # rendered per item
4209 return ret
4211 # otherwise, for non-expanding "literal execute", apply
4212 # bind casts as determined by the datatype
4213 if bindparam_type is not None:
4214 type_impl = bindparam_type._unwrapped_dialect_impl(
4215 self.dialect
4216 )
4217 if type_impl.render_literal_cast:
4218 ret = self.render_bind_cast(bindparam_type, type_impl, ret)
4219 return ret
4220 elif self.state is CompilerState.COMPILING:
4221 ret = self.compilation_bindtemplate % {"name": name}
4222 else:
4223 ret = self.bindtemplate % {"name": name}
4225 if (
4226 bindparam_type is not None
4227 and self.dialect._bind_typing_render_casts
4228 ):
4229 type_impl = bindparam_type._unwrapped_dialect_impl(self.dialect)
4230 if type_impl.render_bind_cast:
4231 ret = self.render_bind_cast(bindparam_type, type_impl, ret)
4233 return ret
4235 def _dispatch_independent_ctes(self, stmt, kw):
4236 local_kw = kw.copy()
4237 local_kw.pop("cte_opts", None)
4238 for cte, opt in zip(
4239 stmt._independent_ctes, stmt._independent_ctes_opts
4240 ):
4241 cte._compiler_dispatch(self, cte_opts=opt, **local_kw)
4243 def visit_cte(
4244 self,
4245 cte: CTE,
4246 asfrom: bool = False,
4247 ashint: bool = False,
4248 fromhints: Optional[_FromHintsType] = None,
4249 visiting_cte: Optional[CTE] = None,
4250 from_linter: Optional[FromLinter] = None,
4251 cte_opts: selectable._CTEOpts = selectable._CTEOpts(False),
4252 **kwargs: Any,
4253 ) -> Optional[str]:
4254 self_ctes = self._init_cte_state()
4255 assert self_ctes is self.ctes
4257 kwargs["visiting_cte"] = cte
4259 cte_name = cte.name
4261 if isinstance(cte_name, elements._truncated_label):
4262 cte_name = self._truncated_identifier("alias", cte_name)
4264 is_new_cte = True
4265 embedded_in_current_named_cte = False
4267 _reference_cte = cte._get_reference_cte()
4269 nesting = cte.nesting or cte_opts.nesting
4271 # check for CTE already encountered
4272 if _reference_cte in self.level_name_by_cte:
4273 cte_level, _, existing_cte_opts = self.level_name_by_cte[
4274 _reference_cte
4275 ]
4276 assert _ == cte_name
4278 cte_level_name = (cte_level, cte_name)
4279 existing_cte = self.ctes_by_level_name[cte_level_name]
4281 # check if we are receiving it here with a specific
4282 # "nest_here" location; if so, move it to this location
4284 if cte_opts.nesting:
4285 if existing_cte_opts.nesting:
4286 raise exc.CompileError(
4287 "CTE is stated as 'nest_here' in "
4288 "more than one location"
4289 )
4291 old_level_name = (cte_level, cte_name)
4292 cte_level = len(self.stack) if nesting else 1
4293 cte_level_name = new_level_name = (cte_level, cte_name)
4295 del self.ctes_by_level_name[old_level_name]
4296 self.ctes_by_level_name[new_level_name] = existing_cte
4297 self.level_name_by_cte[_reference_cte] = new_level_name + (
4298 cte_opts,
4299 )
4301 else:
4302 cte_level = len(self.stack) if nesting else 1
4303 cte_level_name = (cte_level, cte_name)
4305 if cte_level_name in self.ctes_by_level_name:
4306 existing_cte = self.ctes_by_level_name[cte_level_name]
4307 else:
4308 existing_cte = None
4310 if existing_cte is not None:
4311 embedded_in_current_named_cte = visiting_cte is existing_cte
4313 # we've generated a same-named CTE that we are enclosed in,
4314 # or this is the same CTE. just return the name.
4315 if cte is existing_cte._restates or cte is existing_cte:
4316 is_new_cte = False
4317 elif existing_cte is cte._restates:
4318 # we've generated a same-named CTE that is
4319 # enclosed in us - we take precedence, so
4320 # discard the text for the "inner".
4321 del self_ctes[existing_cte]
4323 existing_cte_reference_cte = existing_cte._get_reference_cte()
4325 assert existing_cte_reference_cte is _reference_cte
4326 assert existing_cte_reference_cte is existing_cte
4328 del self.level_name_by_cte[existing_cte_reference_cte]
4329 else:
4330 if (
4331 # if the two CTEs have the same hash, which we expect
4332 # here means that one/both is an annotated of the other
4333 (hash(cte) == hash(existing_cte))
4334 # or...
4335 or (
4336 (
4337 # if they are clones, i.e. they came from the ORM
4338 # or some other visit method
4339 cte._is_clone_of is not None
4340 or existing_cte._is_clone_of is not None
4341 )
4342 # and are deep-copy identical
4343 and cte.compare(existing_cte)
4344 )
4345 ):
4346 # then consider these two CTEs the same
4347 is_new_cte = False
4348 else:
4349 # otherwise these are two CTEs that either will render
4350 # differently, or were indicated separately by the user,
4351 # with the same name
4352 raise exc.CompileError(
4353 "Multiple, unrelated CTEs found with "
4354 "the same name: %r" % cte_name
4355 )
4357 if not asfrom and not is_new_cte:
4358 return None
4360 if cte._cte_alias is not None:
4361 pre_alias_cte = cte._cte_alias
4362 cte_pre_alias_name = cte._cte_alias.name
4363 if isinstance(cte_pre_alias_name, elements._truncated_label):
4364 cte_pre_alias_name = self._truncated_identifier(
4365 "alias", cte_pre_alias_name
4366 )
4367 else:
4368 pre_alias_cte = cte
4369 cte_pre_alias_name = None
4371 if is_new_cte:
4372 self.ctes_by_level_name[cte_level_name] = cte
4373 self.level_name_by_cte[_reference_cte] = cte_level_name + (
4374 cte_opts,
4375 )
4377 if pre_alias_cte not in self.ctes:
4378 self.visit_cte(pre_alias_cte, **kwargs)
4380 if not cte_pre_alias_name and cte not in self_ctes:
4381 if cte.recursive:
4382 self.ctes_recursive = True
4383 text = self.preparer.format_alias(cte, cte_name)
4384 if cte.recursive or cte.element.name_cte_columns:
4385 col_source = cte.element
4387 # TODO: can we get at the .columns_plus_names collection
4388 # that is already (or will be?) generated for the SELECT
4389 # rather than calling twice?
4390 recur_cols = [
4391 # TODO: proxy_name is not technically safe,
4392 # see test_cte->
4393 # test_with_recursive_no_name_currently_buggy. not
4394 # clear what should be done with such a case
4395 fallback_label_name or proxy_name
4396 for (
4397 _,
4398 proxy_name,
4399 fallback_label_name,
4400 c,
4401 repeated,
4402 ) in (col_source._generate_columns_plus_names(True))
4403 if not repeated
4404 ]
4406 text += "(%s)" % (
4407 ", ".join(
4408 self.preparer.format_label_name(
4409 ident, anon_map=self.anon_map
4410 )
4411 for ident in recur_cols
4412 )
4413 )
4415 assert kwargs.get("subquery", False) is False
4417 if not self.stack:
4418 # toplevel, this is a stringify of the
4419 # cte directly. just compile the inner
4420 # the way alias() does.
4421 return cte.element._compiler_dispatch(
4422 self, asfrom=asfrom, **kwargs
4423 )
4424 else:
4425 prefixes = self._generate_prefixes(
4426 cte, cte._prefixes, **kwargs
4427 )
4428 inner = cte.element._compiler_dispatch(
4429 self, asfrom=True, **kwargs
4430 )
4432 text += " AS %s\n(%s)" % (prefixes, inner)
4434 if cte._suffixes:
4435 text += " " + self._generate_prefixes(
4436 cte, cte._suffixes, **kwargs
4437 )
4439 self_ctes[cte] = text
4441 if asfrom:
4442 if from_linter:
4443 from_linter.froms[cte._de_clone()] = cte_name
4445 if not is_new_cte and embedded_in_current_named_cte:
4446 return self.preparer.format_alias(cte, cte_name)
4448 if cte_pre_alias_name:
4449 text = self.preparer.format_alias(cte, cte_pre_alias_name)
4450 if self.preparer._requires_quotes(cte_name):
4451 cte_name = self.preparer.quote(cte_name)
4452 text += self.get_render_as_alias_suffix(cte_name)
4453 return text # type: ignore[no-any-return]
4454 else:
4455 return self.preparer.format_alias(cte, cte_name)
4457 return None
4459 def visit_table_valued_alias(self, element, **kw):
4460 if element.joins_implicitly:
4461 kw["from_linter"] = None
4462 if element._is_lateral:
4463 return self.visit_lateral(element, **kw)
4464 else:
4465 return self.visit_alias(element, **kw)
4467 def visit_table_valued_column(self, element, **kw):
4468 return self.visit_column(element, **kw)
4470 def visit_alias(
4471 self,
4472 alias,
4473 asfrom=False,
4474 ashint=False,
4475 iscrud=False,
4476 fromhints=None,
4477 subquery=False,
4478 lateral=False,
4479 enclosing_alias=None,
4480 from_linter=None,
4481 within_tstring=False,
4482 **kwargs,
4483 ):
4484 if lateral:
4485 if "enclosing_lateral" not in kwargs:
4486 # if lateral is set and enclosing_lateral is not
4487 # present, we assume we are being called directly
4488 # from visit_lateral() and we need to set enclosing_lateral.
4489 assert alias._is_lateral
4490 kwargs["enclosing_lateral"] = alias
4492 # for lateral objects, we track a second from_linter that is...
4493 # lateral! to the level above us.
4494 if (
4495 from_linter
4496 and "lateral_from_linter" not in kwargs
4497 and "enclosing_lateral" in kwargs
4498 ):
4499 kwargs["lateral_from_linter"] = from_linter
4501 if enclosing_alias is not None and enclosing_alias.element is alias:
4502 inner = alias.element._compiler_dispatch(
4503 self,
4504 asfrom=asfrom,
4505 ashint=ashint,
4506 iscrud=iscrud,
4507 fromhints=fromhints,
4508 lateral=lateral,
4509 enclosing_alias=alias,
4510 **kwargs,
4511 )
4512 if subquery and (asfrom or lateral):
4513 inner = "(%s)" % (inner,)
4514 return inner
4515 else:
4516 kwargs["enclosing_alias"] = alias
4518 if asfrom or ashint or within_tstring:
4519 if isinstance(alias.name, elements._truncated_label):
4520 alias_name = self._truncated_identifier("alias", alias.name)
4521 else:
4522 alias_name = alias.name
4524 if ashint:
4525 return self.preparer.format_alias(alias, alias_name)
4526 elif asfrom or within_tstring:
4527 if from_linter:
4528 from_linter.froms[alias._de_clone()] = alias_name
4530 inner = alias.element._compiler_dispatch(
4531 self, asfrom=True, lateral=lateral, **kwargs
4532 )
4533 if subquery:
4534 inner = "(%s)" % (inner,)
4536 ret = inner + self.get_render_as_alias_suffix(
4537 self.preparer.format_alias(alias, alias_name)
4538 )
4540 if alias._supports_derived_columns and alias._render_derived:
4541 ret += "(%s)" % (
4542 ", ".join(
4543 "%s%s"
4544 % (
4545 self.preparer.quote(col.name),
4546 (
4547 " %s"
4548 % self.dialect.type_compiler_instance.process(
4549 col.type, **kwargs
4550 )
4551 if alias._render_derived_w_types
4552 else ""
4553 ),
4554 )
4555 for col in alias.c
4556 )
4557 )
4559 if fromhints and alias in fromhints:
4560 ret = self.format_from_hint_text(
4561 ret, alias, fromhints[alias], iscrud
4562 )
4564 return ret
4565 else:
4566 # note we cancel the "subquery" flag here as well
4567 return alias.element._compiler_dispatch(
4568 self, lateral=lateral, **kwargs
4569 )
4571 def visit_subquery(self, subquery, **kw):
4572 kw["subquery"] = True
4573 return self.visit_alias(subquery, **kw)
4575 def visit_lateral(self, lateral_, **kw):
4576 kw["lateral"] = True
4577 return "LATERAL %s" % self.visit_alias(lateral_, **kw)
4579 def visit_tablesample(self, tablesample, asfrom=False, **kw):
4580 text = "%s TABLESAMPLE %s" % (
4581 self.visit_alias(tablesample, asfrom=True, **kw),
4582 tablesample._get_method()._compiler_dispatch(self, **kw),
4583 )
4585 if tablesample.seed is not None:
4586 text += " REPEATABLE (%s)" % (
4587 tablesample.seed._compiler_dispatch(self, **kw)
4588 )
4590 return text
4592 def _render_values(self, element, **kw):
4593 kw.setdefault("literal_binds", element.literal_binds)
4594 tuples = ", ".join(
4595 self.process(
4596 elements.Tuple(
4597 types=element._column_types, *elem
4598 ).self_group(),
4599 **kw,
4600 )
4601 for chunk in element._data
4602 for elem in chunk
4603 )
4604 return f"VALUES {tuples}"
4606 def visit_values(
4607 self, element, asfrom=False, from_linter=None, visiting_cte=None, **kw
4608 ):
4610 if element._independent_ctes:
4611 self._dispatch_independent_ctes(element, kw)
4613 v = self._render_values(element, **kw)
4615 if element._unnamed:
4616 name = None
4617 elif isinstance(element.name, elements._truncated_label):
4618 name = self._truncated_identifier("values", element.name)
4619 else:
4620 name = element.name
4622 if element._is_lateral:
4623 lateral = "LATERAL "
4624 else:
4625 lateral = ""
4627 if asfrom:
4628 if from_linter:
4629 from_linter.froms[element._de_clone()] = (
4630 name if name is not None else "(unnamed VALUES element)"
4631 )
4633 if visiting_cte is not None and visiting_cte.element is element:
4634 if element._is_lateral:
4635 raise exc.CompileError(
4636 "Can't use a LATERAL VALUES expression inside of a CTE"
4637 )
4638 elif name:
4639 kw["include_table"] = False
4640 v = "%s(%s)%s (%s)" % (
4641 lateral,
4642 v,
4643 self.get_render_as_alias_suffix(self.preparer.quote(name)),
4644 (
4645 ", ".join(
4646 c._compiler_dispatch(self, **kw)
4647 for c in element.columns
4648 )
4649 ),
4650 )
4651 else:
4652 v = "%s(%s)" % (lateral, v)
4653 return v
4655 def visit_scalar_values(self, element, **kw):
4656 return f"({self._render_values(element, **kw)})"
4658 def get_render_as_alias_suffix(self, alias_name_text):
4659 return " AS " + alias_name_text
4661 def _add_to_result_map(
4662 self,
4663 keyname: str,
4664 name: str,
4665 objects: Tuple[Any, ...],
4666 type_: TypeEngine[Any],
4667 ) -> None:
4669 # note objects must be non-empty for cursor.py to handle the
4670 # collection properly
4671 assert objects
4673 if keyname is None or keyname == "*":
4674 self._ordered_columns = False
4675 self._ad_hoc_textual = True
4676 if type_._is_tuple_type:
4677 raise exc.CompileError(
4678 "Most backends don't support SELECTing "
4679 "from a tuple() object. If this is an ORM query, "
4680 "consider using the Bundle object."
4681 )
4682 self._result_columns.append(
4683 ResultColumnsEntry(keyname, name, objects, type_)
4684 )
4686 def _label_returning_column(
4687 self, stmt, column, populate_result_map, column_clause_args=None, **kw
4688 ):
4689 """Render a column with necessary labels inside of a RETURNING clause.
4691 This method is provided for individual dialects in place of calling
4692 the _label_select_column method directly, so that the two use cases
4693 of RETURNING vs. SELECT can be disambiguated going forward.
4695 .. versionadded:: 1.4.21
4697 """
4698 return self._label_select_column(
4699 None,
4700 column,
4701 populate_result_map,
4702 False,
4703 {} if column_clause_args is None else column_clause_args,
4704 **kw,
4705 )
4707 def _label_select_column(
4708 self,
4709 select,
4710 column,
4711 populate_result_map,
4712 asfrom,
4713 column_clause_args,
4714 name=None,
4715 proxy_name=None,
4716 fallback_label_name=None,
4717 within_columns_clause=True,
4718 column_is_repeated=False,
4719 need_column_expressions=False,
4720 include_table=True,
4721 ):
4722 """produce labeled columns present in a select()."""
4723 impl = column.type.dialect_impl(self.dialect)
4725 if impl._has_column_expression and (
4726 need_column_expressions or populate_result_map
4727 ):
4728 col_expr = impl.column_expression(column)
4729 else:
4730 col_expr = column
4732 if populate_result_map:
4733 # pass an "add_to_result_map" callable into the compilation
4734 # of embedded columns. this collects information about the
4735 # column as it will be fetched in the result and is coordinated
4736 # with cursor.description when the query is executed.
4737 add_to_result_map = self._add_to_result_map
4739 # if the SELECT statement told us this column is a repeat,
4740 # wrap the callable with one that prevents the addition of the
4741 # targets
4742 if column_is_repeated:
4743 _add_to_result_map = add_to_result_map
4745 def add_to_result_map(keyname, name, objects, type_):
4746 _add_to_result_map(keyname, name, (keyname,), type_)
4748 # if we redefined col_expr for type expressions, wrap the
4749 # callable with one that adds the original column to the targets
4750 elif col_expr is not column:
4751 _add_to_result_map = add_to_result_map
4753 def add_to_result_map(keyname, name, objects, type_):
4754 _add_to_result_map(
4755 keyname, name, (column,) + objects, type_
4756 )
4758 else:
4759 add_to_result_map = None
4761 # this method is used by some of the dialects for RETURNING,
4762 # which has different inputs. _label_returning_column was added
4763 # as the better target for this now however for 1.4 we will keep
4764 # _label_select_column directly compatible with this use case.
4765 # these assertions right now set up the current expected inputs
4766 assert within_columns_clause, (
4767 "_label_select_column is only relevant within "
4768 "the columns clause of a SELECT or RETURNING"
4769 )
4770 result_expr: elements.Label[Any] | _CompileLabel
4772 if isinstance(column, elements.Label):
4773 if col_expr is not column:
4774 result_expr = _CompileLabel(
4775 col_expr, column.name, alt_names=(column.element,)
4776 )
4777 else:
4778 result_expr = col_expr
4780 elif name:
4781 # here, _columns_plus_names has determined there's an explicit
4782 # label name we need to use. this is the default for
4783 # tablenames_plus_columnnames as well as when columns are being
4784 # deduplicated on name
4786 assert (
4787 proxy_name is not None
4788 ), "proxy_name is required if 'name' is passed"
4790 result_expr = _CompileLabel(
4791 col_expr,
4792 name,
4793 alt_names=(
4794 proxy_name,
4795 # this is a hack to allow legacy result column lookups
4796 # to work as they did before; this goes away in 2.0.
4797 # TODO: this only seems to be tested indirectly
4798 # via test/orm/test_deprecations.py. should be a
4799 # resultset test for this
4800 column._tq_label,
4801 ),
4802 )
4803 else:
4804 # determine here whether this column should be rendered in
4805 # a labelled context or not, as we were given no required label
4806 # name from the caller. Here we apply heuristics based on the kind
4807 # of SQL expression involved.
4809 if col_expr is not column:
4810 # type-specific expression wrapping the given column,
4811 # so we render a label
4812 render_with_label = True
4813 elif isinstance(column, elements.ColumnClause):
4814 # table-bound column, we render its name as a label if we are
4815 # inside of a subquery only
4816 render_with_label = (
4817 asfrom
4818 and not column.is_literal
4819 and column.table is not None
4820 )
4821 elif isinstance(column, elements.TextClause):
4822 render_with_label = False
4823 elif isinstance(column, elements.UnaryExpression):
4824 # unary expression. notes added as of #12681
4825 #
4826 # By convention, the visit_unary() method
4827 # itself does not add an entry to the result map, and relies
4828 # upon either the inner expression creating a result map
4829 # entry, or if not, by creating a label here that produces
4830 # the result map entry. Where that happens is based on whether
4831 # or not the element immediately inside the unary is a
4832 # NamedColumn subclass or not.
4833 #
4834 # Now, this also impacts how the SELECT is written; if
4835 # we decide to generate a label here, we get the usual
4836 # "~(x+y) AS anon_1" thing in the columns clause. If we
4837 # don't, we don't get an AS at all, we get like
4838 # "~table.column".
4839 #
4840 # But here is the important thing as of modernish (like 1.4)
4841 # versions of SQLAlchemy - **whether or not the AS <label>
4842 # is present in the statement is not actually important**.
4843 # We target result columns **positionally** for a fully
4844 # compiled ``Select()`` object; before 1.4 we needed those
4845 # labels to match in cursor.description etc etc but now it
4846 # really doesn't matter.
4847 # So really, we could set render_with_label True in all cases.
4848 # Or we could just have visit_unary() populate the result map
4849 # in all cases.
4850 #
4851 # What we're doing here is strictly trying to not rock the
4852 # boat too much with when we do/don't render "AS label";
4853 # labels being present helps in the edge cases that we
4854 # "fall back" to named cursor.description matching, labels
4855 # not being present for columns keeps us from having awkward
4856 # phrases like "SELECT DISTINCT table.x AS x".
4857 render_with_label = (
4858 (
4859 # exception case to detect if we render "not boolean"
4860 # as "not <col>" for native boolean or "<col> = 1"
4861 # for non-native boolean. this is controlled by
4862 # visit_is_<true|false>_unary_operator
4863 column.operator
4864 in (operators.is_false, operators.is_true)
4865 and not self.dialect.supports_native_boolean
4866 )
4867 or column._wraps_unnamed_column()
4868 or asfrom
4869 )
4870 elif (
4871 # general class of expressions that don't have a SQL-column
4872 # addressable name. includes scalar selects, bind parameters,
4873 # SQL functions, others
4874 not isinstance(column, elements.NamedColumn)
4875 # deeper check that indicates there's no natural "name" to
4876 # this element, which accommodates for custom SQL constructs
4877 # that might have a ".name" attribute (but aren't SQL
4878 # functions) but are not implementing this more recently added
4879 # base class. in theory the "NamedColumn" check should be
4880 # enough, however here we seek to maintain legacy behaviors
4881 # as well.
4882 and column._non_anon_label is None
4883 ):
4884 render_with_label = True
4885 else:
4886 render_with_label = False
4888 if render_with_label:
4889 if not fallback_label_name:
4890 # used by the RETURNING case right now. we generate it
4891 # here as 3rd party dialects may be referring to
4892 # _label_select_column method directly instead of the
4893 # just-added _label_returning_column method
4894 assert not column_is_repeated
4895 fallback_label_name = column._anon_name_label
4897 fallback_label_name = (
4898 elements._truncated_label(fallback_label_name)
4899 if not isinstance(
4900 fallback_label_name, elements._truncated_label
4901 )
4902 else fallback_label_name
4903 )
4905 result_expr = _CompileLabel(
4906 col_expr, fallback_label_name, alt_names=(proxy_name,)
4907 )
4908 else:
4909 result_expr = col_expr
4911 column_clause_args.update(
4912 within_columns_clause=within_columns_clause,
4913 add_to_result_map=add_to_result_map,
4914 include_table=include_table,
4915 within_tstring=False,
4916 )
4917 return result_expr._compiler_dispatch(self, **column_clause_args)
4919 def format_from_hint_text(self, sqltext, table, hint, iscrud):
4920 hinttext = self.get_from_hint_text(table, hint)
4921 if hinttext:
4922 sqltext += " " + hinttext
4923 return sqltext
4925 def get_select_hint_text(self, byfroms):
4926 return None
4928 def get_from_hint_text(
4929 self, table: FromClause, text: Optional[str]
4930 ) -> Optional[str]:
4931 return None
4933 def get_crud_hint_text(self, table, text):
4934 return None
4936 def get_statement_hint_text(self, hint_texts):
4937 return " ".join(hint_texts)
4939 _default_stack_entry: _CompilerStackEntry
4941 if not typing.TYPE_CHECKING:
4942 _default_stack_entry = util.immutabledict(
4943 [("correlate_froms", frozenset()), ("asfrom_froms", frozenset())]
4944 )
4946 def _display_froms_for_select(
4947 self, select_stmt, asfrom, lateral=False, **kw
4948 ):
4949 # utility method to help external dialects
4950 # get the correct from list for a select.
4951 # specifically the oracle dialect needs this feature
4952 # right now.
4953 toplevel = not self.stack
4954 entry = self._default_stack_entry if toplevel else self.stack[-1]
4956 compile_state = select_stmt._compile_state_factory(select_stmt, self)
4958 correlate_froms = entry["correlate_froms"]
4959 asfrom_froms = entry["asfrom_froms"]
4961 if asfrom and not lateral:
4962 froms = compile_state._get_display_froms(
4963 explicit_correlate_froms=correlate_froms.difference(
4964 asfrom_froms
4965 ),
4966 implicit_correlate_froms=(),
4967 )
4968 else:
4969 froms = compile_state._get_display_froms(
4970 explicit_correlate_froms=correlate_froms,
4971 implicit_correlate_froms=asfrom_froms,
4972 )
4973 return froms
4975 translate_select_structure: Any = None
4976 """if not ``None``, should be a callable which accepts ``(select_stmt,
4977 **kw)`` and returns a select object. this is used for structural changes
4978 mostly to accommodate for LIMIT/OFFSET schemes
4980 """
4982 def visit_select(
4983 self,
4984 select_stmt,
4985 asfrom=False,
4986 insert_into=False,
4987 fromhints=None,
4988 compound_index=None,
4989 select_wraps_for=None,
4990 lateral=False,
4991 from_linter=None,
4992 **kwargs,
4993 ):
4994 assert select_wraps_for is None, (
4995 "SQLAlchemy 1.4 requires use of "
4996 "the translate_select_structure hook for structural "
4997 "translations of SELECT objects"
4998 )
4999 if self._collect_params:
5000 self._add_to_params(select_stmt)
5002 # initial setup of SELECT. the compile_state_factory may now
5003 # be creating a totally different SELECT from the one that was
5004 # passed in. for ORM use this will convert from an ORM-state
5005 # SELECT to a regular "Core" SELECT. other composed operations
5006 # such as computation of joins will be performed.
5008 kwargs["within_columns_clause"] = False
5010 compile_state = select_stmt._compile_state_factory(
5011 select_stmt, self, **kwargs
5012 )
5013 kwargs["ambiguous_table_name_map"] = (
5014 compile_state._ambiguous_table_name_map
5015 )
5017 select_stmt = compile_state.statement
5019 toplevel = not self.stack
5021 if toplevel and not self.compile_state:
5022 self.compile_state = compile_state
5024 is_embedded_select = compound_index is not None or insert_into
5026 # translate step for Oracle, SQL Server which often need to
5027 # restructure the SELECT to allow for LIMIT/OFFSET and possibly
5028 # other conditions
5029 if self.translate_select_structure:
5030 new_select_stmt = self.translate_select_structure(
5031 select_stmt, asfrom=asfrom, **kwargs
5032 )
5034 # if SELECT was restructured, maintain a link to the originals
5035 # and assemble a new compile state
5036 if new_select_stmt is not select_stmt:
5037 compile_state_wraps_for = compile_state
5038 select_wraps_for = select_stmt
5039 select_stmt = new_select_stmt
5041 compile_state = select_stmt._compile_state_factory(
5042 select_stmt, self, **kwargs
5043 )
5044 select_stmt = compile_state.statement
5046 entry = self._default_stack_entry if toplevel else self.stack[-1]
5048 populate_result_map = need_column_expressions = (
5049 toplevel
5050 or entry.get("need_result_map_for_compound", False)
5051 or entry.get("need_result_map_for_nested", False)
5052 )
5054 # indicates there is a CompoundSelect in play and we are not the
5055 # first select
5056 if compound_index:
5057 populate_result_map = False
5059 # this was first proposed as part of #3372; however, it is not
5060 # reached in current tests and could possibly be an assertion
5061 # instead.
5062 if not populate_result_map and "add_to_result_map" in kwargs:
5063 del kwargs["add_to_result_map"]
5065 froms = self._setup_select_stack(
5066 select_stmt, compile_state, entry, asfrom, lateral, compound_index
5067 )
5069 column_clause_args = kwargs.copy()
5070 column_clause_args.update(
5071 {"within_label_clause": False, "within_columns_clause": False}
5072 )
5074 text = "SELECT " # we're off to a good start !
5076 if select_stmt._post_select_clause is not None:
5077 psc = self.process(select_stmt._post_select_clause, **kwargs)
5078 if psc is not None:
5079 text += psc + " "
5081 if select_stmt._hints:
5082 hint_text, byfrom = self._setup_select_hints(select_stmt)
5083 if hint_text:
5084 text += hint_text + " "
5085 else:
5086 byfrom = None
5088 if select_stmt._independent_ctes:
5089 self._dispatch_independent_ctes(select_stmt, kwargs)
5091 if select_stmt._prefixes:
5092 text += self._generate_prefixes(
5093 select_stmt, select_stmt._prefixes, **kwargs
5094 )
5096 text += self.get_select_precolumns(select_stmt, **kwargs)
5098 if select_stmt._pre_columns_clause is not None:
5099 pcc = self.process(select_stmt._pre_columns_clause, **kwargs)
5100 if pcc is not None:
5101 text += pcc + " "
5103 # the actual list of columns to print in the SELECT column list.
5104 inner_columns = [
5105 c
5106 for c in [
5107 self._label_select_column(
5108 select_stmt,
5109 column,
5110 populate_result_map,
5111 asfrom,
5112 column_clause_args,
5113 name=name,
5114 proxy_name=proxy_name,
5115 fallback_label_name=fallback_label_name,
5116 column_is_repeated=repeated,
5117 need_column_expressions=need_column_expressions,
5118 )
5119 for (
5120 name,
5121 proxy_name,
5122 fallback_label_name,
5123 column,
5124 repeated,
5125 ) in compile_state.columns_plus_names
5126 ]
5127 if c is not None
5128 ]
5130 if populate_result_map and select_wraps_for is not None:
5131 # if this select was generated from translate_select,
5132 # rewrite the targeted columns in the result map
5134 translate = dict(
5135 zip(
5136 [
5137 name
5138 for (
5139 key,
5140 proxy_name,
5141 fallback_label_name,
5142 name,
5143 repeated,
5144 ) in compile_state.columns_plus_names
5145 ],
5146 [
5147 name
5148 for (
5149 key,
5150 proxy_name,
5151 fallback_label_name,
5152 name,
5153 repeated,
5154 ) in compile_state_wraps_for.columns_plus_names
5155 ],
5156 )
5157 )
5159 self._result_columns = [
5160 ResultColumnsEntry(
5161 key, name, tuple(translate.get(o, o) for o in obj), type_
5162 )
5163 for key, name, obj, type_ in self._result_columns
5164 ]
5166 text = self._compose_select_body(
5167 text,
5168 select_stmt,
5169 compile_state,
5170 inner_columns,
5171 froms,
5172 byfrom,
5173 toplevel,
5174 kwargs,
5175 )
5177 if select_stmt._post_body_clause is not None:
5178 pbc = self.process(select_stmt._post_body_clause, **kwargs)
5179 if pbc:
5180 text += " " + pbc
5182 if select_stmt._statement_hints:
5183 per_dialect = [
5184 ht
5185 for (dialect_name, ht) in select_stmt._statement_hints
5186 if dialect_name in ("*", self.dialect.name)
5187 ]
5188 if per_dialect:
5189 text += " " + self.get_statement_hint_text(per_dialect)
5191 # In compound query, CTEs are shared at the compound level
5192 if self.ctes and (not is_embedded_select or toplevel):
5193 nesting_level = len(self.stack) if not toplevel else None
5194 text = self._render_cte_clause(nesting_level=nesting_level) + text
5196 if select_stmt._suffixes:
5197 text += " " + self._generate_prefixes(
5198 select_stmt, select_stmt._suffixes, **kwargs
5199 )
5201 self.stack.pop(-1)
5203 return text
5205 def _setup_select_hints(
5206 self, select: Select[Unpack[TupleAny]]
5207 ) -> Tuple[str, _FromHintsType]:
5208 byfrom = {
5209 from_: hinttext
5210 % {"name": from_._compiler_dispatch(self, ashint=True)}
5211 for (from_, dialect), hinttext in select._hints.items()
5212 if dialect in ("*", self.dialect.name)
5213 }
5214 hint_text = self.get_select_hint_text(byfrom)
5215 return hint_text, byfrom
5217 def _setup_select_stack(
5218 self, select, compile_state, entry, asfrom, lateral, compound_index
5219 ):
5220 correlate_froms = entry["correlate_froms"]
5221 asfrom_froms = entry["asfrom_froms"]
5223 if compound_index == 0:
5224 entry["select_0"] = select
5225 elif compound_index:
5226 select_0 = entry["select_0"]
5227 numcols = len(select_0._all_selected_columns)
5229 if len(compile_state.columns_plus_names) != numcols:
5230 raise exc.CompileError(
5231 "All selectables passed to "
5232 "CompoundSelect must have identical numbers of "
5233 "columns; select #%d has %d columns, select "
5234 "#%d has %d"
5235 % (
5236 1,
5237 numcols,
5238 compound_index + 1,
5239 len(select._all_selected_columns),
5240 )
5241 )
5243 if asfrom and not lateral:
5244 froms = compile_state._get_display_froms(
5245 explicit_correlate_froms=correlate_froms.difference(
5246 asfrom_froms
5247 ),
5248 implicit_correlate_froms=(),
5249 )
5250 else:
5251 froms = compile_state._get_display_froms(
5252 explicit_correlate_froms=correlate_froms,
5253 implicit_correlate_froms=asfrom_froms,
5254 )
5256 new_correlate_froms = set(_from_objects(*froms))
5257 all_correlate_froms = new_correlate_froms.union(correlate_froms)
5259 new_entry: _CompilerStackEntry = {
5260 "asfrom_froms": new_correlate_froms,
5261 "correlate_froms": all_correlate_froms,
5262 "selectable": select,
5263 "compile_state": compile_state,
5264 }
5265 self.stack.append(new_entry)
5267 return froms
5269 def _compose_select_body(
5270 self,
5271 text,
5272 select,
5273 compile_state,
5274 inner_columns,
5275 froms,
5276 byfrom,
5277 toplevel,
5278 kwargs,
5279 ):
5280 text += ", ".join(inner_columns)
5282 if self.linting & COLLECT_CARTESIAN_PRODUCTS:
5283 from_linter = FromLinter({}, set())
5284 warn_linting = self.linting & WARN_LINTING
5285 if toplevel:
5286 self.from_linter = from_linter
5287 else:
5288 from_linter = None
5289 warn_linting = False
5291 # adjust the whitespace for no inner columns, part of #9440,
5292 # so that a no-col SELECT comes out as "SELECT WHERE..." or
5293 # "SELECT FROM ...".
5294 # while it would be better to have built the SELECT starting string
5295 # without trailing whitespace first, then add whitespace only if inner
5296 # cols were present, this breaks compatibility with various custom
5297 # compilation schemes that are currently being tested.
5298 if not inner_columns:
5299 text = text.rstrip()
5301 if froms:
5302 text += " \nFROM "
5304 if select._hints:
5305 text += ", ".join(
5306 [
5307 f._compiler_dispatch(
5308 self,
5309 asfrom=True,
5310 fromhints=byfrom,
5311 from_linter=from_linter,
5312 **kwargs,
5313 )
5314 for f in froms
5315 ]
5316 )
5317 else:
5318 text += ", ".join(
5319 [
5320 f._compiler_dispatch(
5321 self,
5322 asfrom=True,
5323 from_linter=from_linter,
5324 **kwargs,
5325 )
5326 for f in froms
5327 ]
5328 )
5329 else:
5330 text += self.default_from()
5332 if select._where_criteria:
5333 t = self._generate_delimited_and_list(
5334 select._where_criteria, from_linter=from_linter, **kwargs
5335 )
5336 if t:
5337 text += " \nWHERE " + t
5339 if warn_linting:
5340 assert from_linter is not None
5341 from_linter.warn()
5343 if select._group_by_clauses:
5344 text += self.group_by_clause(select, **kwargs)
5346 if select._having_criteria:
5347 t = self._generate_delimited_and_list(
5348 select._having_criteria, **kwargs
5349 )
5350 if t:
5351 text += " \nHAVING " + t
5353 if select._post_criteria_clause is not None:
5354 pcc = self.process(select._post_criteria_clause, **kwargs)
5355 if pcc is not None:
5356 text += " \n" + pcc
5358 if select._order_by_clauses:
5359 text += self.order_by_clause(select, **kwargs)
5361 if select._has_row_limiting_clause:
5362 text += self._row_limit_clause(select, **kwargs)
5364 if select._for_update_arg is not None:
5365 text += self.for_update_clause(select, **kwargs)
5367 return text
5369 def _generate_prefixes(self, stmt, prefixes, **kw):
5370 clause = " ".join(
5371 prefix._compiler_dispatch(self, **kw)
5372 for prefix, dialect_name in prefixes
5373 if dialect_name in (None, "*") or dialect_name == self.dialect.name
5374 )
5375 if clause:
5376 clause += " "
5377 return clause
5379 def _render_cte_clause(
5380 self,
5381 nesting_level=None,
5382 include_following_stack=False,
5383 ):
5384 """
5385 include_following_stack
5386 Also render the nesting CTEs on the next stack. Useful for
5387 SQL structures like UNION or INSERT that can wrap SELECT
5388 statements containing nesting CTEs.
5389 """
5390 if not self.ctes:
5391 return ""
5393 ctes: MutableMapping[CTE, str]
5395 if nesting_level and nesting_level > 1:
5396 ctes = util.OrderedDict()
5397 for cte in list(self.ctes.keys()):
5398 cte_level, cte_name, cte_opts = self.level_name_by_cte[
5399 cte._get_reference_cte()
5400 ]
5401 nesting = cte.nesting or cte_opts.nesting
5402 is_rendered_level = cte_level == nesting_level or (
5403 include_following_stack and cte_level == nesting_level + 1
5404 )
5405 if not (nesting and is_rendered_level):
5406 continue
5408 ctes[cte] = self.ctes[cte]
5410 else:
5411 ctes = self.ctes
5413 if not ctes:
5414 return ""
5415 ctes_recursive = any([cte.recursive for cte in ctes])
5417 cte_text = self.get_cte_preamble(ctes_recursive) + " "
5418 cte_text += ", \n".join([txt for txt in ctes.values()])
5419 cte_text += "\n "
5421 if nesting_level and nesting_level > 1:
5422 for cte in list(ctes.keys()):
5423 cte_level, cte_name, cte_opts = self.level_name_by_cte[
5424 cte._get_reference_cte()
5425 ]
5426 del self.ctes[cte]
5427 del self.ctes_by_level_name[(cte_level, cte_name)]
5428 del self.level_name_by_cte[cte._get_reference_cte()]
5430 return cte_text
5432 def get_cte_preamble(self, recursive):
5433 if recursive:
5434 return "WITH RECURSIVE"
5435 else:
5436 return "WITH"
5438 def get_select_precolumns(self, select: Select[Any], **kw: Any) -> str:
5439 """Called when building a ``SELECT`` statement, position is just
5440 before column list.
5442 """
5443 if select._distinct_on:
5444 util.warn_deprecated(
5445 "DISTINCT ON is currently supported only by the PostgreSQL "
5446 "dialect. Use of DISTINCT ON for other backends is currently "
5447 "silently ignored, however this usage is deprecated, and will "
5448 "raise CompileError in a future release for all backends "
5449 "that do not support this syntax.",
5450 version="1.4",
5451 )
5452 return "DISTINCT " if select._distinct else ""
5454 def group_by_clause(self, select, **kw):
5455 """allow dialects to customize how GROUP BY is rendered."""
5457 group_by = self._generate_delimited_list(
5458 select._group_by_clauses, OPERATORS[operators.comma_op], **kw
5459 )
5460 if group_by:
5461 return " GROUP BY " + group_by
5462 else:
5463 return ""
5465 def order_by_clause(self, select, **kw):
5466 """allow dialects to customize how ORDER BY is rendered."""
5468 order_by = self._generate_delimited_list(
5469 select._order_by_clauses, OPERATORS[operators.comma_op], **kw
5470 )
5472 if order_by:
5473 return " ORDER BY " + order_by
5474 else:
5475 return ""
5477 def for_update_clause(self, select, **kw):
5478 return " FOR UPDATE"
5480 def returning_clause(
5481 self,
5482 stmt: UpdateBase,
5483 returning_cols: Sequence[_ColumnsClauseElement],
5484 *,
5485 populate_result_map: bool,
5486 **kw: Any,
5487 ) -> str:
5488 columns = [
5489 self._label_returning_column(
5490 stmt,
5491 column,
5492 populate_result_map,
5493 fallback_label_name=fallback_label_name,
5494 column_is_repeated=repeated,
5495 name=name,
5496 proxy_name=proxy_name,
5497 **kw,
5498 )
5499 for (
5500 name,
5501 proxy_name,
5502 fallback_label_name,
5503 column,
5504 repeated,
5505 ) in stmt._generate_columns_plus_names(
5506 True, cols=base._select_iterables(returning_cols)
5507 )
5508 ]
5510 return "RETURNING " + ", ".join(columns)
5512 def limit_clause(self, select, **kw):
5513 text = ""
5514 if select._limit_clause is not None:
5515 text += "\n LIMIT " + self.process(select._limit_clause, **kw)
5516 if select._offset_clause is not None:
5517 if select._limit_clause is None:
5518 text += "\n LIMIT -1"
5519 text += " OFFSET " + self.process(select._offset_clause, **kw)
5520 return text
5522 def fetch_clause(
5523 self,
5524 select,
5525 fetch_clause=None,
5526 require_offset=False,
5527 use_literal_execute_for_simple_int=False,
5528 **kw,
5529 ):
5530 if fetch_clause is None:
5531 fetch_clause = select._fetch_clause
5532 fetch_clause_options = select._fetch_clause_options
5533 else:
5534 fetch_clause_options = {"percent": False, "with_ties": False}
5536 text = ""
5538 if select._offset_clause is not None:
5539 offset_clause = select._offset_clause
5540 if (
5541 use_literal_execute_for_simple_int
5542 and select._simple_int_clause(offset_clause)
5543 ):
5544 offset_clause = offset_clause.render_literal_execute()
5545 offset_str = self.process(offset_clause, **kw)
5546 text += "\n OFFSET %s ROWS" % offset_str
5547 elif require_offset:
5548 text += "\n OFFSET 0 ROWS"
5550 if fetch_clause is not None:
5551 if (
5552 use_literal_execute_for_simple_int
5553 and select._simple_int_clause(fetch_clause)
5554 ):
5555 fetch_clause = fetch_clause.render_literal_execute()
5556 text += "\n FETCH FIRST %s%s ROWS %s" % (
5557 self.process(fetch_clause, **kw),
5558 " PERCENT" if fetch_clause_options["percent"] else "",
5559 "WITH TIES" if fetch_clause_options["with_ties"] else "ONLY",
5560 )
5561 return text
5563 def visit_table(
5564 self,
5565 table,
5566 asfrom=False,
5567 iscrud=False,
5568 ashint=False,
5569 fromhints=None,
5570 use_schema=True,
5571 from_linter=None,
5572 ambiguous_table_name_map=None,
5573 enclosing_alias=None,
5574 within_tstring=False,
5575 **kwargs,
5576 ):
5577 if from_linter:
5578 from_linter.froms[table] = table.fullname
5580 if asfrom or ashint or within_tstring:
5581 effective_schema = self.preparer.schema_for_object(table)
5583 if use_schema and effective_schema:
5584 ret = (
5585 self.preparer.quote_schema(effective_schema)
5586 + "."
5587 + self.preparer.quote(table.name)
5588 )
5589 else:
5590 ret = self.preparer.quote(table.name)
5592 if (
5593 (
5594 enclosing_alias is None
5595 or enclosing_alias.element is not table
5596 )
5597 and not effective_schema
5598 and ambiguous_table_name_map
5599 and table.name in ambiguous_table_name_map
5600 ):
5601 anon_name = self._truncated_identifier(
5602 "alias", ambiguous_table_name_map[table.name]
5603 )
5605 ret = ret + self.get_render_as_alias_suffix(
5606 self.preparer.format_alias(None, anon_name)
5607 )
5609 if fromhints and table in fromhints:
5610 ret = self.format_from_hint_text(
5611 ret, table, fromhints[table], iscrud
5612 )
5613 return ret
5614 else:
5615 return ""
5617 def visit_join(self, join, asfrom=False, from_linter=None, **kwargs):
5618 if from_linter:
5619 from_linter.edges.update(
5620 itertools.product(
5621 _de_clone(join.left._from_objects),
5622 _de_clone(join.right._from_objects),
5623 )
5624 )
5626 if join.full:
5627 join_type = " FULL OUTER JOIN "
5628 elif join.isouter:
5629 join_type = " LEFT OUTER JOIN "
5630 else:
5631 join_type = " JOIN "
5632 return (
5633 join.left._compiler_dispatch(
5634 self, asfrom=True, from_linter=from_linter, **kwargs
5635 )
5636 + join_type
5637 + join.right._compiler_dispatch(
5638 self, asfrom=True, from_linter=from_linter, **kwargs
5639 )
5640 + " ON "
5641 # TODO: likely need asfrom=True here?
5642 + join.onclause._compiler_dispatch(
5643 self, from_linter=from_linter, **kwargs
5644 )
5645 )
5647 def _setup_crud_hints(self, stmt, table_text):
5648 dialect_hints = {
5649 table: hint_text
5650 for (table, dialect), hint_text in stmt._hints.items()
5651 if dialect in ("*", self.dialect.name)
5652 }
5653 if stmt.table in dialect_hints:
5654 table_text = self.format_from_hint_text(
5655 table_text, stmt.table, dialect_hints[stmt.table], True
5656 )
5657 return dialect_hints, table_text
5659 # within the realm of "insertmanyvalues sentinel columns",
5660 # these lookups match different kinds of Column() configurations
5661 # to specific backend capabilities. they are broken into two
5662 # lookups, one for autoincrement columns and the other for non
5663 # autoincrement columns
5664 _sentinel_col_non_autoinc_lookup = util.immutabledict(
5665 {
5666 _SentinelDefaultCharacterization.CLIENTSIDE: (
5667 InsertmanyvaluesSentinelOpts._SUPPORTED_OR_NOT
5668 ),
5669 _SentinelDefaultCharacterization.SENTINEL_DEFAULT: (
5670 InsertmanyvaluesSentinelOpts._SUPPORTED_OR_NOT
5671 ),
5672 _SentinelDefaultCharacterization.NONE: (
5673 InsertmanyvaluesSentinelOpts._SUPPORTED_OR_NOT
5674 ),
5675 _SentinelDefaultCharacterization.IDENTITY: (
5676 InsertmanyvaluesSentinelOpts.IDENTITY
5677 ),
5678 _SentinelDefaultCharacterization.SEQUENCE: (
5679 InsertmanyvaluesSentinelOpts.SEQUENCE
5680 ),
5681 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION: (
5682 InsertmanyvaluesSentinelOpts.MONOTONIC_FUNCTION
5683 ),
5684 }
5685 )
5686 _sentinel_col_autoinc_lookup = _sentinel_col_non_autoinc_lookup.union(
5687 {
5688 _SentinelDefaultCharacterization.NONE: (
5689 InsertmanyvaluesSentinelOpts.AUTOINCREMENT
5690 ),
5691 }
5692 )
5694 def _get_sentinel_column_for_table(
5695 self, table: Table
5696 ) -> Optional[Sequence[Column[Any]]]:
5697 """given a :class:`.Table`, return a usable sentinel column or
5698 columns for this dialect if any.
5700 Return None if no sentinel columns could be identified, or raise an
5701 error if a column was marked as a sentinel explicitly but isn't
5702 compatible with this dialect.
5704 """
5706 sentinel_opts = self.dialect.insertmanyvalues_implicit_sentinel
5707 sentinel_characteristics = table._sentinel_column_characteristics
5709 sent_cols = sentinel_characteristics.columns
5711 if sent_cols is None:
5712 return None
5714 if sentinel_characteristics.is_autoinc:
5715 bitmask = self._sentinel_col_autoinc_lookup.get(
5716 sentinel_characteristics.default_characterization, 0
5717 )
5718 else:
5719 bitmask = self._sentinel_col_non_autoinc_lookup.get(
5720 sentinel_characteristics.default_characterization, 0
5721 )
5723 if sentinel_opts & bitmask:
5724 return sent_cols
5726 if sentinel_characteristics.is_explicit:
5727 # a column was explicitly marked as insert_sentinel=True,
5728 # however it is not compatible with this dialect. they should
5729 # not indicate this column as a sentinel if they need to include
5730 # this dialect.
5732 # TODO: do we want non-primary key explicit sentinel cols
5733 # that can gracefully degrade for some backends?
5734 # insert_sentinel="degrade" perhaps. not for the initial release.
5735 # I am hoping people are generally not dealing with this sentinel
5736 # business at all.
5738 # if is_explicit is True, there will be only one sentinel column.
5740 raise exc.InvalidRequestError(
5741 f"Column {sent_cols[0]} can't be explicitly "
5742 "marked as a sentinel column when using the "
5743 f"{self.dialect.name} dialect, as the "
5744 "particular type of default generation on this column is "
5745 "not currently compatible with this dialect's specific "
5746 f"INSERT..RETURNING syntax which can receive the "
5747 "server-generated value in "
5748 "a deterministic way. To remove this error, remove "
5749 "insert_sentinel=True from primary key autoincrement "
5750 "columns; these columns are automatically used as "
5751 "sentinels for supported dialects in any case."
5752 )
5754 return None
5756 def _deliver_insertmanyvalues_batches(
5757 self,
5758 statement: str,
5759 parameters: _DBAPIMultiExecuteParams,
5760 compiled_parameters: List[_MutableCoreSingleExecuteParams],
5761 generic_setinputsizes: Optional[_GenericSetInputSizesType],
5762 batch_size: int,
5763 sort_by_parameter_order: bool,
5764 schema_translate_map: Optional[SchemaTranslateMapType],
5765 ) -> Iterator[_InsertManyValuesBatch]:
5766 imv = self._insertmanyvalues
5767 assert imv is not None
5769 if not imv.sentinel_param_keys:
5770 _sentinel_from_params = None
5771 else:
5772 _sentinel_from_params = operator.itemgetter(
5773 *imv.sentinel_param_keys
5774 )
5776 lenparams = len(parameters)
5777 if imv.is_default_expr and not self.dialect.supports_default_metavalue:
5778 # backend doesn't support
5779 # INSERT INTO table (pk_col) VALUES (DEFAULT), (DEFAULT), ...
5780 # at the moment this is basically SQL Server due to
5781 # not being able to use DEFAULT for identity column
5782 # just yield out that many single statements! still
5783 # faster than a whole connection.execute() call ;)
5784 #
5785 # note we still are taking advantage of the fact that we know
5786 # we are using RETURNING. The generalized approach of fetching
5787 # cursor.lastrowid etc. still goes through the more heavyweight
5788 # "ExecutionContext per statement" system as it isn't usable
5789 # as a generic "RETURNING" approach
5790 use_row_at_a_time = True
5791 downgraded = False
5792 elif not self.dialect.supports_multivalues_insert or (
5793 sort_by_parameter_order
5794 and self._result_columns
5795 and (
5796 imv.sentinel_columns is None
5797 or (
5798 imv.includes_upsert_behaviors
5799 and not imv.embed_values_counter
5800 )
5801 )
5802 ):
5803 # deterministic order was requested and the compiler could
5804 # not organize sentinel columns for this dialect/statement.
5805 # use row at a time. Note: if embed_values_counter is True,
5806 # the counter itself provides the ordering capability we need,
5807 # so we can use batch mode even with upsert behaviors.
5808 use_row_at_a_time = True
5809 downgraded = True
5810 elif (
5811 imv.has_upsert_bound_parameters
5812 and not imv.embed_values_counter
5813 and self._result_columns
5814 ):
5815 # For upsert behaviors (ON CONFLICT DO UPDATE, etc.) with RETURNING
5816 # and parametrized bindparams in the SET clause, we must use
5817 # row-at-a-time. Batching multiple rows in a single statement
5818 # doesn't work when the SET clause contains bound parameters that
5819 # will receive different values per row, as there's only one SET
5820 # clause per statement. See issue #13130.
5821 use_row_at_a_time = True
5822 downgraded = True
5823 else:
5824 use_row_at_a_time = False
5825 downgraded = False
5827 if use_row_at_a_time:
5828 for batchnum, (param, compiled_param) in enumerate(
5829 cast(
5830 "Sequence[Tuple[_DBAPISingleExecuteParams, _MutableCoreSingleExecuteParams]]", # noqa: E501
5831 zip(parameters, compiled_parameters),
5832 ),
5833 1,
5834 ):
5835 yield _InsertManyValuesBatch(
5836 statement,
5837 param,
5838 generic_setinputsizes,
5839 [param],
5840 (
5841 [_sentinel_from_params(compiled_param)]
5842 if _sentinel_from_params
5843 else []
5844 ),
5845 1,
5846 batchnum,
5847 lenparams,
5848 sort_by_parameter_order,
5849 downgraded,
5850 )
5851 return
5853 if schema_translate_map:
5854 rst = functools.partial(
5855 self.preparer._render_schema_translates,
5856 schema_translate_map=schema_translate_map,
5857 )
5858 else:
5859 rst = None
5861 imv_single_values_expr = imv.single_values_expr
5862 if rst:
5863 imv_single_values_expr = rst(imv_single_values_expr)
5865 executemany_values = f"({imv_single_values_expr})"
5866 statement = statement.replace(executemany_values, "__EXECMANY_TOKEN__")
5868 # Use optional insertmanyvalues_max_parameters
5869 # to further shrink the batch size so that there are no more than
5870 # insertmanyvalues_max_parameters params.
5871 # Currently used by SQL Server, which limits statements to 2100 bound
5872 # parameters (actually 2099).
5873 max_params = self.dialect.insertmanyvalues_max_parameters
5874 if max_params:
5875 total_num_of_params = len(self.bind_names)
5876 num_params_per_batch = len(imv.insert_crud_params)
5877 num_params_outside_of_batch = (
5878 total_num_of_params - num_params_per_batch
5879 )
5880 batch_size = min(
5881 batch_size,
5882 (
5883 (max_params - num_params_outside_of_batch)
5884 // num_params_per_batch
5885 ),
5886 )
5888 batches = cast("List[Sequence[Any]]", list(parameters))
5889 compiled_batches = cast(
5890 "List[Sequence[Any]]", list(compiled_parameters)
5891 )
5893 processed_setinputsizes: Optional[_GenericSetInputSizesType] = None
5894 batchnum = 1
5895 total_batches = lenparams // batch_size + (
5896 1 if lenparams % batch_size else 0
5897 )
5899 insert_crud_params = imv.insert_crud_params
5900 assert insert_crud_params is not None
5902 if rst:
5903 insert_crud_params = [
5904 (col, key, rst(expr), st)
5905 for col, key, expr, st in insert_crud_params
5906 ]
5908 escaped_bind_names: Mapping[str, str]
5909 expand_pos_lower_index = expand_pos_upper_index = 0
5911 if not self.positional:
5912 if self.escaped_bind_names:
5913 escaped_bind_names = self.escaped_bind_names
5914 else:
5915 escaped_bind_names = {}
5917 all_keys = set(parameters[0])
5919 def apply_placeholders(keys, formatted):
5920 for key in keys:
5921 key = escaped_bind_names.get(key, key)
5922 formatted = formatted.replace(
5923 self.bindtemplate % {"name": key},
5924 self.bindtemplate
5925 % {"name": f"{key}__EXECMANY_INDEX__"},
5926 )
5927 return formatted
5929 if imv.embed_values_counter:
5930 imv_values_counter = ", _IMV_VALUES_COUNTER"
5931 else:
5932 imv_values_counter = ""
5933 formatted_values_clause = f"""({', '.join(
5934 apply_placeholders(bind_keys, formatted)
5935 for _, _, formatted, bind_keys in insert_crud_params
5936 )}{imv_values_counter})"""
5938 keys_to_replace = all_keys.intersection(
5939 escaped_bind_names.get(key, key)
5940 for _, _, _, bind_keys in insert_crud_params
5941 for key in bind_keys
5942 )
5943 base_parameters = {
5944 key: parameters[0][key]
5945 for key in all_keys.difference(keys_to_replace)
5946 }
5948 executemany_values_w_comma = ""
5949 else:
5950 formatted_values_clause = ""
5951 keys_to_replace = set()
5952 base_parameters = {}
5954 if imv.embed_values_counter:
5955 executemany_values_w_comma = (
5956 f"({imv_single_values_expr}, _IMV_VALUES_COUNTER), "
5957 )
5958 else:
5959 executemany_values_w_comma = f"({imv_single_values_expr}), "
5961 all_names_we_will_expand: Set[str] = set()
5962 for elem in imv.insert_crud_params:
5963 all_names_we_will_expand.update(elem[3])
5965 # get the start and end position in a particular list
5966 # of parameters where we will be doing the "expanding".
5967 # statements can have params on either side or both sides,
5968 # given RETURNING and CTEs
5969 if all_names_we_will_expand:
5970 positiontup = self.positiontup
5971 assert positiontup is not None
5973 all_expand_positions = {
5974 idx
5975 for idx, name in enumerate(positiontup)
5976 if name in all_names_we_will_expand
5977 }
5978 expand_pos_lower_index = min(all_expand_positions)
5979 expand_pos_upper_index = max(all_expand_positions) + 1
5980 assert (
5981 len(all_expand_positions)
5982 == expand_pos_upper_index - expand_pos_lower_index
5983 )
5985 if self._numeric_binds:
5986 escaped = re.escape(self._numeric_binds_identifier_char)
5987 executemany_values_w_comma = re.sub(
5988 rf"{escaped}\d+", "%s", executemany_values_w_comma
5989 )
5991 while batches:
5992 batch = batches[0:batch_size]
5993 compiled_batch = compiled_batches[0:batch_size]
5995 batches[0:batch_size] = []
5996 compiled_batches[0:batch_size] = []
5998 if batches:
5999 current_batch_size = batch_size
6000 else:
6001 current_batch_size = len(batch)
6003 if generic_setinputsizes:
6004 # if setinputsizes is present, expand this collection to
6005 # suit the batch length as well
6006 # currently this will be mssql+pyodbc for internal dialects
6007 processed_setinputsizes = [
6008 (new_key, len_, typ)
6009 for new_key, len_, typ in (
6010 (f"{key}_{index}", len_, typ)
6011 for index in range(current_batch_size)
6012 for key, len_, typ in generic_setinputsizes
6013 )
6014 ]
6016 replaced_parameters: Any
6017 if self.positional:
6018 num_ins_params = imv.num_positional_params_counted
6020 batch_iterator: Iterable[Sequence[Any]]
6021 extra_params_left: Sequence[Any]
6022 extra_params_right: Sequence[Any]
6024 if num_ins_params == len(batch[0]):
6025 extra_params_left = extra_params_right = ()
6026 batch_iterator = batch
6027 else:
6028 extra_params_left = batch[0][:expand_pos_lower_index]
6029 extra_params_right = batch[0][expand_pos_upper_index:]
6030 batch_iterator = (
6031 b[expand_pos_lower_index:expand_pos_upper_index]
6032 for b in batch
6033 )
6035 if imv.embed_values_counter:
6036 expanded_values_string = (
6037 "".join(
6038 executemany_values_w_comma.replace(
6039 "_IMV_VALUES_COUNTER", str(i)
6040 )
6041 for i, _ in enumerate(batch)
6042 )
6043 )[:-2]
6044 else:
6045 expanded_values_string = (
6046 (executemany_values_w_comma * current_batch_size)
6047 )[:-2]
6049 if self._numeric_binds and num_ins_params > 0:
6050 # numeric will always number the parameters inside of
6051 # VALUES (and thus order self.positiontup) to be higher
6052 # than non-VALUES parameters, no matter where in the
6053 # statement those non-VALUES parameters appear (this is
6054 # ensured in _process_numeric by numbering first all
6055 # params that are not in _values_bindparam)
6056 # therefore all extra params are always
6057 # on the left side and numbered lower than the VALUES
6058 # parameters
6059 assert not extra_params_right
6061 start = expand_pos_lower_index + 1
6062 end = num_ins_params * (current_batch_size) + start
6064 # need to format here, since statement may contain
6065 # unescaped %, while values_string contains just (%s, %s)
6066 positions = tuple(
6067 f"{self._numeric_binds_identifier_char}{i}"
6068 for i in range(start, end)
6069 )
6070 expanded_values_string = expanded_values_string % positions
6072 replaced_statement = statement.replace(
6073 "__EXECMANY_TOKEN__", expanded_values_string
6074 )
6076 replaced_parameters = tuple(
6077 itertools.chain.from_iterable(batch_iterator)
6078 )
6080 replaced_parameters = (
6081 extra_params_left
6082 + replaced_parameters
6083 + extra_params_right
6084 )
6086 else:
6087 replaced_values_clauses = []
6088 replaced_parameters = base_parameters.copy()
6090 for i, param in enumerate(batch):
6091 fmv = formatted_values_clause.replace(
6092 "EXECMANY_INDEX__", str(i)
6093 )
6094 if imv.embed_values_counter:
6095 fmv = fmv.replace("_IMV_VALUES_COUNTER", str(i))
6097 replaced_values_clauses.append(fmv)
6098 replaced_parameters.update(
6099 {f"{key}__{i}": param[key] for key in keys_to_replace}
6100 )
6102 replaced_statement = statement.replace(
6103 "__EXECMANY_TOKEN__",
6104 ", ".join(replaced_values_clauses),
6105 )
6107 yield _InsertManyValuesBatch(
6108 replaced_statement,
6109 replaced_parameters,
6110 processed_setinputsizes,
6111 batch,
6112 (
6113 [_sentinel_from_params(cb) for cb in compiled_batch]
6114 if _sentinel_from_params
6115 else []
6116 ),
6117 current_batch_size,
6118 batchnum,
6119 total_batches,
6120 sort_by_parameter_order,
6121 False,
6122 )
6123 batchnum += 1
6125 def visit_insert(
6126 self, insert_stmt, visited_bindparam=None, visiting_cte=None, **kw
6127 ):
6128 compile_state = insert_stmt._compile_state_factory(
6129 insert_stmt, self, **kw
6130 )
6131 insert_stmt = compile_state.statement
6133 if visiting_cte is not None:
6134 kw["visiting_cte"] = visiting_cte
6135 toplevel = False
6136 else:
6137 toplevel = not self.stack
6139 if toplevel:
6140 self.isinsert = True
6141 if not self.dml_compile_state:
6142 self.dml_compile_state = compile_state
6143 if not self.compile_state:
6144 self.compile_state = compile_state
6146 self.stack.append(
6147 {
6148 "correlate_froms": set(),
6149 "asfrom_froms": set(),
6150 "selectable": insert_stmt,
6151 }
6152 )
6154 counted_bindparam = 0
6156 # reset any incoming "visited_bindparam" collection
6157 visited_bindparam = None
6159 # for positional, insertmanyvalues needs to know how many
6160 # bound parameters are in the VALUES sequence; there's no simple
6161 # rule because default expressions etc. can have zero or more
6162 # params inside them. After multiple attempts to figure this out,
6163 # this very simplistic "count after" works and is
6164 # likely the least amount of callcounts, though looks clumsy
6165 if self.positional and visiting_cte is None:
6166 # if we are inside a CTE, don't count parameters
6167 # here since they won't be for insertmanyvalues. keep
6168 # visited_bindparam at None so no counting happens.
6169 # see #9173
6170 visited_bindparam = []
6172 crud_params_struct = crud._get_crud_params(
6173 self,
6174 insert_stmt,
6175 compile_state,
6176 toplevel,
6177 visited_bindparam=visited_bindparam,
6178 **kw,
6179 )
6181 if self.positional and visited_bindparam is not None:
6182 counted_bindparam = len(visited_bindparam)
6183 if self._numeric_binds:
6184 if self._values_bindparam is not None:
6185 self._values_bindparam += visited_bindparam
6186 else:
6187 self._values_bindparam = visited_bindparam
6189 crud_params_single = crud_params_struct.single_params
6191 if (
6192 not crud_params_single
6193 and not self.dialect.supports_default_values
6194 and not self.dialect.supports_default_metavalue
6195 and not self.dialect.supports_empty_insert
6196 ):
6197 raise exc.CompileError(
6198 "The '%s' dialect with current database "
6199 "version settings does not support empty "
6200 "inserts." % self.dialect.name
6201 )
6203 if compile_state._has_multi_parameters:
6204 if not self.dialect.supports_multivalues_insert:
6205 raise exc.CompileError(
6206 "The '%s' dialect with current database "
6207 "version settings does not support "
6208 "in-place multirow inserts." % self.dialect.name
6209 )
6210 elif (
6211 self.implicit_returning or insert_stmt._returning
6212 ) and insert_stmt._sort_by_parameter_order:
6213 raise exc.CompileError(
6214 "RETURNING cannot be deterministically sorted when "
6215 "using an INSERT which includes multi-row values()."
6216 )
6217 crud_params_single = crud_params_struct.single_params
6218 else:
6219 crud_params_single = crud_params_struct.single_params
6221 preparer = self.preparer
6222 supports_default_values = self.dialect.supports_default_values
6224 text = "INSERT "
6226 if insert_stmt._prefixes:
6227 text += self._generate_prefixes(
6228 insert_stmt, insert_stmt._prefixes, **kw
6229 )
6231 text += "INTO "
6232 table_text = preparer.format_table(insert_stmt.table)
6234 if insert_stmt._hints:
6235 _, table_text = self._setup_crud_hints(insert_stmt, table_text)
6237 if insert_stmt._independent_ctes:
6238 self._dispatch_independent_ctes(insert_stmt, kw)
6240 text += table_text
6242 if crud_params_single or not supports_default_values:
6243 text += " (%s)" % ", ".join(
6244 [expr for _, expr, _, _ in crud_params_single]
6245 )
6247 # look for insertmanyvalues attributes that would have been configured
6248 # by crud.py as it scanned through the columns to be part of the
6249 # INSERT
6250 use_insertmanyvalues = crud_params_struct.use_insertmanyvalues
6251 named_sentinel_params: Optional[Sequence[str]] = None
6252 add_sentinel_cols = None
6253 implicit_sentinel = False
6255 returning_cols = self.implicit_returning or insert_stmt._returning
6256 if returning_cols:
6257 add_sentinel_cols = crud_params_struct.use_sentinel_columns
6258 if add_sentinel_cols is not None:
6259 assert use_insertmanyvalues
6261 # search for the sentinel column explicitly present
6262 # in the INSERT columns list, and additionally check that
6263 # this column has a bound parameter name set up that's in the
6264 # parameter list. If both of these cases are present, it means
6265 # we will have a client side value for the sentinel in each
6266 # parameter set.
6268 _params_by_col = {
6269 col: param_names
6270 for col, _, _, param_names in crud_params_single
6271 }
6272 named_sentinel_params = []
6273 for _add_sentinel_col in add_sentinel_cols:
6274 if _add_sentinel_col not in _params_by_col:
6275 named_sentinel_params = None
6276 break
6277 param_name = self._within_exec_param_key_getter(
6278 _add_sentinel_col
6279 )
6280 if param_name not in _params_by_col[_add_sentinel_col]:
6281 named_sentinel_params = None
6282 break
6283 named_sentinel_params.append(param_name)
6285 if named_sentinel_params is None:
6286 # if we are not going to have a client side value for
6287 # the sentinel in the parameter set, that means it's
6288 # an autoincrement, an IDENTITY, or a server-side SQL
6289 # expression like nextval('seqname'). So this is
6290 # an "implicit" sentinel; we will look for it in
6291 # RETURNING
6292 # only, and then sort on it. For this case on PG,
6293 # SQL Server we have to use a special INSERT form
6294 # that guarantees the server side function lines up with
6295 # the entries in the VALUES.
6296 if (
6297 self.dialect.insertmanyvalues_implicit_sentinel
6298 & InsertmanyvaluesSentinelOpts.ANY_AUTOINCREMENT
6299 ):
6300 implicit_sentinel = True
6301 else:
6302 # here, we are not using a sentinel at all
6303 # and we are likely the SQLite dialect.
6304 # The first add_sentinel_col that we have should not
6305 # be marked as "insert_sentinel=True". if it was,
6306 # an error should have been raised in
6307 # _get_sentinel_column_for_table.
6308 assert not add_sentinel_cols[0]._insert_sentinel, (
6309 "sentinel selection rules should have prevented "
6310 "us from getting here for this dialect"
6311 )
6313 # always put the sentinel columns last. even if they are
6314 # in the returning list already, they will be there twice
6315 # then.
6316 returning_cols = list(returning_cols) + list(add_sentinel_cols)
6318 returning_clause = self.returning_clause(
6319 insert_stmt,
6320 returning_cols,
6321 populate_result_map=toplevel,
6322 )
6324 if self.returning_precedes_values:
6325 text += " " + returning_clause
6327 else:
6328 returning_clause = None
6330 if insert_stmt.select is not None:
6331 # placed here by crud.py
6332 select_text = self.process(
6333 self.stack[-1]["insert_from_select"], insert_into=True, **kw
6334 )
6336 if self.ctes and self.dialect.cte_follows_insert:
6337 nesting_level = len(self.stack) if not toplevel else None
6338 text += " %s%s" % (
6339 self._render_cte_clause(
6340 nesting_level=nesting_level,
6341 include_following_stack=True,
6342 ),
6343 select_text,
6344 )
6345 else:
6346 text += " %s" % select_text
6347 elif not crud_params_single and supports_default_values:
6348 text += " DEFAULT VALUES"
6349 if use_insertmanyvalues:
6350 self._insertmanyvalues = _InsertManyValues(
6351 True,
6352 self.dialect.default_metavalue_token,
6353 crud_params_single,
6354 counted_bindparam,
6355 sort_by_parameter_order=(
6356 insert_stmt._sort_by_parameter_order
6357 ),
6358 includes_upsert_behaviors=(
6359 insert_stmt._post_values_clause is not None
6360 ),
6361 sentinel_columns=add_sentinel_cols,
6362 num_sentinel_columns=(
6363 len(add_sentinel_cols) if add_sentinel_cols else 0
6364 ),
6365 implicit_sentinel=implicit_sentinel,
6366 )
6367 elif compile_state._has_multi_parameters:
6368 text += " VALUES %s" % (
6369 ", ".join(
6370 "(%s)"
6371 % (", ".join(value for _, _, value, _ in crud_param_set))
6372 for crud_param_set in crud_params_struct.all_multi_params
6373 ),
6374 )
6375 elif use_insertmanyvalues:
6376 if (
6377 implicit_sentinel
6378 and (
6379 self.dialect.insertmanyvalues_implicit_sentinel
6380 & InsertmanyvaluesSentinelOpts.USE_INSERT_FROM_SELECT
6381 )
6382 # this is checking if we have
6383 # INSERT INTO table (id) VALUES (DEFAULT).
6384 and not (crud_params_struct.is_default_metavalue_only)
6385 ):
6386 # if we have a sentinel column that is server generated,
6387 # then for selected backends render the VALUES list as a
6388 # subquery. This is the orderable form supported by
6389 # PostgreSQL and in fewer cases SQL Server
6390 embed_sentinel_value = True
6392 render_bind_casts = (
6393 self.dialect.insertmanyvalues_implicit_sentinel
6394 & InsertmanyvaluesSentinelOpts.RENDER_SELECT_COL_CASTS
6395 )
6397 add_sentinel_set = add_sentinel_cols or ()
6399 insert_single_values_expr = ", ".join(
6400 [
6401 value
6402 for col, _, value, _ in crud_params_single
6403 if col not in add_sentinel_set
6404 ]
6405 )
6407 colnames = ", ".join(
6408 f"p{i}"
6409 for i, cp in enumerate(crud_params_single)
6410 if cp[0] not in add_sentinel_set
6411 )
6413 if render_bind_casts:
6414 # render casts for the SELECT list. For PG, we are
6415 # already rendering bind casts in the parameter list,
6416 # selectively for the more "tricky" types like ARRAY.
6417 # however, even for the "easy" types, if the parameter
6418 # is NULL for every entry, PG gives up and says
6419 # "it must be TEXT", which fails for other easy types
6420 # like ints. So we cast on this side too.
6421 colnames_w_cast = ", ".join(
6422 (
6423 self.render_bind_cast(
6424 col.type,
6425 col.type._unwrapped_dialect_impl(self.dialect),
6426 f"p{i}",
6427 )
6428 if col not in add_sentinel_set
6429 else expr
6430 )
6431 for i, (col, _, expr, _) in enumerate(
6432 crud_params_single
6433 )
6434 )
6435 else:
6436 colnames_w_cast = ", ".join(
6437 (f"p{i}" if col not in add_sentinel_set else expr)
6438 for i, (col, _, expr, _) in enumerate(
6439 crud_params_single
6440 )
6441 )
6443 insert_crud_params = [
6444 elem
6445 for elem in crud_params_single
6446 if elem[0] not in add_sentinel_set
6447 ]
6449 text += (
6450 f" SELECT {colnames_w_cast} FROM "
6451 f"(VALUES ({insert_single_values_expr})) "
6452 f"AS imp_sen({colnames}, sen_counter) "
6453 "ORDER BY sen_counter"
6454 )
6456 else:
6457 # otherwise, if no sentinel or backend doesn't support
6458 # orderable subquery form, use a plain VALUES list
6459 embed_sentinel_value = False
6460 insert_crud_params = crud_params_single
6461 insert_single_values_expr = ", ".join(
6462 [value for _, _, value, _ in crud_params_single]
6463 )
6465 text += f" VALUES ({insert_single_values_expr})"
6467 self._insertmanyvalues = _InsertManyValues(
6468 is_default_expr=False,
6469 single_values_expr=insert_single_values_expr,
6470 insert_crud_params=insert_crud_params,
6471 num_positional_params_counted=counted_bindparam,
6472 sort_by_parameter_order=(insert_stmt._sort_by_parameter_order),
6473 includes_upsert_behaviors=(
6474 insert_stmt._post_values_clause is not None
6475 ),
6476 sentinel_columns=add_sentinel_cols,
6477 num_sentinel_columns=(
6478 len(add_sentinel_cols) if add_sentinel_cols else 0
6479 ),
6480 sentinel_param_keys=named_sentinel_params,
6481 implicit_sentinel=implicit_sentinel,
6482 embed_values_counter=embed_sentinel_value,
6483 )
6485 else:
6486 insert_single_values_expr = ", ".join(
6487 [value for _, _, value, _ in crud_params_single]
6488 )
6490 text += f" VALUES ({insert_single_values_expr})"
6492 if insert_stmt._post_values_clause is not None:
6493 post_values_clause = self.process(
6494 insert_stmt._post_values_clause, **kw
6495 )
6496 if post_values_clause:
6497 text += " " + post_values_clause
6499 if returning_clause and not self.returning_precedes_values:
6500 text += " " + returning_clause
6502 if self.ctes and not self.dialect.cte_follows_insert:
6503 nesting_level = len(self.stack) if not toplevel else None
6504 text = (
6505 self._render_cte_clause(
6506 nesting_level=nesting_level,
6507 include_following_stack=True,
6508 )
6509 + text
6510 )
6512 self.stack.pop(-1)
6514 return text
6516 def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw):
6517 """Provide a hook to override the initial table clause
6518 in an UPDATE statement.
6520 MySQL overrides this.
6522 """
6523 kw["asfrom"] = True
6524 return from_table._compiler_dispatch(self, iscrud=True, **kw)
6526 def update_from_clause(
6527 self, update_stmt, from_table, extra_froms, from_hints, **kw
6528 ):
6529 """Provide a hook to override the generation of an
6530 UPDATE..FROM clause.
6531 MySQL and MSSQL override this.
6532 """
6533 raise NotImplementedError(
6534 "This backend does not support multiple-table "
6535 "criteria within UPDATE"
6536 )
6538 def update_post_criteria_clause(
6539 self, update_stmt: Update, **kw: Any
6540 ) -> Optional[str]:
6541 """provide a hook to override generation after the WHERE criteria
6542 in an UPDATE statement
6544 .. versionadded:: 2.1
6546 """
6547 if update_stmt._post_criteria_clause is not None:
6548 return self.process(
6549 update_stmt._post_criteria_clause,
6550 **kw,
6551 )
6552 else:
6553 return None
6555 def delete_post_criteria_clause(
6556 self, delete_stmt: Delete, **kw: Any
6557 ) -> Optional[str]:
6558 """provide a hook to override generation after the WHERE criteria
6559 in a DELETE statement
6561 .. versionadded:: 2.1
6563 """
6564 if delete_stmt._post_criteria_clause is not None:
6565 return self.process(
6566 delete_stmt._post_criteria_clause,
6567 **kw,
6568 )
6569 else:
6570 return None
6572 def visit_update(
6573 self,
6574 update_stmt: Update,
6575 visiting_cte: Optional[CTE] = None,
6576 **kw: Any,
6577 ) -> str:
6578 compile_state = update_stmt._compile_state_factory(
6579 update_stmt, self, **kw
6580 )
6581 if TYPE_CHECKING:
6582 assert isinstance(compile_state, UpdateDMLState)
6583 update_stmt = compile_state.statement # type: ignore[assignment]
6585 if visiting_cte is not None:
6586 kw["visiting_cte"] = visiting_cte
6587 toplevel = False
6588 else:
6589 toplevel = not self.stack
6591 if toplevel:
6592 self.isupdate = True
6593 if not self.dml_compile_state:
6594 self.dml_compile_state = compile_state
6595 if not self.compile_state:
6596 self.compile_state = compile_state
6598 if self.linting & COLLECT_CARTESIAN_PRODUCTS:
6599 from_linter = FromLinter({}, set())
6600 warn_linting = self.linting & WARN_LINTING
6601 if toplevel:
6602 self.from_linter = from_linter
6603 else:
6604 from_linter = None
6605 warn_linting = False
6607 extra_froms = compile_state._extra_froms
6608 is_multitable = bool(extra_froms)
6610 if is_multitable:
6611 # main table might be a JOIN
6612 main_froms = set(_from_objects(update_stmt.table))
6613 render_extra_froms = [
6614 f for f in extra_froms if f not in main_froms
6615 ]
6616 correlate_froms = main_froms.union(extra_froms)
6617 else:
6618 render_extra_froms = []
6619 correlate_froms = {update_stmt.table}
6621 self.stack.append(
6622 {
6623 "correlate_froms": correlate_froms,
6624 "asfrom_froms": correlate_froms,
6625 "selectable": update_stmt,
6626 }
6627 )
6629 text = "UPDATE "
6631 if update_stmt._prefixes:
6632 text += self._generate_prefixes(
6633 update_stmt, update_stmt._prefixes, **kw
6634 )
6636 table_text = self.update_tables_clause(
6637 update_stmt,
6638 update_stmt.table,
6639 render_extra_froms,
6640 from_linter=from_linter,
6641 **kw,
6642 )
6643 crud_params_struct = crud._get_crud_params(
6644 self, update_stmt, compile_state, toplevel, **kw
6645 )
6646 crud_params = crud_params_struct.single_params
6648 if update_stmt._hints:
6649 dialect_hints, table_text = self._setup_crud_hints(
6650 update_stmt, table_text
6651 )
6652 else:
6653 dialect_hints = None
6655 if update_stmt._independent_ctes:
6656 self._dispatch_independent_ctes(update_stmt, kw)
6658 text += table_text
6660 text += " SET "
6661 text += ", ".join(
6662 expr + "=" + value
6663 for _, expr, value, _ in cast(
6664 "List[Tuple[Any, str, str, Any]]", crud_params
6665 )
6666 )
6668 if self.implicit_returning or update_stmt._returning:
6669 if self.returning_precedes_values:
6670 text += " " + self.returning_clause(
6671 update_stmt,
6672 self.implicit_returning or update_stmt._returning,
6673 populate_result_map=toplevel,
6674 )
6676 if extra_froms:
6677 extra_from_text = self.update_from_clause(
6678 update_stmt,
6679 update_stmt.table,
6680 render_extra_froms,
6681 dialect_hints,
6682 from_linter=from_linter,
6683 **kw,
6684 )
6685 if extra_from_text:
6686 text += " " + extra_from_text
6688 if update_stmt._where_criteria:
6689 t = self._generate_delimited_and_list(
6690 update_stmt._where_criteria, from_linter=from_linter, **kw
6691 )
6692 if t:
6693 text += " WHERE " + t
6695 ulc = self.update_post_criteria_clause(
6696 update_stmt, from_linter=from_linter, **kw
6697 )
6698 if ulc:
6699 text += " " + ulc
6701 if (
6702 self.implicit_returning or update_stmt._returning
6703 ) and not self.returning_precedes_values:
6704 text += " " + self.returning_clause(
6705 update_stmt,
6706 self.implicit_returning or update_stmt._returning,
6707 populate_result_map=toplevel,
6708 )
6710 if self.ctes:
6711 nesting_level = len(self.stack) if not toplevel else None
6712 text = self._render_cte_clause(nesting_level=nesting_level) + text
6714 if warn_linting:
6715 assert from_linter is not None
6716 from_linter.warn(stmt_type="UPDATE")
6718 self.stack.pop(-1)
6720 return text # type: ignore[no-any-return]
6722 def delete_extra_from_clause(
6723 self, delete_stmt, from_table, extra_froms, from_hints, **kw
6724 ):
6725 """Provide a hook to override the generation of an
6726 DELETE..FROM clause.
6728 This can be used to implement DELETE..USING for example.
6730 MySQL and MSSQL override this.
6732 """
6733 raise NotImplementedError(
6734 "This backend does not support multiple-table "
6735 "criteria within DELETE"
6736 )
6738 def delete_table_clause(self, delete_stmt, from_table, extra_froms, **kw):
6739 return from_table._compiler_dispatch(
6740 self, asfrom=True, iscrud=True, **kw
6741 )
6743 def visit_delete(self, delete_stmt, visiting_cte=None, **kw):
6744 compile_state = delete_stmt._compile_state_factory(
6745 delete_stmt, self, **kw
6746 )
6747 delete_stmt = compile_state.statement
6749 if visiting_cte is not None:
6750 kw["visiting_cte"] = visiting_cte
6751 toplevel = False
6752 else:
6753 toplevel = not self.stack
6755 if toplevel:
6756 self.isdelete = True
6757 if not self.dml_compile_state:
6758 self.dml_compile_state = compile_state
6759 if not self.compile_state:
6760 self.compile_state = compile_state
6762 if self.linting & COLLECT_CARTESIAN_PRODUCTS:
6763 from_linter = FromLinter({}, set())
6764 warn_linting = self.linting & WARN_LINTING
6765 if toplevel:
6766 self.from_linter = from_linter
6767 else:
6768 from_linter = None
6769 warn_linting = False
6771 extra_froms = compile_state._extra_froms
6773 correlate_froms = {delete_stmt.table}.union(extra_froms)
6774 self.stack.append(
6775 {
6776 "correlate_froms": correlate_froms,
6777 "asfrom_froms": correlate_froms,
6778 "selectable": delete_stmt,
6779 }
6780 )
6782 text = "DELETE "
6784 if delete_stmt._prefixes:
6785 text += self._generate_prefixes(
6786 delete_stmt, delete_stmt._prefixes, **kw
6787 )
6789 text += "FROM "
6791 try:
6792 table_text = self.delete_table_clause(
6793 delete_stmt,
6794 delete_stmt.table,
6795 extra_froms,
6796 from_linter=from_linter,
6797 )
6798 except TypeError:
6799 # anticipate 3rd party dialects that don't include **kw
6800 # TODO: remove in 2.1
6801 table_text = self.delete_table_clause(
6802 delete_stmt, delete_stmt.table, extra_froms
6803 )
6804 if from_linter:
6805 _ = self.process(delete_stmt.table, from_linter=from_linter)
6807 crud._get_crud_params(self, delete_stmt, compile_state, toplevel, **kw)
6809 if delete_stmt._hints:
6810 dialect_hints, table_text = self._setup_crud_hints(
6811 delete_stmt, table_text
6812 )
6813 else:
6814 dialect_hints = None
6816 if delete_stmt._independent_ctes:
6817 self._dispatch_independent_ctes(delete_stmt, kw)
6819 text += table_text
6821 if (
6822 self.implicit_returning or delete_stmt._returning
6823 ) and self.returning_precedes_values:
6824 text += " " + self.returning_clause(
6825 delete_stmt,
6826 self.implicit_returning or delete_stmt._returning,
6827 populate_result_map=toplevel,
6828 )
6830 if extra_froms:
6831 extra_from_text = self.delete_extra_from_clause(
6832 delete_stmt,
6833 delete_stmt.table,
6834 extra_froms,
6835 dialect_hints,
6836 from_linter=from_linter,
6837 **kw,
6838 )
6839 if extra_from_text:
6840 text += " " + extra_from_text
6842 if delete_stmt._where_criteria:
6843 t = self._generate_delimited_and_list(
6844 delete_stmt._where_criteria, from_linter=from_linter, **kw
6845 )
6846 if t:
6847 text += " WHERE " + t
6849 dlc = self.delete_post_criteria_clause(
6850 delete_stmt, from_linter=from_linter, **kw
6851 )
6852 if dlc:
6853 text += " " + dlc
6855 if (
6856 self.implicit_returning or delete_stmt._returning
6857 ) and not self.returning_precedes_values:
6858 text += " " + self.returning_clause(
6859 delete_stmt,
6860 self.implicit_returning or delete_stmt._returning,
6861 populate_result_map=toplevel,
6862 )
6864 if self.ctes:
6865 nesting_level = len(self.stack) if not toplevel else None
6866 text = self._render_cte_clause(nesting_level=nesting_level) + text
6868 if warn_linting:
6869 assert from_linter is not None
6870 from_linter.warn(stmt_type="DELETE")
6872 self.stack.pop(-1)
6874 return text
6876 def visit_savepoint(self, savepoint_stmt, **kw):
6877 return "SAVEPOINT %s" % self.preparer.format_savepoint(savepoint_stmt)
6879 def visit_rollback_to_savepoint(self, savepoint_stmt, **kw):
6880 return "ROLLBACK TO SAVEPOINT %s" % self.preparer.format_savepoint(
6881 savepoint_stmt
6882 )
6884 def visit_release_savepoint(self, savepoint_stmt, **kw):
6885 return "RELEASE SAVEPOINT %s" % self.preparer.format_savepoint(
6886 savepoint_stmt
6887 )
6890class StrSQLCompiler(SQLCompiler):
6891 """A :class:`.SQLCompiler` subclass which allows a small selection
6892 of non-standard SQL features to render into a string value.
6894 The :class:`.StrSQLCompiler` is invoked whenever a Core expression
6895 element is directly stringified without calling upon the
6896 :meth:`_expression.ClauseElement.compile` method.
6897 It can render a limited set
6898 of non-standard SQL constructs to assist in basic stringification,
6899 however for more substantial custom or dialect-specific SQL constructs,
6900 it will be necessary to make use of
6901 :meth:`_expression.ClauseElement.compile`
6902 directly.
6904 .. seealso::
6906 :ref:`faq_sql_expression_string`
6908 """
6910 def get_select_precolumns(self, select: Select[Any], **kw: Any) -> str:
6911 return "DISTINCT " if select._distinct else ""
6913 def _fallback_column_name(self, column):
6914 return "<name unknown>"
6916 @util.preload_module("sqlalchemy.engine.url")
6917 def visit_unsupported_compilation(self, element, err, **kw):
6918 if element.stringify_dialect != "default":
6919 url = util.preloaded.engine_url
6920 dialect = url.URL.create(element.stringify_dialect).get_dialect()()
6922 compiler = dialect.statement_compiler(
6923 dialect, None, _supporting_against=self
6924 )
6925 if not isinstance(compiler, StrSQLCompiler):
6926 return compiler.process(element, **kw)
6928 return super().visit_unsupported_compilation(element, err)
6930 def visit_getitem_binary(self, binary, operator, **kw):
6931 return "%s[%s]" % (
6932 self.process(binary.left, **kw),
6933 self.process(binary.right, **kw),
6934 )
6936 def visit_json_getitem_op_binary(self, binary, operator, **kw):
6937 return self.visit_getitem_binary(binary, operator, **kw)
6939 def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
6940 return self.visit_getitem_binary(binary, operator, **kw)
6942 def visit_sequence(self, sequence, **kw):
6943 return (
6944 f"<next sequence value: {self.preparer.format_sequence(sequence)}>"
6945 )
6947 def returning_clause(
6948 self,
6949 stmt: UpdateBase,
6950 returning_cols: Sequence[_ColumnsClauseElement],
6951 *,
6952 populate_result_map: bool,
6953 **kw: Any,
6954 ) -> str:
6955 columns = [
6956 self._label_select_column(None, c, True, False, {})
6957 for c in base._select_iterables(returning_cols)
6958 ]
6959 return "RETURNING " + ", ".join(columns)
6961 def update_from_clause(
6962 self, update_stmt, from_table, extra_froms, from_hints, **kw
6963 ):
6964 kw["asfrom"] = True
6965 return "FROM " + ", ".join(
6966 t._compiler_dispatch(self, fromhints=from_hints, **kw)
6967 for t in extra_froms
6968 )
6970 def delete_extra_from_clause(
6971 self, delete_stmt, from_table, extra_froms, from_hints, **kw
6972 ):
6973 kw["asfrom"] = True
6974 return ", " + ", ".join(
6975 t._compiler_dispatch(self, fromhints=from_hints, **kw)
6976 for t in extra_froms
6977 )
6979 def visit_empty_set_expr(self, element_types, **kw):
6980 return "SELECT 1 WHERE 1!=1"
6982 def get_from_hint_text(self, table, text):
6983 return "[%s]" % text
6985 def visit_regexp_match_op_binary(self, binary, operator, **kw):
6986 return self._generate_generic_binary(binary, " <regexp> ", **kw)
6988 def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
6989 return self._generate_generic_binary(binary, " <not regexp> ", **kw)
6991 def visit_regexp_replace_op_binary(self, binary, operator, **kw):
6992 return "<regexp replace>(%s, %s)" % (
6993 binary.left._compiler_dispatch(self, **kw),
6994 binary.right._compiler_dispatch(self, **kw),
6995 )
6997 def visit_try_cast(self, cast, **kwargs):
6998 return "TRY_CAST(%s AS %s)" % (
6999 cast.clause._compiler_dispatch(self, **kwargs),
7000 cast.typeclause._compiler_dispatch(self, **kwargs),
7001 )
7004class DDLCompiler(Compiled):
7005 is_ddl = True
7007 if TYPE_CHECKING:
7009 def __init__(
7010 self,
7011 dialect: Dialect,
7012 statement: ExecutableDDLElement,
7013 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
7014 render_schema_translate: bool = ...,
7015 compile_kwargs: Mapping[str, Any] = ...,
7016 ): ...
7018 @util.ro_memoized_property
7019 def sql_compiler(self) -> SQLCompiler:
7020 return self.dialect.statement_compiler(
7021 self.dialect, None, schema_translate_map=self.schema_translate_map
7022 )
7024 @util.memoized_property
7025 def type_compiler(self):
7026 return self.dialect.type_compiler_instance
7028 def construct_params(
7029 self,
7030 params: Optional[_CoreSingleExecuteParams] = None,
7031 extracted_parameters: Optional[Sequence[BindParameter[Any]]] = None,
7032 escape_names: bool = True,
7033 ) -> Optional[_MutableCoreSingleExecuteParams]:
7034 return None
7036 def visit_ddl(self, ddl, **kwargs):
7037 # table events can substitute table and schema name
7038 context = ddl.context
7039 if isinstance(ddl.target, schema.Table):
7040 context = context.copy()
7042 preparer = self.preparer
7043 path = preparer.format_table_seq(ddl.target)
7044 if len(path) == 1:
7045 table, sch = path[0], ""
7046 else:
7047 table, sch = path[-1], path[0]
7049 context.setdefault("table", table)
7050 context.setdefault("schema", sch)
7051 context.setdefault("fullname", preparer.format_table(ddl.target))
7053 return self.sql_compiler.post_process_text(ddl.statement % context)
7055 def visit_create_schema(self, create, **kw):
7056 text = "CREATE SCHEMA "
7057 if create.if_not_exists:
7058 text += "IF NOT EXISTS "
7059 return text + self.preparer.format_schema(create.element)
7061 def visit_drop_schema(self, drop, **kw):
7062 text = "DROP SCHEMA "
7063 if drop.if_exists:
7064 text += "IF EXISTS "
7065 text += self.preparer.format_schema(drop.element)
7066 if drop.cascade:
7067 text += " CASCADE"
7068 return text
7070 def visit_create_table(self, create, **kw):
7071 table = create.element
7072 preparer = self.preparer
7074 text = "\nCREATE "
7075 if table._prefixes:
7076 text += " ".join(table._prefixes) + " "
7078 text += "TABLE "
7079 if create.if_not_exists:
7080 text += "IF NOT EXISTS "
7082 text += preparer.format_table(table) + " "
7084 create_table_suffix = self.create_table_suffix(table)
7085 if create_table_suffix:
7086 text += create_table_suffix + " "
7088 text += "("
7090 separator = "\n"
7092 # if only one primary key, specify it along with the column
7093 first_pk = False
7094 for create_column in create.columns:
7095 column = create_column.element
7096 try:
7097 processed = self.process(
7098 create_column, first_pk=column.primary_key and not first_pk
7099 )
7100 if processed is not None:
7101 text += separator
7102 separator = ", \n"
7103 text += "\t" + processed
7104 if column.primary_key:
7105 first_pk = True
7106 except exc.CompileError as ce:
7107 raise exc.CompileError(
7108 "(in table '%s', column '%s'): %s"
7109 % (table.description, column.name, ce.args[0])
7110 ) from ce
7112 const = self.create_table_constraints(
7113 table,
7114 _include_foreign_key_constraints=create.include_foreign_key_constraints, # noqa
7115 )
7116 if const:
7117 text += separator + "\t" + const
7119 text += "\n)%s\n\n" % self.post_create_table(table)
7120 return text
7122 def visit_create_view(self, element: CreateView, **kw: Any) -> str:
7123 return self._generate_table_select(element, "view", **kw)
7125 def visit_create_table_as(self, element: CreateTableAs, **kw: Any) -> str:
7126 return self._generate_table_select(element, "create_table_as", **kw)
7128 def create_table_select_suffixes(
7129 self,
7130 element: _TableViaSelect,
7131 type_: str,
7132 **kw: Any,
7133 ) -> str:
7134 return ""
7136 def _generate_table_select(
7137 self,
7138 element: _TableViaSelect,
7139 type_: str,
7140 if_not_exists: Optional[bool] = None,
7141 **kw: Any,
7142 ) -> str:
7143 prep = self.preparer
7145 inner_kw = dict(kw)
7146 inner_kw["literal_binds"] = True
7147 select_sql = self.sql_compiler.process(element.selectable, **inner_kw)
7149 # Use if_not_exists parameter if provided, otherwise use element's
7150 use_if_not_exists = (
7151 if_not_exists
7152 if if_not_exists is not None
7153 else element.if_not_exists
7154 )
7156 parts: List[Optional[str]] = [
7157 "CREATE",
7158 "OR REPLACE" if getattr(element, "or_replace", False) else None,
7159 "TEMPORARY" if element.temporary else None,
7160 (
7161 "MATERIALIZED VIEW"
7162 if type_ == "view" and getattr(element, "materialized", False)
7163 else "TABLE" if type_ == "create_table_as" else "VIEW"
7164 ),
7165 "IF NOT EXISTS" if use_if_not_exists else None,
7166 prep.format_table(element.table),
7167 ]
7168 suffixes = self.create_table_select_suffixes(element, type_, **kw)
7169 if suffixes:
7170 parts.append(suffixes)
7171 parts += ["AS", select_sql]
7172 return " ".join(p for p in parts if p)
7174 def visit_create_column(self, create, first_pk=False, **kw):
7175 column = create.element
7177 if column.system:
7178 return None
7180 text = self.get_column_specification(column, first_pk=first_pk)
7181 const = " ".join(
7182 self.process(constraint) for constraint in column.constraints
7183 )
7184 if const:
7185 text += " " + const
7187 return text
7189 def create_table_constraints(
7190 self, table, _include_foreign_key_constraints=None, **kw
7191 ):
7192 # On some DB order is significant: visit PK first, then the
7193 # other constraints (engine.ReflectionTest.testbasic failed on FB2)
7194 constraints = []
7195 if table.primary_key:
7196 constraints.append(table.primary_key)
7198 all_fkcs = table.foreign_key_constraints
7199 if _include_foreign_key_constraints is not None:
7200 omit_fkcs = all_fkcs.difference(_include_foreign_key_constraints)
7201 else:
7202 omit_fkcs = set()
7204 constraints.extend(
7205 [
7206 c
7207 for c in table._sorted_constraints
7208 if c is not table.primary_key and c not in omit_fkcs
7209 ]
7210 )
7212 return ", \n\t".join(
7213 p
7214 for p in (
7215 self.process(constraint)
7216 for constraint in constraints
7217 if (constraint._should_create_for_compiler(self))
7218 and (
7219 not self.dialect.supports_alter
7220 or not getattr(constraint, "use_alter", False)
7221 )
7222 )
7223 if p is not None
7224 )
7226 def visit_drop_table(self, drop, **kw):
7227 text = "\nDROP TABLE "
7228 if drop.if_exists:
7229 text += "IF EXISTS "
7230 return text + self.preparer.format_table(drop.element)
7232 def visit_drop_view(self, drop, **kw):
7233 text = "\nDROP "
7234 if drop.materialized:
7235 text += "MATERIALIZED VIEW "
7236 else:
7237 text += "VIEW "
7238 if drop.if_exists:
7239 text += "IF EXISTS "
7240 return text + self.preparer.format_table(drop.element)
7242 def _verify_index_table(self, index: Index) -> None:
7243 if index.table is None:
7244 raise exc.CompileError(
7245 "Index '%s' is not associated with any table." % index.name
7246 )
7248 def visit_create_index(
7249 self, create, include_schema=False, include_table_schema=True, **kw
7250 ):
7251 index = create.element
7252 self._verify_index_table(index)
7253 preparer = self.preparer
7254 text = "CREATE "
7255 if index.unique:
7256 text += "UNIQUE "
7257 if index.name is None:
7258 raise exc.CompileError(
7259 "CREATE INDEX requires that the index have a name"
7260 )
7262 text += "INDEX "
7263 if create.if_not_exists:
7264 text += "IF NOT EXISTS "
7266 text += "%s ON %s (%s)" % (
7267 self._prepared_index_name(index, include_schema=include_schema),
7268 preparer.format_table(
7269 index.table, use_schema=include_table_schema
7270 ),
7271 ", ".join(
7272 self.sql_compiler.process(
7273 expr, include_table=False, literal_binds=True
7274 )
7275 for expr in index.expressions
7276 ),
7277 )
7278 return text
7280 def visit_drop_index(self, drop, **kw):
7281 index = drop.element
7283 if index.name is None:
7284 raise exc.CompileError(
7285 "DROP INDEX requires that the index have a name"
7286 )
7287 text = "\nDROP INDEX "
7288 if drop.if_exists:
7289 text += "IF EXISTS "
7291 return text + self._prepared_index_name(index, include_schema=True)
7293 def _prepared_index_name(
7294 self, index: Index, include_schema: bool = False
7295 ) -> str:
7296 if index.table is not None:
7297 effective_schema = self.preparer.schema_for_object(index.table)
7298 else:
7299 effective_schema = None
7300 if include_schema and effective_schema:
7301 schema_name = self.preparer.quote_schema(effective_schema)
7302 else:
7303 schema_name = None
7305 index_name: str = self.preparer.format_index(index)
7307 if schema_name:
7308 index_name = schema_name + "." + index_name
7309 return index_name
7311 def visit_add_constraint(self, create, **kw):
7312 return "ALTER TABLE %s ADD %s" % (
7313 self.preparer.format_table(create.element.table),
7314 self.process(create.element),
7315 )
7317 def visit_set_table_comment(self, create, **kw):
7318 return "COMMENT ON TABLE %s IS %s" % (
7319 self.preparer.format_table(create.element),
7320 self.sql_compiler.render_literal_value(
7321 create.element.comment, sqltypes.String()
7322 ),
7323 )
7325 def visit_drop_table_comment(self, drop, **kw):
7326 return "COMMENT ON TABLE %s IS NULL" % self.preparer.format_table(
7327 drop.element
7328 )
7330 def visit_set_column_comment(self, create, **kw):
7331 return "COMMENT ON COLUMN %s IS %s" % (
7332 self.preparer.format_column(
7333 create.element, use_table=True, use_schema=True
7334 ),
7335 self.sql_compiler.render_literal_value(
7336 create.element.comment, sqltypes.String()
7337 ),
7338 )
7340 def visit_drop_column_comment(self, drop, **kw):
7341 return "COMMENT ON COLUMN %s IS NULL" % self.preparer.format_column(
7342 drop.element, use_table=True
7343 )
7345 def visit_set_constraint_comment(self, create, **kw):
7346 raise exc.UnsupportedCompilationError(self, type(create))
7348 def visit_drop_constraint_comment(self, drop, **kw):
7349 raise exc.UnsupportedCompilationError(self, type(drop))
7351 def get_identity_options(self, identity_options: IdentityOptions) -> str:
7352 text = []
7353 if identity_options.increment is not None:
7354 text.append("INCREMENT BY %d" % identity_options.increment)
7355 if identity_options.start is not None:
7356 text.append("START WITH %d" % identity_options.start)
7357 if identity_options.minvalue is not None:
7358 text.append("MINVALUE %d" % identity_options.minvalue)
7359 if identity_options.maxvalue is not None:
7360 text.append("MAXVALUE %d" % identity_options.maxvalue)
7361 if identity_options.nominvalue is not None:
7362 text.append("NO MINVALUE")
7363 if identity_options.nomaxvalue is not None:
7364 text.append("NO MAXVALUE")
7365 if identity_options.cache is not None:
7366 text.append("CACHE %d" % identity_options.cache)
7367 if identity_options.cycle is not None:
7368 text.append("CYCLE" if identity_options.cycle else "NO CYCLE")
7369 return " ".join(text)
7371 def visit_create_sequence(self, create, prefix=None, **kw):
7372 text = "CREATE SEQUENCE "
7373 if create.if_not_exists:
7374 text += "IF NOT EXISTS "
7375 text += self.preparer.format_sequence(create.element)
7377 if prefix:
7378 text += prefix
7379 options = self.get_identity_options(create.element)
7380 if options:
7381 text += " " + options
7382 return text
7384 def visit_drop_sequence(self, drop, **kw):
7385 text = "DROP SEQUENCE "
7386 if drop.if_exists:
7387 text += "IF EXISTS "
7388 return text + self.preparer.format_sequence(drop.element)
7390 def visit_drop_constraint(self, drop, **kw):
7391 constraint = drop.element
7392 if constraint.name is not None:
7393 formatted_name = self.preparer.format_constraint(constraint)
7394 else:
7395 formatted_name = None
7397 if formatted_name is None:
7398 raise exc.CompileError(
7399 "Can't emit DROP CONSTRAINT for constraint %r; "
7400 "it has no name" % drop.element
7401 )
7402 return "ALTER TABLE %s DROP CONSTRAINT %s%s%s" % (
7403 self.preparer.format_table(drop.element.table),
7404 "IF EXISTS " if drop.if_exists else "",
7405 formatted_name,
7406 " CASCADE" if drop.cascade else "",
7407 )
7409 def get_column_specification(
7410 self, column: Column[Any], **kwargs: Any
7411 ) -> str:
7412 colspec = (
7413 self.preparer.format_column(column)
7414 + " "
7415 + self.dialect.type_compiler_instance.process(
7416 column.type, type_expression=column
7417 )
7418 )
7419 default = self.get_column_default_string(column)
7420 if default is not None:
7421 colspec += " DEFAULT " + default
7423 if column.computed is not None:
7424 colspec += " " + self.process(column.computed)
7426 if (
7427 column.identity is not None
7428 and self.dialect.supports_identity_columns
7429 ):
7430 colspec += " " + self.process(column.identity)
7432 if not column.nullable and (
7433 not column.identity or not self.dialect.supports_identity_columns
7434 ):
7435 colspec += " NOT NULL"
7436 return colspec
7438 def create_table_suffix(self, table: Table) -> str:
7439 return ""
7441 def post_create_table(self, table: Table) -> str:
7442 return ""
7444 def get_column_default_string(self, column: Column[Any]) -> Optional[str]:
7445 if isinstance(column.server_default, schema.DefaultClause):
7446 return self.render_default_string(column.server_default.arg)
7447 else:
7448 return None
7450 def render_default_string(self, default: Union[Visitable, str]) -> str:
7451 if isinstance(default, str):
7452 return self.sql_compiler.render_literal_value(
7453 default, sqltypes.STRINGTYPE
7454 )
7455 else:
7456 return self.sql_compiler.process(default, literal_binds=True)
7458 def visit_table_or_column_check_constraint(self, constraint, **kw):
7459 if constraint.is_column_level:
7460 return self.visit_column_check_constraint(constraint)
7461 else:
7462 return self.visit_check_constraint(constraint)
7464 def visit_check_constraint(self, constraint, **kw):
7465 text = self.define_constraint_preamble(constraint, **kw)
7466 text += self.define_check_body(constraint, **kw)
7467 text += self.define_constraint_deferrability(constraint)
7468 return text
7470 def visit_column_check_constraint(self, constraint, **kw):
7471 text = self.define_constraint_preamble(constraint, **kw)
7472 text += self.define_check_body(constraint, **kw)
7473 text += self.define_constraint_deferrability(constraint)
7474 return text
7476 def visit_primary_key_constraint(
7477 self, constraint: PrimaryKeyConstraint, **kw: Any
7478 ) -> str:
7479 if len(constraint) == 0:
7480 return ""
7481 text = self.define_constraint_preamble(constraint, **kw)
7482 text += self.define_primary_key_body(constraint, **kw)
7483 text += self.define_constraint_deferrability(constraint)
7484 return text
7486 def visit_foreign_key_constraint(
7487 self, constraint: ForeignKeyConstraint, **kw: Any
7488 ) -> str:
7489 text = self.define_constraint_preamble(constraint, **kw)
7490 text += self.define_foreign_key_body(constraint, **kw)
7491 text += self.define_constraint_match(constraint)
7492 text += self.define_constraint_cascades(constraint)
7493 text += self.define_constraint_deferrability(constraint)
7494 return text
7496 def define_constraint_remote_table(self, constraint, table, preparer):
7497 """Format the remote table clause of a CREATE CONSTRAINT clause."""
7499 return preparer.format_table(table)
7501 def visit_unique_constraint(
7502 self, constraint: UniqueConstraint, **kw: Any
7503 ) -> str:
7504 if len(constraint) == 0:
7505 return ""
7506 text = self.define_constraint_preamble(constraint, **kw)
7507 text += self.define_unique_body(constraint, **kw)
7508 text += self.define_constraint_deferrability(constraint)
7509 return text
7511 def define_constraint_preamble(
7512 self, constraint: Constraint, **kw: Any
7513 ) -> str:
7514 text = ""
7515 if constraint.name is not None:
7516 formatted_name = self.preparer.format_constraint(constraint)
7517 if formatted_name is not None:
7518 text += "CONSTRAINT %s " % formatted_name
7519 return text
7521 def define_primary_key_body(
7522 self, constraint: PrimaryKeyConstraint, **kw: Any
7523 ) -> str:
7524 text = ""
7525 text += "PRIMARY KEY "
7526 text += "(%s)" % ", ".join(
7527 self.preparer.quote(c.name)
7528 for c in (
7529 constraint.columns_autoinc_first
7530 if constraint._implicit_generated
7531 else constraint.columns
7532 )
7533 )
7534 return text
7536 def define_foreign_key_body(
7537 self, constraint: ForeignKeyConstraint, **kw: Any
7538 ) -> str:
7539 preparer = self.preparer
7540 remote_table = list(constraint.elements)[0].column.table
7541 text = "FOREIGN KEY(%s) REFERENCES %s (%s)" % (
7542 ", ".join(
7543 preparer.quote(f.parent.name) for f in constraint.elements
7544 ),
7545 self.define_constraint_remote_table(
7546 constraint, remote_table, preparer
7547 ),
7548 ", ".join(
7549 preparer.quote(f.column.name) for f in constraint.elements
7550 ),
7551 )
7552 return text
7554 def define_unique_body(
7555 self, constraint: UniqueConstraint, **kw: Any
7556 ) -> str:
7557 text = "UNIQUE %s(%s)" % (
7558 self.define_unique_constraint_distinct(constraint, **kw),
7559 ", ".join(self.preparer.quote(c.name) for c in constraint),
7560 )
7561 return text
7563 def define_check_body(self, constraint: CheckConstraint, **kw: Any) -> str:
7564 text = "CHECK (%s)" % self.sql_compiler.process(
7565 constraint.sqltext, include_table=False, literal_binds=True
7566 )
7567 return text
7569 def define_unique_constraint_distinct(
7570 self, constraint: UniqueConstraint, **kw: Any
7571 ) -> str:
7572 return ""
7574 def define_constraint_cascades(
7575 self, constraint: ForeignKeyConstraint
7576 ) -> str:
7577 text = ""
7578 if constraint.ondelete is not None:
7579 text += self.define_constraint_ondelete_cascade(constraint)
7581 if constraint.onupdate is not None:
7582 text += self.define_constraint_onupdate_cascade(constraint)
7583 return text
7585 def define_constraint_ondelete_cascade(
7586 self, constraint: ForeignKeyConstraint
7587 ) -> str:
7588 return " ON DELETE %s" % self.preparer.validate_sql_phrase(
7589 constraint.ondelete, FK_ON_DELETE
7590 )
7592 def define_constraint_onupdate_cascade(
7593 self, constraint: ForeignKeyConstraint
7594 ) -> str:
7595 return " ON UPDATE %s" % self.preparer.validate_sql_phrase(
7596 constraint.onupdate, FK_ON_UPDATE
7597 )
7599 def define_constraint_deferrability(self, constraint: Constraint) -> str:
7600 text = ""
7601 if constraint.deferrable is not None:
7602 if constraint.deferrable:
7603 text += " DEFERRABLE"
7604 else:
7605 text += " NOT DEFERRABLE"
7606 if constraint.initially is not None:
7607 text += " INITIALLY %s" % self.preparer.validate_sql_phrase(
7608 constraint.initially, FK_INITIALLY
7609 )
7610 return text
7612 def define_constraint_match(self, constraint: ForeignKeyConstraint) -> str:
7613 text = ""
7614 if constraint.match is not None:
7615 text += " MATCH %s" % constraint.match
7616 return text
7618 def visit_computed_column(self, generated, **kw):
7619 text = "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process(
7620 generated.sqltext, include_table=False, literal_binds=True
7621 )
7622 if generated.persisted is True:
7623 text += " STORED"
7624 elif generated.persisted is False:
7625 text += " VIRTUAL"
7626 return text
7628 def visit_identity_column(self, identity, **kw):
7629 text = "GENERATED %s AS IDENTITY" % (
7630 "ALWAYS" if identity.always else "BY DEFAULT",
7631 )
7632 options = self.get_identity_options(identity)
7633 if options:
7634 text += " (%s)" % options
7635 return text
7638class GenericTypeCompiler(TypeCompiler):
7639 def visit_FLOAT(self, type_: sqltypes.Float[Any], **kw: Any) -> str:
7640 return "FLOAT"
7642 def visit_DOUBLE(self, type_: sqltypes.Double[Any], **kw: Any) -> str:
7643 return "DOUBLE"
7645 def visit_DOUBLE_PRECISION(
7646 self, type_: sqltypes.DOUBLE_PRECISION[Any], **kw: Any
7647 ) -> str:
7648 return "DOUBLE PRECISION"
7650 def visit_REAL(self, type_: sqltypes.REAL[Any], **kw: Any) -> str:
7651 return "REAL"
7653 def visit_NUMERIC(self, type_: sqltypes.Numeric[Any], **kw: Any) -> str:
7654 if type_.precision is None:
7655 return "NUMERIC"
7656 elif type_.scale is None:
7657 return "NUMERIC(%(precision)s)" % {"precision": type_.precision}
7658 else:
7659 return "NUMERIC(%(precision)s, %(scale)s)" % {
7660 "precision": type_.precision,
7661 "scale": type_.scale,
7662 }
7664 def visit_DECIMAL(self, type_: sqltypes.DECIMAL[Any], **kw: Any) -> str:
7665 if type_.precision is None:
7666 return "DECIMAL"
7667 elif type_.scale is None:
7668 return "DECIMAL(%(precision)s)" % {"precision": type_.precision}
7669 else:
7670 return "DECIMAL(%(precision)s, %(scale)s)" % {
7671 "precision": type_.precision,
7672 "scale": type_.scale,
7673 }
7675 def visit_INTEGER(self, type_: sqltypes.Integer, **kw: Any) -> str:
7676 return "INTEGER"
7678 def visit_SMALLINT(self, type_: sqltypes.SmallInteger, **kw: Any) -> str:
7679 return "SMALLINT"
7681 def visit_BIGINT(self, type_: sqltypes.BigInteger, **kw: Any) -> str:
7682 return "BIGINT"
7684 def visit_TIMESTAMP(self, type_: sqltypes.TIMESTAMP, **kw: Any) -> str:
7685 return "TIMESTAMP"
7687 def visit_DATETIME(self, type_: sqltypes.DateTime, **kw: Any) -> str:
7688 return "DATETIME"
7690 def visit_DATE(self, type_: sqltypes.Date, **kw: Any) -> str:
7691 return "DATE"
7693 def visit_TIME(self, type_: sqltypes.Time, **kw: Any) -> str:
7694 return "TIME"
7696 def visit_CLOB(self, type_: sqltypes.CLOB, **kw: Any) -> str:
7697 return "CLOB"
7699 def visit_NCLOB(self, type_: sqltypes.Text, **kw: Any) -> str:
7700 return "NCLOB"
7702 def _render_string_type(
7703 self,
7704 name: str,
7705 length: Optional[int],
7706 collation: Optional[str],
7707 collation_schema: Optional[str] = None,
7708 identifier_preparer: Optional[IdentifierPreparer] = None,
7709 **kw: Any,
7710 ) -> str:
7711 text = name
7712 if length:
7713 text += f"({length})"
7714 if collation:
7715 if identifier_preparer is None:
7716 identifier_preparer = self.dialect.identifier_preparer
7717 text += " COLLATE " + identifier_preparer.format_collation(
7718 collation, collation_schema
7719 )
7720 return text
7722 def visit_CHAR(self, type_: sqltypes.CHAR, **kw: Any) -> str:
7723 return self._render_string_type(
7724 "CHAR",
7725 type_.length,
7726 type_.collation,
7727 type_.collation_schema,
7728 **kw,
7729 )
7731 def visit_NCHAR(self, type_: sqltypes.NCHAR, **kw: Any) -> str:
7732 return self._render_string_type(
7733 "NCHAR",
7734 type_.length,
7735 type_.collation,
7736 type_.collation_schema,
7737 **kw,
7738 )
7740 def visit_VARCHAR(self, type_: sqltypes.String, **kw: Any) -> str:
7741 return self._render_string_type(
7742 "VARCHAR",
7743 type_.length,
7744 type_.collation,
7745 type_.collation_schema,
7746 **kw,
7747 )
7749 def visit_NVARCHAR(self, type_: sqltypes.NVARCHAR, **kw: Any) -> str:
7750 return self._render_string_type(
7751 "NVARCHAR",
7752 type_.length,
7753 type_.collation,
7754 type_.collation_schema,
7755 **kw,
7756 )
7758 def visit_TEXT(self, type_: sqltypes.Text, **kw: Any) -> str:
7759 return self._render_string_type(
7760 "TEXT",
7761 type_.length,
7762 type_.collation,
7763 type_.collation_schema,
7764 **kw,
7765 )
7767 def visit_UUID(self, type_: sqltypes.Uuid[Any], **kw: Any) -> str:
7768 return "UUID"
7770 def visit_BLOB(self, type_: sqltypes.LargeBinary, **kw: Any) -> str:
7771 return "BLOB"
7773 def visit_BINARY(self, type_: sqltypes.BINARY, **kw: Any) -> str:
7774 return "BINARY" + (type_.length and "(%d)" % type_.length or "")
7776 def visit_VARBINARY(self, type_: sqltypes.VARBINARY, **kw: Any) -> str:
7777 return "VARBINARY" + (type_.length and "(%d)" % type_.length or "")
7779 def visit_BOOLEAN(self, type_: sqltypes.Boolean, **kw: Any) -> str:
7780 return "BOOLEAN"
7782 def visit_uuid(self, type_: sqltypes.Uuid[Any], **kw: Any) -> str:
7783 if not type_.native_uuid or not self.dialect.supports_native_uuid:
7784 return self._render_string_type(
7785 "CHAR",
7786 length=32,
7787 collation=None,
7788 collation_schema=None,
7789 **kw,
7790 )
7791 else:
7792 return self.visit_UUID(type_, **kw)
7794 def visit_large_binary(
7795 self, type_: sqltypes.LargeBinary, **kw: Any
7796 ) -> str:
7797 return self.visit_BLOB(type_, **kw)
7799 def visit_boolean(self, type_: sqltypes.Boolean, **kw: Any) -> str:
7800 return self.visit_BOOLEAN(type_, **kw)
7802 def visit_time(self, type_: sqltypes.Time, **kw: Any) -> str:
7803 return self.visit_TIME(type_, **kw)
7805 def visit_datetime(self, type_: sqltypes.DateTime, **kw: Any) -> str:
7806 return self.visit_DATETIME(type_, **kw)
7808 def visit_date(self, type_: sqltypes.Date, **kw: Any) -> str:
7809 return self.visit_DATE(type_, **kw)
7811 def visit_big_integer(self, type_: sqltypes.BigInteger, **kw: Any) -> str:
7812 return self.visit_BIGINT(type_, **kw)
7814 def visit_small_integer(
7815 self, type_: sqltypes.SmallInteger, **kw: Any
7816 ) -> str:
7817 return self.visit_SMALLINT(type_, **kw)
7819 def visit_integer(self, type_: sqltypes.Integer, **kw: Any) -> str:
7820 return self.visit_INTEGER(type_, **kw)
7822 def visit_real(self, type_: sqltypes.REAL[Any], **kw: Any) -> str:
7823 return self.visit_REAL(type_, **kw)
7825 def visit_float(self, type_: sqltypes.Float[Any], **kw: Any) -> str:
7826 return self.visit_FLOAT(type_, **kw)
7828 def visit_double(self, type_: sqltypes.Double[Any], **kw: Any) -> str:
7829 return self.visit_DOUBLE(type_, **kw)
7831 def visit_numeric(self, type_: sqltypes.Numeric[Any], **kw: Any) -> str:
7832 return self.visit_NUMERIC(type_, **kw)
7834 def visit_string(self, type_: sqltypes.String, **kw: Any) -> str:
7835 return self.visit_VARCHAR(type_, **kw)
7837 def visit_unicode(self, type_: sqltypes.Unicode, **kw: Any) -> str:
7838 return self.visit_VARCHAR(type_, **kw)
7840 def visit_text(self, type_: sqltypes.Text, **kw: Any) -> str:
7841 return self.visit_TEXT(type_, **kw)
7843 def visit_unicode_text(
7844 self, type_: sqltypes.UnicodeText, **kw: Any
7845 ) -> str:
7846 return self.visit_TEXT(type_, **kw)
7848 def visit_enum(self, type_: sqltypes.Enum, **kw: Any) -> str:
7849 return self.visit_VARCHAR(type_, **kw)
7851 def visit_null(self, type_, **kw):
7852 raise exc.CompileError(
7853 "Can't generate DDL for %r; "
7854 "did you forget to specify a "
7855 "type on this Column?" % type_
7856 )
7858 def visit_type_decorator(
7859 self, type_: TypeDecorator[Any], **kw: Any
7860 ) -> str:
7861 return self.process(type_.type_engine(self.dialect), **kw)
7863 def visit_user_defined(
7864 self, type_: UserDefinedType[Any], **kw: Any
7865 ) -> str:
7866 return type_.get_col_spec(**kw)
7869class StrSQLTypeCompiler(GenericTypeCompiler):
7870 def process(self, type_, **kw):
7871 try:
7872 _compiler_dispatch = type_._compiler_dispatch
7873 except AttributeError:
7874 return self._visit_unknown(type_, **kw)
7875 else:
7876 return _compiler_dispatch(self, **kw)
7878 def __getattr__(self, key):
7879 if key.startswith("visit_"):
7880 return self._visit_unknown
7881 else:
7882 raise AttributeError(key)
7884 def _visit_unknown(self, type_, **kw):
7885 if type_.__class__.__name__ == type_.__class__.__name__.upper():
7886 return type_.__class__.__name__
7887 else:
7888 return repr(type_)
7890 def visit_null(self, type_, **kw):
7891 return "NULL"
7893 def visit_user_defined(self, type_, **kw):
7894 try:
7895 get_col_spec = type_.get_col_spec
7896 except AttributeError:
7897 return repr(type_)
7898 else:
7899 return get_col_spec(**kw)
7902class _SchemaForObjectCallable(Protocol):
7903 def __call__(self, obj: Any, /) -> str: ...
7906class _BindNameForColProtocol(Protocol):
7907 def __call__(self, col: ColumnClause[Any]) -> str: ...
7910class IdentifierPreparer:
7911 """Handle quoting and case-folding of identifiers based on options."""
7913 reserved_words = RESERVED_WORDS
7915 legal_characters = LEGAL_CHARACTERS
7917 illegal_initial_characters = ILLEGAL_INITIAL_CHARACTERS
7919 initial_quote: str
7921 final_quote: str
7923 _strings: MutableMapping[str, str]
7925 schema_for_object: _SchemaForObjectCallable = operator.attrgetter("schema")
7926 """Return the .schema attribute for an object.
7928 For the default IdentifierPreparer, the schema for an object is always
7929 the value of the ".schema" attribute. if the preparer is replaced
7930 with one that has a non-empty schema_translate_map, the value of the
7931 ".schema" attribute is rendered a symbol that will be converted to a
7932 real schema name from the mapping post-compile.
7934 """
7936 _includes_none_schema_translate: bool = False
7938 def __init__(
7939 self,
7940 dialect: Dialect,
7941 initial_quote: str = '"',
7942 final_quote: Optional[str] = None,
7943 escape_quote: str = '"',
7944 quote_case_sensitive_collations: bool = True,
7945 omit_schema: bool = False,
7946 ):
7947 """Construct a new ``IdentifierPreparer`` object.
7949 initial_quote
7950 Character that begins a delimited identifier.
7952 final_quote
7953 Character that ends a delimited identifier. Defaults to
7954 `initial_quote`.
7956 omit_schema
7957 Prevent prepending schema name. Useful for databases that do
7958 not support schemae.
7959 """
7961 self.dialect = dialect
7962 self.initial_quote = initial_quote
7963 self.final_quote = final_quote or self.initial_quote
7964 self.escape_quote = escape_quote
7965 self.escape_to_quote = self.escape_quote * 2
7966 self.omit_schema = omit_schema
7967 self.quote_case_sensitive_collations = quote_case_sensitive_collations
7968 self._strings = {}
7969 self._double_percents = self.dialect.paramstyle in (
7970 "format",
7971 "pyformat",
7972 )
7974 def _with_schema_translate(self, schema_translate_map):
7975 prep = self.__class__.__new__(self.__class__)
7976 prep.__dict__.update(self.__dict__)
7978 includes_none = None in schema_translate_map
7980 def symbol_getter(obj):
7981 name = obj.schema
7982 if obj._use_schema_map and (name is not None or includes_none):
7983 if name is not None and ("[" in name or "]" in name):
7984 raise exc.CompileError(
7985 "Square bracket characters ([]) not supported "
7986 "in schema translate name '%s'" % name
7987 )
7988 return quoted_name(
7989 "__[SCHEMA_%s]" % (name or "_none"), quote=False
7990 )
7991 else:
7992 return obj.schema
7994 prep.schema_for_object = symbol_getter
7995 prep._includes_none_schema_translate = includes_none
7996 return prep
7998 def _render_schema_translates(
7999 self, statement: str, schema_translate_map: SchemaTranslateMapType
8000 ) -> str:
8001 d = schema_translate_map
8002 if None in d:
8003 if not self._includes_none_schema_translate:
8004 raise exc.InvalidRequestError(
8005 "schema translate map which previously did not have "
8006 "`None` present as a key now has `None` present; compiled "
8007 "statement may lack adequate placeholders. Please use "
8008 "consistent keys in successive "
8009 "schema_translate_map dictionaries."
8010 )
8012 d["_none"] = d[None] # type: ignore[index]
8014 def replace(m):
8015 name = m.group(2)
8016 if name in d:
8017 effective_schema = d[name]
8018 else:
8019 if name in (None, "_none"):
8020 raise exc.InvalidRequestError(
8021 "schema translate map which previously had `None` "
8022 "present as a key now no longer has it present; don't "
8023 "know how to apply schema for compiled statement. "
8024 "Please use consistent keys in successive "
8025 "schema_translate_map dictionaries."
8026 )
8027 effective_schema = name
8029 if not effective_schema:
8030 effective_schema = self.dialect.default_schema_name
8031 if not effective_schema:
8032 # TODO: no coverage here
8033 raise exc.CompileError(
8034 "Dialect has no default schema name; can't "
8035 "use None as dynamic schema target."
8036 )
8037 return self.quote_schema(effective_schema)
8039 return re.sub(r"(__\[SCHEMA_([^\]]+)\])", replace, statement)
8041 def _escape_identifier(self, value: str) -> str:
8042 """Escape an identifier.
8044 Subclasses should override this to provide database-dependent
8045 escaping behavior.
8046 """
8048 value = value.replace(self.escape_quote, self.escape_to_quote)
8049 if self._double_percents:
8050 value = value.replace("%", "%%")
8051 return value
8053 def _unescape_identifier(self, value: str) -> str:
8054 """Canonicalize an escaped identifier.
8056 Subclasses should override this to provide database-dependent
8057 unescaping behavior that reverses _escape_identifier.
8058 """
8060 return value.replace(self.escape_to_quote, self.escape_quote)
8062 def validate_sql_phrase(self, element, reg):
8063 """keyword sequence filter.
8065 a filter for elements that are intended to represent keyword sequences,
8066 such as "INITIALLY", "INITIALLY DEFERRED", etc. no special characters
8067 should be present.
8069 """
8071 if element is not None and not reg.match(element):
8072 raise exc.CompileError(
8073 "Unexpected SQL phrase: %r (matching against %r)"
8074 % (element, reg.pattern)
8075 )
8076 return element
8078 def quote_identifier(self, value: str) -> str:
8079 """Quote an identifier.
8081 Subclasses should override this to provide database-dependent
8082 quoting behavior.
8083 """
8085 return (
8086 self.initial_quote
8087 + self._escape_identifier(value)
8088 + self.final_quote
8089 )
8091 def _requires_quotes(self, value: str) -> bool:
8092 """Return True if the given identifier requires quoting."""
8093 lc_value = value.lower()
8094 return (
8095 lc_value in self.reserved_words
8096 or value[0] in self.illegal_initial_characters
8097 or not self.legal_characters.match(str(value))
8098 or (lc_value != value)
8099 )
8101 def _requires_quotes_illegal_chars(self, value):
8102 """Return True if the given identifier requires quoting, but
8103 not taking case convention into account."""
8104 return not self.legal_characters.match(str(value))
8106 def quote_schema(self, schema: str) -> str:
8107 """Conditionally quote a schema name.
8110 The name is quoted if it is a reserved word, contains quote-necessary
8111 characters, or is an instance of :class:`.quoted_name` which includes
8112 ``quote`` set to ``True``.
8114 Subclasses can override this to provide database-dependent
8115 quoting behavior for schema names.
8117 :param schema: string schema name
8118 """
8119 return self.quote(schema)
8121 def quote(self, ident: str) -> str:
8122 """Conditionally quote an identifier.
8124 The identifier is quoted if it is a reserved word, contains
8125 quote-necessary characters, or is an instance of
8126 :class:`.quoted_name` which includes ``quote`` set to ``True``.
8128 Subclasses can override this to provide database-dependent
8129 quoting behavior for identifier names.
8131 :param ident: string identifier
8132 """
8133 force = getattr(ident, "quote", None)
8135 if force is None:
8136 if ident in self._strings:
8137 return self._strings[ident]
8138 else:
8139 if self._requires_quotes(ident):
8140 self._strings[ident] = self.quote_identifier(ident)
8141 else:
8142 self._strings[ident] = ident
8143 return self._strings[ident]
8144 elif force:
8145 return self.quote_identifier(ident)
8146 else:
8147 return ident
8149 def format_collation(self, collation_name, collation_schema=None):
8150 if self.quote_case_sensitive_collations:
8151 name = self.quote(collation_name)
8152 else:
8153 name = collation_name
8155 if collation_schema is not None:
8156 return f"{self.quote_schema(collation_schema)}.{name}"
8157 else:
8158 return name
8160 def format_sequence(
8161 self, sequence: schema.Sequence, use_schema: bool = True
8162 ) -> str:
8163 name = self.quote(sequence.name)
8165 effective_schema = self.schema_for_object(sequence)
8167 if (
8168 not self.omit_schema
8169 and use_schema
8170 and effective_schema is not None
8171 ):
8172 name = self.quote_schema(effective_schema) + "." + name
8173 return name
8175 def format_label(
8176 self, label: Label[Any], name: Optional[str] = None
8177 ) -> str:
8178 return self.quote(name or label.name)
8180 def format_alias(
8181 self, alias: Optional[AliasedReturnsRows], name: Optional[str] = None
8182 ) -> str:
8183 if name is None:
8184 assert alias is not None
8185 return self.quote(alias.name)
8186 else:
8187 return self.quote(name)
8189 def format_savepoint(self, savepoint, name=None):
8190 # Running the savepoint name through quoting is unnecessary
8191 # for all known dialects. This is here to support potential
8192 # third party use cases
8193 ident = name or savepoint.ident
8194 if self._requires_quotes(ident):
8195 ident = self.quote_identifier(ident)
8196 return ident
8198 @util.preload_module("sqlalchemy.sql.naming")
8199 def format_constraint(
8200 self, constraint: Union[Constraint, Index], _alembic_quote: bool = True
8201 ) -> Optional[str]:
8202 naming = util.preloaded.sql_naming
8204 if constraint.name is _NONE_NAME:
8205 name = naming._constraint_name_for_table(
8206 constraint, constraint.table
8207 )
8209 if name is None:
8210 return None
8211 else:
8212 name = constraint.name
8214 assert name is not None
8215 if constraint.__visit_name__ == "index":
8216 return self.truncate_and_render_index_name(
8217 name, _alembic_quote=_alembic_quote
8218 )
8219 else:
8220 return self.truncate_and_render_constraint_name(
8221 name, _alembic_quote=_alembic_quote
8222 )
8224 def truncate_and_render_index_name(
8225 self, name: str, _alembic_quote: bool = True
8226 ) -> str:
8227 # calculate these at format time so that ad-hoc changes
8228 # to dialect.max_identifier_length etc. can be reflected
8229 # as IdentifierPreparer is long lived
8230 max_ = (
8231 self.dialect.max_index_name_length
8232 or self.dialect.max_identifier_length
8233 )
8234 return self._truncate_and_render_maxlen_name(
8235 name, max_, _alembic_quote
8236 )
8238 def truncate_and_render_constraint_name(
8239 self, name: str, _alembic_quote: bool = True
8240 ) -> str:
8241 # calculate these at format time so that ad-hoc changes
8242 # to dialect.max_identifier_length etc. can be reflected
8243 # as IdentifierPreparer is long lived
8244 max_ = (
8245 self.dialect.max_constraint_name_length
8246 or self.dialect.max_identifier_length
8247 )
8248 return self._truncate_and_render_maxlen_name(
8249 name, max_, _alembic_quote
8250 )
8252 def _truncate_and_render_maxlen_name(
8253 self, name: str, max_: int, _alembic_quote: bool
8254 ) -> str:
8255 if isinstance(name, elements._truncated_label):
8256 if len(name) > max_:
8257 name = name[0 : max_ - 8] + "_" + util.md5_hex(name)[-4:]
8258 else:
8259 self.dialect.validate_identifier(name)
8261 if not _alembic_quote:
8262 return name
8263 else:
8264 return self.quote(name)
8266 def format_index(self, index: Index) -> str:
8267 name = self.format_constraint(index)
8268 assert name is not None
8269 return name
8271 def format_table(
8272 self,
8273 table: FromClause,
8274 use_schema: bool = True,
8275 name: Optional[str] = None,
8276 ) -> str:
8277 """Prepare a quoted table and schema name."""
8278 if name is None:
8279 if TYPE_CHECKING:
8280 assert isinstance(table, NamedFromClause)
8281 name = table.name
8283 result = self.quote(name)
8285 effective_schema = self.schema_for_object(table)
8287 if not self.omit_schema and use_schema and effective_schema:
8288 result = self.quote_schema(effective_schema) + "." + result
8289 return result
8291 def format_schema(self, name):
8292 """Prepare a quoted schema name."""
8294 return self.quote(name)
8296 def format_label_name(
8297 self,
8298 name,
8299 anon_map=None,
8300 ):
8301 """Prepare a quoted column name."""
8303 if anon_map is not None and isinstance(
8304 name, elements._truncated_label
8305 ):
8306 name = name.apply_map(anon_map)
8308 return self.quote(name)
8310 def format_column(
8311 self,
8312 column: ColumnElement[Any],
8313 use_table: bool = False,
8314 name: Optional[str] = None,
8315 table_name: Optional[str] = None,
8316 use_schema: bool = False,
8317 anon_map: Optional[Mapping[str, Any]] = None,
8318 ) -> str:
8319 """Prepare a quoted column name."""
8321 if name is None:
8322 name = column.name
8323 assert name is not None
8325 if anon_map is not None and isinstance(
8326 name, elements._truncated_label
8327 ):
8328 name = name.apply_map(anon_map)
8330 if not getattr(column, "is_literal", False):
8331 if use_table:
8332 return (
8333 self.format_table(
8334 column.table, use_schema=use_schema, name=table_name
8335 )
8336 + "."
8337 + self.quote(name)
8338 )
8339 else:
8340 return self.quote(name)
8341 else:
8342 # literal textual elements get stuck into ColumnClause a lot,
8343 # which shouldn't get quoted
8345 if use_table:
8346 return (
8347 self.format_table(
8348 column.table, use_schema=use_schema, name=table_name
8349 )
8350 + "."
8351 + name
8352 )
8353 else:
8354 return name
8356 def format_table_seq(self, table, use_schema=True):
8357 """Format table name and schema as a tuple."""
8359 # Dialects with more levels in their fully qualified references
8360 # ('database', 'owner', etc.) could override this and return
8361 # a longer sequence.
8363 effective_schema = self.schema_for_object(table)
8365 if not self.omit_schema and use_schema and effective_schema:
8366 return (
8367 self.quote_schema(effective_schema),
8368 self.format_table(table, use_schema=False),
8369 )
8370 else:
8371 return (self.format_table(table, use_schema=False),)
8373 @util.memoized_property
8374 def _r_identifiers(self):
8375 initial, final, escaped_final = (
8376 re.escape(s)
8377 for s in (
8378 self.initial_quote,
8379 self.final_quote,
8380 self._escape_identifier(self.final_quote),
8381 )
8382 )
8383 r = re.compile(
8384 r"(?:"
8385 r"(?:%(initial)s((?:%(escaped)s|[^%(final)s])+)%(final)s"
8386 r"|([^\.]+))(?=\.|$))+"
8387 % {"initial": initial, "final": final, "escaped": escaped_final}
8388 )
8389 return r
8391 def unformat_identifiers(self, identifiers: str) -> Sequence[str]:
8392 """Unpack 'schema.table.column'-like strings into components."""
8394 r = self._r_identifiers
8395 return [
8396 self._unescape_identifier(i)
8397 for i in [a or b for a, b in r.findall(identifiers)]
8398 ]