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