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

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

1111 FROM table1 JOIN table2 ON true

1112

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

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

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

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

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

1118

1119

1120 .. versionadded:: 1.4

1121

1122 """ # noqa: E501

1123

1124 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1127 when generating the columns clause of a SELECT statement.

1128

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

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

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

1132

1133 .. sourcecode:: pycon+sql

1134

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

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

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

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

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

1140 FROM table1 JOIN table2 ON true

1141

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

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

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

1145

1146 .. versionadded:: 1.4

1147

1148 """ # noqa: E501

1149

1150 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1151 """The default label style, refers to

1152 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1153

1154 .. versionadded:: 1.4

1155

1156 """

1157

1158 LABEL_STYLE_LEGACY_ORM = 3

1159

1160

1161(

1162 LABEL_STYLE_NONE,

1163 LABEL_STYLE_TABLENAME_PLUS_COL,

1164 LABEL_STYLE_DISAMBIGUATE_ONLY,

1165 _,

1166) = list(SelectLabelStyle)

1167

1168LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1169

1170

1171class Join(roles.DMLTableRole, FromClause):

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

1173 :class:`_expression.FromClause`

1174 elements.

1175

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

1177 is the module-level

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

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

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

1181 :class:`_schema.Table`).

1182

1183 .. seealso::

1184

1185 :func:`_expression.join`

1186

1187 :meth:`_expression.FromClause.join`

1188

1189 """

1190

1191 __visit_name__ = "join"

1192

1193 _traverse_internals: _TraverseInternalsType = [

1194 ("left", InternalTraversal.dp_clauseelement),

1195 ("right", InternalTraversal.dp_clauseelement),

1196 ("onclause", InternalTraversal.dp_clauseelement),

1197 ("isouter", InternalTraversal.dp_boolean),

1198 ("full", InternalTraversal.dp_boolean),

1199 ]

1200

1201 _is_join = True

1202

1203 left: FromClause

1204 right: FromClause

1205 onclause: Optional[ColumnElement[bool]]

1206 isouter: bool

1207 full: bool

1208

1209 def __init__(

1210 self,

1211 left: _FromClauseArgument,

1212 right: _FromClauseArgument,

1213 onclause: Optional[_OnClauseArgument] = None,

1214 isouter: bool = False,

1215 full: bool = False,

1216 ):

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

1218

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

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

1221 :class:`_expression.FromClause` object.

1222

1223 """

1224

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

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

1227 # annotated objects. test_orm.py -> test_fetch_results

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

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

1230 # callcounts for a single compilation in that particular test

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

1232 # 29200 -> 30373

1233

1234 self.left = coercions.expect(

1235 roles.FromClauseRole,

1236 left,

1237 )

1238 self.right = coercions.expect(

1239 roles.FromClauseRole,

1240 right,

1241 ).self_group()

1242

1243 if onclause is None:

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

1245 else:

1246 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1247 # not merged yet

1248 self.onclause = coercions.expect(

1249 roles.OnClauseRole, onclause

1250 ).self_group(against=operators._asbool)

1251

1252 self.isouter = isouter

1253 self.full = full

1254

1255 @util.ro_non_memoized_property

1256 def description(self) -> str:

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

1258 self.left.description,

1259 id(self.left),

1260 self.right.description,

1261 id(self.right),

1262 )

1263

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

1265 return (

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

1267 # as well

1268 hash(fromclause) == hash(self)

1269 or self.left.is_derived_from(fromclause)

1270 or self.right.is_derived_from(fromclause)

1271 )

1272

1273 def self_group(

1274 self, against: Optional[OperatorType] = None

1275 ) -> FromGrouping:

1276 return FromGrouping(self)

1277

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

1279 def _populate_column_collection(self) -> None:

1280 sqlutil = util.preloaded.sql_util

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

1282 c for c in self.right.c

1283 ]

1284

1285 self.primary_key.extend( # type: ignore

1286 sqlutil.reduce_columns(

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

1288 )

1289 )

1290 self._columns._populate_separate_keys(

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

1292 )

1293 self.foreign_keys.update( # type: ignore

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

1295 )

1296

1297 def _copy_internals(

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

1299 ) -> None:

1300 # see Select._copy_internals() for similar concept

1301

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

1303 # determine the new FROM clauses

1304 all_the_froms = set(

1305 itertools.chain(

1306 _from_objects(self.left),

1307 _from_objects(self.right),

1308 )

1309 )

1310

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

1312 # cache used by the clone function

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

1314

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

1316 # ColumnClause with parent table referring to those

1317 # replaced FromClause objects

1318 def replace(

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

1320 **kw: Any,

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

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

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

1324 return newelem

1325 return None

1326

1327 kw["replace"] = replace

1328

1329 # run normal _copy_internals. the clones for

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

1331 # cache

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

1333

1334 self._reset_memoizations()

1335

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

1337 super()._refresh_for_new_column(column)

1338 self.left._refresh_for_new_column(column)

1339 self.right._refresh_for_new_column(column)

1340

1341 def _match_primaries(

1342 self,

1343 left: FromClause,

1344 right: FromClause,

1345 ) -> ColumnElement[bool]:

1346 if isinstance(left, Join):

1347 left_right = left.right

1348 else:

1349 left_right = None

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

1351

1352 @classmethod

1353 def _join_condition(

1354 cls,

1355 a: FromClause,

1356 b: FromClause,

1357 *,

1358 a_subset: Optional[FromClause] = None,

1359 consider_as_foreign_keys: Optional[

1360 AbstractSet[ColumnClause[Any]]

1361 ] = None,

1362 ) -> ColumnElement[bool]:

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

1364

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

1366

1367 """

1368 constraints = cls._joincond_scan_left_right(

1369 a, a_subset, b, consider_as_foreign_keys

1370 )

1371

1372 if len(constraints) > 1:

1373 cls._joincond_trim_constraints(

1374 a, b, constraints, consider_as_foreign_keys

1375 )

1376

1377 if len(constraints) == 0:

1378 if isinstance(b, FromGrouping):

1379 hint = (

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

1381 "subquery using alias()?"

1382 )

1383 else:

1384 hint = ""

1385 raise exc.NoForeignKeysError(

1386 "Can't find any foreign key relationships "

1387 "between '%s' and '%s'.%s"

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

1389 )

1390

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

1392 if len(crit) == 1:

1393 return crit[0]

1394 else:

1395 return and_(*crit)

1396

1397 @classmethod

1398 def _can_join(

1399 cls,

1400 left: FromClause,

1401 right: FromClause,

1402 *,

1403 consider_as_foreign_keys: Optional[

1404 AbstractSet[ColumnClause[Any]]

1405 ] = None,

1406 ) -> bool:

1407 if isinstance(left, Join):

1408 left_right = left.right

1409 else:

1410 left_right = None

1411

1412 constraints = cls._joincond_scan_left_right(

1413 a=left,

1414 b=right,

1415 a_subset=left_right,

1416 consider_as_foreign_keys=consider_as_foreign_keys,

1417 )

1418

1419 return bool(constraints)

1420

1421 @classmethod

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

1423 def _joincond_scan_left_right(

1424 cls,

1425 a: FromClause,

1426 a_subset: Optional[FromClause],

1427 b: FromClause,

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

1429 ) -> collections.defaultdict[

1430 Optional[ForeignKeyConstraint],

1431 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1432 ]:

1433 sql_util = util.preloaded.sql_util

1434

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

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

1437

1438 constraints: collections.defaultdict[

1439 Optional[ForeignKeyConstraint],

1440 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1441 ] = collections.defaultdict(list)

1442

1443 for left in (a_subset, a):

1444 if left is None:

1445 continue

1446 for fk in sorted(

1447 b.foreign_keys,

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

1449 ):

1450 if (

1451 consider_as_foreign_keys is not None

1452 and fk.parent not in consider_as_foreign_keys

1453 ):

1454 continue

1455 try:

1456 col = fk.get_referent(left)

1457 except exc.NoReferenceError as nrte:

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

1459 if nrte.table_name in table_names:

1460 raise

1461 else:

1462 continue

1463

1464 if col is not None:

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

1466 if left is not b:

1467 for fk in sorted(

1468 left.foreign_keys,

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

1470 ):

1471 if (

1472 consider_as_foreign_keys is not None

1473 and fk.parent not in consider_as_foreign_keys

1474 ):

1475 continue

1476 try:

1477 col = fk.get_referent(b)

1478 except exc.NoReferenceError as nrte:

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

1480 if nrte.table_name in table_names:

1481 raise

1482 else:

1483 continue

1484

1485 if col is not None:

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

1487 if constraints:

1488 break

1489 return constraints

1490

1491 @classmethod

1492 def _joincond_trim_constraints(

1493 cls,

1494 a: FromClause,

1495 b: FromClause,

1496 constraints: Dict[Any, Any],

1497 consider_as_foreign_keys: Optional[Any],

1498 ) -> None:

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

1500 # to include just those FKCs that match exactly to

1501 # "consider_as_foreign_keys".

1502 if consider_as_foreign_keys:

1503 for const in list(constraints):

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

1505 consider_as_foreign_keys

1506 ):

1507 del constraints[const]

1508

1509 # if still multiple constraints, but

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

1511 if len(constraints) > 1:

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

1513 if len(dedupe) == 1:

1514 key = list(constraints)[0]

1515 constraints = {key: constraints[key]}

1516

1517 if len(constraints) != 1:

1518 raise exc.AmbiguousForeignKeysError(

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

1520 "tables have more than one foreign key "

1521 "constraint relationship between them. "

1522 "Please specify the 'onclause' of this "

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

1524 )

1525

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

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

1528 :class:`_expression.Join`.

1529

1530 E.g.::

1531

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

1533

1534 stmt = stmt.select()

1535

1536 The above will produce a SQL string resembling::

1537

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

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

1540

1541 """

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

1543

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

1545 def _anonymous_fromclause(

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

1547 ) -> TODO_Any:

1548 sqlutil = util.preloaded.sql_util

1549 if flat:

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

1551 left_name = name # will recurse

1552 else:

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

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

1555 else:

1556 left_name = name

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

1558 right_name = name # will recurse

1559 else:

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

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

1562 else:

1563 right_name = name

1564 left_a, right_a = (

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

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

1567 )

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

1569 sqlutil.ClauseAdapter(right_a)

1570 )

1571

1572 return left_a.join(

1573 right_a,

1574 adapter.traverse(self.onclause),

1575 isouter=self.isouter,

1576 full=self.full,

1577 )

1578 else:

1579 return (

1580 self.select()

1581 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1582 .correlate(None)

1583 .alias(name)

1584 )

1585

1586 @util.ro_non_memoized_property

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

1588 return itertools.chain(

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

1590 )

1591

1592 @util.ro_non_memoized_property

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

1594 self_list: List[FromClause] = [self]

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

1596

1597

1598class NoInit:

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

1600 raise NotImplementedError(

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

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

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

1604 "selectable objects."

1605 % (

1606 self.__class__.__name__,

1607 self.__class__.__name__.lower(),

1608 self.__class__.__name__.lower(),

1609 )

1610 )

1611

1612

1613class LateralFromClause(NamedFromClause):

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

1615

1616

1617# FromClause ->

1618# AliasedReturnsRows

1619# -> Alias only for FromClause

1620# -> Subquery only for SelectBase

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

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

1623# w/ non-deprecated coercion

1624# -> TableSample -> only for FromClause

1625

1626

1627class AliasedReturnsRows(NoInit, NamedFromClause):

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

1629 selectables."""

1630

1631 _is_from_container = True

1632

1633 _supports_derived_columns = False

1634

1635 element: ReturnsRows

1636

1637 _traverse_internals: _TraverseInternalsType = [

1638 ("element", InternalTraversal.dp_clauseelement),

1639 ("name", InternalTraversal.dp_anon_name),

1640 ]

1641

1642 @classmethod

1643 def _construct(

1644 cls,

1645 selectable: Any,

1646 *,

1647 name: Optional[str] = None,

1648 **kw: Any,

1649 ) -> Self:

1650 obj = cls.__new__(cls)

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

1652 return obj

1653

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

1655 self.element = coercions.expect(

1656 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1657 )

1658 self.element = selectable

1659 self._orig_name = name

1660 if name is None:

1661 if (

1662 isinstance(selectable, FromClause)

1663 and selectable.named_with_column

1664 ):

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

1666 if isinstance(name, _anonymous_label):

1667 name = None

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

1669 self.name = name

1670

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

1672 super()._refresh_for_new_column(column)

1673 self.element._refresh_for_new_column(column)

1674

1675 def _populate_column_collection(self) -> None:

1676 self.element._generate_fromclause_column_proxies(self)

1677

1678 @util.ro_non_memoized_property

1679 def description(self) -> str:

1680 name = self.name

1681 if isinstance(name, _anonymous_label):

1682 name = "anon_1"

1683

1684 return name

1685

1686 @util.ro_non_memoized_property

1687 def implicit_returning(self) -> bool:

1688 return self.element.implicit_returning # type: ignore

1689

1690 @property

1691 def original(self) -> ReturnsRows:

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

1693 return self.element

1694

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

1696 if fromclause in self._cloned_set:

1697 return True

1698 return self.element.is_derived_from(fromclause)

1699

1700 def _copy_internals(

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

1702 ) -> None:

1703 existing_element = self.element

1704

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

1706

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

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

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

1710 # performance.

1711 if existing_element is not self.element:

1712 self._reset_column_collection()

1713

1714 @property

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

1716 return [self]

1717

1718

1719class FromClauseAlias(AliasedReturnsRows):

1720 element: FromClause

1721

1722

1723class Alias(roles.DMLTableRole, FromClauseAlias):

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

1725

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

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

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

1729

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

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

1732 method available

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

1734

1735 .. seealso::

1736

1737 :meth:`_expression.FromClause.alias`

1738

1739 """

1740

1741 __visit_name__ = "alias"

1742

1743 inherit_cache = True

1744

1745 element: FromClause

1746

1747 @classmethod

1748 def _factory(

1749 cls,

1750 selectable: FromClause,

1751 name: Optional[str] = None,

1752 flat: bool = False,

1753 ) -> NamedFromClause:

1754 return coercions.expect(

1755 roles.FromClauseRole, selectable, allow_select=True

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

1757

1758

1759class TableValuedAlias(LateralFromClause, Alias):

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

1761

1762 This construct provides for a SQL function that returns columns

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

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

1765 method, e.g.:

1766

1767 .. sourcecode:: pycon+sql

1768

1769 >>> from sqlalchemy import select, func

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

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

1772 {printsql}SELECT anon_1.value

1773 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1774

1775 .. versionadded:: 1.4.0b2

1776

1777 .. seealso::

1778

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

1780

1781 """ # noqa: E501

1782

1783 __visit_name__ = "table_valued_alias"

1784

1785 _supports_derived_columns = True

1786 _render_derived = False

1787 _render_derived_w_types = False

1788 joins_implicitly = False

1789

1790 _traverse_internals: _TraverseInternalsType = [

1791 ("element", InternalTraversal.dp_clauseelement),

1792 ("name", InternalTraversal.dp_anon_name),

1793 ("_tableval_type", InternalTraversal.dp_type),

1794 ("_render_derived", InternalTraversal.dp_boolean),

1795 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1796 ]

1797

1798 def _init(

1799 self,

1800 selectable: Any,

1801 *,

1802 name: Optional[str] = None,

1803 table_value_type: Optional[TableValueType] = None,

1804 joins_implicitly: bool = False,

1805 ) -> None:

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

1807

1808 self.joins_implicitly = joins_implicitly

1809 self._tableval_type = (

1810 type_api.TABLEVALUE

1811 if table_value_type is None

1812 else table_value_type

1813 )

1814

1815 @HasMemoized.memoized_attribute

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

1817 """Return a column expression representing this

1818 :class:`_sql.TableValuedAlias`.

1819

1820 This accessor is used to implement the

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

1822 method for further details.

1823

1824 E.g.:

1825

1826 .. sourcecode:: pycon+sql

1827

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

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

1830

1831 .. seealso::

1832

1833 :meth:`_functions.FunctionElement.column_valued`

1834

1835 """

1836

1837 return TableValuedColumn(self, self._tableval_type)

1838

1839 def alias(

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

1841 ) -> TableValuedAlias:

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

1843

1844 This creates a distinct FROM object that will be distinguished

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

1846

1847 """

1848

1849 tva: TableValuedAlias = TableValuedAlias._construct(

1850 self,

1851 name=name,

1852 table_value_type=self._tableval_type,

1853 joins_implicitly=self.joins_implicitly,

1854 )

1855

1856 if self._render_derived:

1857 tva._render_derived = True

1858 tva._render_derived_w_types = self._render_derived_w_types

1859

1860 return tva

1861

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

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

1864 set, so that it renders as LATERAL.

1865

1866 .. seealso::

1867

1868 :func:`_expression.lateral`

1869

1870 """

1871 tva = self.alias(name=name)

1872 tva._is_lateral = True

1873 return tva

1874

1875 def render_derived(

1876 self,

1877 name: Optional[str] = None,

1878 with_types: bool = False,

1879 ) -> TableValuedAlias:

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

1881

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

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

1884

1885 .. sourcecode:: pycon+sql

1886

1887 >>> print(

1888 ... select(

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

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

1891 ... )

1892 ... )

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

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

1895

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

1897 the alias expression (this syntax currently applies to the

1898 PostgreSQL database):

1899

1900 .. sourcecode:: pycon+sql

1901

1902 >>> print(

1903 ... select(

1904 ... func.json_to_recordset(

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

1906 ... )

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

1908 ... .render_derived(with_types=True)

1909 ... )

1910 ... )

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

1912 AS anon_1(a INTEGER, b VARCHAR)

1913

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

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

1916

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

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

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

1920

1921 """ # noqa: E501

1922

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

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

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

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

1927 # (just saw it happen on CI)

1928

1929 # construct against original to prevent memory growth

1930 # for repeated generations

1931 new_alias: TableValuedAlias = TableValuedAlias._construct(

1932 self.element,

1933 name=name,

1934 table_value_type=self._tableval_type,

1935 joins_implicitly=self.joins_implicitly,

1936 )

1937 new_alias._render_derived = True

1938 new_alias._render_derived_w_types = with_types

1939 return new_alias

1940

1941

1942class Lateral(FromClauseAlias, LateralFromClause):

1943 """Represent a LATERAL subquery.

1944

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

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

1947 method available

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

1949

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

1951 PostgreSQL versions provide support for this keyword.

1952

1953 .. seealso::

1954

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

1956

1957 """

1958

1959 __visit_name__ = "lateral"

1960 _is_lateral = True

1961

1962 inherit_cache = True

1963

1964 @classmethod

1965 def _factory(

1966 cls,

1967 selectable: Union[SelectBase, _FromClauseArgument],

1968 name: Optional[str] = None,

1969 ) -> LateralFromClause:

1970 return coercions.expect(

1971 roles.FromClauseRole, selectable, explicit_subquery=True

1972 ).lateral(name=name)

1973

1974

1975class TableSample(FromClauseAlias):

1976 """Represent a TABLESAMPLE clause.

1977

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

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

1980 method

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

1982

1983 .. seealso::

1984

1985 :func:`_expression.tablesample`

1986

1987 """

1988

1989 __visit_name__ = "tablesample"

1990

1991 _traverse_internals: _TraverseInternalsType = (

1992 AliasedReturnsRows._traverse_internals

1993 + [

1994 ("sampling", InternalTraversal.dp_clauseelement),

1995 ("seed", InternalTraversal.dp_clauseelement),

1996 ]

1997 )

1998

1999 @classmethod

2000 def _factory(

2001 cls,

2002 selectable: _FromClauseArgument,

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

2004 name: Optional[str] = None,

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

2006 ) -> TableSample:

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

2008 sampling, name=name, seed=seed

2009 )

2010

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

2012 def _init( # type: ignore[override]

2013 self,

2014 selectable: Any,

2015 *,

2016 name: Optional[str] = None,

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

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

2019 ) -> None:

2020 assert sampling is not None

2021 functions = util.preloaded.sql_functions

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

2023 sampling = functions.func.system(sampling)

2024

2025 self.sampling: Function[Any] = sampling

2026 self.seed = seed

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

2028

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

2030 return self.sampling

2031

2032

2033class CTE(

2034 roles.DMLTableRole,

2035 roles.IsCTERole,

2036 Generative,

2037 HasPrefixes,

2038 HasSuffixes,

2039 AliasedReturnsRows,

2040):

2041 """Represent a Common Table Expression.

2042

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

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

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

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

2047 :class:`_sql.Update` and

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

2049 usage details on CTEs.

2050

2051 .. seealso::

2052

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

2054

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

2056

2057 """

2058

2059 __visit_name__ = "cte"

2060

2061 _traverse_internals: _TraverseInternalsType = (

2062 AliasedReturnsRows._traverse_internals

2063 + [

2064 ("_cte_alias", InternalTraversal.dp_clauseelement),

2065 ("_restates", InternalTraversal.dp_clauseelement),

2066 ("recursive", InternalTraversal.dp_boolean),

2067 ("nesting", InternalTraversal.dp_boolean),

2068 ]

2069 + HasPrefixes._has_prefixes_traverse_internals

2070 + HasSuffixes._has_suffixes_traverse_internals

2071 )

2072

2073 element: HasCTE

2074

2075 @classmethod

2076 def _factory(

2077 cls,

2078 selectable: HasCTE,

2079 name: Optional[str] = None,

2080 recursive: bool = False,

2081 ) -> CTE:

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

2083 or Common Table Expression instance.

2084

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

2086

2087 """

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

2089 name=name, recursive=recursive

2090 )

2091

2092 def _init(

2093 self,

2094 selectable: Select[Unpack[TupleAny]],

2095 *,

2096 name: Optional[str] = None,

2097 recursive: bool = False,

2098 nesting: bool = False,

2099 _cte_alias: Optional[CTE] = None,

2100 _restates: Optional[CTE] = None,

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

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

2103 ) -> None:

2104 self.recursive = recursive

2105 self.nesting = nesting

2106 self._cte_alias = _cte_alias

2107 # Keep recursivity reference with union/union_all

2108 self._restates = _restates

2109 if _prefixes:

2110 self._prefixes = _prefixes

2111 if _suffixes:

2112 self._suffixes = _suffixes

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

2114

2115 def _populate_column_collection(self) -> None:

2116 if self._cte_alias is not None:

2117 self._cte_alias._generate_fromclause_column_proxies(self)

2118 else:

2119 self.element._generate_fromclause_column_proxies(self)

2120

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

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

2123 :class:`_expression.CTE`.

2124

2125 This method is a CTE-specific specialization of the

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

2127

2128 .. seealso::

2129

2130 :ref:`tutorial_using_aliases`

2131

2132 :func:`_expression.alias`

2133

2134 """

2135 return CTE._construct(

2136 self.element,

2137 name=name,

2138 recursive=self.recursive,

2139 nesting=self.nesting,

2140 _cte_alias=self,

2141 _prefixes=self._prefixes,

2142 _suffixes=self._suffixes,

2143 )

2144

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

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

2147 of the original CTE against the given selectables provided

2148 as positional arguments.

2149

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

2151 UNION.

2152

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

2154

2155 .. seealso::

2156

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

2158

2159 """

2160 assert is_select_statement(

2161 self.element

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

2163

2164 return CTE._construct(

2165 self.element.union(*other),

2166 name=self.name,

2167 recursive=self.recursive,

2168 nesting=self.nesting,

2169 _restates=self,

2170 _prefixes=self._prefixes,

2171 _suffixes=self._suffixes,

2172 )

2173

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

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

2176 of the original CTE against the given selectables provided

2177 as positional arguments.

2178

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

2180 UNION.

2181

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

2183

2184 .. seealso::

2185

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

2187

2188 """

2189

2190 assert is_select_statement(

2191 self.element

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

2193

2194 return CTE._construct(

2195 self.element.union_all(*other),

2196 name=self.name,

2197 recursive=self.recursive,

2198 nesting=self.nesting,

2199 _restates=self,

2200 _prefixes=self._prefixes,

2201 _suffixes=self._suffixes,

2202 )

2203

2204 def _get_reference_cte(self) -> CTE:

2205 """

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

2207 Updated CTEs should still refer to the original CTE.

2208 This function returns this reference identifier.

2209 """

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

2211

2212

2213class _CTEOpts(NamedTuple):

2214 nesting: bool

2215

2216

2217class _ColumnsPlusNames(NamedTuple):

2218 required_label_name: Optional[str]

2219 """

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

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

2222 """

2223

2224 proxy_key: Optional[str]

2225 """

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

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

2228 select.selected_columns collection

2229 """

2230

2231 fallback_label_name: Optional[str]

2232 """

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

2234 we have to render a label even though

2235 required_label_name was not given

2236 """

2237

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

2239 """

2240 the ColumnElement itself

2241 """

2242

2243 repeated: bool

2244 """

2245 True if this is a duplicate of a previous column

2246 in the list of columns

2247 """

2248

2249

2250class SelectsRows(ReturnsRows):

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

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

2253

2254 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2255

2256 def _generate_columns_plus_names(

2257 self,

2258 anon_for_dupe_key: bool,

2259 cols: Optional[_SelectIterable] = None,

2260 ) -> List[_ColumnsPlusNames]:

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

2262 the compiler.

2263

2264 This is distinct from the _column_naming_convention generator that's

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

2266 different rules. the collection returned here calls upon the

2267 _column_naming_convention as well.

2268

2269 """

2270

2271 if cols is None:

2272 cols = self._all_selected_columns

2273

2274 key_naming_convention = SelectState._column_naming_convention(

2275 self._label_style

2276 )

2277

2278 names = {}

2279

2280 result: List[_ColumnsPlusNames] = []

2281 result_append = result.append

2282

2283 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2284 label_style_none = self._label_style is LABEL_STYLE_NONE

2285

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

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

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

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

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

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

2292 dedupe_hash = 1

2293

2294 for c in cols:

2295 repeated = False

2296

2297 if not c._render_label_in_columns_clause:

2298 effective_name = required_label_name = fallback_label_name = (

2299 None

2300 )

2301 elif label_style_none:

2302 if TYPE_CHECKING:

2303 assert is_column_element(c)

2304

2305 effective_name = required_label_name = None

2306 fallback_label_name = c._non_anon_label or c._anon_name_label

2307 else:

2308 if TYPE_CHECKING:

2309 assert is_column_element(c)

2310

2311 if table_qualified:

2312 required_label_name = effective_name = (

2313 fallback_label_name

2314 ) = c._tq_label

2315 else:

2316 effective_name = fallback_label_name = c._non_anon_label

2317 required_label_name = None

2318

2319 if effective_name is None:

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

2321 # not need _expression_label but it isn't

2322 # giving us a clue when to use anon_label instead

2323 expr_label = c._expression_label

2324 if expr_label is None:

2325 repeated = c._anon_name_label in names

2326 names[c._anon_name_label] = c

2327 effective_name = required_label_name = None

2328

2329 if repeated:

2330 # here, "required_label_name" is sent as

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

2332 if table_qualified:

2333 fallback_label_name = (

2334 c._dedupe_anon_tq_label_idx(dedupe_hash)

2335 )

2336 dedupe_hash += 1

2337 else:

2338 fallback_label_name = c._dedupe_anon_label_idx(

2339 dedupe_hash

2340 )

2341 dedupe_hash += 1

2342 else:

2343 fallback_label_name = c._anon_name_label

2344 else:

2345 required_label_name = effective_name = (

2346 fallback_label_name

2347 ) = expr_label

2348

2349 if effective_name is not None:

2350 if TYPE_CHECKING:

2351 assert is_column_element(c)

2352

2353 if effective_name in names:

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

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

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

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

2358 # different column under the same name. apply

2359 # disambiguating label

2360 if table_qualified:

2361 required_label_name = fallback_label_name = (

2362 c._anon_tq_label

2363 )

2364 else:

2365 required_label_name = fallback_label_name = (

2366 c._anon_name_label

2367 )

2368

2369 if anon_for_dupe_key and required_label_name in names:

2370 # here, c._anon_tq_label is definitely unique to

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

2372 # this should always be true.

2373 # this is also an infrequent codepath because

2374 # you need two levels of duplication to be here

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

2376

2377 # the column under the disambiguating label is

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

2379 # subsequent occurrences of the column so that the

2380 # original stays non-ambiguous

2381 if table_qualified:

2382 required_label_name = fallback_label_name = (

2383 c._dedupe_anon_tq_label_idx(dedupe_hash)

2384 )

2385 dedupe_hash += 1

2386 else:

2387 required_label_name = fallback_label_name = (

2388 c._dedupe_anon_label_idx(dedupe_hash)

2389 )

2390 dedupe_hash += 1

2391 repeated = True

2392 else:

2393 names[required_label_name] = c

2394 elif anon_for_dupe_key:

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

2396 # label so that the original stays non-ambiguous

2397 if table_qualified:

2398 required_label_name = fallback_label_name = (

2399 c._dedupe_anon_tq_label_idx(dedupe_hash)

2400 )

2401 dedupe_hash += 1

2402 else:

2403 required_label_name = fallback_label_name = (

2404 c._dedupe_anon_label_idx(dedupe_hash)

2405 )

2406 dedupe_hash += 1

2407 repeated = True

2408 else:

2409 names[effective_name] = c

2410

2411 result_append(

2412 _ColumnsPlusNames(

2413 required_label_name,

2414 key_naming_convention(c),

2415 fallback_label_name,

2416 c,

2417 repeated,

2418 )

2419 )

2420

2421 return result

2422

2423

2424class HasCTE(roles.HasCTERole, SelectsRows):

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

2426

2427 _has_ctes_traverse_internals: _TraverseInternalsType = [

2428 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2429 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2430 ]

2431

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

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

2434

2435 @_generative

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

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

2438

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

2440 the parent statement such that they will each be unconditionally

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

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

2443

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

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

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

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

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

2449 statement.

2450

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

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

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

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

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

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

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

2458 larger statement.

2459

2460 E.g.::

2461

2462 from sqlalchemy import table, column, select

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

2464

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

2466

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

2468

2469 Would render::

2470

2471 WITH anon_1 AS

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

2473 SELECT t.c1, t.c2

2474 FROM t

2475

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

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

2478 statement.

2479

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

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

2482

2483 from sqlalchemy import table, column

2484 from sqlalchemy.dialects.postgresql import insert

2485

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

2487

2488 delete_statement_cte = (

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

2490 )

2491

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

2493 update_statement = insert_stmt.on_conflict_do_update(

2494 index_elements=[t.c.c1],

2495 set_={

2496 "c1": insert_stmt.excluded.c1,

2497 "c2": insert_stmt.excluded.c2,

2498 },

2499 ).add_cte(delete_statement_cte)

2500

2501 print(update_statement)

2502

2503 The above statement renders as::

2504

2505 WITH deletions AS

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

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

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

2509

2510 .. versionadded:: 1.4.21

2511

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

2513

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

2515

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

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

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

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

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

2521 this statement when this flag is given.

2522

2523 .. versionadded:: 2.0

2524

2525 .. seealso::

2526

2527 :paramref:`.HasCTE.cte.nesting`

2528

2529

2530 """

2531 opt = _CTEOpts(

2532 nest_here,

2533 )

2534 for cte in ctes:

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

2536 self._independent_ctes += (cte,)

2537 self._independent_ctes_opts += (opt,)

2538 return self

2539

2540 def cte(

2541 self,

2542 name: Optional[str] = None,

2543 recursive: bool = False,

2544 nesting: bool = False,

2545 ) -> CTE:

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

2547 or Common Table Expression instance.

2548

2549 Common table expressions are a SQL standard whereby SELECT

2550 statements can draw upon secondary statements specified along

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

2552 Special semantics regarding UNION can also be employed to

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

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

2555

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

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

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

2559 CTE rows.

2560

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

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

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

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

2565

2566 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2568 method may be

2569 used to establish these.

2570

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

2572 In particular - MATERIALIZED and NOT MATERIALIZED.

2573

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

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

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

2577 compile time.

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

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

2580 conjunction with UNION ALL in order to derive rows

2581 from those already selected.

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

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

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

2585 :paramref:`.HasCTE.add_cte.nest_here`

2586 parameter may also be used to more carefully

2587 control the exact placement of a particular CTE.

2588

2589 .. versionadded:: 1.4.24

2590

2591 .. seealso::

2592

2593 :meth:`.HasCTE.add_cte`

2594

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

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

2597 as well as additional examples.

2598

2599 Example 1, non recursive::

2600

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

2602 MetaData, select, func)

2603

2604 metadata = MetaData()

2605

2606 orders = Table('orders', metadata,

2607 Column('region', String),

2608 Column('amount', Integer),

2609 Column('product', String),

2610 Column('quantity', Integer)

2611 )

2612

2613 regional_sales = select(

2614 orders.c.region,

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

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

2617

2618

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

2620 where(

2621 regional_sales.c.total_sales >

2622 select(

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

2624 )

2625 ).cte("top_regions")

2626

2627 statement = select(

2628 orders.c.region,

2629 orders.c.product,

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

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

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

2633 select(top_regions.c.region)

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

2635

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

2637

2638 Example 2, WITH RECURSIVE::

2639

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

2641 MetaData, select, func)

2642

2643 metadata = MetaData()

2644

2645 parts = Table('parts', metadata,

2646 Column('part', String),

2647 Column('sub_part', String),

2648 Column('quantity', Integer),

2649 )

2650

2651 included_parts = select(\

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

2653 ).\

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

2655 cte(recursive=True)

2656

2657

2658 incl_alias = included_parts.alias()

2659 parts_alias = parts.alias()

2660 included_parts = included_parts.union_all(

2661 select(

2662 parts_alias.c.sub_part,

2663 parts_alias.c.part,

2664 parts_alias.c.quantity

2665 ).\

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

2667 )

2668

2669 statement = select(

2670 included_parts.c.sub_part,

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

2672 label('total_quantity')

2673 ).\

2674 group_by(included_parts.c.sub_part)

2675

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

2677

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

2679

2680 from datetime import date

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

2682 Date, select, literal, and_, exists)

2683

2684 metadata = MetaData()

2685

2686 visitors = Table('visitors', metadata,

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

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

2689 Column('count', Integer),

2690 )

2691

2692 # add 5 visitors for the product_id == 1

2693 product_id = 1

2694 day = date.today()

2695 count = 5

2696

2697 update_cte = (

2698 visitors.update()

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

2700 visitors.c.date == day))

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

2702 .returning(literal(1))

2703 .cte('update_cte')

2704 )

2705

2706 upsert = visitors.insert().from_select(

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

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

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

2710 )

2711

2712 connection.execute(upsert)

2713

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

2715

2716 value_a = select(

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

2718 ).cte("value_a")

2719

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

2721 value_a_nested = select(

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

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

2724

2725 # Nesting CTEs takes ascendency locally

2726 # over the CTEs at a higher level

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

2728

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

2730

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

2732 shown with inline parameters below as::

2733

2734 WITH

2735 value_a AS

2736 (SELECT 'root' AS n),

2737 value_b AS

2738 (WITH value_a AS

2739 (SELECT 'nesting' AS n)

2740 SELECT value_a.n AS n FROM value_a)

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

2742 FROM value_a, value_b

2743

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

2745 as follows (SQLAlchemy 2.0 and above)::

2746

2747 value_a = select(

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

2749 ).cte("value_a")

2750

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

2752 value_a_nested = select(

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

2754 ).cte("value_a")

2755

2756 # Nesting CTEs takes ascendency locally

2757 # over the CTEs at a higher level

2758 value_b = (

2759 select(value_a_nested.c.n).

2760 add_cte(value_a_nested, nest_here=True).

2761 cte("value_b")

2762 )

2763

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

2765

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

2767

2768 edge = Table(

2769 "edge",

2770 metadata,

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

2772 Column("left", Integer),

2773 Column("right", Integer),

2774 )

2775

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

2777 "nodes", recursive=True

2778 )

2779

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

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

2782 )

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

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

2785 )

2786

2787 subgraph_cte = root_node.union(left_edge, right_edge)

2788

2789 subgraph = select(subgraph_cte)

2790

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

2792

2793 WITH RECURSIVE nodes(node) AS (

2794 SELECT 1 AS node

2795 UNION

2796 SELECT edge."left" AS "left"

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

2798 UNION

2799 SELECT edge."right" AS "right"

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

2801 )

2802 SELECT nodes.node FROM nodes

2803

2804 .. seealso::

2805

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

2807 :meth:`_expression.HasCTE.cte`.

2808

2809 """

2810 return CTE._construct(

2811 self, name=name, recursive=recursive, nesting=nesting

2812 )

2813

2814

2815class Subquery(AliasedReturnsRows):

2816 """Represent a subquery of a SELECT.

2817

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

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

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

2821 :class:`_expression.SelectBase` subclass

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

2823 :class:`_expression.CompoundSelect`, and

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

2825 it represents the

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

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

2828

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

2830 :class:`_expression.Alias`

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

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

2833 :class:`_expression.Alias` always

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

2835 :class:`.Subquery`

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

2837

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

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

2840 statement.

2841

2842 """

2843

2844 __visit_name__ = "subquery"

2845

2846 _is_subquery = True

2847

2848 inherit_cache = True

2849

2850 element: SelectBase

2851

2852 @classmethod

2853 def _factory(

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

2855 ) -> Subquery:

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

2857

2858 return coercions.expect(

2859 roles.SelectStatementRole, selectable

2860 ).subquery(name=name)

2861

2862 @util.deprecated(

2863 "1.4",

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

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

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

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

2868 ":func:`_expression.select` "

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

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

2871 )

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

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

2874

2875

2876class FromGrouping(GroupedElement, FromClause):

2877 """Represent a grouping of a FROM clause"""

2878

2879 _traverse_internals: _TraverseInternalsType = [

2880 ("element", InternalTraversal.dp_clauseelement)

2881 ]

2882

2883 element: FromClause

2884

2885 def __init__(self, element: FromClause):

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

2887

2888 def _init_collections(self) -> None:

2889 pass

2890

2891 @util.ro_non_memoized_property

2892 def columns(

2893 self,

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

2895 return self.element.columns

2896

2897 @util.ro_non_memoized_property

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

2899 return self.element.columns

2900

2901 @property

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

2903 return self.element.primary_key

2904

2905 @property

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

2907 return self.element.foreign_keys

2908

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

2910 return self.element.is_derived_from(fromclause)

2911

2912 def alias(

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

2914 ) -> NamedFromGrouping:

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

2916

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

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

2919

2920 @util.ro_non_memoized_property

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

2922 return self.element._hide_froms

2923

2924 @util.ro_non_memoized_property

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

2926 return self.element._from_objects

2927

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

2929 return {"element": self.element}

2930

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

2932 self.element = state["element"]

2933

2934 if TYPE_CHECKING:

2935

2936 def self_group(

2937 self, against: Optional[OperatorType] = None

2938 ) -> Self: ...

2939

2940

2941class NamedFromGrouping(FromGrouping, NamedFromClause):

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

2943

2944 .. versionadded:: 2.0

2945

2946 """

2947

2948 inherit_cache = True

2949

2950 if TYPE_CHECKING:

2951

2952 def self_group(

2953 self, against: Optional[OperatorType] = None

2954 ) -> Self: ...

2955

2956

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

2958 """Represents a minimal "table" construct.

2959

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

2961 collection of columns, which are typically produced

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

2963

2964 from sqlalchemy import table, column

2965

2966 user = table("user",

2967 column("id"),

2968 column("name"),

2969 column("description"),

2970 )

2971

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

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

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

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

2976

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

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

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

2980 It's useful

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

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

2983 is not on hand.

2984

2985 """

2986

2987 __visit_name__ = "table"

2988

2989 _traverse_internals: _TraverseInternalsType = [

2990 (

2991 "columns",

2992 InternalTraversal.dp_fromclause_canonical_column_collection,

2993 ),

2994 ("name", InternalTraversal.dp_string),

2995 ("schema", InternalTraversal.dp_string),

2996 ]

2997

2998 _is_table = True

2999

3000 fullname: str

3001

3002 implicit_returning = False

3003 """:class:`_expression.TableClause`

3004 doesn't support having a primary key or column

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

3006

3007 @util.ro_memoized_property

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

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

3010 return None

3011

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

3013 super().__init__()

3014 self.name = name

3015 self._columns = DedupeColumnCollection()

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

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

3018 for c in columns:

3019 self.append_column(c)

3020

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

3022 if schema is not None:

3023 self.schema = schema

3024 if self.schema is not None:

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

3026 else:

3027 self.fullname = self.name

3028 if kw:

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

3030

3031 if TYPE_CHECKING:

3032

3033 @util.ro_non_memoized_property

3034 def columns(

3035 self,

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

3037

3038 @util.ro_non_memoized_property

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

3040

3041 def __str__(self) -> str:

3042 if self.schema is not None:

3043 return self.schema + "." + self.name

3044 else:

3045 return self.name

3046

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

3048 pass

3049

3050 def _init_collections(self) -> None:

3051 pass

3052

3053 @util.ro_memoized_property

3054 def description(self) -> str:

3055 return self.name

3056

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

3058 existing = c.table

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

3060 raise exc.ArgumentError(

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

3062 % (c.key, existing)

3063 )

3064

3065 self._columns.add(c)

3066 c.table = self

3067

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

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

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

3071 :class:`_expression.TableClause`.

3072

3073 E.g.::

3074

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

3076

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

3078

3079 """

3080

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

3082

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

3084 def update(self) -> Update:

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

3086 :class:`_expression.TableClause`.

3087

3088 E.g.::

3089

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

3091

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

3093

3094 """

3095 return util.preloaded.sql_dml.Update(

3096 self,

3097 )

3098

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

3100 def delete(self) -> Delete:

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

3102 :class:`_expression.TableClause`.

3103

3104 E.g.::

3105

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

3107

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

3109

3110 """

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

3112

3113 @util.ro_non_memoized_property

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

3115 return [self]

3116

3117

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

3119

3120

3121class ForUpdateArg(ClauseElement):

3122 _traverse_internals: _TraverseInternalsType = [

3123 ("of", InternalTraversal.dp_clauseelement_list),

3124 ("nowait", InternalTraversal.dp_boolean),

3125 ("read", InternalTraversal.dp_boolean),

3126 ("skip_locked", InternalTraversal.dp_boolean),

3127 ("key_share", InternalTraversal.dp_boolean),

3128 ]

3129

3130 of: Optional[Sequence[ClauseElement]]

3131 nowait: bool

3132 read: bool

3133 skip_locked: bool

3134

3135 @classmethod

3136 def _from_argument(

3137 cls, with_for_update: ForUpdateParameter

3138 ) -> Optional[ForUpdateArg]:

3139 if isinstance(with_for_update, ForUpdateArg):

3140 return with_for_update

3141 elif with_for_update in (None, False):

3142 return None

3143 elif with_for_update is True:

3144 return ForUpdateArg()

3145 else:

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

3147

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

3149 return (

3150 isinstance(other, ForUpdateArg)

3151 and other.nowait == self.nowait

3152 and other.read == self.read

3153 and other.skip_locked == self.skip_locked

3154 and other.key_share == self.key_share

3155 and other.of is self.of

3156 )

3157

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

3159 return not self.__eq__(other)

3160

3161 def __hash__(self) -> int:

3162 return id(self)

3163

3164 def __init__(

3165 self,

3166 *,

3167 nowait: bool = False,

3168 read: bool = False,

3169 of: Optional[_ForUpdateOfArgument] = None,

3170 skip_locked: bool = False,

3171 key_share: bool = False,

3172 ):

3173 """Represents arguments specified to

3174 :meth:`_expression.Select.for_update`.

3175

3176 """

3177

3178 self.nowait = nowait

3179 self.read = read

3180 self.skip_locked = skip_locked

3181 self.key_share = key_share

3182 if of is not None:

3183 self.of = [

3184 coercions.expect(roles.ColumnsClauseRole, elem)

3185 for elem in util.to_list(of)

3186 ]

3187 else:

3188 self.of = None

3189

3190

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

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

3193 in a statement.

3194

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

3196 :func:`_expression.values` function.

3197

3198 .. versionadded:: 1.4

3199

3200 """

3201

3202 __visit_name__ = "values"

3203

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

3205

3206 _unnamed: bool

3207 _traverse_internals: _TraverseInternalsType = [

3208 ("_column_args", InternalTraversal.dp_clauseelement_list),

3209 ("_data", InternalTraversal.dp_dml_multi_values),

3210 ("name", InternalTraversal.dp_string),

3211 ("literal_binds", InternalTraversal.dp_boolean),

3212 ]

3213

3214 def __init__(

3215 self,

3216 *columns: ColumnClause[Any],

3217 name: Optional[str] = None,

3218 literal_binds: bool = False,

3219 ):

3220 super().__init__()

3221 self._column_args = columns

3222

3223 if name is None:

3224 self._unnamed = True

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

3226 else:

3227 self._unnamed = False

3228 self.name = name

3229 self.literal_binds = literal_binds

3230 self.named_with_column = not self._unnamed

3231

3232 @property

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

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

3235

3236 @_generative

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

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

3239 construct that is a copy of this

3240 one with the given name.

3241

3242 This method is a VALUES-specific specialization of the

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

3244

3245 .. seealso::

3246

3247 :ref:`tutorial_using_aliases`

3248

3249 :func:`_expression.alias`

3250

3251 """

3252 non_none_name: str

3253

3254 if name is None:

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

3256 else:

3257 non_none_name = name

3258

3259 self.name = non_none_name

3260 self.named_with_column = True

3261 self._unnamed = False

3262 return self

3263

3264 @_generative

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

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

3267 so that

3268 it renders as LATERAL.

3269

3270 .. seealso::

3271

3272 :func:`_expression.lateral`

3273

3274 """

3275 non_none_name: str

3276

3277 if name is None:

3278 non_none_name = self.name

3279 else:

3280 non_none_name = name

3281

3282 self._is_lateral = True

3283 self.name = non_none_name

3284 self._unnamed = False

3285 return self

3286

3287 @_generative

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

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

3290 adding the given data to the data list.

3291

3292 E.g.::

3293

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

3295

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

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

3298 constructor.

3299

3300 """

3301

3302 self._data += (values,)

3303 return self

3304

3305 def scalar_values(self) -> ScalarValues:

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

3307 COLUMN element in a statement.

3308

3309 .. versionadded:: 2.0.0b4

3310

3311 """

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

3313

3314 def _populate_column_collection(self) -> None:

3315 for c in self._column_args:

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

3317 _, c = c._make_proxy(self)

3318 else:

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

3320 # no memoizations of other FROM clauses.

3321 # see test_values.py -> test_auto_proxy_select_direct_col

3322 c._reset_memoizations()

3323 self._columns.add(c)

3324 c.table = self

3325

3326 @util.ro_non_memoized_property

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

3328 return [self]

3329

3330

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

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

3333 COLUMN element in a statement.

3334

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

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

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

3338 an ``IN`` or ``NOT IN`` condition.

3339

3340 .. versionadded:: 2.0.0b4

3341

3342 """

3343

3344 __visit_name__ = "scalar_values"

3345

3346 _traverse_internals: _TraverseInternalsType = [

3347 ("_column_args", InternalTraversal.dp_clauseelement_list),

3348 ("_data", InternalTraversal.dp_dml_multi_values),

3349 ("literal_binds", InternalTraversal.dp_boolean),

3350 ]

3351

3352 def __init__(

3353 self,

3354 columns: Sequence[ColumnClause[Any]],

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

3356 literal_binds: bool,

3357 ):

3358 super().__init__()

3359 self._column_args = columns

3360 self._data = data

3361 self.literal_binds = literal_binds

3362

3363 @property

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

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

3366

3367 def __clause_element__(self) -> ScalarValues:

3368 return self

3369

3370 if TYPE_CHECKING:

3371

3372 def self_group(

3373 self, against: Optional[OperatorType] = None

3374 ) -> Self: ...

3375

3376

3377class SelectBase(

3378 roles.SelectStatementRole,

3379 roles.DMLSelectRole,

3380 roles.CompoundElementRole,

3381 roles.InElementRole,

3382 HasCTE,

3383 SupportsCloneAnnotations,

3384 Selectable,

3385):

3386 """Base class for SELECT statements.

3387

3388

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

3390 :class:`_expression.CompoundSelect` and

3391 :class:`_expression.TextualSelect`.

3392

3393

3394 """

3395

3396 _is_select_base = True

3397 is_select = True

3398

3399 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3400

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

3402 self._reset_memoizations()

3403

3404 @util.ro_non_memoized_property

3405 def selected_columns(

3406 self,

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

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

3409 representing the columns that

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

3411

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

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

3414 within this collection cannot be directly nested inside another SELECT

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

3416 necessary parenthesization required by SQL.

3417

3418 .. note::

3419

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

3421 include expressions established in the columns clause using the

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

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

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

3425 construct.

3426

3427 .. seealso::

3428

3429 :attr:`_sql.Select.selected_columns`

3430

3431 .. versionadded:: 1.4

3432

3433 """

3434 raise NotImplementedError()

3435

3436 def _generate_fromclause_column_proxies(

3437 self,

3438 subquery: FromClause,

3439 *,

3440 proxy_compound_columns: Optional[

3441 Iterable[Sequence[ColumnElement[Any]]]

3442 ] = None,

3443 ) -> None:

3444 raise NotImplementedError()

3445

3446 @util.ro_non_memoized_property

3447 def _all_selected_columns(self) -> _SelectIterable:

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

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

3450 constructs.

3451

3452 .. versionadded:: 1.4.12

3453

3454 .. seealso::

3455

3456 :attr:`_sql.SelectBase.exported_columns`

3457

3458 """

3459 raise NotImplementedError()

3460

3461 @property

3462 def exported_columns(

3463 self,

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

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

3466 that represents the "exported"

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

3468 :class:`_sql.TextClause` constructs.

3469

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

3471 object are synonymous

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

3473

3474 .. versionadded:: 1.4

3475

3476 .. seealso::

3477

3478 :attr:`_expression.Select.exported_columns`

3479

3480 :attr:`_expression.Selectable.exported_columns`

3481

3482 :attr:`_expression.FromClause.exported_columns`

3483

3484

3485 """

3486 return self.selected_columns.as_readonly()

3487

3488 @property

3489 @util.deprecated(

3490 "1.4",

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

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

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

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

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

3496 "first in order to create "

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

3498 "columns that this SELECT object SELECTs "

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

3500 "attribute.",

3501 )

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

3503 return self._implicit_subquery.columns

3504

3505 @property

3506 def columns(

3507 self,

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

3509 return self.c

3510

3511 def get_label_style(self) -> SelectLabelStyle:

3512 """

3513 Retrieve the current label style.

3514

3515 Implemented by subclasses.

3516

3517 """

3518 raise NotImplementedError()

3519

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

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

3522

3523 Implemented by subclasses.

3524

3525 """

3526

3527 raise NotImplementedError()

3528

3529 @util.deprecated(

3530 "1.4",

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

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

3533 "creates a subquery that should be explicit. "

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

3535 "first in order to create "

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

3537 )

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

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

3540

3541 @HasMemoized.memoized_attribute

3542 def _implicit_subquery(self) -> Subquery:

3543 return self.subquery()

3544

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

3546 raise NotImplementedError()

3547

3548 @util.deprecated(

3549 "1.4",

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

3551 "method is deprecated and will be "

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

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

3554 )

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

3556 return self.scalar_subquery()

3557

3558 def exists(self) -> Exists:

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

3560 which can be used as a column expression.

3561

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

3563

3564 .. seealso::

3565

3566 :func:`_sql.exists`

3567

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

3569

3570 .. versionadded:: 1.4

3571

3572 """

3573 return Exists(self)

3574

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

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

3577 used as a column expression.

3578

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

3580

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

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

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

3584 an enclosing SELECT.

3585

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

3587 subquery that can be produced using the

3588 :meth:`_expression.SelectBase.subquery`

3589 method.

3590

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

3592 :meth:`_expression.SelectBase.scalar_subquery`.

3593

3594 .. seealso::

3595

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

3597

3598 """

3599 if self._label_style is not LABEL_STYLE_NONE:

3600 self = self.set_label_style(LABEL_STYLE_NONE)

3601

3602 return ScalarSelect(self)

3603

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

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

3606 subquery with a label.

3607

3608 .. seealso::

3609

3610 :meth:`_expression.SelectBase.scalar_subquery`.

3611

3612 """

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

3614

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

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

3617

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

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

3620

3621 .. seealso::

3622

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

3624

3625 """

3626 return Lateral._factory(self, name)

3627

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

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

3630

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

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

3633 SELECT statement.

3634

3635 Given a SELECT statement such as::

3636

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

3638

3639 The above statement might look like::

3640

3641 SELECT table.id, table.name FROM table

3642

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

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

3645 a named sub-element::

3646

3647 subq = stmt.subquery()

3648 new_stmt = select(subq)

3649

3650 The above renders as::

3651

3652 SELECT anon_1.id, anon_1.name

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

3654

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

3656 is equivalent to calling

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

3658 method on a FROM object; however,

3659 as a :class:`_expression.SelectBase`

3660 object is not directly FROM object,

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

3662 method provides clearer semantics.

3663

3664 .. versionadded:: 1.4

3665

3666 """

3667

3668 return Subquery._construct(

3669 self._ensure_disambiguated_names(), name=name

3670 )

3671

3672 def _ensure_disambiguated_names(self) -> Self:

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

3674 disambiguated in some way, if possible.

3675

3676 """

3677

3678 raise NotImplementedError()

3679

3680 def alias(

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

3682 ) -> Subquery:

3683 """Return a named subquery against this

3684 :class:`_expression.SelectBase`.

3685

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

3687 :class:`_expression.FromClause`),

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

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

3690 :class:`_expression.FromClause`.

3691

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

3693 method is now

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

3695

3696 """

3697 return self.subquery(name=name)

3698

3699

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

3701

3702

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

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

3705

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

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

3708 compound selects.

3709

3710 """

3711

3712 __visit_name__ = "select_statement_grouping"

3713 _traverse_internals: _TraverseInternalsType = [

3714 ("element", InternalTraversal.dp_clauseelement)

3715 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3716

3717 _is_select_container = True

3718

3719 element: _SB

3720

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

3722 self.element = cast(

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

3724 )

3725

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

3727 new_element = self.element._ensure_disambiguated_names()

3728 if new_element is not self.element:

3729 return SelectStatementGrouping(new_element)

3730 else:

3731 return self

3732

3733 def get_label_style(self) -> SelectLabelStyle:

3734 return self.element.get_label_style()

3735

3736 def set_label_style(

3737 self, label_style: SelectLabelStyle

3738 ) -> SelectStatementGrouping[_SB]:

3739 return SelectStatementGrouping(

3740 self.element.set_label_style(label_style)

3741 )

3742

3743 @property

3744 def select_statement(self) -> _SB:

3745 return self.element

3746

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

3748 return self

3749

3750 if TYPE_CHECKING:

3751

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

3753

3754 # def _generate_columns_plus_names(

3755 # self, anon_for_dupe_key: bool

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

3757 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3758

3759 def _generate_fromclause_column_proxies(

3760 self,

3761 subquery: FromClause,

3762 *,

3763 proxy_compound_columns: Optional[

3764 Iterable[Sequence[ColumnElement[Any]]]

3765 ] = None,

3766 ) -> None:

3767 self.element._generate_fromclause_column_proxies(

3768 subquery, proxy_compound_columns=proxy_compound_columns

3769 )

3770

3771 @util.ro_non_memoized_property

3772 def _all_selected_columns(self) -> _SelectIterable:

3773 return self.element._all_selected_columns

3774

3775 @util.ro_non_memoized_property

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

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

3778 representing the columns that

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

3780 :class:`_sql.TextClause` constructs.

3781

3782 .. versionadded:: 1.4

3783

3784 .. seealso::

3785

3786 :attr:`_sql.Select.selected_columns`

3787

3788 """

3789 return self.element.selected_columns

3790

3791 @util.ro_non_memoized_property

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

3793 return self.element._from_objects

3794

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

3796 # SelectStatementGrouping not generative: has no attribute '_generate'

3797 raise NotImplementedError

3798

3799

3800class GenerativeSelect(SelectBase, Generative):

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

3802 added.

3803

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

3805 :class:`_expression.CompoundSelect`

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

3807 rendering can be controlled. Compare to

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

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

3810 and is also a SELECT construct,

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

3812 only wrapped as a subquery.

3813

3814 """

3815

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

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

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

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

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

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

3822 _for_update_arg: Optional[ForUpdateArg] = None

3823

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

3825 self._label_style = _label_style

3826

3827 @_generative

3828 def with_for_update(

3829 self,

3830 *,

3831 nowait: bool = False,

3832 read: bool = False,

3833 of: Optional[_ForUpdateOfArgument] = None,

3834 skip_locked: bool = False,

3835 key_share: bool = False,

3836 ) -> Self:

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

3838 :class:`_expression.GenerativeSelect`.

3839

3840 E.g.::

3841

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

3843

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

3845 statement like::

3846

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

3848

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

3850 would produce::

3851

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

3853

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

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

3856 provided which allow for common database-specific

3857 variants.

3858

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

3860 and PostgreSQL dialects.

3861

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

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

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

3865

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

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

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

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

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

3871 column depending on backend.

3872

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

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

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

3876

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

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

3879 on the PostgreSQL dialect.

3880

3881 """

3882 self._for_update_arg = ForUpdateArg(

3883 nowait=nowait,

3884 read=read,

3885 of=of,

3886 skip_locked=skip_locked,

3887 key_share=key_share,

3888 )

3889 return self

3890

3891 def get_label_style(self) -> SelectLabelStyle:

3892 """

3893 Retrieve the current label style.

3894

3895 .. versionadded:: 1.4

3896

3897 """

3898 return self._label_style

3899

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

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

3902

3903 There are three "label styles" available,

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

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

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

3907 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

3908

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

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

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

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

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

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

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

3916 that the impact of this labeling is minimal.

3917

3918 The rationale for disambiguation is mostly so that all column

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

3920 collection when a subquery is created.

3921

3922 .. versionadded:: 1.4 - the

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

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

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

3926

3927 .. seealso::

3928

3929 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

3930

3931 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

3932

3933 :data:`_sql.LABEL_STYLE_NONE`

3934

3935 :data:`_sql.LABEL_STYLE_DEFAULT`

3936

3937 """

3938 if self._label_style is not style:

3939 self = self._generate()

3940 self._label_style = style

3941 return self

3942

3943 @property

3944 def _group_by_clause(self) -> ClauseList:

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

3946 return ClauseList._construct_raw(

3947 operators.comma_op, self._group_by_clauses

3948 )

3949

3950 @property

3951 def _order_by_clause(self) -> ClauseList:

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

3953 return ClauseList._construct_raw(

3954 operators.comma_op, self._order_by_clauses

3955 )

3956

3957 def _offset_or_limit_clause(

3958 self,

3959 element: _LimitOffsetType,

3960 name: Optional[str] = None,

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

3962 ) -> ColumnElement[Any]:

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

3964

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

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

3967

3968 """

3969 return coercions.expect(

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

3971 )

3972

3973 @overload

3974 def _offset_or_limit_clause_asint(

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

3976 ) -> NoReturn: ...

3977

3978 @overload

3979 def _offset_or_limit_clause_asint(

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

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

3982

3983 def _offset_or_limit_clause_asint(

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

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

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

3987 integer.

3988

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

3990 parameter. Otherwise, a compilation error is raised.

3991

3992 """

3993 if clause is None:

3994 return None

3995 try:

3996 value = clause._limit_offset_value

3997 except AttributeError as err:

3998 raise exc.CompileError(

3999 "This SELECT structure does not use a simple "

4000 "integer value for %s" % attrname

4001 ) from err

4002 else:

4003 return util.asint(value)

4004

4005 @property

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

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

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

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

4010 isn't currently set to an integer.

4011

4012 """

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

4014

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

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

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

4018 """

4019 return isinstance(clause, _OffsetLimitParam)

4020

4021 @property

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

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

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

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

4026 offset isn't currently set to an integer.

4027

4028 """

4029 return self._offset_or_limit_clause_asint(

4030 self._offset_clause, "offset"

4031 )

4032

4033 @property

4034 def _has_row_limiting_clause(self) -> bool:

4035 return (

4036 self._limit_clause is not None

4037 or self._offset_clause is not None

4038 or self._fetch_clause is not None

4039 )

4040

4041 @_generative

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

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

4044 applied.

4045

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

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

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

4049 functionality.

4050

4051 .. note::

4052

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

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

4055

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

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

4058

4059 .. seealso::

4060

4061 :meth:`_sql.GenerativeSelect.fetch`

4062

4063 :meth:`_sql.GenerativeSelect.offset`

4064

4065 """

4066

4067 self._fetch_clause = self._fetch_clause_options = None

4068 self._limit_clause = self._offset_or_limit_clause(limit)

4069 return self

4070

4071 @_generative

4072 def fetch(

4073 self,

4074 count: _LimitOffsetType,

4075 with_ties: bool = False,

4076 percent: bool = False,

4077 ) -> Self:

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

4079 applied.

4080

4081 This is a numeric value which usually renders as

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

4083 expression in the resulting select. This functionality is

4084 is currently implemented for Oracle, PostgreSQL, MSSQL.

4085

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

4087

4088 .. note::

4089

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

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

4092

4093 .. versionadded:: 1.4

4094

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

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

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

4098 Pass ``None`` to reset it.

4099

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

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

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

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

4104

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

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

4107

4108 .. seealso::

4109

4110 :meth:`_sql.GenerativeSelect.limit`

4111

4112 :meth:`_sql.GenerativeSelect.offset`

4113

4114 """

4115

4116 self._limit_clause = None

4117 if count is None:

4118 self._fetch_clause = self._fetch_clause_options = None

4119 else:

4120 self._fetch_clause = self._offset_or_limit_clause(count)

4121 self._fetch_clause_options = {

4122 "with_ties": with_ties,

4123 "percent": percent,

4124 }

4125 return self

4126

4127 @_generative

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

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

4130 applied.

4131

4132

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

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

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

4136 functionality.

4137

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

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

4140

4141 .. seealso::

4142

4143 :meth:`_sql.GenerativeSelect.limit`

4144

4145 :meth:`_sql.GenerativeSelect.fetch`

4146

4147 """

4148

4149 self._offset_clause = self._offset_or_limit_clause(offset)

4150 return self

4151

4152 @_generative

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

4154 def slice(

4155 self,

4156 start: int,

4157 stop: int,

4158 ) -> Self:

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

4160

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

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

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

4164 query.

4165

4166 For example, ::

4167

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

4169

4170 renders as

4171

4172 .. sourcecode:: sql

4173

4174 SELECT users.id AS users_id,

4175 users.name AS users_name

4176 FROM users ORDER BY users.id

4177 LIMIT ? OFFSET ?

4178 (2, 1)

4179

4180 .. note::

4181

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

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

4184

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

4186 method generalized from the ORM.

4187

4188 .. seealso::

4189

4190 :meth:`_sql.GenerativeSelect.limit`

4191

4192 :meth:`_sql.GenerativeSelect.offset`

4193

4194 :meth:`_sql.GenerativeSelect.fetch`

4195

4196 """

4197 sql_util = util.preloaded.sql_util

4198 self._fetch_clause = self._fetch_clause_options = None

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

4200 self._limit_clause, self._offset_clause, start, stop

4201 )

4202 return self

4203

4204 @_generative

4205 def order_by(

4206 self,

4207 __first: Union[

4208 Literal[None, _NoArg.NO_ARG],

4209 _ColumnExpressionOrStrLabelArgument[Any],

4210 ] = _NoArg.NO_ARG,

4211 /,

4212 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4213 ) -> Self:

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

4215 criteria applied.

4216

4217 e.g.::

4218

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

4220

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

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

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

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

4225

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

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

4228

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

4230 constructs

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

4232

4233 .. seealso::

4234

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

4236

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

4238

4239 """

4240

4241 if not clauses and __first is None:

4242 self._order_by_clauses = ()

4243 elif __first is not _NoArg.NO_ARG:

4244 self._order_by_clauses += tuple(

4245 coercions.expect(

4246 roles.OrderByRole, clause, apply_propagate_attrs=self

4247 )

4248 for clause in (__first,) + clauses

4249 )

4250 return self

4251

4252 @_generative

4253 def group_by(

4254 self,

4255 __first: Union[

4256 Literal[None, _NoArg.NO_ARG],

4257 _ColumnExpressionOrStrLabelArgument[Any],

4258 ] = _NoArg.NO_ARG,

4259 /,

4260 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4261 ) -> Self:

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

4263 criterion applied.

4264

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

4266

4267 e.g.::

4268

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

4270 group_by(table.c.name)

4271

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

4273 constructs

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

4275

4276 .. seealso::

4277

4278 :ref:`tutorial_group_by_w_aggregates` - in the

4279 :ref:`unified_tutorial`

4280

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

4282

4283 """

4284

4285 if not clauses and __first is None:

4286 self._group_by_clauses = ()

4287 elif __first is not _NoArg.NO_ARG:

4288 self._group_by_clauses += tuple(

4289 coercions.expect(

4290 roles.GroupByRole, clause, apply_propagate_attrs=self

4291 )

4292 for clause in (__first,) + clauses

4293 )

4294 return self

4295

4296

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

4298class CompoundSelectState(CompileState):

4299 @util.memoized_property

4300 def _label_resolve_dict(

4301 self,

4302 ) -> Tuple[

4303 Dict[str, ColumnElement[Any]],

4304 Dict[str, ColumnElement[Any]],

4305 Dict[str, ColumnElement[Any]],

4306 ]:

4307 # TODO: this is hacky and slow

4308 hacky_subquery = self.statement.subquery()

4309 hacky_subquery.named_with_column = False

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

4311 return d, d, d

4312

4313

4314class _CompoundSelectKeyword(Enum):

4315 UNION = "UNION"

4316 UNION_ALL = "UNION ALL"

4317 EXCEPT = "EXCEPT"

4318 EXCEPT_ALL = "EXCEPT ALL"

4319 INTERSECT = "INTERSECT"

4320 INTERSECT_ALL = "INTERSECT ALL"

4321

4322

4323class CompoundSelect(HasCompileState, GenerativeSelect, ExecutableReturnsRows):

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

4325 SELECT-based set operations.

4326

4327

4328 .. seealso::

4329

4330 :func:`_expression.union`

4331

4332 :func:`_expression.union_all`

4333

4334 :func:`_expression.intersect`

4335

4336 :func:`_expression.intersect_all`

4337

4338 :func:`_expression.except`

4339

4340 :func:`_expression.except_all`

4341

4342 """

4343

4344 __visit_name__ = "compound_select"

4345

4346 _traverse_internals: _TraverseInternalsType = (

4347 [

4348 ("selects", InternalTraversal.dp_clauseelement_list),

4349 ("_limit_clause", InternalTraversal.dp_clauseelement),

4350 ("_offset_clause", InternalTraversal.dp_clauseelement),

4351 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4352 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4353 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4354 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4355 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4356 ("keyword", InternalTraversal.dp_string),

4357 ]

4358 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4359 + HasCTE._has_ctes_traverse_internals

4360 )

4361

4362 selects: List[SelectBase]

4363

4364 _is_from_container = True

4365 _auto_correlate = False

4366

4367 def __init__(

4368 self,

4369 keyword: _CompoundSelectKeyword,

4370 *selects: _SelectStatementForCompoundArgument,

4371 ):

4372 self.keyword = keyword

4373 self.selects = [

4374 coercions.expect(

4375 roles.CompoundElementRole, s, apply_propagate_attrs=self

4376 ).self_group(against=self)

4377 for s in selects

4378 ]

4379

4380 GenerativeSelect.__init__(self)

4381

4382 @classmethod

4383 def _create_union(

4384 cls, *selects: _SelectStatementForCompoundArgument

4385 ) -> CompoundSelect:

4386 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4387

4388 @classmethod

4389 def _create_union_all(

4390 cls, *selects: _SelectStatementForCompoundArgument

4391 ) -> CompoundSelect:

4392 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4393

4394 @classmethod

4395 def _create_except(

4396 cls, *selects: _SelectStatementForCompoundArgument

4397 ) -> CompoundSelect:

4398 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4399

4400 @classmethod

4401 def _create_except_all(

4402 cls, *selects: _SelectStatementForCompoundArgument

4403 ) -> CompoundSelect:

4404 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4405

4406 @classmethod

4407 def _create_intersect(

4408 cls, *selects: _SelectStatementForCompoundArgument

4409 ) -> CompoundSelect:

4410 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4411

4412 @classmethod

4413 def _create_intersect_all(

4414 cls, *selects: _SelectStatementForCompoundArgument

4415 ) -> CompoundSelect:

4416 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4417

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

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

4420

4421 def self_group(

4422 self, against: Optional[OperatorType] = None

4423 ) -> GroupedElement:

4424 return SelectStatementGrouping(self)

4425

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

4427 for s in self.selects:

4428 if s.is_derived_from(fromclause):

4429 return True

4430 return False

4431

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

4433 if self._label_style is not style:

4434 self = self._generate()

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

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

4437

4438 return self

4439

4440 def _ensure_disambiguated_names(self) -> CompoundSelect:

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

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

4443 self = self._generate()

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

4445

4446 return self

4447

4448 def _generate_fromclause_column_proxies(

4449 self,

4450 subquery: FromClause,

4451 *,

4452 proxy_compound_columns: Optional[

4453 Iterable[Sequence[ColumnElement[Any]]]

4454 ] = None,

4455 ) -> None:

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

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

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

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

4460 # generate below.

4461 select_0 = self.selects[0]

4462

4463 if self._label_style is not LABEL_STYLE_DEFAULT:

4464 select_0 = select_0.set_label_style(self._label_style)

4465

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

4467 # derived columns place a 'weight' annotation corresponding

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

4469 # that the corresponding_column() operation can resolve

4470 # conflicts

4471 extra_col_iterator = zip(

4472 *[

4473 [

4474 c._annotate(dd)

4475 for c in stmt._all_selected_columns

4476 if is_column_element(c)

4477 ]

4478 for dd, stmt in [

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

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

4481 ]

4482 ]

4483 )

4484

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

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

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

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

4489 # compound

4490 select_0._generate_fromclause_column_proxies(

4491 subquery, proxy_compound_columns=extra_col_iterator

4492 )

4493

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

4495 super()._refresh_for_new_column(column)

4496 for select in self.selects:

4497 select._refresh_for_new_column(column)

4498

4499 @util.ro_non_memoized_property

4500 def _all_selected_columns(self) -> _SelectIterable:

4501 return self.selects[0]._all_selected_columns

4502

4503 @util.ro_non_memoized_property

4504 def selected_columns(

4505 self,

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

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

4508 representing the columns that

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

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

4511

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

4513 :attr:`_expression.CompoundSelect.selected_columns`

4514 attribute returns the selected

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

4516 statements within the set operation.

4517

4518 .. seealso::

4519

4520 :attr:`_sql.Select.selected_columns`

4521

4522 .. versionadded:: 1.4

4523

4524 """

4525 return self.selects[0].selected_columns

4526

4527

4528# backwards compat

4529for elem in _CompoundSelectKeyword:

4530 setattr(CompoundSelect, elem.name, elem)

4531

4532

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

4534class SelectState(util.MemoizedSlots, CompileState):

4535 __slots__ = (

4536 "from_clauses",

4537 "froms",

4538 "columns_plus_names",

4539 "_label_resolve_dict",

4540 )

4541

4542 if TYPE_CHECKING:

4543 default_select_compile_options: CacheableOptions

4544 else:

4545

4546 class default_select_compile_options(CacheableOptions):

4547 _cache_key_traversal = []

4548

4549 if TYPE_CHECKING:

4550

4551 @classmethod

4552 def get_plugin_class(

4553 cls, statement: Executable

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

4555

4556 def __init__(

4557 self,

4558 statement: Select[Unpack[TupleAny]],

4559 compiler: Optional[SQLCompiler],

4560 **kw: Any,

4561 ):

4562 self.statement = statement

4563 self.from_clauses = statement._from_obj

4564

4565 for memoized_entities in statement._memoized_select_entities:

4566 self._setup_joins(

4567 memoized_entities._setup_joins, memoized_entities._raw_columns

4568 )

4569

4570 if statement._setup_joins:

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

4572

4573 self.froms = self._get_froms(statement)

4574

4575 self.columns_plus_names = statement._generate_columns_plus_names(True)

4576

4577 @classmethod

4578 def _plugin_not_implemented(cls) -> NoReturn:

4579 raise NotImplementedError(

4580 "The default SELECT construct without plugins does not "

4581 "implement this method."

4582 )

4583

4584 @classmethod

4585 def get_column_descriptions(

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

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

4588 return [

4589 {

4590 "name": name,

4591 "type": element.type,

4592 "expr": element,

4593 }

4594 for _, name, _, element, _ in (

4595 statement._generate_columns_plus_names(False)

4596 )

4597 ]

4598

4599 @classmethod

4600 def from_statement(

4601 cls,

4602 statement: Select[Unpack[TupleAny]],

4603 from_statement: roles.ReturnsRowsRole,

4604 ) -> ExecutableReturnsRows:

4605 cls._plugin_not_implemented()

4606

4607 @classmethod

4608 def get_columns_clause_froms(

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

4610 ) -> List[FromClause]:

4611 return cls._normalize_froms(

4612 itertools.chain.from_iterable(

4613 element._from_objects for element in statement._raw_columns

4614 )

4615 )

4616

4617 @classmethod

4618 def _column_naming_convention(

4619 cls, label_style: SelectLabelStyle

4620 ) -> _LabelConventionCallable:

4621 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4622

4623 dedupe = label_style is not LABEL_STYLE_NONE

4624

4625 pa = prefix_anon_map()

4626 names = set()

4627

4628 def go(

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

4630 col_name: Optional[str] = None,

4631 ) -> Optional[str]:

4632 if is_text_clause(c):

4633 return None

4634 elif TYPE_CHECKING:

4635 assert is_column_element(c)

4636

4637 if not dedupe:

4638 name = c._proxy_key

4639 if name is None:

4640 name = "_no_label"

4641 return name

4642

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

4644

4645 if name is None:

4646 name = "_no_label"

4647 if name in names:

4648 return c._anon_label(name) % pa

4649 else:

4650 names.add(name)

4651 return name

4652

4653 elif name in names:

4654 return (

4655 c._anon_tq_key_label % pa

4656 if table_qualified

4657 else c._anon_key_label % pa

4658 )

4659 else:

4660 names.add(name)

4661 return name

4662

4663 return go

4664

4665 def _get_froms(

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

4667 ) -> List[FromClause]:

4668 ambiguous_table_name_map: _AmbiguousTableNameMap

4669 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4670

4671 return self._normalize_froms(

4672 itertools.chain(

4673 self.from_clauses,

4674 itertools.chain.from_iterable(

4675 [

4676 element._from_objects

4677 for element in statement._raw_columns

4678 ]

4679 ),

4680 itertools.chain.from_iterable(

4681 [

4682 element._from_objects

4683 for element in statement._where_criteria

4684 ]

4685 ),

4686 ),

4687 check_statement=statement,

4688 ambiguous_table_name_map=ambiguous_table_name_map,

4689 )

4690

4691 @classmethod

4692 def _normalize_froms(

4693 cls,

4694 iterable_of_froms: Iterable[FromClause],

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

4696 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4697 ) -> List[FromClause]:

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

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

4700

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

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

4703 etc.

4704

4705 """

4706 seen: Set[FromClause] = set()

4707 froms: List[FromClause] = []

4708

4709 for item in iterable_of_froms:

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

4711 raise exc.InvalidRequestError(

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

4713 )

4714

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

4716 froms.append(item)

4717 seen.update(item._cloned_set)

4718

4719 if froms:

4720 toremove = set(

4721 itertools.chain.from_iterable(

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

4723 )

4724 )

4725 if toremove:

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

4727 # using a list to maintain ordering

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

4729

4730 if ambiguous_table_name_map is not None:

4731 ambiguous_table_name_map.update(

4732 (

4733 fr.name,

4734 _anonymous_label.safe_construct(

4735 hash(fr.name), fr.name

4736 ),

4737 )

4738 for item in froms

4739 for fr in item._from_objects

4740 if is_table(fr)

4741 and fr.schema

4742 and fr.name not in ambiguous_table_name_map

4743 )

4744

4745 return froms

4746

4747 def _get_display_froms(

4748 self,

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

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

4751 ) -> List[FromClause]:

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

4753

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

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

4756 may want to leave those absent if it is automatically

4757 correlating.

4758

4759 """

4760

4761 froms = self.froms

4762

4763 if self.statement._correlate:

4764 to_correlate = self.statement._correlate

4765 if to_correlate:

4766 froms = [

4767 f

4768 for f in froms

4769 if f

4770 not in _cloned_intersection(

4771 _cloned_intersection(

4772 froms, explicit_correlate_froms or ()

4773 ),

4774 to_correlate,

4775 )

4776 ]

4777

4778 if self.statement._correlate_except is not None:

4779 froms = [

4780 f

4781 for f in froms

4782 if f

4783 not in _cloned_difference(

4784 _cloned_intersection(

4785 froms, explicit_correlate_froms or ()

4786 ),

4787 self.statement._correlate_except,

4788 )

4789 ]

4790

4791 if (

4792 self.statement._auto_correlate

4793 and implicit_correlate_froms

4794 and len(froms) > 1

4795 ):

4796 froms = [

4797 f

4798 for f in froms

4799 if f

4800 not in _cloned_intersection(froms, implicit_correlate_froms)

4801 ]

4802

4803 if not len(froms):

4804 raise exc.InvalidRequestError(

4805 "Select statement '%r"

4806 "' returned no FROM clauses "

4807 "due to auto-correlation; "

4808 "specify correlate(<tables>) "

4809 "to control correlation "

4810 "manually." % self.statement

4811 )

4812

4813 return froms

4814

4815 def _memoized_attr__label_resolve_dict(

4816 self,

4817 ) -> Tuple[

4818 Dict[str, ColumnElement[Any]],

4819 Dict[str, ColumnElement[Any]],

4820 Dict[str, ColumnElement[Any]],

4821 ]:

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

4823 c._tq_label or c.key: c

4824 for c in self.statement._all_selected_columns

4825 if c._allow_label_resolve

4826 }

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

4828 c.key: c # type: ignore

4829 for c in _select_iterables(self.froms)

4830 if c._allow_label_resolve

4831 }

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

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

4834 with_cols.setdefault(key, value)

4835

4836 return with_cols, only_froms, only_cols

4837

4838 @classmethod

4839 def determine_last_joined_entity(

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

4841 ) -> Optional[_JoinTargetElement]:

4842 if stmt._setup_joins:

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

4844 else:

4845 return None

4846

4847 @classmethod

4848 def all_selected_columns(

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

4850 ) -> _SelectIterable:

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

4852

4853 def _setup_joins(

4854 self,

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

4856 raw_columns: List[_ColumnsClauseElement],

4857 ) -> None:

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

4859 if TYPE_CHECKING:

4860 if onclause is not None:

4861 assert isinstance(onclause, ColumnElement)

4862

4863 isouter = flags["isouter"]

4864 full = flags["full"]

4865

4866 if left is None:

4867 (

4868 left,

4869 replace_from_obj_index,

4870 ) = self._join_determine_implicit_left_side(

4871 raw_columns, left, right, onclause

4872 )

4873 else:

4874 (replace_from_obj_index) = self._join_place_explicit_left_side(

4875 left

4876 )

4877

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

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

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

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

4882 # used instead.

4883 if TYPE_CHECKING:

4884 assert isinstance(right, FromClause)

4885 if onclause is not None:

4886 assert isinstance(onclause, ColumnElement)

4887

4888 if replace_from_obj_index is not None:

4889 # splice into an existing element in the

4890 # self._from_obj list

4891 left_clause = self.from_clauses[replace_from_obj_index]

4892

4893 self.from_clauses = (

4894 self.from_clauses[:replace_from_obj_index]

4895 + (

4896 Join(

4897 left_clause,

4898 right,

4899 onclause,

4900 isouter=isouter,

4901 full=full,

4902 ),

4903 )

4904 + self.from_clauses[replace_from_obj_index + 1 :]

4905 )

4906 else:

4907 assert left is not None

4908 self.from_clauses = self.from_clauses + (

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

4910 )

4911

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

4913 def _join_determine_implicit_left_side(

4914 self,

4915 raw_columns: List[_ColumnsClauseElement],

4916 left: Optional[FromClause],

4917 right: _JoinTargetElement,

4918 onclause: Optional[ColumnElement[Any]],

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

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

4921 determine if an existing FROM or entity in this query

4922 can serve as the left hand side.

4923

4924 """

4925

4926 sql_util = util.preloaded.sql_util

4927

4928 replace_from_obj_index: Optional[int] = None

4929

4930 from_clauses = self.from_clauses

4931

4932 if from_clauses:

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

4934 from_clauses, right, onclause

4935 )

4936

4937 if len(indexes) == 1:

4938 replace_from_obj_index = indexes[0]

4939 left = from_clauses[replace_from_obj_index]

4940 else:

4941 potential = {}

4942 statement = self.statement

4943

4944 for from_clause in itertools.chain(

4945 itertools.chain.from_iterable(

4946 [element._from_objects for element in raw_columns]

4947 ),

4948 itertools.chain.from_iterable(

4949 [

4950 element._from_objects

4951 for element in statement._where_criteria

4952 ]

4953 ),

4954 ):

4955 potential[from_clause] = ()

4956

4957 all_clauses = list(potential.keys())

4958 indexes = sql_util.find_left_clause_to_join_from(

4959 all_clauses, right, onclause

4960 )

4961

4962 if len(indexes) == 1:

4963 left = all_clauses[indexes[0]]

4964

4965 if len(indexes) > 1:

4966 raise exc.InvalidRequestError(

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

4968 "from, there are multiple FROMS which can "

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

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

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

4972 "help resolve the ambiguity."

4973 )

4974 elif not indexes:

4975 raise exc.InvalidRequestError(

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

4977 "Please use the .select_from() "

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

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

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

4981 )

4982 return left, replace_from_obj_index

4983

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

4985 def _join_place_explicit_left_side(

4986 self, left: FromClause

4987 ) -> Optional[int]:

4988 replace_from_obj_index: Optional[int] = None

4989

4990 sql_util = util.preloaded.sql_util

4991

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

4993

4994 if from_clauses:

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

4996 self.from_clauses, left

4997 )

4998 else:

4999 indexes = []

5000

5001 if len(indexes) > 1:

5002 raise exc.InvalidRequestError(

5003 "Can't identify which entity in which to assign the "

5004 "left side of this join. Please use a more specific "

5005 "ON clause."

5006 )

5007

5008 # have an index, means the left side is already present in

5009 # an existing FROM in the self._from_obj tuple

5010 if indexes:

5011 replace_from_obj_index = indexes[0]

5012

5013 # no index, means we need to add a new element to the

5014 # self._from_obj tuple

5015

5016 return replace_from_obj_index

5017

5018

5019class _SelectFromElements:

5020 __slots__ = ()

5021

5022 _raw_columns: List[_ColumnsClauseElement]

5023 _where_criteria: Tuple[ColumnElement[Any], ...]

5024 _from_obj: Tuple[FromClause, ...]

5025

5026 def _iterate_from_elements(self) -> Iterator[FromClause]:

5027 # note this does not include elements

5028 # in _setup_joins

5029

5030 seen = set()

5031 for element in self._raw_columns:

5032 for fr in element._from_objects:

5033 if fr in seen:

5034 continue

5035 seen.add(fr)

5036 yield fr

5037 for element in self._where_criteria:

5038 for fr in element._from_objects:

5039 if fr in seen:

5040 continue

5041 seen.add(fr)

5042 yield fr

5043 for element in self._from_obj:

5044 if element in seen:

5045 continue

5046 seen.add(element)

5047 yield element

5048

5049

5050class _MemoizedSelectEntities(

5051 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5052):

5053 """represents partial state from a Select object, for the case

5054 where Select.columns() has redefined the set of columns/entities the

5055 statement will be SELECTing from. This object represents

5056 the entities from the SELECT before that transformation was applied,

5057 so that transformations that were made in terms of the SELECT at that

5058 time, such as join() as well as options(), can access the correct context.

5059

5060 In previous SQLAlchemy versions, this wasn't needed because these

5061 constructs calculated everything up front, like when you called join()

5062 or options(), it did everything to figure out how that would translate

5063 into specific SQL constructs that would be ready to send directly to the

5064 SQL compiler when needed. But as of

5065 1.4, all of that stuff is done in the compilation phase, during the

5066 "compile state" portion of the process, so that the work can all be

5067 cached. So it needs to be able to resolve joins/options2 based on what

5068 the list of entities was when those methods were called.

5069

5070

5071 """

5072

5073 __visit_name__ = "memoized_select_entities"

5074

5075 _traverse_internals: _TraverseInternalsType = [

5076 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5077 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5078 ("_with_options", InternalTraversal.dp_executable_options),

5079 ]

5080

5081 _is_clone_of: Optional[ClauseElement]

5082 _raw_columns: List[_ColumnsClauseElement]

5083 _setup_joins: Tuple[_SetupJoinsElement, ...]

5084 _with_options: Tuple[ExecutableOption, ...]

5085

5086 _annotations = util.EMPTY_DICT

5087

5088 def _clone(self, **kw: Any) -> Self:

5089 c = self.__class__.__new__(self.__class__)

5090 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5091

5092 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5093 return c

5094

5095 @classmethod

5096 def _generate_for_statement(

5097 cls, select_stmt: Select[Unpack[TupleAny]]

5098 ) -> None:

5099 if select_stmt._setup_joins or select_stmt._with_options:

5100 self = _MemoizedSelectEntities()

5101 self._raw_columns = select_stmt._raw_columns

5102 self._setup_joins = select_stmt._setup_joins

5103 self._with_options = select_stmt._with_options

5104

5105 select_stmt._memoized_select_entities += (self,)

5106 select_stmt._raw_columns = []

5107 select_stmt._setup_joins = select_stmt._with_options = ()

5108

5109

5110class Select(

5111 HasPrefixes,

5112 HasSuffixes,

5113 HasHints,

5114 HasCompileState,

5115 _SelectFromElements,

5116 GenerativeSelect,

5117 TypedReturnsRows[Unpack[_Ts]],

5118):

5119 """Represents a ``SELECT`` statement.

5120

5121 The :class:`_sql.Select` object is normally constructed using the

5122 :func:`_sql.select` function. See that function for details.

5123

5124 .. seealso::

5125

5126 :func:`_sql.select`

5127

5128 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5129

5130 """

5131

5132 __visit_name__ = "select"

5133

5134 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5135 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5136

5137 _raw_columns: List[_ColumnsClauseElement]

5138

5139 _distinct: bool = False

5140 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5141 _correlate: Tuple[FromClause, ...] = ()

5142 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5143 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5144 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5145 _from_obj: Tuple[FromClause, ...] = ()

5146 _auto_correlate = True

5147 _is_select_statement = True

5148 _compile_options: CacheableOptions = (

5149 SelectState.default_select_compile_options

5150 )

5151

5152 _traverse_internals: _TraverseInternalsType = (

5153 [

5154 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5155 (

5156 "_memoized_select_entities",

5157 InternalTraversal.dp_memoized_select_entities,

5158 ),

5159 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5160 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5161 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5162 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5163 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5164 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5165 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5166 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5167 ("_limit_clause", InternalTraversal.dp_clauseelement),

5168 ("_offset_clause", InternalTraversal.dp_clauseelement),

5169 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5170 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5171 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5172 ("_distinct", InternalTraversal.dp_boolean),

5173 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5174 ("_label_style", InternalTraversal.dp_plain_obj),

5175 ]

5176 + HasCTE._has_ctes_traverse_internals

5177 + HasPrefixes._has_prefixes_traverse_internals

5178 + HasSuffixes._has_suffixes_traverse_internals

5179 + HasHints._has_hints_traverse_internals

5180 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5181 + Executable._executable_traverse_internals

5182 )

5183

5184 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5185 ("_compile_options", InternalTraversal.dp_has_cache_key)

5186 ]

5187

5188 _compile_state_factory: Type[SelectState]

5189

5190 @classmethod

5191 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5192 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5193

5194 Used internally to build up :class:`.Select` constructs with

5195 pre-established state.

5196

5197 """

5198

5199 stmt = Select.__new__(Select)

5200 stmt.__dict__.update(kw)

5201 return stmt

5202

5203 def __init__(self, *entities: _ColumnsClauseArgument[Any]):

5204 r"""Construct a new :class:`_expression.Select`.

5205

5206 The public constructor for :class:`_expression.Select` is the

5207 :func:`_sql.select` function.

5208

5209 """

5210 self._raw_columns = [

5211 coercions.expect(

5212 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5213 )

5214 for ent in entities

5215 ]

5216

5217 GenerativeSelect.__init__(self)

5218

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

5220 if not self._raw_columns:

5221 return NULLTYPE

5222 elem = self._raw_columns[0]

5223 cols = list(elem._select_iterable)

5224 return cols[0].type

5225

5226 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5227 """A synonym for the :meth:`_sql.Select.where` method."""

5228

5229 return self.where(*criteria)

5230

5231 def _filter_by_zero(

5232 self,

5233 ) -> Union[

5234 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5235 ]:

5236 if self._setup_joins:

5237 meth = SelectState.get_plugin_class(

5238 self

5239 ).determine_last_joined_entity

5240 _last_joined_entity = meth(self)

5241 if _last_joined_entity is not None:

5242 return _last_joined_entity

5243

5244 if self._from_obj:

5245 return self._from_obj[0]

5246

5247 return self._raw_columns[0]

5248

5249 if TYPE_CHECKING:

5250

5251 @overload

5252 def scalar_subquery(

5253 self: Select[_MAYBE_ENTITY],

5254 ) -> ScalarSelect[Any]: ...

5255

5256 @overload

5257 def scalar_subquery(

5258 self: Select[_NOT_ENTITY],

5259 ) -> ScalarSelect[_NOT_ENTITY]: ...

5260

5261 @overload

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

5263

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

5265

5266 def filter_by(self, **kwargs: Any) -> Self:

5267 r"""apply the given filtering criterion as a WHERE clause

5268 to this select.

5269

5270 """

5271 from_entity = self._filter_by_zero()

5272

5273 clauses = [

5274 _entity_namespace_key(from_entity, key) == value

5275 for key, value in kwargs.items()

5276 ]

5277 return self.filter(*clauses)

5278

5279 @property

5280 def column_descriptions(self) -> Any:

5281 """Return a :term:`plugin-enabled` 'column descriptions' structure

5282 referring to the columns which are SELECTed by this statement.

5283

5284 This attribute is generally useful when using the ORM, as an

5285 extended structure which includes information about mapped

5286 entities is returned. The section :ref:`queryguide_inspection`

5287 contains more background.

5288

5289 For a Core-only statement, the structure returned by this accessor

5290 is derived from the same objects that are returned by the

5291 :attr:`.Select.selected_columns` accessor, formatted as a list of

5292 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5293 which indicate the column expressions to be selected::

5294

5295 >>> stmt = select(user_table)

5296 >>> stmt.column_descriptions

5297 [

5298 {

5299 'name': 'id',

5300 'type': Integer(),

5301 'expr': Column('id', Integer(), ...)},

5302 {

5303 'name': 'name',

5304 'type': String(length=30),

5305 'expr': Column('name', String(length=30), ...)}

5306 ]

5307

5308 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5309 attribute returns a structure for a Core-only set of entities,

5310 not just ORM-only entities.

5311

5312 .. seealso::

5313

5314 :attr:`.UpdateBase.entity_description` - entity information for

5315 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5316

5317 :ref:`queryguide_inspection` - ORM background

5318

5319 """

5320 meth = SelectState.get_plugin_class(self).get_column_descriptions

5321 return meth(self)

5322

5323 def from_statement(

5324 self, statement: roles.ReturnsRowsRole

5325 ) -> ExecutableReturnsRows:

5326 """Apply the columns which this :class:`.Select` would select

5327 onto another statement.

5328

5329 This operation is :term:`plugin-specific` and will raise a not

5330 supported exception if this :class:`_sql.Select` does not select from

5331 plugin-enabled entities.

5332

5333

5334 The statement is typically either a :func:`_expression.text` or

5335 :func:`_expression.select` construct, and should return the set of

5336 columns appropriate to the entities represented by this

5337 :class:`.Select`.

5338

5339 .. seealso::

5340

5341 :ref:`orm_queryguide_selecting_text` - usage examples in the

5342 ORM Querying Guide

5343

5344 """

5345 meth = SelectState.get_plugin_class(self).from_statement

5346 return meth(self, statement)

5347

5348 @_generative

5349 def join(

5350 self,

5351 target: _JoinTargetArgument,

5352 onclause: Optional[_OnClauseArgument] = None,

5353 *,

5354 isouter: bool = False,

5355 full: bool = False,

5356 ) -> Self:

5357 r"""Create a SQL JOIN against this :class:`_expression.Select`

5358 object's criterion

5359 and apply generatively, returning the newly resulting

5360 :class:`_expression.Select`.

5361

5362 E.g.::

5363

5364 stmt = select(user_table).join(address_table, user_table.c.id == address_table.c.user_id)

5365

5366 The above statement generates SQL similar to::

5367

5368 SELECT user.id, user.name FROM user JOIN address ON user.id = address.user_id

5369

5370 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5371 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5372 source that is within the FROM clause of the existing SELECT,

5373 and a given target :class:`_sql.FromClause`, and then adds

5374 this :class:`_sql.Join` to the FROM clause of the newly generated

5375 SELECT statement. This is completely reworked from the behavior

5376 in 1.3, which would instead create a subquery of the entire

5377 :class:`_expression.Select` and then join that subquery to the

5378 target.

5379

5380 This is a **backwards incompatible change** as the previous behavior

5381 was mostly useless, producing an unnamed subquery rejected by

5382 most databases in any case. The new behavior is modeled after

5383 that of the very successful :meth:`_orm.Query.join` method in the

5384 ORM, in order to support the functionality of :class:`_orm.Query`

5385 being available by using a :class:`_sql.Select` object with an

5386 :class:`_orm.Session`.

5387

5388 See the notes for this change at :ref:`change_select_join`.

5389

5390

5391 :param target: target table to join towards

5392

5393 :param onclause: ON clause of the join. If omitted, an ON clause

5394 is generated automatically based on the :class:`_schema.ForeignKey`

5395 linkages between the two tables, if one can be unambiguously

5396 determined, otherwise an error is raised.

5397

5398 :param isouter: if True, generate LEFT OUTER join. Same as

5399 :meth:`_expression.Select.outerjoin`.

5400

5401 :param full: if True, generate FULL OUTER join.

5402

5403 .. seealso::

5404

5405 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5406

5407 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5408

5409 :meth:`_expression.Select.join_from`

5410

5411 :meth:`_expression.Select.outerjoin`

5412

5413 """ # noqa: E501

5414 join_target = coercions.expect(

5415 roles.JoinTargetRole, target, apply_propagate_attrs=self

5416 )

5417 if onclause is not None:

5418 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5419 else:

5420 onclause_element = None

5421

5422 self._setup_joins += (

5423 (

5424 join_target,

5425 onclause_element,

5426 None,

5427 {"isouter": isouter, "full": full},

5428 ),

5429 )

5430 return self

5431

5432 def outerjoin_from(

5433 self,

5434 from_: _FromClauseArgument,

5435 target: _JoinTargetArgument,

5436 onclause: Optional[_OnClauseArgument] = None,

5437 *,

5438 full: bool = False,

5439 ) -> Self:

5440 r"""Create a SQL LEFT OUTER JOIN against this

5441 :class:`_expression.Select` object's criterion and apply generatively,

5442 returning the newly resulting :class:`_expression.Select`.

5443

5444 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5445

5446 """

5447 return self.join_from(

5448 from_, target, onclause=onclause, isouter=True, full=full

5449 )

5450

5451 @_generative

5452 def join_from(

5453 self,

5454 from_: _FromClauseArgument,

5455 target: _JoinTargetArgument,

5456 onclause: Optional[_OnClauseArgument] = None,

5457 *,

5458 isouter: bool = False,

5459 full: bool = False,

5460 ) -> Self:

5461 r"""Create a SQL JOIN against this :class:`_expression.Select`

5462 object's criterion

5463 and apply generatively, returning the newly resulting

5464 :class:`_expression.Select`.

5465

5466 E.g.::

5467

5468 stmt = select(user_table, address_table).join_from(

5469 user_table, address_table, user_table.c.id == address_table.c.user_id

5470 )

5471

5472 The above statement generates SQL similar to::

5473

5474 SELECT user.id, user.name, address.id, address.email, address.user_id

5475 FROM user JOIN address ON user.id = address.user_id

5476

5477 .. versionadded:: 1.4

5478

5479 :param from\_: the left side of the join, will be rendered in the

5480 FROM clause and is roughly equivalent to using the

5481 :meth:`.Select.select_from` method.

5482

5483 :param target: target table to join towards

5484

5485 :param onclause: ON clause of the join.

5486

5487 :param isouter: if True, generate LEFT OUTER join. Same as

5488 :meth:`_expression.Select.outerjoin`.

5489

5490 :param full: if True, generate FULL OUTER join.

5491

5492 .. seealso::

5493

5494 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5495

5496 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5497

5498 :meth:`_expression.Select.join`

5499

5500 """ # noqa: E501

5501

5502 # note the order of parsing from vs. target is important here, as we

5503 # are also deriving the source of the plugin (i.e. the subject mapper

5504 # in an ORM query) which should favor the "from_" over the "target"

5505

5506 from_ = coercions.expect(

5507 roles.FromClauseRole, from_, apply_propagate_attrs=self

5508 )

5509 join_target = coercions.expect(

5510 roles.JoinTargetRole, target, apply_propagate_attrs=self

5511 )

5512 if onclause is not None:

5513 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5514 else:

5515 onclause_element = None

5516

5517 self._setup_joins += (

5518 (

5519 join_target,

5520 onclause_element,

5521 from_,

5522 {"isouter": isouter, "full": full},

5523 ),

5524 )

5525 return self

5526

5527 def outerjoin(

5528 self,

5529 target: _JoinTargetArgument,

5530 onclause: Optional[_OnClauseArgument] = None,

5531 *,

5532 full: bool = False,

5533 ) -> Self:

5534 """Create a left outer join.

5535

5536 Parameters are the same as that of :meth:`_expression.Select.join`.

5537

5538 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5539 creates a :class:`_sql.Join` object between a

5540 :class:`_sql.FromClause` source that is within the FROM clause of

5541 the existing SELECT, and a given target :class:`_sql.FromClause`,

5542 and then adds this :class:`_sql.Join` to the FROM clause of the

5543 newly generated SELECT statement. This is completely reworked

5544 from the behavior in 1.3, which would instead create a subquery of

5545 the entire

5546 :class:`_expression.Select` and then join that subquery to the

5547 target.

5548

5549 This is a **backwards incompatible change** as the previous behavior

5550 was mostly useless, producing an unnamed subquery rejected by

5551 most databases in any case. The new behavior is modeled after

5552 that of the very successful :meth:`_orm.Query.join` method in the

5553 ORM, in order to support the functionality of :class:`_orm.Query`

5554 being available by using a :class:`_sql.Select` object with an

5555 :class:`_orm.Session`.

5556

5557 See the notes for this change at :ref:`change_select_join`.

5558

5559 .. seealso::

5560

5561 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5562

5563 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5564

5565 :meth:`_expression.Select.join`

5566

5567 """

5568 return self.join(target, onclause=onclause, isouter=True, full=full)

5569

5570 def get_final_froms(self) -> Sequence[FromClause]:

5571 """Compute the final displayed list of :class:`_expression.FromClause`

5572 elements.

5573

5574 This method will run through the full computation required to

5575 determine what FROM elements will be displayed in the resulting

5576 SELECT statement, including shadowing individual tables with

5577 JOIN objects, as well as full computation for ORM use cases including

5578 eager loading clauses.

5579

5580 For ORM use, this accessor returns the **post compilation**

5581 list of FROM objects; this collection will include elements such as

5582 eagerly loaded tables and joins. The objects will **not** be

5583 ORM enabled and not work as a replacement for the

5584 :meth:`_sql.Select.select_froms` collection; additionally, the

5585 method is not well performing for an ORM enabled statement as it

5586 will incur the full ORM construction process.

5587

5588 To retrieve the FROM list that's implied by the "columns" collection

5589 passed to the :class:`_sql.Select` originally, use the

5590 :attr:`_sql.Select.columns_clause_froms` accessor.

5591

5592 To select from an alternative set of columns while maintaining the

5593 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5594 pass the

5595 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5596 parameter.

5597

5598 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5599 method replaces the previous :attr:`_sql.Select.froms` accessor,

5600 which is deprecated.

5601

5602 .. seealso::

5603

5604 :attr:`_sql.Select.columns_clause_froms`

5605

5606 """

5607

5608 return self._compile_state_factory(self, None)._get_display_froms()

5609

5610 @property

5611 @util.deprecated(

5612 "1.4.23",

5613 "The :attr:`_expression.Select.froms` attribute is moved to "

5614 "the :meth:`_expression.Select.get_final_froms` method.",

5615 )

5616 def froms(self) -> Sequence[FromClause]:

5617 """Return the displayed list of :class:`_expression.FromClause`

5618 elements.

5619

5620

5621 """

5622 return self.get_final_froms()

5623

5624 @property

5625 def columns_clause_froms(self) -> List[FromClause]:

5626 """Return the set of :class:`_expression.FromClause` objects implied

5627 by the columns clause of this SELECT statement.

5628

5629 .. versionadded:: 1.4.23

5630

5631 .. seealso::

5632

5633 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5634 statement into account

5635

5636 :meth:`_sql.Select.with_only_columns` - makes use of this

5637 collection to set up a new FROM list

5638

5639 """

5640

5641 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5642 self

5643 )

5644

5645 @property

5646 def inner_columns(self) -> _SelectIterable:

5647 """An iterator of all :class:`_expression.ColumnElement`

5648 expressions which would

5649 be rendered into the columns clause of the resulting SELECT statement.

5650

5651 This method is legacy as of 1.4 and is superseded by the

5652 :attr:`_expression.Select.exported_columns` collection.

5653

5654 """

5655

5656 return iter(self._all_selected_columns)

5657

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

5659 if fromclause is not None and self in fromclause._cloned_set:

5660 return True

5661

5662 for f in self._iterate_from_elements():

5663 if f.is_derived_from(fromclause):

5664 return True

5665 return False

5666

5667 def _copy_internals(

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

5669 ) -> None:

5670 # Select() object has been cloned and probably adapted by the

5671 # given clone function. Apply the cloning function to internal

5672 # objects

5673

5674 # 1. keep a dictionary of the froms we've cloned, and what

5675 # they've become. This allows us to ensure the same cloned from

5676 # is used when other items such as columns are "cloned"

5677

5678 all_the_froms = set(

5679 itertools.chain(

5680 _from_objects(*self._raw_columns),

5681 _from_objects(*self._where_criteria),

5682 _from_objects(*[elem[0] for elem in self._setup_joins]),

5683 )

5684 )

5685

5686 # do a clone for the froms we've gathered. what is important here

5687 # is if any of the things we are selecting from, like tables,

5688 # were converted into Join objects. if so, these need to be

5689 # added to _from_obj explicitly, because otherwise they won't be

5690 # part of the new state, as they don't associate themselves with

5691 # their columns.

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

5693

5694 # 2. copy FROM collections, adding in joins that we've created.

5695 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5696 add_froms = (

5697 {f for f in new_froms.values() if isinstance(f, Join)}

5698 .difference(all_the_froms)

5699 .difference(existing_from_obj)

5700 )

5701

5702 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5703

5704 # 3. clone everything else, making sure we use columns

5705 # corresponding to the froms we just made.

5706 def replace(

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

5708 **kw: Any,

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

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

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

5712 return newelem

5713 return None

5714

5715 kw["replace"] = replace

5716

5717 # copy everything else. for table-ish things like correlate,

5718 # correlate_except, setup_joins, these clone normally. For

5719 # column-expression oriented things like raw_columns, where_criteria,

5720 # order by, we get this from the new froms.

5721 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5722

5723 self._reset_memoizations()

5724

5725 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5726 return itertools.chain(

5727 super().get_children(

5728 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5729 **kw,

5730 ),

5731 self._iterate_from_elements(),

5732 )

5733

5734 @_generative

5735 def add_columns(

5736 self, *entities: _ColumnsClauseArgument[Any]

5737 ) -> Select[Unpack[TupleAny]]:

5738 r"""Return a new :func:`_expression.select` construct with

5739 the given entities appended to its columns clause.

5740

5741 E.g.::

5742

5743 my_select = my_select.add_columns(table.c.new_column)

5744

5745 The original expressions in the columns clause remain in place.

5746 To replace the original expressions with new ones, see the method

5747 :meth:`_expression.Select.with_only_columns`.

5748

5749 :param \*entities: column, table, or other entity expressions to be

5750 added to the columns clause

5751

5752 .. seealso::

5753

5754 :meth:`_expression.Select.with_only_columns` - replaces existing

5755 expressions rather than appending.

5756

5757 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5758 example

5759

5760 """

5761 self._reset_memoizations()

5762

5763 self._raw_columns = self._raw_columns + [

5764 coercions.expect(

5765 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5766 )

5767 for column in entities

5768 ]

5769 return self

5770

5771 def _set_entities(

5772 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5773 ) -> None:

5774 self._raw_columns = [

5775 coercions.expect(

5776 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5777 )

5778 for ent in util.to_list(entities)

5779 ]

5780

5781 @util.deprecated(

5782 "1.4",

5783 "The :meth:`_expression.Select.column` method is deprecated and will "

5784 "be removed in a future release. Please use "

5785 ":meth:`_expression.Select.add_columns`",

5786 )

5787 def column(

5788 self, column: _ColumnsClauseArgument[Any]

5789 ) -> Select[Unpack[TupleAny]]:

5790 """Return a new :func:`_expression.select` construct with

5791 the given column expression added to its columns clause.

5792

5793 E.g.::

5794

5795 my_select = my_select.column(table.c.new_column)

5796

5797 See the documentation for

5798 :meth:`_expression.Select.with_only_columns`

5799 for guidelines on adding /replacing the columns of a

5800 :class:`_expression.Select` object.

5801

5802 """

5803 return self.add_columns(column)

5804

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

5806 def reduce_columns(

5807 self, only_synonyms: bool = True

5808 ) -> Select[Unpack[TupleAny]]:

5809 """Return a new :func:`_expression.select` construct with redundantly

5810 named, equivalently-valued columns removed from the columns clause.

5811

5812 "Redundant" here means two columns where one refers to the

5813 other either based on foreign key, or via a simple equality

5814 comparison in the WHERE clause of the statement. The primary purpose

5815 of this method is to automatically construct a select statement

5816 with all uniquely-named columns, without the need to use

5817 table-qualified labels as

5818 :meth:`_expression.Select.set_label_style`

5819 does.

5820

5821 When columns are omitted based on foreign key, the referred-to

5822 column is the one that's kept. When columns are omitted based on

5823 WHERE equivalence, the first column in the columns clause is the

5824 one that's kept.

5825

5826 :param only_synonyms: when True, limit the removal of columns

5827 to those which have the same name as the equivalent. Otherwise,

5828 all columns that are equivalent to another are removed.

5829

5830 """

5831 woc: Select[Unpack[TupleAny]]

5832 woc = self.with_only_columns(

5833 *util.preloaded.sql_util.reduce_columns(

5834 self._all_selected_columns,

5835 only_synonyms=only_synonyms,

5836 *(self._where_criteria + self._from_obj),

5837 )

5838 )

5839 return woc

5840

5841 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

5842

5843 # code within this block is **programmatically,

5844 # statically generated** by tools/generate_tuple_map_overloads.py

5845

5846 @overload

5847 def with_only_columns(

5848 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

5849 ) -> Select[_T0]: ...

5850

5851 @overload

5852 def with_only_columns(

5853 self,

5854 __ent0: _TCCA[_T0],

5855 __ent1: _TCCA[_T1],

5856 /,

5857 *,

5858 maintain_column_froms: bool = ...,

5859 ) -> Select[_T0, _T1]: ...

5860

5861 @overload

5862 def with_only_columns(

5863 self,

5864 __ent0: _TCCA[_T0],

5865 __ent1: _TCCA[_T1],

5866 __ent2: _TCCA[_T2],

5867 /,

5868 *,

5869 maintain_column_froms: bool = ...,

5870 ) -> Select[_T0, _T1, _T2]: ...

5871

5872 @overload

5873 def with_only_columns(

5874 self,

5875 __ent0: _TCCA[_T0],

5876 __ent1: _TCCA[_T1],

5877 __ent2: _TCCA[_T2],

5878 __ent3: _TCCA[_T3],

5879 /,

5880 *,

5881 maintain_column_froms: bool = ...,

5882 ) -> Select[_T0, _T1, _T2, _T3]: ...

5883

5884 @overload

5885 def with_only_columns(

5886 self,

5887 __ent0: _TCCA[_T0],

5888 __ent1: _TCCA[_T1],

5889 __ent2: _TCCA[_T2],

5890 __ent3: _TCCA[_T3],

5891 __ent4: _TCCA[_T4],

5892 /,

5893 *,

5894 maintain_column_froms: bool = ...,

5895 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

5896

5897 @overload

5898 def with_only_columns(

5899 self,

5900 __ent0: _TCCA[_T0],

5901 __ent1: _TCCA[_T1],

5902 __ent2: _TCCA[_T2],

5903 __ent3: _TCCA[_T3],

5904 __ent4: _TCCA[_T4],

5905 __ent5: _TCCA[_T5],

5906 /,

5907 *,

5908 maintain_column_froms: bool = ...,

5909 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

5910

5911 @overload

5912 def with_only_columns(

5913 self,

5914 __ent0: _TCCA[_T0],

5915 __ent1: _TCCA[_T1],

5916 __ent2: _TCCA[_T2],

5917 __ent3: _TCCA[_T3],

5918 __ent4: _TCCA[_T4],

5919 __ent5: _TCCA[_T5],

5920 __ent6: _TCCA[_T6],

5921 /,

5922 *,

5923 maintain_column_froms: bool = ...,

5924 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

5925

5926 @overload

5927 def with_only_columns(

5928 self,

5929 __ent0: _TCCA[_T0],

5930 __ent1: _TCCA[_T1],

5931 __ent2: _TCCA[_T2],

5932 __ent3: _TCCA[_T3],

5933 __ent4: _TCCA[_T4],

5934 __ent5: _TCCA[_T5],

5935 __ent6: _TCCA[_T6],

5936 __ent7: _TCCA[_T7],

5937 /,

5938 *entities: _ColumnsClauseArgument[Any],

5939 maintain_column_froms: bool = ...,

5940 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

5941

5942 # END OVERLOADED FUNCTIONS self.with_only_columns

5943

5944 @overload

5945 def with_only_columns(

5946 self,

5947 *entities: _ColumnsClauseArgument[Any],

5948 maintain_column_froms: bool = False,

5949 **__kw: Any,

5950 ) -> Select[Unpack[TupleAny]]: ...

5951

5952 @_generative

5953 def with_only_columns(

5954 self,

5955 *entities: _ColumnsClauseArgument[Any],

5956 maintain_column_froms: bool = False,

5957 **__kw: Any,

5958 ) -> Select[Unpack[TupleAny]]:

5959 r"""Return a new :func:`_expression.select` construct with its columns

5960 clause replaced with the given entities.

5961

5962 By default, this method is exactly equivalent to as if the original

5963 :func:`_expression.select` had been called with the given entities.

5964 E.g. a statement::

5965

5966 s = select(table1.c.a, table1.c.b)

5967 s = s.with_only_columns(table1.c.b)

5968

5969 should be exactly equivalent to::

5970

5971 s = select(table1.c.b)

5972

5973 In this mode of operation, :meth:`_sql.Select.with_only_columns`

5974 will also dynamically alter the FROM clause of the

5975 statement if it is not explicitly stated.

5976 To maintain the existing set of FROMs including those implied by the

5977 current columns clause, add the

5978 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5979 parameter::

5980

5981 s = select(table1.c.a, table2.c.b)

5982 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

5983

5984 The above parameter performs a transfer of the effective FROMs

5985 in the columns collection to the :meth:`_sql.Select.select_from`

5986 method, as though the following were invoked::

5987

5988 s = select(table1.c.a, table2.c.b)

5989 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

5990

5991 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5992 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

5993 collection and performs an operation equivalent to the following::

5994

5995 s = select(table1.c.a, table2.c.b)

5996 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

5997

5998 :param \*entities: column expressions to be used.

5999

6000 :param maintain_column_froms: boolean parameter that will ensure the

6001 FROM list implied from the current columns clause will be transferred

6002 to the :meth:`_sql.Select.select_from` method first.

6003

6004 .. versionadded:: 1.4.23

6005

6006 """ # noqa: E501

6007

6008 if __kw:

6009 raise _no_kw()

6010

6011 # memoizations should be cleared here as of

6012 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6013 # is the case for now.

6014 self._assert_no_memoizations()

6015

6016 if maintain_column_froms:

6017 self.select_from.non_generative( # type: ignore

6018 self, *self.columns_clause_froms

6019 )

6020

6021 # then memoize the FROMs etc.

6022 _MemoizedSelectEntities._generate_for_statement(self)

6023

6024 self._raw_columns = [

6025 coercions.expect(roles.ColumnsClauseRole, c)

6026 for c in coercions._expression_collection_was_a_list(

6027 "entities", "Select.with_only_columns", entities

6028 )

6029 ]

6030 return self

6031

6032 @property

6033 def whereclause(self) -> Optional[ColumnElement[Any]]:

6034 """Return the completed WHERE clause for this

6035 :class:`_expression.Select` statement.

6036

6037 This assembles the current collection of WHERE criteria

6038 into a single :class:`_expression.BooleanClauseList` construct.

6039

6040

6041 .. versionadded:: 1.4

6042

6043 """

6044

6045 return BooleanClauseList._construct_for_whereclause(

6046 self._where_criteria

6047 )

6048

6049 _whereclause = whereclause

6050

6051 @_generative

6052 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6053 """Return a new :func:`_expression.select` construct with

6054 the given expression added to

6055 its WHERE clause, joined to the existing clause via AND, if any.

6056

6057 """

6058

6059 assert isinstance(self._where_criteria, tuple)

6060

6061 for criterion in whereclause:

6062 where_criteria: ColumnElement[Any] = coercions.expect(

6063 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6064 )

6065 self._where_criteria += (where_criteria,)

6066 return self

6067

6068 @_generative

6069 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6070 """Return a new :func:`_expression.select` construct with

6071 the given expression added to

6072 its HAVING clause, joined to the existing clause via AND, if any.

6073

6074 """

6075

6076 for criterion in having:

6077 having_criteria = coercions.expect(

6078 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6079 )

6080 self._having_criteria += (having_criteria,)

6081 return self

6082

6083 @_generative

6084 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6085 r"""Return a new :func:`_expression.select` construct which

6086 will apply DISTINCT to the SELECT statement overall.

6087

6088 E.g.::

6089

6090 from sqlalchemy import select

6091 stmt = select(users_table.c.id, users_table.c.name).distinct()

6092

6093 The above would produce an statement resembling::

6094

6095 SELECT DISTINCT user.id, user.name FROM user

6096

6097 The method also accepts an ``*expr`` parameter which produces the

6098 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6099 parameter on other backends which don't support this syntax will

6100 raise an error.

6101

6102 :param \*expr: optional column expressions. When present,

6103 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6104 construct. A deprecation warning and/or :class:`_exc.CompileError`

6105 will be raised on other backends.

6106

6107 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6108 and will raise :class:`_exc.CompileError` in a future version.

6109

6110 """

6111 if expr:

6112 self._distinct = True

6113 self._distinct_on = self._distinct_on + tuple(

6114 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6115 for e in expr

6116 )

6117 else:

6118 self._distinct = True

6119 return self

6120

6121 @_generative

6122 def select_from(self, *froms: _FromClauseArgument) -> Self:

6123 r"""Return a new :func:`_expression.select` construct with the

6124 given FROM expression(s)

6125 merged into its list of FROM objects.

6126

6127 E.g.::

6128

6129 table1 = table('t1', column('a'))

6130 table2 = table('t2', column('b'))

6131 s = select(table1.c.a).\

6132 select_from(

6133 table1.join(table2, table1.c.a==table2.c.b)

6134 )

6135

6136 The "from" list is a unique set on the identity of each element,

6137 so adding an already present :class:`_schema.Table`

6138 or other selectable

6139 will have no effect. Passing a :class:`_expression.Join` that refers

6140 to an already present :class:`_schema.Table`

6141 or other selectable will have

6142 the effect of concealing the presence of that selectable as

6143 an individual element in the rendered FROM list, instead

6144 rendering it into a JOIN clause.

6145

6146 While the typical purpose of :meth:`_expression.Select.select_from`

6147 is to

6148 replace the default, derived FROM clause with a join, it can

6149 also be called with individual table elements, multiple times

6150 if desired, in the case that the FROM clause cannot be fully

6151 derived from the columns clause::

6152

6153 select(func.count('*')).select_from(table1)

6154

6155 """

6156

6157 self._from_obj += tuple(

6158 coercions.expect(

6159 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6160 )

6161 for fromclause in froms

6162 )

6163 return self

6164

6165 @_generative

6166 def correlate(

6167 self,

6168 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6169 ) -> Self:

6170 r"""Return a new :class:`_expression.Select`

6171 which will correlate the given FROM

6172 clauses to that of an enclosing :class:`_expression.Select`.

6173

6174 Calling this method turns off the :class:`_expression.Select` object's

6175 default behavior of "auto-correlation". Normally, FROM elements

6176 which appear in a :class:`_expression.Select`

6177 that encloses this one via

6178 its :term:`WHERE clause`, ORDER BY, HAVING or

6179 :term:`columns clause` will be omitted from this

6180 :class:`_expression.Select`

6181 object's :term:`FROM clause`.

6182 Setting an explicit correlation collection using the

6183 :meth:`_expression.Select.correlate`

6184 method provides a fixed list of FROM objects

6185 that can potentially take place in this process.

6186

6187 When :meth:`_expression.Select.correlate`

6188 is used to apply specific FROM clauses

6189 for correlation, the FROM elements become candidates for

6190 correlation regardless of how deeply nested this

6191 :class:`_expression.Select`

6192 object is, relative to an enclosing :class:`_expression.Select`

6193 which refers to

6194 the same FROM object. This is in contrast to the behavior of

6195 "auto-correlation" which only correlates to an immediate enclosing

6196 :class:`_expression.Select`.

6197 Multi-level correlation ensures that the link

6198 between enclosed and enclosing :class:`_expression.Select`

6199 is always via

6200 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6201 correlation to take place.

6202

6203 If ``None`` is passed, the :class:`_expression.Select`

6204 object will correlate

6205 none of its FROM entries, and all will render unconditionally

6206 in the local FROM clause.

6207

6208 :param \*fromclauses: one or more :class:`.FromClause` or other

6209 FROM-compatible construct such as an ORM mapped entity to become part

6210 of the correlate collection; alternatively pass a single value

6211 ``None`` to remove all existing correlations.

6212

6213 .. seealso::

6214

6215 :meth:`_expression.Select.correlate_except`

6216

6217 :ref:`tutorial_scalar_subquery`

6218

6219 """

6220

6221 # tests failing when we try to change how these

6222 # arguments are passed

6223

6224 self._auto_correlate = False

6225 if not fromclauses or fromclauses[0] in {None, False}:

6226 if len(fromclauses) > 1:

6227 raise exc.ArgumentError(

6228 "additional FROM objects not accepted when "

6229 "passing None/False to correlate()"

6230 )

6231 self._correlate = ()

6232 else:

6233 self._correlate = self._correlate + tuple(

6234 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6235 )

6236 return self

6237

6238 @_generative

6239 def correlate_except(

6240 self,

6241 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6242 ) -> Self:

6243 r"""Return a new :class:`_expression.Select`

6244 which will omit the given FROM

6245 clauses from the auto-correlation process.

6246

6247 Calling :meth:`_expression.Select.correlate_except` turns off the

6248 :class:`_expression.Select` object's default behavior of

6249 "auto-correlation" for the given FROM elements. An element

6250 specified here will unconditionally appear in the FROM list, while

6251 all other FROM elements remain subject to normal auto-correlation

6252 behaviors.

6253

6254 If ``None`` is passed, or no arguments are passed,

6255 the :class:`_expression.Select` object will correlate all of its

6256 FROM entries.

6257

6258 :param \*fromclauses: a list of one or more

6259 :class:`_expression.FromClause`

6260 constructs, or other compatible constructs (i.e. ORM-mapped

6261 classes) to become part of the correlate-exception collection.

6262

6263 .. seealso::

6264

6265 :meth:`_expression.Select.correlate`

6266

6267 :ref:`tutorial_scalar_subquery`

6268

6269 """

6270

6271 self._auto_correlate = False

6272 if not fromclauses or fromclauses[0] in {None, False}:

6273 if len(fromclauses) > 1:

6274 raise exc.ArgumentError(

6275 "additional FROM objects not accepted when "

6276 "passing None/False to correlate_except()"

6277 )

6278 self._correlate_except = ()

6279 else:

6280 self._correlate_except = (self._correlate_except or ()) + tuple(

6281 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6282 )

6283

6284 return self

6285

6286 @HasMemoized_ro_memoized_attribute

6287 def selected_columns(

6288 self,

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

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

6291 representing the columns that

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

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

6294

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

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

6297 within this collection cannot be directly nested inside another SELECT

6298 statement; a subquery must be applied first which provides for the

6299 necessary parenthesization required by SQL.

6300

6301 For a :func:`_expression.select` construct, the collection here is

6302 exactly what would be rendered inside the "SELECT" statement, and the

6303 :class:`_expression.ColumnElement` objects are directly present as they

6304 were given, e.g.::

6305

6306 col1 = column('q', Integer)

6307 col2 = column('p', Integer)

6308 stmt = select(col1, col2)

6309

6310 Above, ``stmt.selected_columns`` would be a collection that contains

6311 the ``col1`` and ``col2`` objects directly. For a statement that is

6312 against a :class:`_schema.Table` or other

6313 :class:`_expression.FromClause`, the collection will use the

6314 :class:`_expression.ColumnElement` objects that are in the

6315 :attr:`_expression.FromClause.c` collection of the from element.

6316

6317 A use case for the :attr:`_sql.Select.selected_columns` collection is

6318 to allow the existing columns to be referenced when adding additional

6319 criteria, e.g.::

6320

6321 def filter_on_id(my_select, id):

6322 return my_select.where(my_select.selected_columns['id'] == id)

6323

6324 stmt = select(MyModel)

6325

6326 # adds "WHERE id=:param" to the statement

6327 stmt = filter_on_id(stmt, 42)

6328

6329 .. note::

6330

6331 The :attr:`_sql.Select.selected_columns` collection does not

6332 include expressions established in the columns clause using the

6333 :func:`_sql.text` construct; these are silently omitted from the

6334 collection. To use plain textual column expressions inside of a

6335 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6336 construct.

6337

6338

6339 .. versionadded:: 1.4

6340

6341 """

6342

6343 # compare to SelectState._generate_columns_plus_names, which

6344 # generates the actual names used in the SELECT string. that

6345 # method is more complex because it also renders columns that are

6346 # fully ambiguous, e.g. same column more than once.

6347 conv = cast(

6348 "Callable[[Any], str]",

6349 SelectState._column_naming_convention(self._label_style),

6350 )

6351

6352 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6353 [

6354 (conv(c), c)

6355 for c in self._all_selected_columns

6356 if is_column_element(c)

6357 ]

6358 )

6359 return cc.as_readonly()

6360

6361 @HasMemoized_ro_memoized_attribute

6362 def _all_selected_columns(self) -> _SelectIterable:

6363 meth = SelectState.get_plugin_class(self).all_selected_columns

6364 return list(meth(self))

6365

6366 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6367 if self._label_style is LABEL_STYLE_NONE:

6368 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6369 return self

6370

6371 def _generate_fromclause_column_proxies(

6372 self,

6373 subquery: FromClause,

6374 *,

6375 proxy_compound_columns: Optional[

6376 Iterable[Sequence[ColumnElement[Any]]]

6377 ] = None,

6378 ) -> None:

6379 """Generate column proxies to place in the exported ``.c``

6380 collection of a subquery."""

6381

6382 if proxy_compound_columns:

6383 extra_col_iterator = proxy_compound_columns

6384 prox = [

6385 c._make_proxy(

6386 subquery,

6387 key=proxy_key,

6388 name=required_label_name,

6389 name_is_truncatable=True,

6390 compound_select_cols=extra_cols,

6391 )

6392 for (

6393 (

6394 required_label_name,

6395 proxy_key,

6396 fallback_label_name,

6397 c,

6398 repeated,

6399 ),

6400 extra_cols,

6401 ) in (

6402 zip(

6403 self._generate_columns_plus_names(False),

6404 extra_col_iterator,

6405 )

6406 )

6407 if is_column_element(c)

6408 ]

6409 else:

6410 prox = [

6411 c._make_proxy(

6412 subquery,

6413 key=proxy_key,

6414 name=required_label_name,

6415 name_is_truncatable=True,

6416 )

6417 for (

6418 required_label_name,

6419 proxy_key,

6420 fallback_label_name,

6421 c,

6422 repeated,

6423 ) in (self._generate_columns_plus_names(False))

6424 if is_column_element(c)

6425 ]

6426

6427 subquery._columns._populate_separate_keys(prox)

6428

6429 def _needs_parens_for_grouping(self) -> bool:

6430 return self._has_row_limiting_clause or bool(

6431 self._order_by_clause.clauses

6432 )

6433

6434 def self_group(

6435 self, against: Optional[OperatorType] = None

6436 ) -> Union[SelectStatementGrouping[Self], Self]:

6437 """Return a 'grouping' construct as per the

6438 :class:`_expression.ClauseElement` specification.

6439

6440 This produces an element that can be embedded in an expression. Note

6441 that this method is called automatically as needed when constructing

6442 expressions and should not require explicit use.

6443

6444 """

6445 if (

6446 isinstance(against, CompoundSelect)

6447 and not self._needs_parens_for_grouping()

6448 ):

6449 return self

6450 else:

6451 return SelectStatementGrouping(self)

6452

6453 def union(

6454 self, *other: _SelectStatementForCompoundArgument

6455 ) -> CompoundSelect:

6456 r"""Return a SQL ``UNION`` of this select() construct against

6457 the given selectables provided as positional arguments.

6458

6459 :param \*other: one or more elements with which to create a

6460 UNION.

6461

6462 .. versionchanged:: 1.4.28

6463

6464 multiple elements are now accepted.

6465

6466 :param \**kwargs: keyword arguments are forwarded to the constructor

6467 for the newly created :class:`_sql.CompoundSelect` object.

6468

6469 """

6470 return CompoundSelect._create_union(self, *other)

6471

6472 def union_all(

6473 self, *other: _SelectStatementForCompoundArgument

6474 ) -> CompoundSelect:

6475 r"""Return a SQL ``UNION ALL`` of this select() construct against

6476 the given selectables provided as positional arguments.

6477

6478 :param \*other: one or more elements with which to create a

6479 UNION.

6480

6481 .. versionchanged:: 1.4.28

6482

6483 multiple elements are now accepted.

6484

6485 :param \**kwargs: keyword arguments are forwarded to the constructor

6486 for the newly created :class:`_sql.CompoundSelect` object.

6487

6488 """

6489 return CompoundSelect._create_union_all(self, *other)

6490

6491 def except_(

6492 self, *other: _SelectStatementForCompoundArgument

6493 ) -> CompoundSelect:

6494 r"""Return a SQL ``EXCEPT`` of this select() construct against

6495 the given selectable provided as positional arguments.

6496

6497 :param \*other: one or more elements with which to create a

6498 UNION.

6499

6500 .. versionchanged:: 1.4.28

6501

6502 multiple elements are now accepted.

6503

6504 """

6505 return CompoundSelect._create_except(self, *other)

6506

6507 def except_all(

6508 self, *other: _SelectStatementForCompoundArgument

6509 ) -> CompoundSelect:

6510 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6511 the given selectables provided as positional arguments.

6512

6513 :param \*other: one or more elements with which to create a

6514 UNION.

6515

6516 .. versionchanged:: 1.4.28

6517

6518 multiple elements are now accepted.

6519

6520 """

6521 return CompoundSelect._create_except_all(self, *other)

6522

6523 def intersect(

6524 self, *other: _SelectStatementForCompoundArgument

6525 ) -> CompoundSelect:

6526 r"""Return a SQL ``INTERSECT`` of this select() construct against

6527 the given selectables provided as positional arguments.

6528

6529 :param \*other: one or more elements with which to create a

6530 UNION.

6531

6532 .. versionchanged:: 1.4.28

6533

6534 multiple elements are now accepted.

6535

6536 :param \**kwargs: keyword arguments are forwarded to the constructor

6537 for the newly created :class:`_sql.CompoundSelect` object.

6538

6539 """

6540 return CompoundSelect._create_intersect(self, *other)

6541

6542 def intersect_all(

6543 self, *other: _SelectStatementForCompoundArgument

6544 ) -> CompoundSelect:

6545 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6546 against the given selectables provided as positional arguments.

6547

6548 :param \*other: one or more elements with which to create a

6549 UNION.

6550

6551 .. versionchanged:: 1.4.28

6552

6553 multiple elements are now accepted.

6554

6555 :param \**kwargs: keyword arguments are forwarded to the constructor

6556 for the newly created :class:`_sql.CompoundSelect` object.

6557

6558 """

6559 return CompoundSelect._create_intersect_all(self, *other)

6560

6561

6562class ScalarSelect(

6563 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6564):

6565 """Represent a scalar subquery.

6566

6567

6568 A :class:`_sql.ScalarSelect` is created by invoking the

6569 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6570 then participates in other SQL expressions as a SQL column expression

6571 within the :class:`_sql.ColumnElement` hierarchy.

6572

6573 .. seealso::

6574

6575 :meth:`_sql.SelectBase.scalar_subquery`

6576

6577 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6578

6579 """

6580

6581 _traverse_internals: _TraverseInternalsType = [

6582 ("element", InternalTraversal.dp_clauseelement),

6583 ("type", InternalTraversal.dp_type),

6584 ]

6585

6586 _from_objects: List[FromClause] = []

6587 _is_from_container = True

6588 if not TYPE_CHECKING:

6589 _is_implicitly_boolean = False

6590 inherit_cache = True

6591

6592 element: SelectBase

6593

6594 def __init__(self, element: SelectBase) -> None:

6595 self.element = element

6596 self.type = element._scalar_type()

6597 self._propagate_attrs = element._propagate_attrs

6598

6599 def __getattr__(self, attr: str) -> Any:

6600 return getattr(self.element, attr)

6601

6602 def __getstate__(self) -> Dict[str, Any]:

6603 return {"element": self.element, "type": self.type}

6604

6605 def __setstate__(self, state: Dict[str, Any]) -> None:

6606 self.element = state["element"]

6607 self.type = state["type"]

6608

6609 @property

6610 def columns(self) -> NoReturn:

6611 raise exc.InvalidRequestError(

6612 "Scalar Select expression has no "

6613 "columns; use this object directly "

6614 "within a column-level expression."

6615 )

6616

6617 c = columns

6618

6619 @_generative

6620 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6621 """Apply a WHERE clause to the SELECT statement referred to

6622 by this :class:`_expression.ScalarSelect`.

6623

6624 """

6625 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6626 crit

6627 )

6628 return self

6629

6630 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6631 return self

6632

6633 if TYPE_CHECKING:

6634

6635 def _ungroup(self) -> Select[Unpack[TupleAny]]: ...

6636

6637 @_generative

6638 def correlate(

6639 self,

6640 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6641 ) -> Self:

6642 r"""Return a new :class:`_expression.ScalarSelect`

6643 which will correlate the given FROM

6644 clauses to that of an enclosing :class:`_expression.Select`.

6645

6646 This method is mirrored from the :meth:`_sql.Select.correlate` method

6647 of the underlying :class:`_sql.Select`. The method applies the

6648 :meth:_sql.Select.correlate` method, then returns a new

6649 :class:`_sql.ScalarSelect` against that statement.

6650

6651 .. versionadded:: 1.4 Previously, the

6652 :meth:`_sql.ScalarSelect.correlate`

6653 method was only available from :class:`_sql.Select`.

6654

6655 :param \*fromclauses: a list of one or more

6656 :class:`_expression.FromClause`

6657 constructs, or other compatible constructs (i.e. ORM-mapped

6658 classes) to become part of the correlate collection.

6659

6660 .. seealso::

6661

6662 :meth:`_expression.ScalarSelect.correlate_except`

6663

6664 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6665

6666

6667 """

6668 self.element = cast(

6669 "Select[Unpack[TupleAny]]", self.element

6670 ).correlate(*fromclauses)

6671 return self

6672

6673 @_generative

6674 def correlate_except(

6675 self,

6676 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6677 ) -> Self:

6678 r"""Return a new :class:`_expression.ScalarSelect`

6679 which will omit the given FROM

6680 clauses from the auto-correlation process.

6681

6682 This method is mirrored from the

6683 :meth:`_sql.Select.correlate_except` method of the underlying

6684 :class:`_sql.Select`. The method applies the

6685 :meth:_sql.Select.correlate_except` method, then returns a new

6686 :class:`_sql.ScalarSelect` against that statement.

6687

6688 .. versionadded:: 1.4 Previously, the

6689 :meth:`_sql.ScalarSelect.correlate_except`

6690 method was only available from :class:`_sql.Select`.

6691

6692 :param \*fromclauses: a list of one or more

6693 :class:`_expression.FromClause`

6694 constructs, or other compatible constructs (i.e. ORM-mapped

6695 classes) to become part of the correlate-exception collection.

6696

6697 .. seealso::

6698

6699 :meth:`_expression.ScalarSelect.correlate`

6700

6701 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6702

6703

6704 """

6705

6706 self.element = cast(

6707 "Select[Unpack[TupleAny]]", self.element

6708 ).correlate_except(*fromclauses)

6709 return self

6710

6711

6712class Exists(UnaryExpression[bool]):

6713 """Represent an ``EXISTS`` clause.

6714

6715 See :func:`_sql.exists` for a description of usage.

6716

6717 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6718 instance by calling :meth:`_sql.SelectBase.exists`.

6719

6720 """

6721

6722 inherit_cache = True

6723 element: Union[

6724 SelectStatementGrouping[Select[Unpack[TupleAny]]],

6725 ScalarSelect[Any],

6726 ]

6727

6728 def __init__(

6729 self,

6730 __argument: Optional[

6731 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6732 ] = None,

6733 /,

6734 ):

6735 s: ScalarSelect[Any]

6736

6737 # TODO: this seems like we should be using coercions for this

6738 if __argument is None:

6739 s = Select(literal_column("*")).scalar_subquery()

6740 elif isinstance(__argument, SelectBase):

6741 s = __argument.scalar_subquery()

6742 s._propagate_attrs = __argument._propagate_attrs

6743 elif isinstance(__argument, ScalarSelect):

6744 s = __argument

6745 else:

6746 s = Select(__argument).scalar_subquery()

6747

6748 UnaryExpression.__init__(

6749 self,

6750 s,

6751 operator=operators.exists,

6752 type_=type_api.BOOLEANTYPE,

6753 wraps_column_expression=True,

6754 )

6755

6756 @util.ro_non_memoized_property

6757 def _from_objects(self) -> List[FromClause]:

6758 return []

6759

6760 def _regroup(

6761 self,

6762 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

6763 ) -> SelectStatementGrouping[Select[Unpack[TupleAny]]]:

6764 element = self.element._ungroup()

6765 new_element = fn(element)

6766

6767 return_value = new_element.self_group(against=operators.exists)

6768 assert isinstance(return_value, SelectStatementGrouping)

6769 return return_value

6770

6771 def select(self) -> Select[bool]:

6772 r"""Return a SELECT of this :class:`_expression.Exists`.

6773

6774 e.g.::

6775

6776 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6777

6778 This will produce a statement resembling::

6779

6780 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6781

6782 .. seealso::

6783

6784 :func:`_expression.select` - general purpose

6785 method which allows for arbitrary column lists.

6786

6787 """ # noqa

6788

6789 return Select(self)

6790

6791 def correlate(

6792 self,

6793 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6794 ) -> Self:

6795 """Apply correlation to the subquery noted by this

6796 :class:`_sql.Exists`.

6797

6798 .. seealso::

6799

6800 :meth:`_sql.ScalarSelect.correlate`

6801

6802 """

6803 e = self._clone()

6804 e.element = self._regroup(

6805 lambda element: element.correlate(*fromclauses)

6806 )

6807 return e

6808

6809 def correlate_except(

6810 self,

6811 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6812 ) -> Self:

6813 """Apply correlation to the subquery noted by this

6814 :class:`_sql.Exists`.

6815

6816 .. seealso::

6817

6818 :meth:`_sql.ScalarSelect.correlate_except`

6819

6820 """

6821

6822 e = self._clone()

6823 e.element = self._regroup(

6824 lambda element: element.correlate_except(*fromclauses)

6825 )

6826 return e

6827

6828 def select_from(self, *froms: _FromClauseArgument) -> Self:

6829 """Return a new :class:`_expression.Exists` construct,

6830 applying the given

6831 expression to the :meth:`_expression.Select.select_from`

6832 method of the select

6833 statement contained.

6834

6835 .. note:: it is typically preferable to build a :class:`_sql.Select`

6836 statement first, including the desired WHERE clause, then use the

6837 :meth:`_sql.SelectBase.exists` method to produce an

6838 :class:`_sql.Exists` object at once.

6839

6840 """

6841 e = self._clone()

6842 e.element = self._regroup(lambda element: element.select_from(*froms))

6843 return e

6844

6845 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

6846 """Return a new :func:`_expression.exists` construct with the

6847 given expression added to

6848 its WHERE clause, joined to the existing clause via AND, if any.

6849

6850

6851 .. note:: it is typically preferable to build a :class:`_sql.Select`

6852 statement first, including the desired WHERE clause, then use the

6853 :meth:`_sql.SelectBase.exists` method to produce an

6854 :class:`_sql.Exists` object at once.

6855

6856 """

6857 e = self._clone()

6858 e.element = self._regroup(lambda element: element.where(*clause))

6859 return e

6860

6861

6862class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

6863 """Wrap a :class:`_expression.TextClause` construct within a

6864 :class:`_expression.SelectBase`

6865 interface.

6866

6867 This allows the :class:`_expression.TextClause` object to gain a

6868 ``.c`` collection

6869 and other FROM-like capabilities such as

6870 :meth:`_expression.FromClause.alias`,

6871 :meth:`_expression.SelectBase.cte`, etc.

6872

6873 The :class:`_expression.TextualSelect` construct is produced via the

6874 :meth:`_expression.TextClause.columns`

6875 method - see that method for details.

6876

6877 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

6878 class was renamed

6879 from ``TextAsFrom``, to more correctly suit its role as a

6880 SELECT-oriented object and not a FROM clause.

6881

6882 .. seealso::

6883

6884 :func:`_expression.text`

6885

6886 :meth:`_expression.TextClause.columns` - primary creation interface.

6887

6888 """

6889

6890 __visit_name__ = "textual_select"

6891

6892 _label_style = LABEL_STYLE_NONE

6893

6894 _traverse_internals: _TraverseInternalsType = (

6895 [

6896 ("element", InternalTraversal.dp_clauseelement),

6897 ("column_args", InternalTraversal.dp_clauseelement_list),

6898 ]

6899 + SupportsCloneAnnotations._clone_annotations_traverse_internals

6900 + HasCTE._has_ctes_traverse_internals

6901 )

6902

6903 _is_textual = True

6904

6905 is_text = True

6906 is_select = True

6907

6908 def __init__(

6909 self,

6910 text: TextClause,

6911 columns: List[_ColumnExpressionArgument[Any]],

6912 positional: bool = False,

6913 ) -> None:

6914 self._init(

6915 text,

6916 # convert for ORM attributes->columns, etc

6917 [

6918 coercions.expect(roles.LabeledColumnExprRole, c)

6919 for c in columns

6920 ],

6921 positional,

6922 )

6923

6924 def _init(

6925 self,

6926 text: TextClause,

6927 columns: List[NamedColumn[Any]],

6928 positional: bool = False,

6929 ) -> None:

6930 self.element = text

6931 self.column_args = columns

6932 self.positional = positional

6933

6934 @HasMemoized_ro_memoized_attribute

6935 def selected_columns(

6936 self,

6937 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

6938 """A :class:`_expression.ColumnCollection`

6939 representing the columns that

6940 this SELECT statement or similar construct returns in its result set,

6941 not including :class:`_sql.TextClause` constructs.

6942

6943 This collection differs from the :attr:`_expression.FromClause.columns`

6944 collection of a :class:`_expression.FromClause` in that the columns

6945 within this collection cannot be directly nested inside another SELECT

6946 statement; a subquery must be applied first which provides for the

6947 necessary parenthesization required by SQL.

6948

6949 For a :class:`_expression.TextualSelect` construct, the collection

6950 contains the :class:`_expression.ColumnElement` objects that were

6951 passed to the constructor, typically via the

6952 :meth:`_expression.TextClause.columns` method.

6953

6954

6955 .. versionadded:: 1.4

6956

6957 """

6958 return ColumnCollection(

6959 (c.key, c) for c in self.column_args

6960 ).as_readonly()

6961

6962 @util.ro_non_memoized_property

6963 def _all_selected_columns(self) -> _SelectIterable:

6964 return self.column_args

6965

6966 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

6967 return self

6968

6969 def _ensure_disambiguated_names(self) -> TextualSelect:

6970 return self

6971

6972 @_generative

6973 def bindparams(

6974 self,

6975 *binds: BindParameter[Any],

6976 **bind_as_values: Any,

6977 ) -> Self:

6978 self.element = self.element.bindparams(*binds, **bind_as_values)

6979 return self

6980

6981 def _generate_fromclause_column_proxies(

6982 self,

6983 fromclause: FromClause,

6984 *,

6985 proxy_compound_columns: Optional[

6986 Iterable[Sequence[ColumnElement[Any]]]

6987 ] = None,

6988 ) -> None:

6989 if TYPE_CHECKING:

6990 assert isinstance(fromclause, Subquery)

6991

6992 if proxy_compound_columns:

6993 fromclause._columns._populate_separate_keys(

6994 c._make_proxy(fromclause, compound_select_cols=extra_cols)

6995 for c, extra_cols in zip(

6996 self.column_args, proxy_compound_columns

6997 )

6998 )

6999 else:

7000 fromclause._columns._populate_separate_keys(

7001 c._make_proxy(fromclause) for c in self.column_args

7002 )

7003

7004 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7005 return self.column_args[0].type

7006

7007

7008TextAsFrom = TextualSelect

7009"""Backwards compatibility with the previous name"""

7010

7011

7012class AnnotatedFromClause(Annotated):

7013 def _copy_internals(self, **kw: Any) -> None:

7014 super()._copy_internals(**kw)

7015 if kw.get("ind_cols_on_fromclause", False):

7016 ee = self._Annotated__element # type: ignore

7017

7018 self.c = ee.__class__.c.fget(self) # type: ignore

7019

7020 @util.ro_memoized_property

7021 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7022 """proxy the .c collection of the underlying FromClause.

7023

7024 Originally implemented in 2008 as a simple load of the .c collection

7025 when the annotated construct was created (see d3621ae961a), in modern

7026 SQLAlchemy versions this can be expensive for statements constructed

7027 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7028 it, which works just as well.

7029

7030 Two different use cases seem to require the collection either copied

7031 from the underlying one, or unique to this AnnotatedFromClause.

7032

7033 See test_selectable->test_annotated_corresponding_column

7034

7035 """

7036 ee = self._Annotated__element # type: ignore

7037 return ee.c # type: ignore