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

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

1160 FROM table1 JOIN table2 ON true

1161

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

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

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

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

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

1167

1168

1169 .. versionadded:: 1.4

1170

1171 """ # noqa: E501

1172

1173 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1176 when generating the columns clause of a SELECT statement.

1177

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

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

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

1181

1182 .. sourcecode:: pycon+sql

1183

1184 >>> from sqlalchemy import (

1185 ... table,

1186 ... column,

1187 ... select,

1188 ... true,

1189 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1190 ... )

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

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

1193 >>> print(

1194 ... select(table1, table2)

1195 ... .join(table2, true())

1196 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1197 ... )

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

1199 FROM table1 JOIN table2 ON true

1200

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

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

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

1204

1205 .. versionadded:: 1.4

1206

1207 """ # noqa: E501

1208

1209 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1210 """The default label style, refers to

1211 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1212

1213 .. versionadded:: 1.4

1214

1215 """

1216

1217 LABEL_STYLE_LEGACY_ORM = 3

1218

1219

1220(

1221 LABEL_STYLE_NONE,

1222 LABEL_STYLE_TABLENAME_PLUS_COL,

1223 LABEL_STYLE_DISAMBIGUATE_ONLY,

1224 _,

1225) = list(SelectLabelStyle)

1226

1227LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1228

1229

1230class Join(roles.DMLTableRole, FromClause):

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

1232 :class:`_expression.FromClause`

1233 elements.

1234

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

1236 is the module-level

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

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

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

1240 :class:`_schema.Table`).

1241

1242 .. seealso::

1243

1244 :func:`_expression.join`

1245

1246 :meth:`_expression.FromClause.join`

1247

1248 """

1249

1250 __visit_name__ = "join"

1251

1252 _traverse_internals: _TraverseInternalsType = [

1253 ("left", InternalTraversal.dp_clauseelement),

1254 ("right", InternalTraversal.dp_clauseelement),

1255 ("onclause", InternalTraversal.dp_clauseelement),

1256 ("isouter", InternalTraversal.dp_boolean),

1257 ("full", InternalTraversal.dp_boolean),

1258 ]

1259

1260 _is_join = True

1261

1262 left: FromClause

1263 right: FromClause

1264 onclause: Optional[ColumnElement[bool]]

1265 isouter: bool

1266 full: bool

1267

1268 def __init__(

1269 self,

1270 left: _FromClauseArgument,

1271 right: _FromClauseArgument,

1272 onclause: Optional[_OnClauseArgument] = None,

1273 isouter: bool = False,

1274 full: bool = False,

1275 ):

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

1277

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

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

1280 :class:`_expression.FromClause` object.

1281

1282 """

1283

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

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

1286 # annotated objects. test_orm.py -> test_fetch_results

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

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

1289 # callcounts for a single compilation in that particular test

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

1291 # 29200 -> 30373

1292

1293 self.left = coercions.expect(

1294 roles.FromClauseRole,

1295 left,

1296 )

1297 self.right = coercions.expect(

1298 roles.FromClauseRole,

1299 right,

1300 ).self_group()

1301

1302 if onclause is None:

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

1304 else:

1305 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1306 # not merged yet

1307 self.onclause = coercions.expect(

1308 roles.OnClauseRole, onclause

1309 ).self_group(against=operators._asbool)

1310

1311 self.isouter = isouter

1312 self.full = full

1313

1314 @util.ro_non_memoized_property

1315 def description(self) -> str:

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

1317 self.left.description,

1318 id(self.left),

1319 self.right.description,

1320 id(self.right),

1321 )

1322

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

1324 return (

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

1326 # as well

1327 hash(fromclause) == hash(self)

1328 or self.left.is_derived_from(fromclause)

1329 or self.right.is_derived_from(fromclause)

1330 )

1331

1332 def self_group(

1333 self, against: Optional[OperatorType] = None

1334 ) -> FromGrouping:

1335 return FromGrouping(self)

1336

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

1338 def _populate_column_collection(

1339 self,

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

1341 primary_key: ColumnSet,

1342 foreign_keys: Set[KeyedColumnElement[Any]],

1343 ) -> None:

1344 sqlutil = util.preloaded.sql_util

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

1346 c for c in self.right.c

1347 ]

1348

1349 primary_key.extend( # type: ignore

1350 sqlutil.reduce_columns(

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

1352 )

1353 )

1354 columns._populate_separate_keys(

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

1356 )

1357 foreign_keys.update(

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

1359 )

1360

1361 def _copy_internals(

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

1363 ) -> None:

1364 # see Select._copy_internals() for similar concept

1365

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

1367 # determine the new FROM clauses

1368 all_the_froms = set(

1369 itertools.chain(

1370 _from_objects(self.left),

1371 _from_objects(self.right),

1372 )

1373 )

1374

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

1376 # cache used by the clone function

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

1378

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

1380 # ColumnClause with parent table referring to those

1381 # replaced FromClause objects

1382 def replace(

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

1384 **kw: Any,

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

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

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

1388 return newelem

1389 return None

1390

1391 kw["replace"] = replace

1392

1393 # run normal _copy_internals. the clones for

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

1395 # cache

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

1397

1398 self._reset_memoizations()

1399

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

1401 super()._refresh_for_new_column(column)

1402 self.left._refresh_for_new_column(column)

1403 self.right._refresh_for_new_column(column)

1404

1405 def _match_primaries(

1406 self,

1407 left: FromClause,

1408 right: FromClause,

1409 ) -> ColumnElement[bool]:

1410 if isinstance(left, Join):

1411 left_right = left.right

1412 else:

1413 left_right = None

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

1415

1416 @classmethod

1417 def _join_condition(

1418 cls,

1419 a: FromClause,

1420 b: FromClause,

1421 *,

1422 a_subset: Optional[FromClause] = None,

1423 consider_as_foreign_keys: Optional[

1424 AbstractSet[ColumnClause[Any]]

1425 ] = None,

1426 ) -> ColumnElement[bool]:

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

1428

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

1430

1431 """

1432 constraints = cls._joincond_scan_left_right(

1433 a, a_subset, b, consider_as_foreign_keys

1434 )

1435

1436 if len(constraints) > 1:

1437 cls._joincond_trim_constraints(

1438 a, b, constraints, consider_as_foreign_keys

1439 )

1440

1441 if len(constraints) == 0:

1442 if isinstance(b, FromGrouping):

1443 hint = (

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

1445 "subquery using alias()?"

1446 )

1447 else:

1448 hint = ""

1449 raise exc.NoForeignKeysError(

1450 "Can't find any foreign key relationships "

1451 "between '%s' and '%s'.%s"

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

1453 )

1454

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

1456 if len(crit) == 1:

1457 return crit[0]

1458 else:

1459 return and_(*crit)

1460

1461 @classmethod

1462 def _can_join(

1463 cls,

1464 left: FromClause,

1465 right: FromClause,

1466 *,

1467 consider_as_foreign_keys: Optional[

1468 AbstractSet[ColumnClause[Any]]

1469 ] = None,

1470 ) -> bool:

1471 if isinstance(left, Join):

1472 left_right = left.right

1473 else:

1474 left_right = None

1475

1476 constraints = cls._joincond_scan_left_right(

1477 a=left,

1478 b=right,

1479 a_subset=left_right,

1480 consider_as_foreign_keys=consider_as_foreign_keys,

1481 )

1482

1483 return bool(constraints)

1484

1485 @classmethod

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

1487 def _joincond_scan_left_right(

1488 cls,

1489 a: FromClause,

1490 a_subset: Optional[FromClause],

1491 b: FromClause,

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

1493 ) -> collections.defaultdict[

1494 Optional[ForeignKeyConstraint],

1495 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1496 ]:

1497 sql_util = util.preloaded.sql_util

1498

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

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

1501

1502 constraints: collections.defaultdict[

1503 Optional[ForeignKeyConstraint],

1504 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1505 ] = collections.defaultdict(list)

1506

1507 for left in (a_subset, a):

1508 if left is None:

1509 continue

1510 for fk in sorted(

1511 b.foreign_keys,

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

1513 ):

1514 if (

1515 consider_as_foreign_keys is not None

1516 and fk.parent not in consider_as_foreign_keys

1517 ):

1518 continue

1519 try:

1520 col = fk.get_referent(left)

1521 except exc.NoReferenceError as nrte:

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

1523 if nrte.table_name in table_names:

1524 raise

1525 else:

1526 continue

1527

1528 if col is not None:

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

1530 if left is not b:

1531 for fk in sorted(

1532 left.foreign_keys,

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

1534 ):

1535 if (

1536 consider_as_foreign_keys is not None

1537 and fk.parent not in consider_as_foreign_keys

1538 ):

1539 continue

1540 try:

1541 col = fk.get_referent(b)

1542 except exc.NoReferenceError as nrte:

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

1544 if nrte.table_name in table_names:

1545 raise

1546 else:

1547 continue

1548

1549 if col is not None:

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

1551 if constraints:

1552 break

1553 return constraints

1554

1555 @classmethod

1556 def _joincond_trim_constraints(

1557 cls,

1558 a: FromClause,

1559 b: FromClause,

1560 constraints: Dict[Any, Any],

1561 consider_as_foreign_keys: Optional[Any],

1562 ) -> None:

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

1564 # to include just those FKCs that match exactly to

1565 # "consider_as_foreign_keys".

1566 if consider_as_foreign_keys:

1567 for const in list(constraints):

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

1569 consider_as_foreign_keys

1570 ):

1571 del constraints[const]

1572

1573 # if still multiple constraints, but

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

1575 if len(constraints) > 1:

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

1577 if len(dedupe) == 1:

1578 key = list(constraints)[0]

1579 constraints = {key: constraints[key]}

1580

1581 if len(constraints) != 1:

1582 raise exc.AmbiguousForeignKeysError(

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

1584 "tables have more than one foreign key "

1585 "constraint relationship between them. "

1586 "Please specify the 'onclause' of this "

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

1588 )

1589

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

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

1592 :class:`_expression.Join`.

1593

1594 E.g.::

1595

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

1597

1598 stmt = stmt.select()

1599

1600 The above will produce a SQL string resembling:

1601

1602 .. sourcecode:: sql

1603

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

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

1606

1607 """

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

1609

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

1611 def _anonymous_fromclause(

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

1613 ) -> TODO_Any:

1614 sqlutil = util.preloaded.sql_util

1615 if flat:

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

1617 left_name = name # will recurse

1618 else:

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

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

1621 else:

1622 left_name = name

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

1624 right_name = name # will recurse

1625 else:

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

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

1628 else:

1629 right_name = name

1630 left_a, right_a = (

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

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

1633 )

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

1635 sqlutil.ClauseAdapter(right_a)

1636 )

1637

1638 return left_a.join(

1639 right_a,

1640 adapter.traverse(self.onclause),

1641 isouter=self.isouter,

1642 full=self.full,

1643 )

1644 else:

1645 return (

1646 self.select()

1647 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1648 .correlate(None)

1649 .alias(name)

1650 )

1651

1652 @util.ro_non_memoized_property

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

1654 return itertools.chain(

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

1656 )

1657

1658 @util.ro_non_memoized_property

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

1660 self_list: List[FromClause] = [self]

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

1662

1663

1664class NoInit:

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

1666 raise NotImplementedError(

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

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

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

1670 "selectable objects."

1671 % (

1672 self.__class__.__name__,

1673 self.__class__.__name__.lower(),

1674 self.__class__.__name__.lower(),

1675 )

1676 )

1677

1678

1679class LateralFromClause(NamedFromClause):

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

1681

1682

1683# FromClause ->

1684# AliasedReturnsRows

1685# -> Alias only for FromClause

1686# -> Subquery only for SelectBase

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

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

1689# w/ non-deprecated coercion

1690# -> TableSample -> only for FromClause

1691

1692

1693class AliasedReturnsRows(NoInit, NamedFromClause):

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

1695 selectables."""

1696

1697 _is_from_container = True

1698

1699 _supports_derived_columns = False

1700

1701 element: ReturnsRows

1702

1703 _traverse_internals: _TraverseInternalsType = [

1704 ("element", InternalTraversal.dp_clauseelement),

1705 ("name", InternalTraversal.dp_anon_name),

1706 ]

1707

1708 @classmethod

1709 def _construct(

1710 cls,

1711 selectable: Any,

1712 *,

1713 name: Optional[str] = None,

1714 **kw: Any,

1715 ) -> Self:

1716 obj = cls.__new__(cls)

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

1718 return obj

1719

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

1721 self.element = coercions.expect(

1722 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1723 )

1724 self.element = selectable

1725 self._orig_name = name

1726 if name is None:

1727 if (

1728 isinstance(selectable, FromClause)

1729 and selectable.named_with_column

1730 ):

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

1732 if isinstance(name, _anonymous_label):

1733 name = None

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

1735 self.name = name

1736

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

1738 super()._refresh_for_new_column(column)

1739 self.element._refresh_for_new_column(column)

1740

1741 def _populate_column_collection(

1742 self,

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

1744 primary_key: ColumnSet,

1745 foreign_keys: Set[KeyedColumnElement[Any]],

1746 ) -> None:

1747 self.element._generate_fromclause_column_proxies(

1748 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1749 )

1750

1751 @util.ro_non_memoized_property

1752 def description(self) -> str:

1753 name = self.name

1754 if isinstance(name, _anonymous_label):

1755 return "anon_1"

1756

1757 return name

1758

1759 @util.ro_non_memoized_property

1760 def implicit_returning(self) -> bool:

1761 return self.element.implicit_returning # type: ignore

1762

1763 @property

1764 def original(self) -> ReturnsRows:

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

1766 return self.element

1767

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

1769 if fromclause in self._cloned_set:

1770 return True

1771 return self.element.is_derived_from(fromclause)

1772

1773 def _copy_internals(

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

1775 ) -> None:

1776 existing_element = self.element

1777

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

1779

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

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

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

1783 # performance.

1784 if existing_element is not self.element:

1785 self._reset_column_collection()

1786

1787 @property

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

1789 return [self]

1790

1791

1792class FromClauseAlias(AliasedReturnsRows):

1793 element: FromClause

1794

1795 @util.ro_non_memoized_property

1796 def description(self) -> str:

1797 name = self.name

1798 if isinstance(name, _anonymous_label):

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

1800

1801 return name

1802

1803

1804class Alias(roles.DMLTableRole, FromClauseAlias):

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

1806

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

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

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

1810

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

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

1813 method available

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

1815

1816 .. seealso::

1817

1818 :meth:`_expression.FromClause.alias`

1819

1820 """

1821

1822 __visit_name__ = "alias"

1823

1824 inherit_cache = True

1825

1826 element: FromClause

1827

1828 @classmethod

1829 def _factory(

1830 cls,

1831 selectable: FromClause,

1832 name: Optional[str] = None,

1833 flat: bool = False,

1834 ) -> NamedFromClause:

1835 return coercions.expect(roles.FromClauseRole, selectable).alias(

1836 name=name, flat=flat

1837 )

1838

1839

1840class TableValuedAlias(LateralFromClause, Alias):

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

1842

1843 This construct provides for a SQL function that returns columns

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

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

1846 method, e.g.:

1847

1848 .. sourcecode:: pycon+sql

1849

1850 >>> from sqlalchemy import select, func

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

1852 ... "value"

1853 ... )

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

1855 {printsql}SELECT anon_1.value

1856 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1857

1858 .. versionadded:: 1.4.0b2

1859

1860 .. seealso::

1861

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

1863

1864 """ # noqa: E501

1865

1866 __visit_name__ = "table_valued_alias"

1867

1868 _supports_derived_columns = True

1869 _render_derived = False

1870 _render_derived_w_types = False

1871 joins_implicitly = False

1872

1873 _traverse_internals: _TraverseInternalsType = [

1874 ("element", InternalTraversal.dp_clauseelement),

1875 ("name", InternalTraversal.dp_anon_name),

1876 ("_tableval_type", InternalTraversal.dp_type),

1877 ("_render_derived", InternalTraversal.dp_boolean),

1878 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1879 ]

1880

1881 def _init(

1882 self,

1883 selectable: Any,

1884 *,

1885 name: Optional[str] = None,

1886 table_value_type: Optional[TableValueType] = None,

1887 joins_implicitly: bool = False,

1888 ) -> None:

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

1890

1891 self.joins_implicitly = joins_implicitly

1892 self._tableval_type = (

1893 type_api.TABLEVALUE

1894 if table_value_type is None

1895 else table_value_type

1896 )

1897

1898 @HasMemoized.memoized_attribute

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

1900 """Return a column expression representing this

1901 :class:`_sql.TableValuedAlias`.

1902

1903 This accessor is used to implement the

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

1905 method for further details.

1906

1907 E.g.:

1908

1909 .. sourcecode:: pycon+sql

1910

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

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

1913

1914 .. seealso::

1915

1916 :meth:`_functions.FunctionElement.column_valued`

1917

1918 """

1919

1920 return TableValuedColumn(self, self._tableval_type)

1921

1922 def alias(

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

1924 ) -> TableValuedAlias:

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

1926

1927 This creates a distinct FROM object that will be distinguished

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

1929

1930 """

1931

1932 tva: TableValuedAlias = TableValuedAlias._construct(

1933 self,

1934 name=name,

1935 table_value_type=self._tableval_type,

1936 joins_implicitly=self.joins_implicitly,

1937 )

1938

1939 if self._render_derived:

1940 tva._render_derived = True

1941 tva._render_derived_w_types = self._render_derived_w_types

1942

1943 return tva

1944

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

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

1947 set, so that it renders as LATERAL.

1948

1949 .. seealso::

1950

1951 :func:`_expression.lateral`

1952

1953 """

1954 tva = self.alias(name=name)

1955 tva._is_lateral = True

1956 return tva

1957

1958 def render_derived(

1959 self,

1960 name: Optional[str] = None,

1961 with_types: bool = False,

1962 ) -> TableValuedAlias:

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

1964

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

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

1967

1968 .. sourcecode:: pycon+sql

1969

1970 >>> print(

1971 ... select(

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

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

1974 ... .render_derived()

1975 ... )

1976 ... )

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

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

1979

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

1981 the alias expression (this syntax currently applies to the

1982 PostgreSQL database):

1983

1984 .. sourcecode:: pycon+sql

1985

1986 >>> print(

1987 ... select(

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

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

1990 ... .render_derived(with_types=True)

1991 ... )

1992 ... )

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

1994 AS anon_1(a INTEGER, b VARCHAR)

1995

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

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

1998

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

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

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

2002

2003 """ # noqa: E501

2004

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

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

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

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

2009 # (just saw it happen on CI)

2010

2011 # construct against original to prevent memory growth

2012 # for repeated generations

2013 new_alias: TableValuedAlias = TableValuedAlias._construct(

2014 self.element,

2015 name=name,

2016 table_value_type=self._tableval_type,

2017 joins_implicitly=self.joins_implicitly,

2018 )

2019 new_alias._render_derived = True

2020 new_alias._render_derived_w_types = with_types

2021 return new_alias

2022

2023

2024class Lateral(FromClauseAlias, LateralFromClause):

2025 """Represent a LATERAL subquery.

2026

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

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

2029 method available

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

2031

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

2033 PostgreSQL versions provide support for this keyword.

2034

2035 .. seealso::

2036

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

2038

2039 """

2040

2041 __visit_name__ = "lateral"

2042 _is_lateral = True

2043

2044 inherit_cache = True

2045

2046 @classmethod

2047 def _factory(

2048 cls,

2049 selectable: Union[SelectBase, _FromClauseArgument],

2050 name: Optional[str] = None,

2051 ) -> LateralFromClause:

2052 return coercions.expect(

2053 roles.FromClauseRole, selectable, explicit_subquery=True

2054 ).lateral(name=name)

2055

2056

2057class TableSample(FromClauseAlias):

2058 """Represent a TABLESAMPLE clause.

2059

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

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

2062 method

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

2064

2065 .. seealso::

2066

2067 :func:`_expression.tablesample`

2068

2069 """

2070

2071 __visit_name__ = "tablesample"

2072

2073 _traverse_internals: _TraverseInternalsType = (

2074 AliasedReturnsRows._traverse_internals

2075 + [

2076 ("sampling", InternalTraversal.dp_clauseelement),

2077 ("seed", InternalTraversal.dp_clauseelement),

2078 ]

2079 )

2080

2081 @classmethod

2082 def _factory(

2083 cls,

2084 selectable: _FromClauseArgument,

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

2086 name: Optional[str] = None,

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

2088 ) -> TableSample:

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

2090 sampling, name=name, seed=seed

2091 )

2092

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

2094 def _init( # type: ignore[override]

2095 self,

2096 selectable: Any,

2097 *,

2098 name: Optional[str] = None,

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

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

2101 ) -> None:

2102 assert sampling is not None

2103 functions = util.preloaded.sql_functions

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

2105 sampling = functions.func.system(sampling)

2106

2107 self.sampling: Function[Any] = sampling

2108 self.seed = seed

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

2110

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

2112 return self.sampling

2113

2114

2115class CTE(

2116 roles.DMLTableRole,

2117 roles.IsCTERole,

2118 Generative,

2119 HasPrefixes,

2120 HasSuffixes,

2121 AliasedReturnsRows,

2122):

2123 """Represent a Common Table Expression.

2124

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

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

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

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

2129 :class:`_sql.Update` and

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

2131 usage details on CTEs.

2132

2133 .. seealso::

2134

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

2136

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

2138

2139 """

2140

2141 __visit_name__ = "cte"

2142

2143 _traverse_internals: _TraverseInternalsType = (

2144 AliasedReturnsRows._traverse_internals

2145 + [

2146 ("_cte_alias", InternalTraversal.dp_clauseelement),

2147 ("_restates", InternalTraversal.dp_clauseelement),

2148 ("recursive", InternalTraversal.dp_boolean),

2149 ("nesting", InternalTraversal.dp_boolean),

2150 ]

2151 + HasPrefixes._has_prefixes_traverse_internals

2152 + HasSuffixes._has_suffixes_traverse_internals

2153 )

2154

2155 element: HasCTE

2156

2157 @classmethod

2158 def _factory(

2159 cls,

2160 selectable: HasCTE,

2161 name: Optional[str] = None,

2162 recursive: bool = False,

2163 ) -> CTE:

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

2165 or Common Table Expression instance.

2166

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

2168

2169 """

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

2171 name=name, recursive=recursive

2172 )

2173

2174 def _init(

2175 self,

2176 selectable: HasCTE,

2177 *,

2178 name: Optional[str] = None,

2179 recursive: bool = False,

2180 nesting: bool = False,

2181 _cte_alias: Optional[CTE] = None,

2182 _restates: Optional[CTE] = None,

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

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

2185 ) -> None:

2186 self.recursive = recursive

2187 self.nesting = nesting

2188 self._cte_alias = _cte_alias

2189 # Keep recursivity reference with union/union_all

2190 self._restates = _restates

2191 if _prefixes:

2192 self._prefixes = _prefixes

2193 if _suffixes:

2194 self._suffixes = _suffixes

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

2196

2197 def _populate_column_collection(

2198 self,

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

2200 primary_key: ColumnSet,

2201 foreign_keys: Set[KeyedColumnElement[Any]],

2202 ) -> None:

2203 if self._cte_alias is not None:

2204 self._cte_alias._generate_fromclause_column_proxies(

2205 self,

2206 columns,

2207 primary_key=primary_key,

2208 foreign_keys=foreign_keys,

2209 )

2210 else:

2211 self.element._generate_fromclause_column_proxies(

2212 self,

2213 columns,

2214 primary_key=primary_key,

2215 foreign_keys=foreign_keys,

2216 )

2217

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

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

2220 :class:`_expression.CTE`.

2221

2222 This method is a CTE-specific specialization of the

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

2224

2225 .. seealso::

2226

2227 :ref:`tutorial_using_aliases`

2228

2229 :func:`_expression.alias`

2230

2231 """

2232 return CTE._construct(

2233 self.element,

2234 name=name,

2235 recursive=self.recursive,

2236 nesting=self.nesting,

2237 _cte_alias=self,

2238 _prefixes=self._prefixes,

2239 _suffixes=self._suffixes,

2240 )

2241

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

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

2244 of the original CTE against the given selectables provided

2245 as positional arguments.

2246

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

2248 UNION.

2249

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

2251

2252 .. seealso::

2253

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

2255

2256 """

2257 assert is_select_statement(

2258 self.element

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

2260

2261 return CTE._construct(

2262 self.element.union(*other),

2263 name=self.name,

2264 recursive=self.recursive,

2265 nesting=self.nesting,

2266 _restates=self,

2267 _prefixes=self._prefixes,

2268 _suffixes=self._suffixes,

2269 )

2270

2271 def union_all(

2272 self, *other: _SelectStatementForCompoundArgument[Any]

2273 ) -> CTE:

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

2275 of the original CTE against the given selectables provided

2276 as positional arguments.

2277

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

2279 UNION.

2280

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

2282

2283 .. seealso::

2284

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

2286

2287 """

2288

2289 assert is_select_statement(

2290 self.element

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

2292

2293 return CTE._construct(

2294 self.element.union_all(*other),

2295 name=self.name,

2296 recursive=self.recursive,

2297 nesting=self.nesting,

2298 _restates=self,

2299 _prefixes=self._prefixes,

2300 _suffixes=self._suffixes,

2301 )

2302

2303 def _get_reference_cte(self) -> CTE:

2304 """

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

2306 Updated CTEs should still refer to the original CTE.

2307 This function returns this reference identifier.

2308 """

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

2310

2311

2312class _CTEOpts(NamedTuple):

2313 nesting: bool

2314

2315

2316class _ColumnsPlusNames(NamedTuple):

2317 required_label_name: Optional[str]

2318 """

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

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

2321 """

2322

2323 proxy_key: Optional[str]

2324 """

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

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

2327 select.selected_columns collection

2328 """

2329

2330 fallback_label_name: Optional[str]

2331 """

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

2333 we have to render a label even though

2334 required_label_name was not given

2335 """

2336

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

2338 """

2339 the ColumnElement itself

2340 """

2341

2342 repeated: bool

2343 """

2344 True if this is a duplicate of a previous column

2345 in the list of columns

2346 """

2347

2348

2349class SelectsRows(ReturnsRows):

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

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

2352

2353 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2354

2355 def _generate_columns_plus_names(

2356 self,

2357 anon_for_dupe_key: bool,

2358 cols: Optional[_SelectIterable] = None,

2359 ) -> List[_ColumnsPlusNames]:

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

2361 the compiler.

2362

2363 This is distinct from the _column_naming_convention generator that's

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

2365 different rules. the collection returned here calls upon the

2366 _column_naming_convention as well.

2367

2368 """

2369

2370 if cols is None:

2371 cols = self._all_selected_columns

2372

2373 key_naming_convention = SelectState._column_naming_convention(

2374 self._label_style

2375 )

2376

2377 names = {}

2378

2379 result: List[_ColumnsPlusNames] = []

2380 result_append = result.append

2381

2382 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2383 label_style_none = self._label_style is LABEL_STYLE_NONE

2384

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

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

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

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

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

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

2391 dedupe_hash = 1

2392

2393 for c in cols:

2394 repeated = False

2395

2396 if not c._render_label_in_columns_clause:

2397 effective_name = required_label_name = fallback_label_name = (

2398 None

2399 )

2400 elif label_style_none:

2401 if TYPE_CHECKING:

2402 assert is_column_element(c)

2403

2404 effective_name = required_label_name = None

2405 fallback_label_name = c._non_anon_label or c._anon_name_label

2406 else:

2407 if TYPE_CHECKING:

2408 assert is_column_element(c)

2409

2410 if table_qualified:

2411 required_label_name = effective_name = (

2412 fallback_label_name

2413 ) = c._tq_label

2414 else:

2415 effective_name = fallback_label_name = c._non_anon_label

2416 required_label_name = None

2417

2418 if effective_name is None:

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

2420 # not need _expression_label but it isn't

2421 # giving us a clue when to use anon_label instead

2422 expr_label = c._expression_label

2423 if expr_label is None:

2424 repeated = c._anon_name_label in names

2425 names[c._anon_name_label] = c

2426 effective_name = required_label_name = None

2427

2428 if repeated:

2429 # here, "required_label_name" is sent as

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

2431 if table_qualified:

2432 fallback_label_name = (

2433 c._dedupe_anon_tq_label_idx(dedupe_hash)

2434 )

2435 dedupe_hash += 1

2436 else:

2437 fallback_label_name = c._dedupe_anon_label_idx(

2438 dedupe_hash

2439 )

2440 dedupe_hash += 1

2441 else:

2442 fallback_label_name = c._anon_name_label

2443 else:

2444 required_label_name = effective_name = (

2445 fallback_label_name

2446 ) = expr_label

2447

2448 if effective_name is not None:

2449 if TYPE_CHECKING:

2450 assert is_column_element(c)

2451

2452 if effective_name in names:

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

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

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

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

2457 # different column under the same name. apply

2458 # disambiguating label

2459 if table_qualified:

2460 required_label_name = fallback_label_name = (

2461 c._anon_tq_label

2462 )

2463 else:

2464 required_label_name = fallback_label_name = (

2465 c._anon_name_label

2466 )

2467

2468 if anon_for_dupe_key and required_label_name in names:

2469 # here, c._anon_tq_label is definitely unique to

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

2471 # this should always be true.

2472 # this is also an infrequent codepath because

2473 # you need two levels of duplication to be here

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

2475

2476 # the column under the disambiguating label is

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

2478 # subsequent occurrences of the column so that the

2479 # original stays non-ambiguous

2480 if table_qualified:

2481 required_label_name = fallback_label_name = (

2482 c._dedupe_anon_tq_label_idx(dedupe_hash)

2483 )

2484 dedupe_hash += 1

2485 else:

2486 required_label_name = fallback_label_name = (

2487 c._dedupe_anon_label_idx(dedupe_hash)

2488 )

2489 dedupe_hash += 1

2490 repeated = True

2491 else:

2492 names[required_label_name] = c

2493 elif anon_for_dupe_key:

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

2495 # label so that the original stays non-ambiguous

2496 if table_qualified:

2497 required_label_name = fallback_label_name = (

2498 c._dedupe_anon_tq_label_idx(dedupe_hash)

2499 )

2500 dedupe_hash += 1

2501 else:

2502 required_label_name = fallback_label_name = (

2503 c._dedupe_anon_label_idx(dedupe_hash)

2504 )

2505 dedupe_hash += 1

2506 repeated = True

2507 else:

2508 names[effective_name] = c

2509

2510 result_append(

2511 _ColumnsPlusNames(

2512 required_label_name,

2513 key_naming_convention(c),

2514 fallback_label_name,

2515 c,

2516 repeated,

2517 )

2518 )

2519

2520 return result

2521

2522

2523class HasCTE(roles.HasCTERole, SelectsRows):

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

2525

2526 _has_ctes_traverse_internals: _TraverseInternalsType = [

2527 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2528 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2529 ]

2530

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

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

2533

2534 name_cte_columns: bool = False

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

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

2537

2538 .. versionadded:: 2.0.42

2539

2540 """

2541

2542 @_generative

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

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

2545

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

2547 the parent statement such that they will each be unconditionally

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

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

2550

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

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

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

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

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

2556 statement.

2557

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

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

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

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

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

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

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

2565 larger statement.

2566

2567 E.g.::

2568

2569 from sqlalchemy import table, column, select

2570

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

2572

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

2574

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

2576

2577 Would render:

2578

2579 .. sourcecode:: sql

2580

2581 WITH anon_1 AS (

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

2583 )

2584 SELECT t.c1, t.c2

2585 FROM t

2586

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

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

2589 statement.

2590

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

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

2593

2594 from sqlalchemy import table, column

2595 from sqlalchemy.dialects.postgresql import insert

2596

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

2598

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

2600

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

2602 update_statement = insert_stmt.on_conflict_do_update(

2603 index_elements=[t.c.c1],

2604 set_={

2605 "c1": insert_stmt.excluded.c1,

2606 "c2": insert_stmt.excluded.c2,

2607 },

2608 ).add_cte(delete_statement_cte)

2609

2610 print(update_statement)

2611

2612 The above statement renders as:

2613

2614 .. sourcecode:: sql

2615

2616 WITH deletions AS (

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

2618 )

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

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

2621

2622 .. versionadded:: 1.4.21

2623

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

2625

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

2627

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

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

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

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

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

2633 this statement when this flag is given.

2634

2635 .. versionadded:: 2.0

2636

2637 .. seealso::

2638

2639 :paramref:`.HasCTE.cte.nesting`

2640

2641

2642 """ # noqa: E501

2643 opt = _CTEOpts(nest_here)

2644 for cte in ctes:

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

2646 self._independent_ctes += (cte,)

2647 self._independent_ctes_opts += (opt,)

2648 return self

2649

2650 def cte(

2651 self,

2652 name: Optional[str] = None,

2653 recursive: bool = False,

2654 nesting: bool = False,

2655 ) -> CTE:

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

2657 or Common Table Expression instance.

2658

2659 Common table expressions are a SQL standard whereby SELECT

2660 statements can draw upon secondary statements specified along

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

2662 Special semantics regarding UNION can also be employed to

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

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

2665

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

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

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

2669 CTE rows.

2670

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

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

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

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

2675

2676 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2678 method may be

2679 used to establish these.

2680

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

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

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

2684 compile time.

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

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

2687 conjunction with UNION ALL in order to derive rows

2688 from those already selected.

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

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

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

2692 :paramref:`.HasCTE.add_cte.nest_here`

2693 parameter may also be used to more carefully

2694 control the exact placement of a particular CTE.

2695

2696 .. versionadded:: 1.4.24

2697

2698 .. seealso::

2699

2700 :meth:`.HasCTE.add_cte`

2701

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

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

2704 as well as additional examples.

2705

2706 Example 1, non recursive::

2707

2708 from sqlalchemy import (

2709 Table,

2710 Column,

2711 String,

2712 Integer,

2713 MetaData,

2714 select,

2715 func,

2716 )

2717

2718 metadata = MetaData()

2719

2720 orders = Table(

2721 "orders",

2722 metadata,

2723 Column("region", String),

2724 Column("amount", Integer),

2725 Column("product", String),

2726 Column("quantity", Integer),

2727 )

2728

2729 regional_sales = (

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

2731 .group_by(orders.c.region)

2732 .cte("regional_sales")

2733 )

2734

2735

2736 top_regions = (

2737 select(regional_sales.c.region)

2738 .where(

2739 regional_sales.c.total_sales

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

2741 )

2742 .cte("top_regions")

2743 )

2744

2745 statement = (

2746 select(

2747 orders.c.region,

2748 orders.c.product,

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

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

2751 )

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

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

2754 )

2755

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

2757

2758 Example 2, WITH RECURSIVE::

2759

2760 from sqlalchemy import (

2761 Table,

2762 Column,

2763 String,

2764 Integer,

2765 MetaData,

2766 select,

2767 func,

2768 )

2769

2770 metadata = MetaData()

2771

2772 parts = Table(

2773 "parts",

2774 metadata,

2775 Column("part", String),

2776 Column("sub_part", String),

2777 Column("quantity", Integer),

2778 )

2779

2780 included_parts = (

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

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

2783 .cte(recursive=True)

2784 )

2785

2786

2787 incl_alias = included_parts.alias()

2788 parts_alias = parts.alias()

2789 included_parts = included_parts.union_all(

2790 select(

2791 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2793 )

2794

2795 statement = select(

2796 included_parts.c.sub_part,

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

2798 ).group_by(included_parts.c.sub_part)

2799

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

2801

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

2803

2804 from datetime import date

2805 from sqlalchemy import (

2806 MetaData,

2807 Table,

2808 Column,

2809 Integer,

2810 Date,

2811 select,

2812 literal,

2813 and_,

2814 exists,

2815 )

2816

2817 metadata = MetaData()

2818

2819 visitors = Table(

2820 "visitors",

2821 metadata,

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

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

2824 Column("count", Integer),

2825 )

2826

2827 # add 5 visitors for the product_id == 1

2828 product_id = 1

2829 day = date.today()

2830 count = 5

2831

2832 update_cte = (

2833 visitors.update()

2834 .where(

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

2836 )

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

2838 .returning(literal(1))

2839 .cte("update_cte")

2840 )

2841

2842 upsert = visitors.insert().from_select(

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

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

2845 ~exists(update_cte.select())

2846 ),

2847 )

2848

2849 connection.execute(upsert)

2850

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

2852

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

2854

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

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

2857 "value_a", nesting=True

2858 )

2859

2860 # Nesting CTEs takes ascendency locally

2861 # over the CTEs at a higher level

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

2863

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

2865

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

2867 shown with inline parameters below as:

2868

2869 .. sourcecode:: sql

2870

2871 WITH

2872 value_a AS

2873 (SELECT 'root' AS n),

2874 value_b AS

2875 (WITH value_a AS

2876 (SELECT 'nesting' AS n)

2877 SELECT value_a.n AS n FROM value_a)

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

2879 FROM value_a, value_b

2880

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

2882 as follows (SQLAlchemy 2.0 and above)::

2883

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

2885

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

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

2888

2889 # Nesting CTEs takes ascendency locally

2890 # over the CTEs at a higher level

2891 value_b = (

2892 select(value_a_nested.c.n)

2893 .add_cte(value_a_nested, nest_here=True)

2894 .cte("value_b")

2895 )

2896

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

2898

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

2900

2901 edge = Table(

2902 "edge",

2903 metadata,

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

2905 Column("left", Integer),

2906 Column("right", Integer),

2907 )

2908

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

2910

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

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

2913 )

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

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

2916 )

2917

2918 subgraph_cte = root_node.union(left_edge, right_edge)

2919

2920 subgraph = select(subgraph_cte)

2921

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

2923

2924 .. sourcecode:: sql

2925

2926 WITH RECURSIVE nodes(node) AS (

2927 SELECT 1 AS node

2928 UNION

2929 SELECT edge."left" AS "left"

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

2931 UNION

2932 SELECT edge."right" AS "right"

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

2934 )

2935 SELECT nodes.node FROM nodes

2936

2937 .. seealso::

2938

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

2940 :meth:`_expression.HasCTE.cte`.

2941

2942 """ # noqa: E501

2943 return CTE._construct(

2944 self, name=name, recursive=recursive, nesting=nesting

2945 )

2946

2947

2948class Subquery(AliasedReturnsRows):

2949 """Represent a subquery of a SELECT.

2950

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

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

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

2954 :class:`_expression.SelectBase` subclass

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

2956 :class:`_expression.CompoundSelect`, and

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

2958 it represents the

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

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

2961

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

2963 :class:`_expression.Alias`

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

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

2966 :class:`_expression.Alias` always

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

2968 :class:`.Subquery`

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

2970

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

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

2973 statement.

2974

2975 """

2976

2977 __visit_name__ = "subquery"

2978

2979 _is_subquery = True

2980

2981 inherit_cache = True

2982

2983 element: SelectBase

2984

2985 @classmethod

2986 def _factory(

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

2988 ) -> Subquery:

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

2990

2991 return coercions.expect(

2992 roles.SelectStatementRole, selectable

2993 ).subquery(name=name)

2994

2995 @util.deprecated(

2996 "1.4",

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

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

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

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

3001 ":func:`_expression.select` "

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

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

3004 )

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

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

3007

3008

3009class FromGrouping(GroupedElement, FromClause):

3010 """Represent a grouping of a FROM clause"""

3011

3012 _traverse_internals: _TraverseInternalsType = [

3013 ("element", InternalTraversal.dp_clauseelement)

3014 ]

3015

3016 element: FromClause

3017

3018 def __init__(self, element: FromClause):

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

3020

3021 @util.ro_non_memoized_property

3022 def columns(

3023 self,

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

3025 return self.element.columns

3026

3027 @util.ro_non_memoized_property

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

3029 return self.element.columns

3030

3031 @property

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

3033 return self.element.primary_key

3034

3035 @property

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

3037 return self.element.foreign_keys

3038

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

3040 return self.element.is_derived_from(fromclause)

3041

3042 def alias(

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

3044 ) -> NamedFromGrouping:

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

3046

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

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

3049

3050 @util.ro_non_memoized_property

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

3052 return self.element._hide_froms

3053

3054 @util.ro_non_memoized_property

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

3056 return self.element._from_objects

3057

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

3059 return {"element": self.element}

3060

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

3062 self.element = state["element"]

3063

3064 if TYPE_CHECKING:

3065

3066 def self_group(

3067 self, against: Optional[OperatorType] = None

3068 ) -> Self: ...

3069

3070

3071class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3073

3074 .. versionadded:: 2.0

3075

3076 """

3077

3078 inherit_cache = True

3079

3080 if TYPE_CHECKING:

3081

3082 def self_group(

3083 self, against: Optional[OperatorType] = None

3084 ) -> Self: ...

3085

3086

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

3088 """Represents a minimal "table" construct.

3089

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

3091 collection of columns, which are typically produced

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

3093

3094 from sqlalchemy import table, column

3095

3096 user = table(

3097 "user",

3098 column("id"),

3099 column("name"),

3100 column("description"),

3101 )

3102

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

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

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

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

3107

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

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

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

3111 It's useful

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

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

3114 is not on hand.

3115

3116 """

3117

3118 __visit_name__ = "table"

3119

3120 _traverse_internals: _TraverseInternalsType = [

3121 (

3122 "columns",

3123 InternalTraversal.dp_fromclause_canonical_column_collection,

3124 ),

3125 ("name", InternalTraversal.dp_string),

3126 ("schema", InternalTraversal.dp_string),

3127 ]

3128

3129 _is_table = True

3130

3131 fullname: str

3132

3133 implicit_returning = False

3134 """:class:`_expression.TableClause`

3135 doesn't support having a primary key or column

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

3137

3138 _columns: DedupeColumnCollection[ColumnClause[Any]]

3139

3140 @util.ro_memoized_property

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

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

3143 return None

3144

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

3146 super().__init__()

3147 self.name = name

3148 self._columns = DedupeColumnCollection() # type: ignore[unused-ignore]

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

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

3151 for c in columns:

3152 self.append_column(c)

3153

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

3155 if schema is not None:

3156 self.schema = schema

3157 if self.schema is not None:

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

3159 else:

3160 self.fullname = self.name

3161 if kw:

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

3163

3164 if TYPE_CHECKING:

3165

3166 @util.ro_non_memoized_property

3167 def columns(

3168 self,

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

3170

3171 @util.ro_non_memoized_property

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

3173

3174 def __str__(self) -> str:

3175 if self.schema is not None:

3176 return self.schema + "." + self.name

3177 else:

3178 return self.name

3179

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

3181 pass

3182

3183 @util.ro_memoized_property

3184 def description(self) -> str:

3185 return self.name

3186

3187 def _insert_col_impl(

3188 self,

3189 c: ColumnClause[Any],

3190 *,

3191 index: Optional[int] = None,

3192 ) -> 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 self._columns.add(c, index=index)

3200 c.table = self

3201

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

3203 self._insert_col_impl(c)

3204

3205 def insert_column(self, c: ColumnClause[Any], index: int) -> None:

3206 self._insert_col_impl(c, index=index)

3207

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

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

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

3211 :class:`_expression.TableClause`.

3212

3213 E.g.::

3214

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

3216

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

3218

3219 """

3220

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

3222

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

3224 def update(self) -> Update:

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

3226 :class:`_expression.TableClause`.

3227

3228 E.g.::

3229

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

3231

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

3233

3234 """

3235 return util.preloaded.sql_dml.Update(

3236 self,

3237 )

3238

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

3240 def delete(self) -> Delete:

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

3242 :class:`_expression.TableClause`.

3243

3244 E.g.::

3245

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

3247

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

3249

3250 """

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

3252

3253 @util.ro_non_memoized_property

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

3255 return [self]

3256

3257

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

3259

3260

3261class ForUpdateArg(ClauseElement):

3262 _traverse_internals: _TraverseInternalsType = [

3263 ("of", InternalTraversal.dp_clauseelement_list),

3264 ("nowait", InternalTraversal.dp_boolean),

3265 ("read", InternalTraversal.dp_boolean),

3266 ("skip_locked", InternalTraversal.dp_boolean),

3267 ("key_share", InternalTraversal.dp_boolean),

3268 ]

3269

3270 of: Optional[Sequence[ClauseElement]]

3271 nowait: bool

3272 read: bool

3273 skip_locked: bool

3274

3275 @classmethod

3276 def _from_argument(

3277 cls, with_for_update: ForUpdateParameter

3278 ) -> Optional[ForUpdateArg]:

3279 if isinstance(with_for_update, ForUpdateArg):

3280 return with_for_update

3281 elif with_for_update in (None, False):

3282 return None

3283 elif with_for_update is True:

3284 return ForUpdateArg()

3285 else:

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

3287

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

3289 return (

3290 isinstance(other, ForUpdateArg)

3291 and other.nowait == self.nowait

3292 and other.read == self.read

3293 and other.skip_locked == self.skip_locked

3294 and other.key_share == self.key_share

3295 and other.of is self.of

3296 )

3297

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

3299 return not self.__eq__(other)

3300

3301 def __hash__(self) -> int:

3302 return id(self)

3303

3304 def __init__(

3305 self,

3306 *,

3307 nowait: bool = False,

3308 read: bool = False,

3309 of: Optional[_ForUpdateOfArgument] = None,

3310 skip_locked: bool = False,

3311 key_share: bool = False,

3312 ):

3313 """Represents arguments specified to

3314 :meth:`_expression.Select.for_update`.

3315

3316 """

3317

3318 self.nowait = nowait

3319 self.read = read

3320 self.skip_locked = skip_locked

3321 self.key_share = key_share

3322 if of is not None:

3323 self.of = [

3324 coercions.expect(roles.ColumnsClauseRole, elem)

3325 for elem in util.to_list(of)

3326 ]

3327 else:

3328 self.of = None

3329

3330

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

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

3333 in a statement.

3334

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

3336 :func:`_expression.values` function.

3337

3338 .. versionadded:: 1.4

3339

3340 """

3341

3342 __visit_name__ = "values"

3343

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

3345

3346 _unnamed: bool

3347 _traverse_internals: _TraverseInternalsType = [

3348 ("_column_args", InternalTraversal.dp_clauseelement_list),

3349 ("_data", InternalTraversal.dp_dml_multi_values),

3350 ("name", InternalTraversal.dp_string),

3351 ("literal_binds", InternalTraversal.dp_boolean),

3352 ] + HasCTE._has_ctes_traverse_internals

3353

3354 name_cte_columns = True

3355

3356 def __init__(

3357 self,

3358 *columns: ColumnClause[Any],

3359 name: Optional[str] = None,

3360 literal_binds: bool = False,

3361 ):

3362 super().__init__()

3363 self._column_args = columns

3364

3365 if name is None:

3366 self._unnamed = True

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

3368 else:

3369 self._unnamed = False

3370 self.name = name

3371 self.literal_binds = literal_binds

3372 self.named_with_column = not self._unnamed

3373

3374 @property

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

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

3377

3378 @util.ro_non_memoized_property

3379 def _all_selected_columns(self) -> _SelectIterable:

3380 return self._column_args

3381

3382 @_generative

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

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

3385 construct that is a copy of this

3386 one with the given name.

3387

3388 This method is a VALUES-specific specialization of the

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

3390

3391 .. seealso::

3392

3393 :ref:`tutorial_using_aliases`

3394

3395 :func:`_expression.alias`

3396

3397 """

3398 non_none_name: str

3399

3400 if name is None:

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

3402 else:

3403 non_none_name = name

3404

3405 self.name = non_none_name

3406 self.named_with_column = True

3407 self._unnamed = False

3408 return self

3409

3410 @_generative

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

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

3413 so that

3414 it renders as LATERAL.

3415

3416 .. seealso::

3417

3418 :func:`_expression.lateral`

3419

3420 """

3421 non_none_name: str

3422

3423 if name is None:

3424 non_none_name = self.name

3425 else:

3426 non_none_name = name

3427

3428 self._is_lateral = True

3429 self.name = non_none_name

3430 self._unnamed = False

3431 return self

3432

3433 @_generative

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

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

3436 adding the given data to the data list.

3437

3438 E.g.::

3439

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

3441

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

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

3444 constructor.

3445

3446 """

3447

3448 self._data += (values,)

3449 return self

3450

3451 def scalar_values(self) -> ScalarValues:

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

3453 COLUMN element in a statement.

3454

3455 .. versionadded:: 2.0.0b4

3456

3457 """

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

3459

3460 def _populate_column_collection(

3461 self,

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

3463 primary_key: ColumnSet,

3464 foreign_keys: Set[KeyedColumnElement[Any]],

3465 ) -> None:

3466 for c in self._column_args:

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

3468 _, c = c._make_proxy(

3469 self, primary_key=primary_key, foreign_keys=foreign_keys

3470 )

3471 else:

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

3473 # no memoizations of other FROM clauses.

3474 # see test_values.py -> test_auto_proxy_select_direct_col

3475 c._reset_memoizations()

3476 columns.add(c)

3477 c.table = self

3478

3479 @util.ro_non_memoized_property

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

3481 return [self]

3482

3483

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

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

3486 COLUMN element in a statement.

3487

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

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

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

3491 an ``IN`` or ``NOT IN`` condition.

3492

3493 .. versionadded:: 2.0.0b4

3494

3495 """

3496

3497 __visit_name__ = "scalar_values"

3498

3499 _traverse_internals: _TraverseInternalsType = [

3500 ("_column_args", InternalTraversal.dp_clauseelement_list),

3501 ("_data", InternalTraversal.dp_dml_multi_values),

3502 ("literal_binds", InternalTraversal.dp_boolean),

3503 ]

3504

3505 def __init__(

3506 self,

3507 columns: Sequence[ColumnClause[Any]],

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

3509 literal_binds: bool,

3510 ):

3511 super().__init__()

3512 self._column_args = columns

3513 self._data = data

3514 self.literal_binds = literal_binds

3515

3516 @property

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

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

3519

3520 def __clause_element__(self) -> ScalarValues:

3521 return self

3522

3523 if TYPE_CHECKING:

3524

3525 def self_group(

3526 self, against: Optional[OperatorType] = None

3527 ) -> Self: ...

3528

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

3530

3531

3532class SelectBase(

3533 roles.SelectStatementRole,

3534 roles.DMLSelectRole,

3535 roles.CompoundElementRole,

3536 roles.InElementRole,

3537 HasCTE,

3538 SupportsCloneAnnotations,

3539 Selectable,

3540):

3541 """Base class for SELECT statements.

3542

3543

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

3545 :class:`_expression.CompoundSelect` and

3546 :class:`_expression.TextualSelect`.

3547

3548

3549 """

3550

3551 _is_select_base = True

3552 is_select = True

3553

3554 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3555

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

3557 self._reset_memoizations()

3558

3559 @util.ro_non_memoized_property

3560 def selected_columns(

3561 self,

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

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

3564 representing the columns that

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

3566

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

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

3569 within this collection cannot be directly nested inside another SELECT

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

3571 necessary parenthesization required by SQL.

3572

3573 .. note::

3574

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

3576 include expressions established in the columns clause using the

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

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

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

3580 construct.

3581

3582 .. seealso::

3583

3584 :attr:`_sql.Select.selected_columns`

3585

3586 .. versionadded:: 1.4

3587

3588 """

3589 raise NotImplementedError()

3590

3591 def _generate_fromclause_column_proxies(

3592 self,

3593 subquery: FromClause,

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

3595 primary_key: ColumnSet,

3596 foreign_keys: Set[KeyedColumnElement[Any]],

3597 *,

3598 proxy_compound_columns: Optional[

3599 Iterable[Sequence[ColumnElement[Any]]]

3600 ] = None,

3601 ) -> None:

3602 raise NotImplementedError()

3603

3604 @util.ro_non_memoized_property

3605 def _all_selected_columns(self) -> _SelectIterable:

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

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

3608 constructs.

3609

3610 .. versionadded:: 1.4.12

3611

3612 .. seealso::

3613

3614 :attr:`_sql.SelectBase.exported_columns`

3615

3616 """

3617 raise NotImplementedError()

3618

3619 @property

3620 def exported_columns(

3621 self,

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

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

3624 that represents the "exported"

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

3626 :class:`_sql.TextClause` constructs.

3627

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

3629 object are synonymous

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

3631

3632 .. versionadded:: 1.4

3633

3634 .. seealso::

3635

3636 :attr:`_expression.Select.exported_columns`

3637

3638 :attr:`_expression.Selectable.exported_columns`

3639

3640 :attr:`_expression.FromClause.exported_columns`

3641

3642

3643 """

3644 return self.selected_columns.as_readonly()

3645

3646 def get_label_style(self) -> SelectLabelStyle:

3647 """

3648 Retrieve the current label style.

3649

3650 Implemented by subclasses.

3651

3652 """

3653 raise NotImplementedError()

3654

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

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

3657

3658 Implemented by subclasses.

3659

3660 """

3661

3662 raise NotImplementedError()

3663

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

3665 raise NotImplementedError()

3666

3667 @util.deprecated(

3668 "1.4",

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

3670 "method is deprecated and will be "

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

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

3673 )

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

3675 return self.scalar_subquery()

3676

3677 def exists(self) -> Exists:

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

3679 which can be used as a column expression.

3680

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

3682

3683 .. seealso::

3684

3685 :func:`_sql.exists`

3686

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

3688

3689 .. versionadded:: 1.4

3690

3691 """

3692 return Exists(self)

3693

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

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

3696 used as a column expression.

3697

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

3699

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

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

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

3703 an enclosing SELECT.

3704

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

3706 subquery that can be produced using the

3707 :meth:`_expression.SelectBase.subquery`

3708 method.

3709

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

3711 :meth:`_expression.SelectBase.scalar_subquery`.

3712

3713 .. seealso::

3714

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

3716

3717 """

3718 if self._label_style is not LABEL_STYLE_NONE:

3719 self = self.set_label_style(LABEL_STYLE_NONE)

3720

3721 return ScalarSelect(self)

3722

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

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

3725 subquery with a label.

3726

3727 .. seealso::

3728

3729 :meth:`_expression.SelectBase.scalar_subquery`.

3730

3731 """

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

3733

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

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

3736

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

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

3739

3740 .. seealso::

3741

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

3743

3744 """

3745 return Lateral._factory(self, name)

3746

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

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

3749

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

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

3752 SELECT statement.

3753

3754 Given a SELECT statement such as::

3755

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

3757

3758 The above statement might look like:

3759

3760 .. sourcecode:: sql

3761

3762 SELECT table.id, table.name FROM table

3763

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

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

3766 a named sub-element::

3767

3768 subq = stmt.subquery()

3769 new_stmt = select(subq)

3770

3771 The above renders as:

3772

3773 .. sourcecode:: sql

3774

3775 SELECT anon_1.id, anon_1.name

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

3777

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

3779 is equivalent to calling

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

3781 method on a FROM object; however,

3782 as a :class:`_expression.SelectBase`

3783 object is not directly FROM object,

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

3785 method provides clearer semantics.

3786

3787 .. versionadded:: 1.4

3788

3789 """

3790

3791 return Subquery._construct(

3792 self._ensure_disambiguated_names(), name=name

3793 )

3794

3795 def _ensure_disambiguated_names(self) -> Self:

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

3797 disambiguated in some way, if possible.

3798

3799 """

3800

3801 raise NotImplementedError()

3802

3803 def alias(

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

3805 ) -> Subquery:

3806 """Return a named subquery against this

3807 :class:`_expression.SelectBase`.

3808

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

3810 :class:`_expression.FromClause`),

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

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

3813 :class:`_expression.FromClause`.

3814

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

3816 method is now

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

3818

3819 """

3820 return self.subquery(name=name)

3821

3822

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

3824

3825

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

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

3828

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

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

3831 compound selects.

3832

3833 """

3834

3835 __visit_name__ = "select_statement_grouping"

3836 _traverse_internals: _TraverseInternalsType = [

3837 ("element", InternalTraversal.dp_clauseelement)

3838 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3839

3840 _is_select_container = True

3841

3842 element: _SB

3843

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

3845 self.element = cast(

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

3847 )

3848

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

3850 new_element = self.element._ensure_disambiguated_names()

3851 if new_element is not self.element:

3852 return SelectStatementGrouping(new_element)

3853 else:

3854 return self

3855

3856 def get_label_style(self) -> SelectLabelStyle:

3857 return self.element.get_label_style()

3858

3859 def set_label_style(

3860 self, label_style: SelectLabelStyle

3861 ) -> SelectStatementGrouping[_SB]:

3862 return SelectStatementGrouping(

3863 self.element.set_label_style(label_style)

3864 )

3865

3866 @property

3867 def select_statement(self) -> _SB:

3868 return self.element

3869

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

3871 return self

3872

3873 if TYPE_CHECKING:

3874

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

3876

3877 # def _generate_columns_plus_names(

3878 # self, anon_for_dupe_key: bool

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

3880 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3881

3882 def _generate_fromclause_column_proxies(

3883 self,

3884 subquery: FromClause,

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

3886 primary_key: ColumnSet,

3887 foreign_keys: Set[KeyedColumnElement[Any]],

3888 *,

3889 proxy_compound_columns: Optional[

3890 Iterable[Sequence[ColumnElement[Any]]]

3891 ] = None,

3892 ) -> None:

3893 self.element._generate_fromclause_column_proxies(

3894 subquery,

3895 columns,

3896 proxy_compound_columns=proxy_compound_columns,

3897 primary_key=primary_key,

3898 foreign_keys=foreign_keys,

3899 )

3900

3901 @util.ro_non_memoized_property

3902 def _all_selected_columns(self) -> _SelectIterable:

3903 return self.element._all_selected_columns

3904

3905 @util.ro_non_memoized_property

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

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

3908 representing the columns that

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

3910 :class:`_sql.TextClause` constructs.

3911

3912 .. versionadded:: 1.4

3913

3914 .. seealso::

3915

3916 :attr:`_sql.Select.selected_columns`

3917

3918 """

3919 return self.element.selected_columns

3920

3921 @util.ro_non_memoized_property

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

3923 return self.element._from_objects

3924

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

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

3927 raise NotImplementedError

3928

3929

3930class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3932 added.

3933

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

3935 :class:`_expression.CompoundSelect`

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

3937 rendering can be controlled. Compare to

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

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

3940 and is also a SELECT construct,

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

3942 only wrapped as a subquery.

3943

3944 """

3945

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

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

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

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

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

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

3952 _for_update_arg: Optional[ForUpdateArg] = None

3953

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

3955 self._label_style = _label_style

3956

3957 @_generative

3958 def with_for_update(

3959 self,

3960 *,

3961 nowait: bool = False,

3962 read: bool = False,

3963 of: Optional[_ForUpdateOfArgument] = None,

3964 skip_locked: bool = False,

3965 key_share: bool = False,

3966 ) -> Self:

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

3968 :class:`_expression.GenerativeSelect`.

3969

3970 E.g.::

3971

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

3973

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

3975 render a statement like:

3976

3977 .. sourcecode:: sql

3978

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

3980

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

3982 would produce:

3983

3984 .. sourcecode:: sql

3985

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

3987

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

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

3990 provided which allow for common database-specific

3991 variants.

3992

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

3994 Database and PostgreSQL dialects.

3995

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

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

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

3999

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

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

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

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

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

4005 table or as a column depending on backend.

4006

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

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

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

4010

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

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

4013 on the PostgreSQL dialect.

4014

4015 """

4016 self._for_update_arg = ForUpdateArg(

4017 nowait=nowait,

4018 read=read,

4019 of=of,

4020 skip_locked=skip_locked,

4021 key_share=key_share,

4022 )

4023 return self

4024

4025 def get_label_style(self) -> SelectLabelStyle:

4026 """

4027 Retrieve the current label style.

4028

4029 .. versionadded:: 1.4

4030

4031 """

4032 return self._label_style

4033

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

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

4036

4037 There are three "label styles" available,

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

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

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

4041 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4042

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

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

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

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

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

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

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

4050 that the impact of this labeling is minimal.

4051

4052 The rationale for disambiguation is mostly so that all column

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

4054 collection when a subquery is created.

4055

4056 .. versionadded:: 1.4 - the

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

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

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

4060

4061 .. seealso::

4062

4063 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4064

4065 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4066

4067 :data:`_sql.LABEL_STYLE_NONE`

4068

4069 :data:`_sql.LABEL_STYLE_DEFAULT`

4070

4071 """

4072 if self._label_style is not style:

4073 self = self._generate()

4074 self._label_style = style

4075 return self

4076

4077 @property

4078 def _group_by_clause(self) -> ClauseList:

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

4080 return ClauseList._construct_raw(

4081 operators.comma_op, self._group_by_clauses

4082 )

4083

4084 @property

4085 def _order_by_clause(self) -> ClauseList:

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

4087 return ClauseList._construct_raw(

4088 operators.comma_op, self._order_by_clauses

4089 )

4090

4091 def _offset_or_limit_clause(

4092 self,

4093 element: _LimitOffsetType,

4094 name: Optional[str] = None,

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

4096 ) -> ColumnElement[Any]:

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

4098

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

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

4101

4102 """

4103 return coercions.expect(

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

4105 )

4106

4107 @overload

4108 def _offset_or_limit_clause_asint(

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

4110 ) -> NoReturn: ...

4111

4112 @overload

4113 def _offset_or_limit_clause_asint(

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

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

4116

4117 def _offset_or_limit_clause_asint(

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

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

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

4121 integer.

4122

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

4124 parameter. Otherwise, a compilation error is raised.

4125

4126 """

4127 if clause is None:

4128 return None

4129 try:

4130 value = clause._limit_offset_value

4131 except AttributeError as err:

4132 raise exc.CompileError(

4133 "This SELECT structure does not use a simple "

4134 "integer value for %s" % attrname

4135 ) from err

4136 else:

4137 return util.asint(value)

4138

4139 @property

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

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

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

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

4144 isn't currently set to an integer.

4145

4146 """

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

4148

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

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

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

4152 """

4153 return isinstance(clause, _OffsetLimitParam)

4154

4155 @property

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

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

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

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

4160 offset isn't currently set to an integer.

4161

4162 """

4163 return self._offset_or_limit_clause_asint(

4164 self._offset_clause, "offset"

4165 )

4166

4167 @property

4168 def _has_row_limiting_clause(self) -> bool:

4169 return (

4170 self._limit_clause is not None

4171 or self._offset_clause is not None

4172 or self._fetch_clause is not None

4173 )

4174

4175 @_generative

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

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

4178 applied.

4179

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

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

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

4183 functionality.

4184

4185 .. note::

4186

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

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

4189

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

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

4192

4193 .. seealso::

4194

4195 :meth:`_sql.GenerativeSelect.fetch`

4196

4197 :meth:`_sql.GenerativeSelect.offset`

4198

4199 """

4200

4201 self._fetch_clause = self._fetch_clause_options = None

4202 self._limit_clause = self._offset_or_limit_clause(limit)

4203 return self

4204

4205 @_generative

4206 def fetch(

4207 self,

4208 count: _LimitOffsetType,

4209 with_ties: bool = False,

4210 percent: bool = False,

4211 **dialect_kw: Any,

4212 ) -> Self:

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

4214 applied.

4215

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

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

4218 select. This functionality is is currently implemented for Oracle

4219 Database, PostgreSQL, MSSQL.

4220

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

4222

4223 .. note::

4224

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

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

4227

4228 .. versionadded:: 1.4

4229

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

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

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

4233 Pass ``None`` to reset it.

4234

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

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

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

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

4239

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

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

4242

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

4244 may be accepted by dialects.

4245

4246 .. versionadded:: 2.0.41

4247

4248 .. seealso::

4249

4250 :meth:`_sql.GenerativeSelect.limit`

4251

4252 :meth:`_sql.GenerativeSelect.offset`

4253

4254 """

4255 self._validate_dialect_kwargs(dialect_kw)

4256 self._limit_clause = None

4257 if count is None:

4258 self._fetch_clause = self._fetch_clause_options = None

4259 else:

4260 self._fetch_clause = self._offset_or_limit_clause(count)

4261 self._fetch_clause_options = {

4262 "with_ties": with_ties,

4263 "percent": percent,

4264 }

4265 return self

4266

4267 @_generative

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

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

4270 applied.

4271

4272

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

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

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

4276 functionality.

4277

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

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

4280

4281 .. seealso::

4282

4283 :meth:`_sql.GenerativeSelect.limit`

4284

4285 :meth:`_sql.GenerativeSelect.fetch`

4286

4287 """

4288

4289 self._offset_clause = self._offset_or_limit_clause(offset)

4290 return self

4291

4292 @_generative

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

4294 def slice(

4295 self,

4296 start: int,

4297 stop: int,

4298 ) -> Self:

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

4300

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

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

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

4304 query.

4305

4306 For example, ::

4307

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

4309

4310 renders as

4311

4312 .. sourcecode:: sql

4313

4314 SELECT users.id AS users_id,

4315 users.name AS users_name

4316 FROM users ORDER BY users.id

4317 LIMIT ? OFFSET ?

4318 (2, 1)

4319

4320 .. note::

4321

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

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

4324

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

4326 method generalized from the ORM.

4327

4328 .. seealso::

4329

4330 :meth:`_sql.GenerativeSelect.limit`

4331

4332 :meth:`_sql.GenerativeSelect.offset`

4333

4334 :meth:`_sql.GenerativeSelect.fetch`

4335

4336 """

4337 sql_util = util.preloaded.sql_util

4338 self._fetch_clause = self._fetch_clause_options = None

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

4340 self._limit_clause, self._offset_clause, start, stop

4341 )

4342 return self

4343

4344 @_generative

4345 def order_by(

4346 self,

4347 __first: Union[

4348 Literal[None, _NoArg.NO_ARG],

4349 _ColumnExpressionOrStrLabelArgument[Any],

4350 ] = _NoArg.NO_ARG,

4351 /,

4352 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4353 ) -> Self:

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

4355 criteria applied.

4356

4357 e.g.::

4358

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

4360

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

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

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

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

4365

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

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

4368

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

4370 constructs

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

4372

4373 .. seealso::

4374

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

4376

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

4378

4379 """

4380

4381 if not clauses and __first is None:

4382 self._order_by_clauses = ()

4383 elif __first is not _NoArg.NO_ARG:

4384 self._order_by_clauses += tuple(

4385 coercions.expect(

4386 roles.OrderByRole, clause, apply_propagate_attrs=self

4387 )

4388 for clause in (__first,) + clauses

4389 )

4390 return self

4391

4392 @_generative

4393 def group_by(

4394 self,

4395 __first: Union[

4396 Literal[None, _NoArg.NO_ARG],

4397 _ColumnExpressionOrStrLabelArgument[Any],

4398 ] = _NoArg.NO_ARG,

4399 /,

4400 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4401 ) -> Self:

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

4403 criterion applied.

4404

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

4406

4407 e.g.::

4408

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

4410

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

4412 constructs

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

4414

4415 .. seealso::

4416

4417 :ref:`tutorial_group_by_w_aggregates` - in the

4418 :ref:`unified_tutorial`

4419

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

4421

4422 """ # noqa: E501

4423

4424 if not clauses and __first is None:

4425 self._group_by_clauses = ()

4426 elif __first is not _NoArg.NO_ARG:

4427 self._group_by_clauses += tuple(

4428 coercions.expect(

4429 roles.GroupByRole, clause, apply_propagate_attrs=self

4430 )

4431 for clause in (__first,) + clauses

4432 )

4433 return self

4434

4435

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

4437class CompoundSelectState(CompileState):

4438 @util.memoized_property

4439 def _label_resolve_dict(

4440 self,

4441 ) -> Tuple[

4442 Dict[str, ColumnElement[Any]],

4443 Dict[str, ColumnElement[Any]],

4444 Dict[str, ColumnElement[Any]],

4445 ]:

4446 # TODO: this is hacky and slow

4447 hacky_subquery = self.statement.subquery()

4448 hacky_subquery.named_with_column = False

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

4450 return d, d, d

4451

4452

4453class _CompoundSelectKeyword(Enum):

4454 UNION = "UNION"

4455 UNION_ALL = "UNION ALL"

4456 EXCEPT = "EXCEPT"

4457 EXCEPT_ALL = "EXCEPT ALL"

4458 INTERSECT = "INTERSECT"

4459 INTERSECT_ALL = "INTERSECT ALL"

4460

4461

4462class CompoundSelect(

4463 HasCompileState, GenerativeSelect, TypedReturnsRows[Unpack[_Ts]]

4464):

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

4466 SELECT-based set operations.

4467

4468

4469 .. seealso::

4470

4471 :func:`_expression.union`

4472

4473 :func:`_expression.union_all`

4474

4475 :func:`_expression.intersect`

4476

4477 :func:`_expression.intersect_all`

4478

4479 :func:`_expression.except`

4480

4481 :func:`_expression.except_all`

4482

4483 """

4484

4485 __visit_name__ = "compound_select"

4486

4487 _traverse_internals: _TraverseInternalsType = (

4488 [

4489 ("selects", InternalTraversal.dp_clauseelement_list),

4490 ("_limit_clause", InternalTraversal.dp_clauseelement),

4491 ("_offset_clause", InternalTraversal.dp_clauseelement),

4492 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4493 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4494 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4495 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4496 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4497 ("keyword", InternalTraversal.dp_string),

4498 ]

4499 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4500 + HasCTE._has_ctes_traverse_internals

4501 + DialectKWArgs._dialect_kwargs_traverse_internals

4502 )

4503

4504 selects: List[SelectBase]

4505

4506 _is_from_container = True

4507 _auto_correlate = False

4508

4509 def __init__(

4510 self,

4511 keyword: _CompoundSelectKeyword,

4512 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],

4513 ):

4514 self.keyword = keyword

4515 self.selects = [

4516 coercions.expect(

4517 roles.CompoundElementRole, s, apply_propagate_attrs=self

4518 ).self_group(against=self)

4519 for s in selects

4520 ]

4521

4522 GenerativeSelect.__init__(self)

4523

4524 @classmethod

4525 def _create_union(

4526 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4527 ) -> CompoundSelect[Unpack[_Ts]]:

4528 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4529

4530 @classmethod

4531 def _create_union_all(

4532 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4533 ) -> CompoundSelect[Unpack[_Ts]]:

4534 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4535

4536 @classmethod

4537 def _create_except(

4538 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4539 ) -> CompoundSelect[Unpack[_Ts]]:

4540 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4541

4542 @classmethod

4543 def _create_except_all(

4544 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4545 ) -> CompoundSelect[Unpack[_Ts]]:

4546 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4547

4548 @classmethod

4549 def _create_intersect(

4550 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4551 ) -> CompoundSelect[Unpack[_Ts]]:

4552 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4553

4554 @classmethod

4555 def _create_intersect_all(

4556 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4557 ) -> CompoundSelect[Unpack[_Ts]]:

4558 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4559

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

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

4562

4563 def self_group(

4564 self, against: Optional[OperatorType] = None

4565 ) -> GroupedElement:

4566 return SelectStatementGrouping(self)

4567

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

4569 for s in self.selects:

4570 if s.is_derived_from(fromclause):

4571 return True

4572 return False

4573

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

4575 if self._label_style is not style:

4576 self = self._generate()

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

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

4579

4580 return self

4581

4582 def _ensure_disambiguated_names(self) -> Self:

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

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

4585 self = self._generate()

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

4587

4588 return self

4589

4590 def _generate_fromclause_column_proxies(

4591 self,

4592 subquery: FromClause,

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

4594 primary_key: ColumnSet,

4595 foreign_keys: Set[KeyedColumnElement[Any]],

4596 *,

4597 proxy_compound_columns: Optional[

4598 Iterable[Sequence[ColumnElement[Any]]]

4599 ] = None,

4600 ) -> None:

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

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

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

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

4605 # generate below.

4606 select_0 = self.selects[0]

4607

4608 if self._label_style is not LABEL_STYLE_DEFAULT:

4609 select_0 = select_0.set_label_style(self._label_style)

4610

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

4612 # derived columns place a 'weight' annotation corresponding

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

4614 # that the corresponding_column() operation can resolve

4615 # conflicts

4616 extra_col_iterator = zip(

4617 *[

4618 [

4619 c._annotate(dd)

4620 for c in stmt._all_selected_columns

4621 if is_column_element(c)

4622 ]

4623 for dd, stmt in [

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

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

4626 ]

4627 ]

4628 )

4629

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

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

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

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

4634 # compound

4635 select_0._generate_fromclause_column_proxies(

4636 subquery,

4637 columns,

4638 proxy_compound_columns=extra_col_iterator,

4639 primary_key=primary_key,

4640 foreign_keys=foreign_keys,

4641 )

4642

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

4644 super()._refresh_for_new_column(column)

4645 for select in self.selects:

4646 select._refresh_for_new_column(column)

4647

4648 @util.ro_non_memoized_property

4649 def _all_selected_columns(self) -> _SelectIterable:

4650 return self.selects[0]._all_selected_columns

4651

4652 @util.ro_non_memoized_property

4653 def selected_columns(

4654 self,

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

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

4657 representing the columns that

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

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

4660

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

4662 :attr:`_expression.CompoundSelect.selected_columns`

4663 attribute returns the selected

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

4665 statements within the set operation.

4666

4667 .. seealso::

4668

4669 :attr:`_sql.Select.selected_columns`

4670

4671 .. versionadded:: 1.4

4672

4673 """

4674 return self.selects[0].selected_columns

4675

4676

4677# backwards compat

4678for elem in _CompoundSelectKeyword:

4679 setattr(CompoundSelect, elem.name, elem)

4680

4681

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

4683class SelectState(util.MemoizedSlots, CompileState):

4684 __slots__ = (

4685 "from_clauses",

4686 "froms",

4687 "columns_plus_names",

4688 "_label_resolve_dict",

4689 )

4690

4691 if TYPE_CHECKING:

4692 default_select_compile_options: CacheableOptions

4693 else:

4694

4695 class default_select_compile_options(CacheableOptions):

4696 _cache_key_traversal = []

4697

4698 if TYPE_CHECKING:

4699

4700 @classmethod

4701 def get_plugin_class(

4702 cls, statement: Executable

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

4704

4705 def __init__(

4706 self,

4707 statement: Select[Unpack[TupleAny]],

4708 compiler: SQLCompiler,

4709 **kw: Any,

4710 ):

4711 self.statement = statement

4712 self.from_clauses = statement._from_obj

4713

4714 for memoized_entities in statement._memoized_select_entities:

4715 self._setup_joins(

4716 memoized_entities._setup_joins, memoized_entities._raw_columns

4717 )

4718

4719 if statement._setup_joins:

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

4721

4722 self.froms = self._get_froms(statement)

4723

4724 self.columns_plus_names = statement._generate_columns_plus_names(True)

4725

4726 @classmethod

4727 def _plugin_not_implemented(cls) -> NoReturn:

4728 raise NotImplementedError(

4729 "The default SELECT construct without plugins does not "

4730 "implement this method."

4731 )

4732

4733 @classmethod

4734 def get_column_descriptions(

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

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

4737 return [

4738 {

4739 "name": name,

4740 "type": element.type,

4741 "expr": element,

4742 }

4743 for _, name, _, element, _ in (

4744 statement._generate_columns_plus_names(False)

4745 )

4746 ]

4747

4748 @classmethod

4749 def from_statement(

4750 cls,

4751 statement: Select[Unpack[TupleAny]],

4752 from_statement: roles.ReturnsRowsRole,

4753 ) -> ExecutableReturnsRows:

4754 cls._plugin_not_implemented()

4755

4756 @classmethod

4757 def get_columns_clause_froms(

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

4759 ) -> List[FromClause]:

4760 return cls._normalize_froms(

4761 itertools.chain.from_iterable(

4762 element._from_objects for element in statement._raw_columns

4763 )

4764 )

4765

4766 @classmethod

4767 def _column_naming_convention(

4768 cls, label_style: SelectLabelStyle

4769 ) -> _LabelConventionCallable:

4770 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4771

4772 dedupe = label_style is not LABEL_STYLE_NONE

4773

4774 pa = prefix_anon_map()

4775 names = set()

4776

4777 def go(

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

4779 col_name: Optional[str] = None,

4780 ) -> Optional[str]:

4781 if is_text_clause(c):

4782 return None

4783 elif TYPE_CHECKING:

4784 assert is_column_element(c)

4785

4786 if not dedupe:

4787 name = c._proxy_key

4788 if name is None:

4789 name = "_no_label"

4790 return name

4791

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

4793

4794 if name is None:

4795 name = "_no_label"

4796 if name in names:

4797 return c._anon_label(name) % pa

4798 else:

4799 names.add(name)

4800 return name

4801

4802 elif name in names:

4803 return (

4804 c._anon_tq_key_label % pa

4805 if table_qualified

4806 else c._anon_key_label % pa

4807 )

4808 else:

4809 names.add(name)

4810 return name

4811

4812 return go

4813

4814 def _get_froms(

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

4816 ) -> List[FromClause]:

4817 ambiguous_table_name_map: _AmbiguousTableNameMap

4818 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4819

4820 return self._normalize_froms(

4821 itertools.chain(

4822 self.from_clauses,

4823 itertools.chain.from_iterable(

4824 [

4825 element._from_objects

4826 for element in statement._raw_columns

4827 ]

4828 ),

4829 itertools.chain.from_iterable(

4830 [

4831 element._from_objects

4832 for element in statement._where_criteria

4833 ]

4834 ),

4835 ),

4836 check_statement=statement,

4837 ambiguous_table_name_map=ambiguous_table_name_map,

4838 )

4839

4840 @classmethod

4841 def _normalize_froms(

4842 cls,

4843 iterable_of_froms: Iterable[FromClause],

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

4845 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4846 ) -> List[FromClause]:

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

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

4849

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

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

4852 etc.

4853

4854 """

4855 seen: Set[FromClause] = set()

4856 froms: List[FromClause] = []

4857

4858 for item in iterable_of_froms:

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

4860 raise exc.InvalidRequestError(

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

4862 )

4863

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

4865 froms.append(item)

4866 seen.update(item._cloned_set)

4867

4868 if froms:

4869 toremove = set(

4870 itertools.chain.from_iterable(

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

4872 )

4873 )

4874 if toremove:

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

4876 # using a list to maintain ordering

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

4878

4879 if ambiguous_table_name_map is not None:

4880 ambiguous_table_name_map.update(

4881 (

4882 fr.name,

4883 _anonymous_label.safe_construct(

4884 hash(fr.name), fr.name

4885 ),

4886 )

4887 for item in froms

4888 for fr in item._from_objects

4889 if is_table(fr)

4890 and fr.schema

4891 and fr.name not in ambiguous_table_name_map

4892 )

4893

4894 return froms

4895

4896 def _get_display_froms(

4897 self,

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

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

4900 ) -> List[FromClause]:

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

4902

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

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

4905 may want to leave those absent if it is automatically

4906 correlating.

4907

4908 """

4909

4910 froms = self.froms

4911

4912 if self.statement._correlate:

4913 to_correlate = self.statement._correlate

4914 if to_correlate:

4915 froms = [

4916 f

4917 for f in froms

4918 if f

4919 not in _cloned_intersection(

4920 _cloned_intersection(

4921 froms, explicit_correlate_froms or ()

4922 ),

4923 to_correlate,

4924 )

4925 ]

4926

4927 if self.statement._correlate_except is not None:

4928 froms = [

4929 f

4930 for f in froms

4931 if f

4932 not in _cloned_difference(

4933 _cloned_intersection(

4934 froms, explicit_correlate_froms or ()

4935 ),

4936 self.statement._correlate_except,

4937 )

4938 ]

4939

4940 if (

4941 self.statement._auto_correlate

4942 and implicit_correlate_froms

4943 and len(froms) > 1

4944 ):

4945 froms = [

4946 f

4947 for f in froms

4948 if f

4949 not in _cloned_intersection(froms, implicit_correlate_froms)

4950 ]

4951

4952 if not len(froms):

4953 raise exc.InvalidRequestError(

4954 "Select statement '%r"

4955 "' returned no FROM clauses "

4956 "due to auto-correlation; "

4957 "specify correlate(<tables>) "

4958 "to control correlation "

4959 "manually." % self.statement

4960 )

4961

4962 return froms

4963

4964 def _memoized_attr__label_resolve_dict(

4965 self,

4966 ) -> Tuple[

4967 Dict[str, ColumnElement[Any]],

4968 Dict[str, ColumnElement[Any]],

4969 Dict[str, ColumnElement[Any]],

4970 ]:

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

4972 c._tq_label or c.key: c

4973 for c in self.statement._all_selected_columns

4974 if c._allow_label_resolve

4975 }

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

4977 c.key: c # type: ignore

4978 for c in _select_iterables(self.froms)

4979 if c._allow_label_resolve

4980 }

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

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

4983 with_cols.setdefault(key, value)

4984

4985 return with_cols, only_froms, only_cols

4986

4987 @classmethod

4988 def determine_last_joined_entity(

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

4990 ) -> Optional[_JoinTargetElement]:

4991 if stmt._setup_joins:

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

4993 else:

4994 return None

4995

4996 @classmethod

4997 def all_selected_columns(

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

4999 ) -> _SelectIterable:

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

5001

5002 def _setup_joins(

5003 self,

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

5005 raw_columns: List[_ColumnsClauseElement],

5006 ) -> None:

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

5008 if TYPE_CHECKING:

5009 if onclause is not None:

5010 assert isinstance(onclause, ColumnElement)

5011

5012 isouter = flags["isouter"]

5013 full = flags["full"]

5014

5015 if left is None:

5016 (

5017 left,

5018 replace_from_obj_index,

5019 ) = self._join_determine_implicit_left_side(

5020 raw_columns, left, right, onclause

5021 )

5022 else:

5023 (replace_from_obj_index) = self._join_place_explicit_left_side(

5024 left

5025 )

5026

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

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

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

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

5031 # used instead.

5032 if TYPE_CHECKING:

5033 assert isinstance(right, FromClause)

5034 if onclause is not None:

5035 assert isinstance(onclause, ColumnElement)

5036

5037 if replace_from_obj_index is not None:

5038 # splice into an existing element in the

5039 # self._from_obj list

5040 left_clause = self.from_clauses[replace_from_obj_index]

5041

5042 self.from_clauses = (

5043 self.from_clauses[:replace_from_obj_index]

5044 + (

5045 Join(

5046 left_clause,

5047 right,

5048 onclause,

5049 isouter=isouter,

5050 full=full,

5051 ),

5052 )

5053 + self.from_clauses[replace_from_obj_index + 1 :]

5054 )

5055 else:

5056 assert left is not None

5057 self.from_clauses = self.from_clauses + (

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

5059 )

5060

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

5062 def _join_determine_implicit_left_side(

5063 self,

5064 raw_columns: List[_ColumnsClauseElement],

5065 left: Optional[FromClause],

5066 right: _JoinTargetElement,

5067 onclause: Optional[ColumnElement[Any]],

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

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

5070 determine if an existing FROM or entity in this query

5071 can serve as the left hand side.

5072

5073 """

5074

5075 sql_util = util.preloaded.sql_util

5076

5077 replace_from_obj_index: Optional[int] = None

5078

5079 from_clauses = self.from_clauses

5080

5081 if from_clauses:

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

5083 from_clauses, right, onclause

5084 )

5085

5086 if len(indexes) == 1:

5087 replace_from_obj_index = indexes[0]

5088 left = from_clauses[replace_from_obj_index]

5089 else:

5090 potential = {}

5091 statement = self.statement

5092

5093 for from_clause in itertools.chain(

5094 itertools.chain.from_iterable(

5095 [element._from_objects for element in raw_columns]

5096 ),

5097 itertools.chain.from_iterable(

5098 [

5099 element._from_objects

5100 for element in statement._where_criteria

5101 ]

5102 ),

5103 ):

5104 potential[from_clause] = ()

5105

5106 all_clauses = list(potential.keys())

5107 indexes = sql_util.find_left_clause_to_join_from(

5108 all_clauses, right, onclause

5109 )

5110

5111 if len(indexes) == 1:

5112 left = all_clauses[indexes[0]]

5113

5114 if len(indexes) > 1:

5115 raise exc.InvalidRequestError(

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

5117 "from, there are multiple FROMS which can "

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

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

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

5121 "help resolve the ambiguity."

5122 )

5123 elif not indexes:

5124 raise exc.InvalidRequestError(

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

5126 "Please use the .select_from() "

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

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

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

5130 )

5131 return left, replace_from_obj_index

5132

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

5134 def _join_place_explicit_left_side(

5135 self, left: FromClause

5136 ) -> Optional[int]:

5137 replace_from_obj_index: Optional[int] = None

5138

5139 sql_util = util.preloaded.sql_util

5140

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

5142

5143 if from_clauses:

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

5145 self.from_clauses, left

5146 )

5147 else:

5148 indexes = []

5149

5150 if len(indexes) > 1:

5151 raise exc.InvalidRequestError(

5152 "Can't identify which entity in which to assign the "

5153 "left side of this join. Please use a more specific "

5154 "ON clause."

5155 )

5156

5157 # have an index, means the left side is already present in

5158 # an existing FROM in the self._from_obj tuple

5159 if indexes:

5160 replace_from_obj_index = indexes[0]

5161

5162 # no index, means we need to add a new element to the

5163 # self._from_obj tuple

5164

5165 return replace_from_obj_index

5166

5167

5168class _SelectFromElements:

5169 __slots__ = ()

5170

5171 _raw_columns: List[_ColumnsClauseElement]

5172 _where_criteria: Tuple[ColumnElement[Any], ...]

5173 _from_obj: Tuple[FromClause, ...]

5174

5175 def _iterate_from_elements(self) -> Iterator[FromClause]:

5176 # note this does not include elements

5177 # in _setup_joins

5178

5179 seen = set()

5180 for element in self._raw_columns:

5181 for fr in element._from_objects:

5182 if fr in seen:

5183 continue

5184 seen.add(fr)

5185 yield fr

5186 for element in self._where_criteria:

5187 for fr in element._from_objects:

5188 if fr in seen:

5189 continue

5190 seen.add(fr)

5191 yield fr

5192 for element in self._from_obj:

5193 if element in seen:

5194 continue

5195 seen.add(element)

5196 yield element

5197

5198

5199class _MemoizedSelectEntities(

5200 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5201):

5202 """represents partial state from a Select object, for the case

5203 where Select.columns() has redefined the set of columns/entities the

5204 statement will be SELECTing from. This object represents

5205 the entities from the SELECT before that transformation was applied,

5206 so that transformations that were made in terms of the SELECT at that

5207 time, such as join() as well as options(), can access the correct context.

5208

5209 In previous SQLAlchemy versions, this wasn't needed because these

5210 constructs calculated everything up front, like when you called join()

5211 or options(), it did everything to figure out how that would translate

5212 into specific SQL constructs that would be ready to send directly to the

5213 SQL compiler when needed. But as of

5214 1.4, all of that stuff is done in the compilation phase, during the

5215 "compile state" portion of the process, so that the work can all be

5216 cached. So it needs to be able to resolve joins/options2 based on what

5217 the list of entities was when those methods were called.

5218

5219

5220 """

5221

5222 __visit_name__ = "memoized_select_entities"

5223

5224 _traverse_internals: _TraverseInternalsType = [

5225 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5226 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5227 ("_with_options", InternalTraversal.dp_executable_options),

5228 ]

5229

5230 _is_clone_of: Optional[ClauseElement]

5231 _raw_columns: List[_ColumnsClauseElement]

5232 _setup_joins: Tuple[_SetupJoinsElement, ...]

5233 _with_options: Tuple[ExecutableOption, ...]

5234

5235 _annotations = util.EMPTY_DICT

5236

5237 def _clone(self, **kw: Any) -> Self:

5238 c = self.__class__.__new__(self.__class__)

5239 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5240

5241 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5242 return c

5243

5244 @classmethod

5245 def _generate_for_statement(

5246 cls, select_stmt: Select[Unpack[TupleAny]]

5247 ) -> None:

5248 if select_stmt._setup_joins or select_stmt._with_options:

5249 self = _MemoizedSelectEntities()

5250 self._raw_columns = select_stmt._raw_columns

5251 self._setup_joins = select_stmt._setup_joins

5252 self._with_options = select_stmt._with_options

5253

5254 select_stmt._memoized_select_entities += (self,)

5255 select_stmt._raw_columns = []

5256 select_stmt._setup_joins = select_stmt._with_options = ()

5257

5258

5259class Select(

5260 HasPrefixes,

5261 HasSuffixes,

5262 HasHints,

5263 HasCompileState,

5264 HasSyntaxExtensions[

5265 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5266 ],

5267 _SelectFromElements,

5268 GenerativeSelect,

5269 TypedReturnsRows[Unpack[_Ts]],

5270):

5271 """Represents a ``SELECT`` statement.

5272

5273 The :class:`_sql.Select` object is normally constructed using the

5274 :func:`_sql.select` function. See that function for details.

5275

5276 Available extension points:

5277

5278 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5279 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5280 keyword (if any) and the list of columns.

5281 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5282 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5283

5284 .. seealso::

5285

5286 :func:`_sql.select`

5287

5288 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5289

5290 """

5291

5292 __visit_name__ = "select"

5293

5294 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5295 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5296

5297 _raw_columns: List[_ColumnsClauseElement]

5298

5299 _distinct: bool = False

5300 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5301 _correlate: Tuple[FromClause, ...] = ()

5302 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5303 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5304 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5305 _from_obj: Tuple[FromClause, ...] = ()

5306

5307 _position_map = util.immutabledict(

5308 {

5309 "post_select": "_post_select_clause",

5310 "pre_columns": "_pre_columns_clause",

5311 "post_criteria": "_post_criteria_clause",

5312 "post_body": "_post_body_clause",

5313 }

5314 )

5315

5316 _post_select_clause: Optional[ClauseElement] = None

5317 """extension point for a ClauseElement that will be compiled directly

5318 after the SELECT keyword.

5319

5320 .. versionadded:: 2.1

5321

5322 """

5323

5324 _pre_columns_clause: Optional[ClauseElement] = None

5325 """extension point for a ClauseElement that will be compiled directly

5326 before the "columns" clause; after DISTINCT (if present).

5327

5328 .. versionadded:: 2.1

5329

5330 """

5331

5332 _post_criteria_clause: Optional[ClauseElement] = None

5333 """extension point for a ClauseElement that will be compiled directly

5334 after "criteria", following the HAVING clause but before ORDER BY.

5335

5336 .. versionadded:: 2.1

5337

5338 """

5339

5340 _post_body_clause: Optional[ClauseElement] = None

5341 """extension point for a ClauseElement that will be compiled directly

5342 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5343 of the SELECT.

5344

5345 .. versionadded:: 2.1

5346

5347 """

5348

5349 _auto_correlate = True

5350 _is_select_statement = True

5351 _compile_options: CacheableOptions = (

5352 SelectState.default_select_compile_options

5353 )

5354

5355 _traverse_internals: _TraverseInternalsType = (

5356 [

5357 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5358 (

5359 "_memoized_select_entities",

5360 InternalTraversal.dp_memoized_select_entities,

5361 ),

5362 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5363 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5364 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5365 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5366 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5367 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5368 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5369 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5370 ("_limit_clause", InternalTraversal.dp_clauseelement),

5371 ("_offset_clause", InternalTraversal.dp_clauseelement),

5372 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5373 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5374 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5375 ("_distinct", InternalTraversal.dp_boolean),

5376 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5377 ("_label_style", InternalTraversal.dp_plain_obj),

5378 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5379 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5380 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5381 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5382 ]

5383 + HasCTE._has_ctes_traverse_internals

5384 + HasPrefixes._has_prefixes_traverse_internals

5385 + HasSuffixes._has_suffixes_traverse_internals

5386 + HasHints._has_hints_traverse_internals

5387 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5388 + Executable._executable_traverse_internals

5389 + DialectKWArgs._dialect_kwargs_traverse_internals

5390 )

5391

5392 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5393 ("_compile_options", InternalTraversal.dp_has_cache_key)

5394 ]

5395

5396 _compile_state_factory: Type[SelectState]

5397

5398 @classmethod

5399 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5400 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5401

5402 Used internally to build up :class:`.Select` constructs with

5403 pre-established state.

5404

5405 """

5406

5407 stmt = Select.__new__(Select)

5408 stmt.__dict__.update(kw)

5409 return stmt

5410

5411 def __init__(

5412 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5413 ):

5414 r"""Construct a new :class:`_expression.Select`.

5415

5416 The public constructor for :class:`_expression.Select` is the

5417 :func:`_sql.select` function.

5418

5419 """

5420 self._raw_columns = [

5421 coercions.expect(

5422 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5423 )

5424 for ent in entities

5425 ]

5426 GenerativeSelect.__init__(self)

5427

5428 def _apply_syntax_extension_to_self(

5429 self, extension: SyntaxExtension

5430 ) -> None:

5431 extension.apply_to_select(self)

5432

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

5434 if not self._raw_columns:

5435 return NULLTYPE

5436 elem = self._raw_columns[0]

5437 cols = list(elem._select_iterable)

5438 return cols[0].type

5439

5440 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5441 """A synonym for the :meth:`_sql.Select.where` method."""

5442

5443 return self.where(*criteria)

5444

5445 def _filter_by_zero(

5446 self,

5447 ) -> Union[

5448 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5449 ]:

5450 if self._setup_joins:

5451 meth = SelectState.get_plugin_class(

5452 self

5453 ).determine_last_joined_entity

5454 _last_joined_entity = meth(self)

5455 if _last_joined_entity is not None:

5456 return _last_joined_entity

5457

5458 if self._from_obj:

5459 return self._from_obj[0]

5460

5461 return self._raw_columns[0]

5462

5463 if TYPE_CHECKING:

5464

5465 @overload

5466 def scalar_subquery(

5467 self: Select[_MAYBE_ENTITY],

5468 ) -> ScalarSelect[Any]: ...

5469

5470 @overload

5471 def scalar_subquery(

5472 self: Select[_NOT_ENTITY],

5473 ) -> ScalarSelect[_NOT_ENTITY]: ...

5474

5475 @overload

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

5477

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

5479

5480 def filter_by(self, **kwargs: Any) -> Self:

5481 r"""apply the given filtering criterion as a WHERE clause

5482 to this select.

5483

5484 """

5485 from_entity = self._filter_by_zero()

5486

5487 clauses = [

5488 _entity_namespace_key(from_entity, key) == value

5489 for key, value in kwargs.items()

5490 ]

5491 return self.filter(*clauses)

5492

5493 @property

5494 def column_descriptions(self) -> Any:

5495 """Return a :term:`plugin-enabled` 'column descriptions' structure

5496 referring to the columns which are SELECTed by this statement.

5497

5498 This attribute is generally useful when using the ORM, as an

5499 extended structure which includes information about mapped

5500 entities is returned. The section :ref:`queryguide_inspection`

5501 contains more background.

5502

5503 For a Core-only statement, the structure returned by this accessor

5504 is derived from the same objects that are returned by the

5505 :attr:`.Select.selected_columns` accessor, formatted as a list of

5506 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5507 which indicate the column expressions to be selected::

5508

5509 >>> stmt = select(user_table)

5510 >>> stmt.column_descriptions

5511 [

5512 {

5513 'name': 'id',

5514 'type': Integer(),

5515 'expr': Column('id', Integer(), ...)},

5516 {

5517 'name': 'name',

5518 'type': String(length=30),

5519 'expr': Column('name', String(length=30), ...)}

5520 ]

5521

5522 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5523 attribute returns a structure for a Core-only set of entities,

5524 not just ORM-only entities.

5525

5526 .. seealso::

5527

5528 :attr:`.UpdateBase.entity_description` - entity information for

5529 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5530

5531 :ref:`queryguide_inspection` - ORM background

5532

5533 """

5534 meth = SelectState.get_plugin_class(self).get_column_descriptions

5535 return meth(self)

5536

5537 def from_statement(

5538 self, statement: roles.ReturnsRowsRole

5539 ) -> ExecutableReturnsRows:

5540 """Apply the columns which this :class:`.Select` would select

5541 onto another statement.

5542

5543 This operation is :term:`plugin-specific` and will raise a not

5544 supported exception if this :class:`_sql.Select` does not select from

5545 plugin-enabled entities.

5546

5547

5548 The statement is typically either a :func:`_expression.text` or

5549 :func:`_expression.select` construct, and should return the set of

5550 columns appropriate to the entities represented by this

5551 :class:`.Select`.

5552

5553 .. seealso::

5554

5555 :ref:`orm_queryguide_selecting_text` - usage examples in the

5556 ORM Querying Guide

5557

5558 """

5559 meth = SelectState.get_plugin_class(self).from_statement

5560 return meth(self, statement)

5561

5562 @_generative

5563 def join(

5564 self,

5565 target: _JoinTargetArgument,

5566 onclause: Optional[_OnClauseArgument] = None,

5567 *,

5568 isouter: bool = False,

5569 full: bool = False,

5570 ) -> Self:

5571 r"""Create a SQL JOIN against this :class:`_expression.Select`

5572 object's criterion

5573 and apply generatively, returning the newly resulting

5574 :class:`_expression.Select`.

5575

5576 E.g.::

5577

5578 stmt = select(user_table).join(

5579 address_table, user_table.c.id == address_table.c.user_id

5580 )

5581

5582 The above statement generates SQL similar to:

5583

5584 .. sourcecode:: sql

5585

5586 SELECT user.id, user.name

5587 FROM user

5588 JOIN address ON user.id = address.user_id

5589

5590 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5591 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5592 source that is within the FROM clause of the existing SELECT,

5593 and a given target :class:`_sql.FromClause`, and then adds

5594 this :class:`_sql.Join` to the FROM clause of the newly generated

5595 SELECT statement. This is completely reworked from the behavior

5596 in 1.3, which would instead create a subquery of the entire

5597 :class:`_expression.Select` and then join that subquery to the

5598 target.

5599

5600 This is a **backwards incompatible change** as the previous behavior

5601 was mostly useless, producing an unnamed subquery rejected by

5602 most databases in any case. The new behavior is modeled after

5603 that of the very successful :meth:`_orm.Query.join` method in the

5604 ORM, in order to support the functionality of :class:`_orm.Query`

5605 being available by using a :class:`_sql.Select` object with an

5606 :class:`_orm.Session`.

5607

5608 See the notes for this change at :ref:`change_select_join`.

5609

5610

5611 :param target: target table to join towards

5612

5613 :param onclause: ON clause of the join. If omitted, an ON clause

5614 is generated automatically based on the :class:`_schema.ForeignKey`

5615 linkages between the two tables, if one can be unambiguously

5616 determined, otherwise an error is raised.

5617

5618 :param isouter: if True, generate LEFT OUTER join. Same as

5619 :meth:`_expression.Select.outerjoin`.

5620

5621 :param full: if True, generate FULL OUTER join.

5622

5623 .. seealso::

5624

5625 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5626

5627 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5628

5629 :meth:`_expression.Select.join_from`

5630

5631 :meth:`_expression.Select.outerjoin`

5632

5633 """ # noqa: E501

5634 join_target = coercions.expect(

5635 roles.JoinTargetRole, target, apply_propagate_attrs=self

5636 )

5637 if onclause is not None:

5638 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5639 else:

5640 onclause_element = None

5641

5642 self._setup_joins += (

5643 (

5644 join_target,

5645 onclause_element,

5646 None,

5647 {"isouter": isouter, "full": full},

5648 ),

5649 )

5650 return self

5651

5652 def outerjoin_from(

5653 self,

5654 from_: _FromClauseArgument,

5655 target: _JoinTargetArgument,

5656 onclause: Optional[_OnClauseArgument] = None,

5657 *,

5658 full: bool = False,

5659 ) -> Self:

5660 r"""Create a SQL LEFT OUTER JOIN against this

5661 :class:`_expression.Select` object's criterion and apply generatively,

5662 returning the newly resulting :class:`_expression.Select`.

5663

5664 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5665

5666 """

5667 return self.join_from(

5668 from_, target, onclause=onclause, isouter=True, full=full

5669 )

5670

5671 @_generative

5672 def join_from(

5673 self,

5674 from_: _FromClauseArgument,

5675 target: _JoinTargetArgument,

5676 onclause: Optional[_OnClauseArgument] = None,

5677 *,

5678 isouter: bool = False,

5679 full: bool = False,

5680 ) -> Self:

5681 r"""Create a SQL JOIN against this :class:`_expression.Select`

5682 object's criterion

5683 and apply generatively, returning the newly resulting

5684 :class:`_expression.Select`.

5685

5686 E.g.::

5687

5688 stmt = select(user_table, address_table).join_from(

5689 user_table, address_table, user_table.c.id == address_table.c.user_id

5690 )

5691

5692 The above statement generates SQL similar to:

5693

5694 .. sourcecode:: sql

5695

5696 SELECT user.id, user.name, address.id, address.email, address.user_id

5697 FROM user JOIN address ON user.id = address.user_id

5698

5699 .. versionadded:: 1.4

5700

5701 :param from\_: the left side of the join, will be rendered in the

5702 FROM clause and is roughly equivalent to using the

5703 :meth:`.Select.select_from` method.

5704

5705 :param target: target table to join towards

5706

5707 :param onclause: ON clause of the join.

5708

5709 :param isouter: if True, generate LEFT OUTER join. Same as

5710 :meth:`_expression.Select.outerjoin`.

5711

5712 :param full: if True, generate FULL OUTER join.

5713

5714 .. seealso::

5715

5716 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5717

5718 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5719

5720 :meth:`_expression.Select.join`

5721

5722 """ # noqa: E501

5723

5724 # note the order of parsing from vs. target is important here, as we

5725 # are also deriving the source of the plugin (i.e. the subject mapper

5726 # in an ORM query) which should favor the "from_" over the "target"

5727

5728 from_ = coercions.expect(

5729 roles.FromClauseRole, from_, apply_propagate_attrs=self

5730 )

5731 join_target = coercions.expect(

5732 roles.JoinTargetRole, target, apply_propagate_attrs=self

5733 )

5734 if onclause is not None:

5735 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5736 else:

5737 onclause_element = None

5738

5739 self._setup_joins += (

5740 (

5741 join_target,

5742 onclause_element,

5743 from_,

5744 {"isouter": isouter, "full": full},

5745 ),

5746 )

5747 return self

5748

5749 def outerjoin(

5750 self,

5751 target: _JoinTargetArgument,

5752 onclause: Optional[_OnClauseArgument] = None,

5753 *,

5754 full: bool = False,

5755 ) -> Self:

5756 """Create a left outer join.

5757

5758 Parameters are the same as that of :meth:`_expression.Select.join`.

5759

5760 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5761 creates a :class:`_sql.Join` object between a

5762 :class:`_sql.FromClause` source that is within the FROM clause of

5763 the existing SELECT, and a given target :class:`_sql.FromClause`,

5764 and then adds this :class:`_sql.Join` to the FROM clause of the

5765 newly generated SELECT statement. This is completely reworked

5766 from the behavior in 1.3, which would instead create a subquery of

5767 the entire

5768 :class:`_expression.Select` and then join that subquery to the

5769 target.

5770

5771 This is a **backwards incompatible change** as the previous behavior

5772 was mostly useless, producing an unnamed subquery rejected by

5773 most databases in any case. The new behavior is modeled after

5774 that of the very successful :meth:`_orm.Query.join` method in the

5775 ORM, in order to support the functionality of :class:`_orm.Query`

5776 being available by using a :class:`_sql.Select` object with an

5777 :class:`_orm.Session`.

5778

5779 See the notes for this change at :ref:`change_select_join`.

5780

5781 .. seealso::

5782

5783 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5784

5785 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5786

5787 :meth:`_expression.Select.join`

5788

5789 """

5790 return self.join(target, onclause=onclause, isouter=True, full=full)

5791

5792 def get_final_froms(self) -> Sequence[FromClause]:

5793 """Compute the final displayed list of :class:`_expression.FromClause`

5794 elements.

5795

5796 This method will run through the full computation required to

5797 determine what FROM elements will be displayed in the resulting

5798 SELECT statement, including shadowing individual tables with

5799 JOIN objects, as well as full computation for ORM use cases including

5800 eager loading clauses.

5801

5802 For ORM use, this accessor returns the **post compilation**

5803 list of FROM objects; this collection will include elements such as

5804 eagerly loaded tables and joins. The objects will **not** be

5805 ORM enabled and not work as a replacement for the

5806 :meth:`_sql.Select.select_froms` collection; additionally, the

5807 method is not well performing for an ORM enabled statement as it

5808 will incur the full ORM construction process.

5809

5810 To retrieve the FROM list that's implied by the "columns" collection

5811 passed to the :class:`_sql.Select` originally, use the

5812 :attr:`_sql.Select.columns_clause_froms` accessor.

5813

5814 To select from an alternative set of columns while maintaining the

5815 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5816 pass the

5817 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5818 parameter.

5819

5820 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5821 method replaces the previous :attr:`_sql.Select.froms` accessor,

5822 which is deprecated.

5823

5824 .. seealso::

5825

5826 :attr:`_sql.Select.columns_clause_froms`

5827

5828 """

5829 compiler = self._default_compiler()

5830

5831 return self._compile_state_factory(self, compiler)._get_display_froms()

5832

5833 @property

5834 @util.deprecated(

5835 "1.4.23",

5836 "The :attr:`_expression.Select.froms` attribute is moved to "

5837 "the :meth:`_expression.Select.get_final_froms` method.",

5838 )

5839 def froms(self) -> Sequence[FromClause]:

5840 """Return the displayed list of :class:`_expression.FromClause`

5841 elements.

5842

5843

5844 """

5845 return self.get_final_froms()

5846

5847 @property

5848 def columns_clause_froms(self) -> List[FromClause]:

5849 """Return the set of :class:`_expression.FromClause` objects implied

5850 by the columns clause of this SELECT statement.

5851

5852 .. versionadded:: 1.4.23

5853

5854 .. seealso::

5855

5856 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5857 statement into account

5858

5859 :meth:`_sql.Select.with_only_columns` - makes use of this

5860 collection to set up a new FROM list

5861

5862 """

5863

5864 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5865 self

5866 )

5867

5868 @property

5869 def inner_columns(self) -> _SelectIterable:

5870 """An iterator of all :class:`_expression.ColumnElement`

5871 expressions which would

5872 be rendered into the columns clause of the resulting SELECT statement.

5873

5874 This method is legacy as of 1.4 and is superseded by the

5875 :attr:`_expression.Select.exported_columns` collection.

5876

5877 """

5878

5879 return iter(self._all_selected_columns)

5880

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

5882 if fromclause is not None and self in fromclause._cloned_set:

5883 return True

5884

5885 for f in self._iterate_from_elements():

5886 if f.is_derived_from(fromclause):

5887 return True

5888 return False

5889

5890 def _copy_internals(

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

5892 ) -> None:

5893 # Select() object has been cloned and probably adapted by the

5894 # given clone function. Apply the cloning function to internal

5895 # objects

5896

5897 # 1. keep a dictionary of the froms we've cloned, and what

5898 # they've become. This allows us to ensure the same cloned from

5899 # is used when other items such as columns are "cloned"

5900

5901 all_the_froms = set(

5902 itertools.chain(

5903 _from_objects(*self._raw_columns),

5904 _from_objects(*self._where_criteria),

5905 _from_objects(*[elem[0] for elem in self._setup_joins]),

5906 )

5907 )

5908

5909 # do a clone for the froms we've gathered. what is important here

5910 # is if any of the things we are selecting from, like tables,

5911 # were converted into Join objects. if so, these need to be

5912 # added to _from_obj explicitly, because otherwise they won't be

5913 # part of the new state, as they don't associate themselves with

5914 # their columns.

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

5916

5917 # 2. copy FROM collections, adding in joins that we've created.

5918 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5919 add_froms = (

5920 {f for f in new_froms.values() if isinstance(f, Join)}

5921 .difference(all_the_froms)

5922 .difference(existing_from_obj)

5923 )

5924

5925 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5926

5927 # 3. clone everything else, making sure we use columns

5928 # corresponding to the froms we just made.

5929 def replace(

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

5931 **kw: Any,

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

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

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

5935 return newelem

5936 return None

5937

5938 kw["replace"] = replace

5939

5940 # copy everything else. for table-ish things like correlate,

5941 # correlate_except, setup_joins, these clone normally. For

5942 # column-expression oriented things like raw_columns, where_criteria,

5943 # order by, we get this from the new froms.

5944 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5945

5946 self._reset_memoizations()

5947

5948 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5949 return itertools.chain(

5950 super().get_children(

5951 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5952 **kw,

5953 ),

5954 self._iterate_from_elements(),

5955 )

5956

5957 @_generative

5958 def add_columns(

5959 self, *entities: _ColumnsClauseArgument[Any]

5960 ) -> Select[Unpack[TupleAny]]:

5961 r"""Return a new :func:`_expression.select` construct with

5962 the given entities appended to its columns clause.

5963

5964 E.g.::

5965

5966 my_select = my_select.add_columns(table.c.new_column)

5967

5968 The original expressions in the columns clause remain in place.

5969 To replace the original expressions with new ones, see the method

5970 :meth:`_expression.Select.with_only_columns`.

5971

5972 :param \*entities: column, table, or other entity expressions to be

5973 added to the columns clause

5974

5975 .. seealso::

5976

5977 :meth:`_expression.Select.with_only_columns` - replaces existing

5978 expressions rather than appending.

5979

5980 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5981 example

5982

5983 """

5984 self._reset_memoizations()

5985

5986 self._raw_columns = self._raw_columns + [

5987 coercions.expect(

5988 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5989 )

5990 for column in entities

5991 ]

5992 return self

5993

5994 def _set_entities(

5995 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5996 ) -> None:

5997 self._raw_columns = [

5998 coercions.expect(

5999 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6000 )

6001 for ent in util.to_list(entities)

6002 ]

6003

6004 @util.deprecated(

6005 "1.4",

6006 "The :meth:`_expression.Select.column` method is deprecated and will "

6007 "be removed in a future release. Please use "

6008 ":meth:`_expression.Select.add_columns`",

6009 )

6010 def column(

6011 self, column: _ColumnsClauseArgument[Any]

6012 ) -> Select[Unpack[TupleAny]]:

6013 """Return a new :func:`_expression.select` construct with

6014 the given column expression added to its columns clause.

6015

6016 E.g.::

6017

6018 my_select = my_select.column(table.c.new_column)

6019

6020 See the documentation for

6021 :meth:`_expression.Select.with_only_columns`

6022 for guidelines on adding /replacing the columns of a

6023 :class:`_expression.Select` object.

6024

6025 """

6026 return self.add_columns(column)

6027

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

6029 def reduce_columns(

6030 self, only_synonyms: bool = True

6031 ) -> Select[Unpack[TupleAny]]:

6032 """Return a new :func:`_expression.select` construct with redundantly

6033 named, equivalently-valued columns removed from the columns clause.

6034

6035 "Redundant" here means two columns where one refers to the

6036 other either based on foreign key, or via a simple equality

6037 comparison in the WHERE clause of the statement. The primary purpose

6038 of this method is to automatically construct a select statement

6039 with all uniquely-named columns, without the need to use

6040 table-qualified labels as

6041 :meth:`_expression.Select.set_label_style`

6042 does.

6043

6044 When columns are omitted based on foreign key, the referred-to

6045 column is the one that's kept. When columns are omitted based on

6046 WHERE equivalence, the first column in the columns clause is the

6047 one that's kept.

6048

6049 :param only_synonyms: when True, limit the removal of columns

6050 to those which have the same name as the equivalent. Otherwise,

6051 all columns that are equivalent to another are removed.

6052

6053 """

6054 woc: Select[Unpack[TupleAny]]

6055 woc = self.with_only_columns(

6056 *util.preloaded.sql_util.reduce_columns(

6057 self._all_selected_columns,

6058 only_synonyms=only_synonyms,

6059 *(self._where_criteria + self._from_obj),

6060 )

6061 )

6062 return woc

6063

6064 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6065

6066 # code within this block is **programmatically,

6067 # statically generated** by tools/generate_tuple_map_overloads.py

6068

6069 @overload

6070 def with_only_columns(

6071 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6072 ) -> Select[_T0]: ...

6073

6074 @overload

6075 def with_only_columns(

6076 self,

6077 __ent0: _TCCA[_T0],

6078 __ent1: _TCCA[_T1],

6079 /,

6080 *,

6081 maintain_column_froms: bool = ...,

6082 ) -> Select[_T0, _T1]: ...

6083

6084 @overload

6085 def with_only_columns(

6086 self,

6087 __ent0: _TCCA[_T0],

6088 __ent1: _TCCA[_T1],

6089 __ent2: _TCCA[_T2],

6090 /,

6091 *,

6092 maintain_column_froms: bool = ...,

6093 ) -> Select[_T0, _T1, _T2]: ...

6094

6095 @overload

6096 def with_only_columns(

6097 self,

6098 __ent0: _TCCA[_T0],

6099 __ent1: _TCCA[_T1],

6100 __ent2: _TCCA[_T2],

6101 __ent3: _TCCA[_T3],

6102 /,

6103 *,

6104 maintain_column_froms: bool = ...,

6105 ) -> Select[_T0, _T1, _T2, _T3]: ...

6106

6107 @overload

6108 def with_only_columns(

6109 self,

6110 __ent0: _TCCA[_T0],

6111 __ent1: _TCCA[_T1],

6112 __ent2: _TCCA[_T2],

6113 __ent3: _TCCA[_T3],

6114 __ent4: _TCCA[_T4],

6115 /,

6116 *,

6117 maintain_column_froms: bool = ...,

6118 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6119

6120 @overload

6121 def with_only_columns(

6122 self,

6123 __ent0: _TCCA[_T0],

6124 __ent1: _TCCA[_T1],

6125 __ent2: _TCCA[_T2],

6126 __ent3: _TCCA[_T3],

6127 __ent4: _TCCA[_T4],

6128 __ent5: _TCCA[_T5],

6129 /,

6130 *,

6131 maintain_column_froms: bool = ...,

6132 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6133

6134 @overload

6135 def with_only_columns(

6136 self,

6137 __ent0: _TCCA[_T0],

6138 __ent1: _TCCA[_T1],

6139 __ent2: _TCCA[_T2],

6140 __ent3: _TCCA[_T3],

6141 __ent4: _TCCA[_T4],

6142 __ent5: _TCCA[_T5],

6143 __ent6: _TCCA[_T6],

6144 /,

6145 *,

6146 maintain_column_froms: bool = ...,

6147 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6148

6149 @overload

6150 def with_only_columns(

6151 self,

6152 __ent0: _TCCA[_T0],

6153 __ent1: _TCCA[_T1],

6154 __ent2: _TCCA[_T2],

6155 __ent3: _TCCA[_T3],

6156 __ent4: _TCCA[_T4],

6157 __ent5: _TCCA[_T5],

6158 __ent6: _TCCA[_T6],

6159 __ent7: _TCCA[_T7],

6160 /,

6161 *entities: _ColumnsClauseArgument[Any],

6162 maintain_column_froms: bool = ...,

6163 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6164

6165 # END OVERLOADED FUNCTIONS self.with_only_columns

6166

6167 @overload

6168 def with_only_columns(

6169 self,

6170 *entities: _ColumnsClauseArgument[Any],

6171 maintain_column_froms: bool = False,

6172 **__kw: Any,

6173 ) -> Select[Unpack[TupleAny]]: ...

6174

6175 @_generative

6176 def with_only_columns(

6177 self,

6178 *entities: _ColumnsClauseArgument[Any],

6179 maintain_column_froms: bool = False,

6180 **__kw: Any,

6181 ) -> Select[Unpack[TupleAny]]:

6182 r"""Return a new :func:`_expression.select` construct with its columns

6183 clause replaced with the given entities.

6184

6185 By default, this method is exactly equivalent to as if the original

6186 :func:`_expression.select` had been called with the given entities.

6187 E.g. a statement::

6188

6189 s = select(table1.c.a, table1.c.b)

6190 s = s.with_only_columns(table1.c.b)

6191

6192 should be exactly equivalent to::

6193

6194 s = select(table1.c.b)

6195

6196 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6197 will also dynamically alter the FROM clause of the

6198 statement if it is not explicitly stated.

6199 To maintain the existing set of FROMs including those implied by the

6200 current columns clause, add the

6201 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6202 parameter::

6203

6204 s = select(table1.c.a, table2.c.b)

6205 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6206

6207 The above parameter performs a transfer of the effective FROMs

6208 in the columns collection to the :meth:`_sql.Select.select_from`

6209 method, as though the following were invoked::

6210

6211 s = select(table1.c.a, table2.c.b)

6212 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6213

6214 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6215 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6216 collection and performs an operation equivalent to the following::

6217

6218 s = select(table1.c.a, table2.c.b)

6219 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6220

6221 :param \*entities: column expressions to be used.

6222

6223 :param maintain_column_froms: boolean parameter that will ensure the

6224 FROM list implied from the current columns clause will be transferred

6225 to the :meth:`_sql.Select.select_from` method first.

6226

6227 .. versionadded:: 1.4.23

6228

6229 """ # noqa: E501

6230

6231 if __kw:

6232 raise _no_kw()

6233

6234 # memoizations should be cleared here as of

6235 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6236 # is the case for now.

6237 self._assert_no_memoizations()

6238

6239 if maintain_column_froms:

6240 self.select_from.non_generative( # type: ignore

6241 self, *self.columns_clause_froms

6242 )

6243

6244 # then memoize the FROMs etc.

6245 _MemoizedSelectEntities._generate_for_statement(self)

6246

6247 self._raw_columns = [

6248 coercions.expect(roles.ColumnsClauseRole, c)

6249 for c in coercions._expression_collection_was_a_list(

6250 "entities", "Select.with_only_columns", entities

6251 )

6252 ]

6253 return self

6254

6255 @property

6256 def whereclause(self) -> Optional[ColumnElement[Any]]:

6257 """Return the completed WHERE clause for this

6258 :class:`_expression.Select` statement.

6259

6260 This assembles the current collection of WHERE criteria

6261 into a single :class:`_expression.BooleanClauseList` construct.

6262

6263

6264 .. versionadded:: 1.4

6265

6266 """

6267

6268 return BooleanClauseList._construct_for_whereclause(

6269 self._where_criteria

6270 )

6271

6272 _whereclause = whereclause

6273

6274 @_generative

6275 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6276 """Return a new :func:`_expression.select` construct with

6277 the given expression added to

6278 its WHERE clause, joined to the existing clause via AND, if any.

6279

6280 """

6281

6282 assert isinstance(self._where_criteria, tuple)

6283

6284 for criterion in whereclause:

6285 where_criteria: ColumnElement[Any] = coercions.expect(

6286 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6287 )

6288 self._where_criteria += (where_criteria,)

6289 return self

6290

6291 @_generative

6292 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6293 """Return a new :func:`_expression.select` construct with

6294 the given expression added to

6295 its HAVING clause, joined to the existing clause via AND, if any.

6296

6297 """

6298

6299 for criterion in having:

6300 having_criteria = coercions.expect(

6301 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6302 )

6303 self._having_criteria += (having_criteria,)

6304 return self

6305

6306 @_generative

6307 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6308 r"""Return a new :func:`_expression.select` construct which

6309 will apply DISTINCT to the SELECT statement overall.

6310

6311 E.g.::

6312

6313 from sqlalchemy import select

6314

6315 stmt = select(users_table.c.id, users_table.c.name).distinct()

6316

6317 The above would produce an statement resembling:

6318

6319 .. sourcecode:: sql

6320

6321 SELECT DISTINCT user.id, user.name FROM user

6322

6323 The method also historically accepted an ``*expr`` parameter which

6324 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6325 This is now replaced using the :func:`_postgresql.distinct_on`

6326 extension::

6327

6328 from sqlalchemy import select

6329 from sqlalchemy.dialects.postgresql import distinct_on

6330

6331 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6332

6333 Using this parameter on other backends which don't support this

6334 syntax will raise an error.

6335

6336 :param \*expr: optional column expressions. When present,

6337 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6338 construct. A deprecation warning and/or :class:`_exc.CompileError`

6339 will be raised on other backends.

6340

6341 .. deprecated:: 2.1 Passing expressions to

6342 :meth:`_sql.Select.distinct` is deprecated, use

6343 :func:`_postgresql.distinct_on` instead.

6344

6345 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6346 and will raise :class:`_exc.CompileError` in a future version.

6347

6348 .. seealso::

6349

6350 :func:`_postgresql.distinct_on`

6351

6352 :meth:`_sql.HasSyntaxExtensions.ext`

6353 """

6354 self._distinct = True

6355 if expr:

6356 warn_deprecated(

6357 "Passing expression to ``distinct`` to generate a "

6358 "DISTINCT ON clause is deprecated. Use instead the "

6359 "``postgresql.distinct_on`` function as an extension.",

6360 "2.1",

6361 )

6362 self._distinct_on = self._distinct_on + tuple(

6363 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6364 for e in expr

6365 )

6366 return self

6367

6368 @_generative

6369 def select_from(self, *froms: _FromClauseArgument) -> Self:

6370 r"""Return a new :func:`_expression.select` construct with the

6371 given FROM expression(s)

6372 merged into its list of FROM objects.

6373

6374 E.g.::

6375

6376 table1 = table("t1", column("a"))

6377 table2 = table("t2", column("b"))

6378 s = select(table1.c.a).select_from(

6379 table1.join(table2, table1.c.a == table2.c.b)

6380 )

6381

6382 The "from" list is a unique set on the identity of each element,

6383 so adding an already present :class:`_schema.Table`

6384 or other selectable

6385 will have no effect. Passing a :class:`_expression.Join` that refers

6386 to an already present :class:`_schema.Table`

6387 or other selectable will have

6388 the effect of concealing the presence of that selectable as

6389 an individual element in the rendered FROM list, instead

6390 rendering it into a JOIN clause.

6391

6392 While the typical purpose of :meth:`_expression.Select.select_from`

6393 is to

6394 replace the default, derived FROM clause with a join, it can

6395 also be called with individual table elements, multiple times

6396 if desired, in the case that the FROM clause cannot be fully

6397 derived from the columns clause::

6398

6399 select(func.count("*")).select_from(table1)

6400

6401 """

6402

6403 self._from_obj += tuple(

6404 coercions.expect(

6405 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6406 )

6407 for fromclause in froms

6408 )

6409 return self

6410

6411 @_generative

6412 def correlate(

6413 self,

6414 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6415 ) -> Self:

6416 r"""Return a new :class:`_expression.Select`

6417 which will correlate the given FROM

6418 clauses to that of an enclosing :class:`_expression.Select`.

6419

6420 Calling this method turns off the :class:`_expression.Select` object's

6421 default behavior of "auto-correlation". Normally, FROM elements

6422 which appear in a :class:`_expression.Select`

6423 that encloses this one via

6424 its :term:`WHERE clause`, ORDER BY, HAVING or

6425 :term:`columns clause` will be omitted from this

6426 :class:`_expression.Select`

6427 object's :term:`FROM clause`.

6428 Setting an explicit correlation collection using the

6429 :meth:`_expression.Select.correlate`

6430 method provides a fixed list of FROM objects

6431 that can potentially take place in this process.

6432

6433 When :meth:`_expression.Select.correlate`

6434 is used to apply specific FROM clauses

6435 for correlation, the FROM elements become candidates for

6436 correlation regardless of how deeply nested this

6437 :class:`_expression.Select`

6438 object is, relative to an enclosing :class:`_expression.Select`

6439 which refers to

6440 the same FROM object. This is in contrast to the behavior of

6441 "auto-correlation" which only correlates to an immediate enclosing

6442 :class:`_expression.Select`.

6443 Multi-level correlation ensures that the link

6444 between enclosed and enclosing :class:`_expression.Select`

6445 is always via

6446 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6447 correlation to take place.

6448

6449 If ``None`` is passed, the :class:`_expression.Select`

6450 object will correlate

6451 none of its FROM entries, and all will render unconditionally

6452 in the local FROM clause.

6453

6454 :param \*fromclauses: one or more :class:`.FromClause` or other

6455 FROM-compatible construct such as an ORM mapped entity to become part

6456 of the correlate collection; alternatively pass a single value

6457 ``None`` to remove all existing correlations.

6458

6459 .. seealso::

6460

6461 :meth:`_expression.Select.correlate_except`

6462

6463 :ref:`tutorial_scalar_subquery`

6464

6465 """

6466

6467 # tests failing when we try to change how these

6468 # arguments are passed

6469

6470 self._auto_correlate = False

6471 if not fromclauses or fromclauses[0] in {None, False}:

6472 if len(fromclauses) > 1:

6473 raise exc.ArgumentError(

6474 "additional FROM objects not accepted when "

6475 "passing None/False to correlate()"

6476 )

6477 self._correlate = ()

6478 else:

6479 self._correlate = self._correlate + tuple(

6480 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6481 )

6482 return self

6483

6484 @_generative

6485 def correlate_except(

6486 self,

6487 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6488 ) -> Self:

6489 r"""Return a new :class:`_expression.Select`

6490 which will omit the given FROM

6491 clauses from the auto-correlation process.

6492

6493 Calling :meth:`_expression.Select.correlate_except` turns off the

6494 :class:`_expression.Select` object's default behavior of

6495 "auto-correlation" for the given FROM elements. An element

6496 specified here will unconditionally appear in the FROM list, while

6497 all other FROM elements remain subject to normal auto-correlation

6498 behaviors.

6499

6500 If ``None`` is passed, or no arguments are passed,

6501 the :class:`_expression.Select` object will correlate all of its

6502 FROM entries.

6503

6504 :param \*fromclauses: a list of one or more

6505 :class:`_expression.FromClause`

6506 constructs, or other compatible constructs (i.e. ORM-mapped

6507 classes) to become part of the correlate-exception collection.

6508

6509 .. seealso::

6510

6511 :meth:`_expression.Select.correlate`

6512

6513 :ref:`tutorial_scalar_subquery`

6514

6515 """

6516

6517 self._auto_correlate = False

6518 if not fromclauses or fromclauses[0] in {None, False}:

6519 if len(fromclauses) > 1:

6520 raise exc.ArgumentError(

6521 "additional FROM objects not accepted when "

6522 "passing None/False to correlate_except()"

6523 )

6524 self._correlate_except = ()

6525 else:

6526 self._correlate_except = (self._correlate_except or ()) + tuple(

6527 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6528 )

6529

6530 return self

6531

6532 @HasMemoized_ro_memoized_attribute

6533 def selected_columns(

6534 self,

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

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

6537 representing the columns that

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

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

6540

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

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

6543 within this collection cannot be directly nested inside another SELECT

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

6545 necessary parenthesization required by SQL.

6546

6547 For a :func:`_expression.select` construct, the collection here is

6548 exactly what would be rendered inside the "SELECT" statement, and the

6549 :class:`_expression.ColumnElement` objects are directly present as they

6550 were given, e.g.::

6551

6552 col1 = column("q", Integer)

6553 col2 = column("p", Integer)

6554 stmt = select(col1, col2)

6555

6556 Above, ``stmt.selected_columns`` would be a collection that contains

6557 the ``col1`` and ``col2`` objects directly. For a statement that is

6558 against a :class:`_schema.Table` or other

6559 :class:`_expression.FromClause`, the collection will use the

6560 :class:`_expression.ColumnElement` objects that are in the

6561 :attr:`_expression.FromClause.c` collection of the from element.

6562

6563 A use case for the :attr:`_sql.Select.selected_columns` collection is

6564 to allow the existing columns to be referenced when adding additional

6565 criteria, e.g.::

6566

6567 def filter_on_id(my_select, id):

6568 return my_select.where(my_select.selected_columns["id"] == id)

6569

6570

6571 stmt = select(MyModel)

6572

6573 # adds "WHERE id=:param" to the statement

6574 stmt = filter_on_id(stmt, 42)

6575

6576 .. note::

6577

6578 The :attr:`_sql.Select.selected_columns` collection does not

6579 include expressions established in the columns clause using the

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

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

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

6583 construct.

6584

6585

6586 .. versionadded:: 1.4

6587

6588 """

6589

6590 # compare to SelectState._generate_columns_plus_names, which

6591 # generates the actual names used in the SELECT string. that

6592 # method is more complex because it also renders columns that are

6593 # fully ambiguous, e.g. same column more than once.

6594 conv = cast(

6595 "Callable[[Any], str]",

6596 SelectState._column_naming_convention(self._label_style),

6597 )

6598

6599 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6600 [

6601 (conv(c), c)

6602 for c in self._all_selected_columns

6603 if is_column_element(c)

6604 ]

6605 )

6606 return cc.as_readonly()

6607

6608 @HasMemoized_ro_memoized_attribute

6609 def _all_selected_columns(self) -> _SelectIterable:

6610 meth = SelectState.get_plugin_class(self).all_selected_columns

6611 return list(meth(self))

6612

6613 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6614 if self._label_style is LABEL_STYLE_NONE:

6615 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6616 return self

6617

6618 def _generate_fromclause_column_proxies(

6619 self,

6620 subquery: FromClause,

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

6622 primary_key: ColumnSet,

6623 foreign_keys: Set[KeyedColumnElement[Any]],

6624 *,

6625 proxy_compound_columns: Optional[

6626 Iterable[Sequence[ColumnElement[Any]]]

6627 ] = None,

6628 ) -> None:

6629 """Generate column proxies to place in the exported ``.c``

6630 collection of a subquery."""

6631

6632 if proxy_compound_columns:

6633 extra_col_iterator = proxy_compound_columns

6634 prox = [

6635 c._make_proxy(

6636 subquery,

6637 key=proxy_key,

6638 name=required_label_name,

6639 name_is_truncatable=True,

6640 compound_select_cols=extra_cols,

6641 primary_key=primary_key,

6642 foreign_keys=foreign_keys,

6643 )

6644 for (

6645 (

6646 required_label_name,

6647 proxy_key,

6648 fallback_label_name,

6649 c,

6650 repeated,

6651 ),

6652 extra_cols,

6653 ) in (

6654 zip(

6655 self._generate_columns_plus_names(False),

6656 extra_col_iterator,

6657 )

6658 )

6659 if is_column_element(c)

6660 ]

6661 else:

6662 prox = [

6663 c._make_proxy(

6664 subquery,

6665 key=proxy_key,

6666 name=required_label_name,

6667 name_is_truncatable=True,

6668 primary_key=primary_key,

6669 foreign_keys=foreign_keys,

6670 )

6671 for (

6672 required_label_name,

6673 proxy_key,

6674 fallback_label_name,

6675 c,

6676 repeated,

6677 ) in (self._generate_columns_plus_names(False))

6678 if is_column_element(c)

6679 ]

6680

6681 columns._populate_separate_keys(prox)

6682

6683 def _needs_parens_for_grouping(self) -> bool:

6684 return self._has_row_limiting_clause or bool(

6685 self._order_by_clause.clauses

6686 )

6687

6688 def self_group(

6689 self, against: Optional[OperatorType] = None

6690 ) -> Union[SelectStatementGrouping[Self], Self]:

6691 """Return a 'grouping' construct as per the

6692 :class:`_expression.ClauseElement` specification.

6693

6694 This produces an element that can be embedded in an expression. Note

6695 that this method is called automatically as needed when constructing

6696 expressions and should not require explicit use.

6697

6698 """

6699 if (

6700 isinstance(against, CompoundSelect)

6701 and not self._needs_parens_for_grouping()

6702 ):

6703 return self

6704 else:

6705 return SelectStatementGrouping(self)

6706

6707 def union(

6708 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6709 ) -> CompoundSelect[Unpack[_Ts]]:

6710 r"""Return a SQL ``UNION`` of this select() construct against

6711 the given selectables provided as positional arguments.

6712

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

6714 UNION.

6715

6716 .. versionchanged:: 1.4.28

6717

6718 multiple elements are now accepted.

6719

6720 :param \**kwargs: keyword arguments are forwarded to the constructor

6721 for the newly created :class:`_sql.CompoundSelect` object.

6722

6723 """

6724 return CompoundSelect._create_union(self, *other)

6725

6726 def union_all(

6727 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6728 ) -> CompoundSelect[Unpack[_Ts]]:

6729 r"""Return a SQL ``UNION ALL`` of this select() construct against

6730 the given selectables provided as positional arguments.

6731

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

6733 UNION.

6734

6735 .. versionchanged:: 1.4.28

6736

6737 multiple elements are now accepted.

6738

6739 :param \**kwargs: keyword arguments are forwarded to the constructor

6740 for the newly created :class:`_sql.CompoundSelect` object.

6741

6742 """

6743 return CompoundSelect._create_union_all(self, *other)

6744

6745 def except_(

6746 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6747 ) -> CompoundSelect[Unpack[_Ts]]:

6748 r"""Return a SQL ``EXCEPT`` of this select() construct against

6749 the given selectable provided as positional arguments.

6750

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

6752 UNION.

6753

6754 .. versionchanged:: 1.4.28

6755

6756 multiple elements are now accepted.

6757

6758 """

6759 return CompoundSelect._create_except(self, *other)

6760

6761 def except_all(

6762 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6763 ) -> CompoundSelect[Unpack[_Ts]]:

6764 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6765 the given selectables provided as positional arguments.

6766

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

6768 UNION.

6769

6770 .. versionchanged:: 1.4.28

6771

6772 multiple elements are now accepted.

6773

6774 """

6775 return CompoundSelect._create_except_all(self, *other)

6776

6777 def intersect(

6778 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6779 ) -> CompoundSelect[Unpack[_Ts]]:

6780 r"""Return a SQL ``INTERSECT`` of this select() construct against

6781 the given selectables provided as positional arguments.

6782

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

6784 UNION.

6785

6786 .. versionchanged:: 1.4.28

6787

6788 multiple elements are now accepted.

6789

6790 :param \**kwargs: keyword arguments are forwarded to the constructor

6791 for the newly created :class:`_sql.CompoundSelect` object.

6792

6793 """

6794 return CompoundSelect._create_intersect(self, *other)

6795

6796 def intersect_all(

6797 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6798 ) -> CompoundSelect[Unpack[_Ts]]:

6799 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6800 against the given selectables provided as positional arguments.

6801

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

6803 UNION.

6804

6805 .. versionchanged:: 1.4.28

6806

6807 multiple elements are now accepted.

6808

6809 :param \**kwargs: keyword arguments are forwarded to the constructor

6810 for the newly created :class:`_sql.CompoundSelect` object.

6811

6812 """

6813 return CompoundSelect._create_intersect_all(self, *other)

6814

6815

6816class ScalarSelect(

6817 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6818):

6819 """Represent a scalar subquery.

6820

6821

6822 A :class:`_sql.ScalarSelect` is created by invoking the

6823 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6824 then participates in other SQL expressions as a SQL column expression

6825 within the :class:`_sql.ColumnElement` hierarchy.

6826

6827 .. seealso::

6828

6829 :meth:`_sql.SelectBase.scalar_subquery`

6830

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

6832

6833 """

6834

6835 _traverse_internals: _TraverseInternalsType = [

6836 ("element", InternalTraversal.dp_clauseelement),

6837 ("type", InternalTraversal.dp_type),

6838 ]

6839

6840 _from_objects: List[FromClause] = []

6841 _is_from_container = True

6842 if not TYPE_CHECKING:

6843 _is_implicitly_boolean = False

6844 inherit_cache = True

6845

6846 element: SelectBase

6847

6848 def __init__(self, element: SelectBase) -> None:

6849 self.element = element

6850 self.type = element._scalar_type()

6851 self._propagate_attrs = element._propagate_attrs

6852

6853 def __getattr__(self, attr: str) -> Any:

6854 return getattr(self.element, attr)

6855

6856 def __getstate__(self) -> Dict[str, Any]:

6857 return {"element": self.element, "type": self.type}

6858

6859 def __setstate__(self, state: Dict[str, Any]) -> None:

6860 self.element = state["element"]

6861 self.type = state["type"]

6862

6863 @property

6864 def columns(self) -> NoReturn:

6865 raise exc.InvalidRequestError(

6866 "Scalar Select expression has no "

6867 "columns; use this object directly "

6868 "within a column-level expression."

6869 )

6870

6871 c = columns

6872

6873 @_generative

6874 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6875 """Apply a WHERE clause to the SELECT statement referred to

6876 by this :class:`_expression.ScalarSelect`.

6877

6878 """

6879 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6880 crit

6881 )

6882 return self

6883

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

6885 return self

6886

6887 def _ungroup(self) -> Self:

6888 return self

6889

6890 @_generative

6891 def correlate(

6892 self,

6893 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6894 ) -> Self:

6895 r"""Return a new :class:`_expression.ScalarSelect`

6896 which will correlate the given FROM

6897 clauses to that of an enclosing :class:`_expression.Select`.

6898

6899 This method is mirrored from the :meth:`_sql.Select.correlate` method

6900 of the underlying :class:`_sql.Select`. The method applies the

6901 :meth:_sql.Select.correlate` method, then returns a new

6902 :class:`_sql.ScalarSelect` against that statement.

6903

6904 .. versionadded:: 1.4 Previously, the

6905 :meth:`_sql.ScalarSelect.correlate`

6906 method was only available from :class:`_sql.Select`.

6907

6908 :param \*fromclauses: a list of one or more

6909 :class:`_expression.FromClause`

6910 constructs, or other compatible constructs (i.e. ORM-mapped

6911 classes) to become part of the correlate collection.

6912

6913 .. seealso::

6914

6915 :meth:`_expression.ScalarSelect.correlate_except`

6916

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

6918

6919

6920 """

6921 self.element = cast(

6922 "Select[Unpack[TupleAny]]", self.element

6923 ).correlate(*fromclauses)

6924 return self

6925

6926 @_generative

6927 def correlate_except(

6928 self,

6929 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6930 ) -> Self:

6931 r"""Return a new :class:`_expression.ScalarSelect`

6932 which will omit the given FROM

6933 clauses from the auto-correlation process.

6934

6935 This method is mirrored from the

6936 :meth:`_sql.Select.correlate_except` method of the underlying

6937 :class:`_sql.Select`. The method applies the

6938 :meth:_sql.Select.correlate_except` method, then returns a new

6939 :class:`_sql.ScalarSelect` against that statement.

6940

6941 .. versionadded:: 1.4 Previously, the

6942 :meth:`_sql.ScalarSelect.correlate_except`

6943 method was only available from :class:`_sql.Select`.

6944

6945 :param \*fromclauses: a list of one or more

6946 :class:`_expression.FromClause`

6947 constructs, or other compatible constructs (i.e. ORM-mapped

6948 classes) to become part of the correlate-exception collection.

6949

6950 .. seealso::

6951

6952 :meth:`_expression.ScalarSelect.correlate`

6953

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

6955

6956

6957 """

6958

6959 self.element = cast(

6960 "Select[Unpack[TupleAny]]", self.element

6961 ).correlate_except(*fromclauses)

6962 return self

6963

6964

6965class Exists(UnaryExpression[bool]):

6966 """Represent an ``EXISTS`` clause.

6967

6968 See :func:`_sql.exists` for a description of usage.

6969

6970 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6971 instance by calling :meth:`_sql.SelectBase.exists`.

6972

6973 """

6974

6975 inherit_cache = True

6976

6977 def __init__(

6978 self,

6979 __argument: Optional[

6980 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6981 ] = None,

6982 /,

6983 ):

6984 s: ScalarSelect[Any]

6985

6986 # TODO: this seems like we should be using coercions for this

6987 if __argument is None:

6988 s = Select(literal_column("*")).scalar_subquery()

6989 elif isinstance(__argument, SelectBase):

6990 s = __argument.scalar_subquery()

6991 s._propagate_attrs = __argument._propagate_attrs

6992 elif isinstance(__argument, ScalarSelect):

6993 s = __argument

6994 else:

6995 s = Select(__argument).scalar_subquery()

6996

6997 UnaryExpression.__init__(

6998 self,

6999 s,

7000 operator=operators.exists,

7001 type_=type_api.BOOLEANTYPE,

7002 )

7003

7004 @util.ro_non_memoized_property

7005 def _from_objects(self) -> List[FromClause]:

7006 return []

7007

7008 def _regroup(

7009 self,

7010 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7011 ) -> ScalarSelect[Any]:

7012

7013 assert isinstance(self.element, ScalarSelect)

7014 element = self.element.element

7015 if not isinstance(element, Select):

7016 raise exc.InvalidRequestError(

7017 "Can only apply this operation to a plain SELECT construct"

7018 )

7019 new_element = fn(element)

7020

7021 return_value = new_element.scalar_subquery()

7022 return return_value

7023

7024 def select(self) -> Select[bool]:

7025 r"""Return a SELECT of this :class:`_expression.Exists`.

7026

7027 e.g.::

7028

7029 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7030

7031 This will produce a statement resembling:

7032

7033 .. sourcecode:: sql

7034

7035 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7036

7037 .. seealso::

7038

7039 :func:`_expression.select` - general purpose

7040 method which allows for arbitrary column lists.

7041

7042 """ # noqa

7043

7044 return Select(self)

7045

7046 def correlate(

7047 self,

7048 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7049 ) -> Self:

7050 """Apply correlation to the subquery noted by this

7051 :class:`_sql.Exists`.

7052

7053 .. seealso::

7054

7055 :meth:`_sql.ScalarSelect.correlate`

7056

7057 """

7058 e = self._clone()

7059 e.element = self._regroup(

7060 lambda element: element.correlate(*fromclauses)

7061 )

7062 return e

7063

7064 def correlate_except(

7065 self,

7066 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7067 ) -> Self:

7068 """Apply correlation to the subquery noted by this

7069 :class:`_sql.Exists`.

7070

7071 .. seealso::

7072

7073 :meth:`_sql.ScalarSelect.correlate_except`

7074

7075 """

7076 e = self._clone()

7077 e.element = self._regroup(

7078 lambda element: element.correlate_except(*fromclauses)

7079 )

7080 return e

7081

7082 def select_from(self, *froms: _FromClauseArgument) -> Self:

7083 """Return a new :class:`_expression.Exists` construct,

7084 applying the given

7085 expression to the :meth:`_expression.Select.select_from`

7086 method of the select

7087 statement contained.

7088

7089 .. note:: it is typically preferable to build a :class:`_sql.Select`

7090 statement first, including the desired WHERE clause, then use the

7091 :meth:`_sql.SelectBase.exists` method to produce an

7092 :class:`_sql.Exists` object at once.

7093

7094 """

7095 e = self._clone()

7096 e.element = self._regroup(lambda element: element.select_from(*froms))

7097 return e

7098

7099 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7100 """Return a new :func:`_expression.exists` construct with the

7101 given expression added to

7102 its WHERE clause, joined to the existing clause via AND, if any.

7103

7104

7105 .. note:: it is typically preferable to build a :class:`_sql.Select`

7106 statement first, including the desired WHERE clause, then use the

7107 :meth:`_sql.SelectBase.exists` method to produce an

7108 :class:`_sql.Exists` object at once.

7109

7110 """

7111 e = self._clone()

7112 e.element = self._regroup(lambda element: element.where(*clause))

7113 return e

7114

7115

7116class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7117 """Wrap a :class:`_expression.TextClause` construct within a

7118 :class:`_expression.SelectBase`

7119 interface.

7120

7121 This allows the :class:`_expression.TextClause` object to gain a

7122 ``.c`` collection

7123 and other FROM-like capabilities such as

7124 :meth:`_expression.FromClause.alias`,

7125 :meth:`_expression.SelectBase.cte`, etc.

7126

7127 The :class:`_expression.TextualSelect` construct is produced via the

7128 :meth:`_expression.TextClause.columns`

7129 method - see that method for details.

7130

7131 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7132 class was renamed

7133 from ``TextAsFrom``, to more correctly suit its role as a

7134 SELECT-oriented object and not a FROM clause.

7135

7136 .. seealso::

7137

7138 :func:`_expression.text`

7139

7140 :meth:`_expression.TextClause.columns` - primary creation interface.

7141

7142 """

7143

7144 __visit_name__ = "textual_select"

7145

7146 _label_style = LABEL_STYLE_NONE

7147

7148 _traverse_internals: _TraverseInternalsType = (

7149 [

7150 ("element", InternalTraversal.dp_clauseelement),

7151 ("column_args", InternalTraversal.dp_clauseelement_list),

7152 ]

7153 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7154 + HasCTE._has_ctes_traverse_internals

7155 )

7156

7157 _is_textual = True

7158

7159 is_text = True

7160 is_select = True

7161

7162 def __init__(

7163 self,

7164 text: TextClause,

7165 columns: List[_ColumnExpressionArgument[Any]],

7166 positional: bool = False,

7167 ) -> None:

7168 self._init(

7169 text,

7170 # convert for ORM attributes->columns, etc

7171 [

7172 coercions.expect(roles.LabeledColumnExprRole, c)

7173 for c in columns

7174 ],

7175 positional,

7176 )

7177

7178 def _init(

7179 self,

7180 text: TextClause,

7181 columns: List[NamedColumn[Any]],

7182 positional: bool = False,

7183 ) -> None:

7184 self.element = text

7185 self.column_args = columns

7186 self.positional = positional

7187

7188 @HasMemoized_ro_memoized_attribute

7189 def selected_columns(

7190 self,

7191 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7192 """A :class:`_expression.ColumnCollection`

7193 representing the columns that

7194 this SELECT statement or similar construct returns in its result set,

7195 not including :class:`_sql.TextClause` constructs.

7196

7197 This collection differs from the :attr:`_expression.FromClause.columns`

7198 collection of a :class:`_expression.FromClause` in that the columns

7199 within this collection cannot be directly nested inside another SELECT

7200 statement; a subquery must be applied first which provides for the

7201 necessary parenthesization required by SQL.

7202

7203 For a :class:`_expression.TextualSelect` construct, the collection

7204 contains the :class:`_expression.ColumnElement` objects that were

7205 passed to the constructor, typically via the

7206 :meth:`_expression.TextClause.columns` method.

7207

7208

7209 .. versionadded:: 1.4

7210

7211 """

7212 return ColumnCollection(

7213 (c.key, c) for c in self.column_args

7214 ).as_readonly()

7215

7216 @util.ro_non_memoized_property

7217 def _all_selected_columns(self) -> _SelectIterable:

7218 return self.column_args

7219

7220 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7221 return self

7222

7223 def _ensure_disambiguated_names(self) -> TextualSelect:

7224 return self

7225

7226 @_generative

7227 def bindparams(

7228 self,

7229 *binds: BindParameter[Any],

7230 **bind_as_values: Any,

7231 ) -> Self:

7232 self.element = self.element.bindparams(*binds, **bind_as_values)

7233 return self

7234

7235 def _generate_fromclause_column_proxies(

7236 self,

7237 fromclause: FromClause,

7238 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7239 primary_key: ColumnSet,

7240 foreign_keys: Set[KeyedColumnElement[Any]],

7241 *,

7242 proxy_compound_columns: Optional[

7243 Iterable[Sequence[ColumnElement[Any]]]

7244 ] = None,

7245 ) -> None:

7246 if TYPE_CHECKING:

7247 assert isinstance(fromclause, Subquery)

7248

7249 if proxy_compound_columns:

7250 columns._populate_separate_keys(

7251 c._make_proxy(

7252 fromclause,

7253 compound_select_cols=extra_cols,

7254 primary_key=primary_key,

7255 foreign_keys=foreign_keys,

7256 )

7257 for c, extra_cols in zip(

7258 self.column_args, proxy_compound_columns

7259 )

7260 )

7261 else:

7262 columns._populate_separate_keys(

7263 c._make_proxy(

7264 fromclause,

7265 primary_key=primary_key,

7266 foreign_keys=foreign_keys,

7267 )

7268 for c in self.column_args

7269 )

7270

7271 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7272 return self.column_args[0].type

7273

7274

7275TextAsFrom = TextualSelect

7276"""Backwards compatibility with the previous name"""

7277

7278

7279class AnnotatedFromClause(Annotated):

7280 def _copy_internals(self, **kw: Any) -> None:

7281 super()._copy_internals(**kw)

7282 if kw.get("ind_cols_on_fromclause", False):

7283 ee = self._Annotated__element # type: ignore

7284

7285 self.c = ee.__class__.c.fget(self) # type: ignore

7286

7287 @util.ro_memoized_property

7288 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7289 """proxy the .c collection of the underlying FromClause.

7290

7291 Originally implemented in 2008 as a simple load of the .c collection

7292 when the annotated construct was created (see d3621ae961a), in modern

7293 SQLAlchemy versions this can be expensive for statements constructed

7294 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7295 it, which works just as well.

7296

7297 Two different use cases seem to require the collection either copied

7298 from the underlying one, or unique to this AnnotatedFromClause.

7299

7300 See test_selectable->test_annotated_corresponding_column

7301

7302 """

7303 ee = self._Annotated__element # type: ignore

7304 return ee.c # type: ignore