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

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

1163 FROM table1 JOIN table2 ON true

1164

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

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

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

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

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

1170

1171

1172 .. versionadded:: 1.4

1173

1174 """ # noqa: E501

1175

1176 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1179 when generating the columns clause of a SELECT statement.

1180

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

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

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

1184

1185 .. sourcecode:: pycon+sql

1186

1187 >>> from sqlalchemy import (

1188 ... table,

1189 ... column,

1190 ... select,

1191 ... true,

1192 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1193 ... )

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

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

1196 >>> print(

1197 ... select(table1, table2)

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

1199 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1200 ... )

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

1202 FROM table1 JOIN table2 ON true

1203

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

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

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

1207

1208 .. versionadded:: 1.4

1209

1210 """ # noqa: E501

1211

1212 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1213 """The default label style, refers to

1214 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1215

1216 .. versionadded:: 1.4

1217

1218 """

1219

1220 LABEL_STYLE_LEGACY_ORM = 3

1221

1222

1223(

1224 LABEL_STYLE_NONE,

1225 LABEL_STYLE_TABLENAME_PLUS_COL,

1226 LABEL_STYLE_DISAMBIGUATE_ONLY,

1227 _,

1228) = list(SelectLabelStyle)

1229

1230LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1231

1232

1233class Join(roles.DMLTableRole, FromClause):

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

1235 :class:`_expression.FromClause`

1236 elements.

1237

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

1239 is the module-level

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

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

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

1243 :class:`_schema.Table`).

1244

1245 .. seealso::

1246

1247 :func:`_expression.join`

1248

1249 :meth:`_expression.FromClause.join`

1250

1251 """

1252

1253 __visit_name__ = "join"

1254

1255 _traverse_internals: _TraverseInternalsType = [

1256 ("left", InternalTraversal.dp_clauseelement),

1257 ("right", InternalTraversal.dp_clauseelement),

1258 ("onclause", InternalTraversal.dp_clauseelement),

1259 ("isouter", InternalTraversal.dp_boolean),

1260 ("full", InternalTraversal.dp_boolean),

1261 ]

1262

1263 _is_join = True

1264

1265 left: FromClause

1266 right: FromClause

1267 onclause: Optional[ColumnElement[bool]]

1268 isouter: bool

1269 full: bool

1270

1271 def __init__(

1272 self,

1273 left: _FromClauseArgument,

1274 right: _FromClauseArgument,

1275 onclause: Optional[_OnClauseArgument] = None,

1276 isouter: bool = False,

1277 full: bool = False,

1278 ):

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

1280

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

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

1283 :class:`_expression.FromClause` object.

1284

1285 """

1286

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

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

1289 # annotated objects. test_orm.py -> test_fetch_results

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

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

1292 # callcounts for a single compilation in that particular test

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

1294 # 29200 -> 30373

1295

1296 self.left = coercions.expect(

1297 roles.FromClauseRole,

1298 left,

1299 )

1300 self.right = coercions.expect(

1301 roles.FromClauseRole,

1302 right,

1303 ).self_group()

1304

1305 if onclause is None:

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

1307 else:

1308 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1309 # not merged yet

1310 self.onclause = coercions.expect(

1311 roles.OnClauseRole, onclause

1312 ).self_group(against=operators._asbool)

1313

1314 self.isouter = isouter

1315 self.full = full

1316

1317 @util.ro_non_memoized_property

1318 def description(self) -> str:

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

1320 self.left.description,

1321 id(self.left),

1322 self.right.description,

1323 id(self.right),

1324 )

1325

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

1327 return (

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

1329 # as well

1330 hash(fromclause) == hash(self)

1331 or self.left.is_derived_from(fromclause)

1332 or self.right.is_derived_from(fromclause)

1333 )

1334

1335 def self_group(

1336 self, against: Optional[OperatorType] = None

1337 ) -> FromGrouping:

1338 return FromGrouping(self)

1339

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

1341 def _populate_column_collection(

1342 self,

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

1344 primary_key: ColumnSet,

1345 foreign_keys: Set[KeyedColumnElement[Any]],

1346 ) -> None:

1347 sqlutil = util.preloaded.sql_util

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

1349 c for c in self.right.c

1350 ]

1351

1352 primary_key.extend(

1353 sqlutil.reduce_columns(

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

1355 )

1356 )

1357 columns._populate_separate_keys(

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

1359 )

1360 foreign_keys.update(

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

1362 )

1363

1364 def _copy_internals(

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

1366 ) -> None:

1367 # see Select._copy_internals() for similar concept

1368

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

1370 # determine the new FROM clauses

1371 all_the_froms = set(

1372 itertools.chain(

1373 _from_objects(self.left),

1374 _from_objects(self.right),

1375 )

1376 )

1377

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

1379 # cache used by the clone function

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

1381

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

1383 # ColumnClause with parent table referring to those

1384 # replaced FromClause objects

1385 def replace(

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

1387 **kw: Any,

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

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

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

1391 return newelem

1392 return None

1393

1394 kw["replace"] = replace

1395

1396 # run normal _copy_internals. the clones for

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

1398 # cache

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

1400

1401 self._reset_memoizations()

1402

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

1404 super()._refresh_for_new_column(column)

1405 self.left._refresh_for_new_column(column)

1406 self.right._refresh_for_new_column(column)

1407

1408 def _match_primaries(

1409 self,

1410 left: FromClause,

1411 right: FromClause,

1412 ) -> ColumnElement[bool]:

1413 if isinstance(left, Join):

1414 left_right = left.right

1415 else:

1416 left_right = None

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

1418

1419 @classmethod

1420 def _join_condition(

1421 cls,

1422 a: FromClause,

1423 b: FromClause,

1424 *,

1425 a_subset: Optional[FromClause] = None,

1426 consider_as_foreign_keys: Optional[

1427 AbstractSet[ColumnClause[Any]]

1428 ] = None,

1429 ) -> ColumnElement[bool]:

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

1431

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

1433

1434 """

1435 constraints = cls._joincond_scan_left_right(

1436 a, a_subset, b, consider_as_foreign_keys

1437 )

1438

1439 if len(constraints) > 1:

1440 cls._joincond_trim_constraints(

1441 a, b, constraints, consider_as_foreign_keys

1442 )

1443

1444 if len(constraints) == 0:

1445 if isinstance(b, FromGrouping):

1446 hint = (

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

1448 "subquery using alias()?"

1449 )

1450 else:

1451 hint = ""

1452 raise exc.NoForeignKeysError(

1453 "Can't find any foreign key relationships "

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

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

1456 )

1457

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

1459 if len(crit) == 1:

1460 return crit[0]

1461 else:

1462 return and_(*crit)

1463

1464 @classmethod

1465 def _can_join(

1466 cls,

1467 left: FromClause,

1468 right: FromClause,

1469 *,

1470 consider_as_foreign_keys: Optional[

1471 AbstractSet[ColumnClause[Any]]

1472 ] = None,

1473 ) -> bool:

1474 if isinstance(left, Join):

1475 left_right = left.right

1476 else:

1477 left_right = None

1478

1479 constraints = cls._joincond_scan_left_right(

1480 a=left,

1481 b=right,

1482 a_subset=left_right,

1483 consider_as_foreign_keys=consider_as_foreign_keys,

1484 )

1485

1486 return bool(constraints)

1487

1488 @classmethod

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

1490 def _joincond_scan_left_right(

1491 cls,

1492 a: FromClause,

1493 a_subset: Optional[FromClause],

1494 b: FromClause,

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

1496 ) -> collections.defaultdict[

1497 Optional[ForeignKeyConstraint],

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

1499 ]:

1500 sql_util = util.preloaded.sql_util

1501

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

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

1504

1505 constraints: collections.defaultdict[

1506 Optional[ForeignKeyConstraint],

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

1508 ] = collections.defaultdict(list)

1509

1510 for left in (a_subset, a):

1511 if left is None:

1512 continue

1513 for fk in sorted(

1514 b.foreign_keys,

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

1516 ):

1517 if (

1518 consider_as_foreign_keys is not None

1519 and fk.parent not in consider_as_foreign_keys

1520 ):

1521 continue

1522 try:

1523 col = fk.get_referent(left)

1524 except exc.NoReferenceError as nrte:

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

1526 if nrte.table_name in table_names:

1527 raise

1528 else:

1529 continue

1530

1531 if col is not None:

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

1533 if left is not b:

1534 for fk in sorted(

1535 left.foreign_keys,

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

1537 ):

1538 if (

1539 consider_as_foreign_keys is not None

1540 and fk.parent not in consider_as_foreign_keys

1541 ):

1542 continue

1543 try:

1544 col = fk.get_referent(b)

1545 except exc.NoReferenceError as nrte:

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

1547 if nrte.table_name in table_names:

1548 raise

1549 else:

1550 continue

1551

1552 if col is not None:

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

1554 if constraints:

1555 break

1556 return constraints

1557

1558 @classmethod

1559 def _joincond_trim_constraints(

1560 cls,

1561 a: FromClause,

1562 b: FromClause,

1563 constraints: Dict[Any, Any],

1564 consider_as_foreign_keys: Optional[Any],

1565 ) -> None:

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

1567 # to include just those FKCs that match exactly to

1568 # "consider_as_foreign_keys".

1569 if consider_as_foreign_keys:

1570 for const in list(constraints):

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

1572 consider_as_foreign_keys

1573 ):

1574 del constraints[const]

1575

1576 # if still multiple constraints, but

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

1578 if len(constraints) > 1:

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

1580 if len(dedupe) == 1:

1581 key = list(constraints)[0]

1582 constraints = {key: constraints[key]}

1583

1584 if len(constraints) != 1:

1585 raise exc.AmbiguousForeignKeysError(

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

1587 "tables have more than one foreign key "

1588 "constraint relationship between them. "

1589 "Please specify the 'onclause' of this "

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

1591 )

1592

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

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

1595 :class:`_expression.Join`.

1596

1597 E.g.::

1598

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

1600

1601 stmt = stmt.select()

1602

1603 The above will produce a SQL string resembling:

1604

1605 .. sourcecode:: sql

1606

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

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

1609

1610 """

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

1612

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

1614 def _anonymous_fromclause(

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

1616 ) -> TODO_Any:

1617 sqlutil = util.preloaded.sql_util

1618 if flat:

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

1620 left_name = name # will recurse

1621 else:

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

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

1624 else:

1625 left_name = name

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

1627 right_name = name # will recurse

1628 else:

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

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

1631 else:

1632 right_name = name

1633 left_a, right_a = (

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

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

1636 )

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

1638 sqlutil.ClauseAdapter(right_a)

1639 )

1640

1641 return left_a.join(

1642 right_a,

1643 adapter.traverse(self.onclause),

1644 isouter=self.isouter,

1645 full=self.full,

1646 )

1647 else:

1648 return (

1649 self.select()

1650 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1651 .correlate(None)

1652 .alias(name)

1653 )

1654

1655 @util.ro_non_memoized_property

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

1657 return itertools.chain(

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

1659 )

1660

1661 @util.ro_non_memoized_property

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

1663 self_list: List[FromClause] = [self]

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

1665

1666

1667class NoInit:

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

1669 raise NotImplementedError(

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

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

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

1673 "selectable objects."

1674 % (

1675 self.__class__.__name__,

1676 self.__class__.__name__.lower(),

1677 self.__class__.__name__.lower(),

1678 )

1679 )

1680

1681

1682class LateralFromClause(NamedFromClause):

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

1684

1685

1686# FromClause ->

1687# AliasedReturnsRows

1688# -> Alias only for FromClause

1689# -> Subquery only for SelectBase

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

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

1692# w/ non-deprecated coercion

1693# -> TableSample -> only for FromClause

1694

1695

1696class AliasedReturnsRows(NoInit, NamedFromClause):

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

1698 selectables."""

1699

1700 _is_from_container = True

1701

1702 _supports_derived_columns = False

1703

1704 element: ReturnsRows

1705

1706 _traverse_internals: _TraverseInternalsType = [

1707 ("element", InternalTraversal.dp_clauseelement),

1708 ("name", InternalTraversal.dp_anon_name),

1709 ]

1710

1711 @classmethod

1712 def _construct(

1713 cls,

1714 selectable: Any,

1715 *,

1716 name: Optional[str] = None,

1717 **kw: Any,

1718 ) -> Self:

1719 obj = cls.__new__(cls)

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

1721 return obj

1722

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

1724 self.element = coercions.expect(

1725 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1726 )

1727 self.element = selectable

1728 self._orig_name = name

1729 if name is None:

1730 if (

1731 isinstance(selectable, FromClause)

1732 and selectable.named_with_column

1733 ):

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

1735 if isinstance(name, _anonymous_label):

1736 name = None

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

1738 self.name = name

1739

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

1741 super()._refresh_for_new_column(column)

1742 self.element._refresh_for_new_column(column)

1743

1744 def _populate_column_collection(

1745 self,

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

1747 primary_key: ColumnSet,

1748 foreign_keys: Set[KeyedColumnElement[Any]],

1749 ) -> None:

1750 self.element._generate_fromclause_column_proxies(

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

1752 )

1753

1754 @util.ro_non_memoized_property

1755 def description(self) -> str:

1756 name = self.name

1757 if isinstance(name, _anonymous_label):

1758 return "anon_1"

1759

1760 return name

1761

1762 @util.ro_non_memoized_property

1763 def implicit_returning(self) -> bool:

1764 return self.element.implicit_returning # type: ignore

1765

1766 @property

1767 def original(self) -> ReturnsRows:

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

1769 return self.element

1770

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

1772 if fromclause in self._cloned_set:

1773 return True

1774 return self.element.is_derived_from(fromclause)

1775

1776 def _copy_internals(

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

1778 ) -> None:

1779 existing_element = self.element

1780

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

1782

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

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

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

1786 # performance.

1787 if existing_element is not self.element:

1788 self._reset_column_collection()

1789

1790 @property

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

1792 return [self]

1793

1794

1795class FromClauseAlias(AliasedReturnsRows):

1796 element: FromClause

1797

1798 @util.ro_non_memoized_property

1799 def description(self) -> str:

1800 name = self.name

1801 if isinstance(name, _anonymous_label):

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

1803

1804 return name

1805

1806

1807class Alias(roles.DMLTableRole, FromClauseAlias):

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

1809

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

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

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

1813

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

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

1816 method available

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

1818

1819 .. seealso::

1820

1821 :meth:`_expression.FromClause.alias`

1822

1823 """

1824

1825 __visit_name__ = "alias"

1826

1827 inherit_cache = True

1828

1829 element: FromClause

1830

1831 @classmethod

1832 def _factory(

1833 cls,

1834 selectable: FromClause,

1835 name: Optional[str] = None,

1836 flat: bool = False,

1837 ) -> NamedFromClause:

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

1839 name=name, flat=flat

1840 )

1841

1842

1843class TableValuedAlias(LateralFromClause, Alias):

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

1845

1846 This construct provides for a SQL function that returns columns

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

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

1849 method, e.g.:

1850

1851 .. sourcecode:: pycon+sql

1852

1853 >>> from sqlalchemy import select, func

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

1855 ... "value"

1856 ... )

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

1858 {printsql}SELECT anon_1.value

1859 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1860

1861 .. versionadded:: 1.4.0b2

1862

1863 .. seealso::

1864

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

1866

1867 """ # noqa: E501

1868

1869 __visit_name__ = "table_valued_alias"

1870

1871 _supports_derived_columns = True

1872 _render_derived = False

1873 _render_derived_w_types = False

1874 joins_implicitly = False

1875

1876 _traverse_internals: _TraverseInternalsType = [

1877 ("element", InternalTraversal.dp_clauseelement),

1878 ("name", InternalTraversal.dp_anon_name),

1879 ("_tableval_type", InternalTraversal.dp_type),

1880 ("_render_derived", InternalTraversal.dp_boolean),

1881 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1882 ]

1883

1884 def _init(

1885 self,

1886 selectable: Any,

1887 *,

1888 name: Optional[str] = None,

1889 table_value_type: Optional[TableValueType] = None,

1890 joins_implicitly: bool = False,

1891 ) -> None:

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

1893

1894 self.joins_implicitly = joins_implicitly

1895 self._tableval_type = (

1896 type_api.TABLEVALUE

1897 if table_value_type is None

1898 else table_value_type

1899 )

1900

1901 @HasMemoized.memoized_attribute

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

1903 """Return a column expression representing this

1904 :class:`_sql.TableValuedAlias`.

1905

1906 This accessor is used to implement the

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

1908 method for further details.

1909

1910 E.g.:

1911

1912 .. sourcecode:: pycon+sql

1913

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

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

1916

1917 .. seealso::

1918

1919 :meth:`_functions.FunctionElement.column_valued`

1920

1921 """

1922

1923 return TableValuedColumn(self, self._tableval_type)

1924

1925 def alias(

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

1927 ) -> TableValuedAlias:

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

1929

1930 This creates a distinct FROM object that will be distinguished

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

1932

1933 """

1934

1935 tva: TableValuedAlias = TableValuedAlias._construct(

1936 self,

1937 name=name,

1938 table_value_type=self._tableval_type,

1939 joins_implicitly=self.joins_implicitly,

1940 )

1941

1942 if self._render_derived:

1943 tva._render_derived = True

1944 tva._render_derived_w_types = self._render_derived_w_types

1945

1946 return tva

1947

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

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

1950 set, so that it renders as LATERAL.

1951

1952 .. seealso::

1953

1954 :func:`_expression.lateral`

1955

1956 """

1957 tva = self.alias(name=name)

1958 tva._is_lateral = True

1959 return tva

1960

1961 def render_derived(

1962 self,

1963 name: Optional[str] = None,

1964 with_types: bool = False,

1965 ) -> TableValuedAlias:

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

1967

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

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

1970

1971 .. sourcecode:: pycon+sql

1972

1973 >>> print(

1974 ... select(

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

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

1977 ... .render_derived()

1978 ... )

1979 ... )

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

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

1982

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

1984 the alias expression (this syntax currently applies to the

1985 PostgreSQL database):

1986

1987 .. sourcecode:: pycon+sql

1988

1989 >>> print(

1990 ... select(

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

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

1993 ... .render_derived(with_types=True)

1994 ... )

1995 ... )

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

1997 AS anon_1(a INTEGER, b VARCHAR)

1998

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

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

2001

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

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

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

2005

2006 """ # noqa: E501

2007

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

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

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

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

2012 # (just saw it happen on CI)

2013

2014 # construct against original to prevent memory growth

2015 # for repeated generations

2016 new_alias: TableValuedAlias = TableValuedAlias._construct(

2017 self.element,

2018 name=name,

2019 table_value_type=self._tableval_type,

2020 joins_implicitly=self.joins_implicitly,

2021 )

2022 new_alias._render_derived = True

2023 new_alias._render_derived_w_types = with_types

2024 return new_alias

2025

2026

2027class Lateral(FromClauseAlias, LateralFromClause):

2028 """Represent a LATERAL subquery.

2029

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

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

2032 method available

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

2034

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

2036 PostgreSQL versions provide support for this keyword.

2037

2038 .. seealso::

2039

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

2041

2042 """

2043

2044 __visit_name__ = "lateral"

2045 _is_lateral = True

2046

2047 inherit_cache = True

2048

2049 @classmethod

2050 def _factory(

2051 cls,

2052 selectable: Union[SelectBase, _FromClauseArgument],

2053 name: Optional[str] = None,

2054 ) -> LateralFromClause:

2055 return coercions.expect(

2056 roles.FromClauseRole, selectable, explicit_subquery=True

2057 ).lateral(name=name)

2058

2059

2060class TableSample(FromClauseAlias):

2061 """Represent a TABLESAMPLE clause.

2062

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

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

2065 method

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

2067

2068 .. seealso::

2069

2070 :func:`_expression.tablesample`

2071

2072 """

2073

2074 __visit_name__ = "tablesample"

2075

2076 _traverse_internals: _TraverseInternalsType = (

2077 AliasedReturnsRows._traverse_internals

2078 + [

2079 ("sampling", InternalTraversal.dp_clauseelement),

2080 ("seed", InternalTraversal.dp_clauseelement),

2081 ]

2082 )

2083

2084 @classmethod

2085 def _factory(

2086 cls,

2087 selectable: _FromClauseArgument,

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

2089 name: Optional[str] = None,

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

2091 ) -> TableSample:

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

2093 sampling, name=name, seed=seed

2094 )

2095

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

2097 def _init( # type: ignore[override]

2098 self,

2099 selectable: Any,

2100 *,

2101 name: Optional[str] = None,

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

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

2104 ) -> None:

2105 assert sampling is not None

2106 functions = util.preloaded.sql_functions

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

2108 sampling = functions.func.system(sampling)

2109

2110 self.sampling: Function[Any] = sampling

2111 self.seed = seed

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

2113

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

2115 return self.sampling

2116

2117

2118class CTE(

2119 roles.DMLTableRole,

2120 roles.IsCTERole,

2121 Generative,

2122 HasPrefixes,

2123 HasSuffixes,

2124 AliasedReturnsRows,

2125):

2126 """Represent a Common Table Expression.

2127

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

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

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

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

2132 :class:`_sql.Update` and

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

2134 usage details on CTEs.

2135

2136 .. seealso::

2137

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

2139

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

2141

2142 """

2143

2144 __visit_name__ = "cte"

2145

2146 _traverse_internals: _TraverseInternalsType = (

2147 AliasedReturnsRows._traverse_internals

2148 + [

2149 ("_cte_alias", InternalTraversal.dp_clauseelement),

2150 ("_restates", InternalTraversal.dp_clauseelement),

2151 ("recursive", InternalTraversal.dp_boolean),

2152 ("nesting", InternalTraversal.dp_boolean),

2153 ]

2154 + HasPrefixes._has_prefixes_traverse_internals

2155 + HasSuffixes._has_suffixes_traverse_internals

2156 )

2157

2158 element: HasCTE

2159

2160 @classmethod

2161 def _factory(

2162 cls,

2163 selectable: HasCTE,

2164 name: Optional[str] = None,

2165 recursive: bool = False,

2166 ) -> CTE:

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

2168 or Common Table Expression instance.

2169

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

2171

2172 """

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

2174 name=name, recursive=recursive

2175 )

2176

2177 def _init(

2178 self,

2179 selectable: HasCTE,

2180 *,

2181 name: Optional[str] = None,

2182 recursive: bool = False,

2183 nesting: bool = False,

2184 _cte_alias: Optional[CTE] = None,

2185 _restates: Optional[CTE] = None,

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

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

2188 ) -> None:

2189 self.recursive = recursive

2190 self.nesting = nesting

2191 self._cte_alias = _cte_alias

2192 # Keep recursivity reference with union/union_all

2193 self._restates = _restates

2194 if _prefixes:

2195 self._prefixes = _prefixes

2196 if _suffixes:

2197 self._suffixes = _suffixes

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

2199

2200 def _populate_column_collection(

2201 self,

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

2203 primary_key: ColumnSet,

2204 foreign_keys: Set[KeyedColumnElement[Any]],

2205 ) -> None:

2206 if self._cte_alias is not None:

2207 self._cte_alias._generate_fromclause_column_proxies(

2208 self,

2209 columns,

2210 primary_key=primary_key,

2211 foreign_keys=foreign_keys,

2212 )

2213 else:

2214 self.element._generate_fromclause_column_proxies(

2215 self,

2216 columns,

2217 primary_key=primary_key,

2218 foreign_keys=foreign_keys,

2219 )

2220

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

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

2223 :class:`_expression.CTE`.

2224

2225 This method is a CTE-specific specialization of the

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

2227

2228 .. seealso::

2229

2230 :ref:`tutorial_using_aliases`

2231

2232 :func:`_expression.alias`

2233

2234 """

2235 return CTE._construct(

2236 self.element,

2237 name=name,

2238 recursive=self.recursive,

2239 nesting=self.nesting,

2240 _cte_alias=self,

2241 _prefixes=self._prefixes,

2242 _suffixes=self._suffixes,

2243 )

2244

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

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

2247 of the original CTE against the given selectables provided

2248 as positional arguments.

2249

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

2251 UNION.

2252

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

2254

2255 .. seealso::

2256

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

2258

2259 """

2260 assert is_select_statement(

2261 self.element

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

2263

2264 return CTE._construct(

2265 self.element.union(*other),

2266 name=self.name,

2267 recursive=self.recursive,

2268 nesting=self.nesting,

2269 _restates=self,

2270 _prefixes=self._prefixes,

2271 _suffixes=self._suffixes,

2272 )

2273

2274 def union_all(

2275 self, *other: _SelectStatementForCompoundArgument[Any]

2276 ) -> CTE:

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

2278 of the original CTE against the given selectables provided

2279 as positional arguments.

2280

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

2282 UNION.

2283

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

2285

2286 .. seealso::

2287

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

2289

2290 """

2291

2292 assert is_select_statement(

2293 self.element

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

2295

2296 return CTE._construct(

2297 self.element.union_all(*other),

2298 name=self.name,

2299 recursive=self.recursive,

2300 nesting=self.nesting,

2301 _restates=self,

2302 _prefixes=self._prefixes,

2303 _suffixes=self._suffixes,

2304 )

2305

2306 def _get_reference_cte(self) -> CTE:

2307 """

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

2309 Updated CTEs should still refer to the original CTE.

2310 This function returns this reference identifier.

2311 """

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

2313

2314

2315class _CTEOpts(NamedTuple):

2316 nesting: bool

2317

2318

2319class _ColumnsPlusNames(NamedTuple):

2320 required_label_name: Optional[str]

2321 """

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

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

2324 """

2325

2326 proxy_key: Optional[str]

2327 """

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

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

2330 select.selected_columns collection

2331 """

2332

2333 fallback_label_name: Optional[str]

2334 """

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

2336 we have to render a label even though

2337 required_label_name was not given

2338 """

2339

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

2341 """

2342 the ColumnElement itself

2343 """

2344

2345 repeated: bool

2346 """

2347 True if this is a duplicate of a previous column

2348 in the list of columns

2349 """

2350

2351

2352class SelectsRows(ReturnsRows):

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

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

2355

2356 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2357

2358 def _generate_columns_plus_names(

2359 self,

2360 anon_for_dupe_key: bool,

2361 cols: Optional[_SelectIterable] = None,

2362 ) -> List[_ColumnsPlusNames]:

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

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

2365 on a :class:`.FromClause`.

2366

2367 This is distinct from the _column_naming_convention generator that's

2368 intended for population of the Select.selected_columns collection,

2369 different rules. the collection returned here calls upon the

2370 _column_naming_convention as well.

2371

2372 """

2373

2374 if cols is None:

2375 cols = self._all_selected_columns

2376

2377 key_naming_convention = SelectState._column_naming_convention(

2378 self._label_style

2379 )

2380

2381 names = {}

2382

2383 result: List[_ColumnsPlusNames] = []

2384 result_append = result.append

2385

2386 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2387 label_style_none = self._label_style is LABEL_STYLE_NONE

2388

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

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

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

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

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

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

2395 dedupe_hash = 1

2396

2397 for c in cols:

2398 repeated = False

2399

2400 if not c._render_label_in_columns_clause:

2401 effective_name = required_label_name = fallback_label_name = (

2402 None

2403 )

2404 elif label_style_none:

2405 if TYPE_CHECKING:

2406 assert is_column_element(c)

2407

2408 effective_name = required_label_name = None

2409 fallback_label_name = c._non_anon_label or c._anon_name_label

2410 else:

2411 if TYPE_CHECKING:

2412 assert is_column_element(c)

2413

2414 if table_qualified:

2415 required_label_name = effective_name = (

2416 fallback_label_name

2417 ) = c._tq_label

2418 else:

2419 effective_name = fallback_label_name = c._non_anon_label

2420 required_label_name = None

2421

2422 if effective_name is None:

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

2424 # not need _expression_label but it isn't

2425 # giving us a clue when to use anon_label instead

2426 expr_label = c._expression_label

2427 if expr_label is None:

2428 repeated = c._anon_name_label in names

2429 names[c._anon_name_label] = c

2430 effective_name = required_label_name = None

2431

2432 if repeated:

2433 # here, "required_label_name" is sent as

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

2435 if table_qualified:

2436 fallback_label_name = (

2437 c._dedupe_anon_tq_label_idx(dedupe_hash)

2438 )

2439 dedupe_hash += 1

2440 else:

2441 fallback_label_name = c._dedupe_anon_label_idx(

2442 dedupe_hash

2443 )

2444 dedupe_hash += 1

2445 else:

2446 fallback_label_name = c._anon_name_label

2447 else:

2448 required_label_name = effective_name = (

2449 fallback_label_name

2450 ) = expr_label

2451

2452 if effective_name is not None:

2453 if TYPE_CHECKING:

2454 assert is_column_element(c)

2455

2456 if effective_name in names:

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

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

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

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

2461 # different column under the same name. apply

2462 # disambiguating label

2463 if table_qualified:

2464 required_label_name = fallback_label_name = (

2465 c._anon_tq_label

2466 )

2467 else:

2468 required_label_name = fallback_label_name = (

2469 c._anon_name_label

2470 )

2471

2472 if anon_for_dupe_key and required_label_name in names:

2473 # here, c._anon_tq_label is definitely unique to

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

2475 # this should always be true.

2476 # this is also an infrequent codepath because

2477 # you need two levels of duplication to be here

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

2479

2480 # the column under the disambiguating label is

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

2482 # subsequent occurrences of the column so that the

2483 # original stays non-ambiguous

2484 if table_qualified:

2485 required_label_name = fallback_label_name = (

2486 c._dedupe_anon_tq_label_idx(dedupe_hash)

2487 )

2488 dedupe_hash += 1

2489 else:

2490 required_label_name = fallback_label_name = (

2491 c._dedupe_anon_label_idx(dedupe_hash)

2492 )

2493 dedupe_hash += 1

2494 repeated = True

2495 else:

2496 names[required_label_name] = c

2497 elif anon_for_dupe_key:

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

2499 # label so that the original stays non-ambiguous

2500 if table_qualified:

2501 required_label_name = fallback_label_name = (

2502 c._dedupe_anon_tq_label_idx(dedupe_hash)

2503 )

2504 dedupe_hash += 1

2505 else:

2506 required_label_name = fallback_label_name = (

2507 c._dedupe_anon_label_idx(dedupe_hash)

2508 )

2509 dedupe_hash += 1

2510 repeated = True

2511 else:

2512 names[effective_name] = c

2513

2514 result_append(

2515 _ColumnsPlusNames(

2516 required_label_name,

2517 key_naming_convention(c),

2518 fallback_label_name,

2519 c,

2520 repeated,

2521 )

2522 )

2523

2524 return result

2525

2526

2527class HasCTE(roles.HasCTERole, SelectsRows):

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

2529

2530 _has_ctes_traverse_internals: _TraverseInternalsType = [

2531 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2532 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2533 ]

2534

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

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

2537

2538 name_cte_columns: bool = False

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

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

2541

2542 .. versionadded:: 2.0.42

2543

2544 """

2545

2546 @_generative

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

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

2549

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

2551 the parent statement such that they will each be unconditionally

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

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

2554

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

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

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

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

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

2560 statement.

2561

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

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

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

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

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

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

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

2569 larger statement.

2570

2571 E.g.::

2572

2573 from sqlalchemy import table, column, select

2574

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

2576

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

2578

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

2580

2581 Would render:

2582

2583 .. sourcecode:: sql

2584

2585 WITH anon_1 AS (

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

2587 )

2588 SELECT t.c1, t.c2

2589 FROM t

2590

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

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

2593 statement.

2594

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

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

2597

2598 from sqlalchemy import table, column

2599 from sqlalchemy.dialects.postgresql import insert

2600

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

2602

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

2604

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

2606 update_statement = insert_stmt.on_conflict_do_update(

2607 index_elements=[t.c.c1],

2608 set_={

2609 "c1": insert_stmt.excluded.c1,

2610 "c2": insert_stmt.excluded.c2,

2611 },

2612 ).add_cte(delete_statement_cte)

2613

2614 print(update_statement)

2615

2616 The above statement renders as:

2617

2618 .. sourcecode:: sql

2619

2620 WITH deletions AS (

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

2622 )

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

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

2625

2626 .. versionadded:: 1.4.21

2627

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

2629

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

2631

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

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

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

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

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

2637 this statement when this flag is given.

2638

2639 .. versionadded:: 2.0

2640

2641 .. seealso::

2642

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

2644

2645

2646 """ # noqa: E501

2647 opt = _CTEOpts(nest_here)

2648 for cte in ctes:

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

2650 self._independent_ctes += (cte,)

2651 self._independent_ctes_opts += (opt,)

2652 return self

2653

2654 def cte(

2655 self,

2656 name: Optional[str] = None,

2657 recursive: bool = False,

2658 nesting: bool = False,

2659 ) -> CTE:

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

2661 or Common Table Expression instance.

2662

2663 Common table expressions are a SQL standard whereby SELECT

2664 statements can draw upon secondary statements specified along

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

2666 Special semantics regarding UNION can also be employed to

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

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

2669

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

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

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

2673 CTE rows.

2674

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

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

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

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

2679

2680 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2682 method may be

2683 used to establish these.

2684

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

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

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

2688 compile time.

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

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

2691 conjunction with UNION ALL in order to derive rows

2692 from those already selected.

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

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

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

2696 :paramref:`.HasCTE.add_cte.nest_here`

2697 parameter may also be used to more carefully

2698 control the exact placement of a particular CTE.

2699

2700 .. versionadded:: 1.4.24

2701

2702 .. seealso::

2703

2704 :meth:`.HasCTE.add_cte`

2705

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

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

2708 as well as additional examples.

2709

2710 Example 1, non recursive::

2711

2712 from sqlalchemy import (

2713 Table,

2714 Column,

2715 String,

2716 Integer,

2717 MetaData,

2718 select,

2719 func,

2720 )

2721

2722 metadata = MetaData()

2723

2724 orders = Table(

2725 "orders",

2726 metadata,

2727 Column("region", String),

2728 Column("amount", Integer),

2729 Column("product", String),

2730 Column("quantity", Integer),

2731 )

2732

2733 regional_sales = (

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

2735 .group_by(orders.c.region)

2736 .cte("regional_sales")

2737 )

2738

2739

2740 top_regions = (

2741 select(regional_sales.c.region)

2742 .where(

2743 regional_sales.c.total_sales

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

2745 )

2746 .cte("top_regions")

2747 )

2748

2749 statement = (

2750 select(

2751 orders.c.region,

2752 orders.c.product,

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

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

2755 )

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

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

2758 )

2759

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

2761

2762 Example 2, WITH RECURSIVE::

2763

2764 from sqlalchemy import (

2765 Table,

2766 Column,

2767 String,

2768 Integer,

2769 MetaData,

2770 select,

2771 func,

2772 )

2773

2774 metadata = MetaData()

2775

2776 parts = Table(

2777 "parts",

2778 metadata,

2779 Column("part", String),

2780 Column("sub_part", String),

2781 Column("quantity", Integer),

2782 )

2783

2784 included_parts = (

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

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

2787 .cte(recursive=True)

2788 )

2789

2790

2791 incl_alias = included_parts.alias()

2792 parts_alias = parts.alias()

2793 included_parts = included_parts.union_all(

2794 select(

2795 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2797 )

2798

2799 statement = select(

2800 included_parts.c.sub_part,

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

2802 ).group_by(included_parts.c.sub_part)

2803

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

2805

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

2807

2808 from datetime import date

2809 from sqlalchemy import (

2810 MetaData,

2811 Table,

2812 Column,

2813 Integer,

2814 Date,

2815 select,

2816 literal,

2817 and_,

2818 exists,

2819 )

2820

2821 metadata = MetaData()

2822

2823 visitors = Table(

2824 "visitors",

2825 metadata,

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

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

2828 Column("count", Integer),

2829 )

2830

2831 # add 5 visitors for the product_id == 1

2832 product_id = 1

2833 day = date.today()

2834 count = 5

2835

2836 update_cte = (

2837 visitors.update()

2838 .where(

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

2840 )

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

2842 .returning(literal(1))

2843 .cte("update_cte")

2844 )

2845

2846 upsert = visitors.insert().from_select(

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

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

2849 ~exists(update_cte.select())

2850 ),

2851 )

2852

2853 connection.execute(upsert)

2854

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

2856

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

2858

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

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

2861 "value_a", nesting=True

2862 )

2863

2864 # Nesting CTEs takes ascendency locally

2865 # over the CTEs at a higher level

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

2867

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

2869

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

2871 shown with inline parameters below as:

2872

2873 .. sourcecode:: sql

2874

2875 WITH

2876 value_a AS

2877 (SELECT 'root' AS n),

2878 value_b AS

2879 (WITH value_a AS

2880 (SELECT 'nesting' AS n)

2881 SELECT value_a.n AS n FROM value_a)

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

2883 FROM value_a, value_b

2884

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

2886 as follows (SQLAlchemy 2.0 and above)::

2887

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

2889

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

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

2892

2893 # Nesting CTEs takes ascendency locally

2894 # over the CTEs at a higher level

2895 value_b = (

2896 select(value_a_nested.c.n)

2897 .add_cte(value_a_nested, nest_here=True)

2898 .cte("value_b")

2899 )

2900

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

2902

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

2904

2905 edge = Table(

2906 "edge",

2907 metadata,

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

2909 Column("left", Integer),

2910 Column("right", Integer),

2911 )

2912

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

2914

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

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

2917 )

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

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

2920 )

2921

2922 subgraph_cte = root_node.union(left_edge, right_edge)

2923

2924 subgraph = select(subgraph_cte)

2925

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

2927

2928 .. sourcecode:: sql

2929

2930 WITH RECURSIVE nodes(node) AS (

2931 SELECT 1 AS node

2932 UNION

2933 SELECT edge."left" AS "left"

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

2935 UNION

2936 SELECT edge."right" AS "right"

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

2938 )

2939 SELECT nodes.node FROM nodes

2940

2941 .. seealso::

2942

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

2944 :meth:`_expression.HasCTE.cte`.

2945

2946 """ # noqa: E501

2947 return CTE._construct(

2948 self, name=name, recursive=recursive, nesting=nesting

2949 )

2950

2951

2952class Subquery(AliasedReturnsRows):

2953 """Represent a subquery of a SELECT.

2954

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

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

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

2958 :class:`_expression.SelectBase` subclass

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

2960 :class:`_expression.CompoundSelect`, and

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

2962 it represents the

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

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

2965

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

2967 :class:`_expression.Alias`

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

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

2970 :class:`_expression.Alias` always

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

2972 :class:`.Subquery`

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

2974

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

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

2977 statement.

2978

2979 """

2980

2981 __visit_name__ = "subquery"

2982

2983 _is_subquery = True

2984

2985 inherit_cache = True

2986

2987 element: SelectBase

2988

2989 @classmethod

2990 def _factory(

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

2992 ) -> Subquery:

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

2994

2995 return coercions.expect(

2996 roles.SelectStatementRole, selectable

2997 ).subquery(name=name)

2998

2999 @util.deprecated(

3000 "1.4",

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

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

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

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

3005 ":func:`_expression.select` "

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

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

3008 )

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

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

3011

3012

3013class FromGrouping(GroupedElement, FromClause):

3014 """Represent a grouping of a FROM clause"""

3015

3016 _traverse_internals: _TraverseInternalsType = [

3017 ("element", InternalTraversal.dp_clauseelement)

3018 ]

3019

3020 element: FromClause

3021

3022 def __init__(self, element: FromClause):

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

3024

3025 @util.ro_non_memoized_property

3026 def columns(

3027 self,

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

3029 return self.element.columns

3030

3031 @util.ro_non_memoized_property

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

3033 return self.element.columns

3034

3035 @property

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

3037 return self.element.primary_key

3038

3039 @property

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

3041 return self.element.foreign_keys

3042

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

3044 return self.element.is_derived_from(fromclause)

3045

3046 def alias(

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

3048 ) -> NamedFromGrouping:

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

3050

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

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

3053

3054 @util.ro_non_memoized_property

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

3056 return self.element._hide_froms

3057

3058 @util.ro_non_memoized_property

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

3060 return self.element._from_objects

3061

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

3063 return {"element": self.element}

3064

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

3066 self.element = state["element"]

3067

3068 if TYPE_CHECKING:

3069

3070 def self_group(

3071 self, against: Optional[OperatorType] = None

3072 ) -> Self: ...

3073

3074

3075class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3077

3078 .. versionadded:: 2.0

3079

3080 """

3081

3082 inherit_cache = True

3083

3084 if TYPE_CHECKING:

3085

3086 def self_group(

3087 self, against: Optional[OperatorType] = None

3088 ) -> Self: ...

3089

3090

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

3092 """Represents a minimal "table" construct.

3093

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

3095 collection of columns, which are typically produced

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

3097

3098 from sqlalchemy import table, column

3099

3100 user = table(

3101 "user",

3102 column("id"),

3103 column("name"),

3104 column("description"),

3105 )

3106

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

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

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

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

3111

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

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

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

3115 It's useful

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

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

3118 is not on hand.

3119

3120 """

3121

3122 __visit_name__ = "table"

3123

3124 _traverse_internals: _TraverseInternalsType = [

3125 (

3126 "columns",

3127 InternalTraversal.dp_fromclause_canonical_column_collection,

3128 ),

3129 ("name", InternalTraversal.dp_string),

3130 ("schema", InternalTraversal.dp_string),

3131 ]

3132

3133 _is_table = True

3134

3135 fullname: str

3136

3137 implicit_returning = False

3138 """:class:`_expression.TableClause`

3139 doesn't support having a primary key or column

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

3141

3142 _columns: DedupeColumnCollection[ColumnClause[Any]]

3143

3144 @util.ro_memoized_property

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

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

3147 return None

3148

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

3150 super().__init__()

3151 self.name = name

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

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

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

3155 for c in columns:

3156 self.append_column(c)

3157

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

3159 if schema is not None:

3160 self.schema = schema

3161 if self.schema is not None:

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

3163 else:

3164 self.fullname = self.name

3165 if kw:

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

3167

3168 if TYPE_CHECKING:

3169

3170 @util.ro_non_memoized_property

3171 def columns(

3172 self,

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

3174

3175 @util.ro_non_memoized_property

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

3177

3178 def __str__(self) -> str:

3179 if self.schema is not None:

3180 return self.schema + "." + self.name

3181 else:

3182 return self.name

3183

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

3185 pass

3186

3187 @util.ro_memoized_property

3188 def description(self) -> str:

3189 return self.name

3190

3191 def _insert_col_impl(

3192 self,

3193 c: ColumnClause[Any],

3194 *,

3195 index: Optional[int] = None,

3196 ) -> None:

3197 existing = c.table

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

3199 raise exc.ArgumentError(

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

3201 % (c.key, existing)

3202 )

3203 self._columns.add(c, index=index)

3204 c.table = self

3205

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

3207 self._insert_col_impl(c)

3208

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

3210 self._insert_col_impl(c, index=index)

3211

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

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

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

3215 :class:`_expression.TableClause`.

3216

3217 E.g.::

3218

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

3220

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

3222

3223 """

3224

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

3226

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

3228 def update(self) -> Update:

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

3230 :class:`_expression.TableClause`.

3231

3232 E.g.::

3233

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

3235

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

3237

3238 """

3239 return util.preloaded.sql_dml.Update(

3240 self,

3241 )

3242

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

3244 def delete(self) -> Delete:

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

3246 :class:`_expression.TableClause`.

3247

3248 E.g.::

3249

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

3251

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

3253

3254 """

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

3256

3257 @util.ro_non_memoized_property

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

3259 return [self]

3260

3261

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

3263

3264

3265class ForUpdateArg(ClauseElement):

3266 _traverse_internals: _TraverseInternalsType = [

3267 ("of", InternalTraversal.dp_clauseelement_list),

3268 ("nowait", InternalTraversal.dp_boolean),

3269 ("read", InternalTraversal.dp_boolean),

3270 ("skip_locked", InternalTraversal.dp_boolean),

3271 ("key_share", InternalTraversal.dp_boolean),

3272 ]

3273

3274 of: Optional[Sequence[ClauseElement]]

3275 nowait: bool

3276 read: bool

3277 skip_locked: bool

3278

3279 @classmethod

3280 def _from_argument(

3281 cls, with_for_update: ForUpdateParameter

3282 ) -> Optional[ForUpdateArg]:

3283 if isinstance(with_for_update, ForUpdateArg):

3284 return with_for_update

3285 elif with_for_update in (None, False):

3286 return None

3287 elif with_for_update is True:

3288 return ForUpdateArg()

3289 else:

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

3291

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

3293 return (

3294 isinstance(other, ForUpdateArg)

3295 and other.nowait == self.nowait

3296 and other.read == self.read

3297 and other.skip_locked == self.skip_locked

3298 and other.key_share == self.key_share

3299 and other.of is self.of

3300 )

3301

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

3303 return not self.__eq__(other)

3304

3305 def __hash__(self) -> int:

3306 return id(self)

3307

3308 def __init__(

3309 self,

3310 *,

3311 nowait: bool = False,

3312 read: bool = False,

3313 of: Optional[_ForUpdateOfArgument] = None,

3314 skip_locked: bool = False,

3315 key_share: bool = False,

3316 ):

3317 """Represents arguments specified to

3318 :meth:`_expression.Select.for_update`.

3319

3320 """

3321

3322 self.nowait = nowait

3323 self.read = read

3324 self.skip_locked = skip_locked

3325 self.key_share = key_share

3326 if of is not None:

3327 self.of = [

3328 coercions.expect(roles.ColumnsClauseRole, elem)

3329 for elem in util.to_list(of)

3330 ]

3331 else:

3332 self.of = None

3333

3334

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

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

3337 in a statement.

3338

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

3340 :func:`_expression.values` function.

3341

3342 .. versionadded:: 1.4

3343

3344 """

3345

3346 __visit_name__ = "values"

3347

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

3349

3350 _unnamed: bool

3351 _traverse_internals: _TraverseInternalsType = [

3352 ("_column_args", InternalTraversal.dp_clauseelement_list),

3353 ("_data", InternalTraversal.dp_dml_multi_values),

3354 ("name", InternalTraversal.dp_string),

3355 ("literal_binds", InternalTraversal.dp_boolean),

3356 ] + HasCTE._has_ctes_traverse_internals

3357

3358 name_cte_columns = True

3359

3360 def __init__(

3361 self,

3362 *columns: ColumnClause[Any],

3363 name: Optional[str] = None,

3364 literal_binds: bool = False,

3365 ):

3366 super().__init__()

3367 self._column_args = columns

3368

3369 if name is None:

3370 self._unnamed = True

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

3372 else:

3373 self._unnamed = False

3374 self.name = name

3375 self.literal_binds = literal_binds

3376 self.named_with_column = not self._unnamed

3377

3378 @property

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

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

3381

3382 @util.ro_non_memoized_property

3383 def _all_selected_columns(self) -> _SelectIterable:

3384 return self._column_args

3385

3386 @_generative

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

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

3389 construct that is a copy of this

3390 one with the given name.

3391

3392 This method is a VALUES-specific specialization of the

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

3394

3395 .. seealso::

3396

3397 :ref:`tutorial_using_aliases`

3398

3399 :func:`_expression.alias`

3400

3401 """

3402 non_none_name: str

3403

3404 if name is None:

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

3406 else:

3407 non_none_name = name

3408

3409 self.name = non_none_name

3410 self.named_with_column = True

3411 self._unnamed = False

3412 return self

3413

3414 @_generative

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

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

3417 so that

3418 it renders as LATERAL.

3419

3420 .. seealso::

3421

3422 :func:`_expression.lateral`

3423

3424 """

3425 non_none_name: str

3426

3427 if name is None:

3428 non_none_name = self.name

3429 else:

3430 non_none_name = name

3431

3432 self._is_lateral = True

3433 self.name = non_none_name

3434 self._unnamed = False

3435 return self

3436

3437 @_generative

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

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

3440 adding the given data to the data list.

3441

3442 E.g.::

3443

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

3445

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

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

3448 constructor.

3449

3450 """

3451

3452 self._data += (values,)

3453 return self

3454

3455 def scalar_values(self) -> ScalarValues:

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

3457 COLUMN element in a statement.

3458

3459 .. versionadded:: 2.0.0b4

3460

3461 """

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

3463

3464 def _populate_column_collection(

3465 self,

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

3467 primary_key: ColumnSet,

3468 foreign_keys: Set[KeyedColumnElement[Any]],

3469 ) -> None:

3470 for c in self._column_args:

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

3472 _, c = c._make_proxy(

3473 self, primary_key=primary_key, foreign_keys=foreign_keys

3474 )

3475 else:

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

3477 # no memoizations of other FROM clauses.

3478 # see test_values.py -> test_auto_proxy_select_direct_col

3479 c._reset_memoizations()

3480 columns.add(c)

3481 c.table = self

3482

3483 @util.ro_non_memoized_property

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

3485 return [self]

3486

3487

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

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

3490 COLUMN element in a statement.

3491

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

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

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

3495 an ``IN`` or ``NOT IN`` condition.

3496

3497 .. versionadded:: 2.0.0b4

3498

3499 """

3500

3501 __visit_name__ = "scalar_values"

3502

3503 _traverse_internals: _TraverseInternalsType = [

3504 ("_column_args", InternalTraversal.dp_clauseelement_list),

3505 ("_data", InternalTraversal.dp_dml_multi_values),

3506 ("literal_binds", InternalTraversal.dp_boolean),

3507 ]

3508

3509 def __init__(

3510 self,

3511 columns: Sequence[ColumnClause[Any]],

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

3513 literal_binds: bool,

3514 ):

3515 super().__init__()

3516 self._column_args = columns

3517 self._data = data

3518 self.literal_binds = literal_binds

3519

3520 @property

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

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

3523

3524 def __clause_element__(self) -> ScalarValues:

3525 return self

3526

3527 if TYPE_CHECKING:

3528

3529 def self_group(

3530 self, against: Optional[OperatorType] = None

3531 ) -> Self: ...

3532

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

3534

3535

3536class SelectBase(

3537 roles.SelectStatementRole,

3538 roles.DMLSelectRole,

3539 roles.CompoundElementRole,

3540 roles.InElementRole,

3541 HasCTE,

3542 SupportsCloneAnnotations,

3543 Selectable,

3544):

3545 """Base class for SELECT statements.

3546

3547

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

3549 :class:`_expression.CompoundSelect` and

3550 :class:`_expression.TextualSelect`.

3551

3552

3553 """

3554

3555 _is_select_base = True

3556 is_select = True

3557

3558 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3559

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

3561 self._reset_memoizations()

3562

3563 @util.ro_non_memoized_property

3564 def selected_columns(

3565 self,

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

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

3568 representing the columns that

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

3570

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

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

3573 within this collection cannot be directly nested inside another SELECT

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

3575 necessary parenthesization required by SQL.

3576

3577 .. note::

3578

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

3580 include expressions established in the columns clause using the

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

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

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

3584 construct.

3585

3586 .. seealso::

3587

3588 :attr:`_sql.Select.selected_columns`

3589

3590 .. versionadded:: 1.4

3591

3592 """

3593 raise NotImplementedError()

3594

3595 def _generate_fromclause_column_proxies(

3596 self,

3597 subquery: FromClause,

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

3599 primary_key: ColumnSet,

3600 foreign_keys: Set[KeyedColumnElement[Any]],

3601 *,

3602 proxy_compound_columns: Optional[

3603 Iterable[Sequence[ColumnElement[Any]]]

3604 ] = None,

3605 ) -> None:

3606 raise NotImplementedError()

3607

3608 @util.ro_non_memoized_property

3609 def _all_selected_columns(self) -> _SelectIterable:

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

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

3612 constructs.

3613

3614 .. versionadded:: 1.4.12

3615

3616 .. seealso::

3617

3618 :attr:`_sql.SelectBase.exported_columns`

3619

3620 """

3621 raise NotImplementedError()

3622

3623 @property

3624 def exported_columns(

3625 self,

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

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

3628 that represents the "exported"

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

3630 :class:`_sql.TextClause` constructs.

3631

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

3633 object are synonymous

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

3635

3636 .. versionadded:: 1.4

3637

3638 .. seealso::

3639

3640 :attr:`_expression.Select.exported_columns`

3641

3642 :attr:`_expression.Selectable.exported_columns`

3643

3644 :attr:`_expression.FromClause.exported_columns`

3645

3646

3647 """

3648 return self.selected_columns.as_readonly()

3649

3650 def get_label_style(self) -> SelectLabelStyle:

3651 """

3652 Retrieve the current label style.

3653

3654 Implemented by subclasses.

3655

3656 """

3657 raise NotImplementedError()

3658

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

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

3661

3662 Implemented by subclasses.

3663

3664 """

3665

3666 raise NotImplementedError()

3667

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

3669 raise NotImplementedError()

3670

3671 @util.deprecated(

3672 "1.4",

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

3674 "method is deprecated and will be "

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

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

3677 )

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

3679 return self.scalar_subquery()

3680

3681 def exists(self) -> Exists:

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

3683 which can be used as a column expression.

3684

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

3686

3687 .. seealso::

3688

3689 :func:`_sql.exists`

3690

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

3692

3693 .. versionadded:: 1.4

3694

3695 """

3696 return Exists(self)

3697

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

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

3700 used as a column expression.

3701

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

3703

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

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

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

3707 an enclosing SELECT.

3708

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

3710 subquery that can be produced using the

3711 :meth:`_expression.SelectBase.subquery`

3712 method.

3713

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

3715 :meth:`_expression.SelectBase.scalar_subquery`.

3716

3717 .. seealso::

3718

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

3720

3721 """

3722 if self._label_style is not LABEL_STYLE_NONE:

3723 self = self.set_label_style(LABEL_STYLE_NONE)

3724

3725 return ScalarSelect(self)

3726

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

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

3729 subquery with a label.

3730

3731 .. seealso::

3732

3733 :meth:`_expression.SelectBase.scalar_subquery`.

3734

3735 """

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

3737

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

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

3740

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

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

3743

3744 .. seealso::

3745

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

3747

3748 """

3749 return Lateral._factory(self, name)

3750

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

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

3753

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

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

3756 SELECT statement.

3757

3758 Given a SELECT statement such as::

3759

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

3761

3762 The above statement might look like:

3763

3764 .. sourcecode:: sql

3765

3766 SELECT table.id, table.name FROM table

3767

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

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

3770 a named sub-element::

3771

3772 subq = stmt.subquery()

3773 new_stmt = select(subq)

3774

3775 The above renders as:

3776

3777 .. sourcecode:: sql

3778

3779 SELECT anon_1.id, anon_1.name

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

3781

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

3783 is equivalent to calling

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

3785 method on a FROM object; however,

3786 as a :class:`_expression.SelectBase`

3787 object is not directly FROM object,

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

3789 method provides clearer semantics.

3790

3791 .. versionadded:: 1.4

3792

3793 """

3794

3795 return Subquery._construct(

3796 self._ensure_disambiguated_names(), name=name

3797 )

3798

3799 def _ensure_disambiguated_names(self) -> Self:

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

3801 disambiguated in some way, if possible.

3802

3803 """

3804

3805 raise NotImplementedError()

3806

3807 def alias(

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

3809 ) -> Subquery:

3810 """Return a named subquery against this

3811 :class:`_expression.SelectBase`.

3812

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

3814 :class:`_expression.FromClause`),

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

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

3817 :class:`_expression.FromClause`.

3818

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

3820 method is now

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

3822

3823 """

3824 return self.subquery(name=name)

3825

3826

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

3828

3829

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

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

3832

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

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

3835 compound selects.

3836

3837 """

3838

3839 __visit_name__ = "select_statement_grouping"

3840 _traverse_internals: _TraverseInternalsType = [

3841 ("element", InternalTraversal.dp_clauseelement)

3842 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3843

3844 _is_select_container = True

3845

3846 element: _SB

3847

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

3849 self.element = cast(

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

3851 )

3852

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

3854 new_element = self.element._ensure_disambiguated_names()

3855 if new_element is not self.element:

3856 return SelectStatementGrouping(new_element)

3857 else:

3858 return self

3859

3860 def get_label_style(self) -> SelectLabelStyle:

3861 return self.element.get_label_style()

3862

3863 def set_label_style(

3864 self, label_style: SelectLabelStyle

3865 ) -> SelectStatementGrouping[_SB]:

3866 return SelectStatementGrouping(

3867 self.element.set_label_style(label_style)

3868 )

3869

3870 @property

3871 def select_statement(self) -> _SB:

3872 return self.element

3873

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

3875 return self

3876

3877 if TYPE_CHECKING:

3878

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

3880

3881 # def _generate_columns_plus_names(

3882 # self, anon_for_dupe_key: bool

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

3884 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3885

3886 def _generate_fromclause_column_proxies(

3887 self,

3888 subquery: FromClause,

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

3890 primary_key: ColumnSet,

3891 foreign_keys: Set[KeyedColumnElement[Any]],

3892 *,

3893 proxy_compound_columns: Optional[

3894 Iterable[Sequence[ColumnElement[Any]]]

3895 ] = None,

3896 ) -> None:

3897 self.element._generate_fromclause_column_proxies(

3898 subquery,

3899 columns,

3900 proxy_compound_columns=proxy_compound_columns,

3901 primary_key=primary_key,

3902 foreign_keys=foreign_keys,

3903 )

3904

3905 @util.ro_non_memoized_property

3906 def _all_selected_columns(self) -> _SelectIterable:

3907 return self.element._all_selected_columns

3908

3909 @util.ro_non_memoized_property

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

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

3912 representing the columns that

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

3914 :class:`_sql.TextClause` constructs.

3915

3916 .. versionadded:: 1.4

3917

3918 .. seealso::

3919

3920 :attr:`_sql.Select.selected_columns`

3921

3922 """

3923 return self.element.selected_columns

3924

3925 @util.ro_non_memoized_property

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

3927 return self.element._from_objects

3928

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

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

3931 raise NotImplementedError

3932

3933

3934class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3936 added.

3937

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

3939 :class:`_expression.CompoundSelect`

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

3941 rendering can be controlled. Compare to

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

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

3944 and is also a SELECT construct,

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

3946 only wrapped as a subquery.

3947

3948 """

3949

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

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

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

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

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

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

3956 _for_update_arg: Optional[ForUpdateArg] = None

3957

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

3959 self._label_style = _label_style

3960

3961 @_generative

3962 def with_for_update(

3963 self,

3964 *,

3965 nowait: bool = False,

3966 read: bool = False,

3967 of: Optional[_ForUpdateOfArgument] = None,

3968 skip_locked: bool = False,

3969 key_share: bool = False,

3970 ) -> Self:

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

3972 :class:`_expression.GenerativeSelect`.

3973

3974 E.g.::

3975

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

3977

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

3979 render a statement like:

3980

3981 .. sourcecode:: sql

3982

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

3984

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

3986 would produce:

3987

3988 .. sourcecode:: sql

3989

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

3991

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

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

3994 provided which allow for common database-specific

3995 variants.

3996

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

3998 Database and PostgreSQL dialects.

3999

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

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

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

4003

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

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

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

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

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

4009 table or as a column depending on backend.

4010

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

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

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

4014

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

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

4017 on the PostgreSQL dialect.

4018

4019 """

4020 self._for_update_arg = ForUpdateArg(

4021 nowait=nowait,

4022 read=read,

4023 of=of,

4024 skip_locked=skip_locked,

4025 key_share=key_share,

4026 )

4027 return self

4028

4029 def get_label_style(self) -> SelectLabelStyle:

4030 """

4031 Retrieve the current label style.

4032

4033 .. versionadded:: 1.4

4034

4035 """

4036 return self._label_style

4037

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

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

4040

4041 There are three "label styles" available,

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

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

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

4045 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4046

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

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

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

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

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

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

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

4054 that the impact of this labeling is minimal.

4055

4056 The rationale for disambiguation is mostly so that all column

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

4058 collection when a subquery is created.

4059

4060 .. versionadded:: 1.4 - the

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

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

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

4064

4065 .. seealso::

4066

4067 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4068

4069 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4070

4071 :data:`_sql.LABEL_STYLE_NONE`

4072

4073 :data:`_sql.LABEL_STYLE_DEFAULT`

4074

4075 """

4076 if self._label_style is not style:

4077 self = self._generate()

4078 self._label_style = style

4079 return self

4080

4081 @property

4082 def _group_by_clause(self) -> ClauseList:

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

4084 return ClauseList._construct_raw(

4085 operators.comma_op, self._group_by_clauses

4086 )

4087

4088 @property

4089 def _order_by_clause(self) -> ClauseList:

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

4091 return ClauseList._construct_raw(

4092 operators.comma_op, self._order_by_clauses

4093 )

4094

4095 def _offset_or_limit_clause(

4096 self,

4097 element: _LimitOffsetType,

4098 name: Optional[str] = None,

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

4100 ) -> ColumnElement[Any]:

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

4102

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

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

4105

4106 """

4107 return coercions.expect(

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

4109 )

4110

4111 @overload

4112 def _offset_or_limit_clause_asint(

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

4114 ) -> NoReturn: ...

4115

4116 @overload

4117 def _offset_or_limit_clause_asint(

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

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

4120

4121 def _offset_or_limit_clause_asint(

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

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

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

4125 integer.

4126

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

4128 parameter. Otherwise, a compilation error is raised.

4129

4130 """

4131 if clause is None:

4132 return None

4133 try:

4134 value = clause._limit_offset_value

4135 except AttributeError as err:

4136 raise exc.CompileError(

4137 "This SELECT structure does not use a simple "

4138 "integer value for %s" % attrname

4139 ) from err

4140 else:

4141 return util.asint(value)

4142

4143 @property

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

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

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

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

4148 isn't currently set to an integer.

4149

4150 """

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

4152

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

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

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

4156 """

4157 return isinstance(clause, _OffsetLimitParam)

4158

4159 @property

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

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

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

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

4164 offset isn't currently set to an integer.

4165

4166 """

4167 return self._offset_or_limit_clause_asint(

4168 self._offset_clause, "offset"

4169 )

4170

4171 @property

4172 def _has_row_limiting_clause(self) -> bool:

4173 return (

4174 self._limit_clause is not None

4175 or self._offset_clause is not None

4176 or self._fetch_clause is not None

4177 )

4178

4179 @_generative

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

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

4182 applied.

4183

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

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

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

4187 functionality.

4188

4189 .. note::

4190

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

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

4193

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

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

4196

4197 .. seealso::

4198

4199 :meth:`_sql.GenerativeSelect.fetch`

4200

4201 :meth:`_sql.GenerativeSelect.offset`

4202

4203 """

4204

4205 self._fetch_clause = self._fetch_clause_options = None

4206 self._limit_clause = self._offset_or_limit_clause(limit)

4207 return self

4208

4209 @_generative

4210 def fetch(

4211 self,

4212 count: _LimitOffsetType,

4213 with_ties: bool = False,

4214 percent: bool = False,

4215 **dialect_kw: Any,

4216 ) -> Self:

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

4218 applied.

4219

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

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

4222 select. This functionality is is currently implemented for Oracle

4223 Database, PostgreSQL, MSSQL.

4224

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

4226

4227 .. note::

4228

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

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

4231

4232 .. versionadded:: 1.4

4233

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

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

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

4237 Pass ``None`` to reset it.

4238

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

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

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

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

4243

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

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

4246

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

4248 may be accepted by dialects.

4249

4250 .. versionadded:: 2.0.41

4251

4252 .. seealso::

4253

4254 :meth:`_sql.GenerativeSelect.limit`

4255

4256 :meth:`_sql.GenerativeSelect.offset`

4257

4258 """

4259 self._validate_dialect_kwargs(dialect_kw)

4260 self._limit_clause = None

4261 if count is None:

4262 self._fetch_clause = self._fetch_clause_options = None

4263 else:

4264 self._fetch_clause = self._offset_or_limit_clause(count)

4265 self._fetch_clause_options = {

4266 "with_ties": with_ties,

4267 "percent": percent,

4268 }

4269 return self

4270

4271 @_generative

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

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

4274 applied.

4275

4276

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

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

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

4280 functionality.

4281

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

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

4284

4285 .. seealso::

4286

4287 :meth:`_sql.GenerativeSelect.limit`

4288

4289 :meth:`_sql.GenerativeSelect.fetch`

4290

4291 """

4292

4293 self._offset_clause = self._offset_or_limit_clause(offset)

4294 return self

4295

4296 @_generative

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

4298 def slice(

4299 self,

4300 start: int,

4301 stop: int,

4302 ) -> Self:

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

4304

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

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

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

4308 query.

4309

4310 For example, ::

4311

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

4313

4314 renders as

4315

4316 .. sourcecode:: sql

4317

4318 SELECT users.id AS users_id,

4319 users.name AS users_name

4320 FROM users ORDER BY users.id

4321 LIMIT ? OFFSET ?

4322 (2, 1)

4323

4324 .. note::

4325

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

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

4328

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

4330 method generalized from the ORM.

4331

4332 .. seealso::

4333

4334 :meth:`_sql.GenerativeSelect.limit`

4335

4336 :meth:`_sql.GenerativeSelect.offset`

4337

4338 :meth:`_sql.GenerativeSelect.fetch`

4339

4340 """

4341 sql_util = util.preloaded.sql_util

4342 self._fetch_clause = self._fetch_clause_options = None

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

4344 self._limit_clause, self._offset_clause, start, stop

4345 )

4346 return self

4347

4348 @_generative

4349 def order_by(

4350 self,

4351 __first: Union[

4352 Literal[None, _NoArg.NO_ARG],

4353 _ColumnExpressionOrStrLabelArgument[Any],

4354 ] = _NoArg.NO_ARG,

4355 /,

4356 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4357 ) -> Self:

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

4359 criteria applied.

4360

4361 e.g.::

4362

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

4364

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

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

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

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

4369

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

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

4372

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

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

4375

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

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

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

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

4380 label matching in ORDER BY and GROUP BY expressions.

4381

4382 .. seealso::

4383

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

4385

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

4387

4388 """

4389

4390 if not clauses and __first is None:

4391 self._order_by_clauses = ()

4392 elif __first is not _NoArg.NO_ARG:

4393 self._order_by_clauses += tuple(

4394 coercions.expect(

4395 roles.OrderByRole, clause, apply_propagate_attrs=self

4396 )

4397 for clause in (__first,) + clauses

4398 )

4399 return self

4400

4401 @_generative

4402 def group_by(

4403 self,

4404 __first: Union[

4405 Literal[None, _NoArg.NO_ARG],

4406 _ColumnExpressionOrStrLabelArgument[Any],

4407 ] = _NoArg.NO_ARG,

4408 /,

4409 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4410 ) -> Self:

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

4412 criterion applied.

4413

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

4415

4416 e.g.::

4417

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

4419

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

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

4422

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

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

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

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

4427 label matching in ORDER BY and GROUP BY expressions.

4428

4429 .. seealso::

4430

4431 :ref:`tutorial_group_by_w_aggregates` - in the

4432 :ref:`unified_tutorial`

4433

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

4435

4436 """ # noqa: E501

4437

4438 if not clauses and __first is None:

4439 self._group_by_clauses = ()

4440 elif __first is not _NoArg.NO_ARG:

4441 self._group_by_clauses += tuple(

4442 coercions.expect(

4443 roles.GroupByRole, clause, apply_propagate_attrs=self

4444 )

4445 for clause in (__first,) + clauses

4446 )

4447 return self

4448

4449

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

4451class CompoundSelectState(CompileState):

4452 @util.memoized_property

4453 def _label_resolve_dict(

4454 self,

4455 ) -> Tuple[

4456 Dict[str, ColumnElement[Any]],

4457 Dict[str, ColumnElement[Any]],

4458 Dict[str, ColumnElement[Any]],

4459 ]:

4460 # TODO: this is hacky and slow

4461 hacky_subquery = self.statement.subquery()

4462 hacky_subquery.named_with_column = False

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

4464 return d, d, d

4465

4466

4467class _CompoundSelectKeyword(Enum):

4468 UNION = "UNION"

4469 UNION_ALL = "UNION ALL"

4470 EXCEPT = "EXCEPT"

4471 EXCEPT_ALL = "EXCEPT ALL"

4472 INTERSECT = "INTERSECT"

4473 INTERSECT_ALL = "INTERSECT ALL"

4474

4475

4476class CompoundSelect(

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

4478):

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

4480 SELECT-based set operations.

4481

4482

4483 .. seealso::

4484

4485 :func:`_expression.union`

4486

4487 :func:`_expression.union_all`

4488

4489 :func:`_expression.intersect`

4490

4491 :func:`_expression.intersect_all`

4492

4493 :func:`_expression.except`

4494

4495 :func:`_expression.except_all`

4496

4497 """

4498

4499 __visit_name__ = "compound_select"

4500

4501 _traverse_internals: _TraverseInternalsType = (

4502 [

4503 ("selects", InternalTraversal.dp_clauseelement_list),

4504 ("_limit_clause", InternalTraversal.dp_clauseelement),

4505 ("_offset_clause", InternalTraversal.dp_clauseelement),

4506 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4507 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4508 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4509 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4510 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4511 ("keyword", InternalTraversal.dp_string),

4512 ]

4513 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4514 + HasCTE._has_ctes_traverse_internals

4515 + DialectKWArgs._dialect_kwargs_traverse_internals

4516 + Executable._executable_traverse_internals

4517 )

4518

4519 selects: List[SelectBase]

4520

4521 _is_from_container = True

4522 _auto_correlate = False

4523

4524 def __init__(

4525 self,

4526 keyword: _CompoundSelectKeyword,

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

4528 ):

4529 self.keyword = keyword

4530 self.selects = [

4531 coercions.expect(

4532 roles.CompoundElementRole, s, apply_propagate_attrs=self

4533 ).self_group(against=self)

4534 for s in selects

4535 ]

4536

4537 GenerativeSelect.__init__(self)

4538

4539 @classmethod

4540 def _create_union(

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

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

4543 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4544

4545 @classmethod

4546 def _create_union_all(

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

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

4549 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4550

4551 @classmethod

4552 def _create_except(

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

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

4555 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4556

4557 @classmethod

4558 def _create_except_all(

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

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

4561 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4562

4563 @classmethod

4564 def _create_intersect(

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

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

4567 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4568

4569 @classmethod

4570 def _create_intersect_all(

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

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

4573 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4574

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

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

4577

4578 def self_group(

4579 self, against: Optional[OperatorType] = None

4580 ) -> GroupedElement:

4581 return SelectStatementGrouping(self)

4582

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

4584 for s in self.selects:

4585 if s.is_derived_from(fromclause):

4586 return True

4587 return False

4588

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

4590 if self._label_style is not style:

4591 self = self._generate()

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

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

4594

4595 return self

4596

4597 def _ensure_disambiguated_names(self) -> Self:

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

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

4600 self = self._generate()

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

4602

4603 return self

4604

4605 def _generate_fromclause_column_proxies(

4606 self,

4607 subquery: FromClause,

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

4609 primary_key: ColumnSet,

4610 foreign_keys: Set[KeyedColumnElement[Any]],

4611 *,

4612 proxy_compound_columns: Optional[

4613 Iterable[Sequence[ColumnElement[Any]]]

4614 ] = None,

4615 ) -> None:

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

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

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

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

4620 # generate below.

4621 select_0 = self.selects[0]

4622

4623 if self._label_style is not LABEL_STYLE_DEFAULT:

4624 select_0 = select_0.set_label_style(self._label_style)

4625

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

4627 # derived columns place a 'weight' annotation corresponding

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

4629 # that the corresponding_column() operation can resolve

4630 # conflicts

4631 extra_col_iterator = zip(

4632 *[

4633 [

4634 c._annotate(dd)

4635 for c in stmt._all_selected_columns

4636 if is_column_element(c)

4637 ]

4638 for dd, stmt in [

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

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

4641 ]

4642 ]

4643 )

4644

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

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

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

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

4649 # compound

4650 select_0._generate_fromclause_column_proxies(

4651 subquery,

4652 columns,

4653 proxy_compound_columns=extra_col_iterator,

4654 primary_key=primary_key,

4655 foreign_keys=foreign_keys,

4656 )

4657

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

4659 super()._refresh_for_new_column(column)

4660 for select in self.selects:

4661 select._refresh_for_new_column(column)

4662

4663 @util.ro_non_memoized_property

4664 def _all_selected_columns(self) -> _SelectIterable:

4665 return self.selects[0]._all_selected_columns

4666

4667 @util.ro_non_memoized_property

4668 def selected_columns(

4669 self,

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

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

4672 representing the columns that

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

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

4675

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

4677 :attr:`_expression.CompoundSelect.selected_columns`

4678 attribute returns the selected

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

4680 statements within the set operation.

4681

4682 .. seealso::

4683

4684 :attr:`_sql.Select.selected_columns`

4685

4686 .. versionadded:: 1.4

4687

4688 """

4689 return self.selects[0].selected_columns

4690

4691

4692# backwards compat

4693for elem in _CompoundSelectKeyword:

4694 setattr(CompoundSelect, elem.name, elem)

4695

4696

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

4698class SelectState(util.MemoizedSlots, CompileState):

4699 __slots__ = (

4700 "from_clauses",

4701 "froms",

4702 "columns_plus_names",

4703 "_label_resolve_dict",

4704 )

4705

4706 if TYPE_CHECKING:

4707 default_select_compile_options: CacheableOptions

4708 else:

4709

4710 class default_select_compile_options(CacheableOptions):

4711 _cache_key_traversal = []

4712

4713 if TYPE_CHECKING:

4714

4715 @classmethod

4716 def get_plugin_class(

4717 cls, statement: Executable

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

4719

4720 def __init__(

4721 self,

4722 statement: Select[Unpack[TupleAny]],

4723 compiler: SQLCompiler,

4724 **kw: Any,

4725 ):

4726 self.statement = statement

4727 self.from_clauses = statement._from_obj

4728

4729 for memoized_entities in statement._memoized_select_entities:

4730 self._setup_joins(

4731 memoized_entities._setup_joins, memoized_entities._raw_columns

4732 )

4733

4734 if statement._setup_joins:

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

4736

4737 self.froms = self._get_froms(statement)

4738

4739 self.columns_plus_names = statement._generate_columns_plus_names(True)

4740

4741 @classmethod

4742 def _plugin_not_implemented(cls) -> NoReturn:

4743 raise NotImplementedError(

4744 "The default SELECT construct without plugins does not "

4745 "implement this method."

4746 )

4747

4748 @classmethod

4749 def get_column_descriptions(

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

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

4752 return [

4753 {

4754 "name": name,

4755 "type": element.type,

4756 "expr": element,

4757 }

4758 for _, name, _, element, _ in (

4759 statement._generate_columns_plus_names(False)

4760 )

4761 ]

4762

4763 @classmethod

4764 def from_statement(

4765 cls,

4766 statement: Select[Unpack[TupleAny]],

4767 from_statement: roles.ReturnsRowsRole,

4768 ) -> ExecutableReturnsRows:

4769 cls._plugin_not_implemented()

4770

4771 @classmethod

4772 def get_columns_clause_froms(

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

4774 ) -> List[FromClause]:

4775 return cls._normalize_froms(

4776 itertools.chain.from_iterable(

4777 element._from_objects for element in statement._raw_columns

4778 )

4779 )

4780

4781 @classmethod

4782 def _column_naming_convention(

4783 cls, label_style: SelectLabelStyle

4784 ) -> _LabelConventionCallable:

4785 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4786

4787 dedupe = label_style is not LABEL_STYLE_NONE

4788

4789 pa = prefix_anon_map()

4790 names = set()

4791

4792 def go(

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

4794 col_name: Optional[str] = None,

4795 ) -> Optional[str]:

4796 if is_text_clause(c):

4797 return None

4798 elif TYPE_CHECKING:

4799 assert is_column_element(c)

4800

4801 if not dedupe:

4802 name = c._proxy_key

4803 if name is None:

4804 name = "_no_label"

4805 return name

4806

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

4808

4809 if name is None:

4810 name = "_no_label"

4811 if name in names:

4812 return c._anon_label(name) % pa

4813 else:

4814 names.add(name)

4815 return name

4816

4817 elif name in names:

4818 return (

4819 c._anon_tq_key_label % pa

4820 if table_qualified

4821 else c._anon_key_label % pa

4822 )

4823 else:

4824 names.add(name)

4825 return name

4826

4827 return go

4828

4829 def _get_froms(

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

4831 ) -> List[FromClause]:

4832 ambiguous_table_name_map: _AmbiguousTableNameMap

4833 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4834

4835 return self._normalize_froms(

4836 itertools.chain(

4837 self.from_clauses,

4838 itertools.chain.from_iterable(

4839 [

4840 element._from_objects

4841 for element in statement._raw_columns

4842 ]

4843 ),

4844 itertools.chain.from_iterable(

4845 [

4846 element._from_objects

4847 for element in statement._where_criteria

4848 ]

4849 ),

4850 ),

4851 check_statement=statement,

4852 ambiguous_table_name_map=ambiguous_table_name_map,

4853 )

4854

4855 @classmethod

4856 def _normalize_froms(

4857 cls,

4858 iterable_of_froms: Iterable[FromClause],

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

4860 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4861 ) -> List[FromClause]:

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

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

4864

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

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

4867 etc.

4868

4869 """

4870 seen: Set[FromClause] = set()

4871 froms: List[FromClause] = []

4872

4873 for item in iterable_of_froms:

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

4875 raise exc.InvalidRequestError(

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

4877 )

4878

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

4880 froms.append(item)

4881 seen.update(item._cloned_set)

4882

4883 if froms:

4884 toremove = set(

4885 itertools.chain.from_iterable(

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

4887 )

4888 )

4889 if toremove:

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

4891 # using a list to maintain ordering

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

4893

4894 if ambiguous_table_name_map is not None:

4895 ambiguous_table_name_map.update(

4896 (

4897 fr.name,

4898 _anonymous_label.safe_construct(

4899 hash(fr.name), fr.name

4900 ),

4901 )

4902 for item in froms

4903 for fr in item._from_objects

4904 if is_table(fr)

4905 and fr.schema

4906 and fr.name not in ambiguous_table_name_map

4907 )

4908

4909 return froms

4910

4911 def _get_display_froms(

4912 self,

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

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

4915 ) -> List[FromClause]:

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

4917

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

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

4920 may want to leave those absent if it is automatically

4921 correlating.

4922

4923 """

4924

4925 froms = self.froms

4926

4927 if self.statement._correlate:

4928 to_correlate = self.statement._correlate

4929 if to_correlate:

4930 froms = [

4931 f

4932 for f in froms

4933 if f

4934 not in _cloned_intersection(

4935 _cloned_intersection(

4936 froms, explicit_correlate_froms or ()

4937 ),

4938 to_correlate,

4939 )

4940 ]

4941

4942 if self.statement._correlate_except is not None:

4943 froms = [

4944 f

4945 for f in froms

4946 if f

4947 not in _cloned_difference(

4948 _cloned_intersection(

4949 froms, explicit_correlate_froms or ()

4950 ),

4951 self.statement._correlate_except,

4952 )

4953 ]

4954

4955 if (

4956 self.statement._auto_correlate

4957 and implicit_correlate_froms

4958 and len(froms) > 1

4959 ):

4960 froms = [

4961 f

4962 for f in froms

4963 if f

4964 not in _cloned_intersection(froms, implicit_correlate_froms)

4965 ]

4966

4967 if not len(froms):

4968 raise exc.InvalidRequestError(

4969 "Select statement '%r"

4970 "' returned no FROM clauses "

4971 "due to auto-correlation; "

4972 "specify correlate(<tables>) "

4973 "to control correlation "

4974 "manually." % self.statement

4975 )

4976

4977 return froms

4978

4979 def _memoized_attr__label_resolve_dict(

4980 self,

4981 ) -> Tuple[

4982 Dict[str, ColumnElement[Any]],

4983 Dict[str, ColumnElement[Any]],

4984 Dict[str, ColumnElement[Any]],

4985 ]:

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

4987 c._tq_label or c.key: c

4988 for c in self.statement._all_selected_columns

4989 if c._allow_label_resolve

4990 }

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

4992 c.key: c # type: ignore

4993 for c in _select_iterables(self.froms)

4994 if c._allow_label_resolve

4995 }

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

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

4998 with_cols.setdefault(key, value)

4999

5000 return with_cols, only_froms, only_cols

5001

5002 @classmethod

5003 def determine_last_joined_entity(

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

5005 ) -> Optional[_JoinTargetElement]:

5006 if stmt._setup_joins:

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

5008 else:

5009 return None

5010

5011 @classmethod

5012 def all_selected_columns(

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

5014 ) -> _SelectIterable:

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

5016

5017 def _setup_joins(

5018 self,

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

5020 raw_columns: List[_ColumnsClauseElement],

5021 ) -> None:

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

5023 if TYPE_CHECKING:

5024 if onclause is not None:

5025 assert isinstance(onclause, ColumnElement)

5026

5027 isouter = flags["isouter"]

5028 full = flags["full"]

5029

5030 if left is None:

5031 (

5032 left,

5033 replace_from_obj_index,

5034 ) = self._join_determine_implicit_left_side(

5035 raw_columns, left, right, onclause

5036 )

5037 else:

5038 (replace_from_obj_index) = self._join_place_explicit_left_side(

5039 left

5040 )

5041

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

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

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

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

5046 # used instead.

5047 if TYPE_CHECKING:

5048 assert isinstance(right, FromClause)

5049 if onclause is not None:

5050 assert isinstance(onclause, ColumnElement)

5051

5052 if replace_from_obj_index is not None:

5053 # splice into an existing element in the

5054 # self._from_obj list

5055 left_clause = self.from_clauses[replace_from_obj_index]

5056

5057 self.from_clauses = (

5058 self.from_clauses[:replace_from_obj_index]

5059 + (

5060 Join(

5061 left_clause,

5062 right,

5063 onclause,

5064 isouter=isouter,

5065 full=full,

5066 ),

5067 )

5068 + self.from_clauses[replace_from_obj_index + 1 :]

5069 )

5070 else:

5071 assert left is not None

5072 self.from_clauses = self.from_clauses + (

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

5074 )

5075

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

5077 def _join_determine_implicit_left_side(

5078 self,

5079 raw_columns: List[_ColumnsClauseElement],

5080 left: Optional[FromClause],

5081 right: _JoinTargetElement,

5082 onclause: Optional[ColumnElement[Any]],

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

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

5085 determine if an existing FROM or entity in this query

5086 can serve as the left hand side.

5087

5088 """

5089

5090 sql_util = util.preloaded.sql_util

5091

5092 replace_from_obj_index: Optional[int] = None

5093

5094 from_clauses = self.from_clauses

5095

5096 if from_clauses:

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

5098 from_clauses, right, onclause

5099 )

5100

5101 if len(indexes) == 1:

5102 replace_from_obj_index = indexes[0]

5103 left = from_clauses[replace_from_obj_index]

5104 else:

5105 potential = {}

5106 statement = self.statement

5107

5108 for from_clause in itertools.chain(

5109 itertools.chain.from_iterable(

5110 [element._from_objects for element in raw_columns]

5111 ),

5112 itertools.chain.from_iterable(

5113 [

5114 element._from_objects

5115 for element in statement._where_criteria

5116 ]

5117 ),

5118 ):

5119 potential[from_clause] = ()

5120

5121 all_clauses = list(potential.keys())

5122 indexes = sql_util.find_left_clause_to_join_from(

5123 all_clauses, right, onclause

5124 )

5125

5126 if len(indexes) == 1:

5127 left = all_clauses[indexes[0]]

5128

5129 if len(indexes) > 1:

5130 raise exc.InvalidRequestError(

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

5132 "from, there are multiple FROMS which can "

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

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

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

5136 "help resolve the ambiguity."

5137 )

5138 elif not indexes:

5139 raise exc.InvalidRequestError(

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

5141 "Please use the .select_from() "

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

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

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

5145 )

5146 return left, replace_from_obj_index

5147

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

5149 def _join_place_explicit_left_side(

5150 self, left: FromClause

5151 ) -> Optional[int]:

5152 replace_from_obj_index: Optional[int] = None

5153

5154 sql_util = util.preloaded.sql_util

5155

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

5157

5158 if from_clauses:

5159 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5160 self.from_clauses, left

5161 )

5162 else:

5163 indexes = []

5164

5165 if len(indexes) > 1:

5166 raise exc.InvalidRequestError(

5167 "Can't identify which entity in which to assign the "

5168 "left side of this join. Please use a more specific "

5169 "ON clause."

5170 )

5171

5172 # have an index, means the left side is already present in

5173 # an existing FROM in the self._from_obj tuple

5174 if indexes:

5175 replace_from_obj_index = indexes[0]

5176

5177 # no index, means we need to add a new element to the

5178 # self._from_obj tuple

5179

5180 return replace_from_obj_index

5181

5182

5183class _SelectFromElements:

5184 __slots__ = ()

5185

5186 _raw_columns: List[_ColumnsClauseElement]

5187 _where_criteria: Tuple[ColumnElement[Any], ...]

5188 _from_obj: Tuple[FromClause, ...]

5189

5190 def _iterate_from_elements(self) -> Iterator[FromClause]:

5191 # note this does not include elements

5192 # in _setup_joins

5193

5194 seen = set()

5195 for element in self._raw_columns:

5196 for fr in element._from_objects:

5197 if fr in seen:

5198 continue

5199 seen.add(fr)

5200 yield fr

5201 for element in self._where_criteria:

5202 for fr in element._from_objects:

5203 if fr in seen:

5204 continue

5205 seen.add(fr)

5206 yield fr

5207 for element in self._from_obj:

5208 if element in seen:

5209 continue

5210 seen.add(element)

5211 yield element

5212

5213

5214class _MemoizedSelectEntities(

5215 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5216):

5217 """represents partial state from a Select object, for the case

5218 where Select.columns() has redefined the set of columns/entities the

5219 statement will be SELECTing from. This object represents

5220 the entities from the SELECT before that transformation was applied,

5221 so that transformations that were made in terms of the SELECT at that

5222 time, such as join() as well as options(), can access the correct context.

5223

5224 In previous SQLAlchemy versions, this wasn't needed because these

5225 constructs calculated everything up front, like when you called join()

5226 or options(), it did everything to figure out how that would translate

5227 into specific SQL constructs that would be ready to send directly to the

5228 SQL compiler when needed. But as of

5229 1.4, all of that stuff is done in the compilation phase, during the

5230 "compile state" portion of the process, so that the work can all be

5231 cached. So it needs to be able to resolve joins/options2 based on what

5232 the list of entities was when those methods were called.

5233

5234

5235 """

5236

5237 __visit_name__ = "memoized_select_entities"

5238

5239 _traverse_internals: _TraverseInternalsType = [

5240 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5241 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5242 ("_with_options", InternalTraversal.dp_executable_options),

5243 ]

5244

5245 _is_clone_of: Optional[ClauseElement]

5246 _raw_columns: List[_ColumnsClauseElement]

5247 _setup_joins: Tuple[_SetupJoinsElement, ...]

5248 _with_options: Tuple[ExecutableOption, ...]

5249

5250 _annotations = util.EMPTY_DICT

5251

5252 def _clone(self, **kw: Any) -> Self:

5253 c = self.__class__.__new__(self.__class__)

5254 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5255

5256 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5257 return c

5258

5259 @classmethod

5260 def _generate_for_statement(

5261 cls, select_stmt: Select[Unpack[TupleAny]]

5262 ) -> None:

5263 if select_stmt._setup_joins or select_stmt._with_options:

5264 self = _MemoizedSelectEntities()

5265 self._raw_columns = select_stmt._raw_columns

5266 self._setup_joins = select_stmt._setup_joins

5267 self._with_options = select_stmt._with_options

5268

5269 select_stmt._memoized_select_entities += (self,)

5270 select_stmt._raw_columns = []

5271 select_stmt._setup_joins = select_stmt._with_options = ()

5272

5273

5274class Select(

5275 HasPrefixes,

5276 HasSuffixes,

5277 HasHints,

5278 HasCompileState,

5279 HasSyntaxExtensions[

5280 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5281 ],

5282 _SelectFromElements,

5283 GenerativeSelect,

5284 TypedReturnsRows[Unpack[_Ts]],

5285):

5286 """Represents a ``SELECT`` statement.

5287

5288 The :class:`_sql.Select` object is normally constructed using the

5289 :func:`_sql.select` function. See that function for details.

5290

5291 Available extension points:

5292

5293 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5294 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5295 keyword (if any) and the list of columns.

5296 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5297 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5298

5299 .. seealso::

5300

5301 :func:`_sql.select`

5302

5303 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5304

5305 """

5306

5307 __visit_name__ = "select"

5308

5309 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5310 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5311

5312 _raw_columns: List[_ColumnsClauseElement]

5313

5314 _distinct: bool = False

5315 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5316 _correlate: Tuple[FromClause, ...] = ()

5317 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5318 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5319 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5320 _from_obj: Tuple[FromClause, ...] = ()

5321

5322 _position_map = util.immutabledict(

5323 {

5324 "post_select": "_post_select_clause",

5325 "pre_columns": "_pre_columns_clause",

5326 "post_criteria": "_post_criteria_clause",

5327 "post_body": "_post_body_clause",

5328 }

5329 )

5330

5331 _post_select_clause: Optional[ClauseElement] = None

5332 """extension point for a ClauseElement that will be compiled directly

5333 after the SELECT keyword.

5334

5335 .. versionadded:: 2.1

5336

5337 """

5338

5339 _pre_columns_clause: Optional[ClauseElement] = None

5340 """extension point for a ClauseElement that will be compiled directly

5341 before the "columns" clause; after DISTINCT (if present).

5342

5343 .. versionadded:: 2.1

5344

5345 """

5346

5347 _post_criteria_clause: Optional[ClauseElement] = None

5348 """extension point for a ClauseElement that will be compiled directly

5349 after "criteria", following the HAVING clause but before ORDER BY.

5350

5351 .. versionadded:: 2.1

5352

5353 """

5354

5355 _post_body_clause: Optional[ClauseElement] = None

5356 """extension point for a ClauseElement that will be compiled directly

5357 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5358 of the SELECT.

5359

5360 .. versionadded:: 2.1

5361

5362 """

5363

5364 _auto_correlate = True

5365 _is_select_statement = True

5366 _compile_options: CacheableOptions = (

5367 SelectState.default_select_compile_options

5368 )

5369

5370 _traverse_internals: _TraverseInternalsType = (

5371 [

5372 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5373 (

5374 "_memoized_select_entities",

5375 InternalTraversal.dp_memoized_select_entities,

5376 ),

5377 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5378 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5379 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5380 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5381 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5382 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5383 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5384 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5385 ("_limit_clause", InternalTraversal.dp_clauseelement),

5386 ("_offset_clause", InternalTraversal.dp_clauseelement),

5387 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5388 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5389 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5390 ("_distinct", InternalTraversal.dp_boolean),

5391 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5392 ("_label_style", InternalTraversal.dp_plain_obj),

5393 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5394 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5395 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5396 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5397 ]

5398 + HasCTE._has_ctes_traverse_internals

5399 + HasPrefixes._has_prefixes_traverse_internals

5400 + HasSuffixes._has_suffixes_traverse_internals

5401 + HasHints._has_hints_traverse_internals

5402 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5403 + Executable._executable_traverse_internals

5404 + DialectKWArgs._dialect_kwargs_traverse_internals

5405 )

5406

5407 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5408 ("_compile_options", InternalTraversal.dp_has_cache_key)

5409 ]

5410

5411 _compile_state_factory: Type[SelectState]

5412

5413 @classmethod

5414 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5415 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5416

5417 Used internally to build up :class:`.Select` constructs with

5418 pre-established state.

5419

5420 """

5421

5422 stmt = Select.__new__(Select)

5423 stmt.__dict__.update(kw)

5424 return stmt

5425

5426 def __init__(

5427 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5428 ):

5429 r"""Construct a new :class:`_expression.Select`.

5430

5431 The public constructor for :class:`_expression.Select` is the

5432 :func:`_sql.select` function.

5433

5434 """

5435 self._raw_columns = [

5436 coercions.expect(

5437 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5438 )

5439 for ent in entities

5440 ]

5441 GenerativeSelect.__init__(self)

5442

5443 def _apply_syntax_extension_to_self(

5444 self, extension: SyntaxExtension

5445 ) -> None:

5446 extension.apply_to_select(self)

5447

5448 def _scalar_type(self) -> TypeEngine[Any]:

5449 if not self._raw_columns:

5450 return NULLTYPE

5451 elem = self._raw_columns[0]

5452 cols = list(elem._select_iterable)

5453 return cols[0].type

5454

5455 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5456 """A synonym for the :meth:`_sql.Select.where` method."""

5457

5458 return self.where(*criteria)

5459

5460 def _filter_by_zero(

5461 self,

5462 ) -> Union[

5463 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5464 ]:

5465 if self._setup_joins:

5466 meth = SelectState.get_plugin_class(

5467 self

5468 ).determine_last_joined_entity

5469 _last_joined_entity = meth(self)

5470 if _last_joined_entity is not None:

5471 return _last_joined_entity

5472

5473 if self._from_obj:

5474 return self._from_obj[0]

5475

5476 return self._raw_columns[0]

5477

5478 if TYPE_CHECKING:

5479

5480 @overload

5481 def scalar_subquery(

5482 self: Select[_MAYBE_ENTITY],

5483 ) -> ScalarSelect[Any]: ...

5484

5485 @overload

5486 def scalar_subquery(

5487 self: Select[_NOT_ENTITY],

5488 ) -> ScalarSelect[_NOT_ENTITY]: ...

5489

5490 @overload

5491 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5492

5493 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5494

5495 def filter_by(self, **kwargs: Any) -> Self:

5496 r"""apply the given filtering criterion as a WHERE clause

5497 to this select.

5498

5499 """

5500 from_entity = self._filter_by_zero()

5501

5502 clauses = [

5503 _entity_namespace_key(from_entity, key) == value

5504 for key, value in kwargs.items()

5505 ]

5506 return self.filter(*clauses)

5507

5508 @property

5509 def column_descriptions(self) -> Any:

5510 """Return a :term:`plugin-enabled` 'column descriptions' structure

5511 referring to the columns which are SELECTed by this statement.

5512

5513 This attribute is generally useful when using the ORM, as an

5514 extended structure which includes information about mapped

5515 entities is returned. The section :ref:`queryguide_inspection`

5516 contains more background.

5517

5518 For a Core-only statement, the structure returned by this accessor

5519 is derived from the same objects that are returned by the

5520 :attr:`.Select.selected_columns` accessor, formatted as a list of

5521 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5522 which indicate the column expressions to be selected::

5523

5524 >>> stmt = select(user_table)

5525 >>> stmt.column_descriptions

5526 [

5527 {

5528 'name': 'id',

5529 'type': Integer(),

5530 'expr': Column('id', Integer(), ...)},

5531 {

5532 'name': 'name',

5533 'type': String(length=30),

5534 'expr': Column('name', String(length=30), ...)}

5535 ]

5536

5537 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5538 attribute returns a structure for a Core-only set of entities,

5539 not just ORM-only entities.

5540

5541 .. seealso::

5542

5543 :attr:`.UpdateBase.entity_description` - entity information for

5544 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5545

5546 :ref:`queryguide_inspection` - ORM background

5547

5548 """

5549 meth = SelectState.get_plugin_class(self).get_column_descriptions

5550 return meth(self)

5551

5552 def from_statement(

5553 self, statement: roles.ReturnsRowsRole

5554 ) -> ExecutableReturnsRows:

5555 """Apply the columns which this :class:`.Select` would select

5556 onto another statement.

5557

5558 This operation is :term:`plugin-specific` and will raise a not

5559 supported exception if this :class:`_sql.Select` does not select from

5560 plugin-enabled entities.

5561

5562

5563 The statement is typically either a :func:`_expression.text` or

5564 :func:`_expression.select` construct, and should return the set of

5565 columns appropriate to the entities represented by this

5566 :class:`.Select`.

5567

5568 .. seealso::

5569

5570 :ref:`orm_queryguide_selecting_text` - usage examples in the

5571 ORM Querying Guide

5572

5573 """

5574 meth = SelectState.get_plugin_class(self).from_statement

5575 return meth(self, statement)

5576

5577 @_generative

5578 def join(

5579 self,

5580 target: _JoinTargetArgument,

5581 onclause: Optional[_OnClauseArgument] = None,

5582 *,

5583 isouter: bool = False,

5584 full: bool = False,

5585 ) -> Self:

5586 r"""Create a SQL JOIN against this :class:`_expression.Select`

5587 object's criterion

5588 and apply generatively, returning the newly resulting

5589 :class:`_expression.Select`.

5590

5591 E.g.::

5592

5593 stmt = select(user_table).join(

5594 address_table, user_table.c.id == address_table.c.user_id

5595 )

5596

5597 The above statement generates SQL similar to:

5598

5599 .. sourcecode:: sql

5600

5601 SELECT user.id, user.name

5602 FROM user

5603 JOIN address ON user.id = address.user_id

5604

5605 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5606 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5607 source that is within the FROM clause of the existing SELECT,

5608 and a given target :class:`_sql.FromClause`, and then adds

5609 this :class:`_sql.Join` to the FROM clause of the newly generated

5610 SELECT statement. This is completely reworked from the behavior

5611 in 1.3, which would instead create a subquery of the entire

5612 :class:`_expression.Select` and then join that subquery to the

5613 target.

5614

5615 This is a **backwards incompatible change** as the previous behavior

5616 was mostly useless, producing an unnamed subquery rejected by

5617 most databases in any case. The new behavior is modeled after

5618 that of the very successful :meth:`_orm.Query.join` method in the

5619 ORM, in order to support the functionality of :class:`_orm.Query`

5620 being available by using a :class:`_sql.Select` object with an

5621 :class:`_orm.Session`.

5622

5623 See the notes for this change at :ref:`change_select_join`.

5624

5625

5626 :param target: target table to join towards

5627

5628 :param onclause: ON clause of the join. If omitted, an ON clause

5629 is generated automatically based on the :class:`_schema.ForeignKey`

5630 linkages between the two tables, if one can be unambiguously

5631 determined, otherwise an error is raised.

5632

5633 :param isouter: if True, generate LEFT OUTER join. Same as

5634 :meth:`_expression.Select.outerjoin`.

5635

5636 :param full: if True, generate FULL OUTER join.

5637

5638 .. seealso::

5639

5640 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5641

5642 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5643

5644 :meth:`_expression.Select.join_from`

5645

5646 :meth:`_expression.Select.outerjoin`

5647

5648 """ # noqa: E501

5649 join_target = coercions.expect(

5650 roles.JoinTargetRole, target, apply_propagate_attrs=self

5651 )

5652 if onclause is not None:

5653 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5654 else:

5655 onclause_element = None

5656

5657 self._setup_joins += (

5658 (

5659 join_target,

5660 onclause_element,

5661 None,

5662 {"isouter": isouter, "full": full},

5663 ),

5664 )

5665 return self

5666

5667 def outerjoin_from(

5668 self,

5669 from_: _FromClauseArgument,

5670 target: _JoinTargetArgument,

5671 onclause: Optional[_OnClauseArgument] = None,

5672 *,

5673 full: bool = False,

5674 ) -> Self:

5675 r"""Create a SQL LEFT OUTER JOIN against this

5676 :class:`_expression.Select` object's criterion and apply generatively,

5677 returning the newly resulting :class:`_expression.Select`.

5678

5679 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5680

5681 """

5682 return self.join_from(

5683 from_, target, onclause=onclause, isouter=True, full=full

5684 )

5685

5686 @_generative

5687 def join_from(

5688 self,

5689 from_: _FromClauseArgument,

5690 target: _JoinTargetArgument,

5691 onclause: Optional[_OnClauseArgument] = None,

5692 *,

5693 isouter: bool = False,

5694 full: bool = False,

5695 ) -> Self:

5696 r"""Create a SQL JOIN against this :class:`_expression.Select`

5697 object's criterion

5698 and apply generatively, returning the newly resulting

5699 :class:`_expression.Select`.

5700

5701 E.g.::

5702

5703 stmt = select(user_table, address_table).join_from(

5704 user_table, address_table, user_table.c.id == address_table.c.user_id

5705 )

5706

5707 The above statement generates SQL similar to:

5708

5709 .. sourcecode:: sql

5710

5711 SELECT user.id, user.name, address.id, address.email, address.user_id

5712 FROM user JOIN address ON user.id = address.user_id

5713

5714 .. versionadded:: 1.4

5715

5716 :param from\_: the left side of the join, will be rendered in the

5717 FROM clause and is roughly equivalent to using the

5718 :meth:`.Select.select_from` method.

5719

5720 :param target: target table to join towards

5721

5722 :param onclause: ON clause of the join.

5723

5724 :param isouter: if True, generate LEFT OUTER join. Same as

5725 :meth:`_expression.Select.outerjoin`.

5726

5727 :param full: if True, generate FULL OUTER join.

5728

5729 .. seealso::

5730

5731 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5732

5733 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5734

5735 :meth:`_expression.Select.join`

5736

5737 """ # noqa: E501

5738

5739 # note the order of parsing from vs. target is important here, as we

5740 # are also deriving the source of the plugin (i.e. the subject mapper

5741 # in an ORM query) which should favor the "from_" over the "target"

5742

5743 from_ = coercions.expect(

5744 roles.FromClauseRole, from_, apply_propagate_attrs=self

5745 )

5746 join_target = coercions.expect(

5747 roles.JoinTargetRole, target, apply_propagate_attrs=self

5748 )

5749 if onclause is not None:

5750 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5751 else:

5752 onclause_element = None

5753

5754 self._setup_joins += (

5755 (

5756 join_target,

5757 onclause_element,

5758 from_,

5759 {"isouter": isouter, "full": full},

5760 ),

5761 )

5762 return self

5763

5764 def outerjoin(

5765 self,

5766 target: _JoinTargetArgument,

5767 onclause: Optional[_OnClauseArgument] = None,

5768 *,

5769 full: bool = False,

5770 ) -> Self:

5771 """Create a left outer join.

5772

5773 Parameters are the same as that of :meth:`_expression.Select.join`.

5774

5775 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5776 creates a :class:`_sql.Join` object between a

5777 :class:`_sql.FromClause` source that is within the FROM clause of

5778 the existing SELECT, and a given target :class:`_sql.FromClause`,

5779 and then adds this :class:`_sql.Join` to the FROM clause of the

5780 newly generated SELECT statement. This is completely reworked

5781 from the behavior in 1.3, which would instead create a subquery of

5782 the entire

5783 :class:`_expression.Select` and then join that subquery to the

5784 target.

5785

5786 This is a **backwards incompatible change** as the previous behavior

5787 was mostly useless, producing an unnamed subquery rejected by

5788 most databases in any case. The new behavior is modeled after

5789 that of the very successful :meth:`_orm.Query.join` method in the

5790 ORM, in order to support the functionality of :class:`_orm.Query`

5791 being available by using a :class:`_sql.Select` object with an

5792 :class:`_orm.Session`.

5793

5794 See the notes for this change at :ref:`change_select_join`.

5795

5796 .. seealso::

5797

5798 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5799

5800 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5801

5802 :meth:`_expression.Select.join`

5803

5804 """

5805 return self.join(target, onclause=onclause, isouter=True, full=full)

5806

5807 def get_final_froms(self) -> Sequence[FromClause]:

5808 """Compute the final displayed list of :class:`_expression.FromClause`

5809 elements.

5810

5811 This method will run through the full computation required to

5812 determine what FROM elements will be displayed in the resulting

5813 SELECT statement, including shadowing individual tables with

5814 JOIN objects, as well as full computation for ORM use cases including

5815 eager loading clauses.

5816

5817 For ORM use, this accessor returns the **post compilation**

5818 list of FROM objects; this collection will include elements such as

5819 eagerly loaded tables and joins. The objects will **not** be

5820 ORM enabled and not work as a replacement for the

5821 :meth:`_sql.Select.select_froms` collection; additionally, the

5822 method is not well performing for an ORM enabled statement as it

5823 will incur the full ORM construction process.

5824

5825 To retrieve the FROM list that's implied by the "columns" collection

5826 passed to the :class:`_sql.Select` originally, use the

5827 :attr:`_sql.Select.columns_clause_froms` accessor.

5828

5829 To select from an alternative set of columns while maintaining the

5830 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5831 pass the

5832 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5833 parameter.

5834

5835 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5836 method replaces the previous :attr:`_sql.Select.froms` accessor,

5837 which is deprecated.

5838

5839 .. seealso::

5840

5841 :attr:`_sql.Select.columns_clause_froms`

5842

5843 """

5844 compiler = self._default_compiler()

5845

5846 return self._compile_state_factory(self, compiler)._get_display_froms()

5847

5848 @property

5849 @util.deprecated(

5850 "1.4.23",

5851 "The :attr:`_expression.Select.froms` attribute is moved to "

5852 "the :meth:`_expression.Select.get_final_froms` method.",

5853 )

5854 def froms(self) -> Sequence[FromClause]:

5855 """Return the displayed list of :class:`_expression.FromClause`

5856 elements.

5857

5858

5859 """

5860 return self.get_final_froms()

5861

5862 @property

5863 def columns_clause_froms(self) -> List[FromClause]:

5864 """Return the set of :class:`_expression.FromClause` objects implied

5865 by the columns clause of this SELECT statement.

5866

5867 .. versionadded:: 1.4.23

5868

5869 .. seealso::

5870

5871 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5872 statement into account

5873

5874 :meth:`_sql.Select.with_only_columns` - makes use of this

5875 collection to set up a new FROM list

5876

5877 """

5878

5879 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5880 self

5881 )

5882

5883 @property

5884 def inner_columns(self) -> _SelectIterable:

5885 """An iterator of all :class:`_expression.ColumnElement`

5886 expressions which would

5887 be rendered into the columns clause of the resulting SELECT statement.

5888

5889 This method is legacy as of 1.4 and is superseded by the

5890 :attr:`_expression.Select.exported_columns` collection.

5891

5892 """

5893

5894 return iter(self._all_selected_columns)

5895

5896 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

5897 if fromclause is not None and self in fromclause._cloned_set:

5898 return True

5899

5900 for f in self._iterate_from_elements():

5901 if f.is_derived_from(fromclause):

5902 return True

5903 return False

5904

5905 def _copy_internals(

5906 self, clone: _CloneCallableType = _clone, **kw: Any

5907 ) -> None:

5908 # Select() object has been cloned and probably adapted by the

5909 # given clone function. Apply the cloning function to internal

5910 # objects

5911

5912 # 1. keep a dictionary of the froms we've cloned, and what

5913 # they've become. This allows us to ensure the same cloned from

5914 # is used when other items such as columns are "cloned"

5915

5916 all_the_froms = set(

5917 itertools.chain(

5918 _from_objects(*self._raw_columns),

5919 _from_objects(*self._where_criteria),

5920 _from_objects(*[elem[0] for elem in self._setup_joins]),

5921 )

5922 )

5923

5924 # do a clone for the froms we've gathered. what is important here

5925 # is if any of the things we are selecting from, like tables,

5926 # were converted into Join objects. if so, these need to be

5927 # added to _from_obj explicitly, because otherwise they won't be

5928 # part of the new state, as they don't associate themselves with

5929 # their columns.

5930 new_froms = {f: clone(f, **kw) for f in all_the_froms}

5931

5932 # 2. copy FROM collections, adding in joins that we've created.

5933 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5934 add_froms = (

5935 {f for f in new_froms.values() if isinstance(f, Join)}

5936 .difference(all_the_froms)

5937 .difference(existing_from_obj)

5938 )

5939

5940 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5941

5942 # 3. clone everything else, making sure we use columns

5943 # corresponding to the froms we just made.

5944 def replace(

5945 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

5946 **kw: Any,

5947 ) -> Optional[KeyedColumnElement[Any]]:

5948 if isinstance(obj, ColumnClause) and obj.table in new_froms:

5949 newelem = new_froms[obj.table].corresponding_column(obj)

5950 return newelem

5951 return None

5952

5953 kw["replace"] = replace

5954

5955 # copy everything else. for table-ish things like correlate,

5956 # correlate_except, setup_joins, these clone normally. For

5957 # column-expression oriented things like raw_columns, where_criteria,

5958 # order by, we get this from the new froms.

5959 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5960

5961 self._reset_memoizations()

5962

5963 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5964 return itertools.chain(

5965 super().get_children(

5966 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5967 **kw,

5968 ),

5969 self._iterate_from_elements(),

5970 )

5971

5972 @_generative

5973 def add_columns(

5974 self, *entities: _ColumnsClauseArgument[Any]

5975 ) -> Select[Unpack[TupleAny]]:

5976 r"""Return a new :func:`_expression.select` construct with

5977 the given entities appended to its columns clause.

5978

5979 E.g.::

5980

5981 my_select = my_select.add_columns(table.c.new_column)

5982

5983 The original expressions in the columns clause remain in place.

5984 To replace the original expressions with new ones, see the method

5985 :meth:`_expression.Select.with_only_columns`.

5986

5987 :param \*entities: column, table, or other entity expressions to be

5988 added to the columns clause

5989

5990 .. seealso::

5991

5992 :meth:`_expression.Select.with_only_columns` - replaces existing

5993 expressions rather than appending.

5994

5995 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5996 example

5997

5998 """

5999 self._reset_memoizations()

6000

6001 self._raw_columns = self._raw_columns + [

6002 coercions.expect(

6003 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6004 )

6005 for column in entities

6006 ]

6007 return self

6008

6009 def _set_entities(

6010 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6011 ) -> None:

6012 self._raw_columns = [

6013 coercions.expect(

6014 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6015 )

6016 for ent in util.to_list(entities)

6017 ]

6018

6019 @util.deprecated(

6020 "1.4",

6021 "The :meth:`_expression.Select.column` method is deprecated and will "

6022 "be removed in a future release. Please use "

6023 ":meth:`_expression.Select.add_columns`",

6024 )

6025 def column(

6026 self, column: _ColumnsClauseArgument[Any]

6027 ) -> Select[Unpack[TupleAny]]:

6028 """Return a new :func:`_expression.select` construct with

6029 the given column expression added to its columns clause.

6030

6031 E.g.::

6032

6033 my_select = my_select.column(table.c.new_column)

6034

6035 See the documentation for

6036 :meth:`_expression.Select.with_only_columns`

6037 for guidelines on adding /replacing the columns of a

6038 :class:`_expression.Select` object.

6039

6040 """

6041 return self.add_columns(column)

6042

6043 @util.preload_module("sqlalchemy.sql.util")

6044 def reduce_columns(

6045 self, only_synonyms: bool = True

6046 ) -> Select[Unpack[TupleAny]]:

6047 """Return a new :func:`_expression.select` construct with redundantly

6048 named, equivalently-valued columns removed from the columns clause.

6049

6050 "Redundant" here means two columns where one refers to the

6051 other either based on foreign key, or via a simple equality

6052 comparison in the WHERE clause of the statement. The primary purpose

6053 of this method is to automatically construct a select statement

6054 with all uniquely-named columns, without the need to use

6055 table-qualified labels as

6056 :meth:`_expression.Select.set_label_style`

6057 does.

6058

6059 When columns are omitted based on foreign key, the referred-to

6060 column is the one that's kept. When columns are omitted based on

6061 WHERE equivalence, the first column in the columns clause is the

6062 one that's kept.

6063

6064 :param only_synonyms: when True, limit the removal of columns

6065 to those which have the same name as the equivalent. Otherwise,

6066 all columns that are equivalent to another are removed.

6067

6068 """

6069 woc: Select[Unpack[TupleAny]]

6070 woc = self.with_only_columns(

6071 *util.preloaded.sql_util.reduce_columns(

6072 self._all_selected_columns,

6073 only_synonyms=only_synonyms,

6074 *(self._where_criteria + self._from_obj),

6075 )

6076 )

6077 return woc

6078

6079 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6080

6081 # code within this block is **programmatically,

6082 # statically generated** by tools/generate_tuple_map_overloads.py

6083

6084 @overload

6085 def with_only_columns(

6086 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6087 ) -> Select[_T0]: ...

6088

6089 @overload

6090 def with_only_columns(

6091 self,

6092 __ent0: _TCCA[_T0],

6093 __ent1: _TCCA[_T1],

6094 /,

6095 *,

6096 maintain_column_froms: bool = ...,

6097 ) -> Select[_T0, _T1]: ...

6098

6099 @overload

6100 def with_only_columns(

6101 self,

6102 __ent0: _TCCA[_T0],

6103 __ent1: _TCCA[_T1],

6104 __ent2: _TCCA[_T2],

6105 /,

6106 *,

6107 maintain_column_froms: bool = ...,

6108 ) -> Select[_T0, _T1, _T2]: ...

6109

6110 @overload

6111 def with_only_columns(

6112 self,

6113 __ent0: _TCCA[_T0],

6114 __ent1: _TCCA[_T1],

6115 __ent2: _TCCA[_T2],

6116 __ent3: _TCCA[_T3],

6117 /,

6118 *,

6119 maintain_column_froms: bool = ...,

6120 ) -> Select[_T0, _T1, _T2, _T3]: ...

6121

6122 @overload

6123 def with_only_columns(

6124 self,

6125 __ent0: _TCCA[_T0],

6126 __ent1: _TCCA[_T1],

6127 __ent2: _TCCA[_T2],

6128 __ent3: _TCCA[_T3],

6129 __ent4: _TCCA[_T4],

6130 /,

6131 *,

6132 maintain_column_froms: bool = ...,

6133 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6134

6135 @overload

6136 def with_only_columns(

6137 self,

6138 __ent0: _TCCA[_T0],

6139 __ent1: _TCCA[_T1],

6140 __ent2: _TCCA[_T2],

6141 __ent3: _TCCA[_T3],

6142 __ent4: _TCCA[_T4],

6143 __ent5: _TCCA[_T5],

6144 /,

6145 *,

6146 maintain_column_froms: bool = ...,

6147 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

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 /,

6160 *,

6161 maintain_column_froms: bool = ...,

6162 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6163

6164 @overload

6165 def with_only_columns(

6166 self,

6167 __ent0: _TCCA[_T0],

6168 __ent1: _TCCA[_T1],

6169 __ent2: _TCCA[_T2],

6170 __ent3: _TCCA[_T3],

6171 __ent4: _TCCA[_T4],

6172 __ent5: _TCCA[_T5],

6173 __ent6: _TCCA[_T6],

6174 __ent7: _TCCA[_T7],

6175 /,

6176 *entities: _ColumnsClauseArgument[Any],

6177 maintain_column_froms: bool = ...,

6178 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6179

6180 # END OVERLOADED FUNCTIONS self.with_only_columns

6181

6182 @overload

6183 def with_only_columns(

6184 self,

6185 *entities: _ColumnsClauseArgument[Any],

6186 maintain_column_froms: bool = False,

6187 **__kw: Any,

6188 ) -> Select[Unpack[TupleAny]]: ...

6189

6190 @_generative

6191 def with_only_columns(

6192 self,

6193 *entities: _ColumnsClauseArgument[Any],

6194 maintain_column_froms: bool = False,

6195 **__kw: Any,

6196 ) -> Select[Unpack[TupleAny]]:

6197 r"""Return a new :func:`_expression.select` construct with its columns

6198 clause replaced with the given entities.

6199

6200 By default, this method is exactly equivalent to as if the original

6201 :func:`_expression.select` had been called with the given entities.

6202 E.g. a statement::

6203

6204 s = select(table1.c.a, table1.c.b)

6205 s = s.with_only_columns(table1.c.b)

6206

6207 should be exactly equivalent to::

6208

6209 s = select(table1.c.b)

6210

6211 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6212 will also dynamically alter the FROM clause of the

6213 statement if it is not explicitly stated.

6214 To maintain the existing set of FROMs including those implied by the

6215 current columns clause, add the

6216 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6217 parameter::

6218

6219 s = select(table1.c.a, table2.c.b)

6220 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6221

6222 The above parameter performs a transfer of the effective FROMs

6223 in the columns collection to the :meth:`_sql.Select.select_from`

6224 method, as though the following were invoked::

6225

6226 s = select(table1.c.a, table2.c.b)

6227 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6228

6229 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6230 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6231 collection and performs an operation equivalent to the following::

6232

6233 s = select(table1.c.a, table2.c.b)

6234 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6235

6236 :param \*entities: column expressions to be used.

6237

6238 :param maintain_column_froms: boolean parameter that will ensure the

6239 FROM list implied from the current columns clause will be transferred

6240 to the :meth:`_sql.Select.select_from` method first.

6241

6242 .. versionadded:: 1.4.23

6243

6244 """ # noqa: E501

6245

6246 if __kw:

6247 raise _no_kw()

6248

6249 # memoizations should be cleared here as of

6250 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6251 # is the case for now.

6252 self._assert_no_memoizations()

6253

6254 if maintain_column_froms:

6255 self.select_from.non_generative( # type: ignore

6256 self, *self.columns_clause_froms

6257 )

6258

6259 # then memoize the FROMs etc.

6260 _MemoizedSelectEntities._generate_for_statement(self)

6261

6262 self._raw_columns = [

6263 coercions.expect(roles.ColumnsClauseRole, c)

6264 for c in coercions._expression_collection_was_a_list(

6265 "entities", "Select.with_only_columns", entities

6266 )

6267 ]

6268 return self

6269

6270 @property

6271 def whereclause(self) -> Optional[ColumnElement[Any]]:

6272 """Return the completed WHERE clause for this

6273 :class:`_expression.Select` statement.

6274

6275 This assembles the current collection of WHERE criteria

6276 into a single :class:`_expression.BooleanClauseList` construct.

6277

6278

6279 .. versionadded:: 1.4

6280

6281 """

6282

6283 return BooleanClauseList._construct_for_whereclause(

6284 self._where_criteria

6285 )

6286

6287 _whereclause = whereclause

6288

6289 @_generative

6290 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6291 """Return a new :func:`_expression.select` construct with

6292 the given expression added to

6293 its WHERE clause, joined to the existing clause via AND, if any.

6294

6295 """

6296

6297 assert isinstance(self._where_criteria, tuple)

6298

6299 for criterion in whereclause:

6300 where_criteria: ColumnElement[Any] = coercions.expect(

6301 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6302 )

6303 self._where_criteria += (where_criteria,)

6304 return self

6305

6306 @_generative

6307 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6308 """Return a new :func:`_expression.select` construct with

6309 the given expression added to

6310 its HAVING clause, joined to the existing clause via AND, if any.

6311

6312 """

6313

6314 for criterion in having:

6315 having_criteria = coercions.expect(

6316 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6317 )

6318 self._having_criteria += (having_criteria,)

6319 return self

6320

6321 @_generative

6322 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6323 r"""Return a new :func:`_expression.select` construct which

6324 will apply DISTINCT to the SELECT statement overall.

6325

6326 E.g.::

6327

6328 from sqlalchemy import select

6329

6330 stmt = select(users_table.c.id, users_table.c.name).distinct()

6331

6332 The above would produce an statement resembling:

6333

6334 .. sourcecode:: sql

6335

6336 SELECT DISTINCT user.id, user.name FROM user

6337

6338 The method also historically accepted an ``*expr`` parameter which

6339 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6340 This is now replaced using the :func:`_postgresql.distinct_on`

6341 extension::

6342

6343 from sqlalchemy import select

6344 from sqlalchemy.dialects.postgresql import distinct_on

6345

6346 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6347

6348 Using this parameter on other backends which don't support this

6349 syntax will raise an error.

6350

6351 :param \*expr: optional column expressions. When present,

6352 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6353 construct. A deprecation warning and/or :class:`_exc.CompileError`

6354 will be raised on other backends.

6355

6356 .. deprecated:: 2.1 Passing expressions to

6357 :meth:`_sql.Select.distinct` is deprecated, use

6358 :func:`_postgresql.distinct_on` instead.

6359

6360 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6361 and will raise :class:`_exc.CompileError` in a future version.

6362

6363 .. seealso::

6364

6365 :func:`_postgresql.distinct_on`

6366

6367 :meth:`_sql.HasSyntaxExtensions.ext`

6368 """

6369 self._distinct = True

6370 if expr:

6371 warn_deprecated(

6372 "Passing expression to ``distinct`` to generate a "

6373 "DISTINCT ON clause is deprecated. Use instead the "

6374 "``postgresql.distinct_on`` function as an extension.",

6375 "2.1",

6376 )

6377 self._distinct_on = self._distinct_on + tuple(

6378 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6379 for e in expr

6380 )

6381 return self

6382

6383 @_generative

6384 def select_from(self, *froms: _FromClauseArgument) -> Self:

6385 r"""Return a new :func:`_expression.select` construct with the

6386 given FROM expression(s)

6387 merged into its list of FROM objects.

6388

6389 E.g.::

6390

6391 table1 = table("t1", column("a"))

6392 table2 = table("t2", column("b"))

6393 s = select(table1.c.a).select_from(

6394 table1.join(table2, table1.c.a == table2.c.b)

6395 )

6396

6397 The "from" list is a unique set on the identity of each element,

6398 so adding an already present :class:`_schema.Table`

6399 or other selectable

6400 will have no effect. Passing a :class:`_expression.Join` that refers

6401 to an already present :class:`_schema.Table`

6402 or other selectable will have

6403 the effect of concealing the presence of that selectable as

6404 an individual element in the rendered FROM list, instead

6405 rendering it into a JOIN clause.

6406

6407 While the typical purpose of :meth:`_expression.Select.select_from`

6408 is to

6409 replace the default, derived FROM clause with a join, it can

6410 also be called with individual table elements, multiple times

6411 if desired, in the case that the FROM clause cannot be fully

6412 derived from the columns clause::

6413

6414 select(func.count("*")).select_from(table1)

6415

6416 """

6417

6418 self._from_obj += tuple(

6419 coercions.expect(

6420 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6421 )

6422 for fromclause in froms

6423 )

6424 return self

6425

6426 @_generative

6427 def correlate(

6428 self,

6429 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6430 ) -> Self:

6431 r"""Return a new :class:`_expression.Select`

6432 which will correlate the given FROM

6433 clauses to that of an enclosing :class:`_expression.Select`.

6434

6435 Calling this method turns off the :class:`_expression.Select` object's

6436 default behavior of "auto-correlation". Normally, FROM elements

6437 which appear in a :class:`_expression.Select`

6438 that encloses this one via

6439 its :term:`WHERE clause`, ORDER BY, HAVING or

6440 :term:`columns clause` will be omitted from this

6441 :class:`_expression.Select`

6442 object's :term:`FROM clause`.

6443 Setting an explicit correlation collection using the

6444 :meth:`_expression.Select.correlate`

6445 method provides a fixed list of FROM objects

6446 that can potentially take place in this process.

6447

6448 When :meth:`_expression.Select.correlate`

6449 is used to apply specific FROM clauses

6450 for correlation, the FROM elements become candidates for

6451 correlation regardless of how deeply nested this

6452 :class:`_expression.Select`

6453 object is, relative to an enclosing :class:`_expression.Select`

6454 which refers to

6455 the same FROM object. This is in contrast to the behavior of

6456 "auto-correlation" which only correlates to an immediate enclosing

6457 :class:`_expression.Select`.

6458 Multi-level correlation ensures that the link

6459 between enclosed and enclosing :class:`_expression.Select`

6460 is always via

6461 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6462 correlation to take place.

6463

6464 If ``None`` is passed, the :class:`_expression.Select`

6465 object will correlate

6466 none of its FROM entries, and all will render unconditionally

6467 in the local FROM clause.

6468

6469 :param \*fromclauses: one or more :class:`.FromClause` or other

6470 FROM-compatible construct such as an ORM mapped entity to become part

6471 of the correlate collection; alternatively pass a single value

6472 ``None`` to remove all existing correlations.

6473

6474 .. seealso::

6475

6476 :meth:`_expression.Select.correlate_except`

6477

6478 :ref:`tutorial_scalar_subquery`

6479

6480 """

6481

6482 # tests failing when we try to change how these

6483 # arguments are passed

6484

6485 self._auto_correlate = False

6486 if not fromclauses or fromclauses[0] in {None, False}:

6487 if len(fromclauses) > 1:

6488 raise exc.ArgumentError(

6489 "additional FROM objects not accepted when "

6490 "passing None/False to correlate()"

6491 )

6492 self._correlate = ()

6493 else:

6494 self._correlate = self._correlate + tuple(

6495 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6496 )

6497 return self

6498

6499 @_generative

6500 def correlate_except(

6501 self,

6502 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6503 ) -> Self:

6504 r"""Return a new :class:`_expression.Select`

6505 which will omit the given FROM

6506 clauses from the auto-correlation process.

6507

6508 Calling :meth:`_expression.Select.correlate_except` turns off the

6509 :class:`_expression.Select` object's default behavior of

6510 "auto-correlation" for the given FROM elements. An element

6511 specified here will unconditionally appear in the FROM list, while

6512 all other FROM elements remain subject to normal auto-correlation

6513 behaviors.

6514

6515 If ``None`` is passed, or no arguments are passed,

6516 the :class:`_expression.Select` object will correlate all of its

6517 FROM entries.

6518

6519 :param \*fromclauses: a list of one or more

6520 :class:`_expression.FromClause`

6521 constructs, or other compatible constructs (i.e. ORM-mapped

6522 classes) to become part of the correlate-exception collection.

6523

6524 .. seealso::

6525

6526 :meth:`_expression.Select.correlate`

6527

6528 :ref:`tutorial_scalar_subquery`

6529

6530 """

6531

6532 self._auto_correlate = False

6533 if not fromclauses or fromclauses[0] in {None, False}:

6534 if len(fromclauses) > 1:

6535 raise exc.ArgumentError(

6536 "additional FROM objects not accepted when "

6537 "passing None/False to correlate_except()"

6538 )

6539 self._correlate_except = ()

6540 else:

6541 self._correlate_except = (self._correlate_except or ()) + tuple(

6542 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6543 )

6544

6545 return self

6546

6547 @HasMemoized_ro_memoized_attribute

6548 def selected_columns(

6549 self,

6550 ) -> ColumnCollection[str, ColumnElement[Any]]:

6551 """A :class:`_expression.ColumnCollection`

6552 representing the columns that

6553 this SELECT statement or similar construct returns in its result set,

6554 not including :class:`_sql.TextClause` constructs.

6555

6556 This collection differs from the :attr:`_expression.FromClause.columns`

6557 collection of a :class:`_expression.FromClause` in that the columns

6558 within this collection cannot be directly nested inside another SELECT

6559 statement; a subquery must be applied first which provides for the

6560 necessary parenthesization required by SQL.

6561

6562 For a :func:`_expression.select` construct, the collection here is

6563 exactly what would be rendered inside the "SELECT" statement, and the

6564 :class:`_expression.ColumnElement` objects are directly present as they

6565 were given, e.g.::

6566

6567 col1 = column("q", Integer)

6568 col2 = column("p", Integer)

6569 stmt = select(col1, col2)

6570

6571 Above, ``stmt.selected_columns`` would be a collection that contains

6572 the ``col1`` and ``col2`` objects directly. For a statement that is

6573 against a :class:`_schema.Table` or other

6574 :class:`_expression.FromClause`, the collection will use the

6575 :class:`_expression.ColumnElement` objects that are in the

6576 :attr:`_expression.FromClause.c` collection of the from element.

6577

6578 A use case for the :attr:`_sql.Select.selected_columns` collection is

6579 to allow the existing columns to be referenced when adding additional

6580 criteria, e.g.::

6581

6582 def filter_on_id(my_select, id):

6583 return my_select.where(my_select.selected_columns["id"] == id)

6584

6585

6586 stmt = select(MyModel)

6587

6588 # adds "WHERE id=:param" to the statement

6589 stmt = filter_on_id(stmt, 42)

6590

6591 .. note::

6592

6593 The :attr:`_sql.Select.selected_columns` collection does not

6594 include expressions established in the columns clause using the

6595 :func:`_sql.text` construct; these are silently omitted from the

6596 collection. To use plain textual column expressions inside of a

6597 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6598 construct.

6599

6600

6601 .. versionadded:: 1.4

6602

6603 """

6604

6605 # compare to SelectState._generate_columns_plus_names, which

6606 # generates the actual names used in the SELECT string. that

6607 # method is more complex because it also renders columns that are

6608 # fully ambiguous, e.g. same column more than once.

6609 conv = cast(

6610 "Callable[[Any], str]",

6611 SelectState._column_naming_convention(self._label_style),

6612 )

6613

6614 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6615 [

6616 (conv(c), c)

6617 for c in self._all_selected_columns

6618 if is_column_element(c)

6619 ]

6620 )

6621 return cc.as_readonly()

6622

6623 @HasMemoized_ro_memoized_attribute

6624 def _all_selected_columns(self) -> _SelectIterable:

6625 meth = SelectState.get_plugin_class(self).all_selected_columns

6626 return list(meth(self))

6627

6628 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6629 if self._label_style is LABEL_STYLE_NONE:

6630 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6631 return self

6632

6633 def _generate_fromclause_column_proxies(

6634 self,

6635 subquery: FromClause,

6636 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6637 primary_key: ColumnSet,

6638 foreign_keys: Set[KeyedColumnElement[Any]],

6639 *,

6640 proxy_compound_columns: Optional[

6641 Iterable[Sequence[ColumnElement[Any]]]

6642 ] = None,

6643 ) -> None:

6644 """Generate column proxies to place in the exported ``.c``

6645 collection of a subquery."""

6646

6647 if proxy_compound_columns:

6648 extra_col_iterator = proxy_compound_columns

6649 prox = [

6650 c._make_proxy(

6651 subquery,

6652 key=proxy_key,

6653 name=required_label_name,

6654 name_is_truncatable=True,

6655 compound_select_cols=extra_cols,

6656 primary_key=primary_key,

6657 foreign_keys=foreign_keys,

6658 )

6659 for (

6660 (

6661 required_label_name,

6662 proxy_key,

6663 fallback_label_name,

6664 c,

6665 repeated,

6666 ),

6667 extra_cols,

6668 ) in (

6669 zip(

6670 self._generate_columns_plus_names(False),

6671 extra_col_iterator,

6672 )

6673 )

6674 if is_column_element(c)

6675 ]

6676 else:

6677 prox = [

6678 c._make_proxy(

6679 subquery,

6680 key=proxy_key,

6681 name=required_label_name,

6682 name_is_truncatable=True,

6683 primary_key=primary_key,

6684 foreign_keys=foreign_keys,

6685 )

6686 for (

6687 required_label_name,

6688 proxy_key,

6689 fallback_label_name,

6690 c,

6691 repeated,

6692 ) in (self._generate_columns_plus_names(False))

6693 if is_column_element(c)

6694 ]

6695

6696 columns._populate_separate_keys(prox)

6697

6698 def _needs_parens_for_grouping(self) -> bool:

6699 return self._has_row_limiting_clause or bool(

6700 self._order_by_clause.clauses

6701 )

6702

6703 def self_group(

6704 self, against: Optional[OperatorType] = None

6705 ) -> Union[SelectStatementGrouping[Self], Self]:

6706 """Return a 'grouping' construct as per the

6707 :class:`_expression.ClauseElement` specification.

6708

6709 This produces an element that can be embedded in an expression. Note

6710 that this method is called automatically as needed when constructing

6711 expressions and should not require explicit use.

6712

6713 """

6714 if (

6715 isinstance(against, CompoundSelect)

6716 and not self._needs_parens_for_grouping()

6717 ):

6718 return self

6719 else:

6720 return SelectStatementGrouping(self)

6721

6722 def union(

6723 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6724 ) -> CompoundSelect[Unpack[_Ts]]:

6725 r"""Return a SQL ``UNION`` of this select() construct against

6726 the given selectables provided as positional arguments.

6727

6728 :param \*other: one or more elements with which to create a

6729 UNION.

6730

6731 .. versionchanged:: 1.4.28

6732

6733 multiple elements are now accepted.

6734

6735 :param \**kwargs: keyword arguments are forwarded to the constructor

6736 for the newly created :class:`_sql.CompoundSelect` object.

6737

6738 """

6739 return CompoundSelect._create_union(self, *other)

6740

6741 def union_all(

6742 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6743 ) -> CompoundSelect[Unpack[_Ts]]:

6744 r"""Return a SQL ``UNION ALL`` of this select() construct against

6745 the given selectables provided as positional arguments.

6746

6747 :param \*other: one or more elements with which to create a

6748 UNION.

6749

6750 .. versionchanged:: 1.4.28

6751

6752 multiple elements are now accepted.

6753

6754 :param \**kwargs: keyword arguments are forwarded to the constructor

6755 for the newly created :class:`_sql.CompoundSelect` object.

6756

6757 """

6758 return CompoundSelect._create_union_all(self, *other)

6759

6760 def except_(

6761 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6762 ) -> CompoundSelect[Unpack[_Ts]]:

6763 r"""Return a SQL ``EXCEPT`` of this select() construct against

6764 the given selectable provided as positional arguments.

6765

6766 :param \*other: one or more elements with which to create a

6767 UNION.

6768

6769 .. versionchanged:: 1.4.28

6770

6771 multiple elements are now accepted.

6772

6773 """

6774 return CompoundSelect._create_except(self, *other)

6775

6776 def except_all(

6777 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6778 ) -> CompoundSelect[Unpack[_Ts]]:

6779 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6780 the given selectables provided as positional arguments.

6781

6782 :param \*other: one or more elements with which to create a

6783 UNION.

6784

6785 .. versionchanged:: 1.4.28

6786

6787 multiple elements are now accepted.

6788

6789 """

6790 return CompoundSelect._create_except_all(self, *other)

6791

6792 def intersect(

6793 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6794 ) -> CompoundSelect[Unpack[_Ts]]:

6795 r"""Return a SQL ``INTERSECT`` of this select() construct against

6796 the given selectables provided as positional arguments.

6797

6798 :param \*other: one or more elements with which to create a

6799 UNION.

6800

6801 .. versionchanged:: 1.4.28

6802

6803 multiple elements are now accepted.

6804

6805 :param \**kwargs: keyword arguments are forwarded to the constructor

6806 for the newly created :class:`_sql.CompoundSelect` object.

6807

6808 """

6809 return CompoundSelect._create_intersect(self, *other)

6810

6811 def intersect_all(

6812 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6813 ) -> CompoundSelect[Unpack[_Ts]]:

6814 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6815 against the given selectables provided as positional arguments.

6816

6817 :param \*other: one or more elements with which to create a

6818 UNION.

6819

6820 .. versionchanged:: 1.4.28

6821

6822 multiple elements are now accepted.

6823

6824 :param \**kwargs: keyword arguments are forwarded to the constructor

6825 for the newly created :class:`_sql.CompoundSelect` object.

6826

6827 """

6828 return CompoundSelect._create_intersect_all(self, *other)

6829

6830

6831class ScalarSelect(

6832 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6833):

6834 """Represent a scalar subquery.

6835

6836

6837 A :class:`_sql.ScalarSelect` is created by invoking the

6838 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6839 then participates in other SQL expressions as a SQL column expression

6840 within the :class:`_sql.ColumnElement` hierarchy.

6841

6842 .. seealso::

6843

6844 :meth:`_sql.SelectBase.scalar_subquery`

6845

6846 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6847

6848 """

6849

6850 _traverse_internals: _TraverseInternalsType = [

6851 ("element", InternalTraversal.dp_clauseelement),

6852 ("type", InternalTraversal.dp_type),

6853 ]

6854

6855 _from_objects: List[FromClause] = []

6856 _is_from_container = True

6857 if not TYPE_CHECKING:

6858 _is_implicitly_boolean = False

6859 inherit_cache = True

6860

6861 element: SelectBase

6862

6863 def __init__(self, element: SelectBase) -> None:

6864 self.element = element

6865 self.type = element._scalar_type()

6866 self._propagate_attrs = element._propagate_attrs

6867

6868 def __getattr__(self, attr: str) -> Any:

6869 return getattr(self.element, attr)

6870

6871 def __getstate__(self) -> Dict[str, Any]:

6872 return {"element": self.element, "type": self.type}

6873

6874 def __setstate__(self, state: Dict[str, Any]) -> None:

6875 self.element = state["element"]

6876 self.type = state["type"]

6877

6878 @property

6879 def columns(self) -> NoReturn:

6880 raise exc.InvalidRequestError(

6881 "Scalar Select expression has no "

6882 "columns; use this object directly "

6883 "within a column-level expression."

6884 )

6885

6886 c = columns

6887

6888 @_generative

6889 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6890 """Apply a WHERE clause to the SELECT statement referred to

6891 by this :class:`_expression.ScalarSelect`.

6892

6893 """

6894 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6895 crit

6896 )

6897 return self

6898

6899 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6900 return self

6901

6902 def _ungroup(self) -> Self:

6903 return self

6904

6905 @_generative

6906 def correlate(

6907 self,

6908 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6909 ) -> Self:

6910 r"""Return a new :class:`_expression.ScalarSelect`

6911 which will correlate the given FROM

6912 clauses to that of an enclosing :class:`_expression.Select`.

6913

6914 This method is mirrored from the :meth:`_sql.Select.correlate` method

6915 of the underlying :class:`_sql.Select`. The method applies the

6916 :meth:_sql.Select.correlate` method, then returns a new

6917 :class:`_sql.ScalarSelect` against that statement.

6918

6919 .. versionadded:: 1.4 Previously, the

6920 :meth:`_sql.ScalarSelect.correlate`

6921 method was only available from :class:`_sql.Select`.

6922

6923 :param \*fromclauses: a list of one or more

6924 :class:`_expression.FromClause`

6925 constructs, or other compatible constructs (i.e. ORM-mapped

6926 classes) to become part of the correlate collection.

6927

6928 .. seealso::

6929

6930 :meth:`_expression.ScalarSelect.correlate_except`

6931

6932 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6933

6934

6935 """

6936 self.element = cast(

6937 "Select[Unpack[TupleAny]]", self.element

6938 ).correlate(*fromclauses)

6939 return self

6940

6941 @_generative

6942 def correlate_except(

6943 self,

6944 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6945 ) -> Self:

6946 r"""Return a new :class:`_expression.ScalarSelect`

6947 which will omit the given FROM

6948 clauses from the auto-correlation process.

6949

6950 This method is mirrored from the

6951 :meth:`_sql.Select.correlate_except` method of the underlying

6952 :class:`_sql.Select`. The method applies the

6953 :meth:_sql.Select.correlate_except` method, then returns a new

6954 :class:`_sql.ScalarSelect` against that statement.

6955

6956 .. versionadded:: 1.4 Previously, the

6957 :meth:`_sql.ScalarSelect.correlate_except`

6958 method was only available from :class:`_sql.Select`.

6959

6960 :param \*fromclauses: a list of one or more

6961 :class:`_expression.FromClause`

6962 constructs, or other compatible constructs (i.e. ORM-mapped

6963 classes) to become part of the correlate-exception collection.

6964

6965 .. seealso::

6966

6967 :meth:`_expression.ScalarSelect.correlate`

6968

6969 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6970

6971

6972 """

6973

6974 self.element = cast(

6975 "Select[Unpack[TupleAny]]", self.element

6976 ).correlate_except(*fromclauses)

6977 return self

6978

6979

6980class Exists(UnaryExpression[bool]):

6981 """Represent an ``EXISTS`` clause.

6982

6983 See :func:`_sql.exists` for a description of usage.

6984

6985 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6986 instance by calling :meth:`_sql.SelectBase.exists`.

6987

6988 """

6989

6990 inherit_cache = True

6991

6992 def __init__(

6993 self,

6994 __argument: Optional[

6995 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6996 ] = None,

6997 /,

6998 ):

6999 s: ScalarSelect[Any]

7000

7001 # TODO: this seems like we should be using coercions for this

7002 if __argument is None:

7003 s = Select(literal_column("*")).scalar_subquery()

7004 elif isinstance(__argument, SelectBase):

7005 s = __argument.scalar_subquery()

7006 s._propagate_attrs = __argument._propagate_attrs

7007 elif isinstance(__argument, ScalarSelect):

7008 s = __argument

7009 else:

7010 s = Select(__argument).scalar_subquery()

7011

7012 UnaryExpression.__init__(

7013 self,

7014 s,

7015 operator=operators.exists,

7016 type_=type_api.BOOLEANTYPE,

7017 )

7018

7019 @util.ro_non_memoized_property

7020 def _from_objects(self) -> List[FromClause]:

7021 return []

7022

7023 def _regroup(

7024 self,

7025 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7026 ) -> ScalarSelect[Any]:

7027

7028 assert isinstance(self.element, ScalarSelect)

7029 element = self.element.element

7030 if not isinstance(element, Select):

7031 raise exc.InvalidRequestError(

7032 "Can only apply this operation to a plain SELECT construct"

7033 )

7034 new_element = fn(element)

7035

7036 return_value = new_element.scalar_subquery()

7037 return return_value

7038

7039 def select(self) -> Select[bool]:

7040 r"""Return a SELECT of this :class:`_expression.Exists`.

7041

7042 e.g.::

7043

7044 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7045

7046 This will produce a statement resembling:

7047

7048 .. sourcecode:: sql

7049

7050 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7051

7052 .. seealso::

7053

7054 :func:`_expression.select` - general purpose

7055 method which allows for arbitrary column lists.

7056

7057 """ # noqa

7058

7059 return Select(self)

7060

7061 def correlate(

7062 self,

7063 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7064 ) -> Self:

7065 """Apply correlation to the subquery noted by this

7066 :class:`_sql.Exists`.

7067

7068 .. seealso::

7069

7070 :meth:`_sql.ScalarSelect.correlate`

7071

7072 """

7073 e = self._clone()

7074 e.element = self._regroup(

7075 lambda element: element.correlate(*fromclauses)

7076 )

7077 return e

7078

7079 def correlate_except(

7080 self,

7081 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7082 ) -> Self:

7083 """Apply correlation to the subquery noted by this

7084 :class:`_sql.Exists`.

7085

7086 .. seealso::

7087

7088 :meth:`_sql.ScalarSelect.correlate_except`

7089

7090 """

7091 e = self._clone()

7092 e.element = self._regroup(

7093 lambda element: element.correlate_except(*fromclauses)

7094 )

7095 return e

7096

7097 def select_from(self, *froms: _FromClauseArgument) -> Self:

7098 """Return a new :class:`_expression.Exists` construct,

7099 applying the given

7100 expression to the :meth:`_expression.Select.select_from`

7101 method of the select

7102 statement contained.

7103

7104 .. note:: it is typically preferable to build a :class:`_sql.Select`

7105 statement first, including the desired WHERE clause, then use the

7106 :meth:`_sql.SelectBase.exists` method to produce an

7107 :class:`_sql.Exists` object at once.

7108

7109 """

7110 e = self._clone()

7111 e.element = self._regroup(lambda element: element.select_from(*froms))

7112 return e

7113

7114 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7115 """Return a new :func:`_expression.exists` construct with the

7116 given expression added to

7117 its WHERE clause, joined to the existing clause via AND, if any.

7118

7119

7120 .. note:: it is typically preferable to build a :class:`_sql.Select`

7121 statement first, including the desired WHERE clause, then use the

7122 :meth:`_sql.SelectBase.exists` method to produce an

7123 :class:`_sql.Exists` object at once.

7124

7125 """

7126 e = self._clone()

7127 e.element = self._regroup(lambda element: element.where(*clause))

7128 return e

7129

7130

7131class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7132 """Wrap a :class:`_expression.TextClause` construct within a

7133 :class:`_expression.SelectBase`

7134 interface.

7135

7136 This allows the :class:`_expression.TextClause` object to gain a

7137 ``.c`` collection

7138 and other FROM-like capabilities such as

7139 :meth:`_expression.FromClause.alias`,

7140 :meth:`_expression.SelectBase.cte`, etc.

7141

7142 The :class:`_expression.TextualSelect` construct is produced via the

7143 :meth:`_expression.TextClause.columns`

7144 method - see that method for details.

7145

7146 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7147 class was renamed

7148 from ``TextAsFrom``, to more correctly suit its role as a

7149 SELECT-oriented object and not a FROM clause.

7150

7151 .. seealso::

7152

7153 :func:`_expression.text`

7154

7155 :meth:`_expression.TextClause.columns` - primary creation interface.

7156

7157 """

7158

7159 __visit_name__ = "textual_select"

7160

7161 _label_style = LABEL_STYLE_NONE

7162

7163 _traverse_internals: _TraverseInternalsType = (

7164 [

7165 ("element", InternalTraversal.dp_clauseelement),

7166 ("column_args", InternalTraversal.dp_clauseelement_list),

7167 ]

7168 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7169 + HasCTE._has_ctes_traverse_internals

7170 + Executable._executable_traverse_internals

7171 )

7172

7173 _is_textual = True

7174

7175 is_text = True

7176 is_select = True

7177

7178 def __init__(

7179 self,

7180 text: TextClause,

7181 columns: List[_ColumnExpressionArgument[Any]],

7182 positional: bool = False,

7183 ) -> None:

7184 self._init(

7185 text,

7186 # convert for ORM attributes->columns, etc

7187 [

7188 coercions.expect(roles.LabeledColumnExprRole, c)

7189 for c in columns

7190 ],

7191 positional,

7192 )

7193

7194 def _init(

7195 self,

7196 text: TextClause,

7197 columns: List[NamedColumn[Any]],

7198 positional: bool = False,

7199 ) -> None:

7200 self.element = text

7201 self.column_args = columns

7202 self.positional = positional

7203

7204 @HasMemoized_ro_memoized_attribute

7205 def selected_columns(

7206 self,

7207 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7208 """A :class:`_expression.ColumnCollection`

7209 representing the columns that

7210 this SELECT statement or similar construct returns in its result set,

7211 not including :class:`_sql.TextClause` constructs.

7212

7213 This collection differs from the :attr:`_expression.FromClause.columns`

7214 collection of a :class:`_expression.FromClause` in that the columns

7215 within this collection cannot be directly nested inside another SELECT

7216 statement; a subquery must be applied first which provides for the

7217 necessary parenthesization required by SQL.

7218

7219 For a :class:`_expression.TextualSelect` construct, the collection

7220 contains the :class:`_expression.ColumnElement` objects that were

7221 passed to the constructor, typically via the

7222 :meth:`_expression.TextClause.columns` method.

7223

7224

7225 .. versionadded:: 1.4

7226

7227 """

7228 return ColumnCollection(

7229 (c.key, c) for c in self.column_args

7230 ).as_readonly()

7231

7232 @util.ro_non_memoized_property

7233 def _all_selected_columns(self) -> _SelectIterable:

7234 return self.column_args

7235

7236 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7237 return self

7238

7239 def _ensure_disambiguated_names(self) -> TextualSelect:

7240 return self

7241

7242 @_generative

7243 def bindparams(

7244 self,

7245 *binds: BindParameter[Any],

7246 **bind_as_values: Any,

7247 ) -> Self:

7248 self.element = self.element.bindparams(*binds, **bind_as_values)

7249 return self

7250

7251 def _generate_fromclause_column_proxies(

7252 self,

7253 fromclause: FromClause,

7254 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7255 primary_key: ColumnSet,

7256 foreign_keys: Set[KeyedColumnElement[Any]],

7257 *,

7258 proxy_compound_columns: Optional[

7259 Iterable[Sequence[ColumnElement[Any]]]

7260 ] = None,

7261 ) -> None:

7262 if TYPE_CHECKING:

7263 assert isinstance(fromclause, Subquery)

7264

7265 if proxy_compound_columns:

7266 columns._populate_separate_keys(

7267 c._make_proxy(

7268 fromclause,

7269 compound_select_cols=extra_cols,

7270 primary_key=primary_key,

7271 foreign_keys=foreign_keys,

7272 )

7273 for c, extra_cols in zip(

7274 self.column_args, proxy_compound_columns

7275 )

7276 )

7277 else:

7278 columns._populate_separate_keys(

7279 c._make_proxy(

7280 fromclause,

7281 primary_key=primary_key,

7282 foreign_keys=foreign_keys,

7283 )

7284 for c in self.column_args

7285 )

7286

7287 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7288 return self.column_args[0].type

7289

7290

7291TextAsFrom = TextualSelect

7292"""Backwards compatibility with the previous name"""

7293

7294

7295class AnnotatedFromClause(Annotated):

7296 def _copy_internals(self, **kw: Any) -> None:

7297 super()._copy_internals(**kw)

7298 if kw.get("ind_cols_on_fromclause", False):

7299 ee = self._Annotated__element # type: ignore

7300

7301 self.c = ee.__class__.c.fget(self) # type: ignore

7302

7303 @util.ro_memoized_property

7304 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7305 """proxy the .c collection of the underlying FromClause.

7306

7307 Originally implemented in 2008 as a simple load of the .c collection

7308 when the annotated construct was created (see d3621ae961a), in modern

7309 SQLAlchemy versions this can be expensive for statements constructed

7310 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7311 it, which works just as well.

7312

7313 Two different use cases seem to require the collection either copied

7314 from the underlying one, or unique to this AnnotatedFromClause.

7315

7316 See test_selectable->test_annotated_corresponding_column

7317

7318 """

7319 ee = self._Annotated__element # type: ignore

7320 return ee.c # type: ignore