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

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

1171 FROM table1 JOIN table2 ON true

1172

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

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

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

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

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

1178

1179

1180 .. versionadded:: 1.4

1181

1182 """ # noqa: E501

1183

1184 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1187 when generating the columns clause of a SELECT statement.

1188

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

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

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

1192

1193 .. sourcecode:: pycon+sql

1194

1195 >>> from sqlalchemy import (

1196 ... table,

1197 ... column,

1198 ... select,

1199 ... true,

1200 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1201 ... )

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

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

1204 >>> print(

1205 ... select(table1, table2)

1206 ... .join(table2, true())

1207 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1208 ... )

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

1210 FROM table1 JOIN table2 ON true

1211

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

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

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

1215

1216 .. versionadded:: 1.4

1217

1218 """ # noqa: E501

1219

1220 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1221 """The default label style, refers to

1222 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1223

1224 .. versionadded:: 1.4

1225

1226 """

1227

1228 LABEL_STYLE_LEGACY_ORM = 3

1229

1230

1231(

1232 LABEL_STYLE_NONE,

1233 LABEL_STYLE_TABLENAME_PLUS_COL,

1234 LABEL_STYLE_DISAMBIGUATE_ONLY,

1235 _,

1236) = list(SelectLabelStyle)

1237

1238LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1239

1240

1241class Join(roles.DMLTableRole, FromClause):

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

1243 :class:`_expression.FromClause`

1244 elements.

1245

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

1247 is the module-level

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

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

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

1251 :class:`_schema.Table`).

1252

1253 .. seealso::

1254

1255 :func:`_expression.join`

1256

1257 :meth:`_expression.FromClause.join`

1258

1259 """

1260

1261 __visit_name__ = "join"

1262

1263 _traverse_internals: _TraverseInternalsType = [

1264 ("left", InternalTraversal.dp_clauseelement),

1265 ("right", InternalTraversal.dp_clauseelement),

1266 ("onclause", InternalTraversal.dp_clauseelement),

1267 ("isouter", InternalTraversal.dp_boolean),

1268 ("full", InternalTraversal.dp_boolean),

1269 ]

1270

1271 _is_join = True

1272

1273 left: FromClause

1274 right: FromClause

1275 onclause: Optional[ColumnElement[bool]]

1276 isouter: bool

1277 full: bool

1278

1279 def __init__(

1280 self,

1281 left: _FromClauseArgument,

1282 right: _FromClauseArgument,

1283 onclause: Optional[_OnClauseArgument] = None,

1284 isouter: bool = False,

1285 full: bool = False,

1286 ):

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

1288

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

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

1291 :class:`_expression.FromClause` object.

1292

1293 """

1294

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

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

1297 # annotated objects. test_orm.py -> test_fetch_results

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

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

1300 # callcounts for a single compilation in that particular test

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

1302 # 29200 -> 30373

1303

1304 self.left = coercions.expect(

1305 roles.FromClauseRole,

1306 left,

1307 )

1308 self.right = coercions.expect(

1309 roles.FromClauseRole,

1310 right,

1311 ).self_group()

1312

1313 if onclause is None:

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

1315 else:

1316 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1317 # not merged yet

1318 self.onclause = coercions.expect(

1319 roles.OnClauseRole, onclause

1320 ).self_group(against=operators._asbool)

1321

1322 self.isouter = isouter

1323 self.full = full

1324

1325 @util.ro_non_memoized_property

1326 def description(self) -> str:

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

1328 self.left.description,

1329 id(self.left),

1330 self.right.description,

1331 id(self.right),

1332 )

1333

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

1335 return (

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

1337 # as well

1338 hash(fromclause) == hash(self)

1339 or self.left.is_derived_from(fromclause)

1340 or self.right.is_derived_from(fromclause)

1341 )

1342

1343 def self_group(

1344 self, against: Optional[OperatorType] = None

1345 ) -> FromGrouping:

1346 return FromGrouping(self)

1347

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

1349 def _populate_column_collection(

1350 self,

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

1352 primary_key: ColumnSet,

1353 foreign_keys: Set[KeyedColumnElement[Any]],

1354 ) -> None:

1355 sqlutil = util.preloaded.sql_util

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

1357 c for c in self.right.c

1358 ]

1359

1360 primary_key.extend(

1361 sqlutil.reduce_columns(

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

1363 )

1364 )

1365 columns._populate_separate_keys(

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

1367 )

1368 foreign_keys.update(

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

1370 )

1371

1372 def _copy_internals(

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

1374 ) -> None:

1375 # see Select._copy_internals() for similar concept

1376

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

1378 # determine the new FROM clauses

1379 all_the_froms = set(

1380 itertools.chain(

1381 _from_objects(self.left),

1382 _from_objects(self.right),

1383 )

1384 )

1385

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

1387 # cache used by the clone function

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

1389

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

1391 # ColumnClause with parent table referring to those

1392 # replaced FromClause objects

1393 def replace(

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

1395 **kw: Any,

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

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

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

1399 return newelem

1400 return None

1401

1402 kw["replace"] = replace

1403

1404 # run normal _copy_internals. the clones for

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

1406 # cache

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

1408

1409 self._reset_memoizations()

1410

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

1412 super()._refresh_for_new_column(column)

1413 self.left._refresh_for_new_column(column)

1414 self.right._refresh_for_new_column(column)

1415

1416 def _match_primaries(

1417 self,

1418 left: FromClause,

1419 right: FromClause,

1420 ) -> ColumnElement[bool]:

1421 if isinstance(left, Join):

1422 left_right = left.right

1423 else:

1424 left_right = None

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

1426

1427 @classmethod

1428 def _join_condition(

1429 cls,

1430 a: FromClause,

1431 b: FromClause,

1432 *,

1433 a_subset: Optional[FromClause] = None,

1434 consider_as_foreign_keys: Optional[

1435 AbstractSet[ColumnClause[Any]]

1436 ] = None,

1437 ) -> ColumnElement[bool]:

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

1439

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

1441

1442 """

1443 constraints = cls._joincond_scan_left_right(

1444 a, a_subset, b, consider_as_foreign_keys

1445 )

1446

1447 if len(constraints) > 1:

1448 cls._joincond_trim_constraints(

1449 a, b, constraints, consider_as_foreign_keys

1450 )

1451

1452 if len(constraints) == 0:

1453 if isinstance(b, FromGrouping):

1454 hint = (

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

1456 "subquery using alias()?"

1457 )

1458 else:

1459 hint = ""

1460 raise exc.NoForeignKeysError(

1461 "Can't find any foreign key relationships "

1462 "between '%s' and '%s'.%s"

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

1464 )

1465

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

1467 if len(crit) == 1:

1468 return crit[0]

1469 else:

1470 return and_(*crit)

1471

1472 @classmethod

1473 def _can_join(

1474 cls,

1475 left: FromClause,

1476 right: FromClause,

1477 *,

1478 consider_as_foreign_keys: Optional[

1479 AbstractSet[ColumnClause[Any]]

1480 ] = None,

1481 ) -> bool:

1482 if isinstance(left, Join):

1483 left_right = left.right

1484 else:

1485 left_right = None

1486

1487 constraints = cls._joincond_scan_left_right(

1488 a=left,

1489 b=right,

1490 a_subset=left_right,

1491 consider_as_foreign_keys=consider_as_foreign_keys,

1492 )

1493

1494 return bool(constraints)

1495

1496 @classmethod

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

1498 def _joincond_scan_left_right(

1499 cls,

1500 a: FromClause,

1501 a_subset: Optional[FromClause],

1502 b: FromClause,

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

1504 ) -> collections.defaultdict[

1505 Optional[ForeignKeyConstraint],

1506 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1507 ]:

1508 sql_util = util.preloaded.sql_util

1509

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

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

1512

1513 constraints: collections.defaultdict[

1514 Optional[ForeignKeyConstraint],

1515 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1516 ] = collections.defaultdict(list)

1517

1518 for left in (a_subset, a):

1519 if left is None:

1520 continue

1521 for fk in sorted(

1522 b.foreign_keys,

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

1524 ):

1525 if (

1526 consider_as_foreign_keys is not None

1527 and fk.parent not in consider_as_foreign_keys

1528 ):

1529 continue

1530 try:

1531 col = fk.get_referent(left)

1532 except exc.NoReferenceError as nrte:

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

1534 if nrte.table_name in table_names:

1535 raise

1536 else:

1537 continue

1538

1539 if col is not None:

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

1541 if left is not b:

1542 for fk in sorted(

1543 left.foreign_keys,

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

1545 ):

1546 if (

1547 consider_as_foreign_keys is not None

1548 and fk.parent not in consider_as_foreign_keys

1549 ):

1550 continue

1551 try:

1552 col = fk.get_referent(b)

1553 except exc.NoReferenceError as nrte:

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

1555 if nrte.table_name in table_names:

1556 raise

1557 else:

1558 continue

1559

1560 if col is not None:

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

1562 if constraints:

1563 break

1564 return constraints

1565

1566 @classmethod

1567 def _joincond_trim_constraints(

1568 cls,

1569 a: FromClause,

1570 b: FromClause,

1571 constraints: Dict[Any, Any],

1572 consider_as_foreign_keys: Optional[Any],

1573 ) -> None:

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

1575 # to include just those FKCs that match exactly to

1576 # "consider_as_foreign_keys".

1577 if consider_as_foreign_keys:

1578 for const in list(constraints):

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

1580 consider_as_foreign_keys

1581 ):

1582 del constraints[const]

1583

1584 # if still multiple constraints, but

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

1586 if len(constraints) > 1:

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

1588 if len(dedupe) == 1:

1589 key = list(constraints)[0]

1590 constraints = {key: constraints[key]}

1591

1592 if len(constraints) != 1:

1593 raise exc.AmbiguousForeignKeysError(

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

1595 "tables have more than one foreign key "

1596 "constraint relationship between them. "

1597 "Please specify the 'onclause' of this "

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

1599 )

1600

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

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

1603 :class:`_expression.Join`.

1604

1605 E.g.::

1606

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

1608

1609 stmt = stmt.select()

1610

1611 The above will produce a SQL string resembling:

1612

1613 .. sourcecode:: sql

1614

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

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

1617

1618 """

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

1620

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

1622 def _anonymous_fromclause(

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

1624 ) -> TODO_Any:

1625 sqlutil = util.preloaded.sql_util

1626 if flat:

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

1628 left_name = name # will recurse

1629 else:

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

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

1632 else:

1633 left_name = name

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

1635 right_name = name # will recurse

1636 else:

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

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

1639 else:

1640 right_name = name

1641 left_a, right_a = (

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

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

1644 )

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

1646 sqlutil.ClauseAdapter(right_a)

1647 )

1648

1649 return left_a.join(

1650 right_a,

1651 adapter.traverse(self.onclause),

1652 isouter=self.isouter,

1653 full=self.full,

1654 )

1655 else:

1656 return (

1657 self.select()

1658 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1659 .correlate(None)

1660 .alias(name)

1661 )

1662

1663 @util.ro_non_memoized_property

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

1665 return itertools.chain(

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

1667 )

1668

1669 @util.ro_non_memoized_property

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

1671 self_list: List[FromClause] = [self]

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

1673

1674

1675class NoInit:

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

1677 raise NotImplementedError(

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

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

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

1681 "selectable objects."

1682 % (

1683 self.__class__.__name__,

1684 self.__class__.__name__.lower(),

1685 self.__class__.__name__.lower(),

1686 )

1687 )

1688

1689

1690class LateralFromClause(NamedFromClause):

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

1692

1693

1694# FromClause ->

1695# AliasedReturnsRows

1696# -> Alias only for FromClause

1697# -> Subquery only for SelectBase

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

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

1700# w/ non-deprecated coercion

1701# -> TableSample -> only for FromClause

1702

1703

1704class AliasedReturnsRows(NoInit, NamedFromClause):

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

1706 selectables."""

1707

1708 _is_from_container = True

1709

1710 _supports_derived_columns = False

1711

1712 element: ReturnsRows

1713

1714 _traverse_internals: _TraverseInternalsType = [

1715 ("element", InternalTraversal.dp_clauseelement),

1716 ("name", InternalTraversal.dp_anon_name),

1717 ]

1718

1719 @classmethod

1720 def _construct(

1721 cls,

1722 selectable: Any,

1723 *,

1724 name: Optional[str] = None,

1725 **kw: Any,

1726 ) -> Self:

1727 obj = cls.__new__(cls)

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

1729 return obj

1730

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

1732 self.element = coercions.expect(

1733 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1734 )

1735 self.element = selectable

1736 self._orig_name = name

1737 if name is None:

1738 if (

1739 isinstance(selectable, FromClause)

1740 and selectable.named_with_column

1741 ):

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

1743 if isinstance(name, _anonymous_label):

1744 name = None

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

1746 self.name = name

1747

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

1749 super()._refresh_for_new_column(column)

1750 self.element._refresh_for_new_column(column)

1751

1752 def _populate_column_collection(

1753 self,

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

1755 primary_key: ColumnSet,

1756 foreign_keys: Set[KeyedColumnElement[Any]],

1757 ) -> None:

1758 self.element._generate_fromclause_column_proxies(

1759 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1760 )

1761

1762 @util.ro_non_memoized_property

1763 def description(self) -> str:

1764 name = self.name

1765 if isinstance(name, _anonymous_label):

1766 return "anon_1"

1767

1768 return name

1769

1770 @util.ro_non_memoized_property

1771 def implicit_returning(self) -> bool:

1772 return self.element.implicit_returning # type: ignore

1773

1774 @property

1775 def original(self) -> ReturnsRows:

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

1777 return self.element

1778

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

1780 if fromclause in self._cloned_set:

1781 return True

1782 return self.element.is_derived_from(fromclause)

1783

1784 def _copy_internals(

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

1786 ) -> None:

1787 existing_element = self.element

1788

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

1790

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

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

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

1794 # performance.

1795 if existing_element is not self.element:

1796 self._reset_column_collection()

1797

1798 @property

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

1800 return [self]

1801

1802

1803class FromClauseAlias(AliasedReturnsRows):

1804 element: FromClause

1805

1806 @util.ro_non_memoized_property

1807 def description(self) -> str:

1808 name = self.name

1809 if isinstance(name, _anonymous_label):

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

1811

1812 return name

1813

1814

1815class Alias(roles.DMLTableRole, FromClauseAlias):

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

1817

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

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

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

1821

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

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

1824 method available

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

1826

1827 .. seealso::

1828

1829 :meth:`_expression.FromClause.alias`

1830

1831 """

1832

1833 __visit_name__ = "alias"

1834

1835 inherit_cache = True

1836

1837 element: FromClause

1838

1839 @classmethod

1840 def _factory(

1841 cls,

1842 selectable: FromClause,

1843 name: Optional[str] = None,

1844 flat: bool = False,

1845 ) -> NamedFromClause:

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

1847 name=name, flat=flat

1848 )

1849

1850

1851class TableValuedAlias(LateralFromClause, Alias):

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

1853

1854 This construct provides for a SQL function that returns columns

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

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

1857 method, e.g.:

1858

1859 .. sourcecode:: pycon+sql

1860

1861 >>> from sqlalchemy import select, func

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

1863 ... "value"

1864 ... )

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

1866 {printsql}SELECT anon_1.value

1867 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1868

1869 .. versionadded:: 1.4.0b2

1870

1871 .. seealso::

1872

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

1874

1875 """ # noqa: E501

1876

1877 __visit_name__ = "table_valued_alias"

1878

1879 _supports_derived_columns = True

1880 _render_derived = False

1881 _render_derived_w_types = False

1882 joins_implicitly = False

1883

1884 _traverse_internals: _TraverseInternalsType = [

1885 ("element", InternalTraversal.dp_clauseelement),

1886 ("name", InternalTraversal.dp_anon_name),

1887 ("_tableval_type", InternalTraversal.dp_type),

1888 ("_render_derived", InternalTraversal.dp_boolean),

1889 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1890 ]

1891

1892 def _init(

1893 self,

1894 selectable: Any,

1895 *,

1896 name: Optional[str] = None,

1897 table_value_type: Optional[TableValueType] = None,

1898 joins_implicitly: bool = False,

1899 ) -> None:

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

1901

1902 self.joins_implicitly = joins_implicitly

1903 self._tableval_type = (

1904 type_api.TABLEVALUE

1905 if table_value_type is None

1906 else table_value_type

1907 )

1908

1909 @HasMemoized.memoized_attribute

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

1911 """Return a column expression representing this

1912 :class:`_sql.TableValuedAlias`.

1913

1914 This accessor is used to implement the

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

1916 method for further details.

1917

1918 E.g.:

1919

1920 .. sourcecode:: pycon+sql

1921

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

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

1924

1925 .. seealso::

1926

1927 :meth:`_functions.FunctionElement.column_valued`

1928

1929 """

1930

1931 return TableValuedColumn(self, self._tableval_type)

1932

1933 def alias(

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

1935 ) -> TableValuedAlias:

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

1937

1938 This creates a distinct FROM object that will be distinguished

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

1940

1941 """

1942

1943 tva: TableValuedAlias = TableValuedAlias._construct(

1944 self,

1945 name=name,

1946 table_value_type=self._tableval_type,

1947 joins_implicitly=self.joins_implicitly,

1948 )

1949

1950 if self._render_derived:

1951 tva._render_derived = True

1952 tva._render_derived_w_types = self._render_derived_w_types

1953

1954 return tva

1955

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

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

1958 set, so that it renders as LATERAL.

1959

1960 .. seealso::

1961

1962 :func:`_expression.lateral`

1963

1964 """

1965 tva = self.alias(name=name)

1966 tva._is_lateral = True

1967 return tva

1968

1969 def render_derived(

1970 self,

1971 name: Optional[str] = None,

1972 with_types: bool = False,

1973 ) -> TableValuedAlias:

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

1975

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

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

1978

1979 .. sourcecode:: pycon+sql

1980

1981 >>> print(

1982 ... select(

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

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

1985 ... .render_derived()

1986 ... )

1987 ... )

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

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

1990

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

1992 the alias expression (this syntax currently applies to the

1993 PostgreSQL database):

1994

1995 .. sourcecode:: pycon+sql

1996

1997 >>> print(

1998 ... select(

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

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

2001 ... .render_derived(with_types=True)

2002 ... )

2003 ... )

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

2005 AS anon_1(a INTEGER, b VARCHAR)

2006

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

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

2009

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

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

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

2013

2014 """ # noqa: E501

2015

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

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

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

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

2020 # (just saw it happen on CI)

2021

2022 # construct against original to prevent memory growth

2023 # for repeated generations

2024 new_alias: TableValuedAlias = TableValuedAlias._construct(

2025 self.element,

2026 name=name,

2027 table_value_type=self._tableval_type,

2028 joins_implicitly=self.joins_implicitly,

2029 )

2030 new_alias._render_derived = True

2031 new_alias._render_derived_w_types = with_types

2032 return new_alias

2033

2034

2035class Lateral(FromClauseAlias, LateralFromClause):

2036 """Represent a LATERAL subquery.

2037

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

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

2040 method available

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

2042

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

2044 PostgreSQL versions provide support for this keyword.

2045

2046 .. seealso::

2047

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

2049

2050 """

2051

2052 __visit_name__ = "lateral"

2053 _is_lateral = True

2054

2055 inherit_cache = True

2056

2057 @classmethod

2058 def _factory(

2059 cls,

2060 selectable: Union[SelectBase, _FromClauseArgument],

2061 name: Optional[str] = None,

2062 ) -> LateralFromClause:

2063 return coercions.expect(

2064 roles.FromClauseRole, selectable, explicit_subquery=True

2065 ).lateral(name=name)

2066

2067

2068class TableSample(FromClauseAlias):

2069 """Represent a TABLESAMPLE clause.

2070

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

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

2073 method

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

2075

2076 .. seealso::

2077

2078 :func:`_expression.tablesample`

2079

2080 """

2081

2082 __visit_name__ = "tablesample"

2083

2084 _traverse_internals: _TraverseInternalsType = (

2085 AliasedReturnsRows._traverse_internals

2086 + [

2087 ("sampling", InternalTraversal.dp_clauseelement),

2088 ("seed", InternalTraversal.dp_clauseelement),

2089 ]

2090 )

2091

2092 @classmethod

2093 def _factory(

2094 cls,

2095 selectable: _FromClauseArgument,

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

2097 name: Optional[str] = None,

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

2099 ) -> TableSample:

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

2101 sampling, name=name, seed=seed

2102 )

2103

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

2105 def _init( # type: ignore[override]

2106 self,

2107 selectable: Any,

2108 *,

2109 name: Optional[str] = None,

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

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

2112 ) -> None:

2113 assert sampling is not None

2114 functions = util.preloaded.sql_functions

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

2116 sampling = functions.func.system(sampling)

2117

2118 self.sampling: Function[Any] = sampling

2119 self.seed = seed

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

2121

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

2123 return self.sampling

2124

2125

2126class CTE(

2127 roles.DMLTableRole,

2128 roles.IsCTERole,

2129 Generative,

2130 HasPrefixes,

2131 HasSuffixes,

2132 AliasedReturnsRows,

2133):

2134 """Represent a Common Table Expression.

2135

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

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

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

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

2140 :class:`_sql.Update` and

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

2142 usage details on CTEs.

2143

2144 .. seealso::

2145

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

2147

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

2149

2150 """

2151

2152 __visit_name__ = "cte"

2153

2154 _traverse_internals: _TraverseInternalsType = (

2155 AliasedReturnsRows._traverse_internals

2156 + [

2157 ("_cte_alias", InternalTraversal.dp_clauseelement),

2158 ("_restates", InternalTraversal.dp_clauseelement),

2159 ("recursive", InternalTraversal.dp_boolean),

2160 ("nesting", InternalTraversal.dp_boolean),

2161 ]

2162 + HasPrefixes._has_prefixes_traverse_internals

2163 + HasSuffixes._has_suffixes_traverse_internals

2164 )

2165

2166 element: HasCTE

2167

2168 @classmethod

2169 def _factory(

2170 cls,

2171 selectable: HasCTE,

2172 name: Optional[str] = None,

2173 recursive: bool = False,

2174 ) -> CTE:

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

2176 or Common Table Expression instance.

2177

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

2179

2180 """

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

2182 name=name, recursive=recursive

2183 )

2184

2185 def _init(

2186 self,

2187 selectable: HasCTE,

2188 *,

2189 name: Optional[str] = None,

2190 recursive: bool = False,

2191 nesting: bool = False,

2192 _cte_alias: Optional[CTE] = None,

2193 _restates: Optional[CTE] = None,

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

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

2196 ) -> None:

2197 self.recursive = recursive

2198 self.nesting = nesting

2199 self._cte_alias = _cte_alias

2200 # Keep recursivity reference with union/union_all

2201 self._restates = _restates

2202 if _prefixes:

2203 self._prefixes = _prefixes

2204 if _suffixes:

2205 self._suffixes = _suffixes

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

2207

2208 def _populate_column_collection(

2209 self,

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

2211 primary_key: ColumnSet,

2212 foreign_keys: Set[KeyedColumnElement[Any]],

2213 ) -> None:

2214 if self._cte_alias is not None:

2215 self._cte_alias._generate_fromclause_column_proxies(

2216 self,

2217 columns,

2218 primary_key=primary_key,

2219 foreign_keys=foreign_keys,

2220 )

2221 else:

2222 self.element._generate_fromclause_column_proxies(

2223 self,

2224 columns,

2225 primary_key=primary_key,

2226 foreign_keys=foreign_keys,

2227 )

2228

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

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

2231 :class:`_expression.CTE`.

2232

2233 This method is a CTE-specific specialization of the

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

2235

2236 .. seealso::

2237

2238 :ref:`tutorial_using_aliases`

2239

2240 :func:`_expression.alias`

2241

2242 """

2243 return CTE._construct(

2244 self.element,

2245 name=name,

2246 recursive=self.recursive,

2247 nesting=self.nesting,

2248 _cte_alias=self,

2249 _prefixes=self._prefixes,

2250 _suffixes=self._suffixes,

2251 )

2252

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

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

2255 of the original CTE against the given selectables provided

2256 as positional arguments.

2257

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

2259 UNION.

2260

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

2262

2263 .. seealso::

2264

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

2266

2267 """

2268 assert is_select_statement(

2269 self.element

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

2271

2272 return CTE._construct(

2273 self.element.union(*other),

2274 name=self.name,

2275 recursive=self.recursive,

2276 nesting=self.nesting,

2277 _restates=self,

2278 _prefixes=self._prefixes,

2279 _suffixes=self._suffixes,

2280 )

2281

2282 def union_all(

2283 self, *other: _SelectStatementForCompoundArgument[Any]

2284 ) -> CTE:

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

2286 of the original CTE against the given selectables provided

2287 as positional arguments.

2288

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

2290 UNION.

2291

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

2293

2294 .. seealso::

2295

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

2297

2298 """

2299

2300 assert is_select_statement(

2301 self.element

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

2303

2304 return CTE._construct(

2305 self.element.union_all(*other),

2306 name=self.name,

2307 recursive=self.recursive,

2308 nesting=self.nesting,

2309 _restates=self,

2310 _prefixes=self._prefixes,

2311 _suffixes=self._suffixes,

2312 )

2313

2314 def _get_reference_cte(self) -> CTE:

2315 """

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

2317 Updated CTEs should still refer to the original CTE.

2318 This function returns this reference identifier.

2319 """

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

2321

2322

2323class _CTEOpts(NamedTuple):

2324 nesting: bool

2325

2326

2327class _ColumnsPlusNames(NamedTuple):

2328 required_label_name: Optional[str]

2329 """

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

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

2332 """

2333

2334 proxy_key: Optional[str]

2335 """

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

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

2338 select.selected_columns collection

2339 """

2340

2341 fallback_label_name: Optional[str]

2342 """

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

2344 we have to render a label even though

2345 required_label_name was not given

2346 """

2347

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

2349 """

2350 the ColumnElement itself

2351 """

2352

2353 repeated: bool

2354 """

2355 True if this is a duplicate of a previous column

2356 in the list of columns

2357 """

2358

2359

2360class SelectsRows(ReturnsRows):

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

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

2363

2364 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2365

2366 def _generate_columns_plus_names(

2367 self,

2368 anon_for_dupe_key: bool,

2369 cols: Optional[_SelectIterable] = None,

2370 ) -> List[_ColumnsPlusNames]:

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

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

2373 on a :class:`.FromClause`.

2374

2375 This is distinct from the _column_naming_convention generator that's

2376 intended for population of the Select.selected_columns collection,

2377 different rules. the collection returned here calls upon the

2378 _column_naming_convention as well.

2379

2380 """

2381

2382 if cols is None:

2383 cols = self._all_selected_columns

2384

2385 key_naming_convention = SelectState._column_naming_convention(

2386 self._label_style

2387 )

2388

2389 names = {}

2390

2391 result: List[_ColumnsPlusNames] = []

2392 result_append = result.append

2393

2394 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2395 label_style_none = self._label_style is LABEL_STYLE_NONE

2396

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

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

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

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

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

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

2403 dedupe_hash = 1

2404

2405 for c in cols:

2406 repeated = False

2407

2408 if not c._render_label_in_columns_clause:

2409 effective_name = required_label_name = fallback_label_name = (

2410 None

2411 )

2412 elif label_style_none:

2413 if TYPE_CHECKING:

2414 assert is_column_element(c)

2415

2416 effective_name = required_label_name = None

2417 fallback_label_name = c._non_anon_label or c._anon_name_label

2418 else:

2419 if TYPE_CHECKING:

2420 assert is_column_element(c)

2421

2422 if table_qualified:

2423 required_label_name = effective_name = (

2424 fallback_label_name

2425 ) = c._tq_label

2426 else:

2427 effective_name = fallback_label_name = c._non_anon_label

2428 required_label_name = None

2429

2430 if effective_name is None:

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

2432 # not need _expression_label but it isn't

2433 # giving us a clue when to use anon_label instead

2434 expr_label = c._expression_label

2435 if expr_label is None:

2436 repeated = c._anon_name_label in names

2437 names[c._anon_name_label] = c

2438 effective_name = required_label_name = None

2439

2440 if repeated:

2441 # here, "required_label_name" is sent as

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

2443 if table_qualified:

2444 fallback_label_name = (

2445 c._dedupe_anon_tq_label_idx(dedupe_hash)

2446 )

2447 dedupe_hash += 1

2448 else:

2449 fallback_label_name = c._dedupe_anon_label_idx(

2450 dedupe_hash

2451 )

2452 dedupe_hash += 1

2453 else:

2454 fallback_label_name = c._anon_name_label

2455 else:

2456 required_label_name = effective_name = (

2457 fallback_label_name

2458 ) = expr_label

2459

2460 if effective_name is not None:

2461 if TYPE_CHECKING:

2462 assert is_column_element(c)

2463

2464 if effective_name in names:

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

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

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

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

2469 # different column under the same name. apply

2470 # disambiguating label

2471 if table_qualified:

2472 required_label_name = fallback_label_name = (

2473 c._anon_tq_label

2474 )

2475 else:

2476 required_label_name = fallback_label_name = (

2477 c._anon_name_label

2478 )

2479

2480 if anon_for_dupe_key and required_label_name in names:

2481 # here, c._anon_tq_label is definitely unique to

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

2483 # this should always be true.

2484 # this is also an infrequent codepath because

2485 # you need two levels of duplication to be here

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

2487

2488 # the column under the disambiguating label is

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

2490 # subsequent occurrences of the column so that the

2491 # original stays non-ambiguous

2492 if table_qualified:

2493 required_label_name = fallback_label_name = (

2494 c._dedupe_anon_tq_label_idx(dedupe_hash)

2495 )

2496 dedupe_hash += 1

2497 else:

2498 required_label_name = fallback_label_name = (

2499 c._dedupe_anon_label_idx(dedupe_hash)

2500 )

2501 dedupe_hash += 1

2502 repeated = True

2503 else:

2504 names[required_label_name] = c

2505 elif anon_for_dupe_key:

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

2507 # label so that the original stays non-ambiguous

2508 if table_qualified:

2509 required_label_name = fallback_label_name = (

2510 c._dedupe_anon_tq_label_idx(dedupe_hash)

2511 )

2512 dedupe_hash += 1

2513 else:

2514 required_label_name = fallback_label_name = (

2515 c._dedupe_anon_label_idx(dedupe_hash)

2516 )

2517 dedupe_hash += 1

2518 repeated = True

2519 else:

2520 names[effective_name] = c

2521

2522 result_append(

2523 _ColumnsPlusNames(

2524 required_label_name,

2525 key_naming_convention(c),

2526 fallback_label_name,

2527 c,

2528 repeated,

2529 )

2530 )

2531

2532 return result

2533

2534

2535class HasCTE(roles.HasCTERole, SelectsRows):

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

2537

2538 _has_ctes_traverse_internals: _TraverseInternalsType = [

2539 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2540 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2541 ]

2542

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

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

2545

2546 name_cte_columns: bool = False

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

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

2549

2550 .. versionadded:: 2.0.42

2551

2552 """

2553

2554 @_generative

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

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

2557

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

2559 the parent statement such that they will each be unconditionally

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

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

2562

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

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

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

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

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

2568 statement.

2569

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

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

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

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

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

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

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

2577 larger statement.

2578

2579 E.g.::

2580

2581 from sqlalchemy import table, column, select

2582

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

2584

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

2586

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

2588

2589 Would render:

2590

2591 .. sourcecode:: sql

2592

2593 WITH anon_1 AS (

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

2595 )

2596 SELECT t.c1, t.c2

2597 FROM t

2598

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

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

2601 statement.

2602

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

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

2605

2606 from sqlalchemy import table, column

2607 from sqlalchemy.dialects.postgresql import insert

2608

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

2610

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

2612

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

2614 update_statement = insert_stmt.on_conflict_do_update(

2615 index_elements=[t.c.c1],

2616 set_={

2617 "c1": insert_stmt.excluded.c1,

2618 "c2": insert_stmt.excluded.c2,

2619 },

2620 ).add_cte(delete_statement_cte)

2621

2622 print(update_statement)

2623

2624 The above statement renders as:

2625

2626 .. sourcecode:: sql

2627

2628 WITH deletions AS (

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

2630 )

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

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

2633

2634 .. versionadded:: 1.4.21

2635

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

2637

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

2639

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

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

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

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

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

2645 this statement when this flag is given.

2646

2647 .. versionadded:: 2.0

2648

2649 .. seealso::

2650

2651 :paramref:`.HasCTE.cte.nesting`

2652

2653

2654 """ # noqa: E501

2655 opt = _CTEOpts(nest_here)

2656 for cte in ctes:

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

2658 self._independent_ctes += (cte,)

2659 self._independent_ctes_opts += (opt,)

2660 return self

2661

2662 def cte(

2663 self,

2664 name: Optional[str] = None,

2665 recursive: bool = False,

2666 nesting: bool = False,

2667 ) -> CTE:

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

2669 or Common Table Expression instance.

2670

2671 Common table expressions are a SQL standard whereby SELECT

2672 statements can draw upon secondary statements specified along

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

2674 Special semantics regarding UNION can also be employed to

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

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

2677

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

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

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

2681 CTE rows.

2682

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

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

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

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

2687

2688 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2690 method may be

2691 used to establish these.

2692

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

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

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

2696 compile time.

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

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

2699 conjunction with UNION ALL in order to derive rows

2700 from those already selected.

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

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

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

2704 :paramref:`.HasCTE.add_cte.nest_here`

2705 parameter may also be used to more carefully

2706 control the exact placement of a particular CTE.

2707

2708 .. versionadded:: 1.4.24

2709

2710 .. seealso::

2711

2712 :meth:`.HasCTE.add_cte`

2713

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

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

2716 as well as additional examples.

2717

2718 Example 1, non recursive::

2719

2720 from sqlalchemy import (

2721 Table,

2722 Column,

2723 String,

2724 Integer,

2725 MetaData,

2726 select,

2727 func,

2728 )

2729

2730 metadata = MetaData()

2731

2732 orders = Table(

2733 "orders",

2734 metadata,

2735 Column("region", String),

2736 Column("amount", Integer),

2737 Column("product", String),

2738 Column("quantity", Integer),

2739 )

2740

2741 regional_sales = (

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

2743 .group_by(orders.c.region)

2744 .cte("regional_sales")

2745 )

2746

2747

2748 top_regions = (

2749 select(regional_sales.c.region)

2750 .where(

2751 regional_sales.c.total_sales

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

2753 )

2754 .cte("top_regions")

2755 )

2756

2757 statement = (

2758 select(

2759 orders.c.region,

2760 orders.c.product,

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

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

2763 )

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

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

2766 )

2767

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

2769

2770 Example 2, WITH RECURSIVE::

2771

2772 from sqlalchemy import (

2773 Table,

2774 Column,

2775 String,

2776 Integer,

2777 MetaData,

2778 select,

2779 func,

2780 )

2781

2782 metadata = MetaData()

2783

2784 parts = Table(

2785 "parts",

2786 metadata,

2787 Column("part", String),

2788 Column("sub_part", String),

2789 Column("quantity", Integer),

2790 )

2791

2792 included_parts = (

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

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

2795 .cte(recursive=True)

2796 )

2797

2798

2799 incl_alias = included_parts.alias()

2800 parts_alias = parts.alias()

2801 included_parts = included_parts.union_all(

2802 select(

2803 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2805 )

2806

2807 statement = select(

2808 included_parts.c.sub_part,

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

2810 ).group_by(included_parts.c.sub_part)

2811

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

2813

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

2815

2816 from datetime import date

2817 from sqlalchemy import (

2818 MetaData,

2819 Table,

2820 Column,

2821 Integer,

2822 Date,

2823 select,

2824 literal,

2825 and_,

2826 exists,

2827 )

2828

2829 metadata = MetaData()

2830

2831 visitors = Table(

2832 "visitors",

2833 metadata,

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

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

2836 Column("count", Integer),

2837 )

2838

2839 # add 5 visitors for the product_id == 1

2840 product_id = 1

2841 day = date.today()

2842 count = 5

2843

2844 update_cte = (

2845 visitors.update()

2846 .where(

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

2848 )

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

2850 .returning(literal(1))

2851 .cte("update_cte")

2852 )

2853

2854 upsert = visitors.insert().from_select(

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

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

2857 ~exists(update_cte.select())

2858 ),

2859 )

2860

2861 connection.execute(upsert)

2862

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

2864

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

2866

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

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

2869 "value_a", nesting=True

2870 )

2871

2872 # Nesting CTEs takes ascendency locally

2873 # over the CTEs at a higher level

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

2875

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

2877

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

2879 shown with inline parameters below as:

2880

2881 .. sourcecode:: sql

2882

2883 WITH

2884 value_a AS

2885 (SELECT 'root' AS n),

2886 value_b AS

2887 (WITH value_a AS

2888 (SELECT 'nesting' AS n)

2889 SELECT value_a.n AS n FROM value_a)

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

2891 FROM value_a, value_b

2892

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

2894 as follows (SQLAlchemy 2.0 and above)::

2895

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

2897

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

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

2900

2901 # Nesting CTEs takes ascendency locally

2902 # over the CTEs at a higher level

2903 value_b = (

2904 select(value_a_nested.c.n)

2905 .add_cte(value_a_nested, nest_here=True)

2906 .cte("value_b")

2907 )

2908

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

2910

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

2912

2913 edge = Table(

2914 "edge",

2915 metadata,

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

2917 Column("left", Integer),

2918 Column("right", Integer),

2919 )

2920

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

2922

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

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

2925 )

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

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

2928 )

2929

2930 subgraph_cte = root_node.union(left_edge, right_edge)

2931

2932 subgraph = select(subgraph_cte)

2933

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

2935

2936 .. sourcecode:: sql

2937

2938 WITH RECURSIVE nodes(node) AS (

2939 SELECT 1 AS node

2940 UNION

2941 SELECT edge."left" AS "left"

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

2943 UNION

2944 SELECT edge."right" AS "right"

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

2946 )

2947 SELECT nodes.node FROM nodes

2948

2949 .. seealso::

2950

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

2952 :meth:`_expression.HasCTE.cte`.

2953

2954 """ # noqa: E501

2955 return CTE._construct(

2956 self, name=name, recursive=recursive, nesting=nesting

2957 )

2958

2959

2960class Subquery(AliasedReturnsRows):

2961 """Represent a subquery of a SELECT.

2962

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

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

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

2966 :class:`_expression.SelectBase` subclass

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

2968 :class:`_expression.CompoundSelect`, and

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

2970 it represents the

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

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

2973

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

2975 :class:`_expression.Alias`

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

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

2978 :class:`_expression.Alias` always

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

2980 :class:`.Subquery`

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

2982

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

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

2985 statement.

2986

2987 """

2988

2989 __visit_name__ = "subquery"

2990

2991 _is_subquery = True

2992

2993 inherit_cache = True

2994

2995 element: SelectBase

2996

2997 @classmethod

2998 def _factory(

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

3000 ) -> Subquery:

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

3002

3003 return coercions.expect(

3004 roles.SelectStatementRole, selectable

3005 ).subquery(name=name)

3006

3007 @util.deprecated(

3008 "1.4",

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

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

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

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

3013 ":func:`_expression.select` "

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

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

3016 )

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

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

3019

3020

3021class FromGrouping(GroupedElement, FromClause):

3022 """Represent a grouping of a FROM clause"""

3023

3024 _traverse_internals: _TraverseInternalsType = [

3025 ("element", InternalTraversal.dp_clauseelement)

3026 ]

3027

3028 element: FromClause

3029

3030 def __init__(self, element: FromClause):

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

3032

3033 @util.ro_non_memoized_property

3034 def columns(

3035 self,

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

3037 return self.element.columns

3038

3039 @util.ro_non_memoized_property

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

3041 return self.element.columns

3042

3043 @property

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

3045 return self.element.primary_key

3046

3047 @property

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

3049 return self.element.foreign_keys

3050

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

3052 return self.element.is_derived_from(fromclause)

3053

3054 def alias(

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

3056 ) -> NamedFromGrouping:

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

3058

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

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

3061

3062 @util.ro_non_memoized_property

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

3064 return self.element._hide_froms

3065

3066 @util.ro_non_memoized_property

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

3068 return self.element._from_objects

3069

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

3071 return {"element": self.element}

3072

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

3074 self.element = state["element"]

3075

3076 if TYPE_CHECKING:

3077

3078 def self_group(

3079 self, against: Optional[OperatorType] = None

3080 ) -> Self: ...

3081

3082

3083class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3085

3086 .. versionadded:: 2.0

3087

3088 """

3089

3090 inherit_cache = True

3091

3092 if TYPE_CHECKING:

3093

3094 def self_group(

3095 self, against: Optional[OperatorType] = None

3096 ) -> Self: ...

3097

3098

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

3100 """Represents a minimal "table" construct.

3101

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

3103 collection of columns, which are typically produced

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

3105

3106 from sqlalchemy import table, column

3107

3108 user = table(

3109 "user",

3110 column("id"),

3111 column("name"),

3112 column("description"),

3113 )

3114

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

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

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

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

3119

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

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

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

3123 It's useful

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

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

3126 is not on hand.

3127

3128 """

3129

3130 __visit_name__ = "table"

3131

3132 _traverse_internals: _TraverseInternalsType = [

3133 (

3134 "columns",

3135 InternalTraversal.dp_fromclause_canonical_column_collection,

3136 ),

3137 ("name", InternalTraversal.dp_string),

3138 ("schema", InternalTraversal.dp_string),

3139 ]

3140

3141 _is_table = True

3142

3143 fullname: str

3144

3145 implicit_returning = False

3146 """:class:`_expression.TableClause`

3147 doesn't support having a primary key or column

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

3149

3150 _columns: DedupeColumnCollection[ColumnClause[Any]]

3151

3152 @util.ro_memoized_property

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

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

3155 return None

3156

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

3158 super().__init__()

3159 self.name = name

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

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

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

3163 for c in columns:

3164 self.append_column(c)

3165

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

3167 if schema is not None:

3168 self.schema = schema

3169 if self.schema is not None:

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

3171 else:

3172 self.fullname = self.name

3173 if kw:

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

3175

3176 if TYPE_CHECKING:

3177

3178 @util.ro_non_memoized_property

3179 def columns(

3180 self,

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

3182

3183 @util.ro_non_memoized_property

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

3185

3186 def __str__(self) -> str:

3187 if self.schema is not None:

3188 return self.schema + "." + self.name

3189 else:

3190 return self.name

3191

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

3193 pass

3194

3195 @util.ro_memoized_property

3196 def description(self) -> str:

3197 return self.name

3198

3199 def _insert_col_impl(

3200 self,

3201 c: ColumnClause[Any],

3202 *,

3203 index: Optional[int] = None,

3204 ) -> None:

3205 existing = c.table

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

3207 raise exc.ArgumentError(

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

3209 % (c.key, existing)

3210 )

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

3212 c.table = self

3213

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

3215 self._insert_col_impl(c)

3216

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

3218 self._insert_col_impl(c, index=index)

3219

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

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

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

3223 :class:`_expression.TableClause`.

3224

3225 E.g.::

3226

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

3228

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

3230

3231 """

3232

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

3234

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

3236 def update(self) -> Update:

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

3238 :class:`_expression.TableClause`.

3239

3240 E.g.::

3241

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

3243

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

3245

3246 """

3247 return util.preloaded.sql_dml.Update(

3248 self,

3249 )

3250

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

3252 def delete(self) -> Delete:

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

3254 :class:`_expression.TableClause`.

3255

3256 E.g.::

3257

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

3259

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

3261

3262 """

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

3264

3265 @util.ro_non_memoized_property

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

3267 return [self]

3268

3269

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

3271

3272

3273class ForUpdateArg(ClauseElement):

3274 _traverse_internals: _TraverseInternalsType = [

3275 ("of", InternalTraversal.dp_clauseelement_list),

3276 ("nowait", InternalTraversal.dp_boolean),

3277 ("read", InternalTraversal.dp_boolean),

3278 ("skip_locked", InternalTraversal.dp_boolean),

3279 ("key_share", InternalTraversal.dp_boolean),

3280 ]

3281

3282 of: Optional[Sequence[ClauseElement]]

3283 nowait: bool

3284 read: bool

3285 skip_locked: bool

3286

3287 @classmethod

3288 def _from_argument(

3289 cls, with_for_update: ForUpdateParameter

3290 ) -> Optional[ForUpdateArg]:

3291 if isinstance(with_for_update, ForUpdateArg):

3292 return with_for_update

3293 elif with_for_update in (None, False):

3294 return None

3295 elif with_for_update is True:

3296 return ForUpdateArg()

3297 else:

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

3299

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

3301 return (

3302 isinstance(other, ForUpdateArg)

3303 and other.nowait == self.nowait

3304 and other.read == self.read

3305 and other.skip_locked == self.skip_locked

3306 and other.key_share == self.key_share

3307 and other.of is self.of

3308 )

3309

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

3311 return not self.__eq__(other)

3312

3313 def __hash__(self) -> int:

3314 return id(self)

3315

3316 def __init__(

3317 self,

3318 *,

3319 nowait: bool = False,

3320 read: bool = False,

3321 of: Optional[_ForUpdateOfArgument] = None,

3322 skip_locked: bool = False,

3323 key_share: bool = False,

3324 ):

3325 """Represents arguments specified to

3326 :meth:`_expression.Select.for_update`.

3327

3328 """

3329

3330 self.nowait = nowait

3331 self.read = read

3332 self.skip_locked = skip_locked

3333 self.key_share = key_share

3334 if of is not None:

3335 self.of = [

3336 coercions.expect(roles.ColumnsClauseRole, elem)

3337 for elem in util.to_list(of)

3338 ]

3339 else:

3340 self.of = None

3341

3342

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

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

3345 in a statement.

3346

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

3348 :func:`_expression.values` function.

3349

3350 .. versionadded:: 1.4

3351

3352 """

3353

3354 __visit_name__ = "values"

3355

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

3357

3358 _unnamed: bool

3359 _traverse_internals: _TraverseInternalsType = [

3360 ("_column_args", InternalTraversal.dp_clauseelement_list),

3361 ("_data", InternalTraversal.dp_dml_multi_values),

3362 ("name", InternalTraversal.dp_string),

3363 ("literal_binds", InternalTraversal.dp_boolean),

3364 ] + HasCTE._has_ctes_traverse_internals

3365

3366 name_cte_columns = True

3367

3368 def __init__(

3369 self,

3370 *columns: ColumnClause[Any],

3371 name: Optional[str] = None,

3372 literal_binds: bool = False,

3373 ):

3374 super().__init__()

3375 self._column_args = columns

3376

3377 if name is None:

3378 self._unnamed = True

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

3380 else:

3381 self._unnamed = False

3382 self.name = name

3383 self.literal_binds = literal_binds

3384 self.named_with_column = not self._unnamed

3385

3386 @property

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

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

3389

3390 @util.ro_non_memoized_property

3391 def _all_selected_columns(self) -> _SelectIterable:

3392 return self._column_args

3393

3394 @_generative

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

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

3397 construct that is a copy of this

3398 one with the given name.

3399

3400 This method is a VALUES-specific specialization of the

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

3402

3403 .. seealso::

3404

3405 :ref:`tutorial_using_aliases`

3406

3407 :func:`_expression.alias`

3408

3409 """

3410 non_none_name: str

3411

3412 if name is None:

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

3414 else:

3415 non_none_name = name

3416

3417 self.name = non_none_name

3418 self.named_with_column = True

3419 self._unnamed = False

3420 return self

3421

3422 @_generative

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

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

3425 so that

3426 it renders as LATERAL.

3427

3428 .. seealso::

3429

3430 :func:`_expression.lateral`

3431

3432 """

3433 non_none_name: str

3434

3435 if name is None:

3436 non_none_name = self.name

3437 else:

3438 non_none_name = name

3439

3440 self._is_lateral = True

3441 self.name = non_none_name

3442 self._unnamed = False

3443 return self

3444

3445 @_generative

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

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

3448 adding the given data to the data list.

3449

3450 E.g.::

3451

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

3453

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

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

3456 constructor.

3457

3458 """

3459

3460 self._data += (values,)

3461 return self

3462

3463 def scalar_values(self) -> ScalarValues:

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

3465 COLUMN element in a statement.

3466

3467 .. versionadded:: 2.0.0b4

3468

3469 """

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

3471

3472 def _populate_column_collection(

3473 self,

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

3475 primary_key: ColumnSet,

3476 foreign_keys: Set[KeyedColumnElement[Any]],

3477 ) -> None:

3478 for c in self._column_args:

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

3480 _, c = c._make_proxy(

3481 self, primary_key=primary_key, foreign_keys=foreign_keys

3482 )

3483 else:

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

3485 # no memoizations of other FROM clauses.

3486 # see test_values.py -> test_auto_proxy_select_direct_col

3487 c._reset_memoizations()

3488 columns.add(c)

3489 c.table = self

3490

3491 @util.ro_non_memoized_property

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

3493 return [self]

3494

3495

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

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

3498 COLUMN element in a statement.

3499

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

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

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

3503 an ``IN`` or ``NOT IN`` condition.

3504

3505 .. versionadded:: 2.0.0b4

3506

3507 """

3508

3509 __visit_name__ = "scalar_values"

3510

3511 _traverse_internals: _TraverseInternalsType = [

3512 ("_column_args", InternalTraversal.dp_clauseelement_list),

3513 ("_data", InternalTraversal.dp_dml_multi_values),

3514 ("literal_binds", InternalTraversal.dp_boolean),

3515 ]

3516

3517 def __init__(

3518 self,

3519 columns: Sequence[ColumnClause[Any]],

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

3521 literal_binds: bool,

3522 ):

3523 super().__init__()

3524 self._column_args = columns

3525 self._data = data

3526 self.literal_binds = literal_binds

3527

3528 @property

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

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

3531

3532 def __clause_element__(self) -> ScalarValues:

3533 return self

3534

3535 if TYPE_CHECKING:

3536

3537 def self_group(

3538 self, against: Optional[OperatorType] = None

3539 ) -> Self: ...

3540

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

3542

3543

3544class SelectBase(

3545 roles.SelectStatementRole,

3546 roles.DMLSelectRole,

3547 roles.CompoundElementRole,

3548 roles.InElementRole,

3549 HasCTE,

3550 SupportsCloneAnnotations,

3551 Selectable,

3552):

3553 """Base class for SELECT statements.

3554

3555

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

3557 :class:`_expression.CompoundSelect` and

3558 :class:`_expression.TextualSelect`.

3559

3560

3561 """

3562

3563 _is_select_base = True

3564 is_select = True

3565

3566 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3567

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

3569 self._reset_memoizations()

3570

3571 @util.ro_non_memoized_property

3572 def selected_columns(

3573 self,

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

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

3576 representing the columns that

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

3578

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

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

3581 within this collection cannot be directly nested inside another SELECT

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

3583 necessary parenthesization required by SQL.

3584

3585 .. note::

3586

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

3588 include expressions established in the columns clause using the

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

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

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

3592 construct.

3593

3594 .. seealso::

3595

3596 :attr:`_sql.Select.selected_columns`

3597

3598 .. versionadded:: 1.4

3599

3600 """

3601 raise NotImplementedError()

3602

3603 def _generate_fromclause_column_proxies(

3604 self,

3605 subquery: FromClause,

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

3607 primary_key: ColumnSet,

3608 foreign_keys: Set[KeyedColumnElement[Any]],

3609 *,

3610 proxy_compound_columns: Optional[

3611 Iterable[Sequence[ColumnElement[Any]]]

3612 ] = None,

3613 ) -> None:

3614 raise NotImplementedError()

3615

3616 @util.ro_non_memoized_property

3617 def _all_selected_columns(self) -> _SelectIterable:

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

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

3620 constructs.

3621

3622 .. versionadded:: 1.4.12

3623

3624 .. seealso::

3625

3626 :attr:`_sql.SelectBase.exported_columns`

3627

3628 """

3629 raise NotImplementedError()

3630

3631 @property

3632 def exported_columns(

3633 self,

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

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

3636 that represents the "exported"

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

3638 :class:`_sql.TextClause` constructs.

3639

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

3641 object are synonymous

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

3643

3644 .. versionadded:: 1.4

3645

3646 .. seealso::

3647

3648 :attr:`_expression.Select.exported_columns`

3649

3650 :attr:`_expression.Selectable.exported_columns`

3651

3652 :attr:`_expression.FromClause.exported_columns`

3653

3654

3655 """

3656 return self.selected_columns.as_readonly()

3657

3658 def get_label_style(self) -> SelectLabelStyle:

3659 """

3660 Retrieve the current label style.

3661

3662 Implemented by subclasses.

3663

3664 """

3665 raise NotImplementedError()

3666

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

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

3669

3670 Implemented by subclasses.

3671

3672 """

3673

3674 raise NotImplementedError()

3675

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

3677 raise NotImplementedError()

3678

3679 @util.deprecated(

3680 "1.4",

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

3682 "method is deprecated and will be "

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

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

3685 )

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

3687 return self.scalar_subquery()

3688

3689 def exists(self) -> Exists:

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

3691 which can be used as a column expression.

3692

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

3694

3695 .. seealso::

3696

3697 :func:`_sql.exists`

3698

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

3700

3701 .. versionadded:: 1.4

3702

3703 """

3704 return Exists(self)

3705

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

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

3708 used as a column expression.

3709

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

3711

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

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

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

3715 an enclosing SELECT.

3716

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

3718 subquery that can be produced using the

3719 :meth:`_expression.SelectBase.subquery`

3720 method.

3721

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

3723 :meth:`_expression.SelectBase.scalar_subquery`.

3724

3725 .. seealso::

3726

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

3728

3729 """

3730 if self._label_style is not LABEL_STYLE_NONE:

3731 self = self.set_label_style(LABEL_STYLE_NONE)

3732

3733 return ScalarSelect(self)

3734

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

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

3737 subquery with a label.

3738

3739 .. seealso::

3740

3741 :meth:`_expression.SelectBase.scalar_subquery`.

3742

3743 """

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

3745

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

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

3748

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

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

3751

3752 .. seealso::

3753

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

3755

3756 """

3757 return Lateral._factory(self, name)

3758

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

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

3761

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

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

3764 SELECT statement.

3765

3766 Given a SELECT statement such as::

3767

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

3769

3770 The above statement might look like:

3771

3772 .. sourcecode:: sql

3773

3774 SELECT table.id, table.name FROM table

3775

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

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

3778 a named sub-element::

3779

3780 subq = stmt.subquery()

3781 new_stmt = select(subq)

3782

3783 The above renders as:

3784

3785 .. sourcecode:: sql

3786

3787 SELECT anon_1.id, anon_1.name

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

3789

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

3791 is equivalent to calling

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

3793 method on a FROM object; however,

3794 as a :class:`_expression.SelectBase`

3795 object is not directly FROM object,

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

3797 method provides clearer semantics.

3798

3799 .. versionadded:: 1.4

3800

3801 """

3802

3803 return Subquery._construct(

3804 self._ensure_disambiguated_names(), name=name

3805 )

3806

3807 @util.preload_module("sqlalchemy.sql.ddl")

3808 def into(

3809 self,

3810 target: str,

3811 *,

3812 metadata: Optional["MetaData"] = None,

3813 schema: Optional[str] = None,

3814 temporary: bool = False,

3815 if_not_exists: bool = False,

3816 ) -> CreateTableAs:

3817 """Create a :class:`_schema.CreateTableAs` construct from this SELECT.

3818

3819 This method provides a convenient way to create a ``CREATE TABLE ...

3820 AS`` statement from a SELECT, as well as compound SELECTs like UNION.

3821 The new table will be created with columns matching the SELECT list.

3822

3823 Supported on all included backends, the construct emits

3824 ``CREATE TABLE...AS`` for all backends except SQL Server, which instead

3825 emits a ``SELECT..INTO`` statement.

3826

3827 e.g.::

3828

3829 from sqlalchemy import select

3830

3831 # Create a new table from a SELECT

3832 stmt = (

3833 select(users.c.id, users.c.name)

3834 .where(users.c.status == "active")

3835 .into("active_users")

3836 )

3837

3838 with engine.begin() as conn:

3839 conn.execute(stmt)

3840

3841 # With optional flags

3842 stmt = (

3843 select(users.c.id)

3844 .where(users.c.status == "inactive")

3845 .into("inactive_users", schema="analytics", if_not_exists=True)

3846 )

3847

3848 .. versionadded:: 2.1

3849

3850 :param target: Name of the table to create as a string. Must be

3851 unqualified; use the ``schema`` parameter for qualification.

3852

3853 :param metadata: :class:`_schema.MetaData`, optional

3854 If provided, the :class:`_schema.Table` object available via the

3855 :attr:`.CreateTableAs.table` attribute will be associated with this

3856 :class:`.MetaData`. Otherwise, a new, empty :class:`.MetaData`

3857 is created.

3858

3859 :param schema: Optional schema name for the new table.

3860

3861 :param temporary: If True, create a temporary table where supported

3862

3863 :param if_not_exists: If True, add IF NOT EXISTS clause where supported

3864

3865 :return: A :class:`_schema.CreateTableAs` construct.

3866

3867 .. seealso::

3868

3869 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3870

3871 :class:`_schema.CreateTableAs`

3872

3873 """

3874 sql_ddl = util.preloaded.sql_ddl

3875

3876 return sql_ddl.CreateTableAs(

3877 self,

3878 target,

3879 metadata=metadata,

3880 schema=schema,

3881 temporary=temporary,

3882 if_not_exists=if_not_exists,

3883 )

3884

3885 def _ensure_disambiguated_names(self) -> Self:

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

3887 disambiguated in some way, if possible.

3888

3889 """

3890

3891 raise NotImplementedError()

3892

3893 def alias(

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

3895 ) -> Subquery:

3896 """Return a named subquery against this

3897 :class:`_expression.SelectBase`.

3898

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

3900 :class:`_expression.FromClause`),

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

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

3903 :class:`_expression.FromClause`.

3904

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

3906 method is now

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

3908

3909 """

3910 return self.subquery(name=name)

3911

3912

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

3914

3915

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

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

3918

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

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

3921 compound selects.

3922

3923 """

3924

3925 __visit_name__ = "select_statement_grouping"

3926 _traverse_internals: _TraverseInternalsType = [

3927 ("element", InternalTraversal.dp_clauseelement)

3928 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3929

3930 _is_select_container = True

3931

3932 element: _SB

3933

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

3935 self.element = cast(

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

3937 )

3938

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

3940 new_element = self.element._ensure_disambiguated_names()

3941 if new_element is not self.element:

3942 return SelectStatementGrouping(new_element)

3943 else:

3944 return self

3945

3946 def get_label_style(self) -> SelectLabelStyle:

3947 return self.element.get_label_style()

3948

3949 def set_label_style(

3950 self, label_style: SelectLabelStyle

3951 ) -> SelectStatementGrouping[_SB]:

3952 return SelectStatementGrouping(

3953 self.element.set_label_style(label_style)

3954 )

3955

3956 @property

3957 def select_statement(self) -> _SB:

3958 return self.element

3959

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

3961 return self

3962

3963 if TYPE_CHECKING:

3964

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

3966

3967 # def _generate_columns_plus_names(

3968 # self, anon_for_dupe_key: bool

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

3970 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3971

3972 def _generate_fromclause_column_proxies(

3973 self,

3974 subquery: FromClause,

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

3976 primary_key: ColumnSet,

3977 foreign_keys: Set[KeyedColumnElement[Any]],

3978 *,

3979 proxy_compound_columns: Optional[

3980 Iterable[Sequence[ColumnElement[Any]]]

3981 ] = None,

3982 ) -> None:

3983 self.element._generate_fromclause_column_proxies(

3984 subquery,

3985 columns,

3986 proxy_compound_columns=proxy_compound_columns,

3987 primary_key=primary_key,

3988 foreign_keys=foreign_keys,

3989 )

3990

3991 @util.ro_non_memoized_property

3992 def _all_selected_columns(self) -> _SelectIterable:

3993 return self.element._all_selected_columns

3994

3995 @util.ro_non_memoized_property

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

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

3998 representing the columns that

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

4000 :class:`_sql.TextClause` constructs.

4001

4002 .. versionadded:: 1.4

4003

4004 .. seealso::

4005

4006 :attr:`_sql.Select.selected_columns`

4007

4008 """

4009 return self.element.selected_columns

4010

4011 @util.ro_non_memoized_property

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

4013 return self.element._from_objects

4014

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

4016 return self.element._scalar_type()

4017

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

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

4020 raise NotImplementedError

4021

4022

4023class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4025 added.

4026

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

4028 :class:`_expression.CompoundSelect`

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

4030 rendering can be controlled. Compare to

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

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

4033 and is also a SELECT construct,

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

4035 only wrapped as a subquery.

4036

4037 """

4038

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

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

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

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

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

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

4045 _for_update_arg: Optional[ForUpdateArg] = None

4046

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

4048 self._label_style = _label_style

4049

4050 @_generative

4051 def with_for_update(

4052 self,

4053 *,

4054 nowait: bool = False,

4055 read: bool = False,

4056 of: Optional[_ForUpdateOfArgument] = None,

4057 skip_locked: bool = False,

4058 key_share: bool = False,

4059 ) -> Self:

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

4061 :class:`_expression.GenerativeSelect`.

4062

4063 E.g.::

4064

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

4066

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

4068 render a statement like:

4069

4070 .. sourcecode:: sql

4071

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

4073

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

4075 would produce:

4076

4077 .. sourcecode:: sql

4078

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

4080

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

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

4083 provided which allow for common database-specific

4084 variants.

4085

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

4087 Database and PostgreSQL dialects.

4088

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

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

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

4092

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

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

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

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

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

4098 table or as a column depending on backend.

4099

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

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

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

4103

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

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

4106 on the PostgreSQL dialect.

4107

4108 """

4109 self._for_update_arg = ForUpdateArg(

4110 nowait=nowait,

4111 read=read,

4112 of=of,

4113 skip_locked=skip_locked,

4114 key_share=key_share,

4115 )

4116 return self

4117

4118 def get_label_style(self) -> SelectLabelStyle:

4119 """

4120 Retrieve the current label style.

4121

4122 .. versionadded:: 1.4

4123

4124 """

4125 return self._label_style

4126

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

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

4129

4130 There are three "label styles" available,

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

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

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

4134 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4135

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

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

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

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

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

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

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

4143 that the impact of this labeling is minimal.

4144

4145 The rationale for disambiguation is mostly so that all column

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

4147 collection when a subquery is created.

4148

4149 .. versionadded:: 1.4 - the

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

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

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

4153

4154 .. seealso::

4155

4156 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4157

4158 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4159

4160 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4161

4162 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4163

4164 """

4165 if self._label_style is not style:

4166 self = self._generate()

4167 self._label_style = style

4168 return self

4169

4170 @property

4171 def _group_by_clause(self) -> ClauseList:

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

4173 return ClauseList._construct_raw(

4174 operators.comma_op, self._group_by_clauses

4175 )

4176

4177 @property

4178 def _order_by_clause(self) -> ClauseList:

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

4180 return ClauseList._construct_raw(

4181 operators.comma_op, self._order_by_clauses

4182 )

4183

4184 def _offset_or_limit_clause(

4185 self,

4186 element: _LimitOffsetType,

4187 name: Optional[str] = None,

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

4189 ) -> ColumnElement[Any]:

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

4191

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

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

4194

4195 """

4196 return coercions.expect(

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

4198 )

4199

4200 @overload

4201 def _offset_or_limit_clause_asint(

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

4203 ) -> NoReturn: ...

4204

4205 @overload

4206 def _offset_or_limit_clause_asint(

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

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

4209

4210 def _offset_or_limit_clause_asint(

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

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

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

4214 integer.

4215

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

4217 parameter. Otherwise, a compilation error is raised.

4218

4219 """

4220 if clause is None:

4221 return None

4222 try:

4223 value = clause._limit_offset_value

4224 except AttributeError as err:

4225 raise exc.CompileError(

4226 "This SELECT structure does not use a simple "

4227 "integer value for %s" % attrname

4228 ) from err

4229 else:

4230 return util.asint(value)

4231

4232 @property

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

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

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

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

4237 isn't currently set to an integer.

4238

4239 """

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

4241

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

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

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

4245 """

4246 return isinstance(clause, _OffsetLimitParam)

4247

4248 @property

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

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

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

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

4253 offset isn't currently set to an integer.

4254

4255 """

4256 return self._offset_or_limit_clause_asint(

4257 self._offset_clause, "offset"

4258 )

4259

4260 @property

4261 def _has_row_limiting_clause(self) -> bool:

4262 return (

4263 self._limit_clause is not None

4264 or self._offset_clause is not None

4265 or self._fetch_clause is not None

4266 )

4267

4268 @_generative

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

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

4271 applied.

4272

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

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

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

4276 functionality.

4277

4278 .. note::

4279

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

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

4282

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

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

4285

4286 .. seealso::

4287

4288 :meth:`_sql.GenerativeSelect.fetch`

4289

4290 :meth:`_sql.GenerativeSelect.offset`

4291

4292 """

4293

4294 self._fetch_clause = self._fetch_clause_options = None

4295 self._limit_clause = self._offset_or_limit_clause(limit)

4296 return self

4297

4298 @_generative

4299 def fetch(

4300 self,

4301 count: _LimitOffsetType,

4302 with_ties: bool = False,

4303 percent: bool = False,

4304 **dialect_kw: Any,

4305 ) -> Self:

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

4307 applied.

4308

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

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

4311 select. This functionality is is currently implemented for Oracle

4312 Database, PostgreSQL, MSSQL.

4313

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

4315

4316 .. note::

4317

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

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

4320

4321 .. versionadded:: 1.4

4322

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

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

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

4326 Pass ``None`` to reset it.

4327

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

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

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

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

4332

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

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

4335

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

4337 may be accepted by dialects.

4338

4339 .. versionadded:: 2.0.41

4340

4341 .. seealso::

4342

4343 :meth:`_sql.GenerativeSelect.limit`

4344

4345 :meth:`_sql.GenerativeSelect.offset`

4346

4347 """

4348 self._validate_dialect_kwargs(dialect_kw)

4349 self._limit_clause = None

4350 if count is None:

4351 self._fetch_clause = self._fetch_clause_options = None

4352 else:

4353 self._fetch_clause = self._offset_or_limit_clause(count)

4354 self._fetch_clause_options = {

4355 "with_ties": with_ties,

4356 "percent": percent,

4357 }

4358 return self

4359

4360 @_generative

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

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

4363 applied.

4364

4365

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

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

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

4369 functionality.

4370

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

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

4373

4374 .. seealso::

4375

4376 :meth:`_sql.GenerativeSelect.limit`

4377

4378 :meth:`_sql.GenerativeSelect.fetch`

4379

4380 """

4381

4382 self._offset_clause = self._offset_or_limit_clause(offset)

4383 return self

4384

4385 @_generative

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

4387 def slice(

4388 self,

4389 start: int,

4390 stop: int,

4391 ) -> Self:

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

4393

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

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

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

4397 query.

4398

4399 For example, ::

4400

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

4402

4403 renders as

4404

4405 .. sourcecode:: sql

4406

4407 SELECT users.id AS users_id,

4408 users.name AS users_name

4409 FROM users ORDER BY users.id

4410 LIMIT ? OFFSET ?

4411 (2, 1)

4412

4413 .. note::

4414

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

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

4417

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

4419 method generalized from the ORM.

4420

4421 .. seealso::

4422

4423 :meth:`_sql.GenerativeSelect.limit`

4424

4425 :meth:`_sql.GenerativeSelect.offset`

4426

4427 :meth:`_sql.GenerativeSelect.fetch`

4428

4429 """

4430 sql_util = util.preloaded.sql_util

4431 self._fetch_clause = self._fetch_clause_options = None

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

4433 self._limit_clause, self._offset_clause, start, stop

4434 )

4435 return self

4436

4437 @_generative

4438 def order_by(

4439 self,

4440 __first: Union[

4441 Literal[None, _NoArg.NO_ARG],

4442 _ColumnExpressionOrStrLabelArgument[Any],

4443 ] = _NoArg.NO_ARG,

4444 /,

4445 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4446 ) -> Self:

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

4448 criteria applied.

4449

4450 e.g.::

4451

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

4453

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

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

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

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

4458

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

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

4461

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

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

4464

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

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

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

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

4469 label matching in ORDER BY and GROUP BY expressions.

4470

4471 .. seealso::

4472

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

4474

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

4476

4477 """

4478

4479 if not clauses and __first is None:

4480 self._order_by_clauses = ()

4481 elif __first is not _NoArg.NO_ARG:

4482 self._order_by_clauses += tuple(

4483 coercions.expect(

4484 roles.OrderByRole, clause, apply_propagate_attrs=self

4485 )

4486 for clause in (__first,) + clauses

4487 )

4488 return self

4489

4490 @_generative

4491 def group_by(

4492 self,

4493 __first: Union[

4494 Literal[None, _NoArg.NO_ARG],

4495 _ColumnExpressionOrStrLabelArgument[Any],

4496 ] = _NoArg.NO_ARG,

4497 /,

4498 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4499 ) -> Self:

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

4501 criterion applied.

4502

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

4504

4505 e.g.::

4506

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

4508

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

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

4511

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

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

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

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

4516 label matching in ORDER BY and GROUP BY expressions.

4517

4518 .. seealso::

4519

4520 :ref:`tutorial_group_by_w_aggregates` - in the

4521 :ref:`unified_tutorial`

4522

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

4524

4525 """ # noqa: E501

4526

4527 if not clauses and __first is None:

4528 self._group_by_clauses = ()

4529 elif __first is not _NoArg.NO_ARG:

4530 self._group_by_clauses += tuple(

4531 coercions.expect(

4532 roles.GroupByRole, clause, apply_propagate_attrs=self

4533 )

4534 for clause in (__first,) + clauses

4535 )

4536 return self

4537

4538

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

4540class CompoundSelectState(CompileState):

4541 @util.memoized_property

4542 def _label_resolve_dict(

4543 self,

4544 ) -> Tuple[

4545 Dict[str, ColumnElement[Any]],

4546 Dict[str, ColumnElement[Any]],

4547 Dict[str, ColumnElement[Any]],

4548 ]:

4549 # TODO: this is hacky and slow

4550 hacky_subquery = self.statement.subquery()

4551 hacky_subquery.named_with_column = False

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

4553 return d, d, d

4554

4555

4556class _CompoundSelectKeyword(Enum):

4557 UNION = "UNION"

4558 UNION_ALL = "UNION ALL"

4559 EXCEPT = "EXCEPT"

4560 EXCEPT_ALL = "EXCEPT ALL"

4561 INTERSECT = "INTERSECT"

4562 INTERSECT_ALL = "INTERSECT ALL"

4563

4564

4565class CompoundSelect(

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

4567):

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

4569 SELECT-based set operations.

4570

4571

4572 .. seealso::

4573

4574 :func:`_expression.union`

4575

4576 :func:`_expression.union_all`

4577

4578 :func:`_expression.intersect`

4579

4580 :func:`_expression.intersect_all`

4581

4582 :func:`_expression.except`

4583

4584 :func:`_expression.except_all`

4585

4586 """

4587

4588 __visit_name__ = "compound_select"

4589

4590 _traverse_internals: _TraverseInternalsType = (

4591 [

4592 ("selects", InternalTraversal.dp_clauseelement_list),

4593 ("_limit_clause", InternalTraversal.dp_clauseelement),

4594 ("_offset_clause", InternalTraversal.dp_clauseelement),

4595 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4596 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4597 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4598 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4599 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4600 ("keyword", InternalTraversal.dp_string),

4601 ]

4602 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4603 + HasCTE._has_ctes_traverse_internals

4604 + DialectKWArgs._dialect_kwargs_traverse_internals

4605 + ExecutableStatement._executable_traverse_internals

4606 )

4607

4608 selects: List[SelectBase]

4609

4610 _is_from_container = True

4611 _auto_correlate = False

4612

4613 def __init__(

4614 self,

4615 keyword: _CompoundSelectKeyword,

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

4617 ):

4618 self.keyword = keyword

4619 self.selects = [

4620 coercions.expect(

4621 roles.CompoundElementRole, s, apply_propagate_attrs=self

4622 ).self_group(against=self)

4623 for s in selects

4624 ]

4625

4626 GenerativeSelect.__init__(self)

4627

4628 @classmethod

4629 def _create_union(

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

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

4632 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4633

4634 @classmethod

4635 def _create_union_all(

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

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

4638 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4639

4640 @classmethod

4641 def _create_except(

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

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

4644 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4645

4646 @classmethod

4647 def _create_except_all(

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

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

4650 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4651

4652 @classmethod

4653 def _create_intersect(

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

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

4656 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4657

4658 @classmethod

4659 def _create_intersect_all(

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

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

4662 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4663

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

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

4666

4667 def self_group(

4668 self, against: Optional[OperatorType] = None

4669 ) -> GroupedElement:

4670 return SelectStatementGrouping(self)

4671

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

4673 for s in self.selects:

4674 if s.is_derived_from(fromclause):

4675 return True

4676 return False

4677

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

4679 if self._label_style is not style:

4680 self = self._generate()

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

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

4683

4684 return self

4685

4686 def _ensure_disambiguated_names(self) -> Self:

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

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

4689 self = self._generate()

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

4691

4692 return self

4693

4694 def _generate_fromclause_column_proxies(

4695 self,

4696 subquery: FromClause,

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

4698 primary_key: ColumnSet,

4699 foreign_keys: Set[KeyedColumnElement[Any]],

4700 *,

4701 proxy_compound_columns: Optional[

4702 Iterable[Sequence[ColumnElement[Any]]]

4703 ] = None,

4704 ) -> None:

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

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

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

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

4709 # generate below.

4710 select_0 = self.selects[0]

4711

4712 if self._label_style is not LABEL_STYLE_DEFAULT:

4713 select_0 = select_0.set_label_style(self._label_style)

4714

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

4716 # derived columns place a 'weight' annotation corresponding

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

4718 # that the corresponding_column() operation can resolve

4719 # conflicts

4720 extra_col_iterator = zip(

4721 *[

4722 [

4723 c._annotate(dd)

4724 for c in stmt._all_selected_columns

4725 if is_column_element(c)

4726 ]

4727 for dd, stmt in [

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

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

4730 ]

4731 ]

4732 )

4733

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

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

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

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

4738 # compound

4739 select_0._generate_fromclause_column_proxies(

4740 subquery,

4741 columns,

4742 proxy_compound_columns=extra_col_iterator,

4743 primary_key=primary_key,

4744 foreign_keys=foreign_keys,

4745 )

4746

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

4748 super()._refresh_for_new_column(column)

4749 for select in self.selects:

4750 select._refresh_for_new_column(column)

4751

4752 @util.ro_non_memoized_property

4753 def _all_selected_columns(self) -> _SelectIterable:

4754 return self.selects[0]._all_selected_columns

4755

4756 @util.ro_non_memoized_property

4757 def selected_columns(

4758 self,

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

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

4761 representing the columns that

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

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

4764

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

4766 :attr:`_expression.CompoundSelect.selected_columns`

4767 attribute returns the selected

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

4769 statements within the set operation.

4770

4771 .. seealso::

4772

4773 :attr:`_sql.Select.selected_columns`

4774

4775 .. versionadded:: 1.4

4776

4777 """

4778 return self.selects[0].selected_columns

4779

4780

4781# backwards compat

4782for elem in _CompoundSelectKeyword:

4783 setattr(CompoundSelect, elem.name, elem)

4784

4785

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

4787class SelectState(util.MemoizedSlots, CompileState):

4788 __slots__ = (

4789 "from_clauses",

4790 "froms",

4791 "columns_plus_names",

4792 "_label_resolve_dict",

4793 )

4794

4795 if TYPE_CHECKING:

4796 default_select_compile_options: CacheableOptions

4797 else:

4798

4799 class default_select_compile_options(CacheableOptions):

4800 _cache_key_traversal = []

4801

4802 if TYPE_CHECKING:

4803

4804 @classmethod

4805 def get_plugin_class(

4806 cls, statement: Executable

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

4808

4809 def __init__(

4810 self,

4811 statement: Select[Unpack[TupleAny]],

4812 compiler: SQLCompiler,

4813 **kw: Any,

4814 ):

4815 self.statement = statement

4816 self.from_clauses = statement._from_obj

4817

4818 for memoized_entities in statement._memoized_select_entities:

4819 self._setup_joins(

4820 memoized_entities._setup_joins, memoized_entities._raw_columns

4821 )

4822

4823 if statement._setup_joins:

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

4825

4826 self.froms = self._get_froms(statement)

4827

4828 self.columns_plus_names = statement._generate_columns_plus_names(True)

4829

4830 @classmethod

4831 def _plugin_not_implemented(cls) -> NoReturn:

4832 raise NotImplementedError(

4833 "The default SELECT construct without plugins does not "

4834 "implement this method."

4835 )

4836

4837 @classmethod

4838 def get_column_descriptions(

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

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

4841 return [

4842 {

4843 "name": name,

4844 "type": element.type,

4845 "expr": element,

4846 }

4847 for _, name, _, element, _ in (

4848 statement._generate_columns_plus_names(False)

4849 )

4850 ]

4851

4852 @classmethod

4853 def from_statement(

4854 cls,

4855 statement: Select[Unpack[TupleAny]],

4856 from_statement: roles.ReturnsRowsRole,

4857 ) -> ExecutableReturnsRows:

4858 cls._plugin_not_implemented()

4859

4860 @classmethod

4861 def get_columns_clause_froms(

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

4863 ) -> List[FromClause]:

4864 return cls._normalize_froms(

4865 itertools.chain.from_iterable(

4866 element._from_objects for element in statement._raw_columns

4867 )

4868 )

4869

4870 @classmethod

4871 def _column_naming_convention(

4872 cls, label_style: SelectLabelStyle

4873 ) -> _LabelConventionCallable:

4874 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4875

4876 dedupe = label_style is not LABEL_STYLE_NONE

4877

4878 pa = prefix_anon_map()

4879 names = set()

4880

4881 def go(

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

4883 col_name: Optional[str] = None,

4884 ) -> Optional[str]:

4885 if is_text_clause(c):

4886 return None

4887 elif TYPE_CHECKING:

4888 assert is_column_element(c)

4889

4890 if not dedupe:

4891 name = c._proxy_key

4892 if name is None:

4893 name = "_no_label"

4894 return name

4895

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

4897

4898 if name is None:

4899 name = "_no_label"

4900 if name in names:

4901 return c._anon_label(name) % pa

4902 else:

4903 names.add(name)

4904 return name

4905

4906 elif name in names:

4907 return (

4908 c._anon_tq_key_label % pa

4909 if table_qualified

4910 else c._anon_key_label % pa

4911 )

4912 else:

4913 names.add(name)

4914 return name

4915

4916 return go

4917

4918 def _get_froms(

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

4920 ) -> List[FromClause]:

4921 ambiguous_table_name_map: _AmbiguousTableNameMap

4922 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4923

4924 return self._normalize_froms(

4925 itertools.chain(

4926 self.from_clauses,

4927 itertools.chain.from_iterable(

4928 [

4929 element._from_objects

4930 for element in statement._raw_columns

4931 ]

4932 ),

4933 itertools.chain.from_iterable(

4934 [

4935 element._from_objects

4936 for element in statement._where_criteria

4937 ]

4938 ),

4939 ),

4940 check_statement=statement,

4941 ambiguous_table_name_map=ambiguous_table_name_map,

4942 )

4943

4944 @classmethod

4945 def _normalize_froms(

4946 cls,

4947 iterable_of_froms: Iterable[FromClause],

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

4949 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4950 ) -> List[FromClause]:

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

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

4953

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

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

4956 etc.

4957

4958 """

4959 seen: Set[FromClause] = set()

4960 froms: List[FromClause] = []

4961

4962 for item in iterable_of_froms:

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

4964 raise exc.InvalidRequestError(

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

4966 )

4967

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

4969 froms.append(item)

4970 seen.update(item._cloned_set)

4971

4972 if froms:

4973 toremove = set(

4974 itertools.chain.from_iterable(

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

4976 )

4977 )

4978 if toremove:

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

4980 # using a list to maintain ordering

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

4982

4983 if ambiguous_table_name_map is not None:

4984 ambiguous_table_name_map.update(

4985 (

4986 fr.name,

4987 _anonymous_label.safe_construct(

4988 hash(fr.name), fr.name

4989 ),

4990 )

4991 for item in froms

4992 for fr in item._from_objects

4993 if is_table(fr)

4994 and fr.schema

4995 and fr.name not in ambiguous_table_name_map

4996 )

4997

4998 return froms

4999

5000 def _get_display_froms(

5001 self,

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

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

5004 ) -> List[FromClause]:

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

5006

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

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

5009 may want to leave those absent if it is automatically

5010 correlating.

5011

5012 """

5013

5014 froms = self.froms

5015

5016 if self.statement._correlate:

5017 to_correlate = self.statement._correlate

5018 if to_correlate:

5019 froms = [

5020 f

5021 for f in froms

5022 if f

5023 not in _cloned_intersection(

5024 _cloned_intersection(

5025 froms, explicit_correlate_froms or ()

5026 ),

5027 to_correlate,

5028 )

5029 ]

5030

5031 if self.statement._correlate_except is not None:

5032 froms = [

5033 f

5034 for f in froms

5035 if f

5036 not in _cloned_difference(

5037 _cloned_intersection(

5038 froms, explicit_correlate_froms or ()

5039 ),

5040 self.statement._correlate_except,

5041 )

5042 ]

5043

5044 if (

5045 self.statement._auto_correlate

5046 and implicit_correlate_froms

5047 and len(froms) > 1

5048 ):

5049 froms = [

5050 f

5051 for f in froms

5052 if f

5053 not in _cloned_intersection(froms, implicit_correlate_froms)

5054 ]

5055

5056 if not len(froms):

5057 raise exc.InvalidRequestError(

5058 "Select statement '%r"

5059 "' returned no FROM clauses "

5060 "due to auto-correlation; "

5061 "specify correlate(<tables>) "

5062 "to control correlation "

5063 "manually." % self.statement

5064 )

5065

5066 return froms

5067

5068 def _memoized_attr__label_resolve_dict(

5069 self,

5070 ) -> Tuple[

5071 Dict[str, ColumnElement[Any]],

5072 Dict[str, ColumnElement[Any]],

5073 Dict[str, ColumnElement[Any]],

5074 ]:

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

5076 c._tq_label or c.key: c

5077 for c in self.statement._all_selected_columns

5078 if c._allow_label_resolve

5079 }

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

5081 c.key: c # type: ignore

5082 for c in _select_iterables(self.froms)

5083 if c._allow_label_resolve

5084 }

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

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

5087 with_cols.setdefault(key, value)

5088

5089 return with_cols, only_froms, only_cols

5090

5091 @classmethod

5092 def determine_last_joined_entity(

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

5094 ) -> Optional[_JoinTargetElement]:

5095 if stmt._setup_joins:

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

5097 else:

5098 return None

5099

5100 @classmethod

5101 def all_selected_columns(

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

5103 ) -> _SelectIterable:

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

5105

5106 def _setup_joins(

5107 self,

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

5109 raw_columns: List[_ColumnsClauseElement],

5110 ) -> None:

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

5112 if TYPE_CHECKING:

5113 if onclause is not None:

5114 assert isinstance(onclause, ColumnElement)

5115

5116 explicit_left = left

5117 isouter = flags["isouter"]

5118 full = flags["full"]

5119

5120 if left is None:

5121 (

5122 left,

5123 replace_from_obj_index,

5124 ) = self._join_determine_implicit_left_side(

5125 raw_columns, left, right, onclause

5126 )

5127 else:

5128 (replace_from_obj_index) = self._join_place_explicit_left_side(

5129 left

5130 )

5131

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

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

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

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

5136 # used instead.

5137 if TYPE_CHECKING:

5138 assert isinstance(right, FromClause)

5139 if onclause is not None:

5140 assert isinstance(onclause, ColumnElement)

5141

5142 if replace_from_obj_index is not None:

5143 # splice into an existing element in the

5144 # self._from_obj list

5145 left_clause = self.from_clauses[replace_from_obj_index]

5146

5147 if explicit_left is not None and onclause is None:

5148 onclause = Join._join_condition(explicit_left, right)

5149

5150 self.from_clauses = (

5151 self.from_clauses[:replace_from_obj_index]

5152 + (

5153 Join(

5154 left_clause,

5155 right,

5156 onclause,

5157 isouter=isouter,

5158 full=full,

5159 ),

5160 )

5161 + self.from_clauses[replace_from_obj_index + 1 :]

5162 )

5163 else:

5164 assert left is not None

5165 self.from_clauses = self.from_clauses + (

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

5167 )

5168

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

5170 def _join_determine_implicit_left_side(

5171 self,

5172 raw_columns: List[_ColumnsClauseElement],

5173 left: Optional[FromClause],

5174 right: _JoinTargetElement,

5175 onclause: Optional[ColumnElement[Any]],

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

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

5178 determine if an existing FROM or entity in this query

5179 can serve as the left hand side.

5180

5181 """

5182

5183 sql_util = util.preloaded.sql_util

5184

5185 replace_from_obj_index: Optional[int] = None

5186

5187 from_clauses = self.from_clauses

5188

5189 if from_clauses:

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

5191 from_clauses, right, onclause

5192 )

5193

5194 if len(indexes) == 1:

5195 replace_from_obj_index = indexes[0]

5196 left = from_clauses[replace_from_obj_index]

5197 else:

5198 potential = {}

5199 statement = self.statement

5200

5201 for from_clause in itertools.chain(

5202 itertools.chain.from_iterable(

5203 [element._from_objects for element in raw_columns]

5204 ),

5205 itertools.chain.from_iterable(

5206 [

5207 element._from_objects

5208 for element in statement._where_criteria

5209 ]

5210 ),

5211 ):

5212 potential[from_clause] = ()

5213

5214 all_clauses = list(potential.keys())

5215 indexes = sql_util.find_left_clause_to_join_from(

5216 all_clauses, right, onclause

5217 )

5218

5219 if len(indexes) == 1:

5220 left = all_clauses[indexes[0]]

5221

5222 if len(indexes) > 1:

5223 raise exc.InvalidRequestError(

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

5225 "from, there are multiple FROMS which can "

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

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

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

5229 "help resolve the ambiguity."

5230 )

5231 elif not indexes:

5232 raise exc.InvalidRequestError(

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

5234 "Please use the .select_from() "

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

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

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

5238 )

5239 return left, replace_from_obj_index

5240

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

5242 def _join_place_explicit_left_side(

5243 self, left: FromClause

5244 ) -> Optional[int]:

5245 replace_from_obj_index: Optional[int] = None

5246

5247 sql_util = util.preloaded.sql_util

5248

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

5250

5251 if from_clauses:

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

5253 self.from_clauses, left

5254 )

5255 else:

5256 indexes = []

5257

5258 if len(indexes) > 1:

5259 raise exc.InvalidRequestError(

5260 "Can't identify which entity in which to assign the "

5261 "left side of this join. Please use a more specific "

5262 "ON clause."

5263 )

5264

5265 # have an index, means the left side is already present in

5266 # an existing FROM in the self._from_obj tuple

5267 if indexes:

5268 replace_from_obj_index = indexes[0]

5269

5270 # no index, means we need to add a new element to the

5271 # self._from_obj tuple

5272

5273 return replace_from_obj_index

5274

5275

5276class _SelectFromElements:

5277 __slots__ = ()

5278

5279 _raw_columns: List[_ColumnsClauseElement]

5280 _where_criteria: Tuple[ColumnElement[Any], ...]

5281 _from_obj: Tuple[FromClause, ...]

5282

5283 def _iterate_from_elements(self) -> Iterator[FromClause]:

5284 # note this does not include elements

5285 # in _setup_joins

5286

5287 seen = set()

5288 for element in self._raw_columns:

5289 for fr in element._from_objects:

5290 if fr in seen:

5291 continue

5292 seen.add(fr)

5293 yield fr

5294 for element in self._where_criteria:

5295 for fr in element._from_objects:

5296 if fr in seen:

5297 continue

5298 seen.add(fr)

5299 yield fr

5300 for element in self._from_obj:

5301 if element in seen:

5302 continue

5303 seen.add(element)

5304 yield element

5305

5306

5307class _MemoizedSelectEntities(

5308 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5309):

5310 """represents partial state from a Select object, for the case

5311 where Select.columns() has redefined the set of columns/entities the

5312 statement will be SELECTing from. This object represents

5313 the entities from the SELECT before that transformation was applied,

5314 so that transformations that were made in terms of the SELECT at that

5315 time, such as join() as well as options(), can access the correct context.

5316

5317 In previous SQLAlchemy versions, this wasn't needed because these

5318 constructs calculated everything up front, like when you called join()

5319 or options(), it did everything to figure out how that would translate

5320 into specific SQL constructs that would be ready to send directly to the

5321 SQL compiler when needed. But as of

5322 1.4, all of that stuff is done in the compilation phase, during the

5323 "compile state" portion of the process, so that the work can all be

5324 cached. So it needs to be able to resolve joins/options2 based on what

5325 the list of entities was when those methods were called.

5326

5327

5328 """

5329

5330 __visit_name__ = "memoized_select_entities"

5331

5332 _traverse_internals: _TraverseInternalsType = [

5333 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5334 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5335 ("_with_options", InternalTraversal.dp_executable_options),

5336 ]

5337

5338 _is_clone_of: Optional[ClauseElement]

5339 _raw_columns: List[_ColumnsClauseElement]

5340 _setup_joins: Tuple[_SetupJoinsElement, ...]

5341 _with_options: Tuple[ExecutableOption, ...]

5342

5343 _annotations = util.EMPTY_DICT

5344

5345 def _clone(self, **kw: Any) -> Self:

5346 c = self.__class__.__new__(self.__class__)

5347 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5348

5349 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5350 return c

5351

5352 @classmethod

5353 def _generate_for_statement(

5354 cls, select_stmt: Select[Unpack[TupleAny]]

5355 ) -> None:

5356 if select_stmt._setup_joins or select_stmt._with_options:

5357 self = _MemoizedSelectEntities()

5358 self._raw_columns = select_stmt._raw_columns

5359 self._setup_joins = select_stmt._setup_joins

5360 self._with_options = select_stmt._with_options

5361

5362 select_stmt._memoized_select_entities += (self,)

5363 select_stmt._raw_columns = []

5364 select_stmt._setup_joins = select_stmt._with_options = ()

5365

5366

5367class Select(

5368 HasPrefixes,

5369 HasSuffixes,

5370 HasHints,

5371 HasCompileState,

5372 HasSyntaxExtensions[

5373 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5374 ],

5375 _SelectFromElements,

5376 GenerativeSelect,

5377 TypedReturnsRows[Unpack[_Ts]],

5378):

5379 """Represents a ``SELECT`` statement.

5380

5381 The :class:`_sql.Select` object is normally constructed using the

5382 :func:`_sql.select` function. See that function for details.

5383

5384 Available extension points:

5385

5386 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5387 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5388 keyword (if any) and the list of columns.

5389 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5390 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5391

5392 .. seealso::

5393

5394 :func:`_sql.select`

5395

5396 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5397

5398 """

5399

5400 __visit_name__ = "select"

5401

5402 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5403 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5404

5405 _raw_columns: List[_ColumnsClauseElement]

5406

5407 _distinct: bool = False

5408 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5409 _correlate: Tuple[FromClause, ...] = ()

5410 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5411 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5412 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5413 _from_obj: Tuple[FromClause, ...] = ()

5414

5415 _position_map = util.immutabledict(

5416 {

5417 "post_select": "_post_select_clause",

5418 "pre_columns": "_pre_columns_clause",

5419 "post_criteria": "_post_criteria_clause",

5420 "post_body": "_post_body_clause",

5421 }

5422 )

5423

5424 _post_select_clause: Optional[ClauseElement] = None

5425 """extension point for a ClauseElement that will be compiled directly

5426 after the SELECT keyword.

5427

5428 .. versionadded:: 2.1

5429

5430 """

5431

5432 _pre_columns_clause: Optional[ClauseElement] = None

5433 """extension point for a ClauseElement that will be compiled directly

5434 before the "columns" clause; after DISTINCT (if present).

5435

5436 .. versionadded:: 2.1

5437

5438 """

5439

5440 _post_criteria_clause: Optional[ClauseElement] = None

5441 """extension point for a ClauseElement that will be compiled directly

5442 after "criteria", following the HAVING clause but before ORDER BY.

5443

5444 .. versionadded:: 2.1

5445

5446 """

5447

5448 _post_body_clause: Optional[ClauseElement] = None

5449 """extension point for a ClauseElement that will be compiled directly

5450 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5451 of the SELECT.

5452

5453 .. versionadded:: 2.1

5454

5455 """

5456

5457 _auto_correlate = True

5458 _is_select_statement = True

5459 _compile_options: CacheableOptions = (

5460 SelectState.default_select_compile_options

5461 )

5462

5463 _traverse_internals: _TraverseInternalsType = (

5464 [

5465 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5466 (

5467 "_memoized_select_entities",

5468 InternalTraversal.dp_memoized_select_entities,

5469 ),

5470 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5471 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5472 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5473 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5474 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5475 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5476 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5477 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5478 ("_limit_clause", InternalTraversal.dp_clauseelement),

5479 ("_offset_clause", InternalTraversal.dp_clauseelement),

5480 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5481 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5482 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5483 ("_distinct", InternalTraversal.dp_boolean),

5484 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5485 ("_label_style", InternalTraversal.dp_plain_obj),

5486 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5487 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5488 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5489 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5490 ]

5491 + HasCTE._has_ctes_traverse_internals

5492 + HasPrefixes._has_prefixes_traverse_internals

5493 + HasSuffixes._has_suffixes_traverse_internals

5494 + HasHints._has_hints_traverse_internals

5495 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5496 + ExecutableStatement._executable_traverse_internals

5497 + DialectKWArgs._dialect_kwargs_traverse_internals

5498 )

5499

5500 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5501 ("_compile_options", InternalTraversal.dp_has_cache_key)

5502 ]

5503

5504 _compile_state_factory: Type[SelectState]

5505

5506 @classmethod

5507 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5508 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5509

5510 Used internally to build up :class:`.Select` constructs with

5511 pre-established state.

5512

5513 """

5514

5515 stmt = Select.__new__(Select)

5516 stmt.__dict__.update(kw)

5517 return stmt

5518

5519 def __init__(

5520 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5521 ):

5522 r"""Construct a new :class:`_expression.Select`.

5523

5524 The public constructor for :class:`_expression.Select` is the

5525 :func:`_sql.select` function.

5526

5527 """

5528 self._raw_columns = [

5529 coercions.expect(

5530 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5531 )

5532 for ent in entities

5533 ]

5534 GenerativeSelect.__init__(self)

5535

5536 def _apply_syntax_extension_to_self(

5537 self, extension: SyntaxExtension

5538 ) -> None:

5539 extension.apply_to_select(self)

5540

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

5542 if not self._raw_columns:

5543 return NULLTYPE

5544 elem = self._raw_columns[0]

5545 cols = list(elem._select_iterable)

5546 return cols[0].type

5547

5548 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5549 """A synonym for the :meth:`_sql.Select.where` method."""

5550

5551 return self.where(*criteria)

5552

5553 def _filter_by_zero(

5554 self,

5555 ) -> Union[

5556 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5557 ]:

5558 if self._setup_joins:

5559 meth = SelectState.get_plugin_class(

5560 self

5561 ).determine_last_joined_entity

5562 _last_joined_entity = meth(self)

5563 if _last_joined_entity is not None:

5564 return _last_joined_entity

5565

5566 if self._from_obj:

5567 return self._from_obj[0]

5568

5569 return self._raw_columns[0]

5570

5571 if TYPE_CHECKING:

5572

5573 @overload

5574 def scalar_subquery(

5575 self: Select[_MAYBE_ENTITY],

5576 ) -> ScalarSelect[Any]: ...

5577

5578 @overload

5579 def scalar_subquery(

5580 self: Select[_NOT_ENTITY],

5581 ) -> ScalarSelect[_NOT_ENTITY]: ...

5582

5583 @overload

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

5585

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

5587

5588 def filter_by(self, **kwargs: Any) -> Self:

5589 r"""apply the given filtering criterion as a WHERE clause

5590 to this select.

5591

5592 """

5593 from_entity = self._filter_by_zero()

5594

5595 clauses = [

5596 _entity_namespace_key(from_entity, key) == value

5597 for key, value in kwargs.items()

5598 ]

5599 return self.filter(*clauses)

5600

5601 @property

5602 def column_descriptions(self) -> Any:

5603 """Return a :term:`plugin-enabled` 'column descriptions' structure

5604 referring to the columns which are SELECTed by this statement.

5605

5606 This attribute is generally useful when using the ORM, as an

5607 extended structure which includes information about mapped

5608 entities is returned. The section :ref:`queryguide_inspection`

5609 contains more background.

5610

5611 For a Core-only statement, the structure returned by this accessor

5612 is derived from the same objects that are returned by the

5613 :attr:`.Select.selected_columns` accessor, formatted as a list of

5614 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5615 which indicate the column expressions to be selected::

5616

5617 >>> stmt = select(user_table)

5618 >>> stmt.column_descriptions

5619 [

5620 {

5621 'name': 'id',

5622 'type': Integer(),

5623 'expr': Column('id', Integer(), ...)},

5624 {

5625 'name': 'name',

5626 'type': String(length=30),

5627 'expr': Column('name', String(length=30), ...)}

5628 ]

5629

5630 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5631 attribute returns a structure for a Core-only set of entities,

5632 not just ORM-only entities.

5633

5634 .. seealso::

5635

5636 :attr:`.UpdateBase.entity_description` - entity information for

5637 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5638

5639 :ref:`queryguide_inspection` - ORM background

5640

5641 """

5642 meth = SelectState.get_plugin_class(self).get_column_descriptions

5643 return meth(self)

5644

5645 def from_statement(

5646 self, statement: roles.ReturnsRowsRole

5647 ) -> ExecutableReturnsRows:

5648 """Apply the columns which this :class:`.Select` would select

5649 onto another statement.

5650

5651 This operation is :term:`plugin-specific` and will raise a not

5652 supported exception if this :class:`_sql.Select` does not select from

5653 plugin-enabled entities.

5654

5655

5656 The statement is typically either a :func:`_expression.text` or

5657 :func:`_expression.select` construct, and should return the set of

5658 columns appropriate to the entities represented by this

5659 :class:`.Select`.

5660

5661 .. seealso::

5662

5663 :ref:`orm_queryguide_selecting_text` - usage examples in the

5664 ORM Querying Guide

5665

5666 """

5667 meth = SelectState.get_plugin_class(self).from_statement

5668 return meth(self, statement)

5669

5670 @_generative

5671 def join(

5672 self,

5673 target: _JoinTargetArgument,

5674 onclause: Optional[_OnClauseArgument] = None,

5675 *,

5676 isouter: bool = False,

5677 full: bool = False,

5678 ) -> Self:

5679 r"""Create a SQL JOIN against this :class:`_expression.Select`

5680 object's criterion

5681 and apply generatively, returning the newly resulting

5682 :class:`_expression.Select`.

5683

5684 E.g.::

5685

5686 stmt = select(user_table).join(

5687 address_table, user_table.c.id == address_table.c.user_id

5688 )

5689

5690 The above statement generates SQL similar to:

5691

5692 .. sourcecode:: sql

5693

5694 SELECT user.id, user.name

5695 FROM user

5696 JOIN address ON user.id = address.user_id

5697

5698 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5699 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5700 source that is within the FROM clause of the existing SELECT,

5701 and a given target :class:`_sql.FromClause`, and then adds

5702 this :class:`_sql.Join` to the FROM clause of the newly generated

5703 SELECT statement. This is completely reworked from the behavior

5704 in 1.3, which would instead create a subquery of the entire

5705 :class:`_expression.Select` and then join that subquery to the

5706 target.

5707

5708 This is a **backwards incompatible change** as the previous behavior

5709 was mostly useless, producing an unnamed subquery rejected by

5710 most databases in any case. The new behavior is modeled after

5711 that of the very successful :meth:`_orm.Query.join` method in the

5712 ORM, in order to support the functionality of :class:`_orm.Query`

5713 being available by using a :class:`_sql.Select` object with an

5714 :class:`_orm.Session`.

5715

5716 See the notes for this change at :ref:`change_select_join`.

5717

5718

5719 :param target: target table to join towards

5720

5721 :param onclause: ON clause of the join. If omitted, an ON clause

5722 is generated automatically based on the :class:`_schema.ForeignKey`

5723 linkages between the two tables, if one can be unambiguously

5724 determined, otherwise an error is raised.

5725

5726 :param isouter: if True, generate LEFT OUTER join. Same as

5727 :meth:`_expression.Select.outerjoin`.

5728

5729 :param full: if True, generate FULL OUTER join.

5730

5731 .. seealso::

5732

5733 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5734

5735 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5736

5737 :meth:`_expression.Select.join_from`

5738

5739 :meth:`_expression.Select.outerjoin`

5740

5741 """ # noqa: E501

5742 join_target = coercions.expect(

5743 roles.JoinTargetRole, target, apply_propagate_attrs=self

5744 )

5745 if onclause is not None:

5746 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5747 else:

5748 onclause_element = None

5749

5750 self._setup_joins += (

5751 (

5752 join_target,

5753 onclause_element,

5754 None,

5755 {"isouter": isouter, "full": full},

5756 ),

5757 )

5758 return self

5759

5760 def outerjoin_from(

5761 self,

5762 from_: _FromClauseArgument,

5763 target: _JoinTargetArgument,

5764 onclause: Optional[_OnClauseArgument] = None,

5765 *,

5766 full: bool = False,

5767 ) -> Self:

5768 r"""Create a SQL LEFT OUTER JOIN against this

5769 :class:`_expression.Select` object's criterion and apply generatively,

5770 returning the newly resulting :class:`_expression.Select`.

5771

5772 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5773

5774 """

5775 return self.join_from(

5776 from_, target, onclause=onclause, isouter=True, full=full

5777 )

5778

5779 @_generative

5780 def join_from(

5781 self,

5782 from_: _FromClauseArgument,

5783 target: _JoinTargetArgument,

5784 onclause: Optional[_OnClauseArgument] = None,

5785 *,

5786 isouter: bool = False,

5787 full: bool = False,

5788 ) -> Self:

5789 r"""Create a SQL JOIN against this :class:`_expression.Select`

5790 object's criterion

5791 and apply generatively, returning the newly resulting

5792 :class:`_expression.Select`.

5793

5794 E.g.::

5795

5796 stmt = select(user_table, address_table).join_from(

5797 user_table, address_table, user_table.c.id == address_table.c.user_id

5798 )

5799

5800 The above statement generates SQL similar to:

5801

5802 .. sourcecode:: sql

5803

5804 SELECT user.id, user.name, address.id, address.email, address.user_id

5805 FROM user JOIN address ON user.id = address.user_id

5806

5807 .. versionadded:: 1.4

5808

5809 :param from\_: the left side of the join, will be rendered in the

5810 FROM clause and is roughly equivalent to using the

5811 :meth:`.Select.select_from` method.

5812

5813 :param target: target table to join towards

5814

5815 :param onclause: ON clause of the join.

5816

5817 :param isouter: if True, generate LEFT OUTER join. Same as

5818 :meth:`_expression.Select.outerjoin`.

5819

5820 :param full: if True, generate FULL OUTER join.

5821

5822 .. seealso::

5823

5824 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5825

5826 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5827

5828 :meth:`_expression.Select.join`

5829

5830 """ # noqa: E501

5831

5832 # note the order of parsing from vs. target is important here, as we

5833 # are also deriving the source of the plugin (i.e. the subject mapper

5834 # in an ORM query) which should favor the "from_" over the "target"

5835

5836 from_ = coercions.expect(

5837 roles.FromClauseRole, from_, apply_propagate_attrs=self

5838 )

5839 join_target = coercions.expect(

5840 roles.JoinTargetRole, target, apply_propagate_attrs=self

5841 )

5842 if onclause is not None:

5843 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5844 else:

5845 onclause_element = None

5846

5847 self._setup_joins += (

5848 (

5849 join_target,

5850 onclause_element,

5851 from_,

5852 {"isouter": isouter, "full": full},

5853 ),

5854 )

5855 return self

5856

5857 def outerjoin(

5858 self,

5859 target: _JoinTargetArgument,

5860 onclause: Optional[_OnClauseArgument] = None,

5861 *,

5862 full: bool = False,

5863 ) -> Self:

5864 """Create a left outer join.

5865

5866 Parameters are the same as that of :meth:`_expression.Select.join`.

5867

5868 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5869 creates a :class:`_sql.Join` object between a

5870 :class:`_sql.FromClause` source that is within the FROM clause of

5871 the existing SELECT, and a given target :class:`_sql.FromClause`,

5872 and then adds this :class:`_sql.Join` to the FROM clause of the

5873 newly generated SELECT statement. This is completely reworked

5874 from the behavior in 1.3, which would instead create a subquery of

5875 the entire

5876 :class:`_expression.Select` and then join that subquery to the

5877 target.

5878

5879 This is a **backwards incompatible change** as the previous behavior

5880 was mostly useless, producing an unnamed subquery rejected by

5881 most databases in any case. The new behavior is modeled after

5882 that of the very successful :meth:`_orm.Query.join` method in the

5883 ORM, in order to support the functionality of :class:`_orm.Query`

5884 being available by using a :class:`_sql.Select` object with an

5885 :class:`_orm.Session`.

5886

5887 See the notes for this change at :ref:`change_select_join`.

5888

5889 .. seealso::

5890

5891 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5892

5893 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5894

5895 :meth:`_expression.Select.join`

5896

5897 """

5898 return self.join(target, onclause=onclause, isouter=True, full=full)

5899

5900 def get_final_froms(self) -> Sequence[FromClause]:

5901 """Compute the final displayed list of :class:`_expression.FromClause`

5902 elements.

5903

5904 This method will run through the full computation required to

5905 determine what FROM elements will be displayed in the resulting

5906 SELECT statement, including shadowing individual tables with

5907 JOIN objects, as well as full computation for ORM use cases including

5908 eager loading clauses.

5909

5910 For ORM use, this accessor returns the **post compilation**

5911 list of FROM objects; this collection will include elements such as

5912 eagerly loaded tables and joins. The objects will **not** be

5913 ORM enabled and not work as a replacement for the

5914 :meth:`_sql.Select.select_froms` collection; additionally, the

5915 method is not well performing for an ORM enabled statement as it

5916 will incur the full ORM construction process.

5917

5918 To retrieve the FROM list that's implied by the "columns" collection

5919 passed to the :class:`_sql.Select` originally, use the

5920 :attr:`_sql.Select.columns_clause_froms` accessor.

5921

5922 To select from an alternative set of columns while maintaining the

5923 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5924 pass the

5925 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5926 parameter.

5927

5928 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5929 method replaces the previous :attr:`_sql.Select.froms` accessor,

5930 which is deprecated.

5931

5932 .. seealso::

5933

5934 :attr:`_sql.Select.columns_clause_froms`

5935

5936 """

5937 compiler = self._default_compiler()

5938

5939 return self._compile_state_factory(self, compiler)._get_display_froms()

5940

5941 @property

5942 @util.deprecated(

5943 "1.4.23",

5944 "The :attr:`_expression.Select.froms` attribute is moved to "

5945 "the :meth:`_expression.Select.get_final_froms` method.",

5946 )

5947 def froms(self) -> Sequence[FromClause]:

5948 """Return the displayed list of :class:`_expression.FromClause`

5949 elements.

5950

5951

5952 """

5953 return self.get_final_froms()

5954

5955 @property

5956 def columns_clause_froms(self) -> List[FromClause]:

5957 """Return the set of :class:`_expression.FromClause` objects implied

5958 by the columns clause of this SELECT statement.

5959

5960 .. versionadded:: 1.4.23

5961

5962 .. seealso::

5963

5964 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5965 statement into account

5966

5967 :meth:`_sql.Select.with_only_columns` - makes use of this

5968 collection to set up a new FROM list

5969

5970 """

5971

5972 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5973 self

5974 )

5975

5976 @property

5977 def inner_columns(self) -> _SelectIterable:

5978 """An iterator of all :class:`_expression.ColumnElement`

5979 expressions which would

5980 be rendered into the columns clause of the resulting SELECT statement.

5981

5982 This method is legacy as of 1.4 and is superseded by the

5983 :attr:`_expression.Select.exported_columns` collection.

5984

5985 """

5986

5987 return iter(self._all_selected_columns)

5988

5989 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

5990 if fromclause is not None and self in fromclause._cloned_set:

5991 return True

5992

5993 for f in self._iterate_from_elements():

5994 if f.is_derived_from(fromclause):

5995 return True

5996 return False

5997

5998 def _copy_internals(

5999 self, clone: _CloneCallableType = _clone, **kw: Any

6000 ) -> None:

6001 # Select() object has been cloned and probably adapted by the

6002 # given clone function. Apply the cloning function to internal

6003 # objects

6004

6005 # 1. keep a dictionary of the froms we've cloned, and what

6006 # they've become. This allows us to ensure the same cloned from

6007 # is used when other items such as columns are "cloned"

6008

6009 all_the_froms = set(

6010 itertools.chain(

6011 _from_objects(*self._raw_columns),

6012 _from_objects(*self._where_criteria),

6013 _from_objects(*[elem[0] for elem in self._setup_joins]),

6014 )

6015 )

6016

6017 # do a clone for the froms we've gathered. what is important here

6018 # is if any of the things we are selecting from, like tables,

6019 # were converted into Join objects. if so, these need to be

6020 # added to _from_obj explicitly, because otherwise they won't be

6021 # part of the new state, as they don't associate themselves with

6022 # their columns.

6023 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6024

6025 # 2. copy FROM collections, adding in joins that we've created.

6026 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6027 add_froms = (

6028 {f for f in new_froms.values() if isinstance(f, Join)}

6029 .difference(all_the_froms)

6030 .difference(existing_from_obj)

6031 )

6032

6033 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6034

6035 # 3. clone everything else, making sure we use columns

6036 # corresponding to the froms we just made.

6037 def replace(

6038 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6039 **kw: Any,

6040 ) -> Optional[KeyedColumnElement[Any]]:

6041 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6042 newelem = new_froms[obj.table].corresponding_column(obj)

6043 return newelem

6044 return None

6045

6046 kw["replace"] = replace

6047

6048 # copy everything else. for table-ish things like correlate,

6049 # correlate_except, setup_joins, these clone normally. For

6050 # column-expression oriented things like raw_columns, where_criteria,

6051 # order by, we get this from the new froms.

6052 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6053

6054 self._reset_memoizations()

6055

6056 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6057 return itertools.chain(

6058 super().get_children(

6059 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6060 **kw,

6061 ),

6062 self._iterate_from_elements(),

6063 )

6064

6065 @_generative

6066 def add_columns(

6067 self, *entities: _ColumnsClauseArgument[Any]

6068 ) -> Select[Unpack[TupleAny]]:

6069 r"""Return a new :func:`_expression.select` construct with

6070 the given entities appended to its columns clause.

6071

6072 E.g.::

6073

6074 my_select = my_select.add_columns(table.c.new_column)

6075

6076 The original expressions in the columns clause remain in place.

6077 To replace the original expressions with new ones, see the method

6078 :meth:`_expression.Select.with_only_columns`.

6079

6080 :param \*entities: column, table, or other entity expressions to be

6081 added to the columns clause

6082

6083 .. seealso::

6084

6085 :meth:`_expression.Select.with_only_columns` - replaces existing

6086 expressions rather than appending.

6087

6088 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6089 example

6090

6091 """

6092 self._reset_memoizations()

6093

6094 self._raw_columns = self._raw_columns + [

6095 coercions.expect(

6096 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6097 )

6098 for column in entities

6099 ]

6100 return self

6101

6102 def _set_entities(

6103 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6104 ) -> None:

6105 self._raw_columns = [

6106 coercions.expect(

6107 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6108 )

6109 for ent in util.to_list(entities)

6110 ]

6111

6112 @util.deprecated(

6113 "1.4",

6114 "The :meth:`_expression.Select.column` method is deprecated and will "

6115 "be removed in a future release. Please use "

6116 ":meth:`_expression.Select.add_columns`",

6117 )

6118 def column(

6119 self, column: _ColumnsClauseArgument[Any]

6120 ) -> Select[Unpack[TupleAny]]:

6121 """Return a new :func:`_expression.select` construct with

6122 the given column expression added to its columns clause.

6123

6124 E.g.::

6125

6126 my_select = my_select.column(table.c.new_column)

6127

6128 See the documentation for

6129 :meth:`_expression.Select.with_only_columns`

6130 for guidelines on adding /replacing the columns of a

6131 :class:`_expression.Select` object.

6132

6133 """

6134 return self.add_columns(column)

6135

6136 @util.preload_module("sqlalchemy.sql.util")

6137 def reduce_columns(

6138 self, only_synonyms: bool = True

6139 ) -> Select[Unpack[TupleAny]]:

6140 """Return a new :func:`_expression.select` construct with redundantly

6141 named, equivalently-valued columns removed from the columns clause.

6142

6143 "Redundant" here means two columns where one refers to the

6144 other either based on foreign key, or via a simple equality

6145 comparison in the WHERE clause of the statement. The primary purpose

6146 of this method is to automatically construct a select statement

6147 with all uniquely-named columns, without the need to use

6148 table-qualified labels as

6149 :meth:`_expression.Select.set_label_style`

6150 does.

6151

6152 When columns are omitted based on foreign key, the referred-to

6153 column is the one that's kept. When columns are omitted based on

6154 WHERE equivalence, the first column in the columns clause is the

6155 one that's kept.

6156

6157 :param only_synonyms: when True, limit the removal of columns

6158 to those which have the same name as the equivalent. Otherwise,

6159 all columns that are equivalent to another are removed.

6160

6161 """

6162 woc: Select[Unpack[TupleAny]]

6163 woc = self.with_only_columns(

6164 *util.preloaded.sql_util.reduce_columns(

6165 self._all_selected_columns,

6166 only_synonyms=only_synonyms,

6167 *(self._where_criteria + self._from_obj),

6168 )

6169 )

6170 return woc

6171

6172 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6173

6174 # code within this block is **programmatically,

6175 # statically generated** by tools/generate_tuple_map_overloads.py

6176

6177 @overload

6178 def with_only_columns(

6179 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6180 ) -> Select[_T0]: ...

6181

6182 @overload

6183 def with_only_columns(

6184 self,

6185 __ent0: _TCCA[_T0],

6186 __ent1: _TCCA[_T1],

6187 /,

6188 *,

6189 maintain_column_froms: bool = ...,

6190 ) -> Select[_T0, _T1]: ...

6191

6192 @overload

6193 def with_only_columns(

6194 self,

6195 __ent0: _TCCA[_T0],

6196 __ent1: _TCCA[_T1],

6197 __ent2: _TCCA[_T2],

6198 /,

6199 *,

6200 maintain_column_froms: bool = ...,

6201 ) -> Select[_T0, _T1, _T2]: ...

6202

6203 @overload

6204 def with_only_columns(

6205 self,

6206 __ent0: _TCCA[_T0],

6207 __ent1: _TCCA[_T1],

6208 __ent2: _TCCA[_T2],

6209 __ent3: _TCCA[_T3],

6210 /,

6211 *,

6212 maintain_column_froms: bool = ...,

6213 ) -> Select[_T0, _T1, _T2, _T3]: ...

6214

6215 @overload

6216 def with_only_columns(

6217 self,

6218 __ent0: _TCCA[_T0],

6219 __ent1: _TCCA[_T1],

6220 __ent2: _TCCA[_T2],

6221 __ent3: _TCCA[_T3],

6222 __ent4: _TCCA[_T4],

6223 /,

6224 *,

6225 maintain_column_froms: bool = ...,

6226 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6227

6228 @overload

6229 def with_only_columns(

6230 self,

6231 __ent0: _TCCA[_T0],

6232 __ent1: _TCCA[_T1],

6233 __ent2: _TCCA[_T2],

6234 __ent3: _TCCA[_T3],

6235 __ent4: _TCCA[_T4],

6236 __ent5: _TCCA[_T5],

6237 /,

6238 *,

6239 maintain_column_froms: bool = ...,

6240 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6241

6242 @overload

6243 def with_only_columns(

6244 self,

6245 __ent0: _TCCA[_T0],

6246 __ent1: _TCCA[_T1],

6247 __ent2: _TCCA[_T2],

6248 __ent3: _TCCA[_T3],

6249 __ent4: _TCCA[_T4],

6250 __ent5: _TCCA[_T5],

6251 __ent6: _TCCA[_T6],

6252 /,

6253 *,

6254 maintain_column_froms: bool = ...,

6255 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6256

6257 @overload

6258 def with_only_columns(

6259 self,

6260 __ent0: _TCCA[_T0],

6261 __ent1: _TCCA[_T1],

6262 __ent2: _TCCA[_T2],

6263 __ent3: _TCCA[_T3],

6264 __ent4: _TCCA[_T4],

6265 __ent5: _TCCA[_T5],

6266 __ent6: _TCCA[_T6],

6267 __ent7: _TCCA[_T7],

6268 /,

6269 *entities: _ColumnsClauseArgument[Any],

6270 maintain_column_froms: bool = ...,

6271 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6272

6273 # END OVERLOADED FUNCTIONS self.with_only_columns

6274

6275 @overload

6276 def with_only_columns(

6277 self,

6278 *entities: _ColumnsClauseArgument[Any],

6279 maintain_column_froms: bool = False,

6280 **__kw: Any,

6281 ) -> Select[Unpack[TupleAny]]: ...

6282

6283 @_generative

6284 def with_only_columns(

6285 self,

6286 *entities: _ColumnsClauseArgument[Any],

6287 maintain_column_froms: bool = False,

6288 **__kw: Any,

6289 ) -> Select[Unpack[TupleAny]]:

6290 r"""Return a new :func:`_expression.select` construct with its columns

6291 clause replaced with the given entities.

6292

6293 By default, this method is exactly equivalent to as if the original

6294 :func:`_expression.select` had been called with the given entities.

6295 E.g. a statement::

6296

6297 s = select(table1.c.a, table1.c.b)

6298 s = s.with_only_columns(table1.c.b)

6299

6300 should be exactly equivalent to::

6301

6302 s = select(table1.c.b)

6303

6304 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6305 will also dynamically alter the FROM clause of the

6306 statement if it is not explicitly stated.

6307 To maintain the existing set of FROMs including those implied by the

6308 current columns clause, add the

6309 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6310 parameter::

6311

6312 s = select(table1.c.a, table2.c.b)

6313 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6314

6315 The above parameter performs a transfer of the effective FROMs

6316 in the columns collection to the :meth:`_sql.Select.select_from`

6317 method, as though the following were invoked::

6318

6319 s = select(table1.c.a, table2.c.b)

6320 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6321

6322 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6323 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6324 collection and performs an operation equivalent to the following::

6325

6326 s = select(table1.c.a, table2.c.b)

6327 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6328

6329 :param \*entities: column expressions to be used.

6330

6331 :param maintain_column_froms: boolean parameter that will ensure the

6332 FROM list implied from the current columns clause will be transferred

6333 to the :meth:`_sql.Select.select_from` method first.

6334

6335 .. versionadded:: 1.4.23

6336

6337 """ # noqa: E501

6338

6339 if __kw:

6340 raise _no_kw()

6341

6342 # memoizations should be cleared here as of

6343 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6344 # is the case for now.

6345 self._assert_no_memoizations()

6346

6347 if maintain_column_froms:

6348 self.select_from.non_generative( # type: ignore

6349 self, *self.columns_clause_froms

6350 )

6351

6352 # then memoize the FROMs etc.

6353 _MemoizedSelectEntities._generate_for_statement(self)

6354

6355 self._raw_columns = [

6356 coercions.expect(roles.ColumnsClauseRole, c)

6357 for c in coercions._expression_collection_was_a_list(

6358 "entities", "Select.with_only_columns", entities

6359 )

6360 ]

6361 return self

6362

6363 @property

6364 def whereclause(self) -> Optional[ColumnElement[Any]]:

6365 """Return the completed WHERE clause for this

6366 :class:`_expression.Select` statement.

6367

6368 This assembles the current collection of WHERE criteria

6369 into a single :class:`_expression.BooleanClauseList` construct.

6370

6371

6372 .. versionadded:: 1.4

6373

6374 """

6375

6376 return BooleanClauseList._construct_for_whereclause(

6377 self._where_criteria

6378 )

6379

6380 _whereclause = whereclause

6381

6382 @_generative

6383 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6384 """Return a new :func:`_expression.select` construct with

6385 the given expression added to

6386 its WHERE clause, joined to the existing clause via AND, if any.

6387

6388 """

6389

6390 assert isinstance(self._where_criteria, tuple)

6391

6392 for criterion in whereclause:

6393 where_criteria: ColumnElement[Any] = coercions.expect(

6394 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6395 )

6396 self._where_criteria += (where_criteria,)

6397 return self

6398

6399 @_generative

6400 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6401 """Return a new :func:`_expression.select` construct with

6402 the given expression added to

6403 its HAVING clause, joined to the existing clause via AND, if any.

6404

6405 """

6406

6407 for criterion in having:

6408 having_criteria = coercions.expect(

6409 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6410 )

6411 self._having_criteria += (having_criteria,)

6412 return self

6413

6414 @_generative

6415 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6416 r"""Return a new :func:`_expression.select` construct which

6417 will apply DISTINCT to the SELECT statement overall.

6418

6419 E.g.::

6420

6421 from sqlalchemy import select

6422

6423 stmt = select(users_table.c.id, users_table.c.name).distinct()

6424

6425 The above would produce an statement resembling:

6426

6427 .. sourcecode:: sql

6428

6429 SELECT DISTINCT user.id, user.name FROM user

6430

6431 The method also historically accepted an ``*expr`` parameter which

6432 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6433 This is now replaced using the :func:`_postgresql.distinct_on`

6434 extension::

6435

6436 from sqlalchemy import select

6437 from sqlalchemy.dialects.postgresql import distinct_on

6438

6439 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6440

6441 Using this parameter on other backends which don't support this

6442 syntax will raise an error.

6443

6444 :param \*expr: optional column expressions. When present,

6445 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6446 construct. A deprecation warning and/or :class:`_exc.CompileError`

6447 will be raised on other backends.

6448

6449 .. deprecated:: 2.1 Passing expressions to

6450 :meth:`_sql.Select.distinct` is deprecated, use

6451 :func:`_postgresql.distinct_on` instead.

6452

6453 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6454 and will raise :class:`_exc.CompileError` in a future version.

6455

6456 .. seealso::

6457

6458 :func:`_postgresql.distinct_on`

6459

6460 :meth:`_sql.HasSyntaxExtensions.ext`

6461 """

6462 self._distinct = True

6463 if expr:

6464 warn_deprecated(

6465 "Passing expression to ``distinct`` to generate a "

6466 "DISTINCT ON clause is deprecated. Use instead the "

6467 "``postgresql.distinct_on`` function as an extension.",

6468 "2.1",

6469 )

6470 self._distinct_on = self._distinct_on + tuple(

6471 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6472 for e in expr

6473 )

6474 return self

6475

6476 @_generative

6477 def select_from(self, *froms: _FromClauseArgument) -> Self:

6478 r"""Return a new :func:`_expression.select` construct with the

6479 given FROM expression(s)

6480 merged into its list of FROM objects.

6481

6482 E.g.::

6483

6484 table1 = table("t1", column("a"))

6485 table2 = table("t2", column("b"))

6486 s = select(table1.c.a).select_from(

6487 table1.join(table2, table1.c.a == table2.c.b)

6488 )

6489

6490 The "from" list is a unique set on the identity of each element,

6491 so adding an already present :class:`_schema.Table`

6492 or other selectable

6493 will have no effect. Passing a :class:`_expression.Join` that refers

6494 to an already present :class:`_schema.Table`

6495 or other selectable will have

6496 the effect of concealing the presence of that selectable as

6497 an individual element in the rendered FROM list, instead

6498 rendering it into a JOIN clause.

6499

6500 While the typical purpose of :meth:`_expression.Select.select_from`

6501 is to

6502 replace the default, derived FROM clause with a join, it can

6503 also be called with individual table elements, multiple times

6504 if desired, in the case that the FROM clause cannot be fully

6505 derived from the columns clause::

6506

6507 select(func.count("*")).select_from(table1)

6508

6509 """

6510

6511 self._from_obj += tuple(

6512 coercions.expect(

6513 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6514 )

6515 for fromclause in froms

6516 )

6517 return self

6518

6519 @_generative

6520 def correlate(

6521 self,

6522 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6523 ) -> Self:

6524 r"""Return a new :class:`_expression.Select`

6525 which will correlate the given FROM

6526 clauses to that of an enclosing :class:`_expression.Select`.

6527

6528 Calling this method turns off the :class:`_expression.Select` object's

6529 default behavior of "auto-correlation". Normally, FROM elements

6530 which appear in a :class:`_expression.Select`

6531 that encloses this one via

6532 its :term:`WHERE clause`, ORDER BY, HAVING or

6533 :term:`columns clause` will be omitted from this

6534 :class:`_expression.Select`

6535 object's :term:`FROM clause`.

6536 Setting an explicit correlation collection using the

6537 :meth:`_expression.Select.correlate`

6538 method provides a fixed list of FROM objects

6539 that can potentially take place in this process.

6540

6541 When :meth:`_expression.Select.correlate`

6542 is used to apply specific FROM clauses

6543 for correlation, the FROM elements become candidates for

6544 correlation regardless of how deeply nested this

6545 :class:`_expression.Select`

6546 object is, relative to an enclosing :class:`_expression.Select`

6547 which refers to

6548 the same FROM object. This is in contrast to the behavior of

6549 "auto-correlation" which only correlates to an immediate enclosing

6550 :class:`_expression.Select`.

6551 Multi-level correlation ensures that the link

6552 between enclosed and enclosing :class:`_expression.Select`

6553 is always via

6554 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6555 correlation to take place.

6556

6557 If ``None`` is passed, the :class:`_expression.Select`

6558 object will correlate

6559 none of its FROM entries, and all will render unconditionally

6560 in the local FROM clause.

6561

6562 :param \*fromclauses: one or more :class:`.FromClause` or other

6563 FROM-compatible construct such as an ORM mapped entity to become part

6564 of the correlate collection; alternatively pass a single value

6565 ``None`` to remove all existing correlations.

6566

6567 .. seealso::

6568

6569 :meth:`_expression.Select.correlate_except`

6570

6571 :ref:`tutorial_scalar_subquery`

6572

6573 """

6574

6575 # tests failing when we try to change how these

6576 # arguments are passed

6577

6578 self._auto_correlate = False

6579 if not fromclauses or fromclauses[0] in {None, False}:

6580 if len(fromclauses) > 1:

6581 raise exc.ArgumentError(

6582 "additional FROM objects not accepted when "

6583 "passing None/False to correlate()"

6584 )

6585 self._correlate = ()

6586 else:

6587 self._correlate = self._correlate + tuple(

6588 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6589 )

6590 return self

6591

6592 @_generative

6593 def correlate_except(

6594 self,

6595 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6596 ) -> Self:

6597 r"""Return a new :class:`_expression.Select`

6598 which will omit the given FROM

6599 clauses from the auto-correlation process.

6600

6601 Calling :meth:`_expression.Select.correlate_except` turns off the

6602 :class:`_expression.Select` object's default behavior of

6603 "auto-correlation" for the given FROM elements. An element

6604 specified here will unconditionally appear in the FROM list, while

6605 all other FROM elements remain subject to normal auto-correlation

6606 behaviors.

6607

6608 If ``None`` is passed, or no arguments are passed,

6609 the :class:`_expression.Select` object will correlate all of its

6610 FROM entries.

6611

6612 :param \*fromclauses: a list of one or more

6613 :class:`_expression.FromClause`

6614 constructs, or other compatible constructs (i.e. ORM-mapped

6615 classes) to become part of the correlate-exception collection.

6616

6617 .. seealso::

6618

6619 :meth:`_expression.Select.correlate`

6620

6621 :ref:`tutorial_scalar_subquery`

6622

6623 """

6624

6625 self._auto_correlate = False

6626 if not fromclauses or fromclauses[0] in {None, False}:

6627 if len(fromclauses) > 1:

6628 raise exc.ArgumentError(

6629 "additional FROM objects not accepted when "

6630 "passing None/False to correlate_except()"

6631 )

6632 self._correlate_except = ()

6633 else:

6634 self._correlate_except = (self._correlate_except or ()) + tuple(

6635 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6636 )

6637

6638 return self

6639

6640 @HasMemoized_ro_memoized_attribute

6641 def selected_columns(

6642 self,

6643 ) -> ColumnCollection[str, ColumnElement[Any]]:

6644 """A :class:`_expression.ColumnCollection`

6645 representing the columns that

6646 this SELECT statement or similar construct returns in its result set,

6647 not including :class:`_sql.TextClause` constructs.

6648

6649 This collection differs from the :attr:`_expression.FromClause.columns`

6650 collection of a :class:`_expression.FromClause` in that the columns

6651 within this collection cannot be directly nested inside another SELECT

6652 statement; a subquery must be applied first which provides for the

6653 necessary parenthesization required by SQL.

6654

6655 For a :func:`_expression.select` construct, the collection here is

6656 exactly what would be rendered inside the "SELECT" statement, and the

6657 :class:`_expression.ColumnElement` objects are directly present as they

6658 were given, e.g.::

6659

6660 col1 = column("q", Integer)

6661 col2 = column("p", Integer)

6662 stmt = select(col1, col2)

6663

6664 Above, ``stmt.selected_columns`` would be a collection that contains

6665 the ``col1`` and ``col2`` objects directly. For a statement that is

6666 against a :class:`_schema.Table` or other

6667 :class:`_expression.FromClause`, the collection will use the

6668 :class:`_expression.ColumnElement` objects that are in the

6669 :attr:`_expression.FromClause.c` collection of the from element.

6670

6671 A use case for the :attr:`_sql.Select.selected_columns` collection is

6672 to allow the existing columns to be referenced when adding additional

6673 criteria, e.g.::

6674

6675 def filter_on_id(my_select, id):

6676 return my_select.where(my_select.selected_columns["id"] == id)

6677

6678

6679 stmt = select(MyModel)

6680

6681 # adds "WHERE id=:param" to the statement

6682 stmt = filter_on_id(stmt, 42)

6683

6684 .. note::

6685

6686 The :attr:`_sql.Select.selected_columns` collection does not

6687 include expressions established in the columns clause using the

6688 :func:`_sql.text` construct; these are silently omitted from the

6689 collection. To use plain textual column expressions inside of a

6690 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6691 construct.

6692

6693

6694 .. versionadded:: 1.4

6695

6696 """

6697

6698 # compare to SelectState._generate_columns_plus_names, which

6699 # generates the actual names used in the SELECT string. that

6700 # method is more complex because it also renders columns that are

6701 # fully ambiguous, e.g. same column more than once.

6702 conv = cast(

6703 "Callable[[Any], str]",

6704 SelectState._column_naming_convention(self._label_style),

6705 )

6706

6707 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6708 [

6709 (conv(c), c)

6710 for c in self._all_selected_columns

6711 if is_column_element(c)

6712 ]

6713 )

6714 return cc.as_readonly()

6715

6716 @HasMemoized_ro_memoized_attribute

6717 def _all_selected_columns(self) -> _SelectIterable:

6718 meth = SelectState.get_plugin_class(self).all_selected_columns

6719 return list(meth(self))

6720

6721 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6722 if self._label_style is LABEL_STYLE_NONE:

6723 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6724 return self

6725

6726 def _generate_fromclause_column_proxies(

6727 self,

6728 subquery: FromClause,

6729 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6730 primary_key: ColumnSet,

6731 foreign_keys: Set[KeyedColumnElement[Any]],

6732 *,

6733 proxy_compound_columns: Optional[

6734 Iterable[Sequence[ColumnElement[Any]]]

6735 ] = None,

6736 ) -> None:

6737 """Generate column proxies to place in the exported ``.c``

6738 collection of a subquery."""

6739

6740 if proxy_compound_columns:

6741 extra_col_iterator = proxy_compound_columns

6742 prox = [

6743 c._make_proxy(

6744 subquery,

6745 key=proxy_key,

6746 name=required_label_name,

6747 name_is_truncatable=True,

6748 compound_select_cols=extra_cols,

6749 primary_key=primary_key,

6750 foreign_keys=foreign_keys,

6751 )

6752 for (

6753 (

6754 required_label_name,

6755 proxy_key,

6756 fallback_label_name,

6757 c,

6758 repeated,

6759 ),

6760 extra_cols,

6761 ) in (

6762 zip(

6763 self._generate_columns_plus_names(False),

6764 extra_col_iterator,

6765 )

6766 )

6767 if is_column_element(c)

6768 ]

6769 else:

6770 prox = [

6771 c._make_proxy(

6772 subquery,

6773 key=proxy_key,

6774 name=required_label_name,

6775 name_is_truncatable=True,

6776 primary_key=primary_key,

6777 foreign_keys=foreign_keys,

6778 )

6779 for (

6780 required_label_name,

6781 proxy_key,

6782 fallback_label_name,

6783 c,

6784 repeated,

6785 ) in (self._generate_columns_plus_names(False))

6786 if is_column_element(c)

6787 ]

6788

6789 columns._populate_separate_keys(prox)

6790

6791 def _needs_parens_for_grouping(self) -> bool:

6792 return self._has_row_limiting_clause or bool(

6793 self._order_by_clause.clauses

6794 )

6795

6796 def self_group(

6797 self, against: Optional[OperatorType] = None

6798 ) -> Union[SelectStatementGrouping[Self], Self]:

6799 """Return a 'grouping' construct as per the

6800 :class:`_expression.ClauseElement` specification.

6801

6802 This produces an element that can be embedded in an expression. Note

6803 that this method is called automatically as needed when constructing

6804 expressions and should not require explicit use.

6805

6806 """

6807 if (

6808 isinstance(against, CompoundSelect)

6809 and not self._needs_parens_for_grouping()

6810 ):

6811 return self

6812 else:

6813 return SelectStatementGrouping(self)

6814

6815 def union(

6816 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6817 ) -> CompoundSelect[Unpack[_Ts]]:

6818 r"""Return a SQL ``UNION`` of this select() construct against

6819 the given selectables provided as positional arguments.

6820

6821 :param \*other: one or more elements with which to create a

6822 UNION.

6823

6824 .. versionchanged:: 1.4.28

6825

6826 multiple elements are now accepted.

6827

6828 :param \**kwargs: keyword arguments are forwarded to the constructor

6829 for the newly created :class:`_sql.CompoundSelect` object.

6830

6831 """

6832 return CompoundSelect._create_union(self, *other)

6833

6834 def union_all(

6835 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6836 ) -> CompoundSelect[Unpack[_Ts]]:

6837 r"""Return a SQL ``UNION ALL`` of this select() construct against

6838 the given selectables provided as positional arguments.

6839

6840 :param \*other: one or more elements with which to create a

6841 UNION.

6842

6843 .. versionchanged:: 1.4.28

6844

6845 multiple elements are now accepted.

6846

6847 :param \**kwargs: keyword arguments are forwarded to the constructor

6848 for the newly created :class:`_sql.CompoundSelect` object.

6849

6850 """

6851 return CompoundSelect._create_union_all(self, *other)

6852

6853 def except_(

6854 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6855 ) -> CompoundSelect[Unpack[_Ts]]:

6856 r"""Return a SQL ``EXCEPT`` of this select() construct against

6857 the given selectable provided as positional arguments.

6858

6859 :param \*other: one or more elements with which to create a

6860 UNION.

6861

6862 .. versionchanged:: 1.4.28

6863

6864 multiple elements are now accepted.

6865

6866 """

6867 return CompoundSelect._create_except(self, *other)

6868

6869 def except_all(

6870 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6871 ) -> CompoundSelect[Unpack[_Ts]]:

6872 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6873 the given selectables provided as positional arguments.

6874

6875 :param \*other: one or more elements with which to create a

6876 UNION.

6877

6878 .. versionchanged:: 1.4.28

6879

6880 multiple elements are now accepted.

6881

6882 """

6883 return CompoundSelect._create_except_all(self, *other)

6884

6885 def intersect(

6886 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6887 ) -> CompoundSelect[Unpack[_Ts]]:

6888 r"""Return a SQL ``INTERSECT`` of this select() construct against

6889 the given selectables provided as positional arguments.

6890

6891 :param \*other: one or more elements with which to create a

6892 UNION.

6893

6894 .. versionchanged:: 1.4.28

6895

6896 multiple elements are now accepted.

6897

6898 :param \**kwargs: keyword arguments are forwarded to the constructor

6899 for the newly created :class:`_sql.CompoundSelect` object.

6900

6901 """

6902 return CompoundSelect._create_intersect(self, *other)

6903

6904 def intersect_all(

6905 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6906 ) -> CompoundSelect[Unpack[_Ts]]:

6907 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6908 against the given selectables provided as positional arguments.

6909

6910 :param \*other: one or more elements with which to create a

6911 UNION.

6912

6913 .. versionchanged:: 1.4.28

6914

6915 multiple elements are now accepted.

6916

6917 :param \**kwargs: keyword arguments are forwarded to the constructor

6918 for the newly created :class:`_sql.CompoundSelect` object.

6919

6920 """

6921 return CompoundSelect._create_intersect_all(self, *other)

6922

6923

6924class ScalarSelect(

6925 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6926):

6927 """Represent a scalar subquery.

6928

6929

6930 A :class:`_sql.ScalarSelect` is created by invoking the

6931 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6932 then participates in other SQL expressions as a SQL column expression

6933 within the :class:`_sql.ColumnElement` hierarchy.

6934

6935 .. seealso::

6936

6937 :meth:`_sql.SelectBase.scalar_subquery`

6938

6939 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6940

6941 """

6942

6943 _traverse_internals: _TraverseInternalsType = [

6944 ("element", InternalTraversal.dp_clauseelement),

6945 ("type", InternalTraversal.dp_type),

6946 ]

6947

6948 _from_objects: List[FromClause] = []

6949 _is_from_container = True

6950 if not TYPE_CHECKING:

6951 _is_implicitly_boolean = False

6952 inherit_cache = True

6953

6954 element: SelectBase

6955

6956 def __init__(self, element: SelectBase) -> None:

6957 self.element = element

6958 self.type = element._scalar_type()

6959 self._propagate_attrs = element._propagate_attrs

6960

6961 def __getattr__(self, attr: str) -> Any:

6962 return getattr(self.element, attr)

6963

6964 def __getstate__(self) -> Dict[str, Any]:

6965 return {"element": self.element, "type": self.type}

6966

6967 def __setstate__(self, state: Dict[str, Any]) -> None:

6968 self.element = state["element"]

6969 self.type = state["type"]

6970

6971 @property

6972 def columns(self) -> NoReturn:

6973 raise exc.InvalidRequestError(

6974 "Scalar Select expression has no "

6975 "columns; use this object directly "

6976 "within a column-level expression."

6977 )

6978

6979 c = columns

6980

6981 @_generative

6982 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6983 """Apply a WHERE clause to the SELECT statement referred to

6984 by this :class:`_expression.ScalarSelect`.

6985

6986 """

6987 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6988 crit

6989 )

6990 return self

6991

6992 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6993 return self

6994

6995 def _ungroup(self) -> Self:

6996 return self

6997

6998 @_generative

6999 def correlate(

7000 self,

7001 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7002 ) -> Self:

7003 r"""Return a new :class:`_expression.ScalarSelect`

7004 which will correlate the given FROM

7005 clauses to that of an enclosing :class:`_expression.Select`.

7006

7007 This method is mirrored from the :meth:`_sql.Select.correlate` method

7008 of the underlying :class:`_sql.Select`. The method applies the

7009 :meth:_sql.Select.correlate` method, then returns a new

7010 :class:`_sql.ScalarSelect` against that statement.

7011

7012 .. versionadded:: 1.4 Previously, the

7013 :meth:`_sql.ScalarSelect.correlate`

7014 method was only available from :class:`_sql.Select`.

7015

7016 :param \*fromclauses: a list of one or more

7017 :class:`_expression.FromClause`

7018 constructs, or other compatible constructs (i.e. ORM-mapped

7019 classes) to become part of the correlate collection.

7020

7021 .. seealso::

7022

7023 :meth:`_expression.ScalarSelect.correlate_except`

7024

7025 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7026

7027

7028 """

7029 self.element = cast(

7030 "Select[Unpack[TupleAny]]", self.element

7031 ).correlate(*fromclauses)

7032 return self

7033

7034 @_generative

7035 def correlate_except(

7036 self,

7037 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7038 ) -> Self:

7039 r"""Return a new :class:`_expression.ScalarSelect`

7040 which will omit the given FROM

7041 clauses from the auto-correlation process.

7042

7043 This method is mirrored from the

7044 :meth:`_sql.Select.correlate_except` method of the underlying

7045 :class:`_sql.Select`. The method applies the

7046 :meth:_sql.Select.correlate_except` method, then returns a new

7047 :class:`_sql.ScalarSelect` against that statement.

7048

7049 .. versionadded:: 1.4 Previously, the

7050 :meth:`_sql.ScalarSelect.correlate_except`

7051 method was only available from :class:`_sql.Select`.

7052

7053 :param \*fromclauses: a list of one or more

7054 :class:`_expression.FromClause`

7055 constructs, or other compatible constructs (i.e. ORM-mapped

7056 classes) to become part of the correlate-exception collection.

7057

7058 .. seealso::

7059

7060 :meth:`_expression.ScalarSelect.correlate`

7061

7062 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7063

7064

7065 """

7066

7067 self.element = cast(

7068 "Select[Unpack[TupleAny]]", self.element

7069 ).correlate_except(*fromclauses)

7070 return self

7071

7072

7073class Exists(UnaryExpression[bool]):

7074 """Represent an ``EXISTS`` clause.

7075

7076 See :func:`_sql.exists` for a description of usage.

7077

7078 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7079 instance by calling :meth:`_sql.SelectBase.exists`.

7080

7081 """

7082

7083 inherit_cache = True

7084

7085 def __init__(

7086 self,

7087 __argument: Optional[

7088 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7089 ] = None,

7090 /,

7091 ):

7092 s: ScalarSelect[Any]

7093

7094 # TODO: this seems like we should be using coercions for this

7095 if __argument is None:

7096 s = Select(literal_column("*")).scalar_subquery()

7097 elif isinstance(__argument, SelectBase):

7098 s = __argument.scalar_subquery()

7099 s._propagate_attrs = __argument._propagate_attrs

7100 elif isinstance(__argument, ScalarSelect):

7101 s = __argument

7102 else:

7103 s = Select(__argument).scalar_subquery()

7104

7105 UnaryExpression.__init__(

7106 self,

7107 s,

7108 operator=operators.exists,

7109 type_=type_api.BOOLEANTYPE,

7110 )

7111

7112 @util.ro_non_memoized_property

7113 def _from_objects(self) -> List[FromClause]:

7114 return []

7115

7116 def _regroup(

7117 self,

7118 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7119 ) -> ScalarSelect[Any]:

7120

7121 assert isinstance(self.element, ScalarSelect)

7122 element = self.element.element

7123 if not isinstance(element, Select):

7124 raise exc.InvalidRequestError(

7125 "Can only apply this operation to a plain SELECT construct"

7126 )

7127 new_element = fn(element)

7128

7129 return_value = new_element.scalar_subquery()

7130 return return_value

7131

7132 def select(self) -> Select[bool]:

7133 r"""Return a SELECT of this :class:`_expression.Exists`.

7134

7135 e.g.::

7136

7137 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7138

7139 This will produce a statement resembling:

7140

7141 .. sourcecode:: sql

7142

7143 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7144

7145 .. seealso::

7146

7147 :func:`_expression.select` - general purpose

7148 method which allows for arbitrary column lists.

7149

7150 """ # noqa

7151

7152 return Select(self)

7153

7154 def correlate(

7155 self,

7156 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7157 ) -> Self:

7158 """Apply correlation to the subquery noted by this

7159 :class:`_sql.Exists`.

7160

7161 .. seealso::

7162

7163 :meth:`_sql.ScalarSelect.correlate`

7164

7165 """

7166 e = self._clone()

7167 e.element = self._regroup(

7168 lambda element: element.correlate(*fromclauses)

7169 )

7170 return e

7171

7172 def correlate_except(

7173 self,

7174 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7175 ) -> Self:

7176 """Apply correlation to the subquery noted by this

7177 :class:`_sql.Exists`.

7178

7179 .. seealso::

7180

7181 :meth:`_sql.ScalarSelect.correlate_except`

7182

7183 """

7184 e = self._clone()

7185 e.element = self._regroup(

7186 lambda element: element.correlate_except(*fromclauses)

7187 )

7188 return e

7189

7190 def select_from(self, *froms: _FromClauseArgument) -> Self:

7191 """Return a new :class:`_expression.Exists` construct,

7192 applying the given

7193 expression to the :meth:`_expression.Select.select_from`

7194 method of the select

7195 statement contained.

7196

7197 .. note:: it is typically preferable to build a :class:`_sql.Select`

7198 statement first, including the desired WHERE clause, then use the

7199 :meth:`_sql.SelectBase.exists` method to produce an

7200 :class:`_sql.Exists` object at once.

7201

7202 """

7203 e = self._clone()

7204 e.element = self._regroup(lambda element: element.select_from(*froms))

7205 return e

7206

7207 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7208 """Return a new :func:`_expression.exists` construct with the

7209 given expression added to

7210 its WHERE clause, joined to the existing clause via AND, if any.

7211

7212

7213 .. note:: it is typically preferable to build a :class:`_sql.Select`

7214 statement first, including the desired WHERE clause, then use the

7215 :meth:`_sql.SelectBase.exists` method to produce an

7216 :class:`_sql.Exists` object at once.

7217

7218 """

7219 e = self._clone()

7220 e.element = self._regroup(lambda element: element.where(*clause))

7221 return e

7222

7223

7224class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7225 """Wrap a :class:`_expression.TextClause` construct within a

7226 :class:`_expression.SelectBase`

7227 interface.

7228

7229 This allows the :class:`_expression.TextClause` object to gain a

7230 ``.c`` collection

7231 and other FROM-like capabilities such as

7232 :meth:`_expression.FromClause.alias`,

7233 :meth:`_expression.SelectBase.cte`, etc.

7234

7235 The :class:`_expression.TextualSelect` construct is produced via the

7236 :meth:`_expression.TextClause.columns`

7237 method - see that method for details.

7238

7239 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7240 class was renamed

7241 from ``TextAsFrom``, to more correctly suit its role as a

7242 SELECT-oriented object and not a FROM clause.

7243

7244 .. seealso::

7245

7246 :func:`_expression.text`

7247

7248 :meth:`_expression.TextClause.columns` - primary creation interface.

7249

7250 """

7251

7252 __visit_name__ = "textual_select"

7253

7254 _label_style = LABEL_STYLE_NONE

7255

7256 _traverse_internals: _TraverseInternalsType = (

7257 [

7258 ("element", InternalTraversal.dp_clauseelement),

7259 ("column_args", InternalTraversal.dp_clauseelement_list),

7260 ]

7261 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7262 + HasCTE._has_ctes_traverse_internals

7263 + ExecutableStatement._executable_traverse_internals

7264 )

7265

7266 _is_textual = True

7267

7268 is_text = True

7269 is_select = True

7270

7271 def __init__(

7272 self,

7273 text: TextClause,

7274 columns: List[_ColumnExpressionArgument[Any]],

7275 positional: bool = False,

7276 ) -> None:

7277 self._init(

7278 text,

7279 # convert for ORM attributes->columns, etc

7280 [

7281 coercions.expect(roles.LabeledColumnExprRole, c)

7282 for c in columns

7283 ],

7284 positional,

7285 )

7286

7287 def _init(

7288 self,

7289 text: TextClause,

7290 columns: List[NamedColumn[Any]],

7291 positional: bool = False,

7292 ) -> None:

7293 self.element = text

7294 self.column_args = columns

7295 self.positional = positional

7296

7297 @HasMemoized_ro_memoized_attribute

7298 def selected_columns(

7299 self,

7300 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7301 """A :class:`_expression.ColumnCollection`

7302 representing the columns that

7303 this SELECT statement or similar construct returns in its result set,

7304 not including :class:`_sql.TextClause` constructs.

7305

7306 This collection differs from the :attr:`_expression.FromClause.columns`

7307 collection of a :class:`_expression.FromClause` in that the columns

7308 within this collection cannot be directly nested inside another SELECT

7309 statement; a subquery must be applied first which provides for the

7310 necessary parenthesization required by SQL.

7311

7312 For a :class:`_expression.TextualSelect` construct, the collection

7313 contains the :class:`_expression.ColumnElement` objects that were

7314 passed to the constructor, typically via the

7315 :meth:`_expression.TextClause.columns` method.

7316

7317

7318 .. versionadded:: 1.4

7319

7320 """

7321 return ColumnCollection(

7322 (c.key, c) for c in self.column_args

7323 ).as_readonly()

7324

7325 @util.ro_non_memoized_property

7326 def _all_selected_columns(self) -> _SelectIterable:

7327 return self.column_args

7328

7329 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7330 return self

7331

7332 def _ensure_disambiguated_names(self) -> TextualSelect:

7333 return self

7334

7335 @_generative

7336 def bindparams(

7337 self,

7338 *binds: BindParameter[Any],

7339 **bind_as_values: Any,

7340 ) -> Self:

7341 self.element = self.element.bindparams(*binds, **bind_as_values)

7342 return self

7343

7344 def _generate_fromclause_column_proxies(

7345 self,

7346 fromclause: FromClause,

7347 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7348 primary_key: ColumnSet,

7349 foreign_keys: Set[KeyedColumnElement[Any]],

7350 *,

7351 proxy_compound_columns: Optional[

7352 Iterable[Sequence[ColumnElement[Any]]]

7353 ] = None,

7354 ) -> None:

7355 if TYPE_CHECKING:

7356 assert isinstance(fromclause, Subquery)

7357

7358 if proxy_compound_columns:

7359 columns._populate_separate_keys(

7360 c._make_proxy(

7361 fromclause,

7362 compound_select_cols=extra_cols,

7363 primary_key=primary_key,

7364 foreign_keys=foreign_keys,

7365 )

7366 for c, extra_cols in zip(

7367 self.column_args, proxy_compound_columns

7368 )

7369 )

7370 else:

7371 columns._populate_separate_keys(

7372 c._make_proxy(

7373 fromclause,

7374 primary_key=primary_key,

7375 foreign_keys=foreign_keys,

7376 )

7377 for c in self.column_args

7378 )

7379

7380 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7381 return self.column_args[0].type

7382

7383

7384TextAsFrom = TextualSelect

7385"""Backwards compatibility with the previous name"""

7386

7387

7388class AnnotatedFromClause(Annotated):

7389 def _copy_internals(

7390 self,

7391 _annotations_traversal: bool = False,

7392 ind_cols_on_fromclause: bool = False,

7393 **kw: Any,

7394 ) -> None:

7395 super()._copy_internals(**kw)

7396

7397 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7398 # the traversals used by annotations for these cases are not currently

7399 # designed around expecting that inner elements inside of

7400 # AnnotatedFromClause's element are also deep copied, so skip for these

7401 # cases. in other cases such as plain visitors.cloned_traverse(), we

7402 # expect this to happen. see issue #12915

7403 if not _annotations_traversal:

7404 ee = self._Annotated__element # type: ignore

7405 ee._copy_internals(**kw)

7406

7407 if ind_cols_on_fromclause:

7408 # passed from annotations._deep_annotate(). See that function

7409 # for notes

7410 ee = self._Annotated__element # type: ignore

7411 self.c = ee.__class__.c.fget(self) # type: ignore

7412

7413 @util.ro_memoized_property

7414 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7415 """proxy the .c collection of the underlying FromClause.

7416

7417 Originally implemented in 2008 as a simple load of the .c collection

7418 when the annotated construct was created (see d3621ae961a), in modern

7419 SQLAlchemy versions this can be expensive for statements constructed

7420 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7421 it, which works just as well.

7422

7423 Two different use cases seem to require the collection either copied

7424 from the underlying one, or unique to this AnnotatedFromClause.

7425

7426 See test_selectable->test_annotated_corresponding_column

7427

7428 """

7429 ee = self._Annotated__element # type: ignore

7430 return ee.c # type: ignore