Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py: 46%

1084 {printsql}SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc

1085 FROM table1 JOIN table2 ON true

1086

1087 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method.

1088 Equivalent to the legacy method ``Select.apply_labels()``;

1089 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` is SQLAlchemy's legacy

1090 auto-labeling style. :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` provides a

1091 less intrusive approach to disambiguation of same-named column expressions.

1092

1093

1094 .. versionadded:: 1.4

1095

1096 """ # noqa: E501

1097

1098 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

1099 """Label style indicating that columns with a name that conflicts with

1100 an existing name should be labeled with a semi-anonymizing label

1101 when generating the columns clause of a SELECT statement.

1102

1103 Below, most column names are left unaffected, except for the second

1104 occurrence of the name ``columna``, which is labeled using the

1105 label ``columna_1`` to disambiguate it from that of ``tablea.columna``:

1106

1107 .. sourcecode:: pycon+sql

1108

1109 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_DISAMBIGUATE_ONLY

1110 >>> table1 = table("table1", column("columna"), column("columnb"))

1111 >>> table2 = table("table2", column("columna"), column("columnc"))

1112 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY))

1113 {printsql}SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc

1114 FROM table1 JOIN table2 ON true

1115

1116 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method,

1117 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` is the default labeling style

1118 for all SELECT statements outside of :term:`1.x style` ORM queries.

1119

1120 .. versionadded:: 1.4

1121

1122 """ # noqa: E501

1123

1124 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1125 """The default label style, refers to

1126 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1127

1128 .. versionadded:: 1.4

1129

1130 """

1131

1132 LABEL_STYLE_LEGACY_ORM = 3

1133

1134

1135(

1136 LABEL_STYLE_NONE,

1137 LABEL_STYLE_TABLENAME_PLUS_COL,

1138 LABEL_STYLE_DISAMBIGUATE_ONLY,

1139 _,

1140) = list(SelectLabelStyle)

1141

1142LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1143

1144

1145class Join(roles.DMLTableRole, FromClause):

1146 """Represent a ``JOIN`` construct between two

1147 :class:`_expression.FromClause`

1148 elements.

1149

1150 The public constructor function for :class:`_expression.Join`

1151 is the module-level

1152 :func:`_expression.join()` function, as well as the

1153 :meth:`_expression.FromClause.join` method

1154 of any :class:`_expression.FromClause` (e.g. such as

1155 :class:`_schema.Table`).

1156

1157 .. seealso::

1158

1159 :func:`_expression.join`

1160

1161 :meth:`_expression.FromClause.join`

1162

1163 """

1164

1165 __visit_name__ = "join"

1166

1167 _traverse_internals: _TraverseInternalsType = [

1168 ("left", InternalTraversal.dp_clauseelement),

1169 ("right", InternalTraversal.dp_clauseelement),

1170 ("onclause", InternalTraversal.dp_clauseelement),

1171 ("isouter", InternalTraversal.dp_boolean),

1172 ("full", InternalTraversal.dp_boolean),

1173 ]

1174

1175 _is_join = True

1176

1177 left: FromClause

1178 right: FromClause

1179 onclause: Optional[ColumnElement[bool]]

1180 isouter: bool

1181 full: bool

1182

1183 def __init__(

1184 self,

1185 left: _FromClauseArgument,

1186 right: _FromClauseArgument,

1187 onclause: Optional[_OnClauseArgument] = None,

1188 isouter: bool = False,

1189 full: bool = False,

1190 ):

1191 """Construct a new :class:`_expression.Join`.

1192

1193 The usual entrypoint here is the :func:`_expression.join`

1194 function or the :meth:`_expression.FromClause.join` method of any

1195 :class:`_expression.FromClause` object.

1196

1197 """

1198

1199 # when deannotate was removed here, callcounts went up for ORM

1200 # compilation of eager joins, since there were more comparisons of

1201 # annotated objects. test_orm.py -> test_fetch_results

1202 # was therefore changed to show a more real-world use case, where the

1203 # compilation is cached; there's no change in post-cache callcounts.

1204 # callcounts for a single compilation in that particular test

1205 # that includes about eight joins about 1100 extra fn calls, from

1206 # 29200 -> 30373

1207

1208 self.left = coercions.expect(

1209 roles.FromClauseRole,

1210 left,

1211 )

1212 self.right = coercions.expect(

1213 roles.FromClauseRole,

1214 right,

1215 ).self_group()

1216

1217 if onclause is None:

1218 self.onclause = self._match_primaries(self.left, self.right)

1219 else:

1220 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1221 # not merged yet

1222 self.onclause = coercions.expect(

1223 roles.OnClauseRole, onclause

1224 ).self_group(against=operators._asbool)

1225

1226 self.isouter = isouter

1227 self.full = full

1228

1229 @util.ro_non_memoized_property

1230 def description(self) -> str:

1231 return "Join object on %s(%d) and %s(%d)" % (

1232 self.left.description,

1233 id(self.left),

1234 self.right.description,

1235 id(self.right),

1236 )

1237

1238 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

1239 return (

1240 # use hash() to ensure direct comparison to annotated works

1241 # as well

1242 hash(fromclause) == hash(self)

1243 or self.left.is_derived_from(fromclause)

1244 or self.right.is_derived_from(fromclause)

1245 )

1246

1247 def self_group(

1248 self, against: Optional[OperatorType] = None

1249 ) -> FromGrouping:

1250 return FromGrouping(self)

1251

1252 @util.preload_module("sqlalchemy.sql.util")

1253 def _populate_column_collection(self) -> None:

1254 sqlutil = util.preloaded.sql_util

1255 columns: List[KeyedColumnElement[Any]] = [c for c in self.left.c] + [

1256 c for c in self.right.c

1257 ]

1258

1259 self.primary_key.extend( # type: ignore

1260 sqlutil.reduce_columns(

1261 (c for c in columns if c.primary_key), self.onclause

1262 )

1263 )

1264 self._columns._populate_separate_keys(

1265 (col._tq_key_label, col) for col in columns

1266 )

1267 self.foreign_keys.update( # type: ignore

1268 itertools.chain(*[col.foreign_keys for col in columns])

1269 )

1270

1271 def _copy_internals(

1272 self, clone: _CloneCallableType = _clone, **kw: Any

1273 ) -> None:

1274 # see Select._copy_internals() for similar concept

1275

1276 # here we pre-clone "left" and "right" so that we can

1277 # determine the new FROM clauses

1278 all_the_froms = set(

1279 itertools.chain(

1280 _from_objects(self.left),

1281 _from_objects(self.right),

1282 )

1283 )

1284

1285 # run the clone on those. these will be placed in the

1286 # cache used by the clone function

1287 new_froms = {f: clone(f, **kw) for f in all_the_froms}

1288

1289 # set up a special replace function that will replace for

1290 # ColumnClause with parent table referring to those

1291 # replaced FromClause objects

1292 def replace(

1293 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

1294 **kw: Any,

1295 ) -> Optional[KeyedColumnElement[ColumnElement[Any]]]:

1296 if isinstance(obj, ColumnClause) and obj.table in new_froms:

1297 newelem = new_froms[obj.table].corresponding_column(obj)

1298 return newelem

1299 return None

1300

1301 kw["replace"] = replace

1302

1303 # run normal _copy_internals. the clones for

1304 # left and right will come from the clone function's

1305 # cache

1306 super()._copy_internals(clone=clone, **kw)

1307

1308 self._reset_memoizations()

1309

1310 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

1311 super()._refresh_for_new_column(column)

1312 self.left._refresh_for_new_column(column)

1313 self.right._refresh_for_new_column(column)

1314

1315 def _match_primaries(

1316 self,

1317 left: FromClause,

1318 right: FromClause,

1319 ) -> ColumnElement[bool]:

1320 if isinstance(left, Join):

1321 left_right = left.right

1322 else:

1323 left_right = None

1324 return self._join_condition(left, right, a_subset=left_right)

1325

1326 @classmethod

1327 def _join_condition(

1328 cls,

1329 a: FromClause,

1330 b: FromClause,

1331 *,

1332 a_subset: Optional[FromClause] = None,

1333 consider_as_foreign_keys: Optional[

1334 AbstractSet[ColumnClause[Any]]

1335 ] = None,

1336 ) -> ColumnElement[bool]:

1337 """Create a join condition between two tables or selectables.

1338

1339 See sqlalchemy.sql.util.join_condition() for full docs.

1340

1341 """

1342 constraints = cls._joincond_scan_left_right(

1343 a, a_subset, b, consider_as_foreign_keys

1344 )

1345

1346 if len(constraints) > 1:

1347 cls._joincond_trim_constraints(

1348 a, b, constraints, consider_as_foreign_keys

1349 )

1350

1351 if len(constraints) == 0:

1352 if isinstance(b, FromGrouping):

1353 hint = (

1354 " Perhaps you meant to convert the right side to a "

1355 "subquery using alias()?"

1356 )

1357 else:

1358 hint = ""

1359 raise exc.NoForeignKeysError(

1360 "Can't find any foreign key relationships "

1361 "between '%s' and '%s'.%s"

1362 % (a.description, b.description, hint)

1363 )

1364

1365 crit = [(x == y) for x, y in list(constraints.values())[0]]

1366 if len(crit) == 1:

1367 return crit[0]

1368 else:

1369 return and_(*crit)

1370

1371 @classmethod

1372 def _can_join(

1373 cls,

1374 left: FromClause,

1375 right: FromClause,

1376 *,

1377 consider_as_foreign_keys: Optional[

1378 AbstractSet[ColumnClause[Any]]

1379 ] = None,

1380 ) -> bool:

1381 if isinstance(left, Join):

1382 left_right = left.right

1383 else:

1384 left_right = None

1385

1386 constraints = cls._joincond_scan_left_right(

1387 a=left,

1388 b=right,

1389 a_subset=left_right,

1390 consider_as_foreign_keys=consider_as_foreign_keys,

1391 )

1392

1393 return bool(constraints)

1394

1395 @classmethod

1396 @util.preload_module("sqlalchemy.sql.util")

1397 def _joincond_scan_left_right(

1398 cls,

1399 a: FromClause,

1400 a_subset: Optional[FromClause],

1401 b: FromClause,

1402 consider_as_foreign_keys: Optional[AbstractSet[ColumnClause[Any]]],

1403 ) -> collections.defaultdict[

1404 Optional[ForeignKeyConstraint],

1405 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1406 ]:

1407 sql_util = util.preloaded.sql_util

1408

1409 a = coercions.expect(roles.FromClauseRole, a)

1410 b = coercions.expect(roles.FromClauseRole, b)

1411

1412 constraints: collections.defaultdict[

1413 Optional[ForeignKeyConstraint],

1414 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1415 ] = collections.defaultdict(list)

1416

1417 for left in (a_subset, a):

1418 if left is None:

1419 continue

1420 for fk in sorted(

1421 b.foreign_keys,

1422 key=lambda fk: fk.parent._creation_order,

1423 ):

1424 if (

1425 consider_as_foreign_keys is not None

1426 and fk.parent not in consider_as_foreign_keys

1427 ):

1428 continue

1429 try:

1430 col = fk.get_referent(left)

1431 except exc.NoReferenceError as nrte:

1432 table_names = {t.name for t in sql_util.find_tables(left)}

1433 if nrte.table_name in table_names:

1434 raise

1435 else:

1436 continue

1437

1438 if col is not None:

1439 constraints[fk.constraint].append((col, fk.parent))

1440 if left is not b:

1441 for fk in sorted(

1442 left.foreign_keys,

1443 key=lambda fk: fk.parent._creation_order,

1444 ):

1445 if (

1446 consider_as_foreign_keys is not None

1447 and fk.parent not in consider_as_foreign_keys

1448 ):

1449 continue

1450 try:

1451 col = fk.get_referent(b)

1452 except exc.NoReferenceError as nrte:

1453 table_names = {t.name for t in sql_util.find_tables(b)}

1454 if nrte.table_name in table_names:

1455 raise

1456 else:

1457 continue

1458

1459 if col is not None:

1460 constraints[fk.constraint].append((col, fk.parent))

1461 if constraints:

1462 break

1463 return constraints

1464

1465 @classmethod

1466 def _joincond_trim_constraints(

1467 cls,

1468 a: FromClause,

1469 b: FromClause,

1470 constraints: Dict[Any, Any],

1471 consider_as_foreign_keys: Optional[Any],

1472 ) -> None:

1473 # more than one constraint matched. narrow down the list

1474 # to include just those FKCs that match exactly to

1475 # "consider_as_foreign_keys".

1476 if consider_as_foreign_keys:

1477 for const in list(constraints):

1478 if {f.parent for f in const.elements} != set(

1479 consider_as_foreign_keys

1480 ):

1481 del constraints[const]

1482

1483 # if still multiple constraints, but

1484 # they all refer to the exact same end result, use it.

1485 if len(constraints) > 1:

1486 dedupe = {tuple(crit) for crit in constraints.values()}

1487 if len(dedupe) == 1:

1488 key = list(constraints)[0]

1489 constraints = {key: constraints[key]}

1490

1491 if len(constraints) != 1:

1492 raise exc.AmbiguousForeignKeysError(

1493 "Can't determine join between '%s' and '%s'; "

1494 "tables have more than one foreign key "

1495 "constraint relationship between them. "

1496 "Please specify the 'onclause' of this "

1497 "join explicitly." % (a.description, b.description)

1498 )

1499

1500 def select(self) -> Select[Unpack[TupleAny]]:

1501 r"""Create a :class:`_expression.Select` from this

1502 :class:`_expression.Join`.

1503

1504 E.g.::

1505

1506 stmt = table_a.join(table_b, table_a.c.id == table_b.c.a_id)

1507

1508 stmt = stmt.select()

1509

1510 The above will produce a SQL string resembling::

1511

1512 SELECT table_a.id, table_a.col, table_b.id, table_b.a_id

1513 FROM table_a JOIN table_b ON table_a.id = table_b.a_id

1514

1515 """

1516 return Select(self.left, self.right).select_from(self)

1517

1518 @util.preload_module("sqlalchemy.sql.util")

1519 def _anonymous_fromclause(

1520 self, name: Optional[str] = None, flat: bool = False

1521 ) -> TODO_Any:

1522 sqlutil = util.preloaded.sql_util

1523 if flat:

1524 if isinstance(self.left, (FromGrouping, Join)):

1525 left_name = name # will recurse

1526 else:

1527 if name and isinstance(self.left, NamedFromClause):

1528 left_name = f"{name}_{self.left.name}"

1529 else:

1530 left_name = name

1531 if isinstance(self.right, (FromGrouping, Join)):

1532 right_name = name # will recurse

1533 else:

1534 if name and isinstance(self.right, NamedFromClause):

1535 right_name = f"{name}_{self.right.name}"

1536 else:

1537 right_name = name

1538 left_a, right_a = (

1539 self.left._anonymous_fromclause(name=left_name, flat=flat),

1540 self.right._anonymous_fromclause(name=right_name, flat=flat),

1541 )

1542 adapter = sqlutil.ClauseAdapter(left_a).chain(

1543 sqlutil.ClauseAdapter(right_a)

1544 )

1545

1546 return left_a.join(

1547 right_a,

1548 adapter.traverse(self.onclause),

1549 isouter=self.isouter,

1550 full=self.full,

1551 )

1552 else:

1553 return (

1554 self.select()

1555 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1556 .correlate(None)

1557 .alias(name)

1558 )

1559

1560 @util.ro_non_memoized_property

1561 def _hide_froms(self) -> Iterable[FromClause]:

1562 return itertools.chain(

1563 *[_from_objects(x.left, x.right) for x in self._cloned_set]

1564 )

1565

1566 @util.ro_non_memoized_property

1567 def _from_objects(self) -> List[FromClause]:

1568 self_list: List[FromClause] = [self]

1569 return self_list + self.left._from_objects + self.right._from_objects

1570

1571

1572class NoInit:

1573 def __init__(self, *arg: Any, **kw: Any):

1574 raise NotImplementedError(

1575 "The %s class is not intended to be constructed "

1576 "directly. Please use the %s() standalone "

1577 "function or the %s() method available from appropriate "

1578 "selectable objects."

1579 % (

1580 self.__class__.__name__,

1581 self.__class__.__name__.lower(),

1582 self.__class__.__name__.lower(),

1583 )

1584 )

1585

1586

1587class LateralFromClause(NamedFromClause):

1588 """mark a FROM clause as being able to render directly as LATERAL"""

1589

1590

1591# FromClause ->

1592# AliasedReturnsRows

1593# -> Alias only for FromClause

1594# -> Subquery only for SelectBase

1595# -> CTE only for HasCTE -> SelectBase, DML

1596# -> Lateral -> FromClause, but we accept SelectBase

1597# w/ non-deprecated coercion

1598# -> TableSample -> only for FromClause

1599

1600

1601class AliasedReturnsRows(NoInit, NamedFromClause):

1602 """Base class of aliases against tables, subqueries, and other

1603 selectables."""

1604

1605 _is_from_container = True

1606

1607 _supports_derived_columns = False

1608

1609 element: ReturnsRows

1610

1611 _traverse_internals: _TraverseInternalsType = [

1612 ("element", InternalTraversal.dp_clauseelement),

1613 ("name", InternalTraversal.dp_anon_name),

1614 ]

1615

1616 @classmethod

1617 def _construct(

1618 cls,

1619 selectable: Any,

1620 *,

1621 name: Optional[str] = None,

1622 **kw: Any,

1623 ) -> Self:

1624 obj = cls.__new__(cls)

1625 obj._init(selectable, name=name, **kw)

1626 return obj

1627

1628 def _init(self, selectable: Any, *, name: Optional[str] = None) -> None:

1629 self.element = coercions.expect(

1630 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1631 )

1632 self.element = selectable

1633 self._orig_name = name

1634 if name is None:

1635 if (

1636 isinstance(selectable, FromClause)

1637 and selectable.named_with_column

1638 ):

1639 name = getattr(selectable, "name", None)

1640 if isinstance(name, _anonymous_label):

1641 name = None

1642 name = _anonymous_label.safe_construct(id(self), name or "anon")

1643 self.name = name

1644

1645 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

1646 super()._refresh_for_new_column(column)

1647 self.element._refresh_for_new_column(column)

1648

1649 def _populate_column_collection(self) -> None:

1650 self.element._generate_fromclause_column_proxies(self)

1651

1652 @util.ro_non_memoized_property

1653 def description(self) -> str:

1654 name = self.name

1655 if isinstance(name, _anonymous_label):

1656 name = "anon_1"

1657

1658 return name

1659

1660 @util.ro_non_memoized_property

1661 def implicit_returning(self) -> bool:

1662 return self.element.implicit_returning # type: ignore

1663

1664 @property

1665 def original(self) -> ReturnsRows:

1666 """Legacy for dialects that are referring to Alias.original."""

1667 return self.element

1668

1669 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

1670 if fromclause in self._cloned_set:

1671 return True

1672 return self.element.is_derived_from(fromclause)

1673

1674 def _copy_internals(

1675 self, clone: _CloneCallableType = _clone, **kw: Any

1676 ) -> None:

1677 existing_element = self.element

1678

1679 super()._copy_internals(clone=clone, **kw)

1680

1681 # the element clone is usually against a Table that returns the

1682 # same object. don't reset exported .c. collections and other

1683 # memoized details if it was not changed. this saves a lot on

1684 # performance.

1685 if existing_element is not self.element:

1686 self._reset_column_collection()

1687

1688 @property

1689 def _from_objects(self) -> List[FromClause]:

1690 return [self]

1691

1692

1693class FromClauseAlias(AliasedReturnsRows):

1694 element: FromClause

1695

1696

1697class Alias(roles.DMLTableRole, FromClauseAlias):

1698 """Represents an table or selectable alias (AS).

1699

1700 Represents an alias, as typically applied to any table or

1701 sub-select within a SQL statement using the ``AS`` keyword (or

1702 without the keyword on certain databases such as Oracle).

1703

1704 This object is constructed from the :func:`_expression.alias` module

1705 level function as well as the :meth:`_expression.FromClause.alias`

1706 method available

1707 on all :class:`_expression.FromClause` subclasses.

1708

1709 .. seealso::

1710

1711 :meth:`_expression.FromClause.alias`

1712

1713 """

1714

1715 __visit_name__ = "alias"

1716

1717 inherit_cache = True

1718

1719 element: FromClause

1720

1721 @classmethod

1722 def _factory(

1723 cls,

1724 selectable: FromClause,

1725 name: Optional[str] = None,

1726 flat: bool = False,

1727 ) -> NamedFromClause:

1728 return coercions.expect(

1729 roles.FromClauseRole, selectable, allow_select=True

1730 ).alias(name=name, flat=flat)

1731

1732

1733class TableValuedAlias(LateralFromClause, Alias):

1734 """An alias against a "table valued" SQL function.

1735

1736 This construct provides for a SQL function that returns columns

1737 to be used in the FROM clause of a SELECT statement. The

1738 object is generated using the :meth:`_functions.FunctionElement.table_valued`

1739 method, e.g.:

1740

1741 .. sourcecode:: pycon+sql

1742

1743 >>> from sqlalchemy import select, func

1744 >>> fn = func.json_array_elements_text('["one", "two", "three"]').table_valued("value")

1745 >>> print(select(fn.c.value))

1746 {printsql}SELECT anon_1.value

1747 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1748

1749 .. versionadded:: 1.4.0b2

1750

1751 .. seealso::

1752

1753 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`

1754

1755 """ # noqa: E501

1756

1757 __visit_name__ = "table_valued_alias"

1758

1759 _supports_derived_columns = True

1760 _render_derived = False

1761 _render_derived_w_types = False

1762 joins_implicitly = False

1763

1764 _traverse_internals: _TraverseInternalsType = [

1765 ("element", InternalTraversal.dp_clauseelement),

1766 ("name", InternalTraversal.dp_anon_name),

1767 ("_tableval_type", InternalTraversal.dp_type),

1768 ("_render_derived", InternalTraversal.dp_boolean),

1769 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1770 ]

1771

1772 def _init(

1773 self,

1774 selectable: Any,

1775 *,

1776 name: Optional[str] = None,

1777 table_value_type: Optional[TableValueType] = None,

1778 joins_implicitly: bool = False,

1779 ) -> None:

1780 super()._init(selectable, name=name)

1781

1782 self.joins_implicitly = joins_implicitly

1783 self._tableval_type = (

1784 type_api.TABLEVALUE

1785 if table_value_type is None

1786 else table_value_type

1787 )

1788

1789 @HasMemoized.memoized_attribute

1790 def column(self) -> TableValuedColumn[Any]:

1791 """Return a column expression representing this

1792 :class:`_sql.TableValuedAlias`.

1793

1794 This accessor is used to implement the

1795 :meth:`_functions.FunctionElement.column_valued` method. See that

1796 method for further details.

1797

1798 E.g.:

1799

1800 .. sourcecode:: pycon+sql

1801

1802 >>> print(select(func.some_func().table_valued("value").column))

1803 {printsql}SELECT anon_1 FROM some_func() AS anon_1

1804

1805 .. seealso::

1806

1807 :meth:`_functions.FunctionElement.column_valued`

1808

1809 """

1810

1811 return TableValuedColumn(self, self._tableval_type)

1812

1813 def alias(

1814 self, name: Optional[str] = None, flat: bool = False

1815 ) -> TableValuedAlias:

1816 """Return a new alias of this :class:`_sql.TableValuedAlias`.

1817

1818 This creates a distinct FROM object that will be distinguished

1819 from the original one when used in a SQL statement.

1820

1821 """

1822

1823 tva: TableValuedAlias = TableValuedAlias._construct(

1824 self,

1825 name=name,

1826 table_value_type=self._tableval_type,

1827 joins_implicitly=self.joins_implicitly,

1828 )

1829

1830 if self._render_derived:

1831 tva._render_derived = True

1832 tva._render_derived_w_types = self._render_derived_w_types

1833

1834 return tva

1835

1836 def lateral(self, name: Optional[str] = None) -> LateralFromClause:

1837 """Return a new :class:`_sql.TableValuedAlias` with the lateral flag

1838 set, so that it renders as LATERAL.

1839

1840 .. seealso::

1841

1842 :func:`_expression.lateral`

1843

1844 """

1845 tva = self.alias(name=name)

1846 tva._is_lateral = True

1847 return tva

1848

1849 def render_derived(

1850 self,

1851 name: Optional[str] = None,

1852 with_types: bool = False,

1853 ) -> TableValuedAlias:

1854 """Apply "render derived" to this :class:`_sql.TableValuedAlias`.

1855

1856 This has the effect of the individual column names listed out

1857 after the alias name in the "AS" sequence, e.g.:

1858

1859 .. sourcecode:: pycon+sql

1860

1861 >>> print(

1862 ... select(

1863 ... func.unnest(array(["one", "two", "three"])).

1864 table_valued("x", with_ordinality="o").render_derived()

1865 ... )

1866 ... )

1867 {printsql}SELECT anon_1.x, anon_1.o

1868 FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1(x, o)

1869

1870 The ``with_types`` keyword will render column types inline within

1871 the alias expression (this syntax currently applies to the

1872 PostgreSQL database):

1873

1874 .. sourcecode:: pycon+sql

1875

1876 >>> print(

1877 ... select(

1878 ... func.json_to_recordset(

1879 ... '[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]'

1880 ... )

1881 ... .table_valued(column("a", Integer), column("b", String))

1882 ... .render_derived(with_types=True)

1883 ... )

1884 ... )

1885 {printsql}SELECT anon_1.a, anon_1.b FROM json_to_recordset(:json_to_recordset_1)

1886 AS anon_1(a INTEGER, b VARCHAR)

1887

1888 :param name: optional string name that will be applied to the alias

1889 generated. If left as None, a unique anonymizing name will be used.

1890

1891 :param with_types: if True, the derived columns will include the

1892 datatype specification with each column. This is a special syntax

1893 currently known to be required by PostgreSQL for some SQL functions.

1894

1895 """ # noqa: E501

1896

1897 # note: don't use the @_generative system here, keep a reference

1898 # to the original object. otherwise you can have re-use of the

1899 # python id() of the original which can cause name conflicts if

1900 # a new anon-name grabs the same identifier as the local anon-name

1901 # (just saw it happen on CI)

1902

1903 # construct against original to prevent memory growth

1904 # for repeated generations

1905 new_alias: TableValuedAlias = TableValuedAlias._construct(

1906 self.element,

1907 name=name,

1908 table_value_type=self._tableval_type,

1909 joins_implicitly=self.joins_implicitly,

1910 )

1911 new_alias._render_derived = True

1912 new_alias._render_derived_w_types = with_types

1913 return new_alias

1914

1915

1916class Lateral(FromClauseAlias, LateralFromClause):

1917 """Represent a LATERAL subquery.

1918

1919 This object is constructed from the :func:`_expression.lateral` module

1920 level function as well as the :meth:`_expression.FromClause.lateral`

1921 method available

1922 on all :class:`_expression.FromClause` subclasses.

1923

1924 While LATERAL is part of the SQL standard, currently only more recent

1925 PostgreSQL versions provide support for this keyword.

1926

1927 .. seealso::

1928

1929 :ref:`tutorial_lateral_correlation` - overview of usage.

1930

1931 """

1932

1933 __visit_name__ = "lateral"

1934 _is_lateral = True

1935

1936 inherit_cache = True

1937

1938 @classmethod

1939 def _factory(

1940 cls,

1941 selectable: Union[SelectBase, _FromClauseArgument],

1942 name: Optional[str] = None,

1943 ) -> LateralFromClause:

1944 return coercions.expect(

1945 roles.FromClauseRole, selectable, explicit_subquery=True

1946 ).lateral(name=name)

1947

1948

1949class TableSample(FromClauseAlias):

1950 """Represent a TABLESAMPLE clause.

1951

1952 This object is constructed from the :func:`_expression.tablesample` module

1953 level function as well as the :meth:`_expression.FromClause.tablesample`

1954 method

1955 available on all :class:`_expression.FromClause` subclasses.

1956

1957 .. seealso::

1958

1959 :func:`_expression.tablesample`

1960

1961 """

1962

1963 __visit_name__ = "tablesample"

1964

1965 _traverse_internals: _TraverseInternalsType = (

1966 AliasedReturnsRows._traverse_internals

1967 + [

1968 ("sampling", InternalTraversal.dp_clauseelement),

1969 ("seed", InternalTraversal.dp_clauseelement),

1970 ]

1971 )

1972

1973 @classmethod

1974 def _factory(

1975 cls,

1976 selectable: _FromClauseArgument,

1977 sampling: Union[float, Function[Any]],

1978 name: Optional[str] = None,

1979 seed: Optional[roles.ExpressionElementRole[Any]] = None,

1980 ) -> TableSample:

1981 return coercions.expect(roles.FromClauseRole, selectable).tablesample(

1982 sampling, name=name, seed=seed

1983 )

1984

1985 @util.preload_module("sqlalchemy.sql.functions")

1986 def _init( # type: ignore[override]

1987 self,

1988 selectable: Any,

1989 *,

1990 name: Optional[str] = None,

1991 sampling: Union[float, Function[Any]],

1992 seed: Optional[roles.ExpressionElementRole[Any]] = None,

1993 ) -> None:

1994 assert sampling is not None

1995 functions = util.preloaded.sql_functions

1996 if not isinstance(sampling, functions.Function):

1997 sampling = functions.func.system(sampling)

1998

1999 self.sampling: Function[Any] = sampling

2000 self.seed = seed

2001 super()._init(selectable, name=name)

2002

2003 def _get_method(self) -> Function[Any]:

2004 return self.sampling

2005

2006

2007class CTE(

2008 roles.DMLTableRole,

2009 roles.IsCTERole,

2010 Generative,

2011 HasPrefixes,

2012 HasSuffixes,

2013 AliasedReturnsRows,

2014):

2015 """Represent a Common Table Expression.

2016

2017 The :class:`_expression.CTE` object is obtained using the

2018 :meth:`_sql.SelectBase.cte` method from any SELECT statement. A less often

2019 available syntax also allows use of the :meth:`_sql.HasCTE.cte` method

2020 present on :term:`DML` constructs such as :class:`_sql.Insert`,

2021 :class:`_sql.Update` and

2022 :class:`_sql.Delete`. See the :meth:`_sql.HasCTE.cte` method for

2023 usage details on CTEs.

2024

2025 .. seealso::

2026

2027 :ref:`tutorial_subqueries_ctes` - in the 2.0 tutorial

2028

2029 :meth:`_sql.HasCTE.cte` - examples of calling styles

2030

2031 """

2032

2033 __visit_name__ = "cte"

2034

2035 _traverse_internals: _TraverseInternalsType = (

2036 AliasedReturnsRows._traverse_internals

2037 + [

2038 ("_cte_alias", InternalTraversal.dp_clauseelement),

2039 ("_restates", InternalTraversal.dp_clauseelement),

2040 ("recursive", InternalTraversal.dp_boolean),

2041 ("nesting", InternalTraversal.dp_boolean),

2042 ]

2043 + HasPrefixes._has_prefixes_traverse_internals

2044 + HasSuffixes._has_suffixes_traverse_internals

2045 )

2046

2047 element: HasCTE

2048

2049 @classmethod

2050 def _factory(

2051 cls,

2052 selectable: HasCTE,

2053 name: Optional[str] = None,

2054 recursive: bool = False,

2055 ) -> CTE:

2056 r"""Return a new :class:`_expression.CTE`,

2057 or Common Table Expression instance.

2058

2059 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.

2060

2061 """

2062 return coercions.expect(roles.HasCTERole, selectable).cte(

2063 name=name, recursive=recursive

2064 )

2065

2066 def _init(

2067 self,

2068 selectable: Select[Unpack[TupleAny]],

2069 *,

2070 name: Optional[str] = None,

2071 recursive: bool = False,

2072 nesting: bool = False,

2073 _cte_alias: Optional[CTE] = None,

2074 _restates: Optional[CTE] = None,

2075 _prefixes: Optional[Tuple[()]] = None,

2076 _suffixes: Optional[Tuple[()]] = None,

2077 ) -> None:

2078 self.recursive = recursive

2079 self.nesting = nesting

2080 self._cte_alias = _cte_alias

2081 # Keep recursivity reference with union/union_all

2082 self._restates = _restates

2083 if _prefixes:

2084 self._prefixes = _prefixes

2085 if _suffixes:

2086 self._suffixes = _suffixes

2087 super()._init(selectable, name=name)

2088

2089 def _populate_column_collection(self) -> None:

2090 if self._cte_alias is not None:

2091 self._cte_alias._generate_fromclause_column_proxies(self)

2092 else:

2093 self.element._generate_fromclause_column_proxies(self)

2094

2095 def alias(self, name: Optional[str] = None, flat: bool = False) -> CTE:

2096 """Return an :class:`_expression.Alias` of this

2097 :class:`_expression.CTE`.

2098

2099 This method is a CTE-specific specialization of the

2100 :meth:`_expression.FromClause.alias` method.

2101

2102 .. seealso::

2103

2104 :ref:`tutorial_using_aliases`

2105

2106 :func:`_expression.alias`

2107

2108 """

2109 return CTE._construct(

2110 self.element,

2111 name=name,

2112 recursive=self.recursive,

2113 nesting=self.nesting,

2114 _cte_alias=self,

2115 _prefixes=self._prefixes,

2116 _suffixes=self._suffixes,

2117 )

2118

2119 def union(self, *other: _SelectStatementForCompoundArgument) -> CTE:

2120 r"""Return a new :class:`_expression.CTE` with a SQL ``UNION``

2121 of the original CTE against the given selectables provided

2122 as positional arguments.

2123

2124 :param \*other: one or more elements with which to create a

2125 UNION.

2126

2127 .. versionchanged:: 1.4.28 multiple elements are now accepted.

2128

2129 .. seealso::

2130

2131 :meth:`_sql.HasCTE.cte` - examples of calling styles

2132

2133 """

2134 assert is_select_statement(

2135 self.element

2136 ), f"CTE element f{self.element} does not support union()"

2137

2138 return CTE._construct(

2139 self.element.union(*other),

2140 name=self.name,

2141 recursive=self.recursive,

2142 nesting=self.nesting,

2143 _restates=self,

2144 _prefixes=self._prefixes,

2145 _suffixes=self._suffixes,

2146 )

2147

2148 def union_all(self, *other: _SelectStatementForCompoundArgument) -> CTE:

2149 r"""Return a new :class:`_expression.CTE` with a SQL ``UNION ALL``

2150 of the original CTE against the given selectables provided

2151 as positional arguments.

2152

2153 :param \*other: one or more elements with which to create a

2154 UNION.

2155

2156 .. versionchanged:: 1.4.28 multiple elements are now accepted.

2157

2158 .. seealso::

2159

2160 :meth:`_sql.HasCTE.cte` - examples of calling styles

2161

2162 """

2163

2164 assert is_select_statement(

2165 self.element

2166 ), f"CTE element f{self.element} does not support union_all()"

2167

2168 return CTE._construct(

2169 self.element.union_all(*other),

2170 name=self.name,

2171 recursive=self.recursive,

2172 nesting=self.nesting,

2173 _restates=self,

2174 _prefixes=self._prefixes,

2175 _suffixes=self._suffixes,

2176 )

2177

2178 def _get_reference_cte(self) -> CTE:

2179 """

2180 A recursive CTE is updated to attach the recursive part.

2181 Updated CTEs should still refer to the original CTE.

2182 This function returns this reference identifier.

2183 """

2184 return self._restates if self._restates is not None else self

2185

2186

2187class _CTEOpts(NamedTuple):

2188 nesting: bool

2189

2190

2191class _ColumnsPlusNames(NamedTuple):

2192 required_label_name: Optional[str]

2193 """

2194 string label name, if non-None, must be rendered as a

2195 label, i.e. "AS <name>"

2196 """

2197

2198 proxy_key: Optional[str]

2199 """

2200 proxy_key that is to be part of the result map for this

2201 col. this is also the key in a fromclause.c or

2202 select.selected_columns collection

2203 """

2204

2205 fallback_label_name: Optional[str]

2206 """

2207 name that can be used to render an "AS <name>" when

2208 we have to render a label even though

2209 required_label_name was not given

2210 """

2211

2212 column: Union[ColumnElement[Any], TextClause]

2213 """

2214 the ColumnElement itself

2215 """

2216

2217 repeated: bool

2218 """

2219 True if this is a duplicate of a previous column

2220 in the list of columns

2221 """

2222

2223

2224class SelectsRows(ReturnsRows):

2225 """Sub-base of ReturnsRows for elements that deliver rows

2226 directly, namely SELECT and INSERT/UPDATE/DELETE..RETURNING"""

2227

2228 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2229

2230 def _generate_columns_plus_names(

2231 self,

2232 anon_for_dupe_key: bool,

2233 cols: Optional[_SelectIterable] = None,

2234 ) -> List[_ColumnsPlusNames]:

2235 """Generate column names as rendered in a SELECT statement by

2236 the compiler.

2237

2238 This is distinct from the _column_naming_convention generator that's

2239 intended for population of .c collections and similar, which has

2240 different rules. the collection returned here calls upon the

2241 _column_naming_convention as well.

2242

2243 """

2244

2245 if cols is None:

2246 cols = self._all_selected_columns

2247

2248 key_naming_convention = SelectState._column_naming_convention(

2249 self._label_style

2250 )

2251

2252 names = {}

2253

2254 result: List[_ColumnsPlusNames] = []

2255 result_append = result.append

2256

2257 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2258 label_style_none = self._label_style is LABEL_STYLE_NONE

2259

2260 # a counter used for "dedupe" labels, which have double underscores

2261 # in them and are never referred by name; they only act

2262 # as positional placeholders. they need only be unique within

2263 # the single columns clause they're rendered within (required by

2264 # some dbs such as mysql). So their anon identity is tracked against

2265 # a fixed counter rather than hash() identity.

2266 dedupe_hash = 1

2267

2268 for c in cols:

2269 repeated = False

2270

2271 if not c._render_label_in_columns_clause:

2272 effective_name = required_label_name = fallback_label_name = (

2273 None

2274 )

2275 elif label_style_none:

2276 if TYPE_CHECKING:

2277 assert is_column_element(c)

2278

2279 effective_name = required_label_name = None

2280 fallback_label_name = c._non_anon_label or c._anon_name_label

2281 else:

2282 if TYPE_CHECKING:

2283 assert is_column_element(c)

2284

2285 if table_qualified:

2286 required_label_name = effective_name = (

2287 fallback_label_name

2288 ) = c._tq_label

2289 else:

2290 effective_name = fallback_label_name = c._non_anon_label

2291 required_label_name = None

2292

2293 if effective_name is None:

2294 # it seems like this could be _proxy_key and we would

2295 # not need _expression_label but it isn't

2296 # giving us a clue when to use anon_label instead

2297 expr_label = c._expression_label

2298 if expr_label is None:

2299 repeated = c._anon_name_label in names

2300 names[c._anon_name_label] = c

2301 effective_name = required_label_name = None

2302

2303 if repeated:

2304 # here, "required_label_name" is sent as

2305 # "None" and "fallback_label_name" is sent.

2306 if table_qualified:

2307 fallback_label_name = (

2308 c._dedupe_anon_tq_label_idx(dedupe_hash)

2309 )

2310 dedupe_hash += 1

2311 else:

2312 fallback_label_name = c._dedupe_anon_label_idx(

2313 dedupe_hash

2314 )

2315 dedupe_hash += 1

2316 else:

2317 fallback_label_name = c._anon_name_label

2318 else:

2319 required_label_name = effective_name = (

2320 fallback_label_name

2321 ) = expr_label

2322

2323 if effective_name is not None:

2324 if TYPE_CHECKING:

2325 assert is_column_element(c)

2326

2327 if effective_name in names:

2328 # when looking to see if names[name] is the same column as

2329 # c, use hash(), so that an annotated version of the column

2330 # is seen as the same as the non-annotated

2331 if hash(names[effective_name]) != hash(c):

2332 # different column under the same name. apply

2333 # disambiguating label

2334 if table_qualified:

2335 required_label_name = fallback_label_name = (

2336 c._anon_tq_label

2337 )

2338 else:

2339 required_label_name = fallback_label_name = (

2340 c._anon_name_label

2341 )

2342

2343 if anon_for_dupe_key and required_label_name in names:

2344 # here, c._anon_tq_label is definitely unique to

2345 # that column identity (or annotated version), so

2346 # this should always be true.

2347 # this is also an infrequent codepath because

2348 # you need two levels of duplication to be here

2349 assert hash(names[required_label_name]) == hash(c)

2350

2351 # the column under the disambiguating label is

2352 # already present. apply the "dedupe" label to

2353 # subsequent occurrences of the column so that the

2354 # original stays non-ambiguous

2355 if table_qualified:

2356 required_label_name = fallback_label_name = (

2357 c._dedupe_anon_tq_label_idx(dedupe_hash)

2358 )

2359 dedupe_hash += 1

2360 else:

2361 required_label_name = fallback_label_name = (

2362 c._dedupe_anon_label_idx(dedupe_hash)

2363 )

2364 dedupe_hash += 1

2365 repeated = True

2366 else:

2367 names[required_label_name] = c

2368 elif anon_for_dupe_key:

2369 # same column under the same name. apply the "dedupe"

2370 # label so that the original stays non-ambiguous

2371 if table_qualified:

2372 required_label_name = fallback_label_name = (

2373 c._dedupe_anon_tq_label_idx(dedupe_hash)

2374 )

2375 dedupe_hash += 1

2376 else:

2377 required_label_name = fallback_label_name = (

2378 c._dedupe_anon_label_idx(dedupe_hash)

2379 )

2380 dedupe_hash += 1

2381 repeated = True

2382 else:

2383 names[effective_name] = c

2384

2385 result_append(

2386 _ColumnsPlusNames(

2387 required_label_name,

2388 key_naming_convention(c),

2389 fallback_label_name,

2390 c,

2391 repeated,

2392 )

2393 )

2394

2395 return result

2396

2397

2398class HasCTE(roles.HasCTERole, SelectsRows):

2399 """Mixin that declares a class to include CTE support."""

2400

2401 _has_ctes_traverse_internals: _TraverseInternalsType = [

2402 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2403 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2404 ]

2405

2406 _independent_ctes: Tuple[CTE, ...] = ()

2407 _independent_ctes_opts: Tuple[_CTEOpts, ...] = ()

2408

2409 @_generative

2410 def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self:

2411 r"""Add one or more :class:`_sql.CTE` constructs to this statement.

2412

2413 This method will associate the given :class:`_sql.CTE` constructs with

2414 the parent statement such that they will each be unconditionally

2415 rendered in the WITH clause of the final statement, even if not

2416 referenced elsewhere within the statement or any sub-selects.

2417

2418 The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set

2419 to True will have the effect that each given :class:`_sql.CTE` will

2420 render in a WITH clause rendered directly along with this statement,

2421 rather than being moved to the top of the ultimate rendered statement,

2422 even if this statement is rendered as a subquery within a larger

2423 statement.

2424

2425 This method has two general uses. One is to embed CTE statements that

2426 serve some purpose without being referenced explicitly, such as the use

2427 case of embedding a DML statement such as an INSERT or UPDATE as a CTE

2428 inline with a primary statement that may draw from its results

2429 indirectly. The other is to provide control over the exact placement

2430 of a particular series of CTE constructs that should remain rendered

2431 directly in terms of a particular statement that may be nested in a

2432 larger statement.

2433

2434 E.g.::

2435

2436 from sqlalchemy import table, column, select

2437 t = table('t', column('c1'), column('c2'))

2438

2439 ins = t.insert().values({"c1": "x", "c2": "y"}).cte()

2440

2441 stmt = select(t).add_cte(ins)

2442

2443 Would render::

2444

2445 WITH anon_1 AS

2446 (INSERT INTO t (c1, c2) VALUES (:param_1, :param_2))

2447 SELECT t.c1, t.c2

2448 FROM t

2449

2450 Above, the "anon_1" CTE is not referenced in the SELECT

2451 statement, however still accomplishes the task of running an INSERT

2452 statement.

2453

2454 Similarly in a DML-related context, using the PostgreSQL

2455 :class:`_postgresql.Insert` construct to generate an "upsert"::

2456

2457 from sqlalchemy import table, column

2458 from sqlalchemy.dialects.postgresql import insert

2459

2460 t = table("t", column("c1"), column("c2"))

2461

2462 delete_statement_cte = (

2463 t.delete().where(t.c.c1 < 1).cte("deletions")

2464 )

2465

2466 insert_stmt = insert(t).values({"c1": 1, "c2": 2})

2467 update_statement = insert_stmt.on_conflict_do_update(

2468 index_elements=[t.c.c1],

2469 set_={

2470 "c1": insert_stmt.excluded.c1,

2471 "c2": insert_stmt.excluded.c2,

2472 },

2473 ).add_cte(delete_statement_cte)

2474

2475 print(update_statement)

2476

2477 The above statement renders as::

2478

2479 WITH deletions AS

2480 (DELETE FROM t WHERE t.c1 < %(c1_1)s)

2481 INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)

2482 ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2

2483

2484 .. versionadded:: 1.4.21

2485

2486 :param \*ctes: zero or more :class:`.CTE` constructs.

2487

2488 .. versionchanged:: 2.0 Multiple CTE instances are accepted

2489

2490 :param nest_here: if True, the given CTE or CTEs will be rendered

2491 as though they specified the :paramref:`.HasCTE.cte.nesting` flag

2492 to ``True`` when they were added to this :class:`.HasCTE`.

2493 Assuming the given CTEs are not referenced in an outer-enclosing

2494 statement as well, the CTEs given should render at the level of

2495 this statement when this flag is given.

2496

2497 .. versionadded:: 2.0

2498

2499 .. seealso::

2500

2501 :paramref:`.HasCTE.cte.nesting`

2502

2503

2504 """

2505 opt = _CTEOpts(

2506 nest_here,

2507 )

2508 for cte in ctes:

2509 cte = coercions.expect(roles.IsCTERole, cte)

2510 self._independent_ctes += (cte,)

2511 self._independent_ctes_opts += (opt,)

2512 return self

2513

2514 def cte(

2515 self,

2516 name: Optional[str] = None,

2517 recursive: bool = False,

2518 nesting: bool = False,

2519 ) -> CTE:

2520 r"""Return a new :class:`_expression.CTE`,

2521 or Common Table Expression instance.

2522

2523 Common table expressions are a SQL standard whereby SELECT

2524 statements can draw upon secondary statements specified along

2525 with the primary statement, using a clause called "WITH".

2526 Special semantics regarding UNION can also be employed to

2527 allow "recursive" queries, where a SELECT statement can draw

2528 upon the set of rows that have previously been selected.

2529

2530 CTEs can also be applied to DML constructs UPDATE, INSERT

2531 and DELETE on some databases, both as a source of CTE rows

2532 when combined with RETURNING, as well as a consumer of

2533 CTE rows.

2534

2535 SQLAlchemy detects :class:`_expression.CTE` objects, which are treated

2536 similarly to :class:`_expression.Alias` objects, as special elements

2537 to be delivered to the FROM clause of the statement as well

2538 as to a WITH clause at the top of the statement.

2539

2540 For special prefixes such as PostgreSQL "MATERIALIZED" and

2541 "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`

2542 method may be

2543 used to establish these.

2544

2545 .. versionchanged:: 1.3.13 Added support for prefixes.

2546 In particular - MATERIALIZED and NOT MATERIALIZED.

2547

2548 :param name: name given to the common table expression. Like

2549 :meth:`_expression.FromClause.alias`, the name can be left as

2550 ``None`` in which case an anonymous symbol will be used at query

2551 compile time.

2552 :param recursive: if ``True``, will render ``WITH RECURSIVE``.

2553 A recursive common table expression is intended to be used in

2554 conjunction with UNION ALL in order to derive rows

2555 from those already selected.

2556 :param nesting: if ``True``, will render the CTE locally to the

2557 statement in which it is referenced. For more complex scenarios,

2558 the :meth:`.HasCTE.add_cte` method using the

2559 :paramref:`.HasCTE.add_cte.nest_here`

2560 parameter may also be used to more carefully

2561 control the exact placement of a particular CTE.

2562

2563 .. versionadded:: 1.4.24

2564

2565 .. seealso::

2566

2567 :meth:`.HasCTE.add_cte`

2568

2569 The following examples include two from PostgreSQL's documentation at

2570 https://www.postgresql.org/docs/current/static/queries-with.html,

2571 as well as additional examples.

2572

2573 Example 1, non recursive::

2574

2575 from sqlalchemy import (Table, Column, String, Integer,

2576 MetaData, select, func)

2577

2578 metadata = MetaData()

2579

2580 orders = Table('orders', metadata,

2581 Column('region', String),

2582 Column('amount', Integer),

2583 Column('product', String),

2584 Column('quantity', Integer)

2585 )

2586

2587 regional_sales = select(

2588 orders.c.region,

2589 func.sum(orders.c.amount).label('total_sales')

2590 ).group_by(orders.c.region).cte("regional_sales")

2591

2592

2593 top_regions = select(regional_sales.c.region).\

2594 where(

2595 regional_sales.c.total_sales >

2596 select(

2597 func.sum(regional_sales.c.total_sales) / 10

2598 )

2599 ).cte("top_regions")

2600

2601 statement = select(

2602 orders.c.region,

2603 orders.c.product,

2604 func.sum(orders.c.quantity).label("product_units"),

2605 func.sum(orders.c.amount).label("product_sales")

2606 ).where(orders.c.region.in_(

2607 select(top_regions.c.region)

2608 )).group_by(orders.c.region, orders.c.product)

2609

2610 result = conn.execute(statement).fetchall()

2611

2612 Example 2, WITH RECURSIVE::

2613

2614 from sqlalchemy import (Table, Column, String, Integer,

2615 MetaData, select, func)

2616

2617 metadata = MetaData()

2618

2619 parts = Table('parts', metadata,

2620 Column('part', String),

2621 Column('sub_part', String),

2622 Column('quantity', Integer),

2623 )

2624

2625 included_parts = select(\

2626 parts.c.sub_part, parts.c.part, parts.c.quantity\

2627 ).\

2628 where(parts.c.part=='our part').\

2629 cte(recursive=True)

2630

2631

2632 incl_alias = included_parts.alias()

2633 parts_alias = parts.alias()

2634 included_parts = included_parts.union_all(

2635 select(

2636 parts_alias.c.sub_part,

2637 parts_alias.c.part,

2638 parts_alias.c.quantity

2639 ).\

2640 where(parts_alias.c.part==incl_alias.c.sub_part)

2641 )

2642

2643 statement = select(

2644 included_parts.c.sub_part,

2645 func.sum(included_parts.c.quantity).

2646 label('total_quantity')

2647 ).\

2648 group_by(included_parts.c.sub_part)

2649

2650 result = conn.execute(statement).fetchall()

2651

2652 Example 3, an upsert using UPDATE and INSERT with CTEs::

2653

2654 from datetime import date

2655 from sqlalchemy import (MetaData, Table, Column, Integer,

2656 Date, select, literal, and_, exists)

2657

2658 metadata = MetaData()

2659

2660 visitors = Table('visitors', metadata,

2661 Column('product_id', Integer, primary_key=True),

2662 Column('date', Date, primary_key=True),

2663 Column('count', Integer),

2664 )

2665

2666 # add 5 visitors for the product_id == 1

2667 product_id = 1

2668 day = date.today()

2669 count = 5

2670

2671 update_cte = (

2672 visitors.update()

2673 .where(and_(visitors.c.product_id == product_id,

2674 visitors.c.date == day))

2675 .values(count=visitors.c.count + count)

2676 .returning(literal(1))

2677 .cte('update_cte')

2678 )

2679

2680 upsert = visitors.insert().from_select(

2681 [visitors.c.product_id, visitors.c.date, visitors.c.count],

2682 select(literal(product_id), literal(day), literal(count))

2683 .where(~exists(update_cte.select()))

2684 )

2685

2686 connection.execute(upsert)

2687

2688 Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::

2689

2690 value_a = select(

2691 literal("root").label("n")

2692 ).cte("value_a")

2693

2694 # A nested CTE with the same name as the root one

2695 value_a_nested = select(

2696 literal("nesting").label("n")

2697 ).cte("value_a", nesting=True)

2698

2699 # Nesting CTEs takes ascendency locally

2700 # over the CTEs at a higher level

2701 value_b = select(value_a_nested.c.n).cte("value_b")

2702

2703 value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

2704

2705 The above query will render the second CTE nested inside the first,

2706 shown with inline parameters below as::

2707

2708 WITH

2709 value_a AS

2710 (SELECT 'root' AS n),

2711 value_b AS

2712 (WITH value_a AS

2713 (SELECT 'nesting' AS n)

2714 SELECT value_a.n AS n FROM value_a)

2715 SELECT value_a.n AS a, value_b.n AS b

2716 FROM value_a, value_b

2717

2718 The same CTE can be set up using the :meth:`.HasCTE.add_cte` method

2719 as follows (SQLAlchemy 2.0 and above)::

2720

2721 value_a = select(

2722 literal("root").label("n")

2723 ).cte("value_a")

2724

2725 # A nested CTE with the same name as the root one

2726 value_a_nested = select(

2727 literal("nesting").label("n")

2728 ).cte("value_a")

2729

2730 # Nesting CTEs takes ascendency locally

2731 # over the CTEs at a higher level

2732 value_b = (

2733 select(value_a_nested.c.n).

2734 add_cte(value_a_nested, nest_here=True).

2735 cte("value_b")

2736 )

2737

2738 value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

2739

2740 Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above)::

2741

2742 edge = Table(

2743 "edge",

2744 metadata,

2745 Column("id", Integer, primary_key=True),

2746 Column("left", Integer),

2747 Column("right", Integer),

2748 )

2749

2750 root_node = select(literal(1).label("node")).cte(

2751 "nodes", recursive=True

2752 )

2753

2754 left_edge = select(edge.c.left).join(

2755 root_node, edge.c.right == root_node.c.node

2756 )

2757 right_edge = select(edge.c.right).join(

2758 root_node, edge.c.left == root_node.c.node

2759 )

2760

2761 subgraph_cte = root_node.union(left_edge, right_edge)

2762

2763 subgraph = select(subgraph_cte)

2764

2765 The above query will render 2 UNIONs inside the recursive CTE::

2766

2767 WITH RECURSIVE nodes(node) AS (

2768 SELECT 1 AS node

2769 UNION

2770 SELECT edge."left" AS "left"

2771 FROM edge JOIN nodes ON edge."right" = nodes.node

2772 UNION

2773 SELECT edge."right" AS "right"

2774 FROM edge JOIN nodes ON edge."left" = nodes.node

2775 )

2776 SELECT nodes.node FROM nodes

2777

2778 .. seealso::

2779

2780 :meth:`_orm.Query.cte` - ORM version of

2781 :meth:`_expression.HasCTE.cte`.

2782

2783 """

2784 return CTE._construct(

2785 self, name=name, recursive=recursive, nesting=nesting

2786 )

2787

2788

2789class Subquery(AliasedReturnsRows):

2790 """Represent a subquery of a SELECT.

2791

2792 A :class:`.Subquery` is created by invoking the

2793 :meth:`_expression.SelectBase.subquery` method, or for convenience the

2794 :meth:`_expression.SelectBase.alias` method, on any

2795 :class:`_expression.SelectBase` subclass

2796 which includes :class:`_expression.Select`,

2797 :class:`_expression.CompoundSelect`, and

2798 :class:`_expression.TextualSelect`. As rendered in a FROM clause,

2799 it represents the

2800 body of the SELECT statement inside of parenthesis, followed by the usual

2801 "AS <somename>" that defines all "alias" objects.

2802

2803 The :class:`.Subquery` object is very similar to the

2804 :class:`_expression.Alias`

2805 object and can be used in an equivalent way. The difference between

2806 :class:`_expression.Alias` and :class:`.Subquery` is that

2807 :class:`_expression.Alias` always

2808 contains a :class:`_expression.FromClause` object whereas

2809 :class:`.Subquery`

2810 always contains a :class:`_expression.SelectBase` object.

2811

2812 .. versionadded:: 1.4 The :class:`.Subquery` class was added which now

2813 serves the purpose of providing an aliased version of a SELECT

2814 statement.

2815

2816 """

2817

2818 __visit_name__ = "subquery"

2819

2820 _is_subquery = True

2821

2822 inherit_cache = True

2823

2824 element: SelectBase

2825

2826 @classmethod

2827 def _factory(

2828 cls, selectable: SelectBase, name: Optional[str] = None

2829 ) -> Subquery:

2830 """Return a :class:`.Subquery` object."""

2831

2832 return coercions.expect(

2833 roles.SelectStatementRole, selectable

2834 ).subquery(name=name)

2835

2836 @util.deprecated(

2837 "1.4",

2838 "The :meth:`.Subquery.as_scalar` method, which was previously "

2839 "``Alias.as_scalar()`` prior to version 1.4, is deprecated and "

2840 "will be removed in a future release; Please use the "

2841 ":meth:`_expression.Select.scalar_subquery` method of the "

2842 ":func:`_expression.select` "

2843 "construct before constructing a subquery object, or with the ORM "

2844 "use the :meth:`_query.Query.scalar_subquery` method.",

2845 )

2846 def as_scalar(self) -> ScalarSelect[Any]:

2847 return self.element.set_label_style(LABEL_STYLE_NONE).scalar_subquery()

2848

2849

2850class FromGrouping(GroupedElement, FromClause):

2851 """Represent a grouping of a FROM clause"""

2852

2853 _traverse_internals: _TraverseInternalsType = [

2854 ("element", InternalTraversal.dp_clauseelement)

2855 ]

2856

2857 element: FromClause

2858

2859 def __init__(self, element: FromClause):

2860 self.element = coercions.expect(roles.FromClauseRole, element)

2861

2862 def _init_collections(self) -> None:

2863 pass

2864

2865 @util.ro_non_memoized_property

2866 def columns(

2867 self,

2868 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

2869 return self.element.columns

2870

2871 @util.ro_non_memoized_property

2872 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

2873 return self.element.columns

2874

2875 @property

2876 def primary_key(self) -> Iterable[NamedColumn[Any]]:

2877 return self.element.primary_key

2878

2879 @property

2880 def foreign_keys(self) -> Iterable[ForeignKey]:

2881 return self.element.foreign_keys

2882

2883 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

2884 return self.element.is_derived_from(fromclause)

2885

2886 def alias(

2887 self, name: Optional[str] = None, flat: bool = False

2888 ) -> NamedFromGrouping:

2889 return NamedFromGrouping(self.element.alias(name=name, flat=flat))

2890

2891 def _anonymous_fromclause(self, **kw: Any) -> FromGrouping:

2892 return FromGrouping(self.element._anonymous_fromclause(**kw))

2893

2894 @util.ro_non_memoized_property

2895 def _hide_froms(self) -> Iterable[FromClause]:

2896 return self.element._hide_froms

2897

2898 @util.ro_non_memoized_property

2899 def _from_objects(self) -> List[FromClause]:

2900 return self.element._from_objects

2901

2902 def __getstate__(self) -> Dict[str, FromClause]:

2903 return {"element": self.element}

2904

2905 def __setstate__(self, state: Dict[str, FromClause]) -> None:

2906 self.element = state["element"]

2907

2908 if TYPE_CHECKING:

2909

2910 def self_group(

2911 self, against: Optional[OperatorType] = None

2912 ) -> Self: ...

2913

2914

2915class NamedFromGrouping(FromGrouping, NamedFromClause):

2916 """represent a grouping of a named FROM clause

2917

2918 .. versionadded:: 2.0

2919

2920 """

2921

2922 inherit_cache = True

2923

2924 if TYPE_CHECKING:

2925

2926 def self_group(

2927 self, against: Optional[OperatorType] = None

2928 ) -> Self: ...

2929

2930

2931class TableClause(roles.DMLTableRole, Immutable, NamedFromClause):

2932 """Represents a minimal "table" construct.

2933

2934 This is a lightweight table object that has only a name, a

2935 collection of columns, which are typically produced

2936 by the :func:`_expression.column` function, and a schema::

2937

2938 from sqlalchemy import table, column

2939

2940 user = table("user",

2941 column("id"),

2942 column("name"),

2943 column("description"),

2944 )

2945

2946 The :class:`_expression.TableClause` construct serves as the base for

2947 the more commonly used :class:`_schema.Table` object, providing

2948 the usual set of :class:`_expression.FromClause` services including

2949 the ``.c.`` collection and statement generation methods.

2950

2951 It does **not** provide all the additional schema-level services

2952 of :class:`_schema.Table`, including constraints, references to other

2953 tables, or support for :class:`_schema.MetaData`-level services.

2954 It's useful

2955 on its own as an ad-hoc construct used to generate quick SQL

2956 statements when a more fully fledged :class:`_schema.Table`

2957 is not on hand.

2958

2959 """

2960

2961 __visit_name__ = "table"

2962

2963 _traverse_internals: _TraverseInternalsType = [

2964 (

2965 "columns",

2966 InternalTraversal.dp_fromclause_canonical_column_collection,

2967 ),

2968 ("name", InternalTraversal.dp_string),

2969 ("schema", InternalTraversal.dp_string),

2970 ]

2971

2972 _is_table = True

2973

2974 fullname: str

2975

2976 implicit_returning = False

2977 """:class:`_expression.TableClause`

2978 doesn't support having a primary key or column

2979 -level defaults, so implicit returning doesn't apply."""

2980

2981 @util.ro_memoized_property

2982 def _autoincrement_column(self) -> Optional[ColumnClause[Any]]:

2983 """No PK or default support so no autoincrement column."""

2984 return None

2985

2986 def __init__(self, name: str, *columns: ColumnClause[Any], **kw: Any):

2987 super().__init__()

2988 self.name = name

2989 self._columns = DedupeColumnCollection()

2990 self.primary_key = ColumnSet() # type: ignore

2991 self.foreign_keys = set() # type: ignore

2992 for c in columns:

2993 self.append_column(c)

2994

2995 schema = kw.pop("schema", None)

2996 if schema is not None:

2997 self.schema = schema

2998 if self.schema is not None:

2999 self.fullname = "%s.%s" % (self.schema, self.name)

3000 else:

3001 self.fullname = self.name

3002 if kw:

3003 raise exc.ArgumentError("Unsupported argument(s): %s" % list(kw))

3004

3005 if TYPE_CHECKING:

3006

3007 @util.ro_non_memoized_property

3008 def columns(

3009 self,

3010 ) -> ReadOnlyColumnCollection[str, ColumnClause[Any]]: ...

3011

3012 @util.ro_non_memoized_property

3013 def c(self) -> ReadOnlyColumnCollection[str, ColumnClause[Any]]: ...

3014

3015 def __str__(self) -> str:

3016 if self.schema is not None:

3017 return self.schema + "." + self.name

3018 else:

3019 return self.name

3020

3021 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

3022 pass

3023

3024 def _init_collections(self) -> None:

3025 pass

3026

3027 @util.ro_memoized_property

3028 def description(self) -> str:

3029 return self.name

3030

3031 def append_column(self, c: ColumnClause[Any]) -> None:

3032 existing = c.table

3033 if existing is not None and existing is not self:

3034 raise exc.ArgumentError(

3035 "column object '%s' already assigned to table '%s'"

3036 % (c.key, existing)

3037 )

3038

3039 self._columns.add(c)

3040 c.table = self

3041

3042 @util.preload_module("sqlalchemy.sql.dml")

3043 def insert(self) -> util.preloaded.sql_dml.Insert:

3044 """Generate an :class:`_sql.Insert` construct against this

3045 :class:`_expression.TableClause`.

3046

3047 E.g.::

3048

3049 table.insert().values(name='foo')

3050

3051 See :func:`_expression.insert` for argument and usage information.

3052

3053 """

3054

3055 return util.preloaded.sql_dml.Insert(self)

3056

3057 @util.preload_module("sqlalchemy.sql.dml")

3058 def update(self) -> Update:

3059 """Generate an :func:`_expression.update` construct against this

3060 :class:`_expression.TableClause`.

3061

3062 E.g.::

3063

3064 table.update().where(table.c.id==7).values(name='foo')

3065

3066 See :func:`_expression.update` for argument and usage information.

3067

3068 """

3069 return util.preloaded.sql_dml.Update(

3070 self,

3071 )

3072

3073 @util.preload_module("sqlalchemy.sql.dml")

3074 def delete(self) -> Delete:

3075 """Generate a :func:`_expression.delete` construct against this

3076 :class:`_expression.TableClause`.

3077

3078 E.g.::

3079

3080 table.delete().where(table.c.id==7)

3081

3082 See :func:`_expression.delete` for argument and usage information.

3083

3084 """

3085 return util.preloaded.sql_dml.Delete(self)

3086

3087 @util.ro_non_memoized_property

3088 def _from_objects(self) -> List[FromClause]:

3089 return [self]

3090

3091

3092ForUpdateParameter = Union["ForUpdateArg", None, bool, Dict[str, Any]]

3093

3094

3095class ForUpdateArg(ClauseElement):

3096 _traverse_internals: _TraverseInternalsType = [

3097 ("of", InternalTraversal.dp_clauseelement_list),

3098 ("nowait", InternalTraversal.dp_boolean),

3099 ("read", InternalTraversal.dp_boolean),

3100 ("skip_locked", InternalTraversal.dp_boolean),

3101 ("key_share", InternalTraversal.dp_boolean),

3102 ]

3103

3104 of: Optional[Sequence[ClauseElement]]

3105 nowait: bool

3106 read: bool

3107 skip_locked: bool

3108

3109 @classmethod

3110 def _from_argument(

3111 cls, with_for_update: ForUpdateParameter

3112 ) -> Optional[ForUpdateArg]:

3113 if isinstance(with_for_update, ForUpdateArg):

3114 return with_for_update

3115 elif with_for_update in (None, False):

3116 return None

3117 elif with_for_update is True:

3118 return ForUpdateArg()

3119 else:

3120 return ForUpdateArg(**cast("Dict[str, Any]", with_for_update))

3121

3122 def __eq__(self, other: Any) -> bool:

3123 return (

3124 isinstance(other, ForUpdateArg)

3125 and other.nowait == self.nowait

3126 and other.read == self.read

3127 and other.skip_locked == self.skip_locked

3128 and other.key_share == self.key_share

3129 and other.of is self.of

3130 )

3131

3132 def __ne__(self, other: Any) -> bool:

3133 return not self.__eq__(other)

3134

3135 def __hash__(self) -> int:

3136 return id(self)

3137

3138 def __init__(

3139 self,

3140 *,

3141 nowait: bool = False,

3142 read: bool = False,

3143 of: Optional[_ForUpdateOfArgument] = None,

3144 skip_locked: bool = False,

3145 key_share: bool = False,

3146 ):

3147 """Represents arguments specified to

3148 :meth:`_expression.Select.for_update`.

3149

3150 """

3151

3152 self.nowait = nowait

3153 self.read = read

3154 self.skip_locked = skip_locked

3155 self.key_share = key_share

3156 if of is not None:

3157 self.of = [

3158 coercions.expect(roles.ColumnsClauseRole, elem)

3159 for elem in util.to_list(of)

3160 ]

3161 else:

3162 self.of = None

3163

3164

3165class Values(roles.InElementRole, Generative, LateralFromClause):

3166 """Represent a ``VALUES`` construct that can be used as a FROM element

3167 in a statement.

3168

3169 The :class:`_expression.Values` object is created from the

3170 :func:`_expression.values` function.

3171

3172 .. versionadded:: 1.4

3173

3174 """

3175

3176 __visit_name__ = "values"

3177

3178 _data: Tuple[Sequence[Tuple[Any, ...]], ...] = ()

3179

3180 _unnamed: bool

3181 _traverse_internals: _TraverseInternalsType = [

3182 ("_column_args", InternalTraversal.dp_clauseelement_list),

3183 ("_data", InternalTraversal.dp_dml_multi_values),

3184 ("name", InternalTraversal.dp_string),

3185 ("literal_binds", InternalTraversal.dp_boolean),

3186 ]

3187

3188 def __init__(

3189 self,

3190 *columns: ColumnClause[Any],

3191 name: Optional[str] = None,

3192 literal_binds: bool = False,

3193 ):

3194 super().__init__()

3195 self._column_args = columns

3196

3197 if name is None:

3198 self._unnamed = True

3199 self.name = _anonymous_label.safe_construct(id(self), "anon")

3200 else:

3201 self._unnamed = False

3202 self.name = name

3203 self.literal_binds = literal_binds

3204 self.named_with_column = not self._unnamed

3205

3206 @property

3207 def _column_types(self) -> List[TypeEngine[Any]]:

3208 return [col.type for col in self._column_args]

3209

3210 @_generative

3211 def alias(self, name: Optional[str] = None, flat: bool = False) -> Self:

3212 """Return a new :class:`_expression.Values`

3213 construct that is a copy of this

3214 one with the given name.

3215

3216 This method is a VALUES-specific specialization of the

3217 :meth:`_expression.FromClause.alias` method.

3218

3219 .. seealso::

3220

3221 :ref:`tutorial_using_aliases`

3222

3223 :func:`_expression.alias`

3224

3225 """

3226 non_none_name: str

3227

3228 if name is None:

3229 non_none_name = _anonymous_label.safe_construct(id(self), "anon")

3230 else:

3231 non_none_name = name

3232

3233 self.name = non_none_name

3234 self.named_with_column = True

3235 self._unnamed = False

3236 return self

3237

3238 @_generative

3239 def lateral(self, name: Optional[str] = None) -> LateralFromClause:

3240 """Return a new :class:`_expression.Values` with the lateral flag set,

3241 so that

3242 it renders as LATERAL.

3243

3244 .. seealso::

3245

3246 :func:`_expression.lateral`

3247

3248 """

3249 non_none_name: str

3250

3251 if name is None:

3252 non_none_name = self.name

3253 else:

3254 non_none_name = name

3255

3256 self._is_lateral = True

3257 self.name = non_none_name

3258 self._unnamed = False

3259 return self

3260

3261 @_generative

3262 def data(self, values: Sequence[Tuple[Any, ...]]) -> Self:

3263 """Return a new :class:`_expression.Values` construct,

3264 adding the given data to the data list.

3265

3266 E.g.::

3267

3268 my_values = my_values.data([(1, 'value 1'), (2, 'value2')])

3269

3270 :param values: a sequence (i.e. list) of tuples that map to the

3271 column expressions given in the :class:`_expression.Values`

3272 constructor.

3273

3274 """

3275

3276 self._data += (values,)

3277 return self

3278

3279 def scalar_values(self) -> ScalarValues:

3280 """Returns a scalar ``VALUES`` construct that can be used as a

3281 COLUMN element in a statement.

3282

3283 .. versionadded:: 2.0.0b4

3284

3285 """

3286 return ScalarValues(self._column_args, self._data, self.literal_binds)

3287

3288 def _populate_column_collection(self) -> None:

3289 for c in self._column_args:

3290 if c.table is not None and c.table is not self:

3291 _, c = c._make_proxy(self)

3292 else:

3293 # if the column was used in other contexts, ensure

3294 # no memoizations of other FROM clauses.

3295 # see test_values.py -> test_auto_proxy_select_direct_col

3296 c._reset_memoizations()

3297 self._columns.add(c)

3298 c.table = self

3299

3300 @util.ro_non_memoized_property

3301 def _from_objects(self) -> List[FromClause]:

3302 return [self]

3303

3304

3305class ScalarValues(roles.InElementRole, GroupedElement, ColumnElement[Any]):

3306 """Represent a scalar ``VALUES`` construct that can be used as a

3307 COLUMN element in a statement.

3308

3309 The :class:`_expression.ScalarValues` object is created from the

3310 :meth:`_expression.Values.scalar_values` method. It's also

3311 automatically generated when a :class:`_expression.Values` is used in

3312 an ``IN`` or ``NOT IN`` condition.

3313

3314 .. versionadded:: 2.0.0b4

3315

3316 """

3317

3318 __visit_name__ = "scalar_values"

3319

3320 _traverse_internals: _TraverseInternalsType = [

3321 ("_column_args", InternalTraversal.dp_clauseelement_list),

3322 ("_data", InternalTraversal.dp_dml_multi_values),

3323 ("literal_binds", InternalTraversal.dp_boolean),

3324 ]

3325

3326 def __init__(

3327 self,

3328 columns: Sequence[ColumnClause[Any]],

3329 data: Tuple[Sequence[Tuple[Any, ...]], ...],

3330 literal_binds: bool,

3331 ):

3332 super().__init__()

3333 self._column_args = columns

3334 self._data = data

3335 self.literal_binds = literal_binds

3336

3337 @property

3338 def _column_types(self) -> List[TypeEngine[Any]]:

3339 return [col.type for col in self._column_args]

3340

3341 def __clause_element__(self) -> ScalarValues:

3342 return self

3343

3344 if TYPE_CHECKING:

3345

3346 def self_group(

3347 self, against: Optional[OperatorType] = None

3348 ) -> Self: ...

3349

3350

3351class SelectBase(

3352 roles.SelectStatementRole,

3353 roles.DMLSelectRole,

3354 roles.CompoundElementRole,

3355 roles.InElementRole,

3356 HasCTE,

3357 SupportsCloneAnnotations,

3358 Selectable,

3359):

3360 """Base class for SELECT statements.

3361

3362

3363 This includes :class:`_expression.Select`,

3364 :class:`_expression.CompoundSelect` and

3365 :class:`_expression.TextualSelect`.

3366

3367

3368 """

3369

3370 _is_select_base = True

3371 is_select = True

3372

3373 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3374

3375 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

3376 self._reset_memoizations()

3377

3378 @util.ro_non_memoized_property

3379 def selected_columns(

3380 self,

3381 ) -> ColumnCollection[str, ColumnElement[Any]]:

3382 """A :class:`_expression.ColumnCollection`

3383 representing the columns that

3384 this SELECT statement or similar construct returns in its result set.

3385

3386 This collection differs from the :attr:`_expression.FromClause.columns`

3387 collection of a :class:`_expression.FromClause` in that the columns

3388 within this collection cannot be directly nested inside another SELECT

3389 statement; a subquery must be applied first which provides for the

3390 necessary parenthesization required by SQL.

3391

3392 .. note::

3393

3394 The :attr:`_sql.SelectBase.selected_columns` collection does not

3395 include expressions established in the columns clause using the

3396 :func:`_sql.text` construct; these are silently omitted from the

3397 collection. To use plain textual column expressions inside of a

3398 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

3399 construct.

3400

3401 .. seealso::

3402

3403 :attr:`_sql.Select.selected_columns`

3404

3405 .. versionadded:: 1.4

3406

3407 """

3408 raise NotImplementedError()

3409

3410 def _generate_fromclause_column_proxies(

3411 self,

3412 subquery: FromClause,

3413 *,

3414 proxy_compound_columns: Optional[

3415 Iterable[Sequence[ColumnElement[Any]]]

3416 ] = None,

3417 ) -> None:

3418 raise NotImplementedError()

3419

3420 @util.ro_non_memoized_property

3421 def _all_selected_columns(self) -> _SelectIterable:

3422 """A sequence of expressions that correspond to what is rendered

3423 in the columns clause, including :class:`_sql.TextClause`

3424 constructs.

3425

3426 .. versionadded:: 1.4.12

3427

3428 .. seealso::

3429

3430 :attr:`_sql.SelectBase.exported_columns`

3431

3432 """

3433 raise NotImplementedError()

3434

3435 @property

3436 def exported_columns(

3437 self,

3438 ) -> ReadOnlyColumnCollection[str, ColumnElement[Any]]:

3439 """A :class:`_expression.ColumnCollection`

3440 that represents the "exported"

3441 columns of this :class:`_expression.Selectable`, not including

3442 :class:`_sql.TextClause` constructs.

3443

3444 The "exported" columns for a :class:`_expression.SelectBase`

3445 object are synonymous

3446 with the :attr:`_expression.SelectBase.selected_columns` collection.

3447

3448 .. versionadded:: 1.4

3449

3450 .. seealso::

3451

3452 :attr:`_expression.Select.exported_columns`

3453

3454 :attr:`_expression.Selectable.exported_columns`

3455

3456 :attr:`_expression.FromClause.exported_columns`

3457

3458

3459 """

3460 return self.selected_columns.as_readonly()

3461

3462 @property

3463 @util.deprecated(

3464 "1.4",

3465 "The :attr:`_expression.SelectBase.c` and "

3466 ":attr:`_expression.SelectBase.columns` attributes "

3467 "are deprecated and will be removed in a future release; these "

3468 "attributes implicitly create a subquery that should be explicit. "

3469 "Please call :meth:`_expression.SelectBase.subquery` "

3470 "first in order to create "

3471 "a subquery, which then contains this attribute. To access the "

3472 "columns that this SELECT object SELECTs "

3473 "from, use the :attr:`_expression.SelectBase.selected_columns` "

3474 "attribute.",

3475 )

3476 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

3477 return self._implicit_subquery.columns

3478

3479 @property

3480 def columns(

3481 self,

3482 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

3483 return self.c

3484

3485 def get_label_style(self) -> SelectLabelStyle:

3486 """

3487 Retrieve the current label style.

3488

3489 Implemented by subclasses.

3490

3491 """

3492 raise NotImplementedError()

3493

3494 def set_label_style(self, style: SelectLabelStyle) -> Self:

3495 """Return a new selectable with the specified label style.

3496

3497 Implemented by subclasses.

3498

3499 """

3500

3501 raise NotImplementedError()

3502

3503 @util.deprecated(

3504 "1.4",

3505 "The :meth:`_expression.SelectBase.select` method is deprecated "

3506 "and will be removed in a future release; this method implicitly "

3507 "creates a subquery that should be explicit. "

3508 "Please call :meth:`_expression.SelectBase.subquery` "

3509 "first in order to create "

3510 "a subquery, which then can be selected.",

3511 )

3512 def select(self, *arg: Any, **kw: Any) -> Select[Unpack[TupleAny]]:

3513 return self._implicit_subquery.select(*arg, **kw)

3514

3515 @HasMemoized.memoized_attribute

3516 def _implicit_subquery(self) -> Subquery:

3517 return self.subquery()

3518

3519 def _scalar_type(self) -> TypeEngine[Any]:

3520 raise NotImplementedError()

3521

3522 @util.deprecated(

3523 "1.4",

3524 "The :meth:`_expression.SelectBase.as_scalar` "

3525 "method is deprecated and will be "

3526 "removed in a future release. Please refer to "

3527 ":meth:`_expression.SelectBase.scalar_subquery`.",

3528 )

3529 def as_scalar(self) -> ScalarSelect[Any]:

3530 return self.scalar_subquery()

3531

3532 def exists(self) -> Exists:

3533 """Return an :class:`_sql.Exists` representation of this selectable,

3534 which can be used as a column expression.

3535

3536 The returned object is an instance of :class:`_sql.Exists`.

3537

3538 .. seealso::

3539

3540 :func:`_sql.exists`

3541

3542 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.

3543

3544 .. versionadded:: 1.4

3545

3546 """

3547 return Exists(self)

3548

3549 def scalar_subquery(self) -> ScalarSelect[Any]:

3550 """Return a 'scalar' representation of this selectable, which can be

3551 used as a column expression.

3552

3553 The returned object is an instance of :class:`_sql.ScalarSelect`.

3554

3555 Typically, a select statement which has only one column in its columns

3556 clause is eligible to be used as a scalar expression. The scalar

3557 subquery can then be used in the WHERE clause or columns clause of

3558 an enclosing SELECT.

3559

3560 Note that the scalar subquery differentiates from the FROM-level

3561 subquery that can be produced using the

3562 :meth:`_expression.SelectBase.subquery`

3563 method.

3564

3565 .. versionchanged: 1.4 - the ``.as_scalar()`` method was renamed to

3566 :meth:`_expression.SelectBase.scalar_subquery`.

3567

3568 .. seealso::

3569

3570 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

3571

3572 """

3573 if self._label_style is not LABEL_STYLE_NONE:

3574 self = self.set_label_style(LABEL_STYLE_NONE)

3575

3576 return ScalarSelect(self)

3577

3578 def label(self, name: Optional[str]) -> Label[Any]:

3579 """Return a 'scalar' representation of this selectable, embedded as a

3580 subquery with a label.

3581

3582 .. seealso::

3583

3584 :meth:`_expression.SelectBase.scalar_subquery`.

3585

3586 """

3587 return self.scalar_subquery().label(name)

3588

3589 def lateral(self, name: Optional[str] = None) -> LateralFromClause:

3590 """Return a LATERAL alias of this :class:`_expression.Selectable`.

3591

3592 The return value is the :class:`_expression.Lateral` construct also

3593 provided by the top-level :func:`_expression.lateral` function.

3594

3595 .. seealso::

3596

3597 :ref:`tutorial_lateral_correlation` - overview of usage.

3598

3599 """

3600 return Lateral._factory(self, name)

3601

3602 def subquery(self, name: Optional[str] = None) -> Subquery:

3603 """Return a subquery of this :class:`_expression.SelectBase`.

3604

3605 A subquery is from a SQL perspective a parenthesized, named

3606 construct that can be placed in the FROM clause of another

3607 SELECT statement.

3608

3609 Given a SELECT statement such as::

3610

3611 stmt = select(table.c.id, table.c.name)

3612

3613 The above statement might look like::

3614

3615 SELECT table.id, table.name FROM table

3616

3617 The subquery form by itself renders the same way, however when

3618 embedded into the FROM clause of another SELECT statement, it becomes

3619 a named sub-element::

3620

3621 subq = stmt.subquery()

3622 new_stmt = select(subq)

3623

3624 The above renders as::

3625

3626 SELECT anon_1.id, anon_1.name

3627 FROM (SELECT table.id, table.name FROM table) AS anon_1

3628

3629 Historically, :meth:`_expression.SelectBase.subquery`

3630 is equivalent to calling

3631 the :meth:`_expression.FromClause.alias`

3632 method on a FROM object; however,

3633 as a :class:`_expression.SelectBase`

3634 object is not directly FROM object,

3635 the :meth:`_expression.SelectBase.subquery`

3636 method provides clearer semantics.

3637

3638 .. versionadded:: 1.4

3639

3640 """

3641

3642 return Subquery._construct(

3643 self._ensure_disambiguated_names(), name=name

3644 )

3645

3646 def _ensure_disambiguated_names(self) -> Self:

3647 """Ensure that the names generated by this selectbase will be

3648 disambiguated in some way, if possible.

3649

3650 """

3651

3652 raise NotImplementedError()

3653

3654 def alias(

3655 self, name: Optional[str] = None, flat: bool = False

3656 ) -> Subquery:

3657 """Return a named subquery against this

3658 :class:`_expression.SelectBase`.

3659

3660 For a :class:`_expression.SelectBase` (as opposed to a

3661 :class:`_expression.FromClause`),

3662 this returns a :class:`.Subquery` object which behaves mostly the

3663 same as the :class:`_expression.Alias` object that is used with a

3664 :class:`_expression.FromClause`.

3665

3666 .. versionchanged:: 1.4 The :meth:`_expression.SelectBase.alias`

3667 method is now

3668 a synonym for the :meth:`_expression.SelectBase.subquery` method.

3669

3670 """

3671 return self.subquery(name=name)

3672

3673

3674_SB = TypeVar("_SB", bound=SelectBase)

3675

3676

3677class SelectStatementGrouping(GroupedElement, SelectBase, Generic[_SB]):

3678 """Represent a grouping of a :class:`_expression.SelectBase`.

3679

3680 This differs from :class:`.Subquery` in that we are still

3681 an "inner" SELECT statement, this is strictly for grouping inside of

3682 compound selects.

3683

3684 """

3685

3686 __visit_name__ = "select_statement_grouping"

3687 _traverse_internals: _TraverseInternalsType = [

3688 ("element", InternalTraversal.dp_clauseelement)

3689 ]

3690

3691 _is_select_container = True

3692

3693 element: _SB

3694

3695 def __init__(self, element: _SB) -> None:

3696 self.element = cast(

3697 _SB, coercions.expect(roles.SelectStatementRole, element)

3698 )

3699

3700 def _ensure_disambiguated_names(self) -> SelectStatementGrouping[_SB]:

3701 new_element = self.element._ensure_disambiguated_names()

3702 if new_element is not self.element:

3703 return SelectStatementGrouping(new_element)

3704 else:

3705 return self

3706

3707 def get_label_style(self) -> SelectLabelStyle:

3708 return self.element.get_label_style()

3709

3710 def set_label_style(

3711 self, label_style: SelectLabelStyle

3712 ) -> SelectStatementGrouping[_SB]:

3713 return SelectStatementGrouping(

3714 self.element.set_label_style(label_style)

3715 )

3716

3717 @property

3718 def select_statement(self) -> _SB:

3719 return self.element

3720

3721 def self_group(self, against: Optional[OperatorType] = None) -> Self:

3722 return self

3723

3724 if TYPE_CHECKING:

3725

3726 def _ungroup(self) -> _SB: ...

3727

3728 # def _generate_columns_plus_names(

3729 # self, anon_for_dupe_key: bool

3730 # ) -> List[Tuple[str, str, str, ColumnElement[Any], bool]]:

3731 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3732

3733 def _generate_fromclause_column_proxies(

3734 self,

3735 subquery: FromClause,

3736 *,

3737 proxy_compound_columns: Optional[

3738 Iterable[Sequence[ColumnElement[Any]]]

3739 ] = None,

3740 ) -> None:

3741 self.element._generate_fromclause_column_proxies(

3742 subquery, proxy_compound_columns=proxy_compound_columns

3743 )

3744

3745 @util.ro_non_memoized_property

3746 def _all_selected_columns(self) -> _SelectIterable:

3747 return self.element._all_selected_columns

3748

3749 @util.ro_non_memoized_property

3750 def selected_columns(self) -> ColumnCollection[str, ColumnElement[Any]]:

3751 """A :class:`_expression.ColumnCollection`

3752 representing the columns that

3753 the embedded SELECT statement returns in its result set, not including

3754 :class:`_sql.TextClause` constructs.

3755

3756 .. versionadded:: 1.4

3757

3758 .. seealso::

3759

3760 :attr:`_sql.Select.selected_columns`

3761

3762 """

3763 return self.element.selected_columns

3764

3765 @util.ro_non_memoized_property

3766 def _from_objects(self) -> List[FromClause]:

3767 return self.element._from_objects

3768

3769

3770class GenerativeSelect(SelectBase, Generative):

3771 """Base class for SELECT statements where additional elements can be

3772 added.

3773

3774 This serves as the base for :class:`_expression.Select` and

3775 :class:`_expression.CompoundSelect`

3776 where elements such as ORDER BY, GROUP BY can be added and column

3777 rendering can be controlled. Compare to

3778 :class:`_expression.TextualSelect`, which,

3779 while it subclasses :class:`_expression.SelectBase`

3780 and is also a SELECT construct,

3781 represents a fixed textual string which cannot be altered at this level,

3782 only wrapped as a subquery.

3783

3784 """

3785

3786 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()

3787 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()

3788 _limit_clause: Optional[ColumnElement[Any]] = None

3789 _offset_clause: Optional[ColumnElement[Any]] = None

3790 _fetch_clause: Optional[ColumnElement[Any]] = None

3791 _fetch_clause_options: Optional[Dict[str, bool]] = None

3792 _for_update_arg: Optional[ForUpdateArg] = None

3793

3794 def __init__(self, _label_style: SelectLabelStyle = LABEL_STYLE_DEFAULT):

3795 self._label_style = _label_style

3796

3797 @_generative

3798 def with_for_update(

3799 self,

3800 *,

3801 nowait: bool = False,

3802 read: bool = False,

3803 of: Optional[_ForUpdateOfArgument] = None,

3804 skip_locked: bool = False,

3805 key_share: bool = False,

3806 ) -> Self:

3807 """Specify a ``FOR UPDATE`` clause for this

3808 :class:`_expression.GenerativeSelect`.

3809

3810 E.g.::

3811

3812 stmt = select(table).with_for_update(nowait=True)

3813

3814 On a database like PostgreSQL or Oracle, the above would render a

3815 statement like::

3816

3817 SELECT table.a, table.b FROM table FOR UPDATE NOWAIT

3818

3819 on other backends, the ``nowait`` option is ignored and instead

3820 would produce::

3821

3822 SELECT table.a, table.b FROM table FOR UPDATE

3823

3824 When called with no arguments, the statement will render with

3825 the suffix ``FOR UPDATE``. Additional arguments can then be

3826 provided which allow for common database-specific

3827 variants.

3828

3829 :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle

3830 and PostgreSQL dialects.

3831

3832 :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,

3833 ``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with

3834 ``nowait``, will render ``FOR SHARE NOWAIT``.

3835

3836 :param of: SQL expression or list of SQL expression elements,

3837 (typically :class:`_schema.Column` objects or a compatible expression,

3838 for some backends may also be a table expression) which will render

3839 into a ``FOR UPDATE OF`` clause; supported by PostgreSQL, Oracle, some

3840 MySQL versions and possibly others. May render as a table or as a

3841 column depending on backend.

3842

3843 :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``

3844 on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if

3845 ``read=True`` is also specified.

3846

3847 :param key_share: boolean, will render ``FOR NO KEY UPDATE``,

3848 or if combined with ``read=True`` will render ``FOR KEY SHARE``,

3849 on the PostgreSQL dialect.

3850

3851 """

3852 self._for_update_arg = ForUpdateArg(

3853 nowait=nowait,

3854 read=read,

3855 of=of,

3856 skip_locked=skip_locked,

3857 key_share=key_share,

3858 )

3859 return self

3860

3861 def get_label_style(self) -> SelectLabelStyle:

3862 """

3863 Retrieve the current label style.

3864

3865 .. versionadded:: 1.4

3866

3867 """

3868 return self._label_style

3869

3870 def set_label_style(self, style: SelectLabelStyle) -> Self:

3871 """Return a new selectable with the specified label style.

3872

3873 There are three "label styles" available,

3874 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`,

3875 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`, and

3876 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`. The default style is

3877 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

3878

3879 In modern SQLAlchemy, there is not generally a need to change the

3880 labeling style, as per-expression labels are more effectively used by

3881 making use of the :meth:`_sql.ColumnElement.label` method. In past

3882 versions, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` was used to

3883 disambiguate same-named columns from different tables, aliases, or

3884 subqueries; the newer :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` now

3885 applies labels only to names that conflict with an existing name so

3886 that the impact of this labeling is minimal.

3887

3888 The rationale for disambiguation is mostly so that all column

3889 expressions are available from a given :attr:`_sql.FromClause.c`

3890 collection when a subquery is created.

3891

3892 .. versionadded:: 1.4 - the

3893 :meth:`_sql.GenerativeSelect.set_label_style` method replaces the

3894 previous combination of ``.apply_labels()``, ``.with_labels()`` and

3895 ``use_labels=True`` methods and/or parameters.

3896

3897 .. seealso::

3898

3899 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

3900

3901 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

3902

3903 :data:`_sql.LABEL_STYLE_NONE`

3904

3905 :data:`_sql.LABEL_STYLE_DEFAULT`

3906

3907 """

3908 if self._label_style is not style:

3909 self = self._generate()

3910 self._label_style = style

3911 return self

3912

3913 @property

3914 def _group_by_clause(self) -> ClauseList:

3915 """ClauseList access to group_by_clauses for legacy dialects"""

3916 return ClauseList._construct_raw(

3917 operators.comma_op, self._group_by_clauses

3918 )

3919

3920 @property

3921 def _order_by_clause(self) -> ClauseList:

3922 """ClauseList access to order_by_clauses for legacy dialects"""

3923 return ClauseList._construct_raw(

3924 operators.comma_op, self._order_by_clauses

3925 )

3926

3927 def _offset_or_limit_clause(

3928 self,

3929 element: _LimitOffsetType,

3930 name: Optional[str] = None,

3931 type_: Optional[_TypeEngineArgument[int]] = None,

3932 ) -> ColumnElement[Any]:

3933 """Convert the given value to an "offset or limit" clause.

3934

3935 This handles incoming integers and converts to an expression; if

3936 an expression is already given, it is passed through.

3937

3938 """

3939 return coercions.expect(

3940 roles.LimitOffsetRole, element, name=name, type_=type_

3941 )

3942

3943 @overload

3944 def _offset_or_limit_clause_asint(

3945 self, clause: ColumnElement[Any], attrname: str

3946 ) -> NoReturn: ...

3947

3948 @overload

3949 def _offset_or_limit_clause_asint(

3950 self, clause: Optional[_OffsetLimitParam], attrname: str

3951 ) -> Optional[int]: ...

3952

3953 def _offset_or_limit_clause_asint(

3954 self, clause: Optional[ColumnElement[Any]], attrname: str

3955 ) -> Union[NoReturn, Optional[int]]:

3956 """Convert the "offset or limit" clause of a select construct to an

3957 integer.

3958

3959 This is only possible if the value is stored as a simple bound

3960 parameter. Otherwise, a compilation error is raised.

3961

3962 """

3963 if clause is None:

3964 return None

3965 try:

3966 value = clause._limit_offset_value

3967 except AttributeError as err:

3968 raise exc.CompileError(

3969 "This SELECT structure does not use a simple "

3970 "integer value for %s" % attrname

3971 ) from err

3972 else:

3973 return util.asint(value)

3974

3975 @property

3976 def _limit(self) -> Optional[int]:

3977 """Get an integer value for the limit. This should only be used

3978 by code that cannot support a limit as a BindParameter or

3979 other custom clause as it will throw an exception if the limit

3980 isn't currently set to an integer.

3981

3982 """

3983 return self._offset_or_limit_clause_asint(self._limit_clause, "limit")

3984

3985 def _simple_int_clause(self, clause: ClauseElement) -> bool:

3986 """True if the clause is a simple integer, False

3987 if it is not present or is a SQL expression.

3988 """

3989 return isinstance(clause, _OffsetLimitParam)

3990

3991 @property

3992 def _offset(self) -> Optional[int]:

3993 """Get an integer value for the offset. This should only be used

3994 by code that cannot support an offset as a BindParameter or

3995 other custom clause as it will throw an exception if the

3996 offset isn't currently set to an integer.

3997

3998 """

3999 return self._offset_or_limit_clause_asint(

4000 self._offset_clause, "offset"

4001 )

4002

4003 @property

4004 def _has_row_limiting_clause(self) -> bool:

4005 return (

4006 self._limit_clause is not None

4007 or self._offset_clause is not None

4008 or self._fetch_clause is not None

4009 )

4010

4011 @_generative

4012 def limit(self, limit: _LimitOffsetType) -> Self:

4013 """Return a new selectable with the given LIMIT criterion

4014 applied.

4015

4016 This is a numerical value which usually renders as a ``LIMIT``

4017 expression in the resulting select. Backends that don't

4018 support ``LIMIT`` will attempt to provide similar

4019 functionality.

4020

4021 .. note::

4022

4023 The :meth:`_sql.GenerativeSelect.limit` method will replace

4024 any clause applied with :meth:`_sql.GenerativeSelect.fetch`.

4025

4026 :param limit: an integer LIMIT parameter, or a SQL expression

4027 that provides an integer result. Pass ``None`` to reset it.

4028

4029 .. seealso::

4030

4031 :meth:`_sql.GenerativeSelect.fetch`

4032

4033 :meth:`_sql.GenerativeSelect.offset`

4034

4035 """

4036

4037 self._fetch_clause = self._fetch_clause_options = None

4038 self._limit_clause = self._offset_or_limit_clause(limit)

4039 return self

4040

4041 @_generative

4042 def fetch(

4043 self,

4044 count: _LimitOffsetType,

4045 with_ties: bool = False,

4046 percent: bool = False,

4047 ) -> Self:

4048 """Return a new selectable with the given FETCH FIRST criterion

4049 applied.

4050

4051 This is a numeric value which usually renders as

4052 ``FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES}``

4053 expression in the resulting select. This functionality is

4054 is currently implemented for Oracle, PostgreSQL, MSSQL.

4055

4056 Use :meth:`_sql.GenerativeSelect.offset` to specify the offset.

4057

4058 .. note::

4059

4060 The :meth:`_sql.GenerativeSelect.fetch` method will replace

4061 any clause applied with :meth:`_sql.GenerativeSelect.limit`.

4062

4063 .. versionadded:: 1.4

4064

4065 :param count: an integer COUNT parameter, or a SQL expression

4066 that provides an integer result. When ``percent=True`` this will

4067 represent the percentage of rows to return, not the absolute value.

4068 Pass ``None`` to reset it.

4069

4070 :param with_ties: When ``True``, the WITH TIES option is used

4071 to return any additional rows that tie for the last place in the

4072 result set according to the ``ORDER BY`` clause. The

4073 ``ORDER BY`` may be mandatory in this case. Defaults to ``False``

4074

4075 :param percent: When ``True``, ``count`` represents the percentage

4076 of the total number of selected rows to return. Defaults to ``False``

4077

4078 .. seealso::

4079

4080 :meth:`_sql.GenerativeSelect.limit`

4081

4082 :meth:`_sql.GenerativeSelect.offset`

4083

4084 """

4085

4086 self._limit_clause = None

4087 if count is None:

4088 self._fetch_clause = self._fetch_clause_options = None

4089 else:

4090 self._fetch_clause = self._offset_or_limit_clause(count)

4091 self._fetch_clause_options = {

4092 "with_ties": with_ties,

4093 "percent": percent,

4094 }

4095 return self

4096

4097 @_generative

4098 def offset(self, offset: _LimitOffsetType) -> Self:

4099 """Return a new selectable with the given OFFSET criterion

4100 applied.

4101

4102

4103 This is a numeric value which usually renders as an ``OFFSET``

4104 expression in the resulting select. Backends that don't

4105 support ``OFFSET`` will attempt to provide similar

4106 functionality.

4107

4108 :param offset: an integer OFFSET parameter, or a SQL expression

4109 that provides an integer result. Pass ``None`` to reset it.

4110

4111 .. seealso::

4112

4113 :meth:`_sql.GenerativeSelect.limit`

4114

4115 :meth:`_sql.GenerativeSelect.fetch`

4116

4117 """

4118

4119 self._offset_clause = self._offset_or_limit_clause(offset)

4120 return self

4121

4122 @_generative

4123 @util.preload_module("sqlalchemy.sql.util")

4124 def slice(

4125 self,

4126 start: int,

4127 stop: int,

4128 ) -> Self:

4129 """Apply LIMIT / OFFSET to this statement based on a slice.

4130

4131 The start and stop indices behave like the argument to Python's

4132 built-in :func:`range` function. This method provides an

4133 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the

4134 query.

4135

4136 For example, ::

4137

4138 stmt = select(User).order_by(User).id.slice(1, 3)

4139

4140 renders as

4141

4142 .. sourcecode:: sql

4143

4144 SELECT users.id AS users_id,

4145 users.name AS users_name

4146 FROM users ORDER BY users.id

4147 LIMIT ? OFFSET ?

4148 (2, 1)

4149

4150 .. note::

4151

4152 The :meth:`_sql.GenerativeSelect.slice` method will replace

4153 any clause applied with :meth:`_sql.GenerativeSelect.fetch`.

4154

4155 .. versionadded:: 1.4 Added the :meth:`_sql.GenerativeSelect.slice`

4156 method generalized from the ORM.

4157

4158 .. seealso::

4159

4160 :meth:`_sql.GenerativeSelect.limit`

4161

4162 :meth:`_sql.GenerativeSelect.offset`

4163

4164 :meth:`_sql.GenerativeSelect.fetch`

4165

4166 """

4167 sql_util = util.preloaded.sql_util

4168 self._fetch_clause = self._fetch_clause_options = None

4169 self._limit_clause, self._offset_clause = sql_util._make_slice(

4170 self._limit_clause, self._offset_clause, start, stop

4171 )

4172 return self

4173

4174 @_generative

4175 def order_by(

4176 self,

4177 __first: Union[

4178 Literal[None, _NoArg.NO_ARG],

4179 _ColumnExpressionOrStrLabelArgument[Any],

4180 ] = _NoArg.NO_ARG,

4181 /,

4182 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4183 ) -> Self:

4184 r"""Return a new selectable with the given list of ORDER BY

4185 criteria applied.

4186

4187 e.g.::

4188

4189 stmt = select(table).order_by(table.c.id, table.c.name)

4190

4191 Calling this method multiple times is equivalent to calling it once

4192 with all the clauses concatenated. All existing ORDER BY criteria may

4193 be cancelled by passing ``None`` by itself. New ORDER BY criteria may

4194 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::

4195

4196 # will erase all ORDER BY and ORDER BY new_col alone

4197 stmt = stmt.order_by(None).order_by(new_col)

4198

4199 :param \*clauses: a series of :class:`_expression.ColumnElement`

4200 constructs

4201 which will be used to generate an ORDER BY clause.

4202

4203 .. seealso::

4204

4205 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`

4206

4207 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

4208

4209 """

4210

4211 if not clauses and __first is None:

4212 self._order_by_clauses = ()

4213 elif __first is not _NoArg.NO_ARG:

4214 self._order_by_clauses += tuple(

4215 coercions.expect(

4216 roles.OrderByRole, clause, apply_propagate_attrs=self

4217 )

4218 for clause in (__first,) + clauses

4219 )

4220 return self

4221

4222 @_generative

4223 def group_by(

4224 self,

4225 __first: Union[

4226 Literal[None, _NoArg.NO_ARG],

4227 _ColumnExpressionOrStrLabelArgument[Any],

4228 ] = _NoArg.NO_ARG,

4229 /,

4230 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4231 ) -> Self:

4232 r"""Return a new selectable with the given list of GROUP BY

4233 criterion applied.

4234

4235 All existing GROUP BY settings can be suppressed by passing ``None``.

4236

4237 e.g.::

4238

4239 stmt = select(table.c.name, func.max(table.c.stat)).\

4240 group_by(table.c.name)

4241

4242 :param \*clauses: a series of :class:`_expression.ColumnElement`

4243 constructs

4244 which will be used to generate an GROUP BY clause.

4245

4246 .. seealso::

4247

4248 :ref:`tutorial_group_by_w_aggregates` - in the

4249 :ref:`unified_tutorial`

4250

4251 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

4252

4253 """

4254

4255 if not clauses and __first is None:

4256 self._group_by_clauses = ()

4257 elif __first is not _NoArg.NO_ARG:

4258 self._group_by_clauses += tuple(

4259 coercions.expect(

4260 roles.GroupByRole, clause, apply_propagate_attrs=self

4261 )

4262 for clause in (__first,) + clauses

4263 )

4264 return self

4265

4266

4267@CompileState.plugin_for("default", "compound_select")

4268class CompoundSelectState(CompileState):

4269 @util.memoized_property

4270 def _label_resolve_dict(

4271 self,

4272 ) -> Tuple[

4273 Dict[str, ColumnElement[Any]],

4274 Dict[str, ColumnElement[Any]],

4275 Dict[str, ColumnElement[Any]],

4276 ]:

4277 # TODO: this is hacky and slow

4278 hacky_subquery = self.statement.subquery()

4279 hacky_subquery.named_with_column = False

4280 d = {c.key: c for c in hacky_subquery.c}

4281 return d, d, d

4282

4283

4284class _CompoundSelectKeyword(Enum):

4285 UNION = "UNION"

4286 UNION_ALL = "UNION ALL"

4287 EXCEPT = "EXCEPT"

4288 EXCEPT_ALL = "EXCEPT ALL"

4289 INTERSECT = "INTERSECT"

4290 INTERSECT_ALL = "INTERSECT ALL"

4291

4292

4293class CompoundSelect(HasCompileState, GenerativeSelect, ExecutableReturnsRows):

4294 """Forms the basis of ``UNION``, ``UNION ALL``, and other

4295 SELECT-based set operations.

4296

4297

4298 .. seealso::

4299

4300 :func:`_expression.union`

4301

4302 :func:`_expression.union_all`

4303

4304 :func:`_expression.intersect`

4305

4306 :func:`_expression.intersect_all`

4307

4308 :func:`_expression.except`

4309

4310 :func:`_expression.except_all`

4311

4312 """

4313

4314 __visit_name__ = "compound_select"

4315

4316 _traverse_internals: _TraverseInternalsType = [

4317 ("selects", InternalTraversal.dp_clauseelement_list),

4318 ("_limit_clause", InternalTraversal.dp_clauseelement),

4319 ("_offset_clause", InternalTraversal.dp_clauseelement),

4320 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4321 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4322 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4323 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4324 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4325 ("keyword", InternalTraversal.dp_string),

4326 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

4327

4328 selects: List[SelectBase]

4329

4330 _is_from_container = True

4331 _auto_correlate = False

4332

4333 def __init__(

4334 self,

4335 keyword: _CompoundSelectKeyword,

4336 *selects: _SelectStatementForCompoundArgument,

4337 ):

4338 self.keyword = keyword

4339 self.selects = [

4340 coercions.expect(

4341 roles.CompoundElementRole, s, apply_propagate_attrs=self

4342 ).self_group(against=self)

4343 for s in selects

4344 ]

4345

4346 GenerativeSelect.__init__(self)

4347

4348 @classmethod

4349 def _create_union(

4350 cls, *selects: _SelectStatementForCompoundArgument

4351 ) -> CompoundSelect:

4352 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4353

4354 @classmethod

4355 def _create_union_all(

4356 cls, *selects: _SelectStatementForCompoundArgument

4357 ) -> CompoundSelect:

4358 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4359

4360 @classmethod

4361 def _create_except(

4362 cls, *selects: _SelectStatementForCompoundArgument

4363 ) -> CompoundSelect:

4364 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4365

4366 @classmethod

4367 def _create_except_all(

4368 cls, *selects: _SelectStatementForCompoundArgument

4369 ) -> CompoundSelect:

4370 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4371

4372 @classmethod

4373 def _create_intersect(

4374 cls, *selects: _SelectStatementForCompoundArgument

4375 ) -> CompoundSelect:

4376 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4377

4378 @classmethod

4379 def _create_intersect_all(

4380 cls, *selects: _SelectStatementForCompoundArgument

4381 ) -> CompoundSelect:

4382 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4383

4384 def _scalar_type(self) -> TypeEngine[Any]:

4385 return self.selects[0]._scalar_type()

4386

4387 def self_group(

4388 self, against: Optional[OperatorType] = None

4389 ) -> GroupedElement:

4390 return SelectStatementGrouping(self)

4391

4392 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

4393 for s in self.selects:

4394 if s.is_derived_from(fromclause):

4395 return True

4396 return False

4397

4398 def set_label_style(self, style: SelectLabelStyle) -> CompoundSelect:

4399 if self._label_style is not style:

4400 self = self._generate()

4401 select_0 = self.selects[0].set_label_style(style)

4402 self.selects = [select_0] + self.selects[1:]

4403

4404 return self

4405

4406 def _ensure_disambiguated_names(self) -> CompoundSelect:

4407 new_select = self.selects[0]._ensure_disambiguated_names()

4408 if new_select is not self.selects[0]:

4409 self = self._generate()

4410 self.selects = [new_select] + self.selects[1:]

4411

4412 return self

4413

4414 def _generate_fromclause_column_proxies(

4415 self,

4416 subquery: FromClause,

4417 *,

4418 proxy_compound_columns: Optional[

4419 Iterable[Sequence[ColumnElement[Any]]]

4420 ] = None,

4421 ) -> None:

4422 # this is a slightly hacky thing - the union exports a

4423 # column that resembles just that of the *first* selectable.

4424 # to get at a "composite" column, particularly foreign keys,

4425 # you have to dig through the proxies collection which we

4426 # generate below.

4427 select_0 = self.selects[0]

4428

4429 if self._label_style is not LABEL_STYLE_DEFAULT:

4430 select_0 = select_0.set_label_style(self._label_style)

4431

4432 # hand-construct the "_proxies" collection to include all

4433 # derived columns place a 'weight' annotation corresponding

4434 # to how low in the list of select()s the column occurs, so

4435 # that the corresponding_column() operation can resolve

4436 # conflicts

4437 extra_col_iterator = zip(

4438 *[

4439 [

4440 c._annotate(dd)

4441 for c in stmt._all_selected_columns

4442 if is_column_element(c)

4443 ]

4444 for dd, stmt in [

4445 ({"weight": i + 1}, stmt)

4446 for i, stmt in enumerate(self.selects)

4447 ]

4448 ]

4449 )

4450

4451 # the incoming proxy_compound_columns can be present also if this is

4452 # a compound embedded in a compound. it's probably more appropriate

4453 # that we generate new weights local to this nested compound, though

4454 # i haven't tried to think what it means for compound nested in

4455 # compound

4456 select_0._generate_fromclause_column_proxies(

4457 subquery, proxy_compound_columns=extra_col_iterator

4458 )

4459

4460 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

4461 super()._refresh_for_new_column(column)

4462 for select in self.selects:

4463 select._refresh_for_new_column(column)

4464

4465 @util.ro_non_memoized_property

4466 def _all_selected_columns(self) -> _SelectIterable:

4467 return self.selects[0]._all_selected_columns

4468

4469 @util.ro_non_memoized_property

4470 def selected_columns(

4471 self,

4472 ) -> ColumnCollection[str, ColumnElement[Any]]:

4473 """A :class:`_expression.ColumnCollection`

4474 representing the columns that

4475 this SELECT statement or similar construct returns in its result set,

4476 not including :class:`_sql.TextClause` constructs.

4477

4478 For a :class:`_expression.CompoundSelect`, the

4479 :attr:`_expression.CompoundSelect.selected_columns`

4480 attribute returns the selected

4481 columns of the first SELECT statement contained within the series of

4482 statements within the set operation.

4483

4484 .. seealso::

4485

4486 :attr:`_sql.Select.selected_columns`

4487

4488 .. versionadded:: 1.4

4489

4490 """

4491 return self.selects[0].selected_columns

4492

4493

4494# backwards compat

4495for elem in _CompoundSelectKeyword:

4496 setattr(CompoundSelect, elem.name, elem)

4497

4498

4499@CompileState.plugin_for("default", "select")

4500class SelectState(util.MemoizedSlots, CompileState):

4501 __slots__ = (

4502 "from_clauses",

4503 "froms",

4504 "columns_plus_names",

4505 "_label_resolve_dict",

4506 )

4507

4508 if TYPE_CHECKING:

4509 default_select_compile_options: CacheableOptions

4510 else:

4511

4512 class default_select_compile_options(CacheableOptions):

4513 _cache_key_traversal = []

4514

4515 if TYPE_CHECKING:

4516

4517 @classmethod

4518 def get_plugin_class(

4519 cls, statement: Executable

4520 ) -> Type[SelectState]: ...

4521

4522 def __init__(

4523 self,

4524 statement: Select[Unpack[TupleAny]],

4525 compiler: Optional[SQLCompiler],

4526 **kw: Any,

4527 ):

4528 self.statement = statement

4529 self.from_clauses = statement._from_obj

4530

4531 for memoized_entities in statement._memoized_select_entities:

4532 self._setup_joins(

4533 memoized_entities._setup_joins, memoized_entities._raw_columns

4534 )

4535

4536 if statement._setup_joins:

4537 self._setup_joins(statement._setup_joins, statement._raw_columns)

4538

4539 self.froms = self._get_froms(statement)

4540

4541 self.columns_plus_names = statement._generate_columns_plus_names(True)

4542

4543 @classmethod

4544 def _plugin_not_implemented(cls) -> NoReturn:

4545 raise NotImplementedError(

4546 "The default SELECT construct without plugins does not "

4547 "implement this method."

4548 )

4549

4550 @classmethod

4551 def get_column_descriptions(

4552 cls, statement: Select[Unpack[TupleAny]]

4553 ) -> List[Dict[str, Any]]:

4554 return [

4555 {

4556 "name": name,

4557 "type": element.type,

4558 "expr": element,

4559 }

4560 for _, name, _, element, _ in (

4561 statement._generate_columns_plus_names(False)

4562 )

4563 ]

4564

4565 @classmethod

4566 def from_statement(

4567 cls,

4568 statement: Select[Unpack[TupleAny]],

4569 from_statement: roles.ReturnsRowsRole,

4570 ) -> ExecutableReturnsRows:

4571 cls._plugin_not_implemented()

4572

4573 @classmethod

4574 def get_columns_clause_froms(

4575 cls, statement: Select[Unpack[TupleAny]]

4576 ) -> List[FromClause]:

4577 return cls._normalize_froms(

4578 itertools.chain.from_iterable(

4579 element._from_objects for element in statement._raw_columns

4580 )

4581 )

4582

4583 @classmethod

4584 def _column_naming_convention(

4585 cls, label_style: SelectLabelStyle

4586 ) -> _LabelConventionCallable:

4587 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4588

4589 dedupe = label_style is not LABEL_STYLE_NONE

4590

4591 pa = prefix_anon_map()

4592 names = set()

4593

4594 def go(

4595 c: Union[ColumnElement[Any], TextClause],

4596 col_name: Optional[str] = None,

4597 ) -> Optional[str]:

4598 if is_text_clause(c):

4599 return None

4600 elif TYPE_CHECKING:

4601 assert is_column_element(c)

4602

4603 if not dedupe:

4604 name = c._proxy_key

4605 if name is None:

4606 name = "_no_label"

4607 return name

4608

4609 name = c._tq_key_label if table_qualified else c._proxy_key

4610

4611 if name is None:

4612 name = "_no_label"

4613 if name in names:

4614 return c._anon_label(name) % pa

4615 else:

4616 names.add(name)

4617 return name

4618

4619 elif name in names:

4620 return (

4621 c._anon_tq_key_label % pa

4622 if table_qualified

4623 else c._anon_key_label % pa

4624 )

4625 else:

4626 names.add(name)

4627 return name

4628

4629 return go

4630

4631 def _get_froms(

4632 self, statement: Select[Unpack[TupleAny]]

4633 ) -> List[FromClause]:

4634 ambiguous_table_name_map: _AmbiguousTableNameMap

4635 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4636

4637 return self._normalize_froms(

4638 itertools.chain(

4639 self.from_clauses,

4640 itertools.chain.from_iterable(

4641 [

4642 element._from_objects

4643 for element in statement._raw_columns

4644 ]

4645 ),

4646 itertools.chain.from_iterable(

4647 [

4648 element._from_objects

4649 for element in statement._where_criteria

4650 ]

4651 ),

4652 ),

4653 check_statement=statement,

4654 ambiguous_table_name_map=ambiguous_table_name_map,

4655 )

4656

4657 @classmethod

4658 def _normalize_froms(

4659 cls,

4660 iterable_of_froms: Iterable[FromClause],

4661 check_statement: Optional[Select[Unpack[TupleAny]]] = None,

4662 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4663 ) -> List[FromClause]:

4664 """given an iterable of things to select FROM, reduce them to what

4665 would actually render in the FROM clause of a SELECT.

4666

4667 This does the job of checking for JOINs, tables, etc. that are in fact

4668 overlapping due to cloning, adaption, present in overlapping joins,

4669 etc.

4670

4671 """

4672 seen: Set[FromClause] = set()

4673 froms: List[FromClause] = []

4674

4675 for item in iterable_of_froms:

4676 if is_subquery(item) and item.element is check_statement:

4677 raise exc.InvalidRequestError(

4678 "select() construct refers to itself as a FROM"

4679 )

4680

4681 if not seen.intersection(item._cloned_set):

4682 froms.append(item)

4683 seen.update(item._cloned_set)

4684

4685 if froms:

4686 toremove = set(

4687 itertools.chain.from_iterable(

4688 [_expand_cloned(f._hide_froms) for f in froms]

4689 )

4690 )

4691 if toremove:

4692 # filter out to FROM clauses not in the list,

4693 # using a list to maintain ordering

4694 froms = [f for f in froms if f not in toremove]

4695

4696 if ambiguous_table_name_map is not None:

4697 ambiguous_table_name_map.update(

4698 (

4699 fr.name,

4700 _anonymous_label.safe_construct(

4701 hash(fr.name), fr.name

4702 ),

4703 )

4704 for item in froms

4705 for fr in item._from_objects

4706 if is_table(fr)

4707 and fr.schema

4708 and fr.name not in ambiguous_table_name_map

4709 )

4710

4711 return froms

4712

4713 def _get_display_froms(

4714 self,

4715 explicit_correlate_froms: Optional[Sequence[FromClause]] = None,

4716 implicit_correlate_froms: Optional[Sequence[FromClause]] = None,

4717 ) -> List[FromClause]:

4718 """Return the full list of 'from' clauses to be displayed.

4719

4720 Takes into account a set of existing froms which may be

4721 rendered in the FROM clause of enclosing selects; this Select

4722 may want to leave those absent if it is automatically

4723 correlating.

4724

4725 """

4726

4727 froms = self.froms

4728

4729 if self.statement._correlate:

4730 to_correlate = self.statement._correlate

4731 if to_correlate:

4732 froms = [

4733 f

4734 for f in froms

4735 if f

4736 not in _cloned_intersection(

4737 _cloned_intersection(

4738 froms, explicit_correlate_froms or ()

4739 ),

4740 to_correlate,

4741 )

4742 ]

4743

4744 if self.statement._correlate_except is not None:

4745 froms = [

4746 f

4747 for f in froms

4748 if f

4749 not in _cloned_difference(

4750 _cloned_intersection(

4751 froms, explicit_correlate_froms or ()

4752 ),

4753 self.statement._correlate_except,

4754 )

4755 ]

4756

4757 if (

4758 self.statement._auto_correlate

4759 and implicit_correlate_froms

4760 and len(froms) > 1

4761 ):

4762 froms = [

4763 f

4764 for f in froms

4765 if f

4766 not in _cloned_intersection(froms, implicit_correlate_froms)

4767 ]

4768

4769 if not len(froms):

4770 raise exc.InvalidRequestError(

4771 "Select statement '%r"

4772 "' returned no FROM clauses "

4773 "due to auto-correlation; "

4774 "specify correlate(<tables>) "

4775 "to control correlation "

4776 "manually." % self.statement

4777 )

4778

4779 return froms

4780

4781 def _memoized_attr__label_resolve_dict(

4782 self,

4783 ) -> Tuple[

4784 Dict[str, ColumnElement[Any]],

4785 Dict[str, ColumnElement[Any]],

4786 Dict[str, ColumnElement[Any]],

4787 ]:

4788 with_cols: Dict[str, ColumnElement[Any]] = {

4789 c._tq_label or c.key: c

4790 for c in self.statement._all_selected_columns

4791 if c._allow_label_resolve

4792 }

4793 only_froms: Dict[str, ColumnElement[Any]] = {

4794 c.key: c # type: ignore

4795 for c in _select_iterables(self.froms)

4796 if c._allow_label_resolve

4797 }

4798 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

4799 for key, value in only_froms.items():

4800 with_cols.setdefault(key, value)

4801

4802 return with_cols, only_froms, only_cols

4803

4804 @classmethod

4805 def determine_last_joined_entity(

4806 cls, stmt: Select[Unpack[TupleAny]]

4807 ) -> Optional[_JoinTargetElement]:

4808 if stmt._setup_joins:

4809 return stmt._setup_joins[-1][0]

4810 else:

4811 return None

4812

4813 @classmethod

4814 def all_selected_columns(

4815 cls, statement: Select[Unpack[TupleAny]]

4816 ) -> _SelectIterable:

4817 return [c for c in _select_iterables(statement._raw_columns)]

4818

4819 def _setup_joins(

4820 self,

4821 args: Tuple[_SetupJoinsElement, ...],

4822 raw_columns: List[_ColumnsClauseElement],

4823 ) -> None:

4824 for right, onclause, left, flags in args:

4825 if TYPE_CHECKING:

4826 if onclause is not None:

4827 assert isinstance(onclause, ColumnElement)

4828

4829 isouter = flags["isouter"]

4830 full = flags["full"]

4831

4832 if left is None:

4833 (

4834 left,

4835 replace_from_obj_index,

4836 ) = self._join_determine_implicit_left_side(

4837 raw_columns, left, right, onclause

4838 )

4839 else:

4840 (replace_from_obj_index) = self._join_place_explicit_left_side(

4841 left

4842 )

4843

4844 # these assertions can be made here, as if the right/onclause

4845 # contained ORM elements, the select() statement would have been

4846 # upgraded to an ORM select, and this method would not be called;

4847 # orm.context.ORMSelectCompileState._join() would be

4848 # used instead.

4849 if TYPE_CHECKING:

4850 assert isinstance(right, FromClause)

4851 if onclause is not None:

4852 assert isinstance(onclause, ColumnElement)

4853

4854 if replace_from_obj_index is not None:

4855 # splice into an existing element in the

4856 # self._from_obj list

4857 left_clause = self.from_clauses[replace_from_obj_index]

4858

4859 self.from_clauses = (

4860 self.from_clauses[:replace_from_obj_index]

4861 + (

4862 Join(

4863 left_clause,

4864 right,

4865 onclause,

4866 isouter=isouter,

4867 full=full,

4868 ),

4869 )

4870 + self.from_clauses[replace_from_obj_index + 1 :]

4871 )

4872 else:

4873 assert left is not None

4874 self.from_clauses = self.from_clauses + (

4875 Join(left, right, onclause, isouter=isouter, full=full),

4876 )

4877

4878 @util.preload_module("sqlalchemy.sql.util")

4879 def _join_determine_implicit_left_side(

4880 self,

4881 raw_columns: List[_ColumnsClauseElement],

4882 left: Optional[FromClause],

4883 right: _JoinTargetElement,

4884 onclause: Optional[ColumnElement[Any]],

4885 ) -> Tuple[Optional[FromClause], Optional[int]]:

4886 """When join conditions don't express the left side explicitly,

4887 determine if an existing FROM or entity in this query

4888 can serve as the left hand side.

4889

4890 """

4891

4892 sql_util = util.preloaded.sql_util

4893

4894 replace_from_obj_index: Optional[int] = None

4895

4896 from_clauses = self.from_clauses

4897

4898 if from_clauses:

4899 indexes: List[int] = sql_util.find_left_clause_to_join_from(

4900 from_clauses, right, onclause

4901 )

4902

4903 if len(indexes) == 1:

4904 replace_from_obj_index = indexes[0]

4905 left = from_clauses[replace_from_obj_index]

4906 else:

4907 potential = {}

4908 statement = self.statement

4909

4910 for from_clause in itertools.chain(

4911 itertools.chain.from_iterable(

4912 [element._from_objects for element in raw_columns]

4913 ),

4914 itertools.chain.from_iterable(

4915 [

4916 element._from_objects

4917 for element in statement._where_criteria

4918 ]

4919 ),

4920 ):

4921 potential[from_clause] = ()

4922

4923 all_clauses = list(potential.keys())

4924 indexes = sql_util.find_left_clause_to_join_from(

4925 all_clauses, right, onclause

4926 )

4927

4928 if len(indexes) == 1:

4929 left = all_clauses[indexes[0]]

4930

4931 if len(indexes) > 1:

4932 raise exc.InvalidRequestError(

4933 "Can't determine which FROM clause to join "

4934 "from, there are multiple FROMS which can "

4935 "join to this entity. Please use the .select_from() "

4936 "method to establish an explicit left side, as well as "

4937 "providing an explicit ON clause if not present already to "

4938 "help resolve the ambiguity."

4939 )

4940 elif not indexes:

4941 raise exc.InvalidRequestError(

4942 "Don't know how to join to %r. "

4943 "Please use the .select_from() "

4944 "method to establish an explicit left side, as well as "

4945 "providing an explicit ON clause if not present already to "

4946 "help resolve the ambiguity." % (right,)

4947 )

4948 return left, replace_from_obj_index

4949

4950 @util.preload_module("sqlalchemy.sql.util")

4951 def _join_place_explicit_left_side(

4952 self, left: FromClause

4953 ) -> Optional[int]:

4954 replace_from_obj_index: Optional[int] = None

4955

4956 sql_util = util.preloaded.sql_util

4957

4958 from_clauses = list(self.statement._iterate_from_elements())

4959

4960 if from_clauses:

4961 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

4962 self.from_clauses, left

4963 )

4964 else:

4965 indexes = []

4966

4967 if len(indexes) > 1:

4968 raise exc.InvalidRequestError(

4969 "Can't identify which entity in which to assign the "

4970 "left side of this join. Please use a more specific "

4971 "ON clause."

4972 )

4973

4974 # have an index, means the left side is already present in

4975 # an existing FROM in the self._from_obj tuple

4976 if indexes:

4977 replace_from_obj_index = indexes[0]

4978

4979 # no index, means we need to add a new element to the

4980 # self._from_obj tuple

4981

4982 return replace_from_obj_index

4983

4984

4985class _SelectFromElements:

4986 __slots__ = ()

4987

4988 _raw_columns: List[_ColumnsClauseElement]

4989 _where_criteria: Tuple[ColumnElement[Any], ...]

4990 _from_obj: Tuple[FromClause, ...]

4991

4992 def _iterate_from_elements(self) -> Iterator[FromClause]:

4993 # note this does not include elements

4994 # in _setup_joins

4995

4996 seen = set()

4997 for element in self._raw_columns:

4998 for fr in element._from_objects:

4999 if fr in seen:

5000 continue

5001 seen.add(fr)

5002 yield fr

5003 for element in self._where_criteria:

5004 for fr in element._from_objects:

5005 if fr in seen:

5006 continue

5007 seen.add(fr)

5008 yield fr

5009 for element in self._from_obj:

5010 if element in seen:

5011 continue

5012 seen.add(element)

5013 yield element

5014

5015

5016class _MemoizedSelectEntities(

5017 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5018):

5019 """represents partial state from a Select object, for the case

5020 where Select.columns() has redefined the set of columns/entities the

5021 statement will be SELECTing from. This object represents

5022 the entities from the SELECT before that transformation was applied,

5023 so that transformations that were made in terms of the SELECT at that

5024 time, such as join() as well as options(), can access the correct context.

5025

5026 In previous SQLAlchemy versions, this wasn't needed because these

5027 constructs calculated everything up front, like when you called join()

5028 or options(), it did everything to figure out how that would translate

5029 into specific SQL constructs that would be ready to send directly to the

5030 SQL compiler when needed. But as of

5031 1.4, all of that stuff is done in the compilation phase, during the

5032 "compile state" portion of the process, so that the work can all be

5033 cached. So it needs to be able to resolve joins/options2 based on what

5034 the list of entities was when those methods were called.

5035

5036

5037 """

5038

5039 __visit_name__ = "memoized_select_entities"

5040

5041 _traverse_internals: _TraverseInternalsType = [

5042 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5043 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5044 ("_with_options", InternalTraversal.dp_executable_options),

5045 ]

5046

5047 _is_clone_of: Optional[ClauseElement]

5048 _raw_columns: List[_ColumnsClauseElement]

5049 _setup_joins: Tuple[_SetupJoinsElement, ...]

5050 _with_options: Tuple[ExecutableOption, ...]

5051

5052 _annotations = util.EMPTY_DICT

5053

5054 def _clone(self, **kw: Any) -> Self:

5055 c = self.__class__.__new__(self.__class__)

5056 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5057

5058 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5059 return c

5060

5061 @classmethod

5062 def _generate_for_statement(

5063 cls, select_stmt: Select[Unpack[TupleAny]]

5064 ) -> None:

5065 if select_stmt._setup_joins or select_stmt._with_options:

5066 self = _MemoizedSelectEntities()

5067 self._raw_columns = select_stmt._raw_columns

5068 self._setup_joins = select_stmt._setup_joins

5069 self._with_options = select_stmt._with_options

5070

5071 select_stmt._memoized_select_entities += (self,)

5072 select_stmt._raw_columns = []

5073 select_stmt._setup_joins = select_stmt._with_options = ()

5074

5075

5076class Select(

5077 HasPrefixes,

5078 HasSuffixes,

5079 HasHints,

5080 HasCompileState,

5081 _SelectFromElements,

5082 GenerativeSelect,

5083 TypedReturnsRows[Unpack[_Ts]],

5084):

5085 """Represents a ``SELECT`` statement.

5086

5087 The :class:`_sql.Select` object is normally constructed using the

5088 :func:`_sql.select` function. See that function for details.

5089

5090 .. seealso::

5091

5092 :func:`_sql.select`

5093

5094 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5095

5096 """

5097

5098 __visit_name__ = "select"

5099

5100 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5101 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5102

5103 _raw_columns: List[_ColumnsClauseElement]

5104

5105 _distinct: bool = False

5106 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5107 _correlate: Tuple[FromClause, ...] = ()

5108 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5109 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5110 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5111 _from_obj: Tuple[FromClause, ...] = ()

5112 _auto_correlate = True

5113 _is_select_statement = True

5114 _compile_options: CacheableOptions = (

5115 SelectState.default_select_compile_options

5116 )

5117

5118 _traverse_internals: _TraverseInternalsType = (

5119 [

5120 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5121 (

5122 "_memoized_select_entities",

5123 InternalTraversal.dp_memoized_select_entities,

5124 ),

5125 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5126 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5127 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5128 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5129 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5130 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5131 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5132 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5133 ("_limit_clause", InternalTraversal.dp_clauseelement),

5134 ("_offset_clause", InternalTraversal.dp_clauseelement),

5135 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5136 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5137 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5138 ("_distinct", InternalTraversal.dp_boolean),

5139 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5140 ("_label_style", InternalTraversal.dp_plain_obj),

5141 ]

5142 + HasCTE._has_ctes_traverse_internals

5143 + HasPrefixes._has_prefixes_traverse_internals

5144 + HasSuffixes._has_suffixes_traverse_internals

5145 + HasHints._has_hints_traverse_internals

5146 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5147 + Executable._executable_traverse_internals

5148 )

5149

5150 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5151 ("_compile_options", InternalTraversal.dp_has_cache_key)

5152 ]

5153

5154 _compile_state_factory: Type[SelectState]

5155

5156 @classmethod

5157 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5158 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5159

5160 Used internally to build up :class:`.Select` constructs with

5161 pre-established state.

5162

5163 """

5164

5165 stmt = Select.__new__(Select)

5166 stmt.__dict__.update(kw)

5167 return stmt

5168

5169 def __init__(self, *entities: _ColumnsClauseArgument[Any]):

5170 r"""Construct a new :class:`_expression.Select`.

5171

5172 The public constructor for :class:`_expression.Select` is the

5173 :func:`_sql.select` function.

5174

5175 """

5176 self._raw_columns = [

5177 coercions.expect(

5178 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5179 )

5180 for ent in entities

5181 ]

5182

5183 GenerativeSelect.__init__(self)

5184

5185 def _scalar_type(self) -> TypeEngine[Any]:

5186 if not self._raw_columns:

5187 return NULLTYPE

5188 elem = self._raw_columns[0]

5189 cols = list(elem._select_iterable)

5190 return cols[0].type

5191

5192 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5193 """A synonym for the :meth:`_sql.Select.where` method."""

5194

5195 return self.where(*criteria)

5196

5197 def _filter_by_zero(

5198 self,

5199 ) -> Union[

5200 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5201 ]:

5202 if self._setup_joins:

5203 meth = SelectState.get_plugin_class(

5204 self

5205 ).determine_last_joined_entity

5206 _last_joined_entity = meth(self)

5207 if _last_joined_entity is not None:

5208 return _last_joined_entity

5209

5210 if self._from_obj:

5211 return self._from_obj[0]

5212

5213 return self._raw_columns[0]

5214

5215 if TYPE_CHECKING:

5216

5217 @overload

5218 def scalar_subquery(

5219 self: Select[_MAYBE_ENTITY],

5220 ) -> ScalarSelect[Any]: ...

5221

5222 @overload

5223 def scalar_subquery(

5224 self: Select[_NOT_ENTITY],

5225 ) -> ScalarSelect[_NOT_ENTITY]: ...

5226

5227 @overload

5228 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5229

5230 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5231

5232 def filter_by(self, **kwargs: Any) -> Self:

5233 r"""apply the given filtering criterion as a WHERE clause

5234 to this select.

5235

5236 """

5237 from_entity = self._filter_by_zero()

5238

5239 clauses = [

5240 _entity_namespace_key(from_entity, key) == value

5241 for key, value in kwargs.items()

5242 ]

5243 return self.filter(*clauses)

5244

5245 @property

5246 def column_descriptions(self) -> Any:

5247 """Return a :term:`plugin-enabled` 'column descriptions' structure

5248 referring to the columns which are SELECTed by this statement.

5249

5250 This attribute is generally useful when using the ORM, as an

5251 extended structure which includes information about mapped

5252 entities is returned. The section :ref:`queryguide_inspection`

5253 contains more background.

5254

5255 For a Core-only statement, the structure returned by this accessor

5256 is derived from the same objects that are returned by the

5257 :attr:`.Select.selected_columns` accessor, formatted as a list of

5258 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5259 which indicate the column expressions to be selected::

5260

5261 >>> stmt = select(user_table)

5262 >>> stmt.column_descriptions

5263 [

5264 {

5265 'name': 'id',

5266 'type': Integer(),

5267 'expr': Column('id', Integer(), ...)},

5268 {

5269 'name': 'name',

5270 'type': String(length=30),

5271 'expr': Column('name', String(length=30), ...)}

5272 ]

5273

5274 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5275 attribute returns a structure for a Core-only set of entities,

5276 not just ORM-only entities.

5277

5278 .. seealso::

5279

5280 :attr:`.UpdateBase.entity_description` - entity information for

5281 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5282

5283 :ref:`queryguide_inspection` - ORM background

5284

5285 """

5286 meth = SelectState.get_plugin_class(self).get_column_descriptions

5287 return meth(self)

5288

5289 def from_statement(

5290 self, statement: roles.ReturnsRowsRole

5291 ) -> ExecutableReturnsRows:

5292 """Apply the columns which this :class:`.Select` would select

5293 onto another statement.

5294

5295 This operation is :term:`plugin-specific` and will raise a not

5296 supported exception if this :class:`_sql.Select` does not select from

5297 plugin-enabled entities.

5298

5299

5300 The statement is typically either a :func:`_expression.text` or

5301 :func:`_expression.select` construct, and should return the set of

5302 columns appropriate to the entities represented by this

5303 :class:`.Select`.

5304

5305 .. seealso::

5306

5307 :ref:`orm_queryguide_selecting_text` - usage examples in the

5308 ORM Querying Guide

5309

5310 """

5311 meth = SelectState.get_plugin_class(self).from_statement

5312 return meth(self, statement)

5313

5314 @_generative

5315 def join(

5316 self,

5317 target: _JoinTargetArgument,

5318 onclause: Optional[_OnClauseArgument] = None,

5319 *,

5320 isouter: bool = False,

5321 full: bool = False,

5322 ) -> Self:

5323 r"""Create a SQL JOIN against this :class:`_expression.Select`

5324 object's criterion

5325 and apply generatively, returning the newly resulting

5326 :class:`_expression.Select`.

5327

5328 E.g.::

5329

5330 stmt = select(user_table).join(address_table, user_table.c.id == address_table.c.user_id)

5331

5332 The above statement generates SQL similar to::

5333

5334 SELECT user.id, user.name FROM user JOIN address ON user.id = address.user_id

5335

5336 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5337 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5338 source that is within the FROM clause of the existing SELECT,

5339 and a given target :class:`_sql.FromClause`, and then adds

5340 this :class:`_sql.Join` to the FROM clause of the newly generated

5341 SELECT statement. This is completely reworked from the behavior

5342 in 1.3, which would instead create a subquery of the entire

5343 :class:`_expression.Select` and then join that subquery to the

5344 target.

5345

5346 This is a **backwards incompatible change** as the previous behavior

5347 was mostly useless, producing an unnamed subquery rejected by

5348 most databases in any case. The new behavior is modeled after

5349 that of the very successful :meth:`_orm.Query.join` method in the

5350 ORM, in order to support the functionality of :class:`_orm.Query`

5351 being available by using a :class:`_sql.Select` object with an

5352 :class:`_orm.Session`.

5353

5354 See the notes for this change at :ref:`change_select_join`.

5355

5356

5357 :param target: target table to join towards

5358

5359 :param onclause: ON clause of the join. If omitted, an ON clause

5360 is generated automatically based on the :class:`_schema.ForeignKey`

5361 linkages between the two tables, if one can be unambiguously

5362 determined, otherwise an error is raised.

5363

5364 :param isouter: if True, generate LEFT OUTER join. Same as

5365 :meth:`_expression.Select.outerjoin`.

5366

5367 :param full: if True, generate FULL OUTER join.

5368

5369 .. seealso::

5370

5371 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5372

5373 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5374

5375 :meth:`_expression.Select.join_from`

5376

5377 :meth:`_expression.Select.outerjoin`

5378

5379 """ # noqa: E501

5380 join_target = coercions.expect(

5381 roles.JoinTargetRole, target, apply_propagate_attrs=self

5382 )

5383 if onclause is not None:

5384 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5385 else:

5386 onclause_element = None

5387

5388 self._setup_joins += (

5389 (

5390 join_target,

5391 onclause_element,

5392 None,

5393 {"isouter": isouter, "full": full},

5394 ),

5395 )

5396 return self

5397

5398 def outerjoin_from(

5399 self,

5400 from_: _FromClauseArgument,

5401 target: _JoinTargetArgument,

5402 onclause: Optional[_OnClauseArgument] = None,

5403 *,

5404 full: bool = False,

5405 ) -> Self:

5406 r"""Create a SQL LEFT OUTER JOIN against this

5407 :class:`_expression.Select` object's criterion and apply generatively,

5408 returning the newly resulting :class:`_expression.Select`.

5409

5410 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5411

5412 """

5413 return self.join_from(

5414 from_, target, onclause=onclause, isouter=True, full=full

5415 )

5416

5417 @_generative

5418 def join_from(

5419 self,

5420 from_: _FromClauseArgument,

5421 target: _JoinTargetArgument,

5422 onclause: Optional[_OnClauseArgument] = None,

5423 *,

5424 isouter: bool = False,

5425 full: bool = False,

5426 ) -> Self:

5427 r"""Create a SQL JOIN against this :class:`_expression.Select`

5428 object's criterion

5429 and apply generatively, returning the newly resulting

5430 :class:`_expression.Select`.

5431

5432 E.g.::

5433

5434 stmt = select(user_table, address_table).join_from(

5435 user_table, address_table, user_table.c.id == address_table.c.user_id

5436 )

5437

5438 The above statement generates SQL similar to::

5439

5440 SELECT user.id, user.name, address.id, address.email, address.user_id

5441 FROM user JOIN address ON user.id = address.user_id

5442

5443 .. versionadded:: 1.4

5444

5445 :param from\_: the left side of the join, will be rendered in the

5446 FROM clause and is roughly equivalent to using the

5447 :meth:`.Select.select_from` method.

5448

5449 :param target: target table to join towards

5450

5451 :param onclause: ON clause of the join.

5452

5453 :param isouter: if True, generate LEFT OUTER join. Same as

5454 :meth:`_expression.Select.outerjoin`.

5455

5456 :param full: if True, generate FULL OUTER join.

5457

5458 .. seealso::

5459

5460 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5461

5462 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5463

5464 :meth:`_expression.Select.join`

5465

5466 """ # noqa: E501

5467

5468 # note the order of parsing from vs. target is important here, as we

5469 # are also deriving the source of the plugin (i.e. the subject mapper

5470 # in an ORM query) which should favor the "from_" over the "target"

5471

5472 from_ = coercions.expect(

5473 roles.FromClauseRole, from_, apply_propagate_attrs=self

5474 )

5475 join_target = coercions.expect(

5476 roles.JoinTargetRole, target, apply_propagate_attrs=self

5477 )

5478 if onclause is not None:

5479 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5480 else:

5481 onclause_element = None

5482

5483 self._setup_joins += (

5484 (

5485 join_target,

5486 onclause_element,

5487 from_,

5488 {"isouter": isouter, "full": full},

5489 ),

5490 )

5491 return self

5492

5493 def outerjoin(

5494 self,

5495 target: _JoinTargetArgument,

5496 onclause: Optional[_OnClauseArgument] = None,

5497 *,

5498 full: bool = False,

5499 ) -> Self:

5500 """Create a left outer join.

5501

5502 Parameters are the same as that of :meth:`_expression.Select.join`.

5503

5504 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5505 creates a :class:`_sql.Join` object between a

5506 :class:`_sql.FromClause` source that is within the FROM clause of

5507 the existing SELECT, and a given target :class:`_sql.FromClause`,

5508 and then adds this :class:`_sql.Join` to the FROM clause of the

5509 newly generated SELECT statement. This is completely reworked

5510 from the behavior in 1.3, which would instead create a subquery of

5511 the entire

5512 :class:`_expression.Select` and then join that subquery to the

5513 target.

5514

5515 This is a **backwards incompatible change** as the previous behavior

5516 was mostly useless, producing an unnamed subquery rejected by

5517 most databases in any case. The new behavior is modeled after

5518 that of the very successful :meth:`_orm.Query.join` method in the

5519 ORM, in order to support the functionality of :class:`_orm.Query`

5520 being available by using a :class:`_sql.Select` object with an

5521 :class:`_orm.Session`.

5522

5523 See the notes for this change at :ref:`change_select_join`.

5524

5525 .. seealso::

5526

5527 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5528

5529 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5530

5531 :meth:`_expression.Select.join`

5532

5533 """

5534 return self.join(target, onclause=onclause, isouter=True, full=full)

5535

5536 def get_final_froms(self) -> Sequence[FromClause]:

5537 """Compute the final displayed list of :class:`_expression.FromClause`

5538 elements.

5539

5540 This method will run through the full computation required to

5541 determine what FROM elements will be displayed in the resulting

5542 SELECT statement, including shadowing individual tables with

5543 JOIN objects, as well as full computation for ORM use cases including

5544 eager loading clauses.

5545

5546 For ORM use, this accessor returns the **post compilation**

5547 list of FROM objects; this collection will include elements such as

5548 eagerly loaded tables and joins. The objects will **not** be

5549 ORM enabled and not work as a replacement for the

5550 :meth:`_sql.Select.select_froms` collection; additionally, the

5551 method is not well performing for an ORM enabled statement as it

5552 will incur the full ORM construction process.

5553

5554 To retrieve the FROM list that's implied by the "columns" collection

5555 passed to the :class:`_sql.Select` originally, use the

5556 :attr:`_sql.Select.columns_clause_froms` accessor.

5557

5558 To select from an alternative set of columns while maintaining the

5559 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5560 pass the

5561 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5562 parameter.

5563

5564 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5565 method replaces the previous :attr:`_sql.Select.froms` accessor,

5566 which is deprecated.

5567

5568 .. seealso::

5569

5570 :attr:`_sql.Select.columns_clause_froms`

5571

5572 """

5573

5574 return self._compile_state_factory(self, None)._get_display_froms()

5575

5576 @property

5577 @util.deprecated(

5578 "1.4.23",

5579 "The :attr:`_expression.Select.froms` attribute is moved to "

5580 "the :meth:`_expression.Select.get_final_froms` method.",

5581 )

5582 def froms(self) -> Sequence[FromClause]:

5583 """Return the displayed list of :class:`_expression.FromClause`

5584 elements.

5585

5586

5587 """

5588 return self.get_final_froms()

5589

5590 @property

5591 def columns_clause_froms(self) -> List[FromClause]:

5592 """Return the set of :class:`_expression.FromClause` objects implied

5593 by the columns clause of this SELECT statement.

5594

5595 .. versionadded:: 1.4.23

5596

5597 .. seealso::

5598

5599 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5600 statement into account

5601

5602 :meth:`_sql.Select.with_only_columns` - makes use of this

5603 collection to set up a new FROM list

5604

5605 """

5606

5607 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5608 self

5609 )

5610

5611 @property

5612 def inner_columns(self) -> _SelectIterable:

5613 """An iterator of all :class:`_expression.ColumnElement`

5614 expressions which would

5615 be rendered into the columns clause of the resulting SELECT statement.

5616

5617 This method is legacy as of 1.4 and is superseded by the

5618 :attr:`_expression.Select.exported_columns` collection.

5619

5620 """

5621

5622 return iter(self._all_selected_columns)

5623

5624 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

5625 if fromclause is not None and self in fromclause._cloned_set:

5626 return True

5627

5628 for f in self._iterate_from_elements():

5629 if f.is_derived_from(fromclause):

5630 return True

5631 return False

5632

5633 def _copy_internals(

5634 self, clone: _CloneCallableType = _clone, **kw: Any

5635 ) -> None:

5636 # Select() object has been cloned and probably adapted by the

5637 # given clone function. Apply the cloning function to internal

5638 # objects

5639

5640 # 1. keep a dictionary of the froms we've cloned, and what

5641 # they've become. This allows us to ensure the same cloned from

5642 # is used when other items such as columns are "cloned"

5643

5644 all_the_froms = set(

5645 itertools.chain(

5646 _from_objects(*self._raw_columns),

5647 _from_objects(*self._where_criteria),

5648 _from_objects(*[elem[0] for elem in self._setup_joins]),

5649 )

5650 )

5651

5652 # do a clone for the froms we've gathered. what is important here

5653 # is if any of the things we are selecting from, like tables,

5654 # were converted into Join objects. if so, these need to be

5655 # added to _from_obj explicitly, because otherwise they won't be

5656 # part of the new state, as they don't associate themselves with

5657 # their columns.

5658 new_froms = {f: clone(f, **kw) for f in all_the_froms}

5659

5660 # 2. copy FROM collections, adding in joins that we've created.

5661 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5662 add_froms = (

5663 {f for f in new_froms.values() if isinstance(f, Join)}

5664 .difference(all_the_froms)

5665 .difference(existing_from_obj)

5666 )

5667

5668 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5669

5670 # 3. clone everything else, making sure we use columns

5671 # corresponding to the froms we just made.

5672 def replace(

5673 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

5674 **kw: Any,

5675 ) -> Optional[KeyedColumnElement[ColumnElement[Any]]]:

5676 if isinstance(obj, ColumnClause) and obj.table in new_froms:

5677 newelem = new_froms[obj.table].corresponding_column(obj)

5678 return newelem

5679 return None

5680

5681 kw["replace"] = replace

5682

5683 # copy everything else. for table-ish things like correlate,

5684 # correlate_except, setup_joins, these clone normally. For

5685 # column-expression oriented things like raw_columns, where_criteria,

5686 # order by, we get this from the new froms.

5687 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5688

5689 self._reset_memoizations()

5690

5691 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5692 return itertools.chain(

5693 super().get_children(

5694 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5695 **kw,

5696 ),

5697 self._iterate_from_elements(),

5698 )

5699

5700 @_generative

5701 def add_columns(

5702 self, *entities: _ColumnsClauseArgument[Any]

5703 ) -> Select[Unpack[TupleAny]]:

5704 r"""Return a new :func:`_expression.select` construct with

5705 the given entities appended to its columns clause.

5706

5707 E.g.::

5708

5709 my_select = my_select.add_columns(table.c.new_column)

5710

5711 The original expressions in the columns clause remain in place.

5712 To replace the original expressions with new ones, see the method

5713 :meth:`_expression.Select.with_only_columns`.

5714

5715 :param \*entities: column, table, or other entity expressions to be

5716 added to the columns clause

5717

5718 .. seealso::

5719

5720 :meth:`_expression.Select.with_only_columns` - replaces existing

5721 expressions rather than appending.

5722

5723 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5724 example

5725

5726 """

5727 self._reset_memoizations()

5728

5729 self._raw_columns = self._raw_columns + [

5730 coercions.expect(

5731 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5732 )

5733 for column in entities

5734 ]

5735 return self

5736

5737 def _set_entities(

5738 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5739 ) -> None:

5740 self._raw_columns = [

5741 coercions.expect(

5742 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5743 )

5744 for ent in util.to_list(entities)

5745 ]

5746

5747 @util.deprecated(

5748 "1.4",

5749 "The :meth:`_expression.Select.column` method is deprecated and will "

5750 "be removed in a future release. Please use "

5751 ":meth:`_expression.Select.add_columns`",

5752 )

5753 def column(

5754 self, column: _ColumnsClauseArgument[Any]

5755 ) -> Select[Unpack[TupleAny]]:

5756 """Return a new :func:`_expression.select` construct with

5757 the given column expression added to its columns clause.

5758

5759 E.g.::

5760

5761 my_select = my_select.column(table.c.new_column)

5762

5763 See the documentation for

5764 :meth:`_expression.Select.with_only_columns`

5765 for guidelines on adding /replacing the columns of a

5766 :class:`_expression.Select` object.

5767

5768 """

5769 return self.add_columns(column)

5770

5771 @util.preload_module("sqlalchemy.sql.util")

5772 def reduce_columns(

5773 self, only_synonyms: bool = True

5774 ) -> Select[Unpack[TupleAny]]:

5775 """Return a new :func:`_expression.select` construct with redundantly

5776 named, equivalently-valued columns removed from the columns clause.

5777

5778 "Redundant" here means two columns where one refers to the

5779 other either based on foreign key, or via a simple equality

5780 comparison in the WHERE clause of the statement. The primary purpose

5781 of this method is to automatically construct a select statement

5782 with all uniquely-named columns, without the need to use

5783 table-qualified labels as

5784 :meth:`_expression.Select.set_label_style`

5785 does.

5786

5787 When columns are omitted based on foreign key, the referred-to

5788 column is the one that's kept. When columns are omitted based on

5789 WHERE equivalence, the first column in the columns clause is the

5790 one that's kept.

5791

5792 :param only_synonyms: when True, limit the removal of columns

5793 to those which have the same name as the equivalent. Otherwise,

5794 all columns that are equivalent to another are removed.

5795

5796 """

5797 woc: Select[Unpack[TupleAny]]

5798 woc = self.with_only_columns(

5799 *util.preloaded.sql_util.reduce_columns(

5800 self._all_selected_columns,

5801 only_synonyms=only_synonyms,

5802 *(self._where_criteria + self._from_obj),

5803 )

5804 )

5805 return woc

5806

5807 # START OVERLOADED FUNCTIONS self.with_only_columns Select 8

5808

5809 # code within this block is **programmatically,

5810 # statically generated** by tools/generate_sel_v1_overloads.py

5811

5812 @overload

5813 def with_only_columns(self, __ent0: _TCCA[_T0]) -> Select[_T0]: ...

5814

5815 @overload

5816 def with_only_columns(

5817 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]

5818 ) -> Select[_T0, _T1]: ...

5819

5820 @overload

5821 def with_only_columns(

5822 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]

5823 ) -> Select[_T0, _T1, _T2]: ...

5824

5825 @overload

5826 def with_only_columns(

5827 self,

5828 __ent0: _TCCA[_T0],

5829 __ent1: _TCCA[_T1],

5830 __ent2: _TCCA[_T2],

5831 __ent3: _TCCA[_T3],

5832 ) -> Select[_T0, _T1, _T2, _T3]: ...

5833

5834 @overload

5835 def with_only_columns(

5836 self,

5837 __ent0: _TCCA[_T0],

5838 __ent1: _TCCA[_T1],

5839 __ent2: _TCCA[_T2],

5840 __ent3: _TCCA[_T3],

5841 __ent4: _TCCA[_T4],

5842 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

5843

5844 @overload

5845 def with_only_columns(

5846 self,

5847 __ent0: _TCCA[_T0],

5848 __ent1: _TCCA[_T1],

5849 __ent2: _TCCA[_T2],

5850 __ent3: _TCCA[_T3],

5851 __ent4: _TCCA[_T4],

5852 __ent5: _TCCA[_T5],

5853 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

5854

5855 @overload

5856 def with_only_columns(

5857 self,

5858 __ent0: _TCCA[_T0],

5859 __ent1: _TCCA[_T1],

5860 __ent2: _TCCA[_T2],

5861 __ent3: _TCCA[_T3],

5862 __ent4: _TCCA[_T4],

5863 __ent5: _TCCA[_T5],

5864 __ent6: _TCCA[_T6],

5865 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

5866

5867 @overload

5868 def with_only_columns(

5869 self,

5870 __ent0: _TCCA[_T0],

5871 __ent1: _TCCA[_T1],

5872 __ent2: _TCCA[_T2],

5873 __ent3: _TCCA[_T3],

5874 __ent4: _TCCA[_T4],

5875 __ent5: _TCCA[_T5],

5876 __ent6: _TCCA[_T6],

5877 __ent7: _TCCA[_T7],

5878 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]: ...

5879

5880 # END OVERLOADED FUNCTIONS self.with_only_columns

5881

5882 @overload

5883 def with_only_columns(

5884 self,

5885 *entities: _ColumnsClauseArgument[Any],

5886 maintain_column_froms: bool = False,

5887 **__kw: Any,

5888 ) -> Select[Unpack[TupleAny]]: ...

5889

5890 @_generative

5891 def with_only_columns(

5892 self,

5893 *entities: _ColumnsClauseArgument[Any],

5894 maintain_column_froms: bool = False,

5895 **__kw: Any,

5896 ) -> Select[Unpack[TupleAny]]:

5897 r"""Return a new :func:`_expression.select` construct with its columns

5898 clause replaced with the given entities.

5899

5900 By default, this method is exactly equivalent to as if the original

5901 :func:`_expression.select` had been called with the given entities.

5902 E.g. a statement::

5903

5904 s = select(table1.c.a, table1.c.b)

5905 s = s.with_only_columns(table1.c.b)

5906

5907 should be exactly equivalent to::

5908

5909 s = select(table1.c.b)

5910

5911 In this mode of operation, :meth:`_sql.Select.with_only_columns`

5912 will also dynamically alter the FROM clause of the

5913 statement if it is not explicitly stated.

5914 To maintain the existing set of FROMs including those implied by the

5915 current columns clause, add the

5916 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5917 parameter::

5918

5919 s = select(table1.c.a, table2.c.b)

5920 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

5921

5922 The above parameter performs a transfer of the effective FROMs

5923 in the columns collection to the :meth:`_sql.Select.select_from`

5924 method, as though the following were invoked::

5925

5926 s = select(table1.c.a, table2.c.b)

5927 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

5928

5929 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5930 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

5931 collection and performs an operation equivalent to the following::

5932

5933 s = select(table1.c.a, table2.c.b)

5934 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

5935

5936 :param \*entities: column expressions to be used.

5937

5938 :param maintain_column_froms: boolean parameter that will ensure the

5939 FROM list implied from the current columns clause will be transferred

5940 to the :meth:`_sql.Select.select_from` method first.

5941

5942 .. versionadded:: 1.4.23

5943

5944 """ # noqa: E501

5945

5946 if __kw:

5947 raise _no_kw()

5948

5949 # memoizations should be cleared here as of

5950 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

5951 # is the case for now.

5952 self._assert_no_memoizations()

5953

5954 if maintain_column_froms:

5955 self.select_from.non_generative( # type: ignore

5956 self, *self.columns_clause_froms

5957 )

5958

5959 # then memoize the FROMs etc.

5960 _MemoizedSelectEntities._generate_for_statement(self)

5961

5962 self._raw_columns = [

5963 coercions.expect(roles.ColumnsClauseRole, c)

5964 for c in coercions._expression_collection_was_a_list(

5965 "entities", "Select.with_only_columns", entities

5966 )

5967 ]

5968 return self

5969

5970 @property

5971 def whereclause(self) -> Optional[ColumnElement[Any]]:

5972 """Return the completed WHERE clause for this

5973 :class:`_expression.Select` statement.

5974

5975 This assembles the current collection of WHERE criteria

5976 into a single :class:`_expression.BooleanClauseList` construct.

5977

5978

5979 .. versionadded:: 1.4

5980

5981 """

5982

5983 return BooleanClauseList._construct_for_whereclause(

5984 self._where_criteria

5985 )

5986

5987 _whereclause = whereclause

5988

5989 @_generative

5990 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

5991 """Return a new :func:`_expression.select` construct with

5992 the given expression added to

5993 its WHERE clause, joined to the existing clause via AND, if any.

5994

5995 """

5996

5997 assert isinstance(self._where_criteria, tuple)

5998

5999 for criterion in whereclause:

6000 where_criteria: ColumnElement[Any] = coercions.expect(

6001 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6002 )

6003 self._where_criteria += (where_criteria,)

6004 return self

6005

6006 @_generative

6007 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6008 """Return a new :func:`_expression.select` construct with

6009 the given expression added to

6010 its HAVING clause, joined to the existing clause via AND, if any.

6011

6012 """

6013

6014 for criterion in having:

6015 having_criteria = coercions.expect(

6016 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6017 )

6018 self._having_criteria += (having_criteria,)

6019 return self

6020

6021 @_generative

6022 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6023 r"""Return a new :func:`_expression.select` construct which

6024 will apply DISTINCT to the SELECT statement overall.

6025

6026 E.g.::

6027

6028 from sqlalchemy import select

6029 stmt = select(users_table.c.id, users_table.c.name).distinct()

6030

6031 The above would produce an statement resembling::

6032

6033 SELECT DISTINCT user.id, user.name FROM user

6034

6035 The method also accepts an ``*expr`` parameter which produces the

6036 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6037 parameter on other backends which don't support this syntax will

6038 raise an error.

6039

6040 :param \*expr: optional column expressions. When present,

6041 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6042 construct. A deprecation warning and/or :class:`_exc.CompileError`

6043 will be raised on other backends.

6044

6045 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6046 and will raise :class:`_exc.CompileError` in a future version.

6047

6048 """

6049 if expr:

6050 self._distinct = True

6051 self._distinct_on = self._distinct_on + tuple(

6052 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6053 for e in expr

6054 )

6055 else:

6056 self._distinct = True

6057 return self

6058

6059 @_generative

6060 def select_from(self, *froms: _FromClauseArgument) -> Self:

6061 r"""Return a new :func:`_expression.select` construct with the

6062 given FROM expression(s)

6063 merged into its list of FROM objects.

6064

6065 E.g.::

6066

6067 table1 = table('t1', column('a'))

6068 table2 = table('t2', column('b'))

6069 s = select(table1.c.a).\

6070 select_from(

6071 table1.join(table2, table1.c.a==table2.c.b)

6072 )

6073

6074 The "from" list is a unique set on the identity of each element,

6075 so adding an already present :class:`_schema.Table`

6076 or other selectable

6077 will have no effect. Passing a :class:`_expression.Join` that refers

6078 to an already present :class:`_schema.Table`

6079 or other selectable will have

6080 the effect of concealing the presence of that selectable as

6081 an individual element in the rendered FROM list, instead

6082 rendering it into a JOIN clause.

6083

6084 While the typical purpose of :meth:`_expression.Select.select_from`

6085 is to

6086 replace the default, derived FROM clause with a join, it can

6087 also be called with individual table elements, multiple times

6088 if desired, in the case that the FROM clause cannot be fully

6089 derived from the columns clause::

6090

6091 select(func.count('*')).select_from(table1)

6092

6093 """

6094

6095 self._from_obj += tuple(

6096 coercions.expect(

6097 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6098 )

6099 for fromclause in froms

6100 )

6101 return self

6102

6103 @_generative

6104 def correlate(

6105 self,

6106 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6107 ) -> Self:

6108 r"""Return a new :class:`_expression.Select`

6109 which will correlate the given FROM

6110 clauses to that of an enclosing :class:`_expression.Select`.

6111

6112 Calling this method turns off the :class:`_expression.Select` object's

6113 default behavior of "auto-correlation". Normally, FROM elements

6114 which appear in a :class:`_expression.Select`

6115 that encloses this one via

6116 its :term:`WHERE clause`, ORDER BY, HAVING or

6117 :term:`columns clause` will be omitted from this

6118 :class:`_expression.Select`

6119 object's :term:`FROM clause`.

6120 Setting an explicit correlation collection using the

6121 :meth:`_expression.Select.correlate`

6122 method provides a fixed list of FROM objects

6123 that can potentially take place in this process.

6124

6125 When :meth:`_expression.Select.correlate`

6126 is used to apply specific FROM clauses

6127 for correlation, the FROM elements become candidates for

6128 correlation regardless of how deeply nested this

6129 :class:`_expression.Select`

6130 object is, relative to an enclosing :class:`_expression.Select`

6131 which refers to

6132 the same FROM object. This is in contrast to the behavior of

6133 "auto-correlation" which only correlates to an immediate enclosing

6134 :class:`_expression.Select`.

6135 Multi-level correlation ensures that the link

6136 between enclosed and enclosing :class:`_expression.Select`

6137 is always via

6138 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6139 correlation to take place.

6140

6141 If ``None`` is passed, the :class:`_expression.Select`

6142 object will correlate

6143 none of its FROM entries, and all will render unconditionally

6144 in the local FROM clause.

6145

6146 :param \*fromclauses: one or more :class:`.FromClause` or other

6147 FROM-compatible construct such as an ORM mapped entity to become part

6148 of the correlate collection; alternatively pass a single value

6149 ``None`` to remove all existing correlations.

6150

6151 .. seealso::

6152

6153 :meth:`_expression.Select.correlate_except`

6154

6155 :ref:`tutorial_scalar_subquery`

6156

6157 """

6158

6159 # tests failing when we try to change how these

6160 # arguments are passed

6161

6162 self._auto_correlate = False

6163 if not fromclauses or fromclauses[0] in {None, False}:

6164 if len(fromclauses) > 1:

6165 raise exc.ArgumentError(

6166 "additional FROM objects not accepted when "

6167 "passing None/False to correlate()"

6168 )

6169 self._correlate = ()

6170 else:

6171 self._correlate = self._correlate + tuple(

6172 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6173 )

6174 return self

6175

6176 @_generative

6177 def correlate_except(

6178 self,

6179 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6180 ) -> Self:

6181 r"""Return a new :class:`_expression.Select`

6182 which will omit the given FROM

6183 clauses from the auto-correlation process.

6184

6185 Calling :meth:`_expression.Select.correlate_except` turns off the

6186 :class:`_expression.Select` object's default behavior of

6187 "auto-correlation" for the given FROM elements. An element

6188 specified here will unconditionally appear in the FROM list, while

6189 all other FROM elements remain subject to normal auto-correlation

6190 behaviors.

6191

6192 If ``None`` is passed, or no arguments are passed,

6193 the :class:`_expression.Select` object will correlate all of its

6194 FROM entries.

6195

6196 :param \*fromclauses: a list of one or more

6197 :class:`_expression.FromClause`

6198 constructs, or other compatible constructs (i.e. ORM-mapped

6199 classes) to become part of the correlate-exception collection.

6200

6201 .. seealso::

6202

6203 :meth:`_expression.Select.correlate`

6204

6205 :ref:`tutorial_scalar_subquery`

6206

6207 """

6208

6209 self._auto_correlate = False

6210 if not fromclauses or fromclauses[0] in {None, False}:

6211 if len(fromclauses) > 1:

6212 raise exc.ArgumentError(

6213 "additional FROM objects not accepted when "

6214 "passing None/False to correlate_except()"

6215 )

6216 self._correlate_except = ()

6217 else:

6218 self._correlate_except = (self._correlate_except or ()) + tuple(

6219 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6220 )

6221

6222 return self

6223

6224 @HasMemoized_ro_memoized_attribute

6225 def selected_columns(

6226 self,

6227 ) -> ColumnCollection[str, ColumnElement[Any]]:

6228 """A :class:`_expression.ColumnCollection`

6229 representing the columns that

6230 this SELECT statement or similar construct returns in its result set,

6231 not including :class:`_sql.TextClause` constructs.

6232

6233 This collection differs from the :attr:`_expression.FromClause.columns`

6234 collection of a :class:`_expression.FromClause` in that the columns

6235 within this collection cannot be directly nested inside another SELECT

6236 statement; a subquery must be applied first which provides for the

6237 necessary parenthesization required by SQL.

6238

6239 For a :func:`_expression.select` construct, the collection here is

6240 exactly what would be rendered inside the "SELECT" statement, and the

6241 :class:`_expression.ColumnElement` objects are directly present as they

6242 were given, e.g.::

6243

6244 col1 = column('q', Integer)

6245 col2 = column('p', Integer)

6246 stmt = select(col1, col2)

6247

6248 Above, ``stmt.selected_columns`` would be a collection that contains

6249 the ``col1`` and ``col2`` objects directly. For a statement that is

6250 against a :class:`_schema.Table` or other

6251 :class:`_expression.FromClause`, the collection will use the

6252 :class:`_expression.ColumnElement` objects that are in the

6253 :attr:`_expression.FromClause.c` collection of the from element.

6254

6255 A use case for the :attr:`_sql.Select.selected_columns` collection is

6256 to allow the existing columns to be referenced when adding additional

6257 criteria, e.g.::

6258

6259 def filter_on_id(my_select, id):

6260 return my_select.where(my_select.selected_columns['id'] == id)

6261

6262 stmt = select(MyModel)

6263

6264 # adds "WHERE id=:param" to the statement

6265 stmt = filter_on_id(stmt, 42)

6266

6267 .. note::

6268

6269 The :attr:`_sql.Select.selected_columns` collection does not

6270 include expressions established in the columns clause using the

6271 :func:`_sql.text` construct; these are silently omitted from the

6272 collection. To use plain textual column expressions inside of a

6273 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6274 construct.

6275

6276

6277 .. versionadded:: 1.4

6278

6279 """

6280

6281 # compare to SelectState._generate_columns_plus_names, which

6282 # generates the actual names used in the SELECT string. that

6283 # method is more complex because it also renders columns that are

6284 # fully ambiguous, e.g. same column more than once.

6285 conv = cast(

6286 "Callable[[Any], str]",

6287 SelectState._column_naming_convention(self._label_style),

6288 )

6289

6290 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6291 [

6292 (conv(c), c)

6293 for c in self._all_selected_columns

6294 if is_column_element(c)

6295 ]

6296 )

6297 return cc.as_readonly()

6298

6299 @HasMemoized_ro_memoized_attribute

6300 def _all_selected_columns(self) -> _SelectIterable:

6301 meth = SelectState.get_plugin_class(self).all_selected_columns

6302 return list(meth(self))

6303

6304 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6305 if self._label_style is LABEL_STYLE_NONE:

6306 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6307 return self

6308

6309 def _generate_fromclause_column_proxies(

6310 self,

6311 subquery: FromClause,

6312 *,

6313 proxy_compound_columns: Optional[

6314 Iterable[Sequence[ColumnElement[Any]]]

6315 ] = None,

6316 ) -> None:

6317 """Generate column proxies to place in the exported ``.c``

6318 collection of a subquery."""

6319

6320 if proxy_compound_columns:

6321 extra_col_iterator = proxy_compound_columns

6322 prox = [

6323 c._make_proxy(

6324 subquery,

6325 key=proxy_key,

6326 name=required_label_name,

6327 name_is_truncatable=True,

6328 compound_select_cols=extra_cols,

6329 )

6330 for (

6331 (

6332 required_label_name,

6333 proxy_key,

6334 fallback_label_name,

6335 c,

6336 repeated,

6337 ),

6338 extra_cols,

6339 ) in (

6340 zip(

6341 self._generate_columns_plus_names(False),

6342 extra_col_iterator,

6343 )

6344 )

6345 if is_column_element(c)

6346 ]

6347 else:

6348 prox = [

6349 c._make_proxy(

6350 subquery,

6351 key=proxy_key,

6352 name=required_label_name,

6353 name_is_truncatable=True,

6354 )

6355 for (

6356 required_label_name,

6357 proxy_key,

6358 fallback_label_name,

6359 c,

6360 repeated,

6361 ) in (self._generate_columns_plus_names(False))

6362 if is_column_element(c)

6363 ]

6364

6365 subquery._columns._populate_separate_keys(prox)

6366

6367 def _needs_parens_for_grouping(self) -> bool:

6368 return self._has_row_limiting_clause or bool(

6369 self._order_by_clause.clauses

6370 )

6371

6372 def self_group(

6373 self, against: Optional[OperatorType] = None

6374 ) -> Union[SelectStatementGrouping[Self], Self]:

6375 """Return a 'grouping' construct as per the

6376 :class:`_expression.ClauseElement` specification.

6377

6378 This produces an element that can be embedded in an expression. Note

6379 that this method is called automatically as needed when constructing

6380 expressions and should not require explicit use.

6381

6382 """

6383 if (

6384 isinstance(against, CompoundSelect)

6385 and not self._needs_parens_for_grouping()

6386 ):

6387 return self

6388 else:

6389 return SelectStatementGrouping(self)

6390

6391 def union(

6392 self, *other: _SelectStatementForCompoundArgument

6393 ) -> CompoundSelect:

6394 r"""Return a SQL ``UNION`` of this select() construct against

6395 the given selectables provided as positional arguments.

6396

6397 :param \*other: one or more elements with which to create a

6398 UNION.

6399

6400 .. versionchanged:: 1.4.28

6401

6402 multiple elements are now accepted.

6403

6404 :param \**kwargs: keyword arguments are forwarded to the constructor

6405 for the newly created :class:`_sql.CompoundSelect` object.

6406

6407 """

6408 return CompoundSelect._create_union(self, *other)

6409

6410 def union_all(

6411 self, *other: _SelectStatementForCompoundArgument

6412 ) -> CompoundSelect:

6413 r"""Return a SQL ``UNION ALL`` of this select() construct against

6414 the given selectables provided as positional arguments.

6415

6416 :param \*other: one or more elements with which to create a

6417 UNION.

6418

6419 .. versionchanged:: 1.4.28

6420

6421 multiple elements are now accepted.

6422

6423 :param \**kwargs: keyword arguments are forwarded to the constructor

6424 for the newly created :class:`_sql.CompoundSelect` object.

6425

6426 """

6427 return CompoundSelect._create_union_all(self, *other)

6428

6429 def except_(

6430 self, *other: _SelectStatementForCompoundArgument

6431 ) -> CompoundSelect:

6432 r"""Return a SQL ``EXCEPT`` of this select() construct against

6433 the given selectable provided as positional arguments.

6434

6435 :param \*other: one or more elements with which to create a

6436 UNION.

6437

6438 .. versionchanged:: 1.4.28

6439

6440 multiple elements are now accepted.

6441

6442 """

6443 return CompoundSelect._create_except(self, *other)

6444

6445 def except_all(

6446 self, *other: _SelectStatementForCompoundArgument

6447 ) -> CompoundSelect:

6448 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6449 the given selectables provided as positional arguments.

6450

6451 :param \*other: one or more elements with which to create a

6452 UNION.

6453

6454 .. versionchanged:: 1.4.28

6455

6456 multiple elements are now accepted.

6457

6458 """

6459 return CompoundSelect._create_except_all(self, *other)

6460

6461 def intersect(

6462 self, *other: _SelectStatementForCompoundArgument

6463 ) -> CompoundSelect:

6464 r"""Return a SQL ``INTERSECT`` of this select() construct against

6465 the given selectables provided as positional arguments.

6466

6467 :param \*other: one or more elements with which to create a

6468 UNION.

6469

6470 .. versionchanged:: 1.4.28

6471

6472 multiple elements are now accepted.

6473

6474 :param \**kwargs: keyword arguments are forwarded to the constructor

6475 for the newly created :class:`_sql.CompoundSelect` object.

6476

6477 """

6478 return CompoundSelect._create_intersect(self, *other)

6479

6480 def intersect_all(

6481 self, *other: _SelectStatementForCompoundArgument

6482 ) -> CompoundSelect:

6483 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6484 against the given selectables provided as positional arguments.

6485

6486 :param \*other: one or more elements with which to create a

6487 UNION.

6488

6489 .. versionchanged:: 1.4.28

6490

6491 multiple elements are now accepted.

6492

6493 :param \**kwargs: keyword arguments are forwarded to the constructor

6494 for the newly created :class:`_sql.CompoundSelect` object.

6495

6496 """

6497 return CompoundSelect._create_intersect_all(self, *other)

6498

6499

6500class ScalarSelect(

6501 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6502):

6503 """Represent a scalar subquery.

6504

6505

6506 A :class:`_sql.ScalarSelect` is created by invoking the

6507 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6508 then participates in other SQL expressions as a SQL column expression

6509 within the :class:`_sql.ColumnElement` hierarchy.

6510

6511 .. seealso::

6512

6513 :meth:`_sql.SelectBase.scalar_subquery`

6514

6515 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6516

6517 """

6518

6519 _traverse_internals: _TraverseInternalsType = [

6520 ("element", InternalTraversal.dp_clauseelement),

6521 ("type", InternalTraversal.dp_type),

6522 ]

6523

6524 _from_objects: List[FromClause] = []

6525 _is_from_container = True

6526 if not TYPE_CHECKING:

6527 _is_implicitly_boolean = False

6528 inherit_cache = True

6529

6530 element: SelectBase

6531

6532 def __init__(self, element: SelectBase) -> None:

6533 self.element = element

6534 self.type = element._scalar_type()

6535 self._propagate_attrs = element._propagate_attrs

6536

6537 def __getattr__(self, attr: str) -> Any:

6538 return getattr(self.element, attr)

6539

6540 def __getstate__(self) -> Dict[str, Any]:

6541 return {"element": self.element, "type": self.type}

6542

6543 def __setstate__(self, state: Dict[str, Any]) -> None:

6544 self.element = state["element"]

6545 self.type = state["type"]

6546

6547 @property

6548 def columns(self) -> NoReturn:

6549 raise exc.InvalidRequestError(

6550 "Scalar Select expression has no "

6551 "columns; use this object directly "

6552 "within a column-level expression."

6553 )

6554

6555 c = columns

6556

6557 @_generative

6558 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6559 """Apply a WHERE clause to the SELECT statement referred to

6560 by this :class:`_expression.ScalarSelect`.

6561

6562 """

6563 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6564 crit

6565 )

6566 return self

6567

6568 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6569 return self

6570

6571 if TYPE_CHECKING:

6572

6573 def _ungroup(self) -> Select[Unpack[TupleAny]]: ...

6574

6575 @_generative

6576 def correlate(

6577 self,

6578 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6579 ) -> Self:

6580 r"""Return a new :class:`_expression.ScalarSelect`

6581 which will correlate the given FROM

6582 clauses to that of an enclosing :class:`_expression.Select`.

6583

6584 This method is mirrored from the :meth:`_sql.Select.correlate` method

6585 of the underlying :class:`_sql.Select`. The method applies the

6586 :meth:_sql.Select.correlate` method, then returns a new

6587 :class:`_sql.ScalarSelect` against that statement.

6588

6589 .. versionadded:: 1.4 Previously, the

6590 :meth:`_sql.ScalarSelect.correlate`

6591 method was only available from :class:`_sql.Select`.

6592

6593 :param \*fromclauses: a list of one or more

6594 :class:`_expression.FromClause`

6595 constructs, or other compatible constructs (i.e. ORM-mapped

6596 classes) to become part of the correlate collection.

6597

6598 .. seealso::

6599

6600 :meth:`_expression.ScalarSelect.correlate_except`

6601

6602 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6603

6604

6605 """

6606 self.element = cast(

6607 "Select[Unpack[TupleAny]]", self.element

6608 ).correlate(*fromclauses)

6609 return self

6610

6611 @_generative

6612 def correlate_except(

6613 self,

6614 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6615 ) -> Self:

6616 r"""Return a new :class:`_expression.ScalarSelect`

6617 which will omit the given FROM

6618 clauses from the auto-correlation process.

6619

6620 This method is mirrored from the

6621 :meth:`_sql.Select.correlate_except` method of the underlying

6622 :class:`_sql.Select`. The method applies the

6623 :meth:_sql.Select.correlate_except` method, then returns a new

6624 :class:`_sql.ScalarSelect` against that statement.

6625

6626 .. versionadded:: 1.4 Previously, the

6627 :meth:`_sql.ScalarSelect.correlate_except`

6628 method was only available from :class:`_sql.Select`.

6629

6630 :param \*fromclauses: a list of one or more

6631 :class:`_expression.FromClause`

6632 constructs, or other compatible constructs (i.e. ORM-mapped

6633 classes) to become part of the correlate-exception collection.

6634

6635 .. seealso::

6636

6637 :meth:`_expression.ScalarSelect.correlate`

6638

6639 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6640

6641

6642 """

6643

6644 self.element = cast(

6645 "Select[Unpack[TupleAny]]", self.element

6646 ).correlate_except(*fromclauses)

6647 return self

6648

6649

6650class Exists(UnaryExpression[bool]):

6651 """Represent an ``EXISTS`` clause.

6652

6653 See :func:`_sql.exists` for a description of usage.

6654

6655 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6656 instance by calling :meth:`_sql.SelectBase.exists`.

6657

6658 """

6659

6660 inherit_cache = True

6661 element: Union[

6662 SelectStatementGrouping[Select[Unpack[TupleAny]]],

6663 ScalarSelect[Any],

6664 ]

6665

6666 def __init__(

6667 self,

6668 __argument: Optional[

6669 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6670 ] = None,

6671 /,

6672 ):

6673 s: ScalarSelect[Any]

6674

6675 # TODO: this seems like we should be using coercions for this

6676 if __argument is None:

6677 s = Select(literal_column("*")).scalar_subquery()

6678 elif isinstance(__argument, SelectBase):

6679 s = __argument.scalar_subquery()

6680 s._propagate_attrs = __argument._propagate_attrs

6681 elif isinstance(__argument, ScalarSelect):

6682 s = __argument

6683 else:

6684 s = Select(__argument).scalar_subquery()

6685

6686 UnaryExpression.__init__(

6687 self,

6688 s,

6689 operator=operators.exists,

6690 type_=type_api.BOOLEANTYPE,

6691 wraps_column_expression=True,

6692 )

6693

6694 @util.ro_non_memoized_property

6695 def _from_objects(self) -> List[FromClause]:

6696 return []

6697

6698 def _regroup(

6699 self,

6700 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

6701 ) -> SelectStatementGrouping[Select[Unpack[TupleAny]]]:

6702 element = self.element._ungroup()

6703 new_element = fn(element)

6704

6705 return_value = new_element.self_group(against=operators.exists)

6706 assert isinstance(return_value, SelectStatementGrouping)

6707 return return_value

6708

6709 def select(self) -> Select[bool]:

6710 r"""Return a SELECT of this :class:`_expression.Exists`.

6711

6712 e.g.::

6713

6714 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6715

6716 This will produce a statement resembling::

6717

6718 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6719

6720 .. seealso::

6721

6722 :func:`_expression.select` - general purpose

6723 method which allows for arbitrary column lists.

6724

6725 """ # noqa

6726

6727 return Select(self)

6728

6729 def correlate(

6730 self,

6731 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6732 ) -> Self:

6733 """Apply correlation to the subquery noted by this

6734 :class:`_sql.Exists`.

6735

6736 .. seealso::

6737

6738 :meth:`_sql.ScalarSelect.correlate`

6739

6740 """

6741 e = self._clone()

6742 e.element = self._regroup(

6743 lambda element: element.correlate(*fromclauses)

6744 )

6745 return e

6746

6747 def correlate_except(

6748 self,

6749 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6750 ) -> Self:

6751 """Apply correlation to the subquery noted by this

6752 :class:`_sql.Exists`.

6753

6754 .. seealso::

6755

6756 :meth:`_sql.ScalarSelect.correlate_except`

6757

6758 """

6759

6760 e = self._clone()

6761 e.element = self._regroup(

6762 lambda element: element.correlate_except(*fromclauses)

6763 )

6764 return e

6765

6766 def select_from(self, *froms: _FromClauseArgument) -> Self:

6767 """Return a new :class:`_expression.Exists` construct,

6768 applying the given

6769 expression to the :meth:`_expression.Select.select_from`

6770 method of the select

6771 statement contained.

6772

6773 .. note:: it is typically preferable to build a :class:`_sql.Select`

6774 statement first, including the desired WHERE clause, then use the

6775 :meth:`_sql.SelectBase.exists` method to produce an

6776 :class:`_sql.Exists` object at once.

6777

6778 """

6779 e = self._clone()

6780 e.element = self._regroup(lambda element: element.select_from(*froms))

6781 return e

6782

6783 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

6784 """Return a new :func:`_expression.exists` construct with the

6785 given expression added to

6786 its WHERE clause, joined to the existing clause via AND, if any.

6787

6788

6789 .. note:: it is typically preferable to build a :class:`_sql.Select`

6790 statement first, including the desired WHERE clause, then use the

6791 :meth:`_sql.SelectBase.exists` method to produce an

6792 :class:`_sql.Exists` object at once.

6793

6794 """

6795 e = self._clone()

6796 e.element = self._regroup(lambda element: element.where(*clause))

6797 return e

6798

6799

6800class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

6801 """Wrap a :class:`_expression.TextClause` construct within a

6802 :class:`_expression.SelectBase`

6803 interface.

6804

6805 This allows the :class:`_expression.TextClause` object to gain a

6806 ``.c`` collection

6807 and other FROM-like capabilities such as

6808 :meth:`_expression.FromClause.alias`,

6809 :meth:`_expression.SelectBase.cte`, etc.

6810

6811 The :class:`_expression.TextualSelect` construct is produced via the

6812 :meth:`_expression.TextClause.columns`

6813 method - see that method for details.

6814

6815 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

6816 class was renamed

6817 from ``TextAsFrom``, to more correctly suit its role as a

6818 SELECT-oriented object and not a FROM clause.

6819

6820 .. seealso::

6821

6822 :func:`_expression.text`

6823

6824 :meth:`_expression.TextClause.columns` - primary creation interface.

6825

6826 """

6827

6828 __visit_name__ = "textual_select"

6829

6830 _label_style = LABEL_STYLE_NONE

6831

6832 _traverse_internals: _TraverseInternalsType = (

6833 [

6834 ("element", InternalTraversal.dp_clauseelement),

6835 ("column_args", InternalTraversal.dp_clauseelement_list),

6836 ]

6837 + SupportsCloneAnnotations._clone_annotations_traverse_internals

6838 + HasCTE._has_ctes_traverse_internals

6839 )

6840

6841 _is_textual = True

6842

6843 is_text = True

6844 is_select = True

6845

6846 def __init__(

6847 self,

6848 text: TextClause,

6849 columns: List[_ColumnExpressionArgument[Any]],

6850 positional: bool = False,

6851 ) -> None:

6852 self._init(

6853 text,

6854 # convert for ORM attributes->columns, etc

6855 [

6856 coercions.expect(roles.LabeledColumnExprRole, c)

6857 for c in columns

6858 ],

6859 positional,

6860 )

6861

6862 def _init(

6863 self,

6864 text: TextClause,

6865 columns: List[NamedColumn[Any]],

6866 positional: bool = False,

6867 ) -> None:

6868 self.element = text

6869 self.column_args = columns

6870 self.positional = positional

6871

6872 @HasMemoized_ro_memoized_attribute

6873 def selected_columns(

6874 self,

6875 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

6876 """A :class:`_expression.ColumnCollection`

6877 representing the columns that

6878 this SELECT statement or similar construct returns in its result set,

6879 not including :class:`_sql.TextClause` constructs.

6880

6881 This collection differs from the :attr:`_expression.FromClause.columns`

6882 collection of a :class:`_expression.FromClause` in that the columns

6883 within this collection cannot be directly nested inside another SELECT

6884 statement; a subquery must be applied first which provides for the

6885 necessary parenthesization required by SQL.

6886

6887 For a :class:`_expression.TextualSelect` construct, the collection

6888 contains the :class:`_expression.ColumnElement` objects that were

6889 passed to the constructor, typically via the

6890 :meth:`_expression.TextClause.columns` method.

6891

6892

6893 .. versionadded:: 1.4

6894

6895 """

6896 return ColumnCollection(

6897 (c.key, c) for c in self.column_args

6898 ).as_readonly()

6899

6900 @util.ro_non_memoized_property

6901 def _all_selected_columns(self) -> _SelectIterable:

6902 return self.column_args

6903

6904 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

6905 return self

6906

6907 def _ensure_disambiguated_names(self) -> TextualSelect:

6908 return self

6909

6910 @_generative

6911 def bindparams(

6912 self,

6913 *binds: BindParameter[Any],

6914 **bind_as_values: Any,

6915 ) -> Self:

6916 self.element = self.element.bindparams(*binds, **bind_as_values)

6917 return self

6918

6919 def _generate_fromclause_column_proxies(

6920 self,

6921 fromclause: FromClause,

6922 *,

6923 proxy_compound_columns: Optional[

6924 Iterable[Sequence[ColumnElement[Any]]]

6925 ] = None,

6926 ) -> None:

6927 if TYPE_CHECKING:

6928 assert isinstance(fromclause, Subquery)

6929

6930 if proxy_compound_columns:

6931 fromclause._columns._populate_separate_keys(

6932 c._make_proxy(fromclause, compound_select_cols=extra_cols)

6933 for c, extra_cols in zip(

6934 self.column_args, proxy_compound_columns

6935 )

6936 )

6937 else:

6938 fromclause._columns._populate_separate_keys(

6939 c._make_proxy(fromclause) for c in self.column_args

6940 )

6941

6942 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

6943 return self.column_args[0].type

6944

6945

6946TextAsFrom = TextualSelect

6947"""Backwards compatibility with the previous name"""

6948

6949

6950class AnnotatedFromClause(Annotated):

6951 def _copy_internals(self, **kw: Any) -> None:

6952 super()._copy_internals(**kw)

6953 if kw.get("ind_cols_on_fromclause", False):

6954 ee = self._Annotated__element # type: ignore

6955

6956 self.c = ee.__class__.c.fget(self) # type: ignore

6957

6958 @util.ro_memoized_property

6959 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

6960 """proxy the .c collection of the underlying FromClause.

6961

6962 Originally implemented in 2008 as a simple load of the .c collection

6963 when the annotated construct was created (see d3621ae961a), in modern

6964 SQLAlchemy versions this can be expensive for statements constructed

6965 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

6966 it, which works just as well.

6967

6968 Two different use cases seem to require the collection either copied

6969 from the underlying one, or unique to this AnnotatedFromClause.

6970

6971 See test_selectable->test_annotated_corresponding_column

6972

6973 """

6974 ee = self._Annotated__element # type: ignore

6975 return ee.c # type: ignore