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

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

1200 FROM table1 JOIN table2 ON true

1201

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

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

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

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

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

1207

1208

1209 .. versionadded:: 1.4

1210

1211 """ # noqa: E501

1212

1213 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1216 when generating the columns clause of a SELECT statement.

1217

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

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

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

1221

1222 .. sourcecode:: pycon+sql

1223

1224 >>> from sqlalchemy import (

1225 ... table,

1226 ... column,

1227 ... select,

1228 ... true,

1229 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1230 ... )

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

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

1233 >>> print(

1234 ... select(table1, table2)

1235 ... .join(table2, true())

1236 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1237 ... )

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

1239 FROM table1 JOIN table2 ON true

1240

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

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

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

1244

1245 .. versionadded:: 1.4

1246

1247 """ # noqa: E501

1248

1249 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1250 """The default label style, refers to

1251 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1252

1253 .. versionadded:: 1.4

1254

1255 """

1256

1257 LABEL_STYLE_LEGACY_ORM = 3

1258

1259

1260(

1261 LABEL_STYLE_NONE,

1262 LABEL_STYLE_TABLENAME_PLUS_COL,

1263 LABEL_STYLE_DISAMBIGUATE_ONLY,

1264 _,

1265) = list(SelectLabelStyle)

1266

1267LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1268

1269

1270class Join(roles.DMLTableRole, FromClause[_KeyColCC_co]):

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

1272 :class:`_expression.FromClause`

1273 elements.

1274

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

1276 is the module-level

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

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

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

1280 :class:`_schema.Table`).

1281

1282 .. seealso::

1283

1284 :func:`_expression.join`

1285

1286 :meth:`_expression.FromClause.join`

1287

1288 """

1289

1290 __visit_name__ = "join"

1291

1292 _traverse_internals: _TraverseInternalsType = [

1293 ("left", InternalTraversal.dp_clauseelement),

1294 ("right", InternalTraversal.dp_clauseelement),

1295 ("onclause", InternalTraversal.dp_clauseelement),

1296 ("isouter", InternalTraversal.dp_boolean),

1297 ("full", InternalTraversal.dp_boolean),

1298 ]

1299

1300 _is_join = True

1301

1302 left: FromClause

1303 right: FromClause

1304 onclause: Optional[ColumnElement[bool]]

1305 isouter: bool

1306 full: bool

1307

1308 def __init__(

1309 self,

1310 left: _FromClauseArgument,

1311 right: _FromClauseArgument,

1312 onclause: Optional[_OnClauseArgument] = None,

1313 isouter: bool = False,

1314 full: bool = False,

1315 ):

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

1317

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

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

1320 :class:`_expression.FromClause` object.

1321

1322 """

1323

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

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

1326 # annotated objects. test_orm.py -> test_fetch_results

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

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

1329 # callcounts for a single compilation in that particular test

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

1331 # 29200 -> 30373

1332

1333 self.left = coercions.expect(

1334 roles.FromClauseRole,

1335 left,

1336 )

1337 self.right = coercions.expect(

1338 roles.FromClauseRole,

1339 right,

1340 ).self_group()

1341

1342 if onclause is None:

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

1344 else:

1345 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1346 # not merged yet

1347 self.onclause = coercions.expect(

1348 roles.OnClauseRole, onclause

1349 ).self_group(against=operators._asbool)

1350

1351 self.isouter = isouter

1352 self.full = full

1353

1354 @util.ro_non_memoized_property

1355 def description(self) -> str:

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

1357 self.left.description,

1358 id(self.left),

1359 self.right.description,

1360 id(self.right),

1361 )

1362

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

1364 return (

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

1366 # as well

1367 hash(fromclause) == hash(self)

1368 or self.left.is_derived_from(fromclause)

1369 or self.right.is_derived_from(fromclause)

1370 )

1371

1372 def self_group(

1373 self, against: Optional[OperatorType] = None

1374 ) -> FromGrouping:

1375 return FromGrouping(self)

1376

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

1378 def _populate_column_collection(

1379 self,

1380 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

1381 primary_key: ColumnSet,

1382 foreign_keys: Set[KeyedColumnElement[Any]],

1383 ) -> None:

1384 sqlutil = util.preloaded.sql_util

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

1386 c for c in self.right.c

1387 ]

1388

1389 primary_key.extend(

1390 sqlutil.reduce_columns(

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

1392 )

1393 )

1394 columns._populate_separate_keys(

1395 (col._tq_key_label, col) for col in _columns # type: ignore[misc]

1396 )

1397 foreign_keys.update(

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

1399 )

1400

1401 def _copy_internals(

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

1403 ) -> None:

1404 # see Select._copy_internals() for similar concept

1405

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

1407 # determine the new FROM clauses

1408 all_the_froms = set(

1409 itertools.chain(

1410 _from_objects(self.left),

1411 _from_objects(self.right),

1412 )

1413 )

1414

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

1416 # cache used by the clone function

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

1418

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

1420 # ColumnClause with parent table referring to those

1421 # replaced FromClause objects

1422 def replace(

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

1424 **kw: Any,

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

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

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

1428 return newelem

1429 return None

1430

1431 kw["replace"] = replace

1432

1433 # run normal _copy_internals. the clones for

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

1435 # cache

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

1437

1438 self._reset_memoizations()

1439

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

1441 super()._refresh_for_new_column(column)

1442 self.left._refresh_for_new_column(column)

1443 self.right._refresh_for_new_column(column)

1444

1445 def _match_primaries(

1446 self,

1447 left: FromClause,

1448 right: FromClause,

1449 ) -> ColumnElement[bool]:

1450 if isinstance(left, Join):

1451 left_right = left.right

1452 else:

1453 left_right = None

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

1455

1456 @classmethod

1457 def _join_condition(

1458 cls,

1459 a: FromClause,

1460 b: FromClause,

1461 *,

1462 a_subset: Optional[FromClause] = None,

1463 consider_as_foreign_keys: Optional[

1464 AbstractSet[ColumnClause[Any]]

1465 ] = None,

1466 ) -> ColumnElement[bool]:

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

1468

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

1470

1471 """

1472 constraints = cls._joincond_scan_left_right(

1473 a, a_subset, b, consider_as_foreign_keys

1474 )

1475

1476 if len(constraints) > 1:

1477 cls._joincond_trim_constraints(

1478 a, b, constraints, consider_as_foreign_keys

1479 )

1480

1481 if len(constraints) == 0:

1482 if isinstance(b, FromGrouping):

1483 hint = (

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

1485 "subquery using alias()?"

1486 )

1487 else:

1488 hint = ""

1489 raise exc.NoForeignKeysError(

1490 "Can't find any foreign key relationships "

1491 "between '%s' and '%s'.%s"

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

1493 )

1494

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

1496 if len(crit) == 1:

1497 return crit[0]

1498 else:

1499 return and_(*crit)

1500

1501 @classmethod

1502 def _can_join(

1503 cls,

1504 left: FromClause,

1505 right: FromClause,

1506 *,

1507 consider_as_foreign_keys: Optional[

1508 AbstractSet[ColumnClause[Any]]

1509 ] = None,

1510 ) -> bool:

1511 if isinstance(left, Join):

1512 left_right = left.right

1513 else:

1514 left_right = None

1515

1516 constraints = cls._joincond_scan_left_right(

1517 a=left,

1518 b=right,

1519 a_subset=left_right,

1520 consider_as_foreign_keys=consider_as_foreign_keys,

1521 )

1522

1523 return bool(constraints)

1524

1525 @classmethod

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

1527 def _joincond_scan_left_right(

1528 cls,

1529 a: FromClause,

1530 a_subset: Optional[FromClause],

1531 b: FromClause,

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

1533 ) -> collections.defaultdict[

1534 Optional[ForeignKeyConstraint],

1535 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1536 ]:

1537 sql_util = util.preloaded.sql_util

1538

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

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

1541

1542 constraints: collections.defaultdict[

1543 Optional[ForeignKeyConstraint],

1544 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1545 ] = collections.defaultdict(list)

1546

1547 for left in (a_subset, a):

1548 if left is None:

1549 continue

1550 for fk in sorted(

1551 b.foreign_keys,

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

1553 ):

1554 if (

1555 consider_as_foreign_keys is not None

1556 and fk.parent not in consider_as_foreign_keys

1557 ):

1558 continue

1559 try:

1560 col = fk.get_referent(left)

1561 except exc.NoReferenceError as nrte:

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

1563 if nrte.table_name in table_names:

1564 raise

1565 else:

1566 continue

1567

1568 if col is not None:

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

1570 if left is not b:

1571 for fk in sorted(

1572 left.foreign_keys,

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

1574 ):

1575 if (

1576 consider_as_foreign_keys is not None

1577 and fk.parent not in consider_as_foreign_keys

1578 ):

1579 continue

1580 try:

1581 col = fk.get_referent(b)

1582 except exc.NoReferenceError as nrte:

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

1584 if nrte.table_name in table_names:

1585 raise

1586 else:

1587 continue

1588

1589 if col is not None:

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

1591 if constraints:

1592 break

1593 return constraints

1594

1595 @classmethod

1596 def _joincond_trim_constraints(

1597 cls,

1598 a: FromClause,

1599 b: FromClause,

1600 constraints: Dict[Any, Any],

1601 consider_as_foreign_keys: Optional[Any],

1602 ) -> None:

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

1604 # to include just those FKCs that match exactly to

1605 # "consider_as_foreign_keys".

1606 if consider_as_foreign_keys:

1607 for const in list(constraints):

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

1609 consider_as_foreign_keys

1610 ):

1611 del constraints[const]

1612

1613 # if still multiple constraints, but

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

1615 if len(constraints) > 1:

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

1617 if len(dedupe) == 1:

1618 key = list(constraints)[0]

1619 constraints = {key: constraints[key]}

1620

1621 if len(constraints) != 1:

1622 raise exc.AmbiguousForeignKeysError(

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

1624 "tables have more than one foreign key "

1625 "constraint relationship between them. "

1626 "Please specify the 'onclause' of this "

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

1628 )

1629

1630 @overload

1631 def select(

1632 self: Join[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]

1633 ) -> Select[Unpack[_Ts]]: ...

1634 @overload

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

1636

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

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

1639 :class:`_expression.Join`.

1640

1641 E.g.::

1642

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

1644

1645 stmt = stmt.select()

1646

1647 The above will produce a SQL string resembling:

1648

1649 .. sourcecode:: sql

1650

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

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

1653

1654 """

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

1656

1657 @staticmethod

1658 def _flat_element_name(

1659 name: Optional[str], element: FromClause

1660 ) -> Optional[str]:

1661 if name and isinstance(element, NamedFromClause):

1662 if isinstance(element.name, _anonymous_label):

1663 # an anonymous name can't be embedded within a new name;

1664 # alias the element anonymously, #13583

1665 return None

1666 return f"{name}_{element.name}"

1667 else:

1668 return name

1669

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

1671 def _anonymous_fromclause(

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

1673 ) -> TODO_Any:

1674 sqlutil = util.preloaded.sql_util

1675 if flat:

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

1677 left_name = name # will recurse

1678 else:

1679 left_name = self._flat_element_name(name, self.left)

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

1681 right_name = name # will recurse

1682 else:

1683 right_name = self._flat_element_name(name, self.right)

1684 left_a, right_a = (

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

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

1687 )

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

1689 sqlutil.ClauseAdapter(right_a)

1690 )

1691

1692 return left_a.join(

1693 right_a,

1694 adapter.traverse(self.onclause),

1695 isouter=self.isouter,

1696 full=self.full,

1697 )

1698 else:

1699 return (

1700 self.select()

1701 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1702 .correlate(None)

1703 .alias(name)

1704 )

1705

1706 @util.ro_non_memoized_property

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

1708 return itertools.chain(

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

1710 )

1711

1712 @util.ro_non_memoized_property

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

1714 self_list: List[FromClause] = [self]

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

1716

1717 if TYPE_CHECKING:

1718

1719 def with_cols(self, type_: type[_TC_co]) -> Join[_TC_co]: ...

1720

1721

1722class NoInit:

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

1724 raise NotImplementedError(

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

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

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

1728 "selectable objects."

1729 % (

1730 self.__class__.__name__,

1731 self.__class__.__name__.lower(),

1732 self.__class__.__name__.lower(),

1733 )

1734 )

1735

1736

1737class LateralFromClause(NamedFromClause):

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

1739

1740

1741# FromClause ->

1742# AliasedReturnsRows

1743# -> Alias only for FromClause

1744# -> Subquery only for SelectBase

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

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

1747# w/ non-deprecated coercion

1748# -> TableSample -> only for FromClause

1749

1750

1751class AliasedReturnsRows(NoInit, NamedFromClause[_KeyColCC_co]):

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

1753 selectables."""

1754

1755 _is_from_container = True

1756

1757 _supports_derived_columns = False

1758

1759 element: ReturnsRows

1760

1761 _traverse_internals: _TraverseInternalsType = [

1762 ("element", InternalTraversal.dp_clauseelement),

1763 ("name", InternalTraversal.dp_anon_name),

1764 ]

1765

1766 @classmethod

1767 def _construct(

1768 cls,

1769 selectable: Any,

1770 *,

1771 name: Optional[str] = None,

1772 **kw: Any,

1773 ) -> Self:

1774 obj = cls.__new__(cls)

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

1776 return obj

1777

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

1779 self.element = coercions.expect(

1780 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1781 )

1782 self.element = selectable

1783 self._orig_name = name

1784 if name is None:

1785 if (

1786 isinstance(selectable, FromClause)

1787 and selectable.named_with_column

1788 ):

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

1790 if isinstance(name, _anonymous_label):

1791 name = None

1792 name = _anonymous_label.safe_construct(

1793 os.urandom(10).hex(), name or "anon"

1794 )

1795 self.name = name

1796

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

1798 super()._refresh_for_new_column(column)

1799 self.element._refresh_for_new_column(column)

1800

1801 def _populate_column_collection(

1802 self,

1803 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

1804 primary_key: ColumnSet,

1805 foreign_keys: Set[KeyedColumnElement[Any]],

1806 ) -> None:

1807 self.element._generate_fromclause_column_proxies(

1808 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1809 )

1810

1811 @util.ro_non_memoized_property

1812 def description(self) -> str:

1813 name = self.name

1814 if isinstance(name, _anonymous_label):

1815 return "anon_1"

1816

1817 return name

1818

1819 @util.ro_non_memoized_property

1820 def implicit_returning(self) -> bool:

1821 return self.element.implicit_returning # type: ignore[attr-defined, no-any-return] # noqa: E501

1822

1823 @property

1824 def original(self) -> ReturnsRows:

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

1826 return self.element

1827

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

1829 if fromclause in self._cloned_set:

1830 return True

1831 return self.element.is_derived_from(fromclause)

1832

1833 def _copy_internals(

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

1835 ) -> None:

1836 existing_element = self.element

1837

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

1839

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

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

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

1843 # performance.

1844 if existing_element is not self.element:

1845 self._reset_column_collection()

1846

1847 @property

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

1849 return [self]

1850

1851

1852class FromClauseAlias(AliasedReturnsRows[_KeyColCC_co]):

1853 element: FromClause[_KeyColCC_co]

1854

1855 @util.ro_non_memoized_property

1856 def description(self) -> str:

1857 name = self.name

1858 if isinstance(name, _anonymous_label):

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

1860

1861 return name

1862

1863

1864class Alias(roles.DMLTableRole, FromClauseAlias[_KeyColCC_co]):

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

1866

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

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

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

1870

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

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

1873 method available

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

1875

1876 .. seealso::

1877

1878 :meth:`_expression.FromClause.alias`

1879

1880 """

1881

1882 __visit_name__ = "alias"

1883

1884 inherit_cache = True

1885

1886 element: FromClause[_KeyColCC_co]

1887

1888 @classmethod

1889 def _factory(

1890 cls,

1891 selectable: FromClause[_KeyColCC_co],

1892 name: Optional[str] = None,

1893 flat: bool = False,

1894 ) -> NamedFromClause[_KeyColCC_co]:

1895 # mypy refuses to see the overload that has this returning

1896 # NamedFromClause[Any]. Pylance sees it just fine.

1897 return coercions.expect(roles.FromClauseRole, selectable).alias( # type: ignore[no-any-return] # noqa: E501

1898 name=name, flat=flat

1899 )

1900

1901

1902class TableValuedAlias(LateralFromClause, Alias):

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

1904

1905 This construct provides for a SQL function that returns columns

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

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

1908 method, e.g.:

1909

1910 .. sourcecode:: pycon+sql

1911

1912 >>> from sqlalchemy import select, func

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

1914 ... "value"

1915 ... )

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

1917 {printsql}SELECT anon_1.value

1918 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1919

1920 .. versionadded:: 1.4.0b2

1921

1922 .. seealso::

1923

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

1925

1926 """ # noqa: E501

1927

1928 __visit_name__ = "table_valued_alias"

1929

1930 _supports_derived_columns = True

1931 _render_derived = False

1932 _render_derived_w_types = False

1933 joins_implicitly = False

1934

1935 _traverse_internals: _TraverseInternalsType = [

1936 ("element", InternalTraversal.dp_clauseelement),

1937 ("name", InternalTraversal.dp_anon_name),

1938 ("_tableval_type", InternalTraversal.dp_type),

1939 ("_render_derived", InternalTraversal.dp_boolean),

1940 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1941 ]

1942

1943 def _init(

1944 self,

1945 selectable: Any,

1946 *,

1947 name: Optional[str] = None,

1948 table_value_type: Optional[TableValueType] = None,

1949 joins_implicitly: bool = False,

1950 ) -> None:

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

1952

1953 self.joins_implicitly = joins_implicitly

1954 self._tableval_type = (

1955 type_api.TABLEVALUE

1956 if table_value_type is None

1957 else table_value_type

1958 )

1959

1960 @HasMemoized.memoized_attribute

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

1962 """Return a column expression representing this

1963 :class:`_sql.TableValuedAlias`.

1964

1965 This accessor is used to implement the

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

1967 method for further details.

1968

1969 E.g.:

1970

1971 .. sourcecode:: pycon+sql

1972

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

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

1975

1976 .. seealso::

1977

1978 :meth:`_functions.FunctionElement.column_valued`

1979

1980 """

1981

1982 return TableValuedColumn(self, self._tableval_type)

1983

1984 def alias(

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

1986 ) -> TableValuedAlias:

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

1988

1989 This creates a distinct FROM object that will be distinguished

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

1991

1992 """

1993

1994 tva: TableValuedAlias = TableValuedAlias._construct(

1995 self,

1996 name=name,

1997 table_value_type=self._tableval_type,

1998 joins_implicitly=self.joins_implicitly,

1999 )

2000

2001 if self._render_derived:

2002 tva._render_derived = True

2003 tva._render_derived_w_types = self._render_derived_w_types

2004

2005 return tva

2006

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

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

2009 set, so that it renders as LATERAL.

2010

2011 .. seealso::

2012

2013 :func:`_expression.lateral`

2014

2015 """

2016 tva = self.alias(name=name)

2017 tva._is_lateral = True

2018 return tva

2019

2020 def render_derived(

2021 self,

2022 name: Optional[str] = None,

2023 with_types: bool = False,

2024 ) -> TableValuedAlias:

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

2026

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

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

2029

2030 .. sourcecode:: pycon+sql

2031

2032 >>> print(

2033 ... select(

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

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

2036 ... .render_derived()

2037 ... )

2038 ... )

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

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

2041

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

2043 the alias expression (this syntax currently applies to the

2044 PostgreSQL database):

2045

2046 .. sourcecode:: pycon+sql

2047

2048 >>> print(

2049 ... select(

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

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

2052 ... .render_derived(with_types=True)

2053 ... )

2054 ... )

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

2056 AS anon_1(a INTEGER, b VARCHAR)

2057

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

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

2060

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

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

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

2064

2065 """ # noqa: E501

2066

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

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

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

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

2071 # (just saw it happen on CI)

2072

2073 # construct against original to prevent memory growth

2074 # for repeated generations

2075 new_alias: TableValuedAlias = TableValuedAlias._construct(

2076 self.element,

2077 name=name,

2078 table_value_type=self._tableval_type,

2079 joins_implicitly=self.joins_implicitly,

2080 )

2081 new_alias._render_derived = True

2082 new_alias._render_derived_w_types = with_types

2083 return new_alias

2084

2085

2086class Lateral(FromClauseAlias, LateralFromClause):

2087 """Represent a LATERAL subquery.

2088

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

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

2091 method available

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

2093

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

2095 PostgreSQL versions provide support for this keyword.

2096

2097 .. seealso::

2098

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

2100

2101 """

2102

2103 __visit_name__ = "lateral"

2104 _is_lateral = True

2105

2106 inherit_cache = True

2107

2108 @classmethod

2109 def _factory(

2110 cls,

2111 selectable: Union[SelectBase, _FromClauseArgument],

2112 name: Optional[str] = None,

2113 ) -> LateralFromClause:

2114 return coercions.expect(

2115 roles.FromClauseRole, selectable, explicit_subquery=True

2116 ).lateral(name=name)

2117

2118

2119class TableSample(FromClauseAlias):

2120 """Represent a TABLESAMPLE clause.

2121

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

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

2124 method

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

2126

2127 .. seealso::

2128

2129 :func:`_expression.tablesample`

2130

2131 """

2132

2133 __visit_name__ = "tablesample"

2134

2135 _traverse_internals: _TraverseInternalsType = (

2136 AliasedReturnsRows._traverse_internals

2137 + [

2138 ("sampling", InternalTraversal.dp_clauseelement),

2139 ("seed", InternalTraversal.dp_clauseelement),

2140 ]

2141 )

2142

2143 @classmethod

2144 def _factory(

2145 cls,

2146 selectable: _FromClauseArgument,

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

2148 name: Optional[str] = None,

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

2150 ) -> TableSample:

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

2152 sampling, name=name, seed=seed

2153 )

2154

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

2156 def _init( # type: ignore[override]

2157 self,

2158 selectable: Any,

2159 *,

2160 name: Optional[str] = None,

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

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

2163 ) -> None:

2164 assert sampling is not None

2165 functions = util.preloaded.sql_functions

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

2167 sampling = functions.func.system(sampling)

2168

2169 self.sampling: Function[Any] = sampling

2170 self.seed = seed

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

2172

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

2174 return self.sampling

2175

2176

2177class CTE(

2178 roles.DMLTableRole,

2179 roles.IsCTERole,

2180 Generative,

2181 HasPrefixes,

2182 HasSuffixes,

2183 AliasedReturnsRows[_KeyColCC_co],

2184):

2185 """Represent a Common Table Expression.

2186

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

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

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

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

2191 :class:`_sql.Update` and

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

2193 usage details on CTEs.

2194

2195 .. seealso::

2196

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

2198

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

2200

2201 """

2202

2203 __visit_name__ = "cte"

2204

2205 _traverse_internals: _TraverseInternalsType = (

2206 AliasedReturnsRows._traverse_internals

2207 + [

2208 ("_cte_alias", InternalTraversal.dp_clauseelement),

2209 ("_restates", InternalTraversal.dp_clauseelement),

2210 ("recursive", InternalTraversal.dp_boolean),

2211 ("nesting", InternalTraversal.dp_boolean),

2212 ]

2213 + HasPrefixes._has_prefixes_traverse_internals

2214 + HasSuffixes._has_suffixes_traverse_internals

2215 )

2216

2217 element: HasCTE

2218

2219 @classmethod

2220 def _factory(

2221 cls,

2222 selectable: HasCTE,

2223 name: Optional[str] = None,

2224 recursive: bool = False,

2225 ) -> CTE:

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

2227 or Common Table Expression instance.

2228

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

2230

2231 """

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

2233 name=name, recursive=recursive

2234 )

2235

2236 def _init(

2237 self,

2238 selectable: HasCTE,

2239 *,

2240 name: Optional[str] = None,

2241 recursive: bool = False,

2242 nesting: bool = False,

2243 _cte_alias: Optional[CTE[_KeyColCC_co]] = None,

2244 _restates: Optional[CTE[_KeyColCC_co]] = None,

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

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

2247 ) -> None:

2248 self.recursive = recursive

2249 self.nesting = nesting

2250 self._cte_alias = _cte_alias

2251 # Keep recursivity reference with union/union_all

2252 self._restates = _restates

2253 if _prefixes:

2254 self._prefixes = _prefixes

2255 if _suffixes:

2256 self._suffixes = _suffixes

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

2258

2259 def _populate_column_collection(

2260 self,

2261 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

2262 primary_key: ColumnSet,

2263 foreign_keys: Set[KeyedColumnElement[Any]],

2264 ) -> None:

2265 if self._cte_alias is not None:

2266 self._cte_alias._generate_fromclause_column_proxies(

2267 self,

2268 columns,

2269 primary_key=primary_key,

2270 foreign_keys=foreign_keys,

2271 )

2272 else:

2273 self.element._generate_fromclause_column_proxies(

2274 self,

2275 columns,

2276 primary_key=primary_key,

2277 foreign_keys=foreign_keys,

2278 )

2279

2280 def alias(

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

2282 ) -> CTE[_KeyColCC_co]:

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

2284 :class:`_expression.CTE`.

2285

2286 This method is a CTE-specific specialization of the

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

2288

2289 .. seealso::

2290

2291 :ref:`tutorial_using_aliases`

2292

2293 :func:`_expression.alias`

2294

2295 """

2296 return CTE._construct(

2297 self.element,

2298 name=name,

2299 recursive=self.recursive,

2300 nesting=self.nesting,

2301 # an alias of an alias refers to the original CTE, #13583

2302 _cte_alias=(

2303 self._cte_alias if self._cte_alias is not None else self

2304 ),

2305 _prefixes=self._prefixes,

2306 _suffixes=self._suffixes,

2307 )

2308

2309 def union(

2310 self, *other: _SelectStatementForCompoundArgument[Any]

2311 ) -> CTE[_KeyColCC_co]:

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

2313 of the original CTE against the given selectables provided

2314 as positional arguments.

2315

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

2317 UNION.

2318

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

2320

2321 .. seealso::

2322

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

2324

2325 """

2326 assert is_select_statement(

2327 self.element

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

2329

2330 return CTE._construct(

2331 self.element.union(*other),

2332 name=self.name,

2333 recursive=self.recursive,

2334 nesting=self.nesting,

2335 _restates=self,

2336 _prefixes=self._prefixes,

2337 _suffixes=self._suffixes,

2338 )

2339

2340 def union_all(

2341 self, *other: _SelectStatementForCompoundArgument[Any]

2342 ) -> CTE[_KeyColCC_co]:

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

2344 of the original CTE against the given selectables provided

2345 as positional arguments.

2346

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

2348 UNION.

2349

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

2351

2352 .. seealso::

2353

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

2355

2356 """

2357

2358 assert is_select_statement(

2359 self.element

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

2361

2362 return CTE._construct(

2363 self.element.union_all(*other),

2364 name=self.name,

2365 recursive=self.recursive,

2366 nesting=self.nesting,

2367 _restates=self,

2368 _prefixes=self._prefixes,

2369 _suffixes=self._suffixes,

2370 )

2371

2372 def _get_reference_cte(self) -> CTE[_KeyColCC_co]:

2373 """

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

2375 Updated CTEs should still refer to the original CTE.

2376 This function returns this reference identifier.

2377 """

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

2379

2380 if TYPE_CHECKING:

2381

2382 def with_cols(self, type_: type[_TC_co]) -> CTE[_TC_co]: ...

2383

2384

2385class _CTEOpts(NamedTuple):

2386 nesting: bool

2387

2388

2389class _ColumnsPlusNames(NamedTuple):

2390 required_label_name: Optional[str]

2391 """

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

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

2394 """

2395

2396 proxy_key: Optional[str]

2397 """

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

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

2400 select.selected_columns collection

2401 """

2402

2403 fallback_label_name: Optional[str]

2404 """

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

2406 we have to render a label even though

2407 required_label_name was not given

2408 """

2409

2410 column: Union[ColumnElement[Any], AbstractTextClause]

2411 """

2412 the ColumnElement itself

2413 """

2414

2415 repeated: bool

2416 """

2417 True if this is a duplicate of a previous column

2418 in the list of columns

2419 """

2420

2421

2422class SelectsRows(ReturnsRows):

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

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

2425

2426 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2427

2428 def _generate_columns_plus_names(

2429 self,

2430 anon_for_dupe_key: bool,

2431 cols: Optional[_SelectIterable] = None,

2432 ) -> List[_ColumnsPlusNames]:

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

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

2435 on a :class:`.FromClause`.

2436

2437 This is distinct from the _column_naming_convention generator that's

2438 intended for population of the Select.selected_columns collection,

2439 different rules. the collection returned here calls upon the

2440 _column_naming_convention as well.

2441

2442 """

2443

2444 if cols is None:

2445 cols = self._all_selected_columns

2446

2447 key_naming_convention = SelectState._column_naming_convention(

2448 self._label_style

2449 )

2450

2451 names = {}

2452

2453 result: List[_ColumnsPlusNames] = []

2454 result_append = result.append

2455

2456 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2457 label_style_none = self._label_style is LABEL_STYLE_NONE

2458

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

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

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

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

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

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

2465 dedupe_hash = 1

2466

2467 for c in cols:

2468 repeated = False

2469

2470 if not c._render_label_in_columns_clause:

2471 effective_name = required_label_name = fallback_label_name = (

2472 None

2473 )

2474 elif label_style_none:

2475 if TYPE_CHECKING:

2476 assert is_column_element(c)

2477

2478 effective_name = required_label_name = None

2479 fallback_label_name = c._non_anon_label or c._anon_name_label

2480 else:

2481 if TYPE_CHECKING:

2482 assert is_column_element(c)

2483

2484 if table_qualified:

2485 required_label_name = effective_name = (

2486 fallback_label_name

2487 ) = c._tq_label

2488 else:

2489 effective_name = fallback_label_name = c._non_anon_label

2490 required_label_name = None

2491

2492 if effective_name is None:

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

2494 # not need _expression_label but it isn't

2495 # giving us a clue when to use anon_label instead

2496 expr_label = c._expression_label

2497 if expr_label is None:

2498 repeated = c._anon_name_label in names

2499 names[c._anon_name_label] = c

2500 effective_name = required_label_name = None

2501

2502 if repeated:

2503 # here, "required_label_name" is sent as

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

2505 if table_qualified:

2506 fallback_label_name = (

2507 c._dedupe_anon_tq_label_idx(dedupe_hash)

2508 )

2509 dedupe_hash += 1

2510 else:

2511 fallback_label_name = c._dedupe_anon_label_idx(

2512 dedupe_hash

2513 )

2514 dedupe_hash += 1

2515 else:

2516 fallback_label_name = c._anon_name_label

2517 else:

2518 required_label_name = effective_name = (

2519 fallback_label_name

2520 ) = expr_label

2521

2522 if effective_name is not None:

2523 if TYPE_CHECKING:

2524 assert is_column_element(c)

2525

2526 if effective_name in names:

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

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

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

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

2531 # different column under the same name. apply

2532 # disambiguating label

2533 if table_qualified:

2534 required_label_name = fallback_label_name = (

2535 c._anon_tq_label

2536 )

2537 else:

2538 required_label_name = fallback_label_name = (

2539 c._anon_name_label

2540 )

2541

2542 if anon_for_dupe_key and required_label_name in names:

2543 # here, c._anon_tq_label is definitely unique to

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

2545 # this should always be true.

2546 # this is also an infrequent codepath because

2547 # you need two levels of duplication to be here

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

2549

2550 # the column under the disambiguating label is

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

2552 # subsequent occurrences of the column so that the

2553 # original stays non-ambiguous

2554 if table_qualified:

2555 required_label_name = fallback_label_name = (

2556 c._dedupe_anon_tq_label_idx(dedupe_hash)

2557 )

2558 dedupe_hash += 1

2559 else:

2560 required_label_name = fallback_label_name = (

2561 c._dedupe_anon_label_idx(dedupe_hash)

2562 )

2563 dedupe_hash += 1

2564 repeated = True

2565 else:

2566 names[required_label_name] = c

2567 elif anon_for_dupe_key:

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

2569 # label so that the original stays non-ambiguous

2570 if table_qualified:

2571 required_label_name = fallback_label_name = (

2572 c._dedupe_anon_tq_label_idx(dedupe_hash)

2573 )

2574 dedupe_hash += 1

2575 else:

2576 required_label_name = fallback_label_name = (

2577 c._dedupe_anon_label_idx(dedupe_hash)

2578 )

2579 dedupe_hash += 1

2580 repeated = True

2581 else:

2582 names[effective_name] = c

2583

2584 result_append(

2585 _ColumnsPlusNames(

2586 required_label_name,

2587 key_naming_convention(c),

2588 fallback_label_name,

2589 c,

2590 repeated,

2591 )

2592 )

2593

2594 return result

2595

2596

2597class HasCTE(roles.HasCTERole, SelectsRows):

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

2599

2600 _has_ctes_traverse_internals: _TraverseInternalsType = [

2601 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2602 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2603 ]

2604

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

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

2607

2608 name_cte_columns: bool = False

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

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

2611

2612 .. versionadded:: 2.0.42

2613

2614 """

2615

2616 @_generative

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

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

2619

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

2621 the parent statement such that they will each be unconditionally

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

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

2624

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

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

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

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

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

2630 statement.

2631

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

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

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

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

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

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

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

2639 larger statement.

2640

2641 E.g.::

2642

2643 from sqlalchemy import table, column, select

2644

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

2646

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

2648

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

2650

2651 Would render:

2652

2653 .. sourcecode:: sql

2654

2655 WITH anon_1 AS (

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

2657 )

2658 SELECT t.c1, t.c2

2659 FROM t

2660

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

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

2663 statement.

2664

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

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

2667

2668 from sqlalchemy import table, column

2669 from sqlalchemy.dialects.postgresql import insert

2670

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

2672

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

2674

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

2676 update_statement = insert_stmt.on_conflict_do_update(

2677 index_elements=[t.c.c1],

2678 set_={

2679 "c1": insert_stmt.excluded.c1,

2680 "c2": insert_stmt.excluded.c2,

2681 },

2682 ).add_cte(delete_statement_cte)

2683

2684 print(update_statement)

2685

2686 The above statement renders as:

2687

2688 .. sourcecode:: sql

2689

2690 WITH deletions AS (

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

2692 )

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

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

2695

2696 .. versionadded:: 1.4.21

2697

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

2699

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

2701

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

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

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

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

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

2707 this statement when this flag is given.

2708

2709 .. versionadded:: 2.0

2710

2711 .. seealso::

2712

2713 :paramref:`.HasCTE.cte.nesting`

2714

2715

2716 """ # noqa: E501

2717 opt = _CTEOpts(nest_here)

2718 for cte in ctes:

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

2720 self._independent_ctes += (cte,)

2721 self._independent_ctes_opts += (opt,)

2722 return self

2723

2724 def cte(

2725 self,

2726 name: Optional[str] = None,

2727 recursive: bool = False,

2728 nesting: bool = False,

2729 ) -> CTE:

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

2731 or Common Table Expression instance.

2732

2733 Common table expressions are a SQL standard whereby SELECT

2734 statements can draw upon secondary statements specified along

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

2736 Special semantics regarding UNION can also be employed to

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

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

2739

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

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

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

2743 CTE rows.

2744

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

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

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

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

2749

2750 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2752 method may be

2753 used to establish these.

2754

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

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

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

2758 compile time.

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

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

2761 conjunction with UNION ALL in order to derive rows

2762 from those already selected.

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

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

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

2766 :paramref:`.HasCTE.add_cte.nest_here`

2767 parameter may also be used to more carefully

2768 control the exact placement of a particular CTE.

2769

2770 .. versionadded:: 1.4.24

2771

2772 .. seealso::

2773

2774 :meth:`.HasCTE.add_cte`

2775

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

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

2778 as well as additional examples.

2779

2780 Example 1, non recursive::

2781

2782 from sqlalchemy import (

2783 Table,

2784 Column,

2785 String,

2786 Integer,

2787 MetaData,

2788 select,

2789 func,

2790 )

2791

2792 metadata = MetaData()

2793

2794 orders = Table(

2795 "orders",

2796 metadata,

2797 Column("region", String),

2798 Column("amount", Integer),

2799 Column("product", String),

2800 Column("quantity", Integer),

2801 )

2802

2803 regional_sales = (

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

2805 .group_by(orders.c.region)

2806 .cte("regional_sales")

2807 )

2808

2809

2810 top_regions = (

2811 select(regional_sales.c.region)

2812 .where(

2813 regional_sales.c.total_sales

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

2815 )

2816 .cte("top_regions")

2817 )

2818

2819 statement = (

2820 select(

2821 orders.c.region,

2822 orders.c.product,

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

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

2825 )

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

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

2828 )

2829

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

2831

2832 Example 2, WITH RECURSIVE::

2833

2834 from sqlalchemy import (

2835 Table,

2836 Column,

2837 String,

2838 Integer,

2839 MetaData,

2840 select,

2841 func,

2842 )

2843

2844 metadata = MetaData()

2845

2846 parts = Table(

2847 "parts",

2848 metadata,

2849 Column("part", String),

2850 Column("sub_part", String),

2851 Column("quantity", Integer),

2852 )

2853

2854 included_parts = (

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

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

2857 .cte(recursive=True)

2858 )

2859

2860

2861 incl_alias = included_parts.alias()

2862 parts_alias = parts.alias()

2863 included_parts = included_parts.union_all(

2864 select(

2865 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2867 )

2868

2869 statement = select(

2870 included_parts.c.sub_part,

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

2872 ).group_by(included_parts.c.sub_part)

2873

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

2875

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

2877

2878 from datetime import date

2879 from sqlalchemy import (

2880 MetaData,

2881 Table,

2882 Column,

2883 Integer,

2884 Date,

2885 select,

2886 literal,

2887 and_,

2888 exists,

2889 )

2890

2891 metadata = MetaData()

2892

2893 visitors = Table(

2894 "visitors",

2895 metadata,

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

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

2898 Column("count", Integer),

2899 )

2900

2901 # add 5 visitors for the product_id == 1

2902 product_id = 1

2903 day = date.today()

2904 count = 5

2905

2906 update_cte = (

2907 visitors.update()

2908 .where(

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

2910 )

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

2912 .returning(literal(1))

2913 .cte("update_cte")

2914 )

2915

2916 upsert = visitors.insert().from_select(

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

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

2919 ~exists(update_cte.select())

2920 ),

2921 )

2922

2923 connection.execute(upsert)

2924

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

2926

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

2928

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

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

2931 "value_a", nesting=True

2932 )

2933

2934 # Nesting CTEs takes ascendency locally

2935 # over the CTEs at a higher level

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

2937

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

2939

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

2941 shown with inline parameters below as:

2942

2943 .. sourcecode:: sql

2944

2945 WITH

2946 value_a AS

2947 (SELECT 'root' AS n),

2948 value_b AS

2949 (WITH value_a AS

2950 (SELECT 'nesting' AS n)

2951 SELECT value_a.n AS n FROM value_a)

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

2953 FROM value_a, value_b

2954

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

2956 as follows (SQLAlchemy 2.0 and above)::

2957

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

2959

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

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

2962

2963 # Nesting CTEs takes ascendency locally

2964 # over the CTEs at a higher level

2965 value_b = (

2966 select(value_a_nested.c.n)

2967 .add_cte(value_a_nested, nest_here=True)

2968 .cte("value_b")

2969 )

2970

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

2972

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

2974

2975 edge = Table(

2976 "edge",

2977 metadata,

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

2979 Column("left", Integer),

2980 Column("right", Integer),

2981 )

2982

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

2984

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

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

2987 )

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

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

2990 )

2991

2992 subgraph_cte = root_node.union(left_edge, right_edge)

2993

2994 subgraph = select(subgraph_cte)

2995

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

2997

2998 .. sourcecode:: sql

2999

3000 WITH RECURSIVE nodes(node) AS (

3001 SELECT 1 AS node

3002 UNION

3003 SELECT edge."left" AS "left"

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

3005 UNION

3006 SELECT edge."right" AS "right"

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

3008 )

3009 SELECT nodes.node FROM nodes

3010

3011 .. seealso::

3012

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

3014 :meth:`_expression.HasCTE.cte`.

3015

3016 """ # noqa: E501

3017 return CTE._construct(

3018 self, name=name, recursive=recursive, nesting=nesting

3019 )

3020

3021

3022class Subquery(AliasedReturnsRows[_KeyColCC_co]):

3023 """Represent a subquery of a SELECT.

3024

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

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

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

3028 :class:`_expression.SelectBase` subclass

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

3030 :class:`_expression.CompoundSelect`, and

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

3032 it represents the

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

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

3035

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

3037 :class:`_expression.Alias`

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

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

3040 :class:`_expression.Alias` always

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

3042 :class:`.Subquery`

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

3044

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

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

3047 statement.

3048

3049 """

3050

3051 __visit_name__ = "subquery"

3052

3053 _is_subquery = True

3054

3055 inherit_cache = True

3056

3057 element: SelectBase

3058

3059 @classmethod

3060 def _factory(

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

3062 ) -> Subquery:

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

3064

3065 return coercions.expect(

3066 roles.SelectStatementRole, selectable

3067 ).subquery(name=name)

3068

3069 @util.deprecated(

3070 "1.4",

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

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

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

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

3075 ":func:`_expression.select` "

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

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

3078 )

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

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

3081

3082

3083class FromGrouping(GroupedElement, FromClause[_KeyColCC_co]):

3084 """Represent a grouping of a FROM clause"""

3085

3086 _traverse_internals: _TraverseInternalsType = [

3087 ("element", InternalTraversal.dp_clauseelement)

3088 ]

3089

3090 element: FromClause[_KeyColCC_co]

3091

3092 def __init__(self, element: FromClause[_KeyColCC_co]):

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

3094

3095 @util.ro_non_memoized_property

3096 def columns(self) -> _KeyColCC_co:

3097 return self.element.columns

3098

3099 @util.ro_non_memoized_property

3100 def c(self) -> _KeyColCC_co:

3101 return self.element.columns

3102

3103 @property

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

3105 return self.element.primary_key

3106

3107 @property

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

3109 return self.element.foreign_keys

3110

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

3112 return self.element.is_derived_from(fromclause)

3113

3114 def alias(

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

3116 ) -> NamedFromGrouping[_KeyColCC_co]:

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

3118

3119 def _anonymous_fromclause(

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

3121 ) -> FromGrouping:

3122 return FromGrouping(

3123 self.element._anonymous_fromclause(name=name, flat=flat)

3124 )

3125

3126 @util.ro_non_memoized_property

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

3128 return self.element._hide_froms

3129

3130 @util.ro_non_memoized_property

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

3132 return self.element._from_objects

3133

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

3135 return {"element": self.element}

3136

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

3138 self.element = state["element"]

3139

3140 if TYPE_CHECKING:

3141

3142 def self_group(

3143 self, against: Optional[OperatorType] = None

3144 ) -> Self: ...

3145

3146

3147class NamedFromGrouping(

3148 FromGrouping[_KeyColCC_co], NamedFromClause[_KeyColCC_co]

3149):

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

3151

3152 .. versionadded:: 2.0

3153

3154 """

3155

3156 inherit_cache = True

3157

3158 if TYPE_CHECKING:

3159

3160 def self_group(

3161 self, against: Optional[OperatorType] = None

3162 ) -> Self: ...

3163

3164

3165class TableClause(

3166 roles.DMLTableRole, Immutable, NamedFromClause[_ColClauseCC_co]

3167):

3168 """Represents a minimal "table" construct.

3169

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

3171 collection of columns, which are typically produced

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

3173

3174 from sqlalchemy import table, column

3175

3176 user = table(

3177 "user",

3178 column("id"),

3179 column("name"),

3180 column("description"),

3181 )

3182

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

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

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

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

3187

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

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

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

3191 It's useful

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

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

3194 is not on hand.

3195

3196 """

3197

3198 __visit_name__ = "table"

3199

3200 _traverse_internals: _TraverseInternalsType = [

3201 (

3202 "columns",

3203 InternalTraversal.dp_fromclause_canonical_column_collection,

3204 ),

3205 ("name", InternalTraversal.dp_string),

3206 ("schema", InternalTraversal.dp_string),

3207 ]

3208

3209 _is_table = True

3210

3211 fullname: str

3212

3213 implicit_returning = False

3214 """:class:`_expression.TableClause`

3215 doesn't support having a primary key or column

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

3217

3218 _columns: DedupeColumnCollection[ColumnClause[Any]]

3219

3220 @util.ro_memoized_property

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

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

3223 return None

3224

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

3226 super().__init__()

3227 self.name = name

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

3229 self.primary_key = ColumnSet() # type: ignore[misc]

3230 self.foreign_keys = set() # type: ignore[misc]

3231 for c in columns:

3232 self.append_column(c)

3233

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

3235 if schema is not None:

3236 self.schema = schema

3237 if self.schema is not None:

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

3239 else:

3240 self.fullname = self.name

3241 if kw:

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

3243

3244 if TYPE_CHECKING:

3245

3246 def with_cols(self, type_: type[_TC_co]) -> TableClause[_TC_co]: ...

3247

3248 def __str__(self) -> str:

3249 if self.schema is not None:

3250 return self.schema + "." + self.name

3251 else:

3252 return self.name

3253

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

3255 pass

3256

3257 @util.ro_memoized_property

3258 def description(self) -> str:

3259 return self.name

3260

3261 def _insert_col_impl(

3262 self,

3263 c: ColumnClause[Any],

3264 *,

3265 index: Optional[int] = None,

3266 ) -> None:

3267 existing = c.table

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

3269 raise exc.ArgumentError(

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

3271 % (c.key, existing)

3272 )

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

3274 c.table = self

3275

3276 def append_column(self, column: ColumnClause[Any]) -> None:

3277 """Append a :class:`.ColumnClause` to this :class:`.TableClause`."""

3278 self._insert_col_impl(column)

3279

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

3281 """Insert a :class:`.ColumnClause` to this :class:`.TableClause` at

3282 a specific position.

3283

3284 .. versionadded:: 2.1

3285

3286 """

3287 self._insert_col_impl(column, index=index)

3288

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

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

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

3292 :class:`_expression.TableClause`.

3293

3294 E.g.::

3295

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

3297

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

3299

3300 """

3301

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

3303

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

3305 def update(self) -> Update:

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

3307 :class:`_expression.TableClause`.

3308

3309 E.g.::

3310

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

3312

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

3314

3315 """

3316 return util.preloaded.sql_dml.Update(

3317 self,

3318 )

3319

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

3321 def delete(self) -> Delete:

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

3323 :class:`_expression.TableClause`.

3324

3325 E.g.::

3326

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

3328

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

3330

3331 """

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

3333

3334 @util.ro_non_memoized_property

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

3336 return [self]

3337

3338

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

3340

3341

3342class ForUpdateArg(ClauseElement):

3343 _traverse_internals: _TraverseInternalsType = [

3344 ("of", InternalTraversal.dp_clauseelement_list),

3345 ("nowait", InternalTraversal.dp_boolean),

3346 ("read", InternalTraversal.dp_boolean),

3347 ("skip_locked", InternalTraversal.dp_boolean),

3348 ("key_share", InternalTraversal.dp_boolean),

3349 ]

3350

3351 of: Optional[Sequence[ClauseElement]]

3352 nowait: bool

3353 read: bool

3354 skip_locked: bool

3355

3356 @classmethod

3357 def _from_argument(

3358 cls, with_for_update: ForUpdateParameter

3359 ) -> Optional[ForUpdateArg]:

3360 if isinstance(with_for_update, ForUpdateArg):

3361 return with_for_update

3362 elif with_for_update in (None, False):

3363 return None

3364 elif with_for_update is True:

3365 return ForUpdateArg()

3366 else:

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

3368

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

3370 return (

3371 isinstance(other, ForUpdateArg)

3372 and other.nowait == self.nowait

3373 and other.read == self.read

3374 and other.skip_locked == self.skip_locked

3375 and other.key_share == self.key_share

3376 and other.of is self.of

3377 )

3378

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

3380 return not self.__eq__(other)

3381

3382 def __hash__(self) -> int:

3383 return id(self)

3384

3385 def __init__(

3386 self,

3387 *,

3388 nowait: bool = False,

3389 read: bool = False,

3390 of: Optional[_ForUpdateOfArgument] = None,

3391 skip_locked: bool = False,

3392 key_share: bool = False,

3393 ):

3394 """Represents arguments specified to

3395 :meth:`_expression.Select.for_update`.

3396

3397 """

3398

3399 self.nowait = nowait

3400 self.read = read

3401 self.skip_locked = skip_locked

3402 self.key_share = key_share

3403 if of is not None:

3404 self.of = [

3405 coercions.expect(roles.ColumnsClauseRole, elem)

3406 for elem in util.to_list(of)

3407 ]

3408 else:

3409 self.of = None

3410

3411

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

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

3414 in a statement.

3415

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

3417 :func:`_expression.values` function.

3418

3419 .. versionadded:: 1.4

3420

3421 """

3422

3423 __visit_name__ = "values"

3424

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

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

3427

3428 _unnamed: bool

3429 _traverse_internals: _TraverseInternalsType = [

3430 ("_column_args", InternalTraversal.dp_clauseelement_list),

3431 ("_data", InternalTraversal.dp_dml_multi_values),

3432 ("name", InternalTraversal.dp_string),

3433 ("literal_binds", InternalTraversal.dp_boolean),

3434 ] + HasCTE._has_ctes_traverse_internals

3435

3436 name_cte_columns = True

3437

3438 def __init__(

3439 self,

3440 *columns: _OnlyColumnArgument[Any],

3441 name: Optional[str] = None,

3442 literal_binds: bool = False,

3443 ):

3444 super().__init__()

3445 self._column_args = tuple(

3446 coercions.expect(roles.LabeledColumnExprRole, col)

3447 for col in columns

3448 )

3449

3450 if name is None:

3451 self._unnamed = True

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

3453 else:

3454 self._unnamed = False

3455 self.name = name

3456 self.literal_binds = literal_binds

3457 self.named_with_column = not self._unnamed

3458

3459 @property

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

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

3462

3463 @util.ro_non_memoized_property

3464 def _all_selected_columns(self) -> _SelectIterable:

3465 return self._column_args

3466

3467 @_generative

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

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

3470 construct that is a copy of this

3471 one with the given name.

3472

3473 This method is a VALUES-specific specialization of the

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

3475

3476 .. seealso::

3477

3478 :ref:`tutorial_using_aliases`

3479

3480 :func:`_expression.alias`

3481

3482 """

3483 non_none_name: str

3484

3485 if name is None:

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

3487 else:

3488 non_none_name = name

3489

3490 self.name = non_none_name

3491 self.named_with_column = True

3492 self._unnamed = False

3493 return self

3494

3495 @_generative

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

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

3498 so that

3499 it renders as LATERAL.

3500

3501 .. seealso::

3502

3503 :func:`_expression.lateral`

3504

3505 """

3506 non_none_name: str

3507

3508 if name is None:

3509 non_none_name = self.name

3510 else:

3511 non_none_name = name

3512

3513 self._is_lateral = True

3514 self.name = non_none_name

3515 self._unnamed = False

3516 return self

3517

3518 @_generative

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

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

3521 adding the given data to the data list.

3522

3523 E.g.::

3524

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

3526

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

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

3529 constructor.

3530

3531 """

3532

3533 self._data += (values,)

3534 return self

3535

3536 def scalar_values(self) -> ScalarValues:

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

3538 COLUMN element in a statement.

3539

3540 .. versionadded:: 2.0.0b4

3541

3542 """

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

3544

3545 def _populate_column_collection(

3546 self,

3547 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3548 primary_key: ColumnSet,

3549 foreign_keys: Set[KeyedColumnElement[Any]],

3550 ) -> None:

3551 for c in self._column_args:

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

3553 _, c = c._make_proxy(

3554 self, primary_key=primary_key, foreign_keys=foreign_keys

3555 )

3556 else:

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

3558 # no memoizations of other FROM clauses.

3559 # see test_values.py -> test_auto_proxy_select_direct_col

3560 c._reset_memoizations()

3561 columns.add(c)

3562 c.table = self

3563

3564 @util.ro_non_memoized_property

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

3566 return [self]

3567

3568

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

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

3571 COLUMN element in a statement.

3572

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

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

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

3576 an ``IN`` or ``NOT IN`` condition.

3577

3578 .. versionadded:: 2.0.0b4

3579

3580 """

3581

3582 __visit_name__ = "scalar_values"

3583

3584 _traverse_internals: _TraverseInternalsType = [

3585 ("_column_args", InternalTraversal.dp_clauseelement_list),

3586 ("_data", InternalTraversal.dp_dml_multi_values),

3587 ("literal_binds", InternalTraversal.dp_boolean),

3588 ]

3589

3590 def __init__(

3591 self,

3592 columns: Sequence[NamedColumn[Any]],

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

3594 literal_binds: bool,

3595 ):

3596 super().__init__()

3597 self._column_args = columns

3598 self._data = data

3599 self.literal_binds = literal_binds

3600

3601 @property

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

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

3604

3605 def __clause_element__(self) -> ScalarValues:

3606 return self

3607

3608 if TYPE_CHECKING:

3609

3610 def self_group(

3611 self, against: Optional[OperatorType] = None

3612 ) -> Self: ...

3613

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

3615

3616

3617class SelectBase(

3618 roles.SelectStatementRole,

3619 roles.DMLSelectRole,

3620 roles.CompoundElementRole,

3621 roles.InElementRole,

3622 HasCTE,

3623 SupportsCloneAnnotations,

3624 Selectable,

3625):

3626 """Base class for SELECT statements.

3627

3628

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

3630 :class:`_expression.CompoundSelect` and

3631 :class:`_expression.TextualSelect`.

3632

3633

3634 """

3635

3636 _is_select_base = True

3637 is_select = True

3638

3639 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3640

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

3642 self._reset_memoizations()

3643

3644 @util.ro_non_memoized_property

3645 def selected_columns(

3646 self,

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

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

3649 representing the columns that

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

3651

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

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

3654 within this collection cannot be directly nested inside another SELECT

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

3656 necessary parenthesization required by SQL.

3657

3658 .. note::

3659

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

3661 include expressions established in the columns clause using the

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

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

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

3665 construct.

3666

3667 .. seealso::

3668

3669 :attr:`_sql.Select.selected_columns`

3670

3671 .. versionadded:: 1.4

3672

3673 """

3674 raise NotImplementedError()

3675

3676 def _generate_fromclause_column_proxies(

3677 self,

3678 subquery: FromClause,

3679 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3680 primary_key: ColumnSet,

3681 foreign_keys: Set[KeyedColumnElement[Any]],

3682 *,

3683 proxy_compound_columns: Optional[

3684 Iterable[Sequence[ColumnElement[Any]]]

3685 ] = None,

3686 ) -> None:

3687 raise NotImplementedError()

3688

3689 @util.ro_non_memoized_property

3690 def _all_selected_columns(self) -> _SelectIterable:

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

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

3693 constructs.

3694

3695 .. versionadded:: 1.4.12

3696

3697 .. seealso::

3698

3699 :attr:`_sql.SelectBase.exported_columns`

3700

3701 """

3702 raise NotImplementedError()

3703

3704 @property

3705 def exported_columns(

3706 self,

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

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

3709 that represents the "exported"

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

3711 :class:`_sql.TextClause` constructs.

3712

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

3714 object are synonymous

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

3716

3717 .. versionadded:: 1.4

3718

3719 .. seealso::

3720

3721 :attr:`_expression.Select.exported_columns`

3722

3723 :attr:`_expression.Selectable.exported_columns`

3724

3725 :attr:`_expression.FromClause.exported_columns`

3726

3727

3728 """

3729 return self.selected_columns._as_readonly()

3730

3731 def get_label_style(self) -> SelectLabelStyle:

3732 """

3733 Retrieve the current label style.

3734

3735 Implemented by subclasses.

3736

3737 """

3738 raise NotImplementedError()

3739

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

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

3742

3743 Implemented by subclasses.

3744

3745 """

3746

3747 raise NotImplementedError()

3748

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

3750 raise NotImplementedError()

3751

3752 @util.deprecated(

3753 "1.4",

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

3755 "method is deprecated and will be "

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

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

3758 )

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

3760 return self.scalar_subquery()

3761

3762 def exists(self) -> Exists:

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

3764 which can be used as a column expression.

3765

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

3767

3768 .. seealso::

3769

3770 :func:`_sql.exists`

3771

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

3773

3774 .. versionadded:: 1.4

3775

3776 """

3777 return Exists(self)

3778

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

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

3781 used as a column expression.

3782

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

3784

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

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

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

3788 an enclosing SELECT.

3789

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

3791 subquery that can be produced using the

3792 :meth:`_expression.SelectBase.subquery`

3793 method.

3794

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

3796 :meth:`_expression.SelectBase.scalar_subquery`.

3797

3798 .. seealso::

3799

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

3801

3802 """

3803 if self._label_style is not LABEL_STYLE_NONE:

3804 self = self.set_label_style(LABEL_STYLE_NONE)

3805

3806 return ScalarSelect(self)

3807

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

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

3810 subquery with a label.

3811

3812 .. seealso::

3813

3814 :meth:`_expression.SelectBase.scalar_subquery`.

3815

3816 """

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

3818

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

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

3821

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

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

3824

3825 .. seealso::

3826

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

3828

3829 """

3830 return Lateral._factory(self, name)

3831

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

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

3834

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

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

3837 SELECT statement.

3838

3839 Given a SELECT statement such as::

3840

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

3842

3843 The above statement might look like:

3844

3845 .. sourcecode:: sql

3846

3847 SELECT table.id, table.name FROM table

3848

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

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

3851 a named sub-element::

3852

3853 subq = stmt.subquery()

3854 new_stmt = select(subq)

3855

3856 The above renders as:

3857

3858 .. sourcecode:: sql

3859

3860 SELECT anon_1.id, anon_1.name

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

3862

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

3864 is equivalent to calling

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

3866 method on a FROM object; however,

3867 as a :class:`_expression.SelectBase`

3868 object is not directly FROM object,

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

3870 method provides clearer semantics.

3871

3872 .. versionadded:: 1.4

3873

3874 """

3875

3876 return Subquery._construct(

3877 self._ensure_disambiguated_names(), name=name

3878 )

3879

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

3881 def into(

3882 self,

3883 target: str,

3884 *,

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

3886 schema: Optional[str] = None,

3887 temporary: bool = False,

3888 if_not_exists: bool = False,

3889 ) -> CreateTableAs:

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

3891

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

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

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

3895

3896 Supported on all included backends, the construct emits

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

3898 emits a ``SELECT..INTO`` statement.

3899

3900 e.g.::

3901

3902 from sqlalchemy import select

3903

3904 # Create a new table from a SELECT

3905 stmt = (

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

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

3908 .into("active_users")

3909 )

3910

3911 with engine.begin() as conn:

3912 conn.execute(stmt)

3913

3914 # With optional flags

3915 stmt = (

3916 select(users.c.id)

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

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

3919 )

3920

3921 .. versionadded:: 2.1

3922

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

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

3925

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

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

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

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

3930 is created.

3931

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

3933

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

3935

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

3937

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

3939

3940 .. seealso::

3941

3942 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3943

3944 :class:`_schema.CreateTableAs`

3945

3946 """

3947 sql_ddl = util.preloaded.sql_ddl

3948

3949 return sql_ddl.CreateTableAs(

3950 self,

3951 target,

3952 metadata=metadata,

3953 schema=schema,

3954 temporary=temporary,

3955 if_not_exists=if_not_exists,

3956 )

3957

3958 def _ensure_disambiguated_names(self) -> Self:

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

3960 disambiguated in some way, if possible.

3961

3962 """

3963

3964 raise NotImplementedError()

3965

3966 def alias(

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

3968 ) -> Subquery:

3969 """Return a named subquery against this

3970 :class:`_expression.SelectBase`.

3971

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

3973 :class:`_expression.FromClause`),

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

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

3976 :class:`_expression.FromClause`.

3977

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

3979 method is now

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

3981

3982 """

3983 return self.subquery(name=name)

3984

3985

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

3987

3988

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

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

3991

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

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

3994 compound selects.

3995

3996 """

3997

3998 __visit_name__ = "select_statement_grouping"

3999 _traverse_internals: _TraverseInternalsType = [

4000 ("element", InternalTraversal.dp_clauseelement)

4001 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

4002

4003 _is_select_container = True

4004

4005 element: _SB

4006

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

4008 self.element = cast(

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

4010 )

4011

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

4013 new_element = self.element._ensure_disambiguated_names()

4014 if new_element is not self.element:

4015 return SelectStatementGrouping(new_element)

4016 else:

4017 return self

4018

4019 def get_label_style(self) -> SelectLabelStyle:

4020 return self.element.get_label_style()

4021

4022 def set_label_style(

4023 self, label_style: SelectLabelStyle

4024 ) -> SelectStatementGrouping[_SB]:

4025 return SelectStatementGrouping(

4026 self.element.set_label_style(label_style)

4027 )

4028

4029 @property

4030 def select_statement(self) -> _SB:

4031 return self.element

4032

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

4034 return self

4035

4036 if TYPE_CHECKING:

4037

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

4039

4040 # def _generate_columns_plus_names(

4041 # self, anon_for_dupe_key: bool

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

4043 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

4044

4045 def _generate_fromclause_column_proxies(

4046 self,

4047 subquery: FromClause,

4048 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

4049 primary_key: ColumnSet,

4050 foreign_keys: Set[KeyedColumnElement[Any]],

4051 *,

4052 proxy_compound_columns: Optional[

4053 Iterable[Sequence[ColumnElement[Any]]]

4054 ] = None,

4055 ) -> None:

4056 self.element._generate_fromclause_column_proxies(

4057 subquery,

4058 columns,

4059 proxy_compound_columns=proxy_compound_columns,

4060 primary_key=primary_key,

4061 foreign_keys=foreign_keys,

4062 )

4063

4064 @util.ro_non_memoized_property

4065 def _all_selected_columns(self) -> _SelectIterable:

4066 return self.element._all_selected_columns

4067

4068 @util.ro_non_memoized_property

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

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

4071 representing the columns that

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

4073 :class:`_sql.TextClause` constructs.

4074

4075 .. versionadded:: 1.4

4076

4077 .. seealso::

4078

4079 :attr:`_sql.Select.selected_columns`

4080

4081 """

4082 return self.element.selected_columns

4083

4084 @util.ro_non_memoized_property

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

4086 return self.element._from_objects

4087

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

4089 return self.element._scalar_type()

4090

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

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

4093 raise NotImplementedError

4094

4095

4096class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4098 added.

4099

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

4101 :class:`_expression.CompoundSelect`

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

4103 rendering can be controlled. Compare to

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

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

4106 and is also a SELECT construct,

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

4108 only wrapped as a subquery.

4109

4110 """

4111

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

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

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

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

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

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

4118 _for_update_arg: Optional[ForUpdateArg] = None

4119

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

4121 self._label_style = _label_style

4122

4123 @_generative

4124 def with_for_update(

4125 self,

4126 *,

4127 nowait: bool = False,

4128 read: bool = False,

4129 of: Optional[_ForUpdateOfArgument] = None,

4130 skip_locked: bool = False,

4131 key_share: bool = False,

4132 ) -> Self:

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

4134 :class:`_expression.GenerativeSelect`.

4135

4136 E.g.::

4137

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

4139

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

4141 render a statement like:

4142

4143 .. sourcecode:: sql

4144

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

4146

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

4148 would produce:

4149

4150 .. sourcecode:: sql

4151

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

4153

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

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

4156 provided which allow for common database-specific

4157 variants.

4158

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

4160 Database and PostgreSQL dialects.

4161

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

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

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

4165

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

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

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

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

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

4171 table or as a column depending on backend.

4172

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

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

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

4176

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

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

4179 on the PostgreSQL dialect.

4180

4181 """

4182 self._for_update_arg = ForUpdateArg(

4183 nowait=nowait,

4184 read=read,

4185 of=of,

4186 skip_locked=skip_locked,

4187 key_share=key_share,

4188 )

4189 return self

4190

4191 def get_label_style(self) -> SelectLabelStyle:

4192 """

4193 Retrieve the current label style.

4194

4195 .. versionadded:: 1.4

4196

4197 """

4198 return self._label_style

4199

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

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

4202

4203 There are three "label styles" available,

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

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

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

4207 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4208

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

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

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

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

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

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

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

4216 that the impact of this labeling is minimal.

4217

4218 The rationale for disambiguation is mostly so that all column

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

4220 collection when a subquery is created.

4221

4222 .. versionadded:: 1.4 - the

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

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

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

4226

4227 .. seealso::

4228

4229 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4230

4231 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4232

4233 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4234

4235 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4236

4237 """

4238 if self._label_style is not style:

4239 self = self._generate()

4240 self._label_style = style

4241 return self

4242

4243 @property

4244 def _group_by_clause(self) -> ClauseList:

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

4246 return ClauseList._construct_raw(

4247 operators.comma_op, self._group_by_clauses

4248 )

4249

4250 @property

4251 def _order_by_clause(self) -> ClauseList:

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

4253 return ClauseList._construct_raw(

4254 operators.comma_op, self._order_by_clauses

4255 )

4256

4257 def _offset_or_limit_clause(

4258 self,

4259 element: _LimitOffsetType,

4260 name: Optional[str] = None,

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

4262 ) -> ColumnElement[Any]:

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

4264

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

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

4267

4268 """

4269 return coercions.expect(

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

4271 )

4272

4273 @overload

4274 def _offset_or_limit_clause_asint(

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

4276 ) -> NoReturn: ...

4277

4278 @overload

4279 def _offset_or_limit_clause_asint(

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

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

4282

4283 def _offset_or_limit_clause_asint(

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

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

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

4287 integer.

4288

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

4290 parameter. Otherwise, a compilation error is raised.

4291

4292 """

4293 if clause is None:

4294 return None

4295 try:

4296 value = clause._limit_offset_value

4297 except AttributeError as err:

4298 raise exc.CompileError(

4299 "This SELECT structure does not use a simple "

4300 "integer value for %s" % attrname

4301 ) from err

4302 else:

4303 return util.asint(value)

4304

4305 @property

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

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

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

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

4310 isn't currently set to an integer.

4311

4312 """

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

4314

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

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

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

4318 """

4319 return isinstance(clause, _OffsetLimitParam)

4320

4321 @property

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

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

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

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

4326 offset isn't currently set to an integer.

4327

4328 """

4329 return self._offset_or_limit_clause_asint(

4330 self._offset_clause, "offset"

4331 )

4332

4333 @property

4334 def _has_row_limiting_clause(self) -> bool:

4335 return (

4336 self._limit_clause is not None

4337 or self._offset_clause is not None

4338 or self._fetch_clause is not None

4339 )

4340

4341 @_generative

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

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

4344 applied.

4345

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

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

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

4349 functionality.

4350

4351 .. note::

4352

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

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

4355

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

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

4358

4359 .. seealso::

4360

4361 :meth:`_sql.GenerativeSelect.fetch`

4362

4363 :meth:`_sql.GenerativeSelect.offset`

4364

4365 """

4366

4367 self._fetch_clause = self._fetch_clause_options = None

4368 self._limit_clause = self._offset_or_limit_clause(limit)

4369 return self

4370

4371 @_generative

4372 def fetch(

4373 self,

4374 count: _LimitOffsetType,

4375 with_ties: bool = False,

4376 percent: bool = False,

4377 **dialect_kw: Any,

4378 ) -> Self:

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

4380 applied.

4381

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

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

4384 select. This functionality is is currently implemented for Oracle

4385 Database, PostgreSQL, MSSQL.

4386

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

4388

4389 .. note::

4390

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

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

4393

4394 .. versionadded:: 1.4

4395

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

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

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

4399 Pass ``None`` to reset it.

4400

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

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

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

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

4405

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

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

4408

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

4410 may be accepted by dialects.

4411

4412 .. versionadded:: 2.0.41

4413

4414 .. seealso::

4415

4416 :meth:`_sql.GenerativeSelect.limit`

4417

4418 :meth:`_sql.GenerativeSelect.offset`

4419

4420 """

4421 self._validate_dialect_kwargs(dialect_kw)

4422 self._limit_clause = None

4423 if count is None:

4424 self._fetch_clause = self._fetch_clause_options = None

4425 else:

4426 self._fetch_clause = self._offset_or_limit_clause(count)

4427 self._fetch_clause_options = {

4428 "with_ties": with_ties,

4429 "percent": percent,

4430 }

4431 return self

4432

4433 @_generative

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

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

4436 applied.

4437

4438

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

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

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

4442 functionality.

4443

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

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

4446

4447 .. seealso::

4448

4449 :meth:`_sql.GenerativeSelect.limit`

4450

4451 :meth:`_sql.GenerativeSelect.fetch`

4452

4453 """

4454

4455 self._offset_clause = self._offset_or_limit_clause(offset)

4456 return self

4457

4458 @_generative

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

4460 def slice(

4461 self,

4462 start: int,

4463 stop: int,

4464 ) -> Self:

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

4466

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

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

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

4470 query.

4471

4472 For example, ::

4473

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

4475

4476 renders as

4477

4478 .. sourcecode:: sql

4479

4480 SELECT users.id AS users_id,

4481 users.name AS users_name

4482 FROM users ORDER BY users.id

4483 LIMIT ? OFFSET ?

4484 (2, 1)

4485

4486 .. note::

4487

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

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

4490

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

4492 method generalized from the ORM.

4493

4494 .. seealso::

4495

4496 :meth:`_sql.GenerativeSelect.limit`

4497

4498 :meth:`_sql.GenerativeSelect.offset`

4499

4500 :meth:`_sql.GenerativeSelect.fetch`

4501

4502 """

4503 sql_util = util.preloaded.sql_util

4504 self._fetch_clause = self._fetch_clause_options = None

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

4506 self._limit_clause, self._offset_clause, start, stop

4507 )

4508 return self

4509

4510 @_generative

4511 def order_by(

4512 self,

4513 __first: Union[

4514 Literal[None, _NoArg.NO_ARG],

4515 _ColumnExpressionOrStrLabelArgument[Any],

4516 roles.OrderByRole,

4517 ] = _NoArg.NO_ARG,

4518 /,

4519 *clauses: Union[

4520 _ColumnExpressionOrStrLabelArgument[Any], roles.OrderByRole

4521 ],

4522 ) -> Self:

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

4524 criteria applied.

4525

4526 e.g.::

4527

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

4529

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

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

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

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

4534

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

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

4537

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

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

4540

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

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

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

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

4545 label matching in ORDER BY and GROUP BY expressions.

4546

4547 .. seealso::

4548

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

4550

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

4552

4553 """

4554

4555 if not clauses and __first is None:

4556 self._order_by_clauses = ()

4557 elif __first is not _NoArg.NO_ARG:

4558 self._order_by_clauses += tuple(

4559 coercions.expect(

4560 roles.OrderByRole, clause, apply_propagate_attrs=self

4561 )

4562 for clause in (__first,) + clauses

4563 )

4564 return self

4565

4566 @_generative

4567 def group_by(

4568 self,

4569 __first: Union[

4570 Literal[None, _NoArg.NO_ARG],

4571 _ColumnExpressionOrStrLabelArgument[Any],

4572 ] = _NoArg.NO_ARG,

4573 /,

4574 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4575 ) -> Self:

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

4577 criterion applied.

4578

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

4580

4581 e.g.::

4582

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

4584

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

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

4587

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

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

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

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

4592 label matching in ORDER BY and GROUP BY expressions.

4593

4594 .. seealso::

4595

4596 :ref:`tutorial_group_by_w_aggregates` - in the

4597 :ref:`unified_tutorial`

4598

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

4600

4601 """ # noqa: E501

4602

4603 if not clauses and __first is None:

4604 self._group_by_clauses = ()

4605 elif __first is not _NoArg.NO_ARG:

4606 self._group_by_clauses += tuple(

4607 coercions.expect(

4608 roles.GroupByRole, clause, apply_propagate_attrs=self

4609 )

4610 for clause in (__first,) + clauses

4611 )

4612 return self

4613

4614

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

4616class CompoundSelectState(CompileState):

4617 @util.memoized_property

4618 def _label_resolve_dict(

4619 self,

4620 ) -> Tuple[

4621 Dict[str, ColumnElement[Any]],

4622 Dict[str, ColumnElement[Any]],

4623 Dict[str, ColumnElement[Any]],

4624 ]:

4625 # TODO: this is hacky and slow

4626 hacky_subquery = self.statement.subquery()

4627 hacky_subquery.named_with_column = False

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

4629 return d, d, d

4630

4631

4632class _CompoundSelectKeyword(Enum):

4633 UNION = "UNION"

4634 UNION_ALL = "UNION ALL"

4635 EXCEPT = "EXCEPT"

4636 EXCEPT_ALL = "EXCEPT ALL"

4637 INTERSECT = "INTERSECT"

4638 INTERSECT_ALL = "INTERSECT ALL"

4639

4640

4641class CompoundSelect(

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

4643):

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

4645 SELECT-based set operations.

4646

4647

4648 .. seealso::

4649

4650 :func:`_expression.union`

4651

4652 :func:`_expression.union_all`

4653

4654 :func:`_expression.intersect`

4655

4656 :func:`_expression.intersect_all`

4657

4658 :func:`_expression.except`

4659

4660 :func:`_expression.except_all`

4661

4662 """

4663

4664 __visit_name__ = "compound_select"

4665

4666 _traverse_internals: _TraverseInternalsType = (

4667 [

4668 ("selects", InternalTraversal.dp_clauseelement_list),

4669 ("_limit_clause", InternalTraversal.dp_clauseelement),

4670 ("_offset_clause", InternalTraversal.dp_clauseelement),

4671 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4672 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4673 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4674 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4675 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4676 ("keyword", InternalTraversal.dp_string),

4677 ]

4678 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4679 + HasCTE._has_ctes_traverse_internals

4680 + DialectKWArgs._dialect_kwargs_traverse_internals

4681 + ExecutableStatement._executable_traverse_internals

4682 )

4683

4684 selects: List[SelectBase]

4685

4686 _is_from_container = True

4687 _auto_correlate = False

4688

4689 def __init__(

4690 self,

4691 keyword: _CompoundSelectKeyword,

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

4693 ):

4694 self.keyword = keyword

4695 self.selects = [

4696 coercions.expect(

4697 roles.CompoundElementRole, s, apply_propagate_attrs=self

4698 ).self_group(against=self)

4699 for s in selects

4700 ]

4701

4702 GenerativeSelect.__init__(self)

4703

4704 @classmethod

4705 def _create_union(

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

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

4708 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4709

4710 @classmethod

4711 def _create_union_all(

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

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

4714 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4715

4716 @classmethod

4717 def _create_except(

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

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

4720 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4721

4722 @classmethod

4723 def _create_except_all(

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

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

4726 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4727

4728 @classmethod

4729 def _create_intersect(

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

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

4732 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4733

4734 @classmethod

4735 def _create_intersect_all(

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

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

4738 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4739

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

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

4742

4743 def self_group(

4744 self, against: Optional[OperatorType] = None

4745 ) -> GroupedElement:

4746 return SelectStatementGrouping(self)

4747

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

4749 for s in self.selects:

4750 if s.is_derived_from(fromclause):

4751 return True

4752 return False

4753

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

4755 if self._label_style is not style:

4756 self = self._generate()

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

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

4759

4760 return self

4761

4762 def _ensure_disambiguated_names(self) -> Self:

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

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

4765 self = self._generate()

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

4767

4768 return self

4769

4770 def _generate_fromclause_column_proxies(

4771 self,

4772 subquery: FromClause,

4773 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

4774 primary_key: ColumnSet,

4775 foreign_keys: Set[KeyedColumnElement[Any]],

4776 *,

4777 proxy_compound_columns: Optional[

4778 Iterable[Sequence[ColumnElement[Any]]]

4779 ] = None,

4780 ) -> None:

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

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

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

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

4785 # generate below.

4786 select_0 = self.selects[0]

4787

4788 if self._label_style is not LABEL_STYLE_DEFAULT:

4789 select_0 = select_0.set_label_style(self._label_style)

4790

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

4792 # derived columns place a 'weight' annotation corresponding

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

4794 # that the corresponding_column() operation can resolve

4795 # conflicts

4796 extra_col_iterator = zip(

4797 *[

4798 [

4799 c._annotate(dd)

4800 for c in stmt._all_selected_columns

4801 if is_column_element(c)

4802 ]

4803 for dd, stmt in [

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

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

4806 ]

4807 ]

4808 )

4809

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

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

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

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

4814 # compound

4815 select_0._generate_fromclause_column_proxies(

4816 subquery,

4817 columns,

4818 proxy_compound_columns=extra_col_iterator,

4819 primary_key=primary_key,

4820 foreign_keys=foreign_keys,

4821 )

4822

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

4824 super()._refresh_for_new_column(column)

4825 for select in self.selects:

4826 select._refresh_for_new_column(column)

4827

4828 @util.ro_non_memoized_property

4829 def _all_selected_columns(self) -> _SelectIterable:

4830 return self.selects[0]._all_selected_columns

4831

4832 @util.ro_non_memoized_property

4833 def selected_columns(

4834 self,

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

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

4837 representing the columns that

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

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

4840

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

4842 :attr:`_expression.CompoundSelect.selected_columns`

4843 attribute returns the selected

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

4845 statements within the set operation.

4846

4847 .. seealso::

4848

4849 :attr:`_sql.Select.selected_columns`

4850

4851 .. versionadded:: 1.4

4852

4853 """

4854 return self.selects[0].selected_columns

4855

4856

4857# backwards compat

4858for elem in _CompoundSelectKeyword:

4859 setattr(CompoundSelect, elem.name, elem)

4860

4861

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

4863class SelectState(util.MemoizedSlots, CompileState):

4864 __slots__ = (

4865 "from_clauses",

4866 "froms",

4867 "columns_plus_names",

4868 "_label_resolve_dict",

4869 )

4870

4871 if TYPE_CHECKING:

4872 default_select_compile_options: CacheableOptions

4873 else:

4874

4875 class default_select_compile_options(CacheableOptions):

4876 _cache_key_traversal = []

4877

4878 if TYPE_CHECKING:

4879

4880 @classmethod

4881 def get_plugin_class(

4882 cls, statement: Executable

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

4884

4885 def __init__(

4886 self,

4887 statement: Select[Unpack[TupleAny]],

4888 compiler: SQLCompiler,

4889 **kw: Any,

4890 ):

4891 self.statement = statement

4892 self.from_clauses = statement._from_obj

4893

4894 for memoized_entities in statement._memoized_select_entities:

4895 self._setup_joins(

4896 memoized_entities._setup_joins, memoized_entities._raw_columns

4897 )

4898

4899 if statement._setup_joins:

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

4901

4902 self.froms = self._get_froms(statement)

4903

4904 self.columns_plus_names = statement._generate_columns_plus_names(True)

4905

4906 @classmethod

4907 def _plugin_not_implemented(cls) -> NoReturn:

4908 raise NotImplementedError(

4909 "The default SELECT construct without plugins does not "

4910 "implement this method."

4911 )

4912

4913 @classmethod

4914 def get_column_descriptions(

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

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

4917 return [

4918 {

4919 "name": name,

4920 "type": element.type,

4921 "expr": element,

4922 }

4923 for _, name, _, element, _ in (

4924 statement._generate_columns_plus_names(False)

4925 )

4926 ]

4927

4928 @classmethod

4929 def from_statement(

4930 cls,

4931 statement: Select[Unpack[TupleAny]],

4932 from_statement: roles.ReturnsRowsRole,

4933 ) -> ExecutableReturnsRows:

4934 cls._plugin_not_implemented()

4935

4936 @classmethod

4937 def get_columns_clause_froms(

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

4939 ) -> List[FromClause]:

4940 return cls._normalize_froms(

4941 itertools.chain.from_iterable(

4942 element._from_objects for element in statement._raw_columns

4943 )

4944 )

4945

4946 @classmethod

4947 def _column_naming_convention(

4948 cls, label_style: SelectLabelStyle

4949 ) -> _LabelConventionCallable:

4950 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4951

4952 dedupe = label_style is not LABEL_STYLE_NONE

4953

4954 pa = prefix_anon_map()

4955 names = set()

4956

4957 def go(

4958 c: Union[ColumnElement[Any], AbstractTextClause],

4959 col_name: Optional[str] = None,

4960 ) -> Optional[str]:

4961 if is_text_clause(c):

4962 return None

4963 elif TYPE_CHECKING:

4964 assert is_column_element(c)

4965

4966 if not dedupe:

4967 name = c._proxy_key

4968 if name is None:

4969 name = "_no_label"

4970 return name

4971

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

4973

4974 if name is None:

4975 name = "_no_label"

4976 if name in names:

4977 return c._anon_label(name) % pa

4978 else:

4979 names.add(name)

4980 return name

4981

4982 elif name in names:

4983 return (

4984 c._anon_tq_key_label % pa

4985 if table_qualified

4986 else c._anon_key_label % pa

4987 )

4988 else:

4989 names.add(name)

4990 return name

4991

4992 return go

4993

4994 def _get_froms(

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

4996 ) -> List[FromClause]:

4997 ambiguous_table_name_map: _AmbiguousTableNameMap

4998 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4999

5000 return self._normalize_froms(

5001 itertools.chain(

5002 self.from_clauses,

5003 itertools.chain.from_iterable(

5004 [

5005 element._from_objects

5006 for element in statement._raw_columns

5007 ]

5008 ),

5009 itertools.chain.from_iterable(

5010 [

5011 element._from_objects

5012 for element in statement._where_criteria

5013 ]

5014 ),

5015 ),

5016 check_statement=statement,

5017 ambiguous_table_name_map=ambiguous_table_name_map,

5018 )

5019

5020 @classmethod

5021 def _normalize_froms(

5022 cls,

5023 iterable_of_froms: Iterable[FromClause],

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

5025 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

5026 ) -> List[FromClause]:

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

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

5029

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

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

5032 etc.

5033

5034 """

5035 seen: Set[FromClause] = set()

5036 froms: List[FromClause] = []

5037

5038 for item in iterable_of_froms:

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

5040 raise exc.InvalidRequestError(

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

5042 )

5043

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

5045 froms.append(item)

5046 seen.update(item._cloned_set)

5047

5048 if froms:

5049 toremove = set(

5050 itertools.chain.from_iterable(

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

5052 )

5053 )

5054 if toremove:

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

5056 # using a list to maintain ordering

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

5058

5059 if ambiguous_table_name_map is not None:

5060 ambiguous_table_name_map.update(

5061 (

5062 fr.name,

5063 _anonymous_label.safe_construct(

5064 hash(fr.name), fr.name

5065 ),

5066 )

5067 for item in froms

5068 for fr in item._from_objects

5069 if is_table(fr)

5070 and fr.schema

5071 and fr.name not in ambiguous_table_name_map

5072 )

5073

5074 return froms

5075

5076 def _get_display_froms(

5077 self,

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

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

5080 ) -> List[FromClause]:

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

5082

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

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

5085 may want to leave those absent if it is automatically

5086 correlating.

5087

5088 """

5089

5090 froms = self.froms

5091

5092 if self.statement._correlate:

5093 to_correlate = self.statement._correlate

5094 if to_correlate:

5095 to_remove = _cloned_intersection(

5096 _cloned_intersection(

5097 froms, explicit_correlate_froms or ()

5098 ),

5099 to_correlate,

5100 )

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

5102

5103 if self.statement._correlate_except is not None:

5104 to_remove = _cloned_difference(

5105 _cloned_intersection(froms, explicit_correlate_froms or ()),

5106 self.statement._correlate_except,

5107 )

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

5109

5110 if (

5111 self.statement._auto_correlate

5112 and implicit_correlate_froms

5113 and len(froms) > 1

5114 ):

5115 to_remove = _cloned_intersection(froms, implicit_correlate_froms)

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

5117

5118 if not len(froms):

5119 raise exc.InvalidRequestError(

5120 "Select statement '%r"

5121 "' returned no FROM clauses "

5122 "due to auto-correlation; "

5123 "specify correlate(<tables>) "

5124 "to control correlation "

5125 "manually." % self.statement

5126 )

5127

5128 return froms

5129

5130 def _memoized_attr__label_resolve_dict(

5131 self,

5132 ) -> Tuple[

5133 Dict[str, ColumnElement[Any]],

5134 Dict[str, ColumnElement[Any]],

5135 Dict[str, ColumnElement[Any]],

5136 ]:

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

5138 c._tq_label or c.key: c

5139 for c in self.statement._all_selected_columns

5140 if c._allow_label_resolve

5141 }

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

5143 c.key: c # type: ignore[misc]

5144 for c in _select_iterables(self.froms)

5145 if c._allow_label_resolve

5146 }

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

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

5149 with_cols.setdefault(key, value)

5150

5151 return with_cols, only_froms, only_cols

5152

5153 @classmethod

5154 def _get_filter_by_entities(

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

5156 ) -> Collection[

5157 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5158 ]:

5159 """Return all entities to search for filter_by() attributes.

5160

5161 This includes:

5162

5163 * All joined entities from _setup_joins

5164 * Memoized entities from previous operations (e.g.,

5165 before with_only_columns)

5166 * Explicit FROM objects from _from_obj

5167 * Entities inferred from _raw_columns

5168

5169 .. versionadded:: 2.1

5170

5171 """

5172 entities: set[

5173 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5174 ]

5175

5176 entities = set(

5177 join_element[0] for join_element in statement._setup_joins

5178 )

5179

5180 for memoized in statement._memoized_select_entities:

5181 entities.update(

5182 join_element[0] for join_element in memoized._setup_joins

5183 )

5184

5185 entities.update(statement._from_obj)

5186

5187 for col in statement._raw_columns:

5188 entities.update(col._from_objects)

5189

5190 return entities

5191

5192 @classmethod

5193 def all_selected_columns(

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

5195 ) -> _SelectIterable:

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

5197

5198 def _setup_joins(

5199 self,

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

5201 raw_columns: List[_ColumnsClauseElement],

5202 ) -> None:

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

5204 if TYPE_CHECKING:

5205 if onclause is not None:

5206 assert isinstance(onclause, ColumnElement)

5207

5208 explicit_left = left

5209 isouter = flags["isouter"]

5210 full = flags["full"]

5211

5212 if left is None:

5213 (

5214 left,

5215 replace_from_obj_index,

5216 ) = self._join_determine_implicit_left_side(

5217 raw_columns, left, right, onclause

5218 )

5219 else:

5220 replace_from_obj_index = self._join_place_explicit_left_side(

5221 left

5222 )

5223

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

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

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

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

5228 # used instead.

5229 if TYPE_CHECKING:

5230 assert isinstance(right, FromClause)

5231 if onclause is not None:

5232 assert isinstance(onclause, ColumnElement)

5233

5234 if replace_from_obj_index is not None:

5235 # splice into an existing element in the

5236 # self._from_obj list

5237 left_clause = self.from_clauses[replace_from_obj_index]

5238

5239 if explicit_left is not None and onclause is None:

5240 onclause = Join._join_condition(explicit_left, right)

5241

5242 self.from_clauses = (

5243 self.from_clauses[:replace_from_obj_index]

5244 + (

5245 Join(

5246 left_clause,

5247 right,

5248 onclause,

5249 isouter=isouter,

5250 full=full,

5251 ),

5252 )

5253 + self.from_clauses[replace_from_obj_index + 1 :]

5254 )

5255 else:

5256 assert left is not None

5257 self.from_clauses = self.from_clauses + (

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

5259 )

5260

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

5262 def _join_determine_implicit_left_side(

5263 self,

5264 raw_columns: List[_ColumnsClauseElement],

5265 left: Optional[FromClause],

5266 right: _JoinTargetElement,

5267 onclause: Optional[ColumnElement[Any]],

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

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

5270 determine if an existing FROM or entity in this query

5271 can serve as the left hand side.

5272

5273 """

5274

5275 sql_util = util.preloaded.sql_util

5276

5277 replace_from_obj_index: Optional[int] = None

5278

5279 from_clauses = self.from_clauses

5280

5281 if from_clauses:

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

5283 from_clauses, right, onclause

5284 )

5285

5286 if len(indexes) == 1:

5287 replace_from_obj_index = indexes[0]

5288 left = from_clauses[replace_from_obj_index]

5289 else:

5290 potential = {}

5291 statement = self.statement

5292

5293 for from_clause in itertools.chain(

5294 itertools.chain.from_iterable(

5295 [element._from_objects for element in raw_columns]

5296 ),

5297 itertools.chain.from_iterable(

5298 [

5299 element._from_objects

5300 for element in statement._where_criteria

5301 ]

5302 ),

5303 ):

5304 potential[from_clause] = ()

5305

5306 all_clauses = list(potential.keys())

5307 indexes = sql_util.find_left_clause_to_join_from(

5308 all_clauses, right, onclause

5309 )

5310

5311 if len(indexes) == 1:

5312 left = all_clauses[indexes[0]]

5313

5314 if len(indexes) > 1:

5315 raise exc.InvalidRequestError(

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

5317 "from, there are multiple FROMS which can "

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

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

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

5321 "help resolve the ambiguity."

5322 )

5323 elif not indexes:

5324 raise exc.InvalidRequestError(

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

5326 "Please use the .select_from() "

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

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

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

5330 )

5331 return left, replace_from_obj_index

5332

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

5334 def _join_place_explicit_left_side(

5335 self, left: FromClause

5336 ) -> Optional[int]:

5337 replace_from_obj_index: Optional[int] = None

5338

5339 sql_util = util.preloaded.sql_util

5340

5341 from_clauses = list(self.statement._iterate_from_elements())

5342

5343 if from_clauses:

5344 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5345 self.from_clauses, left

5346 )

5347 else:

5348 indexes = []

5349

5350 if len(indexes) > 1:

5351 raise exc.InvalidRequestError(

5352 "Can't identify which entity in which to assign the "

5353 "left side of this join. Please use a more specific "

5354 "ON clause."

5355 )

5356

5357 # have an index, means the left side is already present in

5358 # an existing FROM in the self._from_obj tuple

5359 if indexes:

5360 replace_from_obj_index = indexes[0]

5361

5362 # no index, means we need to add a new element to the

5363 # self._from_obj tuple

5364

5365 return replace_from_obj_index

5366

5367

5368class _SelectFromElements:

5369 __slots__ = ()

5370

5371 _raw_columns: List[_ColumnsClauseElement]

5372 _where_criteria: Tuple[ColumnElement[Any], ...]

5373 _from_obj: Tuple[FromClause, ...]

5374

5375 def _iterate_from_elements(self) -> Iterator[FromClause]:

5376 # note this does not include elements

5377 # in _setup_joins

5378

5379 seen = set()

5380 for element in self._raw_columns:

5381 for fr in element._from_objects:

5382 if fr in seen:

5383 continue

5384 seen.add(fr)

5385 yield fr

5386 for element in self._where_criteria:

5387 for fr in element._from_objects:

5388 if fr in seen:

5389 continue

5390 seen.add(fr)

5391 yield fr

5392 for element in self._from_obj:

5393 if element in seen:

5394 continue

5395 seen.add(element)

5396 yield element

5397

5398

5399class _MemoizedSelectEntities(

5400 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5401):

5402 """represents partial state from a Select object, for the case

5403 where Select.columns() has redefined the set of columns/entities the

5404 statement will be SELECTing from. This object represents

5405 the entities from the SELECT before that transformation was applied,

5406 so that transformations that were made in terms of the SELECT at that

5407 time, such as join() as well as options(), can access the correct context.

5408

5409 In previous SQLAlchemy versions, this wasn't needed because these

5410 constructs calculated everything up front, like when you called join()

5411 or options(), it did everything to figure out how that would translate

5412 into specific SQL constructs that would be ready to send directly to the

5413 SQL compiler when needed. But as of

5414 1.4, all of that stuff is done in the compilation phase, during the

5415 "compile state" portion of the process, so that the work can all be

5416 cached. So it needs to be able to resolve joins/options2 based on what

5417 the list of entities was when those methods were called.

5418

5419

5420 """

5421

5422 __visit_name__ = "memoized_select_entities"

5423

5424 _traverse_internals: _TraverseInternalsType = [

5425 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5426 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5427 ("_with_options", InternalTraversal.dp_executable_options),

5428 ]

5429

5430 _is_clone_of: Optional[ClauseElement]

5431 _raw_columns: List[_ColumnsClauseElement]

5432 _setup_joins: Tuple[_SetupJoinsElement, ...]

5433 _with_options: Tuple[ExecutableOption, ...]

5434

5435 _annotations = util.EMPTY_DICT

5436

5437 def _clone(self, **kw: Any) -> Self:

5438 c = self.__class__.__new__(self.__class__)

5439 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5440

5441 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5442 return c

5443

5444 @classmethod

5445 def _generate_for_statement(

5446 cls, select_stmt: Select[Unpack[TupleAny]]

5447 ) -> None:

5448 if select_stmt._setup_joins or select_stmt._with_options:

5449 self = _MemoizedSelectEntities()

5450 self._raw_columns = select_stmt._raw_columns

5451 self._setup_joins = select_stmt._setup_joins

5452 self._with_options = select_stmt._with_options

5453

5454 select_stmt._memoized_select_entities += (self,)

5455 select_stmt._raw_columns = []

5456 select_stmt._setup_joins = select_stmt._with_options = ()

5457

5458

5459class Select(

5460 HasPrefixes,

5461 HasSuffixes,

5462 HasHints,

5463 HasCompileState,

5464 HasSyntaxExtensions[

5465 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5466 ],

5467 _SelectFromElements,

5468 GenerativeSelect,

5469 TypedReturnsRows[Unpack[_Ts]],

5470):

5471 """Represents a ``SELECT`` statement.

5472

5473 The :class:`_sql.Select` object is normally constructed using the

5474 :func:`_sql.select` function. See that function for details.

5475

5476 Available extension points:

5477

5478 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5479 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5480 keyword (if any) and the list of columns.

5481 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5482 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5483

5484 .. seealso::

5485

5486 :func:`_sql.select`

5487

5488 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5489

5490 """

5491

5492 __visit_name__ = "select"

5493

5494 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5495 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5496

5497 _raw_columns: List[_ColumnsClauseElement]

5498

5499 _distinct: bool = False

5500 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5501 _correlate: Tuple[FromClause, ...] = ()

5502 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5503 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5504 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5505 _from_obj: Tuple[FromClause, ...] = ()

5506

5507 _position_map = util.immutabledict(

5508 {

5509 "post_select": "_post_select_clause",

5510 "pre_columns": "_pre_columns_clause",

5511 "post_criteria": "_post_criteria_clause",

5512 "post_body": "_post_body_clause",

5513 }

5514 )

5515

5516 _post_select_clause: Optional[ClauseElement] = None

5517 """extension point for a ClauseElement that will be compiled directly

5518 after the SELECT keyword.

5519

5520 .. versionadded:: 2.1

5521

5522 """

5523

5524 _pre_columns_clause: Optional[ClauseElement] = None

5525 """extension point for a ClauseElement that will be compiled directly

5526 before the "columns" clause; after DISTINCT (if present).

5527

5528 .. versionadded:: 2.1

5529

5530 """

5531

5532 _post_criteria_clause: Optional[ClauseElement] = None

5533 """extension point for a ClauseElement that will be compiled directly

5534 after "criteria", following the HAVING clause but before ORDER BY.

5535

5536 .. versionadded:: 2.1

5537

5538 """

5539

5540 _post_body_clause: Optional[ClauseElement] = None

5541 """extension point for a ClauseElement that will be compiled directly

5542 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5543 of the SELECT.

5544

5545 .. versionadded:: 2.1

5546

5547 """

5548

5549 _auto_correlate = True

5550 _is_select_statement = True

5551 _compile_options: CacheableOptions = (

5552 SelectState.default_select_compile_options

5553 )

5554

5555 _traverse_internals: _TraverseInternalsType = (

5556 [

5557 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5558 (

5559 "_memoized_select_entities",

5560 InternalTraversal.dp_memoized_select_entities,

5561 ),

5562 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5563 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5564 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5565 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5566 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5567 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5568 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5569 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5570 ("_limit_clause", InternalTraversal.dp_clauseelement),

5571 ("_offset_clause", InternalTraversal.dp_clauseelement),

5572 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5573 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5574 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5575 ("_distinct", InternalTraversal.dp_boolean),

5576 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5577 ("_label_style", InternalTraversal.dp_plain_obj),

5578 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5579 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5580 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5581 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5582 ]

5583 + HasCTE._has_ctes_traverse_internals

5584 + HasPrefixes._has_prefixes_traverse_internals

5585 + HasSuffixes._has_suffixes_traverse_internals

5586 + HasHints._has_hints_traverse_internals

5587 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5588 + ExecutableStatement._executable_traverse_internals

5589 + DialectKWArgs._dialect_kwargs_traverse_internals

5590 )

5591

5592 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5593 ("_compile_options", InternalTraversal.dp_has_cache_key)

5594 ]

5595

5596 _compile_state_factory: Type[SelectState]

5597

5598 @classmethod

5599 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5600 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5601

5602 Used internally to build up :class:`.Select` constructs with

5603 pre-established state.

5604

5605 """

5606

5607 stmt = Select.__new__(Select)

5608 stmt.__dict__.update(kw)

5609 return stmt

5610

5611 def __init__(

5612 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5613 ):

5614 r"""Construct a new :class:`_expression.Select`.

5615

5616 The public constructor for :class:`_expression.Select` is the

5617 :func:`_sql.select` function.

5618

5619 """

5620 self._raw_columns = [

5621 coercions.expect(

5622 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5623 )

5624 for ent in entities

5625 ]

5626 GenerativeSelect.__init__(self)

5627

5628 def _apply_syntax_extension_to_self(

5629 self, extension: SyntaxExtension

5630 ) -> None:

5631 extension.apply_to_select(self)

5632

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

5634 if not self._raw_columns:

5635 return NULLTYPE

5636 elem = self._raw_columns[0]

5637 cols = list(elem._select_iterable)

5638 return cols[0].type

5639

5640 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5641 """A synonym for the :meth:`_sql.Select.where` method."""

5642

5643 return self.where(*criteria)

5644

5645 if TYPE_CHECKING:

5646

5647 @overload

5648 def scalar_subquery(

5649 self: Select[_MAYBE_ENTITY],

5650 ) -> ScalarSelect[Any]: ...

5651

5652 @overload

5653 def scalar_subquery(

5654 self: Select[_NOT_ENTITY],

5655 ) -> ScalarSelect[_NOT_ENTITY]: ...

5656

5657 @overload

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

5659

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

5661

5662 def filter_by(self, **kwargs: Any) -> Self:

5663 r"""Apply the given filtering criterion as a WHERE clause

5664 to this select, using keyword expressions.

5665

5666 E.g.::

5667

5668 stmt = select(User).filter_by(name="some name")

5669

5670 Multiple criteria may be specified as comma separated; the effect

5671 is that they will be joined together using the :func:`.and_`

5672 function::

5673

5674 stmt = select(User).filter_by(name="some name", id=5)

5675

5676 The keyword expressions are extracted by searching across **all

5677 entities present in the FROM clause** of the statement. If a

5678 keyword name is present in more than one entity,

5679 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5680 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5681 explicit column references::

5682

5683 # both User and Address have an 'id' attribute

5684 stmt = select(User).join(Address).filter_by(id=5)

5685 # raises AmbiguousColumnError

5686

5687 # use filter() with explicit qualification instead

5688 stmt = select(User).join(Address).filter(Address.id == 5)

5689

5690 .. versionchanged:: 2.1

5691

5692 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5693 entities rather than only searching the last joined entity or first

5694 FROM entity. This allows the method to locate attributes

5695 unambiguously across multiple joined tables. The new

5696 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5697 is present in more than one entity.

5698

5699 See :ref:`change_8601` for migration notes.

5700

5701 .. seealso::

5702

5703 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

5704

5705 :meth:`_sql.Select.filter` - filter on SQL expressions.

5706

5707 :meth:`_sql.Select.where` - filter on SQL expressions.

5708

5709 """

5710 # Get all entities via plugin system

5711 all_entities = SelectState.get_plugin_class(

5712 self

5713 )._get_filter_by_entities(self)

5714

5715 clauses = [

5716 _entity_namespace_key_search_all(all_entities, key) == value

5717 for key, value in kwargs.items()

5718 ]

5719 return self.filter(*clauses)

5720

5721 @property

5722 def column_descriptions(self) -> Any:

5723 """Return a :term:`plugin-enabled` 'column descriptions' structure

5724 referring to the columns which are SELECTed by this statement.

5725

5726 This attribute is generally useful when using the ORM, as an

5727 extended structure which includes information about mapped

5728 entities is returned. The section :ref:`queryguide_inspection`

5729 contains more background.

5730

5731 For a Core-only statement, the structure returned by this accessor

5732 is derived from the same objects that are returned by the

5733 :attr:`.Select.selected_columns` accessor, formatted as a list of

5734 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5735 which indicate the column expressions to be selected::

5736

5737 >>> stmt = select(user_table)

5738 >>> stmt.column_descriptions

5739 [

5740 {

5741 'name': 'id',

5742 'type': Integer(),

5743 'expr': Column('id', Integer(), ...)},

5744 {

5745 'name': 'name',

5746 'type': String(length=30),

5747 'expr': Column('name', String(length=30), ...)}

5748 ]

5749

5750 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5751 attribute returns a structure for a Core-only set of entities,

5752 not just ORM-only entities.

5753

5754 .. seealso::

5755

5756 :attr:`.UpdateBase.entity_description` - entity information for

5757 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5758

5759 :ref:`queryguide_inspection` - ORM background

5760

5761 """

5762 meth = SelectState.get_plugin_class(self).get_column_descriptions

5763 return meth(self)

5764

5765 def from_statement(

5766 self, statement: roles.ReturnsRowsRole

5767 ) -> ExecutableReturnsRows:

5768 """Apply the columns which this :class:`.Select` would select

5769 onto another statement.

5770

5771 This operation is :term:`plugin-specific` and will raise a not

5772 supported exception if this :class:`_sql.Select` does not select from

5773 plugin-enabled entities.

5774

5775

5776 The statement is typically either a :func:`_expression.text` or

5777 :func:`_expression.select` construct, and should return the set of

5778 columns appropriate to the entities represented by this

5779 :class:`.Select`.

5780

5781 .. seealso::

5782

5783 :ref:`orm_queryguide_selecting_text` - usage examples in the

5784 ORM Querying Guide

5785

5786 """

5787 meth = SelectState.get_plugin_class(self).from_statement

5788 return meth(self, statement)

5789

5790 @_generative

5791 def join(

5792 self,

5793 target: _JoinTargetArgument,

5794 onclause: Optional[_OnClauseArgument] = None,

5795 *,

5796 isouter: bool = False,

5797 full: bool = False,

5798 ) -> Self:

5799 r"""Create a SQL JOIN against this :class:`_expression.Select`

5800 object's criterion

5801 and apply generatively, returning the newly resulting

5802 :class:`_expression.Select`.

5803

5804 E.g.::

5805

5806 stmt = select(user_table).join(

5807 address_table, user_table.c.id == address_table.c.user_id

5808 )

5809

5810 The above statement generates SQL similar to:

5811

5812 .. sourcecode:: sql

5813

5814 SELECT user.id, user.name

5815 FROM user

5816 JOIN address ON user.id = address.user_id

5817

5818 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5819 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5820 source that is within the FROM clause of the existing SELECT,

5821 and a given target :class:`_sql.FromClause`, and then adds

5822 this :class:`_sql.Join` to the FROM clause of the newly generated

5823 SELECT statement. This is completely reworked from the behavior

5824 in 1.3, which would instead create a subquery of the entire

5825 :class:`_expression.Select` and then join that subquery to the

5826 target.

5827

5828 This is a **backwards incompatible change** as the previous behavior

5829 was mostly useless, producing an unnamed subquery rejected by

5830 most databases in any case. The new behavior is modeled after

5831 that of the very successful :meth:`_orm.Query.join` method in the

5832 ORM, in order to support the functionality of :class:`_orm.Query`

5833 being available by using a :class:`_sql.Select` object with an

5834 :class:`_orm.Session`.

5835

5836 See the notes for this change at :ref:`change_select_join`.

5837

5838

5839 :param target: target table to join towards

5840

5841 :param onclause: ON clause of the join. If omitted, an ON clause

5842 is generated automatically based on the :class:`_schema.ForeignKey`

5843 linkages between the two tables, if one can be unambiguously

5844 determined, otherwise an error is raised.

5845

5846 :param isouter: if True, generate LEFT OUTER join. Same as

5847 :meth:`_expression.Select.outerjoin`.

5848

5849 :param full: if True, generate FULL OUTER join.

5850

5851 .. seealso::

5852

5853 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5854

5855 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5856

5857 :meth:`_expression.Select.join_from`

5858

5859 :meth:`_expression.Select.outerjoin`

5860

5861 """ # noqa: E501

5862 join_target = coercions.expect(

5863 roles.JoinTargetRole, target, apply_propagate_attrs=self

5864 )

5865 if onclause is not None:

5866 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5867 else:

5868 onclause_element = None

5869

5870 self._setup_joins += (

5871 (

5872 join_target,

5873 onclause_element,

5874 None,

5875 {"isouter": isouter, "full": full},

5876 ),

5877 )

5878 return self

5879

5880 def outerjoin_from(

5881 self,

5882 from_: _FromClauseArgument,

5883 target: _JoinTargetArgument,

5884 onclause: Optional[_OnClauseArgument] = None,

5885 *,

5886 full: bool = False,

5887 ) -> Self:

5888 r"""Create a SQL LEFT OUTER JOIN against this

5889 :class:`_expression.Select` object's criterion and apply generatively,

5890 returning the newly resulting :class:`_expression.Select`.

5891

5892 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5893

5894 """

5895 return self.join_from(

5896 from_, target, onclause=onclause, isouter=True, full=full

5897 )

5898

5899 @_generative

5900 def join_from(

5901 self,

5902 from_: _FromClauseArgument,

5903 target: _JoinTargetArgument,

5904 onclause: Optional[_OnClauseArgument] = None,

5905 *,

5906 isouter: bool = False,

5907 full: bool = False,

5908 ) -> Self:

5909 r"""Create a SQL JOIN against this :class:`_expression.Select`

5910 object's criterion

5911 and apply generatively, returning the newly resulting

5912 :class:`_expression.Select`.

5913

5914 E.g.::

5915

5916 stmt = select(user_table, address_table).join_from(

5917 user_table, address_table, user_table.c.id == address_table.c.user_id

5918 )

5919

5920 The above statement generates SQL similar to:

5921

5922 .. sourcecode:: sql

5923

5924 SELECT user.id, user.name, address.id, address.email, address.user_id

5925 FROM user JOIN address ON user.id = address.user_id

5926

5927 .. versionadded:: 1.4

5928

5929 :param from\_: the left side of the join, will be rendered in the

5930 FROM clause and is roughly equivalent to using the

5931 :meth:`.Select.select_from` method.

5932

5933 :param target: target table to join towards

5934

5935 :param onclause: ON clause of the join.

5936

5937 :param isouter: if True, generate LEFT OUTER join. Same as

5938 :meth:`_expression.Select.outerjoin`.

5939

5940 :param full: if True, generate FULL OUTER join.

5941

5942 .. seealso::

5943

5944 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5945

5946 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5947

5948 :meth:`_expression.Select.join`

5949

5950 """ # noqa: E501

5951

5952 # note the order of parsing from vs. target is important here, as we

5953 # are also deriving the source of the plugin (i.e. the subject mapper

5954 # in an ORM query) which should favor the "from_" over the "target"

5955

5956 from_ = coercions.expect(

5957 roles.FromClauseRole, from_, apply_propagate_attrs=self

5958 )

5959 join_target = coercions.expect(

5960 roles.JoinTargetRole, target, apply_propagate_attrs=self

5961 )

5962 if onclause is not None:

5963 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5964 else:

5965 onclause_element = None

5966

5967 self._setup_joins += (

5968 (

5969 join_target,

5970 onclause_element,

5971 from_,

5972 {"isouter": isouter, "full": full},

5973 ),

5974 )

5975 return self

5976

5977 def outerjoin(

5978 self,

5979 target: _JoinTargetArgument,

5980 onclause: Optional[_OnClauseArgument] = None,

5981 *,

5982 full: bool = False,

5983 ) -> Self:

5984 """Create a left outer join.

5985

5986 Parameters are the same as that of :meth:`_expression.Select.join`.

5987

5988 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5989 creates a :class:`_sql.Join` object between a

5990 :class:`_sql.FromClause` source that is within the FROM clause of

5991 the existing SELECT, and a given target :class:`_sql.FromClause`,

5992 and then adds this :class:`_sql.Join` to the FROM clause of the

5993 newly generated SELECT statement. This is completely reworked

5994 from the behavior in 1.3, which would instead create a subquery of

5995 the entire

5996 :class:`_expression.Select` and then join that subquery to the

5997 target.

5998

5999 This is a **backwards incompatible change** as the previous behavior

6000 was mostly useless, producing an unnamed subquery rejected by

6001 most databases in any case. The new behavior is modeled after

6002 that of the very successful :meth:`_orm.Query.join` method in the

6003 ORM, in order to support the functionality of :class:`_orm.Query`

6004 being available by using a :class:`_sql.Select` object with an

6005 :class:`_orm.Session`.

6006

6007 See the notes for this change at :ref:`change_select_join`.

6008

6009 .. seealso::

6010

6011 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

6012

6013 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

6014

6015 :meth:`_expression.Select.join`

6016

6017 """

6018 return self.join(target, onclause=onclause, isouter=True, full=full)

6019

6020 def get_final_froms(self) -> Sequence[FromClause]:

6021 """Compute the final displayed list of :class:`_expression.FromClause`

6022 elements.

6023

6024 This method will run through the full computation required to

6025 determine what FROM elements will be displayed in the resulting

6026 SELECT statement, including shadowing individual tables with

6027 JOIN objects, as well as full computation for ORM use cases including

6028 eager loading clauses.

6029

6030 For ORM use, this accessor returns the **post compilation**

6031 list of FROM objects; this collection will include elements such as

6032 eagerly loaded tables and joins. The objects will **not** be

6033 ORM enabled and not work as a replacement for the

6034 :meth:`_sql.Select.select_froms` collection; additionally, the

6035 method is not well performing for an ORM enabled statement as it

6036 will incur the full ORM construction process.

6037

6038 To retrieve the FROM list that's implied by the "columns" collection

6039 passed to the :class:`_sql.Select` originally, use the

6040 :attr:`_sql.Select.columns_clause_froms` accessor.

6041

6042 To select from an alternative set of columns while maintaining the

6043 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6044 pass the

6045 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6046 parameter.

6047

6048 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6049 method replaces the previous :attr:`_sql.Select.froms` accessor,

6050 which is deprecated.

6051

6052 .. seealso::

6053

6054 :attr:`_sql.Select.columns_clause_froms`

6055

6056 """

6057 compiler = self._default_compiler()

6058

6059 return self._compile_state_factory(self, compiler)._get_display_froms()

6060

6061 @property

6062 @util.deprecated(

6063 "1.4.23",

6064 "The :attr:`_expression.Select.froms` attribute is moved to "

6065 "the :meth:`_expression.Select.get_final_froms` method.",

6066 )

6067 def froms(self) -> Sequence[FromClause]:

6068 """Return the displayed list of :class:`_expression.FromClause`

6069 elements.

6070

6071

6072 """

6073 return self.get_final_froms()

6074

6075 @property

6076 def columns_clause_froms(self) -> List[FromClause]:

6077 """Return the set of :class:`_expression.FromClause` objects implied

6078 by the columns clause of this SELECT statement.

6079

6080 .. versionadded:: 1.4.23

6081

6082 .. seealso::

6083

6084 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6085 statement into account

6086

6087 :meth:`_sql.Select.with_only_columns` - makes use of this

6088 collection to set up a new FROM list

6089

6090 """

6091

6092 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6093 self

6094 )

6095

6096 @property

6097 def inner_columns(self) -> _SelectIterable:

6098 """An iterator of all :class:`_expression.ColumnElement`

6099 expressions which would

6100 be rendered into the columns clause of the resulting SELECT statement.

6101

6102 This method is legacy as of 1.4 and is superseded by the

6103 :attr:`_expression.Select.exported_columns` collection.

6104

6105 """

6106

6107 return iter(self._all_selected_columns)

6108

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

6110 if fromclause is not None and self in fromclause._cloned_set:

6111 return True

6112

6113 for f in self._iterate_from_elements():

6114 if f.is_derived_from(fromclause):

6115 return True

6116 return False

6117

6118 def _copy_internals(

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

6120 ) -> None:

6121 # Select() object has been cloned and probably adapted by the

6122 # given clone function. Apply the cloning function to internal

6123 # objects

6124

6125 # 1. keep a dictionary of the froms we've cloned, and what

6126 # they've become. This allows us to ensure the same cloned from

6127 # is used when other items such as columns are "cloned"

6128

6129 all_the_froms = set(

6130 itertools.chain(

6131 _from_objects(*self._raw_columns),

6132 _from_objects(*self._where_criteria),

6133 _from_objects(*[elem[0] for elem in self._setup_joins]),

6134 )

6135 )

6136

6137 # do a clone for the froms we've gathered. what is important here

6138 # is if any of the things we are selecting from, like tables,

6139 # were converted into Join objects. if so, these need to be

6140 # added to _from_obj explicitly, because otherwise they won't be

6141 # part of the new state, as they don't associate themselves with

6142 # their columns.

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

6144

6145 # 2. copy FROM collections, adding in joins that we've created.

6146 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6147 add_froms = (

6148 {f for f in new_froms.values() if isinstance(f, Join)}

6149 .difference(all_the_froms)

6150 .difference(existing_from_obj)

6151 )

6152

6153 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6154

6155 # 3. clone everything else, making sure we use columns

6156 # corresponding to the froms we just made.

6157 def replace(

6158 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6159 **kw: Any,

6160 ) -> Optional[KeyedColumnElement[Any]]:

6161 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6162 newelem = new_froms[obj.table].corresponding_column(obj)

6163 return newelem

6164 return None

6165

6166 kw["replace"] = replace

6167

6168 # copy everything else. for table-ish things like correlate,

6169 # correlate_except, setup_joins, these clone normally. For

6170 # column-expression oriented things like raw_columns, where_criteria,

6171 # order by, we get this from the new froms.

6172 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6173

6174 self._reset_memoizations()

6175

6176 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6177 return itertools.chain(

6178 super().get_children(

6179 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6180 **kw,

6181 ),

6182 self._iterate_from_elements(),

6183 )

6184

6185 @_generative

6186 def add_columns(

6187 self, *entities: _ColumnsClauseArgument[Any]

6188 ) -> Select[Unpack[TupleAny]]:

6189 r"""Return a new :func:`_expression.select` construct with

6190 the given entities appended to its columns clause.

6191

6192 E.g.::

6193

6194 my_select = my_select.add_columns(table.c.new_column)

6195

6196 The original expressions in the columns clause remain in place.

6197 To replace the original expressions with new ones, see the method

6198 :meth:`_expression.Select.with_only_columns`.

6199

6200 :param \*entities: column, table, or other entity expressions to be

6201 added to the columns clause

6202

6203 .. seealso::

6204

6205 :meth:`_expression.Select.with_only_columns` - replaces existing

6206 expressions rather than appending.

6207

6208 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6209 example

6210

6211 """

6212 self._reset_memoizations()

6213

6214 self._raw_columns = self._raw_columns + [

6215 coercions.expect(

6216 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6217 )

6218 for column in entities

6219 ]

6220 return self

6221

6222 def _set_entities(

6223 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6224 ) -> None:

6225 self._raw_columns = [

6226 coercions.expect(

6227 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6228 )

6229 for ent in util.to_list(entities)

6230 ]

6231

6232 @util.deprecated(

6233 "1.4",

6234 "The :meth:`_expression.Select.column` method is deprecated and will "

6235 "be removed in a future release. Please use "

6236 ":meth:`_expression.Select.add_columns`",

6237 )

6238 def column(

6239 self, column: _ColumnsClauseArgument[Any]

6240 ) -> Select[Unpack[TupleAny]]:

6241 """Return a new :func:`_expression.select` construct with

6242 the given column expression added to its columns clause.

6243

6244 E.g.::

6245

6246 my_select = my_select.column(table.c.new_column)

6247

6248 See the documentation for

6249 :meth:`_expression.Select.with_only_columns`

6250 for guidelines on adding /replacing the columns of a

6251 :class:`_expression.Select` object.

6252

6253 """

6254 return self.add_columns(column)

6255

6256 @util.preload_module("sqlalchemy.sql.util")

6257 def reduce_columns(

6258 self, only_synonyms: bool = True

6259 ) -> Select[Unpack[TupleAny]]:

6260 """Return a new :func:`_expression.select` construct with redundantly

6261 named, equivalently-valued columns removed from the columns clause.

6262

6263 "Redundant" here means two columns where one refers to the

6264 other either based on foreign key, or via a simple equality

6265 comparison in the WHERE clause of the statement. The primary purpose

6266 of this method is to automatically construct a select statement

6267 with all uniquely-named columns, without the need to use

6268 table-qualified labels as

6269 :meth:`_expression.Select.set_label_style`

6270 does.

6271

6272 When columns are omitted based on foreign key, the referred-to

6273 column is the one that's kept. When columns are omitted based on

6274 WHERE equivalence, the first column in the columns clause is the

6275 one that's kept.

6276

6277 :param only_synonyms: when True, limit the removal of columns

6278 to those which have the same name as the equivalent. Otherwise,

6279 all columns that are equivalent to another are removed.

6280

6281 """

6282 woc: Select[Unpack[TupleAny]]

6283 woc = self.with_only_columns(

6284 *util.preloaded.sql_util.reduce_columns(

6285 self._all_selected_columns,

6286 only_synonyms=only_synonyms,

6287 *(self._where_criteria + self._from_obj),

6288 )

6289 )

6290 return woc

6291

6292 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6293

6294 # code within this block is **programmatically,

6295 # statically generated** by tools/generate_tuple_map_overloads.py

6296

6297 @overload

6298 def with_only_columns(

6299 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6300 ) -> Select[_T0]: ...

6301

6302 @overload

6303 def with_only_columns(

6304 self,

6305 __ent0: _TCCA[_T0],

6306 __ent1: _TCCA[_T1],

6307 /,

6308 *,

6309 maintain_column_froms: bool = ...,

6310 ) -> Select[_T0, _T1]: ...

6311

6312 @overload

6313 def with_only_columns(

6314 self,

6315 __ent0: _TCCA[_T0],

6316 __ent1: _TCCA[_T1],

6317 __ent2: _TCCA[_T2],

6318 /,

6319 *,

6320 maintain_column_froms: bool = ...,

6321 ) -> Select[_T0, _T1, _T2]: ...

6322

6323 @overload

6324 def with_only_columns(

6325 self,

6326 __ent0: _TCCA[_T0],

6327 __ent1: _TCCA[_T1],

6328 __ent2: _TCCA[_T2],

6329 __ent3: _TCCA[_T3],

6330 /,

6331 *,

6332 maintain_column_froms: bool = ...,

6333 ) -> Select[_T0, _T1, _T2, _T3]: ...

6334

6335 @overload

6336 def with_only_columns(

6337 self,

6338 __ent0: _TCCA[_T0],

6339 __ent1: _TCCA[_T1],

6340 __ent2: _TCCA[_T2],

6341 __ent3: _TCCA[_T3],

6342 __ent4: _TCCA[_T4],

6343 /,

6344 *,

6345 maintain_column_froms: bool = ...,

6346 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6347

6348 @overload

6349 def with_only_columns(

6350 self,

6351 __ent0: _TCCA[_T0],

6352 __ent1: _TCCA[_T1],

6353 __ent2: _TCCA[_T2],

6354 __ent3: _TCCA[_T3],

6355 __ent4: _TCCA[_T4],

6356 __ent5: _TCCA[_T5],

6357 /,

6358 *,

6359 maintain_column_froms: bool = ...,

6360 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6361

6362 @overload

6363 def with_only_columns(

6364 self,

6365 __ent0: _TCCA[_T0],

6366 __ent1: _TCCA[_T1],

6367 __ent2: _TCCA[_T2],

6368 __ent3: _TCCA[_T3],

6369 __ent4: _TCCA[_T4],

6370 __ent5: _TCCA[_T5],

6371 __ent6: _TCCA[_T6],

6372 /,

6373 *,

6374 maintain_column_froms: bool = ...,

6375 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6376

6377 @overload

6378 def with_only_columns(

6379 self,

6380 __ent0: _TCCA[_T0],

6381 __ent1: _TCCA[_T1],

6382 __ent2: _TCCA[_T2],

6383 __ent3: _TCCA[_T3],

6384 __ent4: _TCCA[_T4],

6385 __ent5: _TCCA[_T5],

6386 __ent6: _TCCA[_T6],

6387 __ent7: _TCCA[_T7],

6388 /,

6389 *entities: _ColumnsClauseArgument[Any],

6390 maintain_column_froms: bool = ...,

6391 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6392

6393 # END OVERLOADED FUNCTIONS self.with_only_columns

6394

6395 @overload

6396 def with_only_columns(

6397 self,

6398 *entities: _ColumnsClauseArgument[Any],

6399 maintain_column_froms: bool = False,

6400 **__kw: Any,

6401 ) -> Select[Unpack[TupleAny]]: ...

6402

6403 @_generative

6404 def with_only_columns(

6405 self,

6406 *entities: _ColumnsClauseArgument[Any],

6407 maintain_column_froms: bool = False,

6408 **__kw: Any,

6409 ) -> Select[Unpack[TupleAny]]:

6410 r"""Return a new :func:`_expression.select` construct with its columns

6411 clause replaced with the given entities.

6412

6413 By default, this method is exactly equivalent to as if the original

6414 :func:`_expression.select` had been called with the given entities.

6415 E.g. a statement::

6416

6417 s = select(table1.c.a, table1.c.b)

6418 s = s.with_only_columns(table1.c.b)

6419

6420 should be exactly equivalent to::

6421

6422 s = select(table1.c.b)

6423

6424 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6425 will also dynamically alter the FROM clause of the

6426 statement if it is not explicitly stated.

6427 To maintain the existing set of FROMs including those implied by the

6428 current columns clause, add the

6429 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6430 parameter::

6431

6432 s = select(table1.c.a, table2.c.b)

6433 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6434

6435 The above parameter performs a transfer of the effective FROMs

6436 in the columns collection to the :meth:`_sql.Select.select_from`

6437 method, as though the following were invoked::

6438

6439 s = select(table1.c.a, table2.c.b)

6440 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6441

6442 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6443 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6444 collection and performs an operation equivalent to the following::

6445

6446 s = select(table1.c.a, table2.c.b)

6447 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6448

6449 :param \*entities: column expressions to be used.

6450

6451 :param maintain_column_froms: boolean parameter that will ensure the

6452 FROM list implied from the current columns clause will be transferred

6453 to the :meth:`_sql.Select.select_from` method first.

6454

6455 .. versionadded:: 1.4.23

6456

6457 """ # noqa: E501

6458

6459 if __kw:

6460 raise _no_kw()

6461

6462 # memoizations should be cleared here as of

6463 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6464 # is the case for now.

6465 self._assert_no_memoizations()

6466

6467 if maintain_column_froms:

6468 self.select_from.non_generative( # type: ignore[attr-defined]

6469 self, *self.columns_clause_froms

6470 )

6471

6472 # then memoize the FROMs etc.

6473 _MemoizedSelectEntities._generate_for_statement(self)

6474

6475 self._raw_columns = [

6476 coercions.expect(roles.ColumnsClauseRole, c)

6477 for c in coercions._expression_collection_was_a_list(

6478 "entities", "Select.with_only_columns", entities

6479 )

6480 ]

6481 return self

6482

6483 @property

6484 def whereclause(self) -> Optional[ColumnElement[Any]]:

6485 """Return the completed WHERE clause for this

6486 :class:`_expression.Select` statement.

6487

6488 This assembles the current collection of WHERE criteria

6489 into a single :class:`_expression.BooleanClauseList` construct.

6490

6491

6492 .. versionadded:: 1.4

6493

6494 """

6495

6496 return BooleanClauseList._construct_for_whereclause(

6497 self._where_criteria

6498 )

6499

6500 _whereclause = whereclause

6501

6502 @_generative

6503 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6504 """Return a new :func:`_expression.select` construct with

6505 the given expression added to

6506 its WHERE clause, joined to the existing clause via AND, if any.

6507

6508 """

6509

6510 assert isinstance(self._where_criteria, tuple)

6511

6512 for criterion in whereclause:

6513 where_criteria: ColumnElement[Any] = coercions.expect(

6514 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6515 )

6516 self._where_criteria += (where_criteria,)

6517 return self

6518

6519 @_generative

6520 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6521 """Return a new :func:`_expression.select` construct with

6522 the given expression added to

6523 its HAVING clause, joined to the existing clause via AND, if any.

6524

6525 """

6526

6527 for criterion in having:

6528 having_criteria = coercions.expect(

6529 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6530 )

6531 self._having_criteria += (having_criteria,)

6532 return self

6533

6534 @_generative

6535 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6536 r"""Return a new :func:`_expression.select` construct which

6537 will apply DISTINCT to the SELECT statement overall.

6538

6539 E.g.::

6540

6541 from sqlalchemy import select

6542

6543 stmt = select(users_table.c.id, users_table.c.name).distinct()

6544

6545 The above would produce an statement resembling:

6546

6547 .. sourcecode:: sql

6548

6549 SELECT DISTINCT user.id, user.name FROM user

6550

6551 The method also historically accepted an ``*expr`` parameter which

6552 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6553 This is now replaced using the :func:`_postgresql.distinct_on`

6554 extension::

6555

6556 from sqlalchemy import select

6557 from sqlalchemy.dialects.postgresql import distinct_on

6558

6559 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6560

6561 Using this parameter on other backends which don't support this

6562 syntax will raise an error.

6563

6564 :param \*expr: optional column expressions. When present,

6565 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6566 construct. A deprecation warning and/or :class:`_exc.CompileError`

6567 will be raised on other backends.

6568

6569 .. deprecated:: 2.1 Passing expressions to

6570 :meth:`_sql.Select.distinct` is deprecated, use

6571 :func:`_postgresql.distinct_on` instead.

6572

6573 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6574 and will raise :class:`_exc.CompileError` in a future version.

6575

6576 .. seealso::

6577

6578 :func:`_postgresql.distinct_on`

6579

6580 :meth:`.ext`

6581 """

6582 self._distinct = True

6583 if expr:

6584 warn_deprecated(

6585 "Passing expression to ``distinct`` to generate a "

6586 "DISTINCT ON clause is deprecated. Use instead the "

6587 "``postgresql.distinct_on`` function as an extension.",

6588 "2.1",

6589 )

6590 self._distinct_on = self._distinct_on + tuple(

6591 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6592 for e in expr

6593 )

6594 return self

6595

6596 @_generative

6597 def select_from(self, *froms: _FromClauseArgument) -> Self:

6598 r"""Return a new :func:`_expression.select` construct with the

6599 given FROM expression(s)

6600 merged into its list of FROM objects.

6601

6602 E.g.::

6603

6604 table1 = table("t1", column("a"))

6605 table2 = table("t2", column("b"))

6606 s = select(table1.c.a).select_from(

6607 table1.join(table2, table1.c.a == table2.c.b)

6608 )

6609

6610 The "from" list is a unique set on the identity of each element,

6611 so adding an already present :class:`_schema.Table`

6612 or other selectable

6613 will have no effect. Passing a :class:`_expression.Join` that refers

6614 to an already present :class:`_schema.Table`

6615 or other selectable will have

6616 the effect of concealing the presence of that selectable as

6617 an individual element in the rendered FROM list, instead

6618 rendering it into a JOIN clause.

6619

6620 While the typical purpose of :meth:`_expression.Select.select_from`

6621 is to

6622 replace the default, derived FROM clause with a join, it can

6623 also be called with individual table elements, multiple times

6624 if desired, in the case that the FROM clause cannot be fully

6625 derived from the columns clause::

6626

6627 select(func.count("*")).select_from(table1)

6628

6629 """

6630

6631 self._from_obj += tuple(

6632 coercions.expect(

6633 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6634 )

6635 for fromclause in froms

6636 )

6637 return self

6638

6639 @_generative

6640 def correlate(

6641 self,

6642 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6643 ) -> Self:

6644 r"""Return a new :class:`_expression.Select`

6645 which will correlate the given FROM

6646 clauses to that of an enclosing :class:`_expression.Select`.

6647

6648 Calling this method turns off the :class:`_expression.Select` object's

6649 default behavior of "auto-correlation". Normally, FROM elements

6650 which appear in a :class:`_expression.Select`

6651 that encloses this one via

6652 its :term:`WHERE clause`, ORDER BY, HAVING or

6653 :term:`columns clause` will be omitted from this

6654 :class:`_expression.Select`

6655 object's :term:`FROM clause`.

6656 Setting an explicit correlation collection using the

6657 :meth:`_expression.Select.correlate`

6658 method provides a fixed list of FROM objects

6659 that can potentially take place in this process.

6660

6661 When :meth:`_expression.Select.correlate`

6662 is used to apply specific FROM clauses

6663 for correlation, the FROM elements become candidates for

6664 correlation regardless of how deeply nested this

6665 :class:`_expression.Select`

6666 object is, relative to an enclosing :class:`_expression.Select`

6667 which refers to

6668 the same FROM object. This is in contrast to the behavior of

6669 "auto-correlation" which only correlates to an immediate enclosing

6670 :class:`_expression.Select`.

6671 Multi-level correlation ensures that the link

6672 between enclosed and enclosing :class:`_expression.Select`

6673 is always via

6674 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6675 correlation to take place.

6676

6677 If ``None`` is passed, the :class:`_expression.Select`

6678 object will correlate

6679 none of its FROM entries, and all will render unconditionally

6680 in the local FROM clause.

6681

6682 :param \*fromclauses: one or more :class:`.FromClause` or other

6683 FROM-compatible construct such as an ORM mapped entity to become part

6684 of the correlate collection; alternatively pass a single value

6685 ``None`` to remove all existing correlations.

6686

6687 .. seealso::

6688

6689 :meth:`_expression.Select.correlate_except`

6690

6691 :ref:`tutorial_scalar_subquery`

6692

6693 """

6694

6695 # tests failing when we try to change how these

6696 # arguments are passed

6697

6698 self._auto_correlate = False

6699 if not fromclauses or fromclauses[0] in {None, False}:

6700 if len(fromclauses) > 1:

6701 raise exc.ArgumentError(

6702 "additional FROM objects not accepted when "

6703 "passing None/False to correlate()"

6704 )

6705 self._correlate = ()

6706 else:

6707 self._correlate = self._correlate + tuple(

6708 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6709 )

6710 return self

6711

6712 @_generative

6713 def correlate_except(

6714 self,

6715 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6716 ) -> Self:

6717 r"""Return a new :class:`_expression.Select`

6718 which will omit the given FROM

6719 clauses from the auto-correlation process.

6720

6721 Calling :meth:`_expression.Select.correlate_except` turns off the

6722 :class:`_expression.Select` object's default behavior of

6723 "auto-correlation" for the given FROM elements. An element

6724 specified here will unconditionally appear in the FROM list, while

6725 all other FROM elements remain subject to normal auto-correlation

6726 behaviors.

6727

6728 If ``None`` is passed, or no arguments are passed,

6729 the :class:`_expression.Select` object will correlate all of its

6730 FROM entries.

6731

6732 :param \*fromclauses: a list of one or more

6733 :class:`_expression.FromClause`

6734 constructs, or other compatible constructs (i.e. ORM-mapped

6735 classes) to become part of the correlate-exception collection.

6736

6737 .. seealso::

6738

6739 :meth:`_expression.Select.correlate`

6740

6741 :ref:`tutorial_scalar_subquery`

6742

6743 """

6744

6745 self._auto_correlate = False

6746 if not fromclauses or fromclauses[0] in {None, False}:

6747 if len(fromclauses) > 1:

6748 raise exc.ArgumentError(

6749 "additional FROM objects not accepted when "

6750 "passing None/False to correlate_except()"

6751 )

6752 self._correlate_except = ()

6753 else:

6754 self._correlate_except = (self._correlate_except or ()) + tuple(

6755 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6756 )

6757

6758 return self

6759

6760 @HasMemoized_ro_memoized_attribute

6761 def selected_columns(

6762 self,

6763 ) -> ColumnCollection[str, ColumnElement[Any]]:

6764 """A :class:`_expression.ColumnCollection`

6765 representing the columns that

6766 this SELECT statement or similar construct returns in its result set,

6767 not including :class:`_sql.TextClause` constructs.

6768

6769 This collection differs from the :attr:`_expression.FromClause.columns`

6770 collection of a :class:`_expression.FromClause` in that the columns

6771 within this collection cannot be directly nested inside another SELECT

6772 statement; a subquery must be applied first which provides for the

6773 necessary parenthesization required by SQL.

6774

6775 For a :func:`_expression.select` construct, the collection here is

6776 exactly what would be rendered inside the "SELECT" statement, and the

6777 :class:`_expression.ColumnElement` objects are directly present as they

6778 were given, e.g.::

6779

6780 col1 = column("q", Integer)

6781 col2 = column("p", Integer)

6782 stmt = select(col1, col2)

6783

6784 Above, ``stmt.selected_columns`` would be a collection that contains

6785 the ``col1`` and ``col2`` objects directly. For a statement that is

6786 against a :class:`_schema.Table` or other

6787 :class:`_expression.FromClause`, the collection will use the

6788 :class:`_expression.ColumnElement` objects that are in the

6789 :attr:`_expression.FromClause.c` collection of the from element.

6790

6791 A use case for the :attr:`_sql.Select.selected_columns` collection is

6792 to allow the existing columns to be referenced when adding additional

6793 criteria, e.g.::

6794

6795 def filter_on_id(my_select, id):

6796 return my_select.where(my_select.selected_columns["id"] == id)

6797

6798

6799 stmt = select(MyModel)

6800

6801 # adds "WHERE id=:param" to the statement

6802 stmt = filter_on_id(stmt, 42)

6803

6804 .. note::

6805

6806 The :attr:`_sql.Select.selected_columns` collection does not

6807 include expressions established in the columns clause using the

6808 :func:`_sql.text` construct; these are silently omitted from the

6809 collection. To use plain textual column expressions inside of a

6810 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6811 construct.

6812

6813

6814 .. versionadded:: 1.4

6815

6816 """

6817

6818 # compare to SelectState._generate_columns_plus_names, which

6819 # generates the actual names used in the SELECT string. that

6820 # method is more complex because it also renders columns that are

6821 # fully ambiguous, e.g. same column more than once.

6822 conv = cast(

6823 "Callable[[Any], str]",

6824 SelectState._column_naming_convention(self._label_style),

6825 )

6826

6827 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6828 WriteableColumnCollection(

6829 [

6830 (conv(c), c)

6831 for c in self._all_selected_columns

6832 if is_column_element(c)

6833 ]

6834 )

6835 )

6836 return cc.as_readonly()

6837

6838 @HasMemoized_ro_memoized_attribute

6839 def _all_selected_columns(self) -> _SelectIterable:

6840 meth = SelectState.get_plugin_class(self).all_selected_columns

6841 return list(meth(self))

6842

6843 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6844 if self._label_style is LABEL_STYLE_NONE:

6845 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6846 return self

6847

6848 def _generate_fromclause_column_proxies(

6849 self,

6850 subquery: FromClause,

6851 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

6852 primary_key: ColumnSet,

6853 foreign_keys: Set[KeyedColumnElement[Any]],

6854 *,

6855 proxy_compound_columns: Optional[

6856 Iterable[Sequence[ColumnElement[Any]]]

6857 ] = None,

6858 ) -> None:

6859 """Generate column proxies to place in the exported ``.c``

6860 collection of a subquery."""

6861

6862 if proxy_compound_columns:

6863 extra_col_iterator = proxy_compound_columns

6864 prox = [

6865 c._make_proxy(

6866 subquery,

6867 key=proxy_key,

6868 name=required_label_name,

6869 name_is_truncatable=True,

6870 compound_select_cols=extra_cols,

6871 primary_key=primary_key,

6872 foreign_keys=foreign_keys,

6873 )

6874 for (

6875 (

6876 required_label_name,

6877 proxy_key,

6878 fallback_label_name,

6879 c,

6880 repeated,

6881 ),

6882 extra_cols,

6883 ) in (

6884 zip(

6885 self._generate_columns_plus_names(False),

6886 extra_col_iterator,

6887 )

6888 )

6889 if is_column_element(c)

6890 ]

6891 else:

6892 prox = [

6893 c._make_proxy(

6894 subquery,

6895 key=proxy_key,

6896 name=required_label_name,

6897 name_is_truncatable=True,

6898 primary_key=primary_key,

6899 foreign_keys=foreign_keys,

6900 )

6901 for (

6902 required_label_name,

6903 proxy_key,

6904 fallback_label_name,

6905 c,

6906 repeated,

6907 ) in (self._generate_columns_plus_names(False))

6908 if is_column_element(c)

6909 ]

6910

6911 columns._populate_separate_keys(prox)

6912

6913 def _needs_parens_for_grouping(self) -> bool:

6914 return self._has_row_limiting_clause or bool(

6915 self._order_by_clause.clauses

6916 )

6917

6918 def self_group(

6919 self, against: Optional[OperatorType] = None

6920 ) -> Union[SelectStatementGrouping[Self], Self]:

6921 """Return a 'grouping' construct as per the

6922 :class:`_expression.ClauseElement` specification.

6923

6924 This produces an element that can be embedded in an expression. Note

6925 that this method is called automatically as needed when constructing

6926 expressions and should not require explicit use.

6927

6928 """

6929 if (

6930 isinstance(against, CompoundSelect)

6931 and not self._needs_parens_for_grouping()

6932 ):

6933 return self

6934 else:

6935 return SelectStatementGrouping(self)

6936

6937 def union(

6938 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6939 ) -> CompoundSelect[Unpack[_Ts]]:

6940 r"""Return a SQL ``UNION`` of this select() construct against

6941 the given selectables provided as positional arguments.

6942

6943 :param \*other: one or more elements with which to create a

6944 UNION.

6945

6946 .. versionchanged:: 1.4.28

6947

6948 multiple elements are now accepted.

6949

6950 :param \**kwargs: keyword arguments are forwarded to the constructor

6951 for the newly created :class:`_sql.CompoundSelect` object.

6952

6953 """

6954 return CompoundSelect._create_union(self, *other)

6955

6956 def union_all(

6957 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6958 ) -> CompoundSelect[Unpack[_Ts]]:

6959 r"""Return a SQL ``UNION ALL`` of this select() construct against

6960 the given selectables provided as positional arguments.

6961

6962 :param \*other: one or more elements with which to create a

6963 UNION.

6964

6965 .. versionchanged:: 1.4.28

6966

6967 multiple elements are now accepted.

6968

6969 :param \**kwargs: keyword arguments are forwarded to the constructor

6970 for the newly created :class:`_sql.CompoundSelect` object.

6971

6972 """

6973 return CompoundSelect._create_union_all(self, *other)

6974

6975 def except_(

6976 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6977 ) -> CompoundSelect[Unpack[_Ts]]:

6978 r"""Return a SQL ``EXCEPT`` of this select() construct against

6979 the given selectable provided as positional arguments.

6980

6981 :param \*other: one or more elements with which to create a

6982 UNION.

6983

6984 .. versionchanged:: 1.4.28

6985

6986 multiple elements are now accepted.

6987

6988 """

6989 return CompoundSelect._create_except(self, *other)

6990

6991 def except_all(

6992 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6993 ) -> CompoundSelect[Unpack[_Ts]]:

6994 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6995 the given selectables provided as positional arguments.

6996

6997 :param \*other: one or more elements with which to create a

6998 UNION.

6999

7000 .. versionchanged:: 1.4.28

7001

7002 multiple elements are now accepted.

7003

7004 """

7005 return CompoundSelect._create_except_all(self, *other)

7006

7007 def intersect(

7008 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7009 ) -> CompoundSelect[Unpack[_Ts]]:

7010 r"""Return a SQL ``INTERSECT`` of this select() construct against

7011 the given selectables provided as positional arguments.

7012

7013 :param \*other: one or more elements with which to create a

7014 UNION.

7015

7016 .. versionchanged:: 1.4.28

7017

7018 multiple elements are now accepted.

7019

7020 :param \**kwargs: keyword arguments are forwarded to the constructor

7021 for the newly created :class:`_sql.CompoundSelect` object.

7022

7023 """

7024 return CompoundSelect._create_intersect(self, *other)

7025

7026 def intersect_all(

7027 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7028 ) -> CompoundSelect[Unpack[_Ts]]:

7029 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

7030 against the given selectables provided as positional arguments.

7031

7032 :param \*other: one or more elements with which to create a

7033 UNION.

7034

7035 .. versionchanged:: 1.4.28

7036

7037 multiple elements are now accepted.

7038

7039 :param \**kwargs: keyword arguments are forwarded to the constructor

7040 for the newly created :class:`_sql.CompoundSelect` object.

7041

7042 """

7043 return CompoundSelect._create_intersect_all(self, *other)

7044

7045

7046class ScalarSelect(

7047 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7048):

7049 """Represent a scalar subquery.

7050

7051

7052 A :class:`_sql.ScalarSelect` is created by invoking the

7053 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7054 then participates in other SQL expressions as a SQL column expression

7055 within the :class:`_sql.ColumnElement` hierarchy.

7056

7057 .. seealso::

7058

7059 :meth:`_sql.SelectBase.scalar_subquery`

7060

7061 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7062

7063 """

7064

7065 _traverse_internals: _TraverseInternalsType = [

7066 ("element", InternalTraversal.dp_clauseelement),

7067 ("type", InternalTraversal.dp_type),

7068 ]

7069

7070 _from_objects: List[FromClause] = []

7071 _is_from_container = True

7072 if not TYPE_CHECKING:

7073 _is_implicitly_boolean = False

7074 inherit_cache = True

7075

7076 element: SelectBase

7077

7078 def __init__(self, element: SelectBase) -> None:

7079 self.element = element

7080 self.type = element._scalar_type()

7081 self._propagate_attrs = element._propagate_attrs

7082

7083 def __getattr__(self, attr: str) -> Any:

7084 return getattr(self.element, attr)

7085

7086 def __getstate__(self) -> Dict[str, Any]:

7087 return {"element": self.element, "type": self.type}

7088

7089 def __setstate__(self, state: Dict[str, Any]) -> None:

7090 self.element = state["element"]

7091 self.type = state["type"]

7092

7093 @property

7094 def columns(self) -> NoReturn:

7095 raise exc.InvalidRequestError(

7096 "Scalar Select expression has no "

7097 "columns; use this object directly "

7098 "within a column-level expression."

7099 )

7100

7101 c = columns

7102

7103 @_generative

7104 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7105 """Apply a WHERE clause to the SELECT statement referred to

7106 by this :class:`_expression.ScalarSelect`.

7107

7108 """

7109 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7110 crit

7111 )

7112 return self

7113

7114 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7115 return self

7116

7117 def _ungroup(self) -> Self:

7118 return self

7119

7120 @_generative

7121 def correlate(

7122 self,

7123 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7124 ) -> Self:

7125 r"""Return a new :class:`_expression.ScalarSelect`

7126 which will correlate the given FROM

7127 clauses to that of an enclosing :class:`_expression.Select`.

7128

7129 This method is mirrored from the :meth:`_sql.Select.correlate` method

7130 of the underlying :class:`_sql.Select`. The method applies the

7131 :meth:_sql.Select.correlate` method, then returns a new

7132 :class:`_sql.ScalarSelect` against that statement.

7133

7134 .. versionadded:: 1.4 Previously, the

7135 :meth:`_sql.ScalarSelect.correlate`

7136 method was only available from :class:`_sql.Select`.

7137

7138 :param \*fromclauses: a list of one or more

7139 :class:`_expression.FromClause`

7140 constructs, or other compatible constructs (i.e. ORM-mapped

7141 classes) to become part of the correlate collection.

7142

7143 .. seealso::

7144

7145 :meth:`_expression.ScalarSelect.correlate_except`

7146

7147 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7148

7149

7150 """

7151 self.element = cast(

7152 "Select[Unpack[TupleAny]]", self.element

7153 ).correlate(*fromclauses)

7154 return self

7155

7156 @_generative

7157 def correlate_except(

7158 self,

7159 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7160 ) -> Self:

7161 r"""Return a new :class:`_expression.ScalarSelect`

7162 which will omit the given FROM

7163 clauses from the auto-correlation process.

7164

7165 This method is mirrored from the

7166 :meth:`_sql.Select.correlate_except` method of the underlying

7167 :class:`_sql.Select`. The method applies the

7168 :meth:_sql.Select.correlate_except` method, then returns a new

7169 :class:`_sql.ScalarSelect` against that statement.

7170

7171 .. versionadded:: 1.4 Previously, the

7172 :meth:`_sql.ScalarSelect.correlate_except`

7173 method was only available from :class:`_sql.Select`.

7174

7175 :param \*fromclauses: a list of one or more

7176 :class:`_expression.FromClause`

7177 constructs, or other compatible constructs (i.e. ORM-mapped

7178 classes) to become part of the correlate-exception collection.

7179

7180 .. seealso::

7181

7182 :meth:`_expression.ScalarSelect.correlate`

7183

7184 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7185

7186

7187 """

7188

7189 self.element = cast(

7190 "Select[Unpack[TupleAny]]", self.element

7191 ).correlate_except(*fromclauses)

7192 return self

7193

7194

7195class Exists(UnaryExpression[bool]):

7196 """Represent an ``EXISTS`` clause.

7197

7198 See :func:`_sql.exists` for a description of usage.

7199

7200 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7201 instance by calling :meth:`_sql.SelectBase.exists`.

7202

7203 """

7204

7205 inherit_cache = True

7206

7207 def __init__(

7208 self,

7209 __argument: Optional[

7210 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7211 ] = None,

7212 /,

7213 ):

7214 s: ScalarSelect[Any]

7215

7216 # TODO: this seems like we should be using coercions for this

7217 if __argument is None:

7218 s = Select(literal_column("*")).scalar_subquery()

7219 elif isinstance(__argument, SelectBase):

7220 s = __argument.scalar_subquery()

7221 s._propagate_attrs = __argument._propagate_attrs

7222 elif isinstance(__argument, ScalarSelect):

7223 s = __argument

7224 else:

7225 s = Select(__argument).scalar_subquery()

7226

7227 UnaryExpression.__init__(

7228 self,

7229 s,

7230 operator=operators.exists,

7231 type_=type_api.BOOLEANTYPE,

7232 )

7233

7234 @util.ro_non_memoized_property

7235 def _from_objects(self) -> List[FromClause]:

7236 return []

7237

7238 def _regroup(

7239 self,

7240 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7241 ) -> ScalarSelect[Any]:

7242

7243 assert isinstance(self.element, ScalarSelect)

7244 element = self.element.element

7245 if not isinstance(element, Select):

7246 raise exc.InvalidRequestError(

7247 "Can only apply this operation to a plain SELECT construct"

7248 )

7249 new_element = fn(element)

7250

7251 return_value = new_element.scalar_subquery()

7252 return return_value

7253

7254 def select(self) -> Select[bool]:

7255 r"""Return a SELECT of this :class:`_expression.Exists`.

7256

7257 e.g.::

7258

7259 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7260

7261 This will produce a statement resembling:

7262

7263 .. sourcecode:: sql

7264

7265 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7266

7267 .. seealso::

7268

7269 :func:`_expression.select` - general purpose

7270 method which allows for arbitrary column lists.

7271

7272 """ # noqa

7273

7274 return Select(self)

7275

7276 def correlate(

7277 self,

7278 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7279 ) -> Self:

7280 """Apply correlation to the subquery noted by this

7281 :class:`_sql.Exists`.

7282

7283 .. seealso::

7284

7285 :meth:`_sql.ScalarSelect.correlate`

7286

7287 """

7288 e = self._clone()

7289 e.element = self._regroup(

7290 lambda element: element.correlate(*fromclauses)

7291 )

7292 return e

7293

7294 def correlate_except(

7295 self,

7296 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7297 ) -> Self:

7298 """Apply correlation to the subquery noted by this

7299 :class:`_sql.Exists`.

7300

7301 .. seealso::

7302

7303 :meth:`_sql.ScalarSelect.correlate_except`

7304

7305 """

7306 e = self._clone()

7307 e.element = self._regroup(

7308 lambda element: element.correlate_except(*fromclauses)

7309 )

7310 return e

7311

7312 def select_from(self, *froms: _FromClauseArgument) -> Self:

7313 """Return a new :class:`_expression.Exists` construct,

7314 applying the given

7315 expression to the :meth:`_expression.Select.select_from`

7316 method of the select

7317 statement contained.

7318

7319 .. note:: it is typically preferable to build a :class:`_sql.Select`

7320 statement first, including the desired WHERE clause, then use the

7321 :meth:`_sql.SelectBase.exists` method to produce an

7322 :class:`_sql.Exists` object at once.

7323

7324 """

7325 e = self._clone()

7326 e.element = self._regroup(lambda element: element.select_from(*froms))

7327 return e

7328

7329 def with_hint(

7330 self,

7331 selectable: _FromClauseArgument,

7332 text: str,

7333 dialect_name: str = "*",

7334 ) -> Self:

7335 r"""Return a new :class:`_expression.Exists` construct, applying

7336 the given arguments to the

7337 :meth:`_expression.Select.with_hint` method of the select

7338 statement contained.

7339

7340 The hint is therefore rendered against the SELECT that's enclosed

7341 by the EXISTS expression. This includes :class:`_expression.Exists`

7342 objects that were generated by ORM constructs such as

7343 :meth:`_orm.PropComparator.any` and

7344 :meth:`_orm.PropComparator.has`::

7345

7346 stmt = select(User).where(

7347 User.addresses.any().with_hint(

7348 Address.__table__, "WITH (NOLOCK)", "mssql"

7349 )

7350 )

7351

7352 The above statement renders as:

7353

7354 .. sourcecode:: sql

7355

7356 SELECT users.id, users.name

7357 FROM users

7358 WHERE EXISTS (SELECT 1

7359 FROM addresses WITH (NOLOCK)

7360 WHERE users.id = addresses.user_id)

7361

7362 .. versionadded:: 2.1

7363

7364 .. seealso::

7365

7366 :meth:`_expression.Select.with_hint`

7367

7368 :meth:`_expression.Exists.with_statement_hint`

7369

7370 """

7371 e = self._clone()

7372 e.element = self._regroup(

7373 lambda element: element.with_hint(selectable, text, dialect_name)

7374 )

7375 return e

7376

7377 def with_statement_hint(self, text: str, dialect_name: str = "*") -> Self:

7378 r"""Return a new :class:`_expression.Exists` construct, applying

7379 the given arguments to the

7380 :meth:`_expression.Select.with_statement_hint` method of the select

7381 statement contained.

7382

7383 The hint is therefore rendered at the trailing end of the SELECT

7384 that's enclosed by the EXISTS expression, rather than at the end of

7385 the enclosing statement. As with

7386 :meth:`_expression.Exists.with_hint`, this includes

7387 :class:`_expression.Exists` objects that were generated by ORM

7388 constructs such as :meth:`_orm.PropComparator.any` and

7389 :meth:`_orm.PropComparator.has`::

7390

7391 has_address = User.addresses.any().with_statement_hint(

7392 "WITH (NOLOCK)", "mssql"

7393 )

7394 stmt = select(User).where(has_address)

7395

7396 The above statement renders as:

7397

7398 .. sourcecode:: sql

7399

7400 SELECT users.id, users.name

7401 FROM users

7402 WHERE EXISTS (SELECT 1

7403 FROM addresses

7404 WHERE users.id = addresses.user_id WITH (NOLOCK))

7405

7406 .. versionadded:: 2.1

7407

7408 .. seealso::

7409

7410 :meth:`_expression.Select.with_statement_hint`

7411

7412 :meth:`_expression.Exists.with_hint`

7413

7414 """

7415 e = self._clone()

7416 e.element = self._regroup(

7417 lambda element: element.with_statement_hint(text, dialect_name)

7418 )

7419 return e

7420

7421 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7422 """Return a new :func:`_expression.exists` construct with the

7423 given expression added to

7424 its WHERE clause, joined to the existing clause via AND, if any.

7425

7426

7427 .. note:: it is typically preferable to build a :class:`_sql.Select`

7428 statement first, including the desired WHERE clause, then use the

7429 :meth:`_sql.SelectBase.exists` method to produce an

7430 :class:`_sql.Exists` object at once.

7431

7432 """

7433 e = self._clone()

7434 e.element = self._regroup(lambda element: element.where(*clause))

7435 return e

7436

7437

7438class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7439 """Wrap a :class:`_expression.TextClause` construct within a

7440 :class:`_expression.SelectBase`

7441 interface.

7442

7443 This allows the :class:`_expression.TextClause` object to gain a

7444 ``.c`` collection

7445 and other FROM-like capabilities such as

7446 :meth:`_expression.FromClause.alias`,

7447 :meth:`_expression.SelectBase.cte`, etc.

7448

7449 The :class:`_expression.TextualSelect` construct is produced via the

7450 :meth:`_expression.TextClause.columns`

7451 method - see that method for details.

7452

7453 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7454 class was renamed

7455 from ``TextAsFrom``, to more correctly suit its role as a

7456 SELECT-oriented object and not a FROM clause.

7457

7458 .. seealso::

7459

7460 :func:`_expression.text`

7461

7462 :meth:`_expression.TextClause.columns` - primary creation interface.

7463

7464 """

7465

7466 __visit_name__ = "textual_select"

7467

7468 _label_style = LABEL_STYLE_NONE

7469

7470 _traverse_internals: _TraverseInternalsType = (

7471 [

7472 ("element", InternalTraversal.dp_clauseelement),

7473 ("column_args", InternalTraversal.dp_clauseelement_list),

7474 ]

7475 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7476 + HasCTE._has_ctes_traverse_internals

7477 + ExecutableStatement._executable_traverse_internals

7478 )

7479

7480 _is_textual = True

7481

7482 is_text = True

7483 is_select = True

7484

7485 def __init__(

7486 self,

7487 text: TextClause,

7488 columns: List[_ColumnExpressionArgument[Any]],

7489 positional: bool = False,

7490 ) -> None:

7491 self._init(

7492 text,

7493 # convert for ORM attributes->columns, etc

7494 [

7495 coercions.expect(roles.LabeledColumnExprRole, c)

7496 for c in columns

7497 ],

7498 positional,

7499 )

7500

7501 def _init(

7502 self,

7503 text: AbstractTextClause,

7504 columns: List[NamedColumn[Any]],

7505 positional: bool = False,

7506 ) -> None:

7507 self.element = text

7508 self.column_args = columns

7509 self.positional = positional

7510

7511 @HasMemoized_ro_memoized_attribute

7512 def selected_columns(

7513 self,

7514 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7515 """A :class:`_expression.ColumnCollection`

7516 representing the columns that

7517 this SELECT statement or similar construct returns in its result set,

7518 not including :class:`_sql.TextClause` constructs.

7519

7520 This collection differs from the :attr:`_expression.FromClause.columns`

7521 collection of a :class:`_expression.FromClause` in that the columns

7522 within this collection cannot be directly nested inside another SELECT

7523 statement; a subquery must be applied first which provides for the

7524 necessary parenthesization required by SQL.

7525

7526 For a :class:`_expression.TextualSelect` construct, the collection

7527 contains the :class:`_expression.ColumnElement` objects that were

7528 passed to the constructor, typically via the

7529 :meth:`_expression.TextClause.columns` method.

7530

7531

7532 .. versionadded:: 1.4

7533

7534 """

7535 return WriteableColumnCollection(

7536 (c.key, c) for c in self.column_args

7537 ).as_readonly()

7538

7539 @util.ro_non_memoized_property

7540 def _all_selected_columns(self) -> _SelectIterable:

7541 return self.column_args

7542

7543 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7544 return self

7545

7546 def _ensure_disambiguated_names(self) -> TextualSelect:

7547 return self

7548

7549 @_generative

7550 def bindparams(

7551 self,

7552 *binds: BindParameter[Any],

7553 **bind_as_values: Any,

7554 ) -> Self:

7555 self.element = self.element.bindparams(*binds, **bind_as_values)

7556 return self

7557

7558 def _generate_fromclause_column_proxies(

7559 self,

7560 fromclause: FromClause,

7561 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

7562 primary_key: ColumnSet,

7563 foreign_keys: Set[KeyedColumnElement[Any]],

7564 *,

7565 proxy_compound_columns: Optional[

7566 Iterable[Sequence[ColumnElement[Any]]]

7567 ] = None,

7568 ) -> None:

7569 if TYPE_CHECKING:

7570 assert isinstance(fromclause, Subquery)

7571

7572 if proxy_compound_columns:

7573 columns._populate_separate_keys(

7574 c._make_proxy(

7575 fromclause,

7576 compound_select_cols=extra_cols,

7577 primary_key=primary_key,

7578 foreign_keys=foreign_keys,

7579 )

7580 for c, extra_cols in zip(

7581 self.column_args, proxy_compound_columns

7582 )

7583 )

7584 else:

7585 columns._populate_separate_keys(

7586 c._make_proxy(

7587 fromclause,

7588 primary_key=primary_key,

7589 foreign_keys=foreign_keys,

7590 )

7591 for c in self.column_args

7592 )

7593

7594 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7595 return self.column_args[0].type

7596

7597

7598TextAsFrom = TextualSelect

7599"""Backwards compatibility with the previous name"""

7600

7601

7602class AnnotatedFromClause(Annotated):

7603 def _copy_internals(

7604 self,

7605 _annotations_traversal: bool = False,

7606 ind_cols_on_fromclause: bool = False,

7607 **kw: Any,

7608 ) -> None:

7609 super()._copy_internals(**kw)

7610

7611 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7612 # the traversals used by annotations for these cases are not currently

7613 # designed around expecting that inner elements inside of

7614 # AnnotatedFromClause's element are also deep copied, so skip for these

7615 # cases. in other cases such as plain visitors.cloned_traverse(), we

7616 # expect this to happen. see issue #12915

7617 if not _annotations_traversal:

7618 ee = self._Annotated__element # type: ignore[attr-defined]

7619 ee._copy_internals(**kw)

7620

7621 if ind_cols_on_fromclause:

7622 # passed from annotations._deep_annotate(). See that function

7623 # for notes

7624 ee = self._Annotated__element # type: ignore[attr-defined]

7625 self.c = ee.__class__.c.fget(self) # type: ignore[misc]

7626

7627 @util.ro_memoized_property

7628 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7629 """proxy the .c collection of the underlying FromClause.

7630

7631 Originally implemented in 2008 as a simple load of the .c collection

7632 when the annotated construct was created (see d3621ae961a), in modern

7633 SQLAlchemy versions this can be expensive for statements constructed

7634 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7635 it, which works just as well.

7636

7637 Two different use cases seem to require the collection either copied

7638 from the underlying one, or unique to this AnnotatedFromClause.

7639

7640 See test_selectable->test_annotated_corresponding_column

7641

7642 """

7643 ee = self._Annotated__element # type: ignore[attr-defined]

7644 return ee.c # type: ignore[no-any-return]