codekingpro/portable-devtools
115k
1# sql/selectable.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8"""The :class:`_expression.FromClause` class of SQL expression elements,
9representing
10SQL tables and derived rowsets.
11
12"""
13
14from __future__ import annotations
15
16import collections
17from enum import Enum
18import itertools
19from typing import AbstractSet
20from typing import Any as TODO_Any
21from typing import Any
22from typing import Callable
23from typing import cast
24from typing import Dict
25from typing import Generic
26from typing import Iterable
27from typing import Iterator
28from typing import List
29from typing import NamedTuple
30from typing import NoReturn
31from typing import Optional
32from typing import overload
33from typing import Sequence
34from typing import Set
35from typing import Tuple
36from typing import Type
37from typing import TYPE_CHECKING
38from typing import TypeVar
39from typing import Union
40
41from . import cache_key
42from . import coercions
43from . import operators
44from . import roles
45from . import traversals
46from . import type_api
47from . import visitors
48from ._typing import _ColumnsClauseArgument
49from ._typing import _no_kw
50from ._typing import _TP
51from ._typing import is_column_element
52from ._typing import is_select_statement
53from ._typing import is_subquery
54from ._typing import is_table
55from ._typing import is_text_clause
56from .annotation import Annotated
57from .annotation import SupportsCloneAnnotations
58from .base import _clone
59from .base import _cloned_difference
60from .base import _cloned_intersection
61from .base import _entity_namespace_key
62from .base import _EntityNamespace
63from .base import _expand_cloned
64from .base import _from_objects
65from .base import _generative
66from .base import _never_select_column
67from .base import _NoArg
68from .base import _select_iterables
69from .base import CacheableOptions
70from .base import ColumnCollection
71from .base import ColumnSet
72from .base import CompileState
73from .base import DedupeColumnCollection
74from .base import Executable
75from .base import Generative
76from .base import HasCompileState
77from .base import HasMemoized
78from .base import Immutable
79from .coercions import _document_text_coercion
80from .elements import _anonymous_label
81from .elements import BindParameter
82from .elements import BooleanClauseList
83from .elements import ClauseElement
84from .elements import ClauseList
85from .elements import ColumnClause
86from .elements import ColumnElement
87from .elements import DQLDMLClauseElement
88from .elements import GroupedElement
89from .elements import literal_column
90from .elements import TableValuedColumn
91from .elements import UnaryExpression
92from .operators import OperatorType
93from .sqltypes import NULLTYPE
94from .visitors import _TraverseInternalsType
95from .visitors import InternalTraversal
96from .visitors import prefix_anon_map
97from .. import exc
98from .. import util
99from ..util import HasMemoized_ro_memoized_attribute
100from ..util.typing import Literal
101from ..util.typing import Protocol
102from ..util.typing import Self
103
104and_ = BooleanClauseList.and_
105
106_T = TypeVar("_T", bound=Any)
107
108if TYPE_CHECKING:
109 from ._typing import _ColumnExpressionArgument
110 from ._typing import _ColumnExpressionOrStrLabelArgument
111 from ._typing import _FromClauseArgument
112 from ._typing import _JoinTargetArgument
113 from ._typing import _LimitOffsetType
114 from ._typing import _MAYBE_ENTITY
115 from ._typing import _NOT_ENTITY
116 from ._typing import _OnClauseArgument
117 from ._typing import _SelectStatementForCompoundArgument
118 from ._typing import _T0
119 from ._typing import _T1
120 from ._typing import _T2
121 from ._typing import _T3
122 from ._typing import _T4
123 from ._typing import _T5
124 from ._typing import _T6
125 from ._typing import _T7
126 from ._typing import _TextCoercedExpressionArgument
127 from ._typing import _TypedColumnClauseArgument as _TCCA
128 from ._typing import _TypeEngineArgument
129 from .base import _AmbiguousTableNameMap
130 from .base import ExecutableOption
131 from .base import ReadOnlyColumnCollection
132 from .cache_key import _CacheKeyTraversalType
133 from .compiler import SQLCompiler
134 from .dml import Delete
135 from .dml import Update
136 from .elements import BinaryExpression
137 from .elements import KeyedColumnElement
138 from .elements import Label
139 from .elements import NamedColumn
140 from .elements import TextClause
141 from .functions import Function
142 from .schema import ForeignKey
143 from .schema import ForeignKeyConstraint
144 from .sqltypes import TableValueType
145 from .type_api import TypeEngine
146 from .visitors import _CloneCallableType
147
148
149_ColumnsClauseElement = Union["FromClause", ColumnElement[Any], "TextClause"]
150_LabelConventionCallable = Callable[
151 [Union["ColumnElement[Any]", "TextClause"]], Optional[str]
152]
153
154
155class _JoinTargetProtocol(Protocol):
156 @util.ro_non_memoized_property
157 def _from_objects(self) -> List[FromClause]: ...
158
159 @util.ro_non_memoized_property
160 def entity_namespace(self) -> _EntityNamespace: ...
161
162
163_JoinTargetElement = Union["FromClause", _JoinTargetProtocol]
164_OnClauseElement = Union["ColumnElement[bool]", _JoinTargetProtocol]
165
166_ForUpdateOfArgument = Union[
167 # single column, Table, ORM Entity
168 Union[
169 "_ColumnExpressionArgument[Any]",
170 "_FromClauseArgument",
171 ],
172 # or sequence of single column elements
173 Sequence["_ColumnExpressionArgument[Any]"],
174]
175
176
177_SetupJoinsElement = Tuple[
178 _JoinTargetElement,
179 Optional[_OnClauseElement],
180 Optional["FromClause"],
181 Dict[str, Any],
182]
183
184
185_SelectIterable = Iterable[Union["ColumnElement[Any]", "TextClause"]]
186
187
188class _OffsetLimitParam(BindParameter[int]):
189 inherit_cache = True
190
191 @property
192 def _limit_offset_value(self) -> Optional[int]:
193 return self.effective_value
194
195
196class ReturnsRows(roles.ReturnsRowsRole, DQLDMLClauseElement):
197 """The base-most class for Core constructs that have some concept of
198 columns that can represent rows.
199
200 While the SELECT statement and TABLE are the primary things we think
201 of in this category, DML like INSERT, UPDATE and DELETE can also specify
202 RETURNING which means they can be used in CTEs and other forms, and
203 PostgreSQL has functions that return rows also.
204
205 .. versionadded:: 1.4
206
207 """
208
209 _is_returns_rows = True
210
211 # sub-elements of returns_rows
212 _is_from_clause = False
213 _is_select_base = False
214 _is_select_statement = False
215 _is_lateral = False
216
217 @property
218 def selectable(self) -> ReturnsRows:
219 return self
220
221 @util.ro_non_memoized_property
222 def _all_selected_columns(self) -> _SelectIterable:
223 """A sequence of column expression objects that represents the
224 "selected" columns of this :class:`_expression.ReturnsRows`.
225
226 This is typically equivalent to .exported_columns except it is
227 delivered in the form of a straight sequence and not keyed
228 :class:`_expression.ColumnCollection`.
229
230 """
231 raise NotImplementedError()
232
233 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
234 """Return ``True`` if this :class:`.ReturnsRows` is
235 'derived' from the given :class:`.FromClause`.
236
237 An example would be an Alias of a Table is derived from that Table.
238
239 """
240 raise NotImplementedError()
241
242 def _generate_fromclause_column_proxies(
243 self, fromclause: FromClause
244 ) -> None:
245 """Populate columns into an :class:`.AliasedReturnsRows` object."""
246
247 raise NotImplementedError()
248
249 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:
250 """reset internal collections for an incoming column being added."""
251 raise NotImplementedError()
252
253 @property
254 def exported_columns(self) -> ReadOnlyColumnCollection[Any, Any]:
255 """A :class:`_expression.ColumnCollection`
256 that represents the "exported"
257 columns of this :class:`_expression.ReturnsRows`.
258
259 The "exported" columns represent the collection of
260 :class:`_expression.ColumnElement`
261 expressions that are rendered by this SQL
262 construct. There are primary varieties which are the
263 "FROM clause columns" of a FROM clause, such as a table, join,
264 or subquery, the "SELECTed columns", which are the columns in
265 the "columns clause" of a SELECT statement, and the RETURNING
266 columns in a DML statement..
267
268 .. versionadded:: 1.4
269
270 .. seealso::
271
272 :attr:`_expression.FromClause.exported_columns`
273
274 :attr:`_expression.SelectBase.exported_columns`
275 """
276
277 raise NotImplementedError()
278
279
280class ExecutableReturnsRows(Executable, ReturnsRows):
281 """base for executable statements that return rows."""
282
283
284class TypedReturnsRows(ExecutableReturnsRows, Generic[_TP]):
285 """base for executable statements that return rows."""
286
287
288class Selectable(ReturnsRows):
289 """Mark a class as being selectable."""
290
291 __visit_name__ = "selectable"
292
293 is_selectable = True
294
295 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:
296 raise NotImplementedError()
297
298 def lateral(self, name: Optional[str] = None) -> LateralFromClause:
299 """Return a LATERAL alias of this :class:`_expression.Selectable`.
300
301 The return value is the :class:`_expression.Lateral` construct also
302 provided by the top-level :func:`_expression.lateral` function.
303
304 .. seealso::
305
306 :ref:`tutorial_lateral_correlation` - overview of usage.
307
308 """
309 return Lateral._construct(self, name=name)
310
311 @util.deprecated(
312 "1.4",
313 message="The :meth:`.Selectable.replace_selectable` method is "
314 "deprecated, and will be removed in a future release. Similar "
315 "functionality is available via the sqlalchemy.sql.visitors module.",
316 )
317 @util.preload_module("sqlalchemy.sql.util")
318 def replace_selectable(self, old: FromClause, alias: Alias) -> Self:
319 """Replace all occurrences of :class:`_expression.FromClause`
320 'old' with the given :class:`_expression.Alias`
321 object, returning a copy of this :class:`_expression.FromClause`.
322
323 """
324 return util.preloaded.sql_util.ClauseAdapter(alias).traverse(self)
325
326 def corresponding_column(
327 self, column: KeyedColumnElement[Any], require_embedded: bool = False
328 ) -> Optional[KeyedColumnElement[Any]]:
329 """Given a :class:`_expression.ColumnElement`, return the exported
330 :class:`_expression.ColumnElement` object from the
331 :attr:`_expression.Selectable.exported_columns`
332 collection of this :class:`_expression.Selectable`
333 which corresponds to that
334 original :class:`_expression.ColumnElement` via a common ancestor
335 column.
336
337 :param column: the target :class:`_expression.ColumnElement`
338 to be matched.
339
340 :param require_embedded: only return corresponding columns for
341 the given :class:`_expression.ColumnElement`, if the given
342 :class:`_expression.ColumnElement`
343 is actually present within a sub-element
344 of this :class:`_expression.Selectable`.
345 Normally the column will match if
346 it merely shares a common ancestor with one of the exported
347 columns of this :class:`_expression.Selectable`.
348
349 .. seealso::
350
351 :attr:`_expression.Selectable.exported_columns` - the
352 :class:`_expression.ColumnCollection`
353 that is used for the operation.
354
355 :meth:`_expression.ColumnCollection.corresponding_column`
356 - implementation
357 method.
358
359 """
360
361 return self.exported_columns.corresponding_column(
362 column, require_embedded
363 )
364
365
366class HasPrefixes:
367 _prefixes: Tuple[Tuple[DQLDMLClauseElement, str], ...] = ()
368
369 _has_prefixes_traverse_internals: _TraverseInternalsType = [
370 ("_prefixes", InternalTraversal.dp_prefix_sequence)
371 ]
372
373 @_generative
374 @_document_text_coercion(
375 "prefixes",
376 ":meth:`_expression.HasPrefixes.prefix_with`",
377 ":paramref:`.HasPrefixes.prefix_with.*prefixes`",
378 )
379 def prefix_with(
380 self,
381 *prefixes: _TextCoercedExpressionArgument[Any],
382 dialect: str = "*",
383 ) -> Self:
384 r"""Add one or more expressions following the statement keyword, i.e.
385 SELECT, INSERT, UPDATE, or DELETE. Generative.
386
387 This is used to support backend-specific prefix keywords such as those
388 provided by MySQL.
389
390 E.g.::
391
392 stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
393
394 # MySQL 5.7 optimizer hints
395 stmt = select(table).prefix_with(
396 "/*+ BKA(t1) */", dialect="mysql")
397
398 Multiple prefixes can be specified by multiple calls
399 to :meth:`_expression.HasPrefixes.prefix_with`.
400
401 :param \*prefixes: textual or :class:`_expression.ClauseElement`
402 construct which
403 will be rendered following the INSERT, UPDATE, or DELETE
404 keyword.
405 :param dialect: optional string dialect name which will
406 limit rendering of this prefix to only that dialect.
407
408 """
409 self._prefixes = self._prefixes + tuple(
410 [
411 (coercions.expect(roles.StatementOptionRole, p), dialect)
412 for p in prefixes
413 ]
414 )
415 return self
416
417
418class HasSuffixes:
419 _suffixes: Tuple[Tuple[DQLDMLClauseElement, str], ...] = ()
420
421 _has_suffixes_traverse_internals: _TraverseInternalsType = [
422 ("_suffixes", InternalTraversal.dp_prefix_sequence)
423 ]
424
425 @_generative
426 @_document_text_coercion(
427 "suffixes",
428 ":meth:`_expression.HasSuffixes.suffix_with`",
429 ":paramref:`.HasSuffixes.suffix_with.*suffixes`",
430 )
431 def suffix_with(
432 self,
433 *suffixes: _TextCoercedExpressionArgument[Any],
434 dialect: str = "*",
435 ) -> Self:
436 r"""Add one or more expressions following the statement as a whole.
437
438 This is used to support backend-specific suffix keywords on
439 certain constructs.
440
441 E.g.::
442
443 stmt = select(col1, col2).cte().suffix_with(
444 "cycle empno set y_cycle to 1 default 0", dialect="oracle")
445
446 Multiple suffixes can be specified by multiple calls
447 to :meth:`_expression.HasSuffixes.suffix_with`.
448
449 :param \*suffixes: textual or :class:`_expression.ClauseElement`
450 construct which
451 will be rendered following the target clause.
452 :param dialect: Optional string dialect name which will
453 limit rendering of this suffix to only that dialect.
454
455 """
456 self._suffixes = self._suffixes + tuple(
457 [
458 (coercions.expect(roles.StatementOptionRole, p), dialect)
459 for p in suffixes
460 ]
461 )
462 return self
463
464
465class HasHints:
466 _hints: util.immutabledict[Tuple[FromClause, str], str] = (
467 util.immutabledict()
468 )
469 _statement_hints: Tuple[Tuple[str, str], ...] = ()
470
471 _has_hints_traverse_internals: _TraverseInternalsType = [
472 ("_statement_hints", InternalTraversal.dp_statement_hint_list),
473 ("_hints", InternalTraversal.dp_table_hint_list),
474 ]
475
476 def with_statement_hint(self, text: str, dialect_name: str = "*") -> Self:
477 """Add a statement hint to this :class:`_expression.Select` or
478 other selectable object.
479
480 This method is similar to :meth:`_expression.Select.with_hint`
481 except that
482 it does not require an individual table, and instead applies to the
483 statement as a whole.
484
485 Hints here are specific to the backend database and may include
486 directives such as isolation levels, file directives, fetch directives,
487 etc.
488
489 .. seealso::
490
491 :meth:`_expression.Select.with_hint`
492
493 :meth:`_expression.Select.prefix_with` - generic SELECT prefixing
494 which also can suit some database-specific HINT syntaxes such as
495 MySQL optimizer hints
496
497 """
498 return self._with_hint(None, text, dialect_name)
499
500 @_generative
501 def with_hint(
502 self,
503 selectable: _FromClauseArgument,
504 text: str,
505 dialect_name: str = "*",
506 ) -> Self:
507 r"""Add an indexing or other executional context hint for the given
508 selectable to this :class:`_expression.Select` or other selectable
509 object.
510
511 The text of the hint is rendered in the appropriate
512 location for the database backend in use, relative
513 to the given :class:`_schema.Table` or :class:`_expression.Alias`
514 passed as the
515 ``selectable`` argument. The dialect implementation
516 typically uses Python string substitution syntax
517 with the token ``%(name)s`` to render the name of
518 the table or alias. E.g. when using Oracle, the
519 following::
520
521 select(mytable).\
522 with_hint(mytable, "index(%(name)s ix_mytable)")
523
524 Would render SQL as::
525
526 select /*+ index(mytable ix_mytable) */ ... from mytable
527
528 The ``dialect_name`` option will limit the rendering of a particular
529 hint to a particular backend. Such as, to add hints for both Oracle
530 and Sybase simultaneously::
531
532 select(mytable).\
533 with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
534 with_hint(mytable, "WITH INDEX ix_mytable", 'mssql')
535
536 .. seealso::
537
538 :meth:`_expression.Select.with_statement_hint`
539
540 """
541
542 return self._with_hint(selectable, text, dialect_name)
543
544 def _with_hint(
545 self,
546 selectable: Optional[_FromClauseArgument],
547 text: str,
548 dialect_name: str,
549 ) -> Self:
550 if selectable is None:
551 self._statement_hints += ((dialect_name, text),)
552 else:
553 self._hints = self._hints.union(
554 {
555 (
556 coercions.expect(roles.FromClauseRole, selectable),
557 dialect_name,
558 ): text
559 }
560 )
561 return self
562
563
564class FromClause(roles.AnonymizedFromClauseRole, Selectable):
565 """Represent an element that can be used within the ``FROM``
566 clause of a ``SELECT`` statement.
567
568 The most common forms of :class:`_expression.FromClause` are the
569 :class:`_schema.Table` and the :func:`_expression.select` constructs. Key
570 features common to all :class:`_expression.FromClause` objects include:
571
572 * a :attr:`.c` collection, which provides per-name access to a collection
573 of :class:`_expression.ColumnElement` objects.
574 * a :attr:`.primary_key` attribute, which is a collection of all those
575 :class:`_expression.ColumnElement`
576 objects that indicate the ``primary_key`` flag.
577 * Methods to generate various derivations of a "from" clause, including
578 :meth:`_expression.FromClause.alias`,
579 :meth:`_expression.FromClause.join`,
580 :meth:`_expression.FromClause.select`.
581
582
583 """
584
585 __visit_name__ = "fromclause"
586 named_with_column = False
587
588 @util.ro_non_memoized_property
589 def _hide_froms(self) -> Iterable[FromClause]:
590 return ()
591
592 _is_clone_of: Optional[FromClause]
593
594 _columns: ColumnCollection[Any, Any]
595
596 schema: Optional[str] = None
597 """Define the 'schema' attribute for this :class:`_expression.FromClause`.
598
599 This is typically ``None`` for most objects except that of
600 :class:`_schema.Table`, where it is taken as the value of the
601 :paramref:`_schema.Table.schema` argument.
602
603 """
604
605 is_selectable = True
606 _is_from_clause = True
607 _is_join = False
608
609 _use_schema_map = False
610
611 def select(self) -> Select[Any]:
612 r"""Return a SELECT of this :class:`_expression.FromClause`.
613
614
615 e.g.::
616
617 stmt = some_table.select().where(some_table.c.id == 5)
618
619 .. seealso::
620
621 :func:`_expression.select` - general purpose
622 method which allows for arbitrary column lists.
623
624 """
625 return Select(self)
626
627 def join(
628 self,
629 right: _FromClauseArgument,
630 onclause: Optional[_ColumnExpressionArgument[bool]] = None,
631 isouter: bool = False,
632 full: bool = False,
633 ) -> Join:
634 """Return a :class:`_expression.Join` from this
635 :class:`_expression.FromClause`
636 to another :class:`FromClause`.
637
638 E.g.::
639
640 from sqlalchemy import join
641
642 j = user_table.join(address_table,
643 user_table.c.id == address_table.c.user_id)
644 stmt = select(user_table).select_from(j)
645
646 would emit SQL along the lines of::
647
648 SELECT user.id, user.name FROM user
649 JOIN address ON user.id = address.user_id
650
651 :param right: the right side of the join; this is any
652 :class:`_expression.FromClause` object such as a
653 :class:`_schema.Table` object, and
654 may also be a selectable-compatible object such as an ORM-mapped
655 class.
656
657 :param onclause: a SQL expression representing the ON clause of the
658 join. If left at ``None``, :meth:`_expression.FromClause.join`
659 will attempt to
660 join the two tables based on a foreign key relationship.
661
662 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
663
664 :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER
665 JOIN. Implies :paramref:`.FromClause.join.isouter`.
666
667 .. seealso::
668
669 :func:`_expression.join` - standalone function
670
671 :class:`_expression.Join` - the type of object produced
672
673 """
674
675 return Join(self, right, onclause, isouter, full)
676
677 def outerjoin(
678 self,
679 right: _FromClauseArgument,
680 onclause: Optional[_ColumnExpressionArgument[bool]] = None,
681 full: bool = False,
682 ) -> Join:
683 """Return a :class:`_expression.Join` from this
684 :class:`_expression.FromClause`
685 to another :class:`FromClause`, with the "isouter" flag set to
686 True.
687
688 E.g.::
689
690 from sqlalchemy import outerjoin
691
692 j = user_table.outerjoin(address_table,
693 user_table.c.id == address_table.c.user_id)
694
695 The above is equivalent to::
696
697 j = user_table.join(
698 address_table,
699 user_table.c.id == address_table.c.user_id,
700 isouter=True)
701
702 :param right: the right side of the join; this is any
703 :class:`_expression.FromClause` object such as a
704 :class:`_schema.Table` object, and
705 may also be a selectable-compatible object such as an ORM-mapped
706 class.
707
708 :param onclause: a SQL expression representing the ON clause of the
709 join. If left at ``None``, :meth:`_expression.FromClause.join`
710 will attempt to
711 join the two tables based on a foreign key relationship.
712
713 :param full: if True, render a FULL OUTER JOIN, instead of
714 LEFT OUTER JOIN.
715
716 .. seealso::
717
718 :meth:`_expression.FromClause.join`
719
720 :class:`_expression.Join`
721
722 """
723
724 return Join(self, right, onclause, True, full)
725
726 def alias(
727 self, name: Optional[str] = None, flat: bool = False
728 ) -> NamedFromClause:
729 """Return an alias of this :class:`_expression.FromClause`.
730
731 E.g.::
732
733 a2 = some_table.alias('a2')
734
735 The above code creates an :class:`_expression.Alias`
736 object which can be used
737 as a FROM clause in any SELECT statement.
738
739 .. seealso::
740
741 :ref:`tutorial_using_aliases`
742
743 :func:`_expression.alias`
744
745 """
746
747 return Alias._construct(self, name=name)
748
749 def tablesample(
750 self,
751 sampling: Union[float, Function[Any]],
752 name: Optional[str] = None,
753 seed: Optional[roles.ExpressionElementRole[Any]] = None,
754 ) -> TableSample:
755 """Return a TABLESAMPLE alias of this :class:`_expression.FromClause`.
756
757 The return value is the :class:`_expression.TableSample`
758 construct also
759 provided by the top-level :func:`_expression.tablesample` function.
760
761 .. seealso::
762
763 :func:`_expression.tablesample` - usage guidelines and parameters
764
765 """
766 return TableSample._construct(
767 self, sampling=sampling, name=name, seed=seed
768 )
769
770 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
771 """Return ``True`` if this :class:`_expression.FromClause` is
772 'derived' from the given ``FromClause``.
773
774 An example would be an Alias of a Table is derived from that Table.
775
776 """
777 # this is essentially an "identity" check in the base class.
778 # Other constructs override this to traverse through
779 # contained elements.
780 return fromclause in self._cloned_set
781
782 def _is_lexical_equivalent(self, other: FromClause) -> bool:
783 """Return ``True`` if this :class:`_expression.FromClause` and
784 the other represent the same lexical identity.
785
786 This tests if either one is a copy of the other, or
787 if they are the same via annotation identity.
788
789 """
790 return bool(self._cloned_set.intersection(other._cloned_set))
791
792 @util.ro_non_memoized_property
793 def description(self) -> str:
794 """A brief description of this :class:`_expression.FromClause`.
795
796 Used primarily for error message formatting.
797
798 """
799 return getattr(self, "name", self.__class__.__name__ + " object")
800
801 def _generate_fromclause_column_proxies(
802 self, fromclause: FromClause
803 ) -> None:
804 fromclause._columns._populate_separate_keys(
805 col._make_proxy(fromclause) for col in self.c
806 )
807
808 @util.ro_non_memoized_property
809 def exported_columns(
810 self,
811 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
812 """A :class:`_expression.ColumnCollection`
813 that represents the "exported"
814 columns of this :class:`_expression.Selectable`.
815
816 The "exported" columns for a :class:`_expression.FromClause`
817 object are synonymous
818 with the :attr:`_expression.FromClause.columns` collection.
819
820 .. versionadded:: 1.4
821
822 .. seealso::
823
824 :attr:`_expression.Selectable.exported_columns`
825
826 :attr:`_expression.SelectBase.exported_columns`
827
828
829 """
830 return self.c
831
832 @util.ro_non_memoized_property
833 def columns(
834 self,
835 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
836 """A named-based collection of :class:`_expression.ColumnElement`
837 objects maintained by this :class:`_expression.FromClause`.
838
839 The :attr:`.columns`, or :attr:`.c` collection, is the gateway
840 to the construction of SQL expressions using table-bound or
841 other selectable-bound columns::
842
843 select(mytable).where(mytable.c.somecolumn == 5)
844
845 :return: a :class:`.ColumnCollection` object.
846
847 """
848 return self.c
849
850 @util.ro_memoized_property
851 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
852 """
853 A synonym for :attr:`.FromClause.columns`
854
855 :return: a :class:`.ColumnCollection`
856
857 """
858 if "_columns" not in self.__dict__:
859 self._init_collections()
860 self._populate_column_collection()
861 return self._columns.as_readonly()
862
863 @util.ro_non_memoized_property
864 def entity_namespace(self) -> _EntityNamespace:
865 """Return a namespace used for name-based access in SQL expressions.
866
867 This is the namespace that is used to resolve "filter_by()" type
868 expressions, such as::
869
870 stmt.filter_by(address='some address')
871
872 It defaults to the ``.c`` collection, however internally it can
873 be overridden using the "entity_namespace" annotation to deliver
874 alternative results.
875
876 """
877 return self.c
878
879 @util.ro_memoized_property
880 def primary_key(self) -> Iterable[NamedColumn[Any]]:
881 """Return the iterable collection of :class:`_schema.Column` objects
882 which comprise the primary key of this :class:`_selectable.FromClause`.
883
884 For a :class:`_schema.Table` object, this collection is represented
885 by the :class:`_schema.PrimaryKeyConstraint` which itself is an
886 iterable collection of :class:`_schema.Column` objects.
887
888 """
889 self._init_collections()
890 self._populate_column_collection()
891 return self.primary_key
892
893 @util.ro_memoized_property
894 def foreign_keys(self) -> Iterable[ForeignKey]:
895 """Return the collection of :class:`_schema.ForeignKey` marker objects
896 which this FromClause references.
897
898 Each :class:`_schema.ForeignKey` is a member of a
899 :class:`_schema.Table`-wide
900 :class:`_schema.ForeignKeyConstraint`.
901
902 .. seealso::
903
904 :attr:`_schema.Table.foreign_key_constraints`
905
906 """
907 self._init_collections()
908 self._populate_column_collection()
909 return self.foreign_keys
910
911 def _reset_column_collection(self) -> None:
912 """Reset the attributes linked to the ``FromClause.c`` attribute.
913
914 This collection is separate from all the other memoized things
915 as it has shown to be sensitive to being cleared out in situations
916 where enclosing code, typically in a replacement traversal scenario,
917 has already established strong relationships
918 with the exported columns.
919
920 The collection is cleared for the case where a table is having a
921 column added to it as well as within a Join during copy internals.
922
923 """
924
925 for key in ["_columns", "columns", "c", "primary_key", "foreign_keys"]:
926 self.__dict__.pop(key, None)
927
928 @util.ro_non_memoized_property
929 def _select_iterable(self) -> _SelectIterable:
930 return (c for c in self.c if not _never_select_column(c))
931
932 def _init_collections(self) -> None:
933 assert "_columns" not in self.__dict__
934 assert "primary_key" not in self.__dict__
935 assert "foreign_keys" not in self.__dict__
936
937 self._columns = ColumnCollection()
938 self.primary_key = ColumnSet() # type: ignore
939 self.foreign_keys = set() # type: ignore
940
941 @property
942 def _cols_populated(self) -> bool:
943 return "_columns" in self.__dict__
944
945 def _populate_column_collection(self) -> None:
946 """Called on subclasses to establish the .c collection.
947
948 Each implementation has a different way of establishing
949 this collection.
950
951 """
952
953 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:
954 """Given a column added to the .c collection of an underlying
955 selectable, produce the local version of that column, assuming this
956 selectable ultimately should proxy this column.
957
958 this is used to "ping" a derived selectable to add a new column
959 to its .c. collection when a Column has been added to one of the
960 Table objects it ultimately derives from.
961
962 If the given selectable hasn't populated its .c. collection yet,
963 it should at least pass on the message to the contained selectables,
964 but it will return None.
965
966 This method is currently used by Declarative to allow Table
967 columns to be added to a partially constructed inheritance
968 mapping that may have already produced joins. The method
969 isn't public right now, as the full span of implications
970 and/or caveats aren't yet clear.
971
972 It's also possible that this functionality could be invoked by
973 default via an event, which would require that
974 selectables maintain a weak referencing collection of all
975 derivations.
976
977 """
978 self._reset_column_collection()
979
980 def _anonymous_fromclause(
981 self, *, name: Optional[str] = None, flat: bool = False
982 ) -> FromClause:
983 return self.alias(name=name)
984
985 if TYPE_CHECKING:
986
987 def self_group(
988 self, against: Optional[OperatorType] = None
989 ) -> Union[FromGrouping, Self]: ...
990
991
992class NamedFromClause(FromClause):
993 """A :class:`.FromClause` that has a name.
994
995 Examples include tables, subqueries, CTEs, aliased tables.
996
997 .. versionadded:: 2.0
998
999 """
1000
1001 named_with_column = True
1002
1003 name: str
1004
1005 @util.preload_module("sqlalchemy.sql.sqltypes")
1006 def table_valued(self) -> TableValuedColumn[Any]:
1007 """Return a :class:`_sql.TableValuedColumn` object for this
1008 :class:`_expression.FromClause`.
1009
1010 A :class:`_sql.TableValuedColumn` is a :class:`_sql.ColumnElement` that
1011 represents a complete row in a table. Support for this construct is
1012 backend dependent, and is supported in various forms by backends
1013 such as PostgreSQL, Oracle and SQL Server.
1014
1015 E.g.:
1016
1017 .. sourcecode:: pycon+sql
1018
1019 >>> from sqlalchemy import select, column, func, table
1020 >>> a = table("a", column("id"), column("x"), column("y"))
1021 >>> stmt = select(func.row_to_json(a.table_valued()))
1022 >>> print(stmt)
1023 {printsql}SELECT row_to_json(a) AS row_to_json_1
1024 FROM a
1025
1026 .. versionadded:: 1.4.0b2
1027
1028 .. seealso::
1029
1030 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1031
1032 """
1033 return TableValuedColumn(self, type_api.TABLEVALUE)
1034
1035
1036class SelectLabelStyle(Enum):
1037 """Label style constants that may be passed to
1038 :meth:`_sql.Select.set_label_style`."""
1039
1040 LABEL_STYLE_NONE = 0
1041 """Label style indicating no automatic labeling should be applied to the
1042 columns clause of a SELECT statement.
1043
1044 Below, the columns named ``columna`` are both rendered as is, meaning that
1045 the name ``columna`` can only refer to the first occurrence of this name
1046 within a result set, as well as if the statement were used as a subquery:
1047
1048 .. sourcecode:: pycon+sql
1049
1050 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_NONE
1051 >>> table1 = table("table1", column("columna"), column("columnb"))
1052 >>> table2 = table("table2", column("columna"), column("columnc"))
1053 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_NONE))
1054 {printsql}SELECT table1.columna, table1.columnb, table2.columna, table2.columnc
1055 FROM table1 JOIN table2 ON true
1056
1057 Used with the :meth:`_sql.Select.set_label_style` method.
1058
1059 .. versionadded:: 1.4
1060
1061 """ # noqa: E501
1062
1063 LABEL_STYLE_TABLENAME_PLUS_COL = 1
1064 """Label style indicating all columns should be labeled as
1065 ``<tablename>_<columnname>`` when generating the columns clause of a SELECT
1066 statement, to disambiguate same-named columns referenced from different
1067 tables, aliases, or subqueries.
1068
1069 Below, all column names are given a label so that the two same-named
1070 columns ``columna`` are disambiguated as ``table1_columna`` and
1071 ``table2_columna``:
1072
1073 .. sourcecode:: pycon+sql
1074
1075 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_TABLENAME_PLUS_COL
1076 >>> table1 = table("table1", column("columna"), column("columnb"))
1077 >>> table2 = table("table2", column("columna"), column("columnc"))
1078 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL))
1079 {printsql}SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc
1080 FROM table1 JOIN table2 ON true
1081
1082 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method.
1083 Equivalent to the legacy method ``Select.apply_labels()``;
1084 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` is SQLAlchemy's legacy
1085 auto-labeling style. :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` provides a
1086 less intrusive approach to disambiguation of same-named column expressions.
1087
1088
1089 .. versionadded:: 1.4
1090
1091 """ # noqa: E501
1092
1093 LABEL_STYLE_DISAMBIGUATE_ONLY = 2
1094 """Label style indicating that columns with a name that conflicts with
1095 an existing name should be labeled with a semi-anonymizing label
1096 when generating the columns clause of a SELECT statement.
1097
1098 Below, most column names are left unaffected, except for the second
1099 occurrence of the name ``columna``, which is labeled using the
1100 label ``columna_1`` to disambiguate it from that of ``tablea.columna``:
1101
1102 .. sourcecode:: pycon+sql
1103
1104 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_DISAMBIGUATE_ONLY
1105 >>> table1 = table("table1", column("columna"), column("columnb"))
1106 >>> table2 = table("table2", column("columna"), column("columnc"))
1107 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY))
1108 {printsql}SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc
1109 FROM table1 JOIN table2 ON true
1110
1111 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method,
1112 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` is the default labeling style
1113 for all SELECT statements outside of :term:`1.x style` ORM queries.
1114
1115 .. versionadded:: 1.4
1116
1117 """ # noqa: E501
1118
1119 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY
1120 """The default label style, refers to
1121 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.
1122
1123 .. versionadded:: 1.4
1124
1125 """
1126
1127 LABEL_STYLE_LEGACY_ORM = 3
1128
1129
1130(
1131 LABEL_STYLE_NONE,
1132 LABEL_STYLE_TABLENAME_PLUS_COL,
1133 LABEL_STYLE_DISAMBIGUATE_ONLY,
1134 _,
1135) = list(SelectLabelStyle)
1136
1137LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY
1138
1139
1140class Join(roles.DMLTableRole, FromClause):
1141 """Represent a ``JOIN`` construct between two
1142 :class:`_expression.FromClause`
1143 elements.
1144
1145 The public constructor function for :class:`_expression.Join`
1146 is the module-level
1147 :func:`_expression.join()` function, as well as the
1148 :meth:`_expression.FromClause.join` method
1149 of any :class:`_expression.FromClause` (e.g. such as
1150 :class:`_schema.Table`).
1151
1152 .. seealso::
1153
1154 :func:`_expression.join`
1155
1156 :meth:`_expression.FromClause.join`
1157
1158 """
1159
1160 __visit_name__ = "join"
1161
1162 _traverse_internals: _TraverseInternalsType = [
1163 ("left", InternalTraversal.dp_clauseelement),
1164 ("right", InternalTraversal.dp_clauseelement),
1165 ("onclause", InternalTraversal.dp_clauseelement),
1166 ("isouter", InternalTraversal.dp_boolean),
1167 ("full", InternalTraversal.dp_boolean),
1168 ]
1169
1170 _is_join = True
1171
1172 left: FromClause
1173 right: FromClause
1174 onclause: Optional[ColumnElement[bool]]
1175 isouter: bool
1176 full: bool
1177
1178 def __init__(
1179 self,
1180 left: _FromClauseArgument,
1181 right: _FromClauseArgument,
1182 onclause: Optional[_OnClauseArgument] = None,
1183 isouter: bool = False,
1184 full: bool = False,
1185 ):
1186 """Construct a new :class:`_expression.Join`.
1187
1188 The usual entrypoint here is the :func:`_expression.join`
1189 function or the :meth:`_expression.FromClause.join` method of any
1190 :class:`_expression.FromClause` object.
1191
1192 """
1193
1194 # when deannotate was removed here, callcounts went up for ORM
1195 # compilation of eager joins, since there were more comparisons of
1196 # annotated objects. test_orm.py -> test_fetch_results
1197 # was therefore changed to show a more real-world use case, where the
1198 # compilation is cached; there's no change in post-cache callcounts.
1199 # callcounts for a single compilation in that particular test
1200 # that includes about eight joins about 1100 extra fn calls, from