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

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

1163 FROM table1 JOIN table2 ON true

1164

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

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

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

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

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

1170

1171

1172 .. versionadded:: 1.4

1173

1174 """ # noqa: E501

1175

1176 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1179 when generating the columns clause of a SELECT statement.

1180

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

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

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

1184

1185 .. sourcecode:: pycon+sql

1186

1187 >>> from sqlalchemy import (

1188 ... table,

1189 ... column,

1190 ... select,

1191 ... true,

1192 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1193 ... )

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

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

1196 >>> print(

1197 ... select(table1, table2)

1198 ... .join(table2, true())

1199 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1200 ... )

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

1202 FROM table1 JOIN table2 ON true

1203

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

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

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

1207

1208 .. versionadded:: 1.4

1209

1210 """ # noqa: E501

1211

1212 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1213 """The default label style, refers to

1214 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1215

1216 .. versionadded:: 1.4

1217

1218 """

1219

1220 LABEL_STYLE_LEGACY_ORM = 3

1221

1222

1223(

1224 LABEL_STYLE_NONE,

1225 LABEL_STYLE_TABLENAME_PLUS_COL,

1226 LABEL_STYLE_DISAMBIGUATE_ONLY,

1227 _,

1228) = list(SelectLabelStyle)

1229

1230LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1231

1232

1233class Join(roles.DMLTableRole, FromClause):

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

1235 :class:`_expression.FromClause`

1236 elements.

1237

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

1239 is the module-level

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

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

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

1243 :class:`_schema.Table`).

1244

1245 .. seealso::

1246

1247 :func:`_expression.join`

1248

1249 :meth:`_expression.FromClause.join`

1250

1251 """

1252

1253 __visit_name__ = "join"

1254

1255 _traverse_internals: _TraverseInternalsType = [

1256 ("left", InternalTraversal.dp_clauseelement),

1257 ("right", InternalTraversal.dp_clauseelement),

1258 ("onclause", InternalTraversal.dp_clauseelement),

1259 ("isouter", InternalTraversal.dp_boolean),

1260 ("full", InternalTraversal.dp_boolean),

1261 ]

1262

1263 _is_join = True

1264

1265 left: FromClause

1266 right: FromClause

1267 onclause: Optional[ColumnElement[bool]]

1268 isouter: bool

1269 full: bool

1270

1271 def __init__(

1272 self,

1273 left: _FromClauseArgument,

1274 right: _FromClauseArgument,

1275 onclause: Optional[_OnClauseArgument] = None,

1276 isouter: bool = False,

1277 full: bool = False,

1278 ):

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

1280

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

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

1283 :class:`_expression.FromClause` object.

1284

1285 """

1286

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

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

1289 # annotated objects. test_orm.py -> test_fetch_results

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

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

1292 # callcounts for a single compilation in that particular test

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

1294 # 29200 -> 30373

1295

1296 self.left = coercions.expect(

1297 roles.FromClauseRole,

1298 left,

1299 )

1300 self.right = coercions.expect(

1301 roles.FromClauseRole,

1302 right,

1303 ).self_group()

1304

1305 if onclause is None:

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

1307 else:

1308 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1309 # not merged yet

1310 self.onclause = coercions.expect(

1311 roles.OnClauseRole, onclause

1312 ).self_group(against=operators._asbool)

1313

1314 self.isouter = isouter

1315 self.full = full

1316

1317 @util.ro_non_memoized_property

1318 def description(self) -> str:

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

1320 self.left.description,

1321 id(self.left),

1322 self.right.description,

1323 id(self.right),

1324 )

1325

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

1327 return (

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

1329 # as well

1330 hash(fromclause) == hash(self)

1331 or self.left.is_derived_from(fromclause)

1332 or self.right.is_derived_from(fromclause)

1333 )

1334

1335 def self_group(

1336 self, against: Optional[OperatorType] = None

1337 ) -> FromGrouping:

1338 return FromGrouping(self)

1339

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

1341 def _populate_column_collection(

1342 self,

1343 columns: ColumnCollection[str, KeyedColumnElement[Any]],

1344 primary_key: ColumnSet,

1345 foreign_keys: Set[KeyedColumnElement[Any]],

1346 ) -> None:

1347 sqlutil = util.preloaded.sql_util

1348 _columns: List[KeyedColumnElement[Any]] = [c for c in self.left.c] + [

1349 c for c in self.right.c

1350 ]

1351

1352 primary_key.extend(

1353 sqlutil.reduce_columns(

1354 (c for c in _columns if c.primary_key), self.onclause

1355 )

1356 )

1357 columns._populate_separate_keys(

1358 (col._tq_key_label, col) for col in _columns # type: ignore

1359 )

1360 foreign_keys.update(

1361 itertools.chain(*[col.foreign_keys for col in _columns]) # type: ignore # noqa: E501

1362 )

1363

1364 def _copy_internals(

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

1366 ) -> None:

1367 # see Select._copy_internals() for similar concept

1368

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

1370 # determine the new FROM clauses

1371 all_the_froms = set(

1372 itertools.chain(

1373 _from_objects(self.left),

1374 _from_objects(self.right),

1375 )

1376 )

1377

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

1379 # cache used by the clone function

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

1381

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

1383 # ColumnClause with parent table referring to those

1384 # replaced FromClause objects

1385 def replace(

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

1387 **kw: Any,

1388 ) -> Optional[KeyedColumnElement[Any]]:

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

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

1391 return newelem

1392 return None

1393

1394 kw["replace"] = replace

1395

1396 # run normal _copy_internals. the clones for

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

1398 # cache

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

1400

1401 self._reset_memoizations()

1402

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

1404 super()._refresh_for_new_column(column)

1405 self.left._refresh_for_new_column(column)

1406 self.right._refresh_for_new_column(column)

1407

1408 def _match_primaries(

1409 self,

1410 left: FromClause,

1411 right: FromClause,

1412 ) -> ColumnElement[bool]:

1413 if isinstance(left, Join):

1414 left_right = left.right

1415 else:

1416 left_right = None

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

1418

1419 @classmethod

1420 def _join_condition(

1421 cls,

1422 a: FromClause,

1423 b: FromClause,

1424 *,

1425 a_subset: Optional[FromClause] = None,

1426 consider_as_foreign_keys: Optional[

1427 AbstractSet[ColumnClause[Any]]

1428 ] = None,

1429 ) -> ColumnElement[bool]:

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

1431

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

1433

1434 """

1435 constraints = cls._joincond_scan_left_right(

1436 a, a_subset, b, consider_as_foreign_keys

1437 )

1438

1439 if len(constraints) > 1:

1440 cls._joincond_trim_constraints(

1441 a, b, constraints, consider_as_foreign_keys

1442 )

1443

1444 if len(constraints) == 0:

1445 if isinstance(b, FromGrouping):

1446 hint = (

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

1448 "subquery using alias()?"

1449 )

1450 else:

1451 hint = ""

1452 raise exc.NoForeignKeysError(

1453 "Can't find any foreign key relationships "

1454 "between '%s' and '%s'.%s"

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

1456 )

1457

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

1459 if len(crit) == 1:

1460 return crit[0]

1461 else:

1462 return and_(*crit)

1463

1464 @classmethod

1465 def _can_join(

1466 cls,

1467 left: FromClause,

1468 right: FromClause,

1469 *,

1470 consider_as_foreign_keys: Optional[

1471 AbstractSet[ColumnClause[Any]]

1472 ] = None,

1473 ) -> bool:

1474 if isinstance(left, Join):

1475 left_right = left.right

1476 else:

1477 left_right = None

1478

1479 constraints = cls._joincond_scan_left_right(

1480 a=left,

1481 b=right,

1482 a_subset=left_right,

1483 consider_as_foreign_keys=consider_as_foreign_keys,

1484 )

1485

1486 return bool(constraints)

1487

1488 @classmethod

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

1490 def _joincond_scan_left_right(

1491 cls,

1492 a: FromClause,

1493 a_subset: Optional[FromClause],

1494 b: FromClause,

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

1496 ) -> collections.defaultdict[

1497 Optional[ForeignKeyConstraint],

1498 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1499 ]:

1500 sql_util = util.preloaded.sql_util

1501

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

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

1504

1505 constraints: collections.defaultdict[

1506 Optional[ForeignKeyConstraint],

1507 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1508 ] = collections.defaultdict(list)

1509

1510 for left in (a_subset, a):

1511 if left is None:

1512 continue

1513 for fk in sorted(

1514 b.foreign_keys,

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

1516 ):

1517 if (

1518 consider_as_foreign_keys is not None

1519 and fk.parent not in consider_as_foreign_keys

1520 ):

1521 continue

1522 try:

1523 col = fk.get_referent(left)

1524 except exc.NoReferenceError as nrte:

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

1526 if nrte.table_name in table_names:

1527 raise

1528 else:

1529 continue

1530

1531 if col is not None:

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

1533 if left is not b:

1534 for fk in sorted(

1535 left.foreign_keys,

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

1537 ):

1538 if (

1539 consider_as_foreign_keys is not None

1540 and fk.parent not in consider_as_foreign_keys

1541 ):

1542 continue

1543 try:

1544 col = fk.get_referent(b)

1545 except exc.NoReferenceError as nrte:

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

1547 if nrte.table_name in table_names:

1548 raise

1549 else:

1550 continue

1551

1552 if col is not None:

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

1554 if constraints:

1555 break

1556 return constraints

1557

1558 @classmethod

1559 def _joincond_trim_constraints(

1560 cls,

1561 a: FromClause,

1562 b: FromClause,

1563 constraints: Dict[Any, Any],

1564 consider_as_foreign_keys: Optional[Any],

1565 ) -> None:

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

1567 # to include just those FKCs that match exactly to

1568 # "consider_as_foreign_keys".

1569 if consider_as_foreign_keys:

1570 for const in list(constraints):

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

1572 consider_as_foreign_keys

1573 ):

1574 del constraints[const]

1575

1576 # if still multiple constraints, but

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

1578 if len(constraints) > 1:

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

1580 if len(dedupe) == 1:

1581 key = list(constraints)[0]

1582 constraints = {key: constraints[key]}

1583

1584 if len(constraints) != 1:

1585 raise exc.AmbiguousForeignKeysError(

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

1587 "tables have more than one foreign key "

1588 "constraint relationship between them. "

1589 "Please specify the 'onclause' of this "

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

1591 )

1592

1593 def select(self) -> Select[Any]:

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

1595 :class:`_expression.Join`.

1596

1597 E.g.::

1598

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

1600

1601 stmt = stmt.select()

1602

1603 The above will produce a SQL string resembling:

1604

1605 .. sourcecode:: sql

1606

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

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

1609

1610 """

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

1612

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

1614 def _anonymous_fromclause(

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

1616 ) -> TODO_Any:

1617 sqlutil = util.preloaded.sql_util

1618 if flat:

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

1620 left_name = name # will recurse

1621 else:

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

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

1624 else:

1625 left_name = name

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

1627 right_name = name # will recurse

1628 else:

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

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

1631 else:

1632 right_name = name

1633 left_a, right_a = (

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

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

1636 )

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

1638 sqlutil.ClauseAdapter(right_a)

1639 )

1640

1641 return left_a.join(

1642 right_a,

1643 adapter.traverse(self.onclause),

1644 isouter=self.isouter,

1645 full=self.full,

1646 )

1647 else:

1648 return (

1649 self.select()

1650 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1651 .correlate(None)

1652 .alias(name)

1653 )

1654

1655 @util.ro_non_memoized_property

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

1657 return itertools.chain(

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

1659 )

1660

1661 @util.ro_non_memoized_property

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

1663 self_list: List[FromClause] = [self]

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

1665

1666

1667class NoInit:

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

1669 raise NotImplementedError(

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

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

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

1673 "selectable objects."

1674 % (

1675 self.__class__.__name__,

1676 self.__class__.__name__.lower(),

1677 self.__class__.__name__.lower(),

1678 )

1679 )

1680

1681

1682class LateralFromClause(NamedFromClause):

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

1684

1685

1686# FromClause ->

1687# AliasedReturnsRows

1688# -> Alias only for FromClause

1689# -> Subquery only for SelectBase

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

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

1692# w/ non-deprecated coercion

1693# -> TableSample -> only for FromClause

1694

1695

1696class AliasedReturnsRows(NoInit, NamedFromClause):

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

1698 selectables."""

1699

1700 _is_from_container = True

1701

1702 _supports_derived_columns = False

1703

1704 element: ReturnsRows

1705

1706 _traverse_internals: _TraverseInternalsType = [

1707 ("element", InternalTraversal.dp_clauseelement),

1708 ("name", InternalTraversal.dp_anon_name),

1709 ]

1710

1711 @classmethod

1712 def _construct(

1713 cls,

1714 selectable: Any,

1715 *,

1716 name: Optional[str] = None,

1717 **kw: Any,

1718 ) -> Self:

1719 obj = cls.__new__(cls)

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

1721 return obj

1722

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

1724 self.element = coercions.expect(

1725 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1726 )

1727 self.element = selectable

1728 self._orig_name = name

1729 if name is None:

1730 if (

1731 isinstance(selectable, FromClause)

1732 and selectable.named_with_column

1733 ):

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

1735 if isinstance(name, _anonymous_label):

1736 name = None

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

1738 self.name = name

1739

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

1741 super()._refresh_for_new_column(column)

1742 self.element._refresh_for_new_column(column)

1743

1744 def _populate_column_collection(

1745 self,

1746 columns: ColumnCollection[str, KeyedColumnElement[Any]],

1747 primary_key: ColumnSet,

1748 foreign_keys: Set[KeyedColumnElement[Any]],

1749 ) -> None:

1750 self.element._generate_fromclause_column_proxies(

1751 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1752 )

1753

1754 @util.ro_non_memoized_property

1755 def description(self) -> str:

1756 name = self.name

1757 if isinstance(name, _anonymous_label):

1758 return "anon_1"

1759

1760 return name

1761

1762 @util.ro_non_memoized_property

1763 def implicit_returning(self) -> bool:

1764 return self.element.implicit_returning # type: ignore

1765

1766 @property

1767 def original(self) -> ReturnsRows:

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

1769 return self.element

1770

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

1772 if fromclause in self._cloned_set:

1773 return True

1774 return self.element.is_derived_from(fromclause)

1775

1776 def _copy_internals(

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

1778 ) -> None:

1779 existing_element = self.element

1780

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

1782

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

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

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

1786 # performance.

1787 if existing_element is not self.element:

1788 self._reset_column_collection()

1789

1790 @property

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

1792 return [self]

1793

1794

1795class FromClauseAlias(AliasedReturnsRows):

1796 element: FromClause

1797

1798 @util.ro_non_memoized_property

1799 def description(self) -> str:

1800 name = self.name

1801 if isinstance(name, _anonymous_label):

1802 return f"Anonymous alias of {self.element.description}"

1803

1804 return name

1805

1806

1807class Alias(roles.DMLTableRole, FromClauseAlias):

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

1809

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

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

1812 without the keyword on certain databases such as Oracle Database).

1813

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

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

1816 method available

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

1818

1819 .. seealso::

1820

1821 :meth:`_expression.FromClause.alias`

1822

1823 """

1824

1825 __visit_name__ = "alias"

1826

1827 inherit_cache = True

1828

1829 element: FromClause

1830

1831 @classmethod

1832 def _factory(

1833 cls,

1834 selectable: FromClause,

1835 name: Optional[str] = None,

1836 flat: bool = False,

1837 ) -> NamedFromClause:

1838 return coercions.expect(

1839 roles.FromClauseRole, selectable, allow_select=True

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

1841

1842

1843class TableValuedAlias(LateralFromClause, Alias):

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

1845

1846 This construct provides for a SQL function that returns columns

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

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

1849 method, e.g.:

1850

1851 .. sourcecode:: pycon+sql

1852

1853 >>> from sqlalchemy import select, func

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

1855 ... "value"

1856 ... )

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

1858 {printsql}SELECT anon_1.value

1859 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1860

1861 .. versionadded:: 1.4.0b2

1862

1863 .. seealso::

1864

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

1866

1867 """ # noqa: E501

1868

1869 __visit_name__ = "table_valued_alias"

1870

1871 _supports_derived_columns = True

1872 _render_derived = False

1873 _render_derived_w_types = False

1874 joins_implicitly = False

1875

1876 _traverse_internals: _TraverseInternalsType = [

1877 ("element", InternalTraversal.dp_clauseelement),

1878 ("name", InternalTraversal.dp_anon_name),

1879 ("_tableval_type", InternalTraversal.dp_type),

1880 ("_render_derived", InternalTraversal.dp_boolean),

1881 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1882 ]

1883

1884 def _init(

1885 self,

1886 selectable: Any,

1887 *,

1888 name: Optional[str] = None,

1889 table_value_type: Optional[TableValueType] = None,

1890 joins_implicitly: bool = False,

1891 ) -> None:

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

1893

1894 self.joins_implicitly = joins_implicitly

1895 self._tableval_type = (

1896 type_api.TABLEVALUE

1897 if table_value_type is None

1898 else table_value_type

1899 )

1900

1901 @HasMemoized.memoized_attribute

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

1903 """Return a column expression representing this

1904 :class:`_sql.TableValuedAlias`.

1905

1906 This accessor is used to implement the

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

1908 method for further details.

1909

1910 E.g.:

1911

1912 .. sourcecode:: pycon+sql

1913

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

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

1916

1917 .. seealso::

1918

1919 :meth:`_functions.FunctionElement.column_valued`

1920

1921 """

1922

1923 return TableValuedColumn(self, self._tableval_type)

1924

1925 def alias(

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

1927 ) -> TableValuedAlias:

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

1929

1930 This creates a distinct FROM object that will be distinguished

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

1932

1933 """

1934

1935 tva: TableValuedAlias = TableValuedAlias._construct(

1936 self,

1937 name=name,

1938 table_value_type=self._tableval_type,

1939 joins_implicitly=self.joins_implicitly,

1940 )

1941

1942 if self._render_derived:

1943 tva._render_derived = True

1944 tva._render_derived_w_types = self._render_derived_w_types

1945

1946 return tva

1947

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

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

1950 set, so that it renders as LATERAL.

1951

1952 .. seealso::

1953

1954 :func:`_expression.lateral`

1955

1956 """

1957 tva = self.alias(name=name)

1958 tva._is_lateral = True

1959 return tva

1960

1961 def render_derived(

1962 self,

1963 name: Optional[str] = None,

1964 with_types: bool = False,

1965 ) -> TableValuedAlias:

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

1967

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

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

1970

1971 .. sourcecode:: pycon+sql

1972

1973 >>> print(

1974 ... select(

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

1976 ... .table_valued("x", with_ordinality="o")

1977 ... .render_derived()

1978 ... )

1979 ... )

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

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

1982

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

1984 the alias expression (this syntax currently applies to the

1985 PostgreSQL database):

1986

1987 .. sourcecode:: pycon+sql

1988

1989 >>> print(

1990 ... select(

1991 ... func.json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]')

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

1993 ... .render_derived(with_types=True)

1994 ... )

1995 ... )

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

1997 AS anon_1(a INTEGER, b VARCHAR)

1998

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

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

2001

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

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

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

2005

2006 """ # noqa: E501

2007

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

2009 # to the original object. otherwise you can have reuse of the

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

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

2012 # (just saw it happen on CI)

2013

2014 # construct against original to prevent memory growth

2015 # for repeated generations

2016 new_alias: TableValuedAlias = TableValuedAlias._construct(

2017 self.element,

2018 name=name,

2019 table_value_type=self._tableval_type,

2020 joins_implicitly=self.joins_implicitly,

2021 )

2022 new_alias._render_derived = True

2023 new_alias._render_derived_w_types = with_types

2024 return new_alias

2025

2026

2027class Lateral(FromClauseAlias, LateralFromClause):

2028 """Represent a LATERAL subquery.

2029

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

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

2032 method available

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

2034

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

2036 PostgreSQL versions provide support for this keyword.

2037

2038 .. seealso::

2039

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

2041

2042 """

2043

2044 __visit_name__ = "lateral"

2045 _is_lateral = True

2046

2047 inherit_cache = True

2048

2049 @classmethod

2050 def _factory(

2051 cls,

2052 selectable: Union[SelectBase, _FromClauseArgument],

2053 name: Optional[str] = None,

2054 ) -> LateralFromClause:

2055 return coercions.expect(

2056 roles.FromClauseRole, selectable, explicit_subquery=True

2057 ).lateral(name=name)

2058

2059

2060class TableSample(FromClauseAlias):

2061 """Represent a TABLESAMPLE clause.

2062

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

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

2065 method

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

2067

2068 .. seealso::

2069

2070 :func:`_expression.tablesample`

2071

2072 """

2073

2074 __visit_name__ = "tablesample"

2075

2076 _traverse_internals: _TraverseInternalsType = (

2077 AliasedReturnsRows._traverse_internals

2078 + [

2079 ("sampling", InternalTraversal.dp_clauseelement),

2080 ("seed", InternalTraversal.dp_clauseelement),

2081 ]

2082 )

2083

2084 @classmethod

2085 def _factory(

2086 cls,

2087 selectable: _FromClauseArgument,

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

2089 name: Optional[str] = None,

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

2091 ) -> TableSample:

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

2093 sampling, name=name, seed=seed

2094 )

2095

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

2097 def _init( # type: ignore[override]

2098 self,

2099 selectable: Any,

2100 *,

2101 name: Optional[str] = None,

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

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

2104 ) -> None:

2105 assert sampling is not None

2106 functions = util.preloaded.sql_functions

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

2108 sampling = functions.func.system(sampling)

2109

2110 self.sampling: Function[Any] = sampling

2111 self.seed = seed

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

2113

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

2115 return self.sampling

2116

2117

2118class CTE(

2119 roles.DMLTableRole,

2120 roles.IsCTERole,

2121 Generative,

2122 HasPrefixes,

2123 HasSuffixes,

2124 AliasedReturnsRows,

2125):

2126 """Represent a Common Table Expression.

2127

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

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

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

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

2132 :class:`_sql.Update` and

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

2134 usage details on CTEs.

2135

2136 .. seealso::

2137

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

2139

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

2141

2142 """

2143

2144 __visit_name__ = "cte"

2145

2146 _traverse_internals: _TraverseInternalsType = (

2147 AliasedReturnsRows._traverse_internals

2148 + [

2149 ("_cte_alias", InternalTraversal.dp_clauseelement),

2150 ("_restates", InternalTraversal.dp_clauseelement),

2151 ("recursive", InternalTraversal.dp_boolean),

2152 ("nesting", InternalTraversal.dp_boolean),

2153 ]

2154 + HasPrefixes._has_prefixes_traverse_internals

2155 + HasSuffixes._has_suffixes_traverse_internals

2156 )

2157

2158 element: HasCTE

2159

2160 @classmethod

2161 def _factory(

2162 cls,

2163 selectable: HasCTE,

2164 name: Optional[str] = None,

2165 recursive: bool = False,

2166 ) -> CTE:

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

2168 or Common Table Expression instance.

2169

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

2171

2172 """

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

2174 name=name, recursive=recursive

2175 )

2176

2177 def _init(

2178 self,

2179 selectable: Select[Any],

2180 *,

2181 name: Optional[str] = None,

2182 recursive: bool = False,

2183 nesting: bool = False,

2184 _cte_alias: Optional[CTE] = None,

2185 _restates: Optional[CTE] = None,

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

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

2188 ) -> None:

2189 self.recursive = recursive

2190 self.nesting = nesting

2191 self._cte_alias = _cte_alias

2192 # Keep recursivity reference with union/union_all

2193 self._restates = _restates

2194 if _prefixes:

2195 self._prefixes = _prefixes

2196 if _suffixes:

2197 self._suffixes = _suffixes

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

2199

2200 def _populate_column_collection(

2201 self,

2202 columns: ColumnCollection[str, KeyedColumnElement[Any]],

2203 primary_key: ColumnSet,

2204 foreign_keys: Set[KeyedColumnElement[Any]],

2205 ) -> None:

2206 if self._cte_alias is not None:

2207 self._cte_alias._generate_fromclause_column_proxies(

2208 self,

2209 columns,

2210 primary_key=primary_key,

2211 foreign_keys=foreign_keys,

2212 )

2213 else:

2214 self.element._generate_fromclause_column_proxies(

2215 self,

2216 columns,

2217 primary_key=primary_key,

2218 foreign_keys=foreign_keys,

2219 )

2220

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

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

2223 :class:`_expression.CTE`.

2224

2225 This method is a CTE-specific specialization of the

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

2227

2228 .. seealso::

2229

2230 :ref:`tutorial_using_aliases`

2231

2232 :func:`_expression.alias`

2233

2234 """

2235 return CTE._construct(

2236 self.element,

2237 name=name,

2238 recursive=self.recursive,

2239 nesting=self.nesting,

2240 _cte_alias=self,

2241 _prefixes=self._prefixes,

2242 _suffixes=self._suffixes,

2243 )

2244

2245 def union(self, *other: _SelectStatementForCompoundArgument[Any]) -> CTE:

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

2247 of the original CTE against the given selectables provided

2248 as positional arguments.

2249

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

2251 UNION.

2252

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

2254

2255 .. seealso::

2256

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

2258

2259 """

2260 assert is_select_statement(

2261 self.element

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

2263

2264 return CTE._construct(

2265 self.element.union(*other),

2266 name=self.name,

2267 recursive=self.recursive,

2268 nesting=self.nesting,

2269 _restates=self,

2270 _prefixes=self._prefixes,

2271 _suffixes=self._suffixes,

2272 )

2273

2274 def union_all(

2275 self, *other: _SelectStatementForCompoundArgument[Any]

2276 ) -> CTE:

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

2278 of the original CTE against the given selectables provided

2279 as positional arguments.

2280

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

2282 UNION.

2283

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

2285

2286 .. seealso::

2287

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

2289

2290 """

2291

2292 assert is_select_statement(

2293 self.element

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

2295

2296 return CTE._construct(

2297 self.element.union_all(*other),

2298 name=self.name,

2299 recursive=self.recursive,

2300 nesting=self.nesting,

2301 _restates=self,

2302 _prefixes=self._prefixes,

2303 _suffixes=self._suffixes,

2304 )

2305

2306 def _get_reference_cte(self) -> CTE:

2307 """

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

2309 Updated CTEs should still refer to the original CTE.

2310 This function returns this reference identifier.

2311 """

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

2313

2314

2315class _CTEOpts(NamedTuple):

2316 nesting: bool

2317

2318

2319class _ColumnsPlusNames(NamedTuple):

2320 required_label_name: Optional[str]

2321 """

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

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

2324 """

2325

2326 proxy_key: Optional[str]

2327 """

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

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

2330 select.selected_columns collection

2331 """

2332

2333 fallback_label_name: Optional[str]

2334 """

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

2336 we have to render a label even though

2337 required_label_name was not given

2338 """

2339

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

2341 """

2342 the ColumnElement itself

2343 """

2344

2345 repeated: bool

2346 """

2347 True if this is a duplicate of a previous column

2348 in the list of columns

2349 """

2350

2351

2352class SelectsRows(ReturnsRows):

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

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

2355

2356 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2357

2358 def _generate_columns_plus_names(

2359 self,

2360 anon_for_dupe_key: bool,

2361 cols: Optional[_SelectIterable] = None,

2362 ) -> List[_ColumnsPlusNames]:

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

2364 the compiler, as well as tokens used to populate the .c. collection

2365 on a :class:`.FromClause`.

2366

2367 This is distinct from the _column_naming_convention generator that's

2368 intended for population of the Select.selected_columns collection,

2369 different rules. the collection returned here calls upon the

2370 _column_naming_convention as well.

2371

2372 """

2373

2374 if cols is None:

2375 cols = self._all_selected_columns

2376

2377 key_naming_convention = SelectState._column_naming_convention(

2378 self._label_style

2379 )

2380

2381 names = {}

2382

2383 result: List[_ColumnsPlusNames] = []

2384 result_append = result.append

2385

2386 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2387 label_style_none = self._label_style is LABEL_STYLE_NONE

2388

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

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

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

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

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

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

2395 dedupe_hash = 1

2396

2397 for c in cols:

2398 repeated = False

2399

2400 if not c._render_label_in_columns_clause:

2401 effective_name = required_label_name = fallback_label_name = (

2402 None

2403 )

2404 elif label_style_none:

2405 if TYPE_CHECKING:

2406 assert is_column_element(c)

2407

2408 effective_name = required_label_name = None

2409 fallback_label_name = c._non_anon_label or c._anon_name_label

2410 else:

2411 if TYPE_CHECKING:

2412 assert is_column_element(c)

2413

2414 if table_qualified:

2415 required_label_name = effective_name = (

2416 fallback_label_name

2417 ) = c._tq_label

2418 else:

2419 effective_name = fallback_label_name = c._non_anon_label

2420 required_label_name = None

2421

2422 if effective_name is None:

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

2424 # not need _expression_label but it isn't

2425 # giving us a clue when to use anon_label instead

2426 expr_label = c._expression_label

2427 if expr_label is None:

2428 repeated = c._anon_name_label in names

2429 names[c._anon_name_label] = c

2430 effective_name = required_label_name = None

2431

2432 if repeated:

2433 # here, "required_label_name" is sent as

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

2435 if table_qualified:

2436 fallback_label_name = (

2437 c._dedupe_anon_tq_label_idx(dedupe_hash)

2438 )

2439 dedupe_hash += 1

2440 else:

2441 fallback_label_name = c._dedupe_anon_label_idx(

2442 dedupe_hash

2443 )

2444 dedupe_hash += 1

2445 else:

2446 fallback_label_name = c._anon_name_label

2447 else:

2448 required_label_name = effective_name = (

2449 fallback_label_name

2450 ) = expr_label

2451

2452 if effective_name is not None:

2453 if TYPE_CHECKING:

2454 assert is_column_element(c)

2455

2456 if effective_name in names:

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

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

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

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

2461 # different column under the same name. apply

2462 # disambiguating label

2463 if table_qualified:

2464 required_label_name = fallback_label_name = (

2465 c._anon_tq_label

2466 )

2467 else:

2468 required_label_name = fallback_label_name = (

2469 c._anon_name_label

2470 )

2471

2472 if anon_for_dupe_key and required_label_name in names:

2473 # here, c._anon_tq_label is definitely unique to

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

2475 # this should always be true.

2476 # this is also an infrequent codepath because

2477 # you need two levels of duplication to be here

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

2479

2480 # the column under the disambiguating label is

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

2482 # subsequent occurrences of the column so that the

2483 # original stays non-ambiguous

2484 if table_qualified:

2485 required_label_name = fallback_label_name = (

2486 c._dedupe_anon_tq_label_idx(dedupe_hash)

2487 )

2488 dedupe_hash += 1

2489 else:

2490 required_label_name = fallback_label_name = (

2491 c._dedupe_anon_label_idx(dedupe_hash)

2492 )

2493 dedupe_hash += 1

2494 repeated = True

2495 else:

2496 names[required_label_name] = c

2497 elif anon_for_dupe_key:

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

2499 # label so that the original stays non-ambiguous

2500 if table_qualified:

2501 required_label_name = fallback_label_name = (

2502 c._dedupe_anon_tq_label_idx(dedupe_hash)

2503 )

2504 dedupe_hash += 1

2505 else:

2506 required_label_name = fallback_label_name = (

2507 c._dedupe_anon_label_idx(dedupe_hash)

2508 )

2509 dedupe_hash += 1

2510 repeated = True

2511 else:

2512 names[effective_name] = c

2513

2514 result_append(

2515 _ColumnsPlusNames(

2516 required_label_name,

2517 key_naming_convention(c),

2518 fallback_label_name,

2519 c,

2520 repeated,

2521 )

2522 )

2523

2524 return result

2525

2526

2527class HasCTE(roles.HasCTERole, SelectsRows):

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

2529

2530 _has_ctes_traverse_internals: _TraverseInternalsType = [

2531 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2532 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2533 ]

2534

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

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

2537

2538 name_cte_columns: bool = False

2539 """indicates if this HasCTE as contained within a CTE should compel the CTE

2540 to render the column names of this object in the WITH clause.

2541

2542 .. versionadded:: 2.0.42

2543

2544 """

2545

2546 @_generative

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

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

2549

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

2551 the parent statement such that they will each be unconditionally

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

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

2554

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

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

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

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

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

2560 statement.

2561

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

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

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

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

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

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

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

2569 larger statement.

2570

2571 E.g.::

2572

2573 from sqlalchemy import table, column, select

2574

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

2576

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

2578

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

2580

2581 Would render:

2582

2583 .. sourcecode:: sql

2584

2585 WITH anon_1 AS (

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

2587 )

2588 SELECT t.c1, t.c2

2589 FROM t

2590

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

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

2593 statement.

2594

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

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

2597

2598 from sqlalchemy import table, column

2599 from sqlalchemy.dialects.postgresql import insert

2600

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

2602

2603 delete_statement_cte = t.delete().where(t.c.c1 < 1).cte("deletions")

2604

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

2606 update_statement = insert_stmt.on_conflict_do_update(

2607 index_elements=[t.c.c1],

2608 set_={

2609 "c1": insert_stmt.excluded.c1,

2610 "c2": insert_stmt.excluded.c2,

2611 },

2612 ).add_cte(delete_statement_cte)

2613

2614 print(update_statement)

2615

2616 The above statement renders as:

2617

2618 .. sourcecode:: sql

2619

2620 WITH deletions AS (

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

2622 )

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

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

2625

2626 .. versionadded:: 1.4.21

2627

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

2629

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

2631

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

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

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

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

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

2637 this statement when this flag is given.

2638

2639 .. versionadded:: 2.0

2640

2641 .. seealso::

2642

2643 :paramref:`.HasCTE.cte.nesting`

2644

2645

2646 """ # noqa: E501

2647 opt = _CTEOpts(nest_here)

2648 for cte in ctes:

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

2650 self._independent_ctes += (cte,)

2651 self._independent_ctes_opts += (opt,)

2652 return self

2653

2654 def cte(

2655 self,

2656 name: Optional[str] = None,

2657 recursive: bool = False,

2658 nesting: bool = False,

2659 ) -> CTE:

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

2661 or Common Table Expression instance.

2662

2663 Common table expressions are a SQL standard whereby SELECT

2664 statements can draw upon secondary statements specified along

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

2666 Special semantics regarding UNION can also be employed to

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

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

2669

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

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

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

2673 CTE rows.

2674

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

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

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

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

2679

2680 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2682 method may be

2683 used to establish these.

2684

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

2686 In particular - MATERIALIZED and NOT MATERIALIZED.

2687

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

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

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

2691 compile time.

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

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

2694 conjunction with UNION ALL in order to derive rows

2695 from those already selected.

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

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

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

2699 :paramref:`.HasCTE.add_cte.nest_here`

2700 parameter may also be used to more carefully

2701 control the exact placement of a particular CTE.

2702

2703 .. versionadded:: 1.4.24

2704

2705 .. seealso::

2706

2707 :meth:`.HasCTE.add_cte`

2708

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

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

2711 as well as additional examples.

2712

2713 Example 1, non recursive::

2714

2715 from sqlalchemy import (

2716 Table,

2717 Column,

2718 String,

2719 Integer,

2720 MetaData,

2721 select,

2722 func,

2723 )

2724

2725 metadata = MetaData()

2726

2727 orders = Table(

2728 "orders",

2729 metadata,

2730 Column("region", String),

2731 Column("amount", Integer),

2732 Column("product", String),

2733 Column("quantity", Integer),

2734 )

2735

2736 regional_sales = (

2737 select(orders.c.region, func.sum(orders.c.amount).label("total_sales"))

2738 .group_by(orders.c.region)

2739 .cte("regional_sales")

2740 )

2741

2742

2743 top_regions = (

2744 select(regional_sales.c.region)

2745 .where(

2746 regional_sales.c.total_sales

2747 > select(func.sum(regional_sales.c.total_sales) / 10)

2748 )

2749 .cte("top_regions")

2750 )

2751

2752 statement = (

2753 select(

2754 orders.c.region,

2755 orders.c.product,

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

2757 func.sum(orders.c.amount).label("product_sales"),

2758 )

2759 .where(orders.c.region.in_(select(top_regions.c.region)))

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

2761 )

2762

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

2764

2765 Example 2, WITH RECURSIVE::

2766

2767 from sqlalchemy import (

2768 Table,

2769 Column,

2770 String,

2771 Integer,

2772 MetaData,

2773 select,

2774 func,

2775 )

2776

2777 metadata = MetaData()

2778

2779 parts = Table(

2780 "parts",

2781 metadata,

2782 Column("part", String),

2783 Column("sub_part", String),

2784 Column("quantity", Integer),

2785 )

2786

2787 included_parts = (

2788 select(parts.c.sub_part, parts.c.part, parts.c.quantity)

2789 .where(parts.c.part == "our part")

2790 .cte(recursive=True)

2791 )

2792

2793

2794 incl_alias = included_parts.alias()

2795 parts_alias = parts.alias()

2796 included_parts = included_parts.union_all(

2797 select(

2798 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2800 )

2801

2802 statement = select(

2803 included_parts.c.sub_part,

2804 func.sum(included_parts.c.quantity).label("total_quantity"),

2805 ).group_by(included_parts.c.sub_part)

2806

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

2808

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

2810

2811 from datetime import date

2812 from sqlalchemy import (

2813 MetaData,

2814 Table,

2815 Column,

2816 Integer,

2817 Date,

2818 select,

2819 literal,

2820 and_,

2821 exists,

2822 )

2823

2824 metadata = MetaData()

2825

2826 visitors = Table(

2827 "visitors",

2828 metadata,

2829 Column("product_id", Integer, primary_key=True),

2830 Column("date", Date, primary_key=True),

2831 Column("count", Integer),

2832 )

2833

2834 # add 5 visitors for the product_id == 1

2835 product_id = 1

2836 day = date.today()

2837 count = 5

2838

2839 update_cte = (

2840 visitors.update()

2841 .where(

2842 and_(visitors.c.product_id == product_id, visitors.c.date == day)

2843 )

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

2845 .returning(literal(1))

2846 .cte("update_cte")

2847 )

2848

2849 upsert = visitors.insert().from_select(

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

2851 select(literal(product_id), literal(day), literal(count)).where(

2852 ~exists(update_cte.select())

2853 ),

2854 )

2855

2856 connection.execute(upsert)

2857

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

2859

2860 value_a = select(literal("root").label("n")).cte("value_a")

2861

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

2863 value_a_nested = select(literal("nesting").label("n")).cte(

2864 "value_a", nesting=True

2865 )

2866

2867 # Nesting CTEs takes ascendency locally

2868 # over the CTEs at a higher level

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

2870

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

2872

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

2874 shown with inline parameters below as:

2875

2876 .. sourcecode:: sql

2877

2878 WITH

2879 value_a AS

2880 (SELECT 'root' AS n),

2881 value_b AS

2882 (WITH value_a AS

2883 (SELECT 'nesting' AS n)

2884 SELECT value_a.n AS n FROM value_a)

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

2886 FROM value_a, value_b

2887

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

2889 as follows (SQLAlchemy 2.0 and above)::

2890

2891 value_a = select(literal("root").label("n")).cte("value_a")

2892

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

2894 value_a_nested = select(literal("nesting").label("n")).cte("value_a")

2895

2896 # Nesting CTEs takes ascendency locally

2897 # over the CTEs at a higher level

2898 value_b = (

2899 select(value_a_nested.c.n)

2900 .add_cte(value_a_nested, nest_here=True)

2901 .cte("value_b")

2902 )

2903

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

2905

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

2907

2908 edge = Table(

2909 "edge",

2910 metadata,

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

2912 Column("left", Integer),

2913 Column("right", Integer),

2914 )

2915

2916 root_node = select(literal(1).label("node")).cte("nodes", recursive=True)

2917

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

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

2920 )

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

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

2923 )

2924

2925 subgraph_cte = root_node.union(left_edge, right_edge)

2926

2927 subgraph = select(subgraph_cte)

2928

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

2930

2931 .. sourcecode:: sql

2932

2933 WITH RECURSIVE nodes(node) AS (

2934 SELECT 1 AS node

2935 UNION

2936 SELECT edge."left" AS "left"

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

2938 UNION

2939 SELECT edge."right" AS "right"

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

2941 )

2942 SELECT nodes.node FROM nodes

2943

2944 .. seealso::

2945

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

2947 :meth:`_expression.HasCTE.cte`.

2948

2949 """ # noqa: E501

2950 return CTE._construct(

2951 self, name=name, recursive=recursive, nesting=nesting

2952 )

2953

2954

2955class Subquery(AliasedReturnsRows):

2956 """Represent a subquery of a SELECT.

2957

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

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

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

2961 :class:`_expression.SelectBase` subclass

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

2963 :class:`_expression.CompoundSelect`, and

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

2965 it represents the

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

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

2968

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

2970 :class:`_expression.Alias`

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

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

2973 :class:`_expression.Alias` always

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

2975 :class:`.Subquery`

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

2977

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

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

2980 statement.

2981

2982 """

2983

2984 __visit_name__ = "subquery"

2985

2986 _is_subquery = True

2987

2988 inherit_cache = True

2989

2990 element: SelectBase

2991

2992 @classmethod

2993 def _factory(

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

2995 ) -> Subquery:

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

2997

2998 return coercions.expect(

2999 roles.SelectStatementRole, selectable

3000 ).subquery(name=name)

3001

3002 @util.deprecated(

3003 "1.4",

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

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

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

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

3008 ":func:`_expression.select` "

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

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

3011 )

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

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

3014

3015

3016class FromGrouping(GroupedElement, FromClause):

3017 """Represent a grouping of a FROM clause"""

3018

3019 _traverse_internals: _TraverseInternalsType = [

3020 ("element", InternalTraversal.dp_clauseelement)

3021 ]

3022

3023 element: FromClause

3024

3025 def __init__(self, element: FromClause):

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

3027

3028 @util.ro_non_memoized_property

3029 def columns(

3030 self,

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

3032 return self.element.columns

3033

3034 @util.ro_non_memoized_property

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

3036 return self.element.columns

3037

3038 @property

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

3040 return self.element.primary_key

3041

3042 @property

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

3044 return self.element.foreign_keys

3045

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

3047 return self.element.is_derived_from(fromclause)

3048

3049 def alias(

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

3051 ) -> NamedFromGrouping:

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

3053

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

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

3056

3057 @util.ro_non_memoized_property

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

3059 return self.element._hide_froms

3060

3061 @util.ro_non_memoized_property

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

3063 return self.element._from_objects

3064

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

3066 return {"element": self.element}

3067

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

3069 self.element = state["element"]

3070

3071 if TYPE_CHECKING:

3072

3073 def self_group(

3074 self, against: Optional[OperatorType] = None

3075 ) -> Self: ...

3076

3077

3078class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3080

3081 .. versionadded:: 2.0

3082

3083 """

3084

3085 inherit_cache = True

3086

3087 if TYPE_CHECKING:

3088

3089 def self_group(

3090 self, against: Optional[OperatorType] = None

3091 ) -> Self: ...

3092

3093

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

3095 """Represents a minimal "table" construct.

3096

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

3098 collection of columns, which are typically produced

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

3100

3101 from sqlalchemy import table, column

3102

3103 user = table(

3104 "user",

3105 column("id"),

3106 column("name"),

3107 column("description"),

3108 )

3109

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

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

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

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

3114

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

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

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

3118 It's useful

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

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

3121 is not on hand.

3122

3123 """

3124

3125 __visit_name__ = "table"

3126

3127 _traverse_internals: _TraverseInternalsType = [

3128 (

3129 "columns",

3130 InternalTraversal.dp_fromclause_canonical_column_collection,

3131 ),

3132 ("name", InternalTraversal.dp_string),

3133 ("schema", InternalTraversal.dp_string),

3134 ]

3135

3136 _is_table = True

3137

3138 fullname: str

3139

3140 implicit_returning = False

3141 """:class:`_expression.TableClause`

3142 doesn't support having a primary key or column

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

3144

3145 @util.ro_memoized_property

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

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

3148 return None

3149

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

3151 super().__init__()

3152 self.name = name

3153 self._columns = DedupeColumnCollection()

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

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

3156 for c in columns:

3157 self.append_column(c)

3158

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

3160 if schema is not None:

3161 self.schema = schema

3162 if self.schema is not None:

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

3164 else:

3165 self.fullname = self.name

3166 if kw:

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

3168

3169 if TYPE_CHECKING:

3170

3171 @util.ro_non_memoized_property

3172 def columns(

3173 self,

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

3175

3176 @util.ro_non_memoized_property

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

3178

3179 def __str__(self) -> str:

3180 if self.schema is not None:

3181 return self.schema + "." + self.name

3182 else:

3183 return self.name

3184

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

3186 pass

3187

3188 @util.ro_memoized_property

3189 def description(self) -> str:

3190 return self.name

3191

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

3193 existing = c.table

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

3195 raise exc.ArgumentError(

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

3197 % (c.key, existing)

3198 )

3199

3200 self._columns.add(c)

3201 c.table = self

3202

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

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

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

3206 :class:`_expression.TableClause`.

3207

3208 E.g.::

3209

3210 table.insert().values(name="foo")

3211

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

3213

3214 """

3215

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

3217

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

3219 def update(self) -> Update:

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

3221 :class:`_expression.TableClause`.

3222

3223 E.g.::

3224

3225 table.update().where(table.c.id == 7).values(name="foo")

3226

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

3228

3229 """

3230 return util.preloaded.sql_dml.Update(

3231 self,

3232 )

3233

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

3235 def delete(self) -> Delete:

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

3237 :class:`_expression.TableClause`.

3238

3239 E.g.::

3240

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

3242

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

3244

3245 """

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

3247

3248 @util.ro_non_memoized_property

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

3250 return [self]

3251

3252

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

3254

3255

3256class ForUpdateArg(ClauseElement):

3257 _traverse_internals: _TraverseInternalsType = [

3258 ("of", InternalTraversal.dp_clauseelement_list),

3259 ("nowait", InternalTraversal.dp_boolean),

3260 ("read", InternalTraversal.dp_boolean),

3261 ("skip_locked", InternalTraversal.dp_boolean),

3262 ("key_share", InternalTraversal.dp_boolean),

3263 ]

3264

3265 of: Optional[Sequence[ClauseElement]]

3266 nowait: bool

3267 read: bool

3268 skip_locked: bool

3269

3270 @classmethod

3271 def _from_argument(

3272 cls, with_for_update: ForUpdateParameter

3273 ) -> Optional[ForUpdateArg]:

3274 if isinstance(with_for_update, ForUpdateArg):

3275 return with_for_update

3276 elif with_for_update in (None, False):

3277 return None

3278 elif with_for_update is True:

3279 return ForUpdateArg()

3280 else:

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

3282

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

3284 return (

3285 isinstance(other, ForUpdateArg)

3286 and other.nowait == self.nowait

3287 and other.read == self.read

3288 and other.skip_locked == self.skip_locked

3289 and other.key_share == self.key_share

3290 and other.of is self.of

3291 )

3292

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

3294 return not self.__eq__(other)

3295

3296 def __hash__(self) -> int:

3297 return id(self)

3298

3299 def __init__(

3300 self,

3301 *,

3302 nowait: bool = False,

3303 read: bool = False,

3304 of: Optional[_ForUpdateOfArgument] = None,

3305 skip_locked: bool = False,

3306 key_share: bool = False,

3307 ):

3308 """Represents arguments specified to

3309 :meth:`_expression.Select.for_update`.

3310

3311 """

3312

3313 self.nowait = nowait

3314 self.read = read

3315 self.skip_locked = skip_locked

3316 self.key_share = key_share

3317 if of is not None:

3318 self.of = [

3319 coercions.expect(roles.ColumnsClauseRole, elem)

3320 for elem in util.to_list(of)

3321 ]

3322 else:

3323 self.of = None

3324

3325

3326class Values(roles.InElementRole, HasCTE, Generative, LateralFromClause):

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

3328 in a statement.

3329

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

3331 :func:`_expression.values` function.

3332

3333 .. versionadded:: 1.4

3334

3335 """

3336

3337 __visit_name__ = "values"

3338

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

3340 _column_args: Tuple[NamedColumn[Any], ...]

3341

3342 _unnamed: bool

3343 _traverse_internals: _TraverseInternalsType = [

3344 ("_column_args", InternalTraversal.dp_clauseelement_list),

3345 ("_data", InternalTraversal.dp_dml_multi_values),

3346 ("name", InternalTraversal.dp_string),

3347 ("literal_binds", InternalTraversal.dp_boolean),

3348 ] + HasCTE._has_ctes_traverse_internals

3349

3350 name_cte_columns = True

3351

3352 def __init__(

3353 self,

3354 *columns: _OnlyColumnArgument[Any],

3355 name: Optional[str] = None,

3356 literal_binds: bool = False,

3357 ):

3358 super().__init__()

3359 self._column_args = tuple(

3360 coercions.expect(roles.LabeledColumnExprRole, col)

3361 for col in columns

3362 )

3363

3364 if name is None:

3365 self._unnamed = True

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

3367 else:

3368 self._unnamed = False

3369 self.name = name

3370 self.literal_binds = literal_binds

3371 self.named_with_column = not self._unnamed

3372

3373 @property

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

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

3376

3377 @util.ro_non_memoized_property

3378 def _all_selected_columns(self) -> _SelectIterable:

3379 return self._column_args

3380

3381 @_generative

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

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

3384 construct that is a copy of this

3385 one with the given name.

3386

3387 This method is a VALUES-specific specialization of the

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

3389

3390 .. seealso::

3391

3392 :ref:`tutorial_using_aliases`

3393

3394 :func:`_expression.alias`

3395

3396 """

3397 non_none_name: str

3398

3399 if name is None:

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

3401 else:

3402 non_none_name = name

3403

3404 self.name = non_none_name

3405 self.named_with_column = True

3406 self._unnamed = False

3407 return self

3408

3409 @_generative

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

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

3412 so that

3413 it renders as LATERAL.

3414

3415 .. seealso::

3416

3417 :func:`_expression.lateral`

3418

3419 """

3420 non_none_name: str

3421

3422 if name is None:

3423 non_none_name = self.name

3424 else:

3425 non_none_name = name

3426

3427 self._is_lateral = True

3428 self.name = non_none_name

3429 self._unnamed = False

3430 return self

3431

3432 @_generative

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

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

3435 adding the given data to the data list.

3436

3437 E.g.::

3438

3439 my_values = my_values.data([(1, "value 1"), (2, "value2")])

3440

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

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

3443 constructor.

3444

3445 """

3446

3447 self._data += (values,)

3448 return self

3449

3450 def scalar_values(self) -> ScalarValues:

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

3452 COLUMN element in a statement.

3453

3454 .. versionadded:: 2.0.0b4

3455

3456 """

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

3458

3459 def _populate_column_collection(

3460 self,

3461 columns: ColumnCollection[str, KeyedColumnElement[Any]],

3462 primary_key: ColumnSet,

3463 foreign_keys: Set[KeyedColumnElement[Any]],

3464 ) -> None:

3465 for c in self._column_args:

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

3467 _, c = c._make_proxy(

3468 self, primary_key=primary_key, foreign_keys=foreign_keys

3469 )

3470 else:

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

3472 # no memoizations of other FROM clauses.

3473 # see test_values.py -> test_auto_proxy_select_direct_col

3474 c._reset_memoizations()

3475 columns.add(c)

3476 c.table = self

3477

3478 @util.ro_non_memoized_property

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

3480 return [self]

3481

3482

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

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

3485 COLUMN element in a statement.

3486

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

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

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

3490 an ``IN`` or ``NOT IN`` condition.

3491

3492 .. versionadded:: 2.0.0b4

3493

3494 """

3495

3496 __visit_name__ = "scalar_values"

3497

3498 _traverse_internals: _TraverseInternalsType = [

3499 ("_column_args", InternalTraversal.dp_clauseelement_list),

3500 ("_data", InternalTraversal.dp_dml_multi_values),

3501 ("literal_binds", InternalTraversal.dp_boolean),

3502 ]

3503

3504 def __init__(

3505 self,

3506 columns: Sequence[NamedColumn[Any]],

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

3508 literal_binds: bool,

3509 ):

3510 super().__init__()

3511 self._column_args = columns

3512 self._data = data

3513 self.literal_binds = literal_binds

3514

3515 @property

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

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

3518

3519 def __clause_element__(self) -> ScalarValues:

3520 return self

3521

3522 if TYPE_CHECKING:

3523

3524 def self_group(

3525 self, against: Optional[OperatorType] = None

3526 ) -> Self: ...

3527

3528 def _ungroup(self) -> ColumnElement[Any]: ...

3529

3530

3531class SelectBase(

3532 roles.SelectStatementRole,

3533 roles.DMLSelectRole,

3534 roles.CompoundElementRole,

3535 roles.InElementRole,

3536 HasCTE,

3537 SupportsCloneAnnotations,

3538 Selectable,

3539):

3540 """Base class for SELECT statements.

3541

3542

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

3544 :class:`_expression.CompoundSelect` and

3545 :class:`_expression.TextualSelect`.

3546

3547

3548 """

3549

3550 _is_select_base = True

3551 is_select = True

3552

3553 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3554

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

3556 self._reset_memoizations()

3557

3558 @util.ro_non_memoized_property

3559 def selected_columns(

3560 self,

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

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

3563 representing the columns that

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

3565

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

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

3568 within this collection cannot be directly nested inside another SELECT

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

3570 necessary parenthesization required by SQL.

3571

3572 .. note::

3573

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

3575 include expressions established in the columns clause using the

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

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

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

3579 construct.

3580

3581 .. seealso::

3582

3583 :attr:`_sql.Select.selected_columns`

3584

3585 .. versionadded:: 1.4

3586

3587 """

3588 raise NotImplementedError()

3589

3590 def _generate_fromclause_column_proxies(

3591 self,

3592 subquery: FromClause,

3593 columns: ColumnCollection[str, KeyedColumnElement[Any]],

3594 primary_key: ColumnSet,

3595 foreign_keys: Set[KeyedColumnElement[Any]],

3596 *,

3597 proxy_compound_columns: Optional[

3598 Iterable[Sequence[ColumnElement[Any]]]

3599 ] = None,

3600 ) -> None:

3601 raise NotImplementedError()

3602

3603 @util.ro_non_memoized_property

3604 def _all_selected_columns(self) -> _SelectIterable:

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

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

3607 constructs.

3608

3609 .. versionadded:: 1.4.12

3610

3611 .. seealso::

3612

3613 :attr:`_sql.SelectBase.exported_columns`

3614

3615 """

3616 raise NotImplementedError()

3617

3618 @property

3619 def exported_columns(

3620 self,

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

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

3623 that represents the "exported"

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

3625 :class:`_sql.TextClause` constructs.

3626

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

3628 object are synonymous

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

3630

3631 .. versionadded:: 1.4

3632

3633 .. seealso::

3634

3635 :attr:`_expression.Select.exported_columns`

3636

3637 :attr:`_expression.Selectable.exported_columns`

3638

3639 :attr:`_expression.FromClause.exported_columns`

3640

3641

3642 """

3643 return self.selected_columns.as_readonly()

3644

3645 @property

3646 @util.deprecated(

3647 "1.4",

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

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

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

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

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

3653 "first in order to create "

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

3655 "columns that this SELECT object SELECTs "

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

3657 "attribute.",

3658 )

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

3660 return self._implicit_subquery.columns

3661

3662 @property

3663 def columns(

3664 self,

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

3666 return self.c

3667

3668 def get_label_style(self) -> SelectLabelStyle:

3669 """

3670 Retrieve the current label style.

3671

3672 Implemented by subclasses.

3673

3674 """

3675 raise NotImplementedError()

3676

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

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

3679

3680 Implemented by subclasses.

3681

3682 """

3683

3684 raise NotImplementedError()

3685

3686 @util.deprecated(

3687 "1.4",

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

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

3690 "creates a subquery that should be explicit. "

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

3692 "first in order to create "

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

3694 )

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

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

3697

3698 @HasMemoized.memoized_attribute

3699 def _implicit_subquery(self) -> Subquery:

3700 return self.subquery()

3701

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

3703 raise NotImplementedError()

3704

3705 @util.deprecated(

3706 "1.4",

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

3708 "method is deprecated and will be "

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

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

3711 )

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

3713 return self.scalar_subquery()

3714

3715 def exists(self) -> Exists:

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

3717 which can be used as a column expression.

3718

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

3720

3721 .. seealso::

3722

3723 :func:`_sql.exists`

3724

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

3726

3727 .. versionadded:: 1.4

3728

3729 """

3730 return Exists(self)

3731

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

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

3734 used as a column expression.

3735

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

3737

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

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

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

3741 an enclosing SELECT.

3742

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

3744 subquery that can be produced using the

3745 :meth:`_expression.SelectBase.subquery`

3746 method.

3747

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

3749 :meth:`_expression.SelectBase.scalar_subquery`.

3750

3751 .. seealso::

3752

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

3754

3755 """

3756 if self._label_style is not LABEL_STYLE_NONE:

3757 self = self.set_label_style(LABEL_STYLE_NONE)

3758

3759 return ScalarSelect(self)

3760

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

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

3763 subquery with a label.

3764

3765 .. seealso::

3766

3767 :meth:`_expression.SelectBase.scalar_subquery`.

3768

3769 """

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

3771

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

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

3774

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

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

3777

3778 .. seealso::

3779

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

3781

3782 """

3783 return Lateral._factory(self, name)

3784

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

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

3787

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

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

3790 SELECT statement.

3791

3792 Given a SELECT statement such as::

3793

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

3795

3796 The above statement might look like:

3797

3798 .. sourcecode:: sql

3799

3800 SELECT table.id, table.name FROM table

3801

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

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

3804 a named sub-element::

3805

3806 subq = stmt.subquery()

3807 new_stmt = select(subq)

3808

3809 The above renders as:

3810

3811 .. sourcecode:: sql

3812

3813 SELECT anon_1.id, anon_1.name

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

3815

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

3817 is equivalent to calling

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

3819 method on a FROM object; however,

3820 as a :class:`_expression.SelectBase`

3821 object is not directly FROM object,

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

3823 method provides clearer semantics.

3824

3825 .. versionadded:: 1.4

3826

3827 """

3828

3829 return Subquery._construct(

3830 self._ensure_disambiguated_names(), name=name

3831 )

3832

3833 def _ensure_disambiguated_names(self) -> Self:

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

3835 disambiguated in some way, if possible.

3836

3837 """

3838

3839 raise NotImplementedError()

3840

3841 def alias(

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

3843 ) -> Subquery:

3844 """Return a named subquery against this

3845 :class:`_expression.SelectBase`.

3846

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

3848 :class:`_expression.FromClause`),

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

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

3851 :class:`_expression.FromClause`.

3852

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

3854 method is now

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

3856

3857 """

3858 return self.subquery(name=name)

3859

3860

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

3862

3863

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

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

3866

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

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

3869 compound selects.

3870

3871 """

3872

3873 __visit_name__ = "select_statement_grouping"

3874 _traverse_internals: _TraverseInternalsType = [

3875 ("element", InternalTraversal.dp_clauseelement)

3876 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3877

3878 _is_select_container = True

3879

3880 element: _SB

3881

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

3883 self.element = cast(

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

3885 )

3886

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

3888 new_element = self.element._ensure_disambiguated_names()

3889 if new_element is not self.element:

3890 return SelectStatementGrouping(new_element)

3891 else:

3892 return self

3893

3894 def get_label_style(self) -> SelectLabelStyle:

3895 return self.element.get_label_style()

3896

3897 def set_label_style(

3898 self, label_style: SelectLabelStyle

3899 ) -> SelectStatementGrouping[_SB]:

3900 return SelectStatementGrouping(

3901 self.element.set_label_style(label_style)

3902 )

3903

3904 @property

3905 def select_statement(self) -> _SB:

3906 return self.element

3907

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

3909 return self

3910

3911 if TYPE_CHECKING:

3912

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

3914

3915 # def _generate_columns_plus_names(

3916 # self, anon_for_dupe_key: bool

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

3918 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3919

3920 def _generate_fromclause_column_proxies(

3921 self,

3922 subquery: FromClause,

3923 columns: ColumnCollection[str, KeyedColumnElement[Any]],

3924 primary_key: ColumnSet,

3925 foreign_keys: Set[KeyedColumnElement[Any]],

3926 *,

3927 proxy_compound_columns: Optional[

3928 Iterable[Sequence[ColumnElement[Any]]]

3929 ] = None,

3930 ) -> None:

3931 self.element._generate_fromclause_column_proxies(

3932 subquery,

3933 columns,

3934 proxy_compound_columns=proxy_compound_columns,

3935 primary_key=primary_key,

3936 foreign_keys=foreign_keys,

3937 )

3938

3939 @util.ro_non_memoized_property

3940 def _all_selected_columns(self) -> _SelectIterable:

3941 return self.element._all_selected_columns

3942

3943 @util.ro_non_memoized_property

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

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

3946 representing the columns that

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

3948 :class:`_sql.TextClause` constructs.

3949

3950 .. versionadded:: 1.4

3951

3952 .. seealso::

3953

3954 :attr:`_sql.Select.selected_columns`

3955

3956 """

3957 return self.element.selected_columns

3958

3959 @util.ro_non_memoized_property

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

3961 return self.element._from_objects

3962

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

3964 return self.element._scalar_type()

3965

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

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

3968 raise NotImplementedError

3969

3970

3971class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3973 added.

3974

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

3976 :class:`_expression.CompoundSelect`

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

3978 rendering can be controlled. Compare to

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

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

3981 and is also a SELECT construct,

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

3983 only wrapped as a subquery.

3984

3985 """

3986

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

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

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

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

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

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

3993 _for_update_arg: Optional[ForUpdateArg] = None

3994

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

3996 self._label_style = _label_style

3997

3998 @_generative

3999 def with_for_update(

4000 self,

4001 *,

4002 nowait: bool = False,

4003 read: bool = False,

4004 of: Optional[_ForUpdateOfArgument] = None,

4005 skip_locked: bool = False,

4006 key_share: bool = False,

4007 ) -> Self:

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

4009 :class:`_expression.GenerativeSelect`.

4010

4011 E.g.::

4012

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

4014

4015 On a database like PostgreSQL or Oracle Database, the above would

4016 render a statement like:

4017

4018 .. sourcecode:: sql

4019

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

4021

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

4023 would produce:

4024

4025 .. sourcecode:: sql

4026

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

4028

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

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

4031 provided which allow for common database-specific

4032 variants.

4033

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

4035 Database and PostgreSQL dialects.

4036

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

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

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

4040

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

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

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

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

4045 Database, some MySQL versions and possibly others. May render as a

4046 table or as a column depending on backend.

4047

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

4049 Oracle Database and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED``

4050 if ``read=True`` is also specified.

4051

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

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

4054 on the PostgreSQL dialect.

4055

4056 """

4057 self._for_update_arg = ForUpdateArg(

4058 nowait=nowait,

4059 read=read,

4060 of=of,

4061 skip_locked=skip_locked,

4062 key_share=key_share,

4063 )

4064 return self

4065

4066 def get_label_style(self) -> SelectLabelStyle:

4067 """

4068 Retrieve the current label style.

4069

4070 .. versionadded:: 1.4

4071

4072 """

4073 return self._label_style

4074

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

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

4077

4078 There are three "label styles" available,

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

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

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

4082 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4083

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

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

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

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

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

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

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

4091 that the impact of this labeling is minimal.

4092

4093 The rationale for disambiguation is mostly so that all column

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

4095 collection when a subquery is created.

4096

4097 .. versionadded:: 1.4 - the

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

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

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

4101

4102 .. seealso::

4103

4104 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4105

4106 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4107

4108 :data:`_sql.LABEL_STYLE_NONE`

4109

4110 :data:`_sql.LABEL_STYLE_DEFAULT`

4111

4112 """

4113 if self._label_style is not style:

4114 self = self._generate()

4115 self._label_style = style

4116 return self

4117

4118 @property

4119 def _group_by_clause(self) -> ClauseList:

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

4121 return ClauseList._construct_raw(

4122 operators.comma_op, self._group_by_clauses

4123 )

4124

4125 @property

4126 def _order_by_clause(self) -> ClauseList:

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

4128 return ClauseList._construct_raw(

4129 operators.comma_op, self._order_by_clauses

4130 )

4131

4132 def _offset_or_limit_clause(

4133 self,

4134 element: _LimitOffsetType,

4135 name: Optional[str] = None,

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

4137 ) -> ColumnElement[Any]:

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

4139

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

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

4142

4143 """

4144 return coercions.expect(

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

4146 )

4147

4148 @overload

4149 def _offset_or_limit_clause_asint(

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

4151 ) -> NoReturn: ...

4152

4153 @overload

4154 def _offset_or_limit_clause_asint(

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

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

4157

4158 def _offset_or_limit_clause_asint(

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

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

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

4162 integer.

4163

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

4165 parameter. Otherwise, a compilation error is raised.

4166

4167 """

4168 if clause is None:

4169 return None

4170 try:

4171 value = clause._limit_offset_value

4172 except AttributeError as err:

4173 raise exc.CompileError(

4174 "This SELECT structure does not use a simple "

4175 "integer value for %s" % attrname

4176 ) from err

4177 else:

4178 return util.asint(value)

4179

4180 @property

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

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

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

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

4185 isn't currently set to an integer.

4186

4187 """

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

4189

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

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

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

4193 """

4194 return isinstance(clause, _OffsetLimitParam)

4195

4196 @property

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

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

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

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

4201 offset isn't currently set to an integer.

4202

4203 """

4204 return self._offset_or_limit_clause_asint(

4205 self._offset_clause, "offset"

4206 )

4207

4208 @property

4209 def _has_row_limiting_clause(self) -> bool:

4210 return (

4211 self._limit_clause is not None

4212 or self._offset_clause is not None

4213 or self._fetch_clause is not None

4214 )

4215

4216 @_generative

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

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

4219 applied.

4220

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

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

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

4224 functionality.

4225

4226 .. note::

4227

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

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

4230

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

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

4233

4234 .. seealso::

4235

4236 :meth:`_sql.GenerativeSelect.fetch`

4237

4238 :meth:`_sql.GenerativeSelect.offset`

4239

4240 """

4241

4242 self._fetch_clause = self._fetch_clause_options = None

4243 self._limit_clause = self._offset_or_limit_clause(limit)

4244 return self

4245

4246 @_generative

4247 def fetch(

4248 self,

4249 count: _LimitOffsetType,

4250 with_ties: bool = False,

4251 percent: bool = False,

4252 **dialect_kw: Any,

4253 ) -> Self:

4254 r"""Return a new selectable with the given FETCH FIRST criterion

4255 applied.

4256

4257 This is a numeric value which usually renders as ``FETCH {FIRST | NEXT}

4258 [ count ] {ROW | ROWS} {ONLY | WITH TIES}`` expression in the resulting

4259 select. This functionality is is currently implemented for Oracle

4260 Database, PostgreSQL, MSSQL.

4261

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

4263

4264 .. note::

4265

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

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

4268

4269 .. versionadded:: 1.4

4270

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

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

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

4274 Pass ``None`` to reset it.

4275

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

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

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

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

4280

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

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

4283

4284 :param \**dialect_kw: Additional dialect-specific keyword arguments

4285 may be accepted by dialects.

4286

4287 .. versionadded:: 2.0.41

4288

4289 .. seealso::

4290

4291 :meth:`_sql.GenerativeSelect.limit`

4292

4293 :meth:`_sql.GenerativeSelect.offset`

4294

4295 """

4296 self._validate_dialect_kwargs(dialect_kw)

4297 self._limit_clause = None

4298 if count is None:

4299 self._fetch_clause = self._fetch_clause_options = None

4300 else:

4301 self._fetch_clause = self._offset_or_limit_clause(count)

4302 self._fetch_clause_options = {

4303 "with_ties": with_ties,

4304 "percent": percent,

4305 }

4306 return self

4307

4308 @_generative

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

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

4311 applied.

4312

4313

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

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

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

4317 functionality.

4318

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

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

4321

4322 .. seealso::

4323

4324 :meth:`_sql.GenerativeSelect.limit`

4325

4326 :meth:`_sql.GenerativeSelect.fetch`

4327

4328 """

4329

4330 self._offset_clause = self._offset_or_limit_clause(offset)

4331 return self

4332

4333 @_generative

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

4335 def slice(

4336 self,

4337 start: int,

4338 stop: int,

4339 ) -> Self:

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

4341

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

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

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

4345 query.

4346

4347 For example, ::

4348

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

4350

4351 renders as

4352

4353 .. sourcecode:: sql

4354

4355 SELECT users.id AS users_id,

4356 users.name AS users_name

4357 FROM users ORDER BY users.id

4358 LIMIT ? OFFSET ?

4359 (2, 1)

4360

4361 .. note::

4362

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

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

4365

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

4367 method generalized from the ORM.

4368

4369 .. seealso::

4370

4371 :meth:`_sql.GenerativeSelect.limit`

4372

4373 :meth:`_sql.GenerativeSelect.offset`

4374

4375 :meth:`_sql.GenerativeSelect.fetch`

4376

4377 """

4378 sql_util = util.preloaded.sql_util

4379 self._fetch_clause = self._fetch_clause_options = None

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

4381 self._limit_clause, self._offset_clause, start, stop

4382 )

4383 return self

4384

4385 @_generative

4386 def order_by(

4387 self,

4388 __first: Union[

4389 Literal[None, _NoArg.NO_ARG],

4390 _ColumnExpressionOrStrLabelArgument[Any],

4391 ] = _NoArg.NO_ARG,

4392 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4393 ) -> Self:

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

4395 criteria applied.

4396

4397 e.g.::

4398

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

4400

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

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

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

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

4405

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

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

4408

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

4410 constructs which will be used to generate an ORDER BY clause.

4411

4412 Alternatively, an individual entry may also be the string name of a

4413 label located elsewhere in the columns clause of the statement which

4414 will be matched and rendered in a backend-specific way based on

4415 context; see :ref:`tutorial_order_by_label` for background on string

4416 label matching in ORDER BY and GROUP BY expressions.

4417

4418 .. seealso::

4419

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

4421

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

4423

4424 """

4425

4426 if not clauses and __first is None:

4427 self._order_by_clauses = ()

4428 elif __first is not _NoArg.NO_ARG:

4429 self._order_by_clauses += tuple(

4430 coercions.expect(

4431 roles.OrderByRole, clause, apply_propagate_attrs=self

4432 )

4433 for clause in (__first,) + clauses

4434 )

4435 return self

4436

4437 @_generative

4438 def group_by(

4439 self,

4440 __first: Union[

4441 Literal[None, _NoArg.NO_ARG],

4442 _ColumnExpressionOrStrLabelArgument[Any],

4443 ] = _NoArg.NO_ARG,

4444 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4445 ) -> Self:

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

4447 criterion applied.

4448

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

4450

4451 e.g.::

4452

4453 stmt = select(table.c.name, func.max(table.c.stat)).group_by(table.c.name)

4454

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

4456 constructs which will be used to generate an GROUP BY clause.

4457

4458 Alternatively, an individual entry may also be the string name of a

4459 label located elsewhere in the columns clause of the statement which

4460 will be matched and rendered in a backend-specific way based on

4461 context; see :ref:`tutorial_order_by_label` for background on string

4462 label matching in ORDER BY and GROUP BY expressions.

4463

4464 .. seealso::

4465

4466 :ref:`tutorial_group_by_w_aggregates` - in the

4467 :ref:`unified_tutorial`

4468

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

4470

4471 """ # noqa: E501

4472

4473 if not clauses and __first is None:

4474 self._group_by_clauses = ()

4475 elif __first is not _NoArg.NO_ARG:

4476 self._group_by_clauses += tuple(

4477 coercions.expect(

4478 roles.GroupByRole, clause, apply_propagate_attrs=self

4479 )

4480 for clause in (__first,) + clauses

4481 )

4482 return self

4483

4484

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

4486class CompoundSelectState(CompileState):

4487 @util.memoized_property

4488 def _label_resolve_dict(

4489 self,

4490 ) -> Tuple[

4491 Dict[str, ColumnElement[Any]],

4492 Dict[str, ColumnElement[Any]],

4493 Dict[str, ColumnElement[Any]],

4494 ]:

4495 # TODO: this is hacky and slow

4496 hacky_subquery = self.statement.subquery()

4497 hacky_subquery.named_with_column = False

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

4499 return d, d, d

4500

4501

4502class _CompoundSelectKeyword(Enum):

4503 UNION = "UNION"

4504 UNION_ALL = "UNION ALL"

4505 EXCEPT = "EXCEPT"

4506 EXCEPT_ALL = "EXCEPT ALL"

4507 INTERSECT = "INTERSECT"

4508 INTERSECT_ALL = "INTERSECT ALL"

4509

4510

4511class CompoundSelect(HasCompileState, GenerativeSelect, TypedReturnsRows[_TP]):

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

4513 SELECT-based set operations.

4514

4515

4516 .. seealso::

4517

4518 :func:`_expression.union`

4519

4520 :func:`_expression.union_all`

4521

4522 :func:`_expression.intersect`

4523

4524 :func:`_expression.intersect_all`

4525

4526 :func:`_expression.except`

4527

4528 :func:`_expression.except_all`

4529

4530 """

4531

4532 __visit_name__ = "compound_select"

4533

4534 _traverse_internals: _TraverseInternalsType = (

4535 [

4536 ("selects", InternalTraversal.dp_clauseelement_list),

4537 ("_limit_clause", InternalTraversal.dp_clauseelement),

4538 ("_offset_clause", InternalTraversal.dp_clauseelement),

4539 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4540 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4541 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4542 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4543 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4544 ("keyword", InternalTraversal.dp_string),

4545 ]

4546 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4547 + HasCTE._has_ctes_traverse_internals

4548 + DialectKWArgs._dialect_kwargs_traverse_internals

4549 + Executable._executable_traverse_internals

4550 )

4551

4552 selects: List[SelectBase]

4553

4554 _is_from_container = True

4555 _auto_correlate = False

4556

4557 def __init__(

4558 self,

4559 keyword: _CompoundSelectKeyword,

4560 *selects: _SelectStatementForCompoundArgument[_TP],

4561 ):

4562 self.keyword = keyword

4563 self.selects = [

4564 coercions.expect(

4565 roles.CompoundElementRole, s, apply_propagate_attrs=self

4566 ).self_group(against=self)

4567 for s in selects

4568 ]

4569

4570 GenerativeSelect.__init__(self)

4571

4572 @classmethod

4573 def _create_union(

4574 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4575 ) -> CompoundSelect[_TP]:

4576 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4577

4578 @classmethod

4579 def _create_union_all(

4580 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4581 ) -> CompoundSelect[_TP]:

4582 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4583

4584 @classmethod

4585 def _create_except(

4586 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4587 ) -> CompoundSelect[_TP]:

4588 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4589

4590 @classmethod

4591 def _create_except_all(

4592 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4593 ) -> CompoundSelect[_TP]:

4594 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4595

4596 @classmethod

4597 def _create_intersect(

4598 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4599 ) -> CompoundSelect[_TP]:

4600 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4601

4602 @classmethod

4603 def _create_intersect_all(

4604 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4605 ) -> CompoundSelect[_TP]:

4606 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4607

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

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

4610

4611 def self_group(

4612 self, against: Optional[OperatorType] = None

4613 ) -> GroupedElement:

4614 return SelectStatementGrouping(self)

4615

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

4617 for s in self.selects:

4618 if s.is_derived_from(fromclause):

4619 return True

4620 return False

4621

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

4623 if self._label_style is not style:

4624 self = self._generate()

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

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

4627

4628 return self

4629

4630 def _ensure_disambiguated_names(self) -> Self:

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

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

4633 self = self._generate()

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

4635

4636 return self

4637

4638 def _generate_fromclause_column_proxies(

4639 self,

4640 subquery: FromClause,

4641 columns: ColumnCollection[str, KeyedColumnElement[Any]],

4642 primary_key: ColumnSet,

4643 foreign_keys: Set[KeyedColumnElement[Any]],

4644 *,

4645 proxy_compound_columns: Optional[

4646 Iterable[Sequence[ColumnElement[Any]]]

4647 ] = None,

4648 ) -> None:

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

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

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

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

4653 # generate below.

4654 select_0 = self.selects[0]

4655

4656 if self._label_style is not LABEL_STYLE_DEFAULT:

4657 select_0 = select_0.set_label_style(self._label_style)

4658

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

4660 # derived columns place a 'weight' annotation corresponding

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

4662 # that the corresponding_column() operation can resolve

4663 # conflicts

4664 extra_col_iterator = zip(

4665 *[

4666 [

4667 c._annotate(dd)

4668 for c in stmt._all_selected_columns

4669 if is_column_element(c)

4670 ]

4671 for dd, stmt in [

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

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

4674 ]

4675 ]

4676 )

4677

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

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

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

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

4682 # compound

4683 select_0._generate_fromclause_column_proxies(

4684 subquery,

4685 columns,

4686 proxy_compound_columns=extra_col_iterator,

4687 primary_key=primary_key,

4688 foreign_keys=foreign_keys,

4689 )

4690

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

4692 super()._refresh_for_new_column(column)

4693 for select in self.selects:

4694 select._refresh_for_new_column(column)

4695

4696 @util.ro_non_memoized_property

4697 def _all_selected_columns(self) -> _SelectIterable:

4698 return self.selects[0]._all_selected_columns

4699

4700 @util.ro_non_memoized_property

4701 def selected_columns(

4702 self,

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

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

4705 representing the columns that

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

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

4708

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

4710 :attr:`_expression.CompoundSelect.selected_columns`

4711 attribute returns the selected

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

4713 statements within the set operation.

4714

4715 .. seealso::

4716

4717 :attr:`_sql.Select.selected_columns`

4718

4719 .. versionadded:: 1.4

4720

4721 """

4722 return self.selects[0].selected_columns

4723

4724

4725# backwards compat

4726for elem in _CompoundSelectKeyword:

4727 setattr(CompoundSelect, elem.name, elem)

4728

4729

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

4731class SelectState(util.MemoizedSlots, CompileState):

4732 __slots__ = (

4733 "from_clauses",

4734 "froms",

4735 "columns_plus_names",

4736 "_label_resolve_dict",

4737 )

4738

4739 if TYPE_CHECKING:

4740 default_select_compile_options: CacheableOptions

4741 else:

4742

4743 class default_select_compile_options(CacheableOptions):

4744 _cache_key_traversal = []

4745

4746 if TYPE_CHECKING:

4747

4748 @classmethod

4749 def get_plugin_class(

4750 cls, statement: Executable

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

4752

4753 def __init__(

4754 self,

4755 statement: Select[Any],

4756 compiler: SQLCompiler,

4757 **kw: Any,

4758 ):

4759 self.statement = statement

4760 self.from_clauses = statement._from_obj

4761

4762 for memoized_entities in statement._memoized_select_entities:

4763 self._setup_joins(

4764 memoized_entities._setup_joins, memoized_entities._raw_columns

4765 )

4766

4767 if statement._setup_joins:

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

4769

4770 self.froms = self._get_froms(statement)

4771

4772 self.columns_plus_names = statement._generate_columns_plus_names(True)

4773

4774 @classmethod

4775 def _plugin_not_implemented(cls) -> NoReturn:

4776 raise NotImplementedError(

4777 "The default SELECT construct without plugins does not "

4778 "implement this method."

4779 )

4780

4781 @classmethod

4782 def get_column_descriptions(

4783 cls, statement: Select[Any]

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

4785 return [

4786 {

4787 "name": name,

4788 "type": element.type,

4789 "expr": element,

4790 }

4791 for _, name, _, element, _ in (

4792 statement._generate_columns_plus_names(False)

4793 )

4794 ]

4795

4796 @classmethod

4797 def from_statement(

4798 cls, statement: Select[Any], from_statement: roles.ReturnsRowsRole

4799 ) -> ExecutableReturnsRows:

4800 cls._plugin_not_implemented()

4801

4802 @classmethod

4803 def get_columns_clause_froms(

4804 cls, statement: Select[Any]

4805 ) -> List[FromClause]:

4806 return cls._normalize_froms(

4807 itertools.chain.from_iterable(

4808 element._from_objects for element in statement._raw_columns

4809 )

4810 )

4811

4812 @classmethod

4813 def _column_naming_convention(

4814 cls, label_style: SelectLabelStyle

4815 ) -> _LabelConventionCallable:

4816 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4817

4818 dedupe = label_style is not LABEL_STYLE_NONE

4819

4820 pa = prefix_anon_map()

4821 names = set()

4822

4823 def go(

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

4825 col_name: Optional[str] = None,

4826 ) -> Optional[str]:

4827 if is_text_clause(c):

4828 return None

4829 elif TYPE_CHECKING:

4830 assert is_column_element(c)

4831

4832 if not dedupe:

4833 name = c._proxy_key

4834 if name is None:

4835 name = "_no_label"

4836 return name

4837

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

4839

4840 if name is None:

4841 name = "_no_label"

4842 if name in names:

4843 return c._anon_label(name) % pa

4844 else:

4845 names.add(name)

4846 return name

4847

4848 elif name in names:

4849 return (

4850 c._anon_tq_key_label % pa

4851 if table_qualified

4852 else c._anon_key_label % pa

4853 )

4854 else:

4855 names.add(name)

4856 return name

4857

4858 return go

4859

4860 def _get_froms(self, statement: Select[Any]) -> List[FromClause]:

4861 ambiguous_table_name_map: _AmbiguousTableNameMap

4862 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4863

4864 return self._normalize_froms(

4865 itertools.chain(

4866 self.from_clauses,

4867 itertools.chain.from_iterable(

4868 [

4869 element._from_objects

4870 for element in statement._raw_columns

4871 ]

4872 ),

4873 itertools.chain.from_iterable(

4874 [

4875 element._from_objects

4876 for element in statement._where_criteria

4877 ]

4878 ),

4879 ),

4880 check_statement=statement,

4881 ambiguous_table_name_map=ambiguous_table_name_map,

4882 )

4883

4884 @classmethod

4885 def _normalize_froms(

4886 cls,

4887 iterable_of_froms: Iterable[FromClause],

4888 check_statement: Optional[Select[Any]] = None,

4889 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4890 ) -> List[FromClause]:

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

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

4893

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

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

4896 etc.

4897

4898 """

4899 seen: Set[FromClause] = set()

4900 froms: List[FromClause] = []

4901

4902 for item in iterable_of_froms:

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

4904 raise exc.InvalidRequestError(

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

4906 )

4907

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

4909 froms.append(item)

4910 seen.update(item._cloned_set)

4911

4912 if froms:

4913 toremove = set(

4914 itertools.chain.from_iterable(

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

4916 )

4917 )

4918 if toremove:

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

4920 # using a list to maintain ordering

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

4922

4923 if ambiguous_table_name_map is not None:

4924 ambiguous_table_name_map.update(

4925 (

4926 fr.name,

4927 _anonymous_label.safe_construct(

4928 hash(fr.name), fr.name

4929 ),

4930 )

4931 for item in froms

4932 for fr in item._from_objects

4933 if is_table(fr)

4934 and fr.schema

4935 and fr.name not in ambiguous_table_name_map

4936 )

4937

4938 return froms

4939

4940 def _get_display_froms(

4941 self,

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

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

4944 ) -> List[FromClause]:

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

4946

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

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

4949 may want to leave those absent if it is automatically

4950 correlating.

4951

4952 """

4953

4954 froms = self.froms

4955

4956 if self.statement._correlate:

4957 to_correlate = self.statement._correlate

4958 if to_correlate:

4959 to_remove = _cloned_intersection(

4960 _cloned_intersection(

4961 froms, explicit_correlate_froms or ()

4962 ),

4963 to_correlate,

4964 )

4965 froms = [f for f in froms if f not in to_remove]

4966

4967 if self.statement._correlate_except is not None:

4968 to_remove = _cloned_difference(

4969 _cloned_intersection(froms, explicit_correlate_froms or ()),

4970 self.statement._correlate_except,

4971 )

4972 froms = [f for f in froms if f not in to_remove]

4973

4974 if (

4975 self.statement._auto_correlate

4976 and implicit_correlate_froms

4977 and len(froms) > 1

4978 ):

4979 to_remove = _cloned_intersection(froms, implicit_correlate_froms)

4980 froms = [f for f in froms if f not in to_remove]

4981

4982 if not len(froms):

4983 raise exc.InvalidRequestError(

4984 "Select statement '%r"

4985 "' returned no FROM clauses "

4986 "due to auto-correlation; "

4987 "specify correlate(<tables>) "

4988 "to control correlation "

4989 "manually." % self.statement

4990 )

4991

4992 return froms

4993

4994 def _memoized_attr__label_resolve_dict(

4995 self,

4996 ) -> Tuple[

4997 Dict[str, ColumnElement[Any]],

4998 Dict[str, ColumnElement[Any]],

4999 Dict[str, ColumnElement[Any]],

5000 ]:

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

5002 c._tq_label or c.key: c

5003 for c in self.statement._all_selected_columns

5004 if c._allow_label_resolve

5005 }

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

5007 c.key: c # type: ignore

5008 for c in _select_iterables(self.froms)

5009 if c._allow_label_resolve

5010 }

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

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

5013 with_cols.setdefault(key, value)

5014

5015 return with_cols, only_froms, only_cols

5016

5017 @classmethod

5018 def determine_last_joined_entity(

5019 cls, stmt: Select[Any]

5020 ) -> Optional[_JoinTargetElement]:

5021 if stmt._setup_joins:

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

5023 else:

5024 return None

5025

5026 @classmethod

5027 def all_selected_columns(cls, statement: Select[Any]) -> _SelectIterable:

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

5029

5030 def _setup_joins(

5031 self,

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

5033 raw_columns: List[_ColumnsClauseElement],

5034 ) -> None:

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

5036 if TYPE_CHECKING:

5037 if onclause is not None:

5038 assert isinstance(onclause, ColumnElement)

5039

5040 isouter = flags["isouter"]

5041 full = flags["full"]

5042

5043 if left is None:

5044 (

5045 left,

5046 replace_from_obj_index,

5047 ) = self._join_determine_implicit_left_side(

5048 raw_columns, left, right, onclause

5049 )

5050 else:

5051 replace_from_obj_index = self._join_place_explicit_left_side(

5052 left

5053 )

5054

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

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

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

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

5059 # used instead.

5060 if TYPE_CHECKING:

5061 assert isinstance(right, FromClause)

5062 if onclause is not None:

5063 assert isinstance(onclause, ColumnElement)

5064

5065 if replace_from_obj_index is not None:

5066 # splice into an existing element in the

5067 # self._from_obj list

5068 left_clause = self.from_clauses[replace_from_obj_index]

5069

5070 self.from_clauses = (

5071 self.from_clauses[:replace_from_obj_index]

5072 + (

5073 Join(

5074 left_clause,

5075 right,

5076 onclause,

5077 isouter=isouter,

5078 full=full,

5079 ),

5080 )

5081 + self.from_clauses[replace_from_obj_index + 1 :]

5082 )

5083 else:

5084 assert left is not None

5085 self.from_clauses = self.from_clauses + (

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

5087 )

5088

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

5090 def _join_determine_implicit_left_side(

5091 self,

5092 raw_columns: List[_ColumnsClauseElement],

5093 left: Optional[FromClause],

5094 right: _JoinTargetElement,

5095 onclause: Optional[ColumnElement[Any]],

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

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

5098 determine if an existing FROM or entity in this query

5099 can serve as the left hand side.

5100

5101 """

5102

5103 sql_util = util.preloaded.sql_util

5104

5105 replace_from_obj_index: Optional[int] = None

5106

5107 from_clauses = self.from_clauses

5108

5109 if from_clauses:

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

5111 from_clauses, right, onclause

5112 )

5113

5114 if len(indexes) == 1:

5115 replace_from_obj_index = indexes[0]

5116 left = from_clauses[replace_from_obj_index]

5117 else:

5118 potential = {}

5119 statement = self.statement

5120

5121 for from_clause in itertools.chain(

5122 itertools.chain.from_iterable(

5123 [element._from_objects for element in raw_columns]

5124 ),

5125 itertools.chain.from_iterable(

5126 [

5127 element._from_objects

5128 for element in statement._where_criteria

5129 ]

5130 ),

5131 ):

5132 potential[from_clause] = ()

5133

5134 all_clauses = list(potential.keys())

5135 indexes = sql_util.find_left_clause_to_join_from(

5136 all_clauses, right, onclause

5137 )

5138

5139 if len(indexes) == 1:

5140 left = all_clauses[indexes[0]]

5141

5142 if len(indexes) > 1:

5143 raise exc.InvalidRequestError(

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

5145 "from, there are multiple FROMS which can "

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

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

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

5149 "help resolve the ambiguity."

5150 )

5151 elif not indexes:

5152 raise exc.InvalidRequestError(

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

5154 "Please use the .select_from() "

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

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

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

5158 )

5159 return left, replace_from_obj_index

5160

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

5162 def _join_place_explicit_left_side(

5163 self, left: FromClause

5164 ) -> Optional[int]:

5165 replace_from_obj_index: Optional[int] = None

5166

5167 sql_util = util.preloaded.sql_util

5168

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

5170

5171 if from_clauses:

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

5173 self.from_clauses, left

5174 )

5175 else:

5176 indexes = []

5177

5178 if len(indexes) > 1:

5179 raise exc.InvalidRequestError(

5180 "Can't identify which entity in which to assign the "

5181 "left side of this join. Please use a more specific "

5182 "ON clause."

5183 )

5184

5185 # have an index, means the left side is already present in

5186 # an existing FROM in the self._from_obj tuple

5187 if indexes:

5188 replace_from_obj_index = indexes[0]

5189

5190 # no index, means we need to add a new element to the

5191 # self._from_obj tuple

5192

5193 return replace_from_obj_index

5194

5195

5196class _SelectFromElements:

5197 __slots__ = ()

5198

5199 _raw_columns: List[_ColumnsClauseElement]

5200 _where_criteria: Tuple[ColumnElement[Any], ...]

5201 _from_obj: Tuple[FromClause, ...]

5202

5203 def _iterate_from_elements(self) -> Iterator[FromClause]:

5204 # note this does not include elements

5205 # in _setup_joins

5206

5207 seen = set()

5208 for element in self._raw_columns:

5209 for fr in element._from_objects:

5210 if fr in seen:

5211 continue

5212 seen.add(fr)

5213 yield fr

5214 for element in self._where_criteria:

5215 for fr in element._from_objects:

5216 if fr in seen:

5217 continue

5218 seen.add(fr)

5219 yield fr

5220 for element in self._from_obj:

5221 if element in seen:

5222 continue

5223 seen.add(element)

5224 yield element

5225

5226

5227class _MemoizedSelectEntities(

5228 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5229):

5230 """represents partial state from a Select object, for the case

5231 where Select.columns() has redefined the set of columns/entities the

5232 statement will be SELECTing from. This object represents

5233 the entities from the SELECT before that transformation was applied,

5234 so that transformations that were made in terms of the SELECT at that

5235 time, such as join() as well as options(), can access the correct context.

5236

5237 In previous SQLAlchemy versions, this wasn't needed because these

5238 constructs calculated everything up front, like when you called join()

5239 or options(), it did everything to figure out how that would translate

5240 into specific SQL constructs that would be ready to send directly to the

5241 SQL compiler when needed. But as of

5242 1.4, all of that stuff is done in the compilation phase, during the

5243 "compile state" portion of the process, so that the work can all be

5244 cached. So it needs to be able to resolve joins/options2 based on what

5245 the list of entities was when those methods were called.

5246

5247

5248 """

5249

5250 __visit_name__ = "memoized_select_entities"

5251

5252 _traverse_internals: _TraverseInternalsType = [

5253 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5254 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5255 ("_with_options", InternalTraversal.dp_executable_options),

5256 ]

5257

5258 _is_clone_of: Optional[ClauseElement]

5259 _raw_columns: List[_ColumnsClauseElement]

5260 _setup_joins: Tuple[_SetupJoinsElement, ...]

5261 _with_options: Tuple[ExecutableOption, ...]

5262

5263 _annotations = util.EMPTY_DICT

5264

5265 def _clone(self, **kw: Any) -> Self:

5266 c = self.__class__.__new__(self.__class__)

5267 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5268

5269 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5270 return c

5271

5272 @classmethod

5273 def _generate_for_statement(cls, select_stmt: Select[Any]) -> None:

5274 if select_stmt._setup_joins or select_stmt._with_options:

5275 self = _MemoizedSelectEntities()

5276 self._raw_columns = select_stmt._raw_columns

5277 self._setup_joins = select_stmt._setup_joins

5278 self._with_options = select_stmt._with_options

5279

5280 select_stmt._memoized_select_entities += (self,)

5281 select_stmt._raw_columns = []

5282 select_stmt._setup_joins = select_stmt._with_options = ()

5283

5284

5285class Select(

5286 HasPrefixes,

5287 HasSuffixes,

5288 HasHints,

5289 HasCompileState,

5290 _SelectFromElements,

5291 GenerativeSelect,

5292 TypedReturnsRows[_TP],

5293):

5294 """Represents a ``SELECT`` statement.

5295

5296 The :class:`_sql.Select` object is normally constructed using the

5297 :func:`_sql.select` function. See that function for details.

5298

5299 .. seealso::

5300

5301 :func:`_sql.select`

5302

5303 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5304

5305 """

5306

5307 __visit_name__ = "select"

5308

5309 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5310 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5311

5312 _raw_columns: List[_ColumnsClauseElement]

5313

5314 _distinct: bool = False

5315 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5316 _correlate: Tuple[FromClause, ...] = ()

5317 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5318 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5319 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5320 _from_obj: Tuple[FromClause, ...] = ()

5321 _auto_correlate = True

5322 _is_select_statement = True

5323 _compile_options: CacheableOptions = (

5324 SelectState.default_select_compile_options

5325 )

5326

5327 _traverse_internals: _TraverseInternalsType = (

5328 [

5329 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5330 (

5331 "_memoized_select_entities",

5332 InternalTraversal.dp_memoized_select_entities,

5333 ),

5334 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5335 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5336 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5337 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5338 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5339 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5340 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5341 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5342 ("_limit_clause", InternalTraversal.dp_clauseelement),

5343 ("_offset_clause", InternalTraversal.dp_clauseelement),

5344 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5345 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5346 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5347 ("_distinct", InternalTraversal.dp_boolean),

5348 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5349 ("_label_style", InternalTraversal.dp_plain_obj),

5350 ]

5351 + HasCTE._has_ctes_traverse_internals

5352 + HasPrefixes._has_prefixes_traverse_internals

5353 + HasSuffixes._has_suffixes_traverse_internals

5354 + HasHints._has_hints_traverse_internals

5355 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5356 + Executable._executable_traverse_internals

5357 + DialectKWArgs._dialect_kwargs_traverse_internals

5358 )

5359

5360 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5361 ("_compile_options", InternalTraversal.dp_has_cache_key)

5362 ]

5363

5364 _compile_state_factory: Type[SelectState]

5365

5366 @classmethod

5367 def _create_raw_select(cls, **kw: Any) -> Select[Any]:

5368 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5369

5370 Used internally to build up :class:`.Select` constructs with

5371 pre-established state.

5372

5373 """

5374

5375 stmt = Select.__new__(Select)

5376 stmt.__dict__.update(kw)

5377 return stmt

5378

5379 def __init__(

5380 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5381 ):

5382 r"""Construct a new :class:`_expression.Select`.

5383

5384 The public constructor for :class:`_expression.Select` is the

5385 :func:`_sql.select` function.

5386

5387 """

5388 self._raw_columns = [

5389 coercions.expect(

5390 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5391 )

5392 for ent in entities

5393 ]

5394 GenerativeSelect.__init__(self)

5395

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

5397 if not self._raw_columns:

5398 return NULLTYPE

5399 elem = self._raw_columns[0]

5400 cols = list(elem._select_iterable)

5401 return cols[0].type

5402

5403 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5404 """A synonym for the :meth:`_sql.Select.where` method."""

5405

5406 return self.where(*criteria)

5407

5408 def _filter_by_zero(

5409 self,

5410 ) -> Union[

5411 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5412 ]:

5413 if self._setup_joins:

5414 meth = SelectState.get_plugin_class(

5415 self

5416 ).determine_last_joined_entity

5417 _last_joined_entity = meth(self)

5418 if _last_joined_entity is not None:

5419 return _last_joined_entity

5420

5421 if self._from_obj:

5422 return self._from_obj[0]

5423

5424 return self._raw_columns[0]

5425

5426 if TYPE_CHECKING:

5427

5428 @overload

5429 def scalar_subquery(

5430 self: Select[Tuple[_MAYBE_ENTITY]],

5431 ) -> ScalarSelect[Any]: ...

5432

5433 @overload

5434 def scalar_subquery(

5435 self: Select[Tuple[_NOT_ENTITY]],

5436 ) -> ScalarSelect[_NOT_ENTITY]: ...

5437

5438 @overload

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

5440

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

5442

5443 def filter_by(self, **kwargs: Any) -> Self:

5444 r"""apply the given filtering criterion as a WHERE clause

5445 to this select.

5446

5447 """

5448 from_entity = self._filter_by_zero()

5449

5450 clauses = [

5451 _entity_namespace_key(from_entity, key) == value

5452 for key, value in kwargs.items()

5453 ]

5454 return self.filter(*clauses)

5455

5456 @property

5457 def column_descriptions(self) -> Any:

5458 """Return a :term:`plugin-enabled` 'column descriptions' structure

5459 referring to the columns which are SELECTed by this statement.

5460

5461 This attribute is generally useful when using the ORM, as an

5462 extended structure which includes information about mapped

5463 entities is returned. The section :ref:`queryguide_inspection`

5464 contains more background.

5465

5466 For a Core-only statement, the structure returned by this accessor

5467 is derived from the same objects that are returned by the

5468 :attr:`.Select.selected_columns` accessor, formatted as a list of

5469 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5470 which indicate the column expressions to be selected::

5471

5472 >>> stmt = select(user_table)

5473 >>> stmt.column_descriptions

5474 [

5475 {

5476 'name': 'id',

5477 'type': Integer(),

5478 'expr': Column('id', Integer(), ...)},

5479 {

5480 'name': 'name',

5481 'type': String(length=30),

5482 'expr': Column('name', String(length=30), ...)}

5483 ]

5484

5485 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5486 attribute returns a structure for a Core-only set of entities,

5487 not just ORM-only entities.

5488

5489 .. seealso::

5490

5491 :attr:`.UpdateBase.entity_description` - entity information for

5492 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5493

5494 :ref:`queryguide_inspection` - ORM background

5495

5496 """

5497 meth = SelectState.get_plugin_class(self).get_column_descriptions

5498 return meth(self)

5499

5500 def from_statement(

5501 self, statement: roles.ReturnsRowsRole

5502 ) -> ExecutableReturnsRows:

5503 """Apply the columns which this :class:`.Select` would select

5504 onto another statement.

5505

5506 This operation is :term:`plugin-specific` and will raise a not

5507 supported exception if this :class:`_sql.Select` does not select from

5508 plugin-enabled entities.

5509

5510

5511 The statement is typically either a :func:`_expression.text` or

5512 :func:`_expression.select` construct, and should return the set of

5513 columns appropriate to the entities represented by this

5514 :class:`.Select`.

5515

5516 .. seealso::

5517

5518 :ref:`orm_queryguide_selecting_text` - usage examples in the

5519 ORM Querying Guide

5520

5521 """

5522 meth = SelectState.get_plugin_class(self).from_statement

5523 return meth(self, statement)

5524

5525 @_generative

5526 def join(

5527 self,

5528 target: _JoinTargetArgument,

5529 onclause: Optional[_OnClauseArgument] = None,

5530 *,

5531 isouter: bool = False,

5532 full: bool = False,

5533 ) -> Self:

5534 r"""Create a SQL JOIN against this :class:`_expression.Select`

5535 object's criterion

5536 and apply generatively, returning the newly resulting

5537 :class:`_expression.Select`.

5538

5539 E.g.::

5540

5541 stmt = select(user_table).join(

5542 address_table, user_table.c.id == address_table.c.user_id

5543 )

5544

5545 The above statement generates SQL similar to:

5546

5547 .. sourcecode:: sql

5548

5549 SELECT user.id, user.name

5550 FROM user

5551 JOIN address ON user.id = address.user_id

5552

5553 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5554 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5555 source that is within the FROM clause of the existing SELECT,

5556 and a given target :class:`_sql.FromClause`, and then adds

5557 this :class:`_sql.Join` to the FROM clause of the newly generated

5558 SELECT statement. This is completely reworked from the behavior

5559 in 1.3, which would instead create a subquery of the entire

5560 :class:`_expression.Select` and then join that subquery to the

5561 target.

5562

5563 This is a **backwards incompatible change** as the previous behavior

5564 was mostly useless, producing an unnamed subquery rejected by

5565 most databases in any case. The new behavior is modeled after

5566 that of the very successful :meth:`_orm.Query.join` method in the

5567 ORM, in order to support the functionality of :class:`_orm.Query`

5568 being available by using a :class:`_sql.Select` object with an

5569 :class:`_orm.Session`.

5570

5571 See the notes for this change at :ref:`change_select_join`.

5572

5573

5574 :param target: target table to join towards

5575

5576 :param onclause: ON clause of the join. If omitted, an ON clause

5577 is generated automatically based on the :class:`_schema.ForeignKey`

5578 linkages between the two tables, if one can be unambiguously

5579 determined, otherwise an error is raised.

5580

5581 :param isouter: if True, generate LEFT OUTER join. Same as

5582 :meth:`_expression.Select.outerjoin`.

5583

5584 :param full: if True, generate FULL OUTER join.

5585

5586 .. seealso::

5587

5588 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5589

5590 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5591

5592 :meth:`_expression.Select.join_from`

5593

5594 :meth:`_expression.Select.outerjoin`

5595

5596 """ # noqa: E501

5597 join_target = coercions.expect(

5598 roles.JoinTargetRole, target, apply_propagate_attrs=self

5599 )

5600 if onclause is not None:

5601 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5602 else:

5603 onclause_element = None

5604

5605 self._setup_joins += (

5606 (

5607 join_target,

5608 onclause_element,

5609 None,

5610 {"isouter": isouter, "full": full},

5611 ),

5612 )

5613 return self

5614

5615 def outerjoin_from(

5616 self,

5617 from_: _FromClauseArgument,

5618 target: _JoinTargetArgument,

5619 onclause: Optional[_OnClauseArgument] = None,

5620 *,

5621 full: bool = False,

5622 ) -> Self:

5623 r"""Create a SQL LEFT OUTER JOIN against this

5624 :class:`_expression.Select` object's criterion and apply generatively,

5625 returning the newly resulting :class:`_expression.Select`.

5626

5627 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5628

5629 """

5630 return self.join_from(

5631 from_, target, onclause=onclause, isouter=True, full=full

5632 )

5633

5634 @_generative

5635 def join_from(

5636 self,

5637 from_: _FromClauseArgument,

5638 target: _JoinTargetArgument,

5639 onclause: Optional[_OnClauseArgument] = None,

5640 *,

5641 isouter: bool = False,

5642 full: bool = False,

5643 ) -> Self:

5644 r"""Create a SQL JOIN against this :class:`_expression.Select`

5645 object's criterion

5646 and apply generatively, returning the newly resulting

5647 :class:`_expression.Select`.

5648

5649 E.g.::

5650

5651 stmt = select(user_table, address_table).join_from(

5652 user_table, address_table, user_table.c.id == address_table.c.user_id

5653 )

5654

5655 The above statement generates SQL similar to:

5656

5657 .. sourcecode:: sql

5658

5659 SELECT user.id, user.name, address.id, address.email, address.user_id

5660 FROM user JOIN address ON user.id = address.user_id

5661

5662 .. versionadded:: 1.4

5663

5664 :param from\_: the left side of the join, will be rendered in the

5665 FROM clause and is roughly equivalent to using the

5666 :meth:`.Select.select_from` method.

5667

5668 :param target: target table to join towards

5669

5670 :param onclause: ON clause of the join.

5671

5672 :param isouter: if True, generate LEFT OUTER join. Same as

5673 :meth:`_expression.Select.outerjoin`.

5674

5675 :param full: if True, generate FULL OUTER join.

5676

5677 .. seealso::

5678

5679 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5680

5681 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5682

5683 :meth:`_expression.Select.join`

5684

5685 """ # noqa: E501

5686

5687 # note the order of parsing from vs. target is important here, as we

5688 # are also deriving the source of the plugin (i.e. the subject mapper

5689 # in an ORM query) which should favor the "from_" over the "target"

5690

5691 from_ = coercions.expect(

5692 roles.FromClauseRole, from_, apply_propagate_attrs=self

5693 )

5694 join_target = coercions.expect(

5695 roles.JoinTargetRole, target, apply_propagate_attrs=self

5696 )

5697 if onclause is not None:

5698 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5699 else:

5700 onclause_element = None

5701

5702 self._setup_joins += (

5703 (

5704 join_target,

5705 onclause_element,

5706 from_,

5707 {"isouter": isouter, "full": full},

5708 ),

5709 )

5710 return self

5711

5712 def outerjoin(

5713 self,

5714 target: _JoinTargetArgument,

5715 onclause: Optional[_OnClauseArgument] = None,

5716 *,

5717 full: bool = False,

5718 ) -> Self:

5719 """Create a left outer join.

5720

5721 Parameters are the same as that of :meth:`_expression.Select.join`.

5722

5723 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5724 creates a :class:`_sql.Join` object between a

5725 :class:`_sql.FromClause` source that is within the FROM clause of

5726 the existing SELECT, and a given target :class:`_sql.FromClause`,

5727 and then adds this :class:`_sql.Join` to the FROM clause of the

5728 newly generated SELECT statement. This is completely reworked

5729 from the behavior in 1.3, which would instead create a subquery of

5730 the entire

5731 :class:`_expression.Select` and then join that subquery to the

5732 target.

5733

5734 This is a **backwards incompatible change** as the previous behavior

5735 was mostly useless, producing an unnamed subquery rejected by

5736 most databases in any case. The new behavior is modeled after

5737 that of the very successful :meth:`_orm.Query.join` method in the

5738 ORM, in order to support the functionality of :class:`_orm.Query`

5739 being available by using a :class:`_sql.Select` object with an

5740 :class:`_orm.Session`.

5741

5742 See the notes for this change at :ref:`change_select_join`.

5743

5744 .. seealso::

5745

5746 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5747

5748 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5749

5750 :meth:`_expression.Select.join`

5751

5752 """

5753 return self.join(target, onclause=onclause, isouter=True, full=full)

5754

5755 def get_final_froms(self) -> Sequence[FromClause]:

5756 """Compute the final displayed list of :class:`_expression.FromClause`

5757 elements.

5758

5759 This method will run through the full computation required to

5760 determine what FROM elements will be displayed in the resulting

5761 SELECT statement, including shadowing individual tables with

5762 JOIN objects, as well as full computation for ORM use cases including

5763 eager loading clauses.

5764

5765 For ORM use, this accessor returns the **post compilation**

5766 list of FROM objects; this collection will include elements such as

5767 eagerly loaded tables and joins. The objects will **not** be

5768 ORM enabled and not work as a replacement for the

5769 :meth:`_sql.Select.select_froms` collection; additionally, the

5770 method is not well performing for an ORM enabled statement as it

5771 will incur the full ORM construction process.

5772

5773 To retrieve the FROM list that's implied by the "columns" collection

5774 passed to the :class:`_sql.Select` originally, use the

5775 :attr:`_sql.Select.columns_clause_froms` accessor.

5776

5777 To select from an alternative set of columns while maintaining the

5778 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5779 pass the

5780 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5781 parameter.

5782

5783 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5784 method replaces the previous :attr:`_sql.Select.froms` accessor,

5785 which is deprecated.

5786

5787 .. seealso::

5788

5789 :attr:`_sql.Select.columns_clause_froms`

5790

5791 """

5792 compiler = self._default_compiler()

5793

5794 return self._compile_state_factory(self, compiler)._get_display_froms()

5795

5796 @property

5797 @util.deprecated(

5798 "1.4.23",

5799 "The :attr:`_expression.Select.froms` attribute is moved to "

5800 "the :meth:`_expression.Select.get_final_froms` method.",

5801 )

5802 def froms(self) -> Sequence[FromClause]:

5803 """Return the displayed list of :class:`_expression.FromClause`

5804 elements.

5805

5806

5807 """

5808 return self.get_final_froms()

5809

5810 @property

5811 def columns_clause_froms(self) -> List[FromClause]:

5812 """Return the set of :class:`_expression.FromClause` objects implied

5813 by the columns clause of this SELECT statement.

5814

5815 .. versionadded:: 1.4.23

5816

5817 .. seealso::

5818

5819 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5820 statement into account

5821

5822 :meth:`_sql.Select.with_only_columns` - makes use of this

5823 collection to set up a new FROM list

5824

5825 """

5826

5827 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5828 self

5829 )

5830

5831 @property

5832 def inner_columns(self) -> _SelectIterable:

5833 """An iterator of all :class:`_expression.ColumnElement`

5834 expressions which would

5835 be rendered into the columns clause of the resulting SELECT statement.

5836

5837 This method is legacy as of 1.4 and is superseded by the

5838 :attr:`_expression.Select.exported_columns` collection.

5839

5840 """

5841

5842 return iter(self._all_selected_columns)

5843

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

5845 if fromclause is not None and self in fromclause._cloned_set:

5846 return True

5847

5848 for f in self._iterate_from_elements():

5849 if f.is_derived_from(fromclause):

5850 return True

5851 return False

5852

5853 def _copy_internals(

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

5855 ) -> None:

5856 # Select() object has been cloned and probably adapted by the

5857 # given clone function. Apply the cloning function to internal

5858 # objects

5859

5860 # 1. keep a dictionary of the froms we've cloned, and what

5861 # they've become. This allows us to ensure the same cloned from

5862 # is used when other items such as columns are "cloned"

5863

5864 all_the_froms = set(

5865 itertools.chain(

5866 _from_objects(*self._raw_columns),

5867 _from_objects(*self._where_criteria),

5868 _from_objects(*[elem[0] for elem in self._setup_joins]),

5869 )

5870 )

5871

5872 # do a clone for the froms we've gathered. what is important here

5873 # is if any of the things we are selecting from, like tables,

5874 # were converted into Join objects. if so, these need to be

5875 # added to _from_obj explicitly, because otherwise they won't be

5876 # part of the new state, as they don't associate themselves with

5877 # their columns.

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

5879

5880 # 2. copy FROM collections, adding in joins that we've created.

5881 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5882 add_froms = (

5883 {f for f in new_froms.values() if isinstance(f, Join)}

5884 .difference(all_the_froms)

5885 .difference(existing_from_obj)

5886 )

5887

5888 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5889

5890 # 3. clone everything else, making sure we use columns

5891 # corresponding to the froms we just made.

5892 def replace(

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

5894 **kw: Any,

5895 ) -> Optional[KeyedColumnElement[Any]]:

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

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

5898 return newelem

5899 return None

5900

5901 kw["replace"] = replace

5902

5903 # copy everything else. for table-ish things like correlate,

5904 # correlate_except, setup_joins, these clone normally. For

5905 # column-expression oriented things like raw_columns, where_criteria,

5906 # order by, we get this from the new froms.

5907 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5908

5909 self._reset_memoizations()

5910

5911 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5912 return itertools.chain(

5913 super().get_children(

5914 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5915 **kw,

5916 ),

5917 self._iterate_from_elements(),

5918 )

5919

5920 @_generative

5921 def add_columns(

5922 self, *entities: _ColumnsClauseArgument[Any]

5923 ) -> Select[Any]:

5924 r"""Return a new :func:`_expression.select` construct with

5925 the given entities appended to its columns clause.

5926

5927 E.g.::

5928

5929 my_select = my_select.add_columns(table.c.new_column)

5930

5931 The original expressions in the columns clause remain in place.

5932 To replace the original expressions with new ones, see the method

5933 :meth:`_expression.Select.with_only_columns`.

5934

5935 :param \*entities: column, table, or other entity expressions to be

5936 added to the columns clause

5937

5938 .. seealso::

5939

5940 :meth:`_expression.Select.with_only_columns` - replaces existing

5941 expressions rather than appending.

5942

5943 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5944 example

5945

5946 """

5947 self._reset_memoizations()

5948

5949 self._raw_columns = self._raw_columns + [

5950 coercions.expect(

5951 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5952 )

5953 for column in entities

5954 ]

5955 return self

5956

5957 def _set_entities(

5958 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5959 ) -> None:

5960 self._raw_columns = [

5961 coercions.expect(

5962 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5963 )

5964 for ent in util.to_list(entities)

5965 ]

5966

5967 @util.deprecated(

5968 "1.4",

5969 "The :meth:`_expression.Select.column` method is deprecated and will "

5970 "be removed in a future release. Please use "

5971 ":meth:`_expression.Select.add_columns`",

5972 )

5973 def column(self, column: _ColumnsClauseArgument[Any]) -> Select[Any]:

5974 """Return a new :func:`_expression.select` construct with

5975 the given column expression added to its columns clause.

5976

5977 E.g.::

5978

5979 my_select = my_select.column(table.c.new_column)

5980

5981 See the documentation for

5982 :meth:`_expression.Select.with_only_columns`

5983 for guidelines on adding /replacing the columns of a

5984 :class:`_expression.Select` object.

5985

5986 """

5987 return self.add_columns(column)

5988

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

5990 def reduce_columns(self, only_synonyms: bool = True) -> Select[Any]:

5991 """Return a new :func:`_expression.select` construct with redundantly

5992 named, equivalently-valued columns removed from the columns clause.

5993

5994 "Redundant" here means two columns where one refers to the

5995 other either based on foreign key, or via a simple equality

5996 comparison in the WHERE clause of the statement. The primary purpose

5997 of this method is to automatically construct a select statement

5998 with all uniquely-named columns, without the need to use

5999 table-qualified labels as

6000 :meth:`_expression.Select.set_label_style`

6001 does.

6002

6003 When columns are omitted based on foreign key, the referred-to

6004 column is the one that's kept. When columns are omitted based on

6005 WHERE equivalence, the first column in the columns clause is the

6006 one that's kept.

6007

6008 :param only_synonyms: when True, limit the removal of columns

6009 to those which have the same name as the equivalent. Otherwise,

6010 all columns that are equivalent to another are removed.

6011

6012 """

6013 woc: Select[Any]

6014 woc = self.with_only_columns(

6015 *util.preloaded.sql_util.reduce_columns(

6016 self._all_selected_columns,

6017 only_synonyms=only_synonyms,

6018 *(self._where_criteria + self._from_obj),

6019 )

6020 )

6021 return woc

6022

6023 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6024

6025 # code within this block is **programmatically,

6026 # statically generated** by tools/generate_tuple_map_overloads.py

6027

6028 @overload

6029 def with_only_columns(

6030 self, __ent0: _TCCA[_T0], *, maintain_column_froms: bool = ...

6031 ) -> Select[Tuple[_T0]]: ...

6032

6033 @overload

6034 def with_only_columns(

6035 self,

6036 __ent0: _TCCA[_T0],

6037 __ent1: _TCCA[_T1],

6038 *,

6039 maintain_column_froms: bool = ...,

6040 ) -> Select[Tuple[_T0, _T1]]: ...

6041

6042 @overload

6043 def with_only_columns(

6044 self,

6045 __ent0: _TCCA[_T0],

6046 __ent1: _TCCA[_T1],

6047 __ent2: _TCCA[_T2],

6048 *,

6049 maintain_column_froms: bool = ...,

6050 ) -> Select[Tuple[_T0, _T1, _T2]]: ...

6051

6052 @overload

6053 def with_only_columns(

6054 self,

6055 __ent0: _TCCA[_T0],

6056 __ent1: _TCCA[_T1],

6057 __ent2: _TCCA[_T2],

6058 __ent3: _TCCA[_T3],

6059 *,

6060 maintain_column_froms: bool = ...,

6061 ) -> Select[Tuple[_T0, _T1, _T2, _T3]]: ...

6062

6063 @overload

6064 def with_only_columns(

6065 self,

6066 __ent0: _TCCA[_T0],

6067 __ent1: _TCCA[_T1],

6068 __ent2: _TCCA[_T2],

6069 __ent3: _TCCA[_T3],

6070 __ent4: _TCCA[_T4],

6071 *,

6072 maintain_column_froms: bool = ...,

6073 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...

6074

6075 @overload

6076 def with_only_columns(

6077 self,

6078 __ent0: _TCCA[_T0],

6079 __ent1: _TCCA[_T1],

6080 __ent2: _TCCA[_T2],

6081 __ent3: _TCCA[_T3],

6082 __ent4: _TCCA[_T4],

6083 __ent5: _TCCA[_T5],

6084 *,

6085 maintain_column_froms: bool = ...,

6086 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...

6087

6088 @overload

6089 def with_only_columns(

6090 self,

6091 __ent0: _TCCA[_T0],

6092 __ent1: _TCCA[_T1],

6093 __ent2: _TCCA[_T2],

6094 __ent3: _TCCA[_T3],

6095 __ent4: _TCCA[_T4],

6096 __ent5: _TCCA[_T5],

6097 __ent6: _TCCA[_T6],

6098 *,

6099 maintain_column_froms: bool = ...,

6100 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...

6101

6102 @overload

6103 def with_only_columns(

6104 self,

6105 __ent0: _TCCA[_T0],

6106 __ent1: _TCCA[_T1],

6107 __ent2: _TCCA[_T2],

6108 __ent3: _TCCA[_T3],

6109 __ent4: _TCCA[_T4],

6110 __ent5: _TCCA[_T5],

6111 __ent6: _TCCA[_T6],

6112 __ent7: _TCCA[_T7],

6113 *,

6114 maintain_column_froms: bool = ...,

6115 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...

6116

6117 # END OVERLOADED FUNCTIONS self.with_only_columns

6118

6119 @overload

6120 def with_only_columns(

6121 self,

6122 *entities: _ColumnsClauseArgument[Any],

6123 maintain_column_froms: bool = False,

6124 **__kw: Any,

6125 ) -> Select[Any]: ...

6126

6127 @_generative

6128 def with_only_columns(

6129 self,

6130 *entities: _ColumnsClauseArgument[Any],

6131 maintain_column_froms: bool = False,

6132 **__kw: Any,

6133 ) -> Select[Any]:

6134 r"""Return a new :func:`_expression.select` construct with its columns

6135 clause replaced with the given entities.

6136

6137 By default, this method is exactly equivalent to as if the original

6138 :func:`_expression.select` had been called with the given entities.

6139 E.g. a statement::

6140

6141 s = select(table1.c.a, table1.c.b)

6142 s = s.with_only_columns(table1.c.b)

6143

6144 should be exactly equivalent to::

6145

6146 s = select(table1.c.b)

6147

6148 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6149 will also dynamically alter the FROM clause of the

6150 statement if it is not explicitly stated.

6151 To maintain the existing set of FROMs including those implied by the

6152 current columns clause, add the

6153 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6154 parameter::

6155

6156 s = select(table1.c.a, table2.c.b)

6157 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6158

6159 The above parameter performs a transfer of the effective FROMs

6160 in the columns collection to the :meth:`_sql.Select.select_from`

6161 method, as though the following were invoked::

6162

6163 s = select(table1.c.a, table2.c.b)

6164 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6165

6166 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6167 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6168 collection and performs an operation equivalent to the following::

6169

6170 s = select(table1.c.a, table2.c.b)

6171 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6172

6173 :param \*entities: column expressions to be used.

6174

6175 :param maintain_column_froms: boolean parameter that will ensure the

6176 FROM list implied from the current columns clause will be transferred

6177 to the :meth:`_sql.Select.select_from` method first.

6178

6179 .. versionadded:: 1.4.23

6180

6181 """ # noqa: E501

6182

6183 if __kw:

6184 raise _no_kw()

6185

6186 # memoizations should be cleared here as of

6187 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6188 # is the case for now.

6189 self._assert_no_memoizations()

6190

6191 if maintain_column_froms:

6192 self.select_from.non_generative( # type: ignore

6193 self, *self.columns_clause_froms

6194 )

6195

6196 # then memoize the FROMs etc.

6197 _MemoizedSelectEntities._generate_for_statement(self)

6198

6199 self._raw_columns = [

6200 coercions.expect(roles.ColumnsClauseRole, c)

6201 for c in coercions._expression_collection_was_a_list(

6202 "entities", "Select.with_only_columns", entities

6203 )

6204 ]

6205 return self

6206

6207 @property

6208 def whereclause(self) -> Optional[ColumnElement[Any]]:

6209 """Return the completed WHERE clause for this

6210 :class:`_expression.Select` statement.

6211

6212 This assembles the current collection of WHERE criteria

6213 into a single :class:`_expression.BooleanClauseList` construct.

6214

6215

6216 .. versionadded:: 1.4

6217

6218 """

6219

6220 return BooleanClauseList._construct_for_whereclause(

6221 self._where_criteria

6222 )

6223

6224 _whereclause = whereclause

6225

6226 @_generative

6227 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6228 """Return a new :func:`_expression.select` construct with

6229 the given expression added to

6230 its WHERE clause, joined to the existing clause via AND, if any.

6231

6232 """

6233

6234 assert isinstance(self._where_criteria, tuple)

6235

6236 for criterion in whereclause:

6237 where_criteria: ColumnElement[Any] = coercions.expect(

6238 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6239 )

6240 self._where_criteria += (where_criteria,)

6241 return self

6242

6243 @_generative

6244 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6245 """Return a new :func:`_expression.select` construct with

6246 the given expression added to

6247 its HAVING clause, joined to the existing clause via AND, if any.

6248

6249 """

6250

6251 for criterion in having:

6252 having_criteria = coercions.expect(

6253 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6254 )

6255 self._having_criteria += (having_criteria,)

6256 return self

6257

6258 @_generative

6259 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6260 r"""Return a new :func:`_expression.select` construct which

6261 will apply DISTINCT to the SELECT statement overall.

6262

6263 E.g.::

6264

6265 from sqlalchemy import select

6266

6267 stmt = select(users_table.c.id, users_table.c.name).distinct()

6268

6269 The above would produce an statement resembling:

6270

6271 .. sourcecode:: sql

6272

6273 SELECT DISTINCT user.id, user.name FROM user

6274

6275 The method also accepts an ``*expr`` parameter which produces the

6276 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6277 parameter on other backends which don't support this syntax will

6278 raise an error.

6279

6280 :param \*expr: optional column expressions. When present,

6281 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6282 construct. A deprecation warning and/or :class:`_exc.CompileError`

6283 will be raised on other backends.

6284

6285 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6286 and will raise :class:`_exc.CompileError` in a future version.

6287

6288 """

6289 if expr:

6290 self._distinct = True

6291 self._distinct_on = self._distinct_on + tuple(

6292 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6293 for e in expr

6294 )

6295 else:

6296 self._distinct = True

6297 return self

6298

6299 @_generative

6300 def select_from(self, *froms: _FromClauseArgument) -> Self:

6301 r"""Return a new :func:`_expression.select` construct with the

6302 given FROM expression(s)

6303 merged into its list of FROM objects.

6304

6305 E.g.::

6306

6307 table1 = table("t1", column("a"))

6308 table2 = table("t2", column("b"))

6309 s = select(table1.c.a).select_from(

6310 table1.join(table2, table1.c.a == table2.c.b)

6311 )

6312

6313 The "from" list is a unique set on the identity of each element,

6314 so adding an already present :class:`_schema.Table`

6315 or other selectable

6316 will have no effect. Passing a :class:`_expression.Join` that refers

6317 to an already present :class:`_schema.Table`

6318 or other selectable will have

6319 the effect of concealing the presence of that selectable as

6320 an individual element in the rendered FROM list, instead

6321 rendering it into a JOIN clause.

6322

6323 While the typical purpose of :meth:`_expression.Select.select_from`

6324 is to

6325 replace the default, derived FROM clause with a join, it can

6326 also be called with individual table elements, multiple times

6327 if desired, in the case that the FROM clause cannot be fully

6328 derived from the columns clause::

6329

6330 select(func.count("*")).select_from(table1)

6331

6332 """

6333

6334 self._from_obj += tuple(

6335 coercions.expect(

6336 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6337 )

6338 for fromclause in froms

6339 )

6340 return self

6341

6342 @_generative

6343 def correlate(

6344 self,

6345 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6346 ) -> Self:

6347 r"""Return a new :class:`_expression.Select`

6348 which will correlate the given FROM

6349 clauses to that of an enclosing :class:`_expression.Select`.

6350

6351 Calling this method turns off the :class:`_expression.Select` object's

6352 default behavior of "auto-correlation". Normally, FROM elements

6353 which appear in a :class:`_expression.Select`

6354 that encloses this one via

6355 its :term:`WHERE clause`, ORDER BY, HAVING or

6356 :term:`columns clause` will be omitted from this

6357 :class:`_expression.Select`

6358 object's :term:`FROM clause`.

6359 Setting an explicit correlation collection using the

6360 :meth:`_expression.Select.correlate`

6361 method provides a fixed list of FROM objects

6362 that can potentially take place in this process.

6363

6364 When :meth:`_expression.Select.correlate`

6365 is used to apply specific FROM clauses

6366 for correlation, the FROM elements become candidates for

6367 correlation regardless of how deeply nested this

6368 :class:`_expression.Select`

6369 object is, relative to an enclosing :class:`_expression.Select`

6370 which refers to

6371 the same FROM object. This is in contrast to the behavior of

6372 "auto-correlation" which only correlates to an immediate enclosing

6373 :class:`_expression.Select`.

6374 Multi-level correlation ensures that the link

6375 between enclosed and enclosing :class:`_expression.Select`

6376 is always via

6377 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6378 correlation to take place.

6379

6380 If ``None`` is passed, the :class:`_expression.Select`

6381 object will correlate

6382 none of its FROM entries, and all will render unconditionally

6383 in the local FROM clause.

6384

6385 :param \*fromclauses: one or more :class:`.FromClause` or other

6386 FROM-compatible construct such as an ORM mapped entity to become part

6387 of the correlate collection; alternatively pass a single value

6388 ``None`` to remove all existing correlations.

6389

6390 .. seealso::

6391

6392 :meth:`_expression.Select.correlate_except`

6393

6394 :ref:`tutorial_scalar_subquery`

6395

6396 """

6397

6398 # tests failing when we try to change how these

6399 # arguments are passed

6400

6401 self._auto_correlate = False

6402 if not fromclauses or fromclauses[0] in {None, False}:

6403 if len(fromclauses) > 1:

6404 raise exc.ArgumentError(

6405 "additional FROM objects not accepted when "

6406 "passing None/False to correlate()"

6407 )

6408 self._correlate = ()

6409 else:

6410 self._correlate = self._correlate + tuple(

6411 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6412 )

6413 return self

6414

6415 @_generative

6416 def correlate_except(

6417 self,

6418 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6419 ) -> Self:

6420 r"""Return a new :class:`_expression.Select`

6421 which will omit the given FROM

6422 clauses from the auto-correlation process.

6423

6424 Calling :meth:`_expression.Select.correlate_except` turns off the

6425 :class:`_expression.Select` object's default behavior of

6426 "auto-correlation" for the given FROM elements. An element

6427 specified here will unconditionally appear in the FROM list, while

6428 all other FROM elements remain subject to normal auto-correlation

6429 behaviors.

6430

6431 If ``None`` is passed, or no arguments are passed,

6432 the :class:`_expression.Select` object will correlate all of its

6433 FROM entries.

6434

6435 :param \*fromclauses: a list of one or more

6436 :class:`_expression.FromClause`

6437 constructs, or other compatible constructs (i.e. ORM-mapped

6438 classes) to become part of the correlate-exception collection.

6439

6440 .. seealso::

6441

6442 :meth:`_expression.Select.correlate`

6443

6444 :ref:`tutorial_scalar_subquery`

6445

6446 """

6447

6448 self._auto_correlate = False

6449 if not fromclauses or fromclauses[0] in {None, False}:

6450 if len(fromclauses) > 1:

6451 raise exc.ArgumentError(

6452 "additional FROM objects not accepted when "

6453 "passing None/False to correlate_except()"

6454 )

6455 self._correlate_except = ()

6456 else:

6457 self._correlate_except = (self._correlate_except or ()) + tuple(

6458 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6459 )

6460

6461 return self

6462

6463 @HasMemoized_ro_memoized_attribute

6464 def selected_columns(

6465 self,

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

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

6468 representing the columns that

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

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

6471

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

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

6474 within this collection cannot be directly nested inside another SELECT

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

6476 necessary parenthesization required by SQL.

6477

6478 For a :func:`_expression.select` construct, the collection here is

6479 exactly what would be rendered inside the "SELECT" statement, and the

6480 :class:`_expression.ColumnElement` objects are directly present as they

6481 were given, e.g.::

6482

6483 col1 = column("q", Integer)

6484 col2 = column("p", Integer)

6485 stmt = select(col1, col2)

6486

6487 Above, ``stmt.selected_columns`` would be a collection that contains

6488 the ``col1`` and ``col2`` objects directly. For a statement that is

6489 against a :class:`_schema.Table` or other

6490 :class:`_expression.FromClause`, the collection will use the

6491 :class:`_expression.ColumnElement` objects that are in the

6492 :attr:`_expression.FromClause.c` collection of the from element.

6493

6494 A use case for the :attr:`_sql.Select.selected_columns` collection is

6495 to allow the existing columns to be referenced when adding additional

6496 criteria, e.g.::

6497

6498 def filter_on_id(my_select, id):

6499 return my_select.where(my_select.selected_columns["id"] == id)

6500

6501

6502 stmt = select(MyModel)

6503

6504 # adds "WHERE id=:param" to the statement

6505 stmt = filter_on_id(stmt, 42)

6506

6507 .. note::

6508

6509 The :attr:`_sql.Select.selected_columns` collection does not

6510 include expressions established in the columns clause using the

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

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

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

6514 construct.

6515

6516

6517 .. versionadded:: 1.4

6518

6519 """

6520

6521 # compare to SelectState._generate_columns_plus_names, which

6522 # generates the actual names used in the SELECT string. that

6523 # method is more complex because it also renders columns that are

6524 # fully ambiguous, e.g. same column more than once.

6525 conv = cast(

6526 "Callable[[Any], str]",

6527 SelectState._column_naming_convention(self._label_style),

6528 )

6529

6530 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6531 [

6532 (conv(c), c)

6533 for c in self._all_selected_columns

6534 if is_column_element(c)

6535 ]

6536 )

6537 return cc.as_readonly()

6538

6539 @HasMemoized_ro_memoized_attribute

6540 def _all_selected_columns(self) -> _SelectIterable:

6541 meth = SelectState.get_plugin_class(self).all_selected_columns

6542 return list(meth(self))

6543

6544 def _ensure_disambiguated_names(self) -> Select[Any]:

6545 if self._label_style is LABEL_STYLE_NONE:

6546 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6547 return self

6548

6549 def _generate_fromclause_column_proxies(

6550 self,

6551 subquery: FromClause,

6552 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6553 primary_key: ColumnSet,

6554 foreign_keys: Set[KeyedColumnElement[Any]],

6555 *,

6556 proxy_compound_columns: Optional[

6557 Iterable[Sequence[ColumnElement[Any]]]

6558 ] = None,

6559 ) -> None:

6560 """Generate column proxies to place in the exported ``.c``

6561 collection of a subquery."""

6562

6563 if proxy_compound_columns:

6564 extra_col_iterator = proxy_compound_columns

6565 prox = [

6566 c._make_proxy(

6567 subquery,

6568 key=proxy_key,

6569 name=required_label_name,

6570 name_is_truncatable=True,

6571 compound_select_cols=extra_cols,

6572 primary_key=primary_key,

6573 foreign_keys=foreign_keys,

6574 )

6575 for (

6576 (

6577 required_label_name,

6578 proxy_key,

6579 fallback_label_name,

6580 c,

6581 repeated,

6582 ),

6583 extra_cols,

6584 ) in (

6585 zip(

6586 self._generate_columns_plus_names(False),

6587 extra_col_iterator,

6588 )

6589 )

6590 if is_column_element(c)

6591 ]

6592 else:

6593 prox = [

6594 c._make_proxy(

6595 subquery,

6596 key=proxy_key,

6597 name=required_label_name,

6598 name_is_truncatable=True,

6599 primary_key=primary_key,

6600 foreign_keys=foreign_keys,

6601 )

6602 for (

6603 required_label_name,

6604 proxy_key,

6605 fallback_label_name,

6606 c,

6607 repeated,

6608 ) in (self._generate_columns_plus_names(False))

6609 if is_column_element(c)

6610 ]

6611

6612 columns._populate_separate_keys(prox)

6613

6614 def _needs_parens_for_grouping(self) -> bool:

6615 return self._has_row_limiting_clause or bool(

6616 self._order_by_clause.clauses

6617 )

6618

6619 def self_group(

6620 self, against: Optional[OperatorType] = None

6621 ) -> Union[SelectStatementGrouping[Self], Self]:

6622 """Return a 'grouping' construct as per the

6623 :class:`_expression.ClauseElement` specification.

6624

6625 This produces an element that can be embedded in an expression. Note

6626 that this method is called automatically as needed when constructing

6627 expressions and should not require explicit use.

6628

6629 """

6630 if (

6631 isinstance(against, CompoundSelect)

6632 and not self._needs_parens_for_grouping()

6633 ):

6634 return self

6635 else:

6636 return SelectStatementGrouping(self)

6637

6638 def union(

6639 self, *other: _SelectStatementForCompoundArgument[_TP]

6640 ) -> CompoundSelect[_TP]:

6641 r"""Return a SQL ``UNION`` of this select() construct against

6642 the given selectables provided as positional arguments.

6643

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

6645 UNION.

6646

6647 .. versionchanged:: 1.4.28

6648

6649 multiple elements are now accepted.

6650

6651 :param \**kwargs: keyword arguments are forwarded to the constructor

6652 for the newly created :class:`_sql.CompoundSelect` object.

6653

6654 """

6655 return CompoundSelect._create_union(self, *other)

6656

6657 def union_all(

6658 self, *other: _SelectStatementForCompoundArgument[_TP]

6659 ) -> CompoundSelect[_TP]:

6660 r"""Return a SQL ``UNION ALL`` of this select() construct against

6661 the given selectables provided as positional arguments.

6662

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

6664 UNION.

6665

6666 .. versionchanged:: 1.4.28

6667

6668 multiple elements are now accepted.

6669

6670 :param \**kwargs: keyword arguments are forwarded to the constructor

6671 for the newly created :class:`_sql.CompoundSelect` object.

6672

6673 """

6674 return CompoundSelect._create_union_all(self, *other)

6675

6676 def except_(

6677 self, *other: _SelectStatementForCompoundArgument[_TP]

6678 ) -> CompoundSelect[_TP]:

6679 r"""Return a SQL ``EXCEPT`` of this select() construct against

6680 the given selectable provided as positional arguments.

6681

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

6683 UNION.

6684

6685 .. versionchanged:: 1.4.28

6686

6687 multiple elements are now accepted.

6688

6689 """

6690 return CompoundSelect._create_except(self, *other)

6691

6692 def except_all(

6693 self, *other: _SelectStatementForCompoundArgument[_TP]

6694 ) -> CompoundSelect[_TP]:

6695 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6696 the given selectables provided as positional arguments.

6697

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

6699 UNION.

6700

6701 .. versionchanged:: 1.4.28

6702

6703 multiple elements are now accepted.

6704

6705 """

6706 return CompoundSelect._create_except_all(self, *other)

6707

6708 def intersect(

6709 self, *other: _SelectStatementForCompoundArgument[_TP]

6710 ) -> CompoundSelect[_TP]:

6711 r"""Return a SQL ``INTERSECT`` of this select() construct against

6712 the given selectables provided as positional arguments.

6713

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

6715 UNION.

6716

6717 .. versionchanged:: 1.4.28

6718

6719 multiple elements are now accepted.

6720

6721 :param \**kwargs: keyword arguments are forwarded to the constructor

6722 for the newly created :class:`_sql.CompoundSelect` object.

6723

6724 """

6725 return CompoundSelect._create_intersect(self, *other)

6726

6727 def intersect_all(

6728 self, *other: _SelectStatementForCompoundArgument[_TP]

6729 ) -> CompoundSelect[_TP]:

6730 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6731 against the given selectables provided as positional arguments.

6732

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

6734 UNION.

6735

6736 .. versionchanged:: 1.4.28

6737

6738 multiple elements are now accepted.

6739

6740 :param \**kwargs: keyword arguments are forwarded to the constructor

6741 for the newly created :class:`_sql.CompoundSelect` object.

6742

6743 """

6744 return CompoundSelect._create_intersect_all(self, *other)

6745

6746

6747class ScalarSelect(

6748 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6749):

6750 """Represent a scalar subquery.

6751

6752

6753 A :class:`_sql.ScalarSelect` is created by invoking the

6754 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6755 then participates in other SQL expressions as a SQL column expression

6756 within the :class:`_sql.ColumnElement` hierarchy.

6757

6758 .. seealso::

6759

6760 :meth:`_sql.SelectBase.scalar_subquery`

6761

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

6763

6764 """

6765

6766 _traverse_internals: _TraverseInternalsType = [

6767 ("element", InternalTraversal.dp_clauseelement),

6768 ("type", InternalTraversal.dp_type),

6769 ]

6770

6771 _from_objects: List[FromClause] = []

6772 _is_from_container = True

6773 if not TYPE_CHECKING:

6774 _is_implicitly_boolean = False

6775 inherit_cache = True

6776

6777 element: SelectBase

6778

6779 def __init__(self, element: SelectBase) -> None:

6780 self.element = element

6781 self.type = element._scalar_type()

6782 self._propagate_attrs = element._propagate_attrs

6783

6784 def __getattr__(self, attr: str) -> Any:

6785 return getattr(self.element, attr)

6786

6787 def __getstate__(self) -> Dict[str, Any]:

6788 return {"element": self.element, "type": self.type}

6789

6790 def __setstate__(self, state: Dict[str, Any]) -> None:

6791 self.element = state["element"]

6792 self.type = state["type"]

6793

6794 @property

6795 def columns(self) -> NoReturn:

6796 raise exc.InvalidRequestError(

6797 "Scalar Select expression has no "

6798 "columns; use this object directly "

6799 "within a column-level expression."

6800 )

6801

6802 c = columns

6803

6804 @_generative

6805 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6806 """Apply a WHERE clause to the SELECT statement referred to

6807 by this :class:`_expression.ScalarSelect`.

6808

6809 """

6810 self.element = cast("Select[Any]", self.element).where(crit)

6811 return self

6812

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

6814 return self

6815

6816 def _ungroup(self) -> Self:

6817 return self

6818

6819 @_generative

6820 def correlate(

6821 self,

6822 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6823 ) -> Self:

6824 r"""Return a new :class:`_expression.ScalarSelect`

6825 which will correlate the given FROM

6826 clauses to that of an enclosing :class:`_expression.Select`.

6827

6828 This method is mirrored from the :meth:`_sql.Select.correlate` method

6829 of the underlying :class:`_sql.Select`. The method applies the

6830 :meth:_sql.Select.correlate` method, then returns a new

6831 :class:`_sql.ScalarSelect` against that statement.

6832

6833 .. versionadded:: 1.4 Previously, the

6834 :meth:`_sql.ScalarSelect.correlate`

6835 method was only available from :class:`_sql.Select`.

6836

6837 :param \*fromclauses: a list of one or more

6838 :class:`_expression.FromClause`

6839 constructs, or other compatible constructs (i.e. ORM-mapped

6840 classes) to become part of the correlate collection.

6841

6842 .. seealso::

6843

6844 :meth:`_expression.ScalarSelect.correlate_except`

6845

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

6847

6848

6849 """

6850 self.element = cast("Select[Any]", self.element).correlate(

6851 *fromclauses

6852 )

6853 return self

6854

6855 @_generative

6856 def correlate_except(

6857 self,

6858 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6859 ) -> Self:

6860 r"""Return a new :class:`_expression.ScalarSelect`

6861 which will omit the given FROM

6862 clauses from the auto-correlation process.

6863

6864 This method is mirrored from the

6865 :meth:`_sql.Select.correlate_except` method of the underlying

6866 :class:`_sql.Select`. The method applies the

6867 :meth:_sql.Select.correlate_except` method, then returns a new

6868 :class:`_sql.ScalarSelect` against that statement.

6869

6870 .. versionadded:: 1.4 Previously, the

6871 :meth:`_sql.ScalarSelect.correlate_except`

6872 method was only available from :class:`_sql.Select`.

6873

6874 :param \*fromclauses: a list of one or more

6875 :class:`_expression.FromClause`

6876 constructs, or other compatible constructs (i.e. ORM-mapped

6877 classes) to become part of the correlate-exception collection.

6878

6879 .. seealso::

6880

6881 :meth:`_expression.ScalarSelect.correlate`

6882

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

6884

6885

6886 """

6887

6888 self.element = cast("Select[Any]", self.element).correlate_except(

6889 *fromclauses

6890 )

6891 return self

6892

6893

6894class Exists(UnaryExpression[bool]):

6895 """Represent an ``EXISTS`` clause.

6896

6897 See :func:`_sql.exists` for a description of usage.

6898

6899 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6900 instance by calling :meth:`_sql.SelectBase.exists`.

6901

6902 """

6903

6904 inherit_cache = True

6905

6906 def __init__(

6907 self,

6908 __argument: Optional[

6909 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6910 ] = None,

6911 ):

6912 s: ScalarSelect[Any]

6913

6914 # TODO: this seems like we should be using coercions for this

6915 if __argument is None:

6916 s = Select(literal_column("*")).scalar_subquery()

6917 elif isinstance(__argument, SelectBase):

6918 s = __argument.scalar_subquery()

6919 s._propagate_attrs = __argument._propagate_attrs

6920 elif isinstance(__argument, ScalarSelect):

6921 s = __argument

6922 else:

6923 s = Select(__argument).scalar_subquery()

6924

6925 UnaryExpression.__init__(

6926 self,

6927 s,

6928 operator=operators.exists,

6929 type_=type_api.BOOLEANTYPE,

6930 )

6931

6932 @util.ro_non_memoized_property

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

6934 return []

6935

6936 def _regroup(

6937 self,

6938 fn: Callable[[Select[Any]], Select[Any]],

6939 ) -> ScalarSelect[Any]:

6940

6941 assert isinstance(self.element, ScalarSelect)

6942 element = self.element.element

6943 if not isinstance(element, Select):

6944 raise exc.InvalidRequestError(

6945 "Can only apply this operation to a plain SELECT construct"

6946 )

6947 new_element = fn(element)

6948

6949 return_value = new_element.scalar_subquery()

6950 return return_value

6951

6952 def select(self) -> Select[Tuple[bool]]:

6953 r"""Return a SELECT of this :class:`_expression.Exists`.

6954

6955 e.g.::

6956

6957 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6958

6959 This will produce a statement resembling:

6960

6961 .. sourcecode:: sql

6962

6963 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6964

6965 .. seealso::

6966

6967 :func:`_expression.select` - general purpose

6968 method which allows for arbitrary column lists.

6969

6970 """ # noqa

6971

6972 return Select(self)

6973

6974 def correlate(

6975 self,

6976 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6977 ) -> Self:

6978 """Apply correlation to the subquery noted by this

6979 :class:`_sql.Exists`.

6980

6981 .. seealso::

6982

6983 :meth:`_sql.ScalarSelect.correlate`

6984

6985 """

6986 e = self._clone()

6987 e.element = self._regroup(

6988 lambda element: element.correlate(*fromclauses)

6989 )

6990 return e

6991

6992 def correlate_except(

6993 self,

6994 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6995 ) -> Self:

6996 """Apply correlation to the subquery noted by this

6997 :class:`_sql.Exists`.

6998

6999 .. seealso::

7000

7001 :meth:`_sql.ScalarSelect.correlate_except`

7002

7003 """

7004 e = self._clone()

7005 e.element = self._regroup(

7006 lambda element: element.correlate_except(*fromclauses)

7007 )

7008 return e

7009

7010 def select_from(self, *froms: _FromClauseArgument) -> Self:

7011 """Return a new :class:`_expression.Exists` construct,

7012 applying the given

7013 expression to the :meth:`_expression.Select.select_from`

7014 method of the select

7015 statement contained.

7016

7017 .. note:: it is typically preferable to build a :class:`_sql.Select`

7018 statement first, including the desired WHERE clause, then use the

7019 :meth:`_sql.SelectBase.exists` method to produce an

7020 :class:`_sql.Exists` object at once.

7021

7022 """

7023 e = self._clone()

7024 e.element = self._regroup(lambda element: element.select_from(*froms))

7025 return e

7026

7027 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7028 """Return a new :func:`_expression.exists` construct with the

7029 given expression added to

7030 its WHERE clause, joined to the existing clause via AND, if any.

7031

7032

7033 .. note:: it is typically preferable to build a :class:`_sql.Select`

7034 statement first, including the desired WHERE clause, then use the

7035 :meth:`_sql.SelectBase.exists` method to produce an

7036 :class:`_sql.Exists` object at once.

7037

7038 """

7039 e = self._clone()

7040 e.element = self._regroup(lambda element: element.where(*clause))

7041 return e

7042

7043

7044class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7045 """Wrap a :class:`_expression.TextClause` construct within a

7046 :class:`_expression.SelectBase`

7047 interface.

7048

7049 This allows the :class:`_expression.TextClause` object to gain a

7050 ``.c`` collection

7051 and other FROM-like capabilities such as

7052 :meth:`_expression.FromClause.alias`,

7053 :meth:`_expression.SelectBase.cte`, etc.

7054

7055 The :class:`_expression.TextualSelect` construct is produced via the

7056 :meth:`_expression.TextClause.columns`

7057 method - see that method for details.

7058

7059 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7060 class was renamed

7061 from ``TextAsFrom``, to more correctly suit its role as a

7062 SELECT-oriented object and not a FROM clause.

7063

7064 .. seealso::

7065

7066 :func:`_expression.text`

7067

7068 :meth:`_expression.TextClause.columns` - primary creation interface.

7069

7070 """

7071

7072 __visit_name__ = "textual_select"

7073

7074 _label_style = LABEL_STYLE_NONE

7075

7076 _traverse_internals: _TraverseInternalsType = (

7077 [

7078 ("element", InternalTraversal.dp_clauseelement),

7079 ("column_args", InternalTraversal.dp_clauseelement_list),

7080 ]

7081 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7082 + HasCTE._has_ctes_traverse_internals

7083 + Executable._executable_traverse_internals

7084 )

7085

7086 _is_textual = True

7087

7088 is_text = True

7089 is_select = True

7090

7091 def __init__(

7092 self,

7093 text: TextClause,

7094 columns: List[_ColumnExpressionArgument[Any]],

7095 positional: bool = False,

7096 ) -> None:

7097 self._init(

7098 text,

7099 # convert for ORM attributes->columns, etc

7100 [

7101 coercions.expect(roles.LabeledColumnExprRole, c)

7102 for c in columns

7103 ],

7104 positional,

7105 )

7106

7107 def _init(

7108 self,

7109 text: TextClause,

7110 columns: List[NamedColumn[Any]],

7111 positional: bool = False,

7112 ) -> None:

7113 self.element = text

7114 self.column_args = columns

7115 self.positional = positional

7116

7117 @HasMemoized_ro_memoized_attribute

7118 def selected_columns(

7119 self,

7120 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

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

7122 representing the columns that

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

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

7125

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

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

7128 within this collection cannot be directly nested inside another SELECT

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

7130 necessary parenthesization required by SQL.

7131

7132 For a :class:`_expression.TextualSelect` construct, the collection

7133 contains the :class:`_expression.ColumnElement` objects that were

7134 passed to the constructor, typically via the

7135 :meth:`_expression.TextClause.columns` method.

7136

7137

7138 .. versionadded:: 1.4

7139

7140 """

7141 return ColumnCollection(

7142 (c.key, c) for c in self.column_args

7143 ).as_readonly()

7144

7145 @util.ro_non_memoized_property

7146 def _all_selected_columns(self) -> _SelectIterable:

7147 return self.column_args

7148

7149 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7150 return self

7151

7152 def _ensure_disambiguated_names(self) -> TextualSelect:

7153 return self

7154

7155 @_generative

7156 def bindparams(

7157 self,

7158 *binds: BindParameter[Any],

7159 **bind_as_values: Any,

7160 ) -> Self:

7161 self.element = self.element.bindparams(*binds, **bind_as_values)

7162 return self

7163

7164 def _generate_fromclause_column_proxies(

7165 self,

7166 fromclause: FromClause,

7167 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7168 primary_key: ColumnSet,

7169 foreign_keys: Set[KeyedColumnElement[Any]],

7170 *,

7171 proxy_compound_columns: Optional[

7172 Iterable[Sequence[ColumnElement[Any]]]

7173 ] = None,

7174 ) -> None:

7175 if TYPE_CHECKING:

7176 assert isinstance(fromclause, Subquery)

7177

7178 if proxy_compound_columns:

7179 columns._populate_separate_keys(

7180 c._make_proxy(

7181 fromclause,

7182 compound_select_cols=extra_cols,

7183 primary_key=primary_key,

7184 foreign_keys=foreign_keys,

7185 )

7186 for c, extra_cols in zip(

7187 self.column_args, proxy_compound_columns

7188 )

7189 )

7190 else:

7191 columns._populate_separate_keys(

7192 c._make_proxy(

7193 fromclause,

7194 primary_key=primary_key,

7195 foreign_keys=foreign_keys,

7196 )

7197 for c in self.column_args

7198 )

7199

7200 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7201 return self.column_args[0].type

7202

7203

7204TextAsFrom = TextualSelect

7205"""Backwards compatibility with the previous name"""

7206

7207

7208class AnnotatedFromClause(Annotated):

7209 def _copy_internals(

7210 self,

7211 _annotations_traversal: bool = False,

7212 ind_cols_on_fromclause: bool = False,

7213 **kw: Any,

7214 ) -> None:

7215 super()._copy_internals(**kw)

7216

7217 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7218 # the traversals used by annotations for these cases are not currently

7219 # designed around expecting that inner elements inside of

7220 # AnnotatedFromClause's element are also deep copied, so skip for these

7221 # cases. in other cases such as plain visitors.cloned_traverse(), we

7222 # expect this to happen. see issue #12915

7223 if not _annotations_traversal:

7224 ee = self._Annotated__element # type: ignore

7225 ee._copy_internals(**kw)

7226

7227 if ind_cols_on_fromclause:

7228 # passed from annotations._deep_annotate(). See that function

7229 # for notes

7230 ee = self._Annotated__element # type: ignore

7231 self.c = ee.__class__.c.fget(self) # type: ignore

7232

7233 @util.ro_memoized_property

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

7235 """proxy the .c collection of the underlying FromClause.

7236

7237 Originally implemented in 2008 as a simple load of the .c collection

7238 when the annotated construct was created (see d3621ae961a), in modern

7239 SQLAlchemy versions this can be expensive for statements constructed

7240 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7241 it, which works just as well.

7242

7243 Two different use cases seem to require the collection either copied

7244 from the underlying one, or unique to this AnnotatedFromClause.

7245

7246 See test_selectable->test_annotated_corresponding_column

7247

7248 """

7249 ee = self._Annotated__element # type: ignore

7250 return ee.c # type: ignore