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

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

1158 FROM table1 JOIN table2 ON true

1159

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

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

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

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

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

1165

1166

1167 .. versionadded:: 1.4

1168

1169 """ # noqa: E501

1170

1171 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1174 when generating the columns clause of a SELECT statement.

1175

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

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

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

1179

1180 .. sourcecode:: pycon+sql

1181

1182 >>> from sqlalchemy import (

1183 ... table,

1184 ... column,

1185 ... select,

1186 ... true,

1187 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1188 ... )

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

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

1191 >>> print(

1192 ... select(table1, table2)

1193 ... .join(table2, true())

1194 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1195 ... )

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

1197 FROM table1 JOIN table2 ON true

1198

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

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

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

1202

1203 .. versionadded:: 1.4

1204

1205 """ # noqa: E501

1206

1207 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1208 """The default label style, refers to

1209 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1210

1211 .. versionadded:: 1.4

1212

1213 """

1214

1215 LABEL_STYLE_LEGACY_ORM = 3

1216

1217

1218(

1219 LABEL_STYLE_NONE,

1220 LABEL_STYLE_TABLENAME_PLUS_COL,

1221 LABEL_STYLE_DISAMBIGUATE_ONLY,

1222 _,

1223) = list(SelectLabelStyle)

1224

1225LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1226

1227

1228class Join(roles.DMLTableRole, FromClause):

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

1230 :class:`_expression.FromClause`

1231 elements.

1232

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

1234 is the module-level

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

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

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

1238 :class:`_schema.Table`).

1239

1240 .. seealso::

1241

1242 :func:`_expression.join`

1243

1244 :meth:`_expression.FromClause.join`

1245

1246 """

1247

1248 __visit_name__ = "join"

1249

1250 _traverse_internals: _TraverseInternalsType = [

1251 ("left", InternalTraversal.dp_clauseelement),

1252 ("right", InternalTraversal.dp_clauseelement),

1253 ("onclause", InternalTraversal.dp_clauseelement),

1254 ("isouter", InternalTraversal.dp_boolean),

1255 ("full", InternalTraversal.dp_boolean),

1256 ]

1257

1258 _is_join = True

1259

1260 left: FromClause

1261 right: FromClause

1262 onclause: Optional[ColumnElement[bool]]

1263 isouter: bool

1264 full: bool

1265

1266 def __init__(

1267 self,

1268 left: _FromClauseArgument,

1269 right: _FromClauseArgument,

1270 onclause: Optional[_OnClauseArgument] = None,

1271 isouter: bool = False,

1272 full: bool = False,

1273 ):

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

1275

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

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

1278 :class:`_expression.FromClause` object.

1279

1280 """

1281

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

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

1284 # annotated objects. test_orm.py -> test_fetch_results

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

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

1287 # callcounts for a single compilation in that particular test

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

1289 # 29200 -> 30373

1290

1291 self.left = coercions.expect(

1292 roles.FromClauseRole,

1293 left,

1294 )

1295 self.right = coercions.expect(

1296 roles.FromClauseRole,

1297 right,

1298 ).self_group()

1299

1300 if onclause is None:

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

1302 else:

1303 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1304 # not merged yet

1305 self.onclause = coercions.expect(

1306 roles.OnClauseRole, onclause

1307 ).self_group(against=operators._asbool)

1308

1309 self.isouter = isouter

1310 self.full = full

1311

1312 @util.ro_non_memoized_property

1313 def description(self) -> str:

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

1315 self.left.description,

1316 id(self.left),

1317 self.right.description,

1318 id(self.right),

1319 )

1320

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

1322 return (

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

1324 # as well

1325 hash(fromclause) == hash(self)

1326 or self.left.is_derived_from(fromclause)

1327 or self.right.is_derived_from(fromclause)

1328 )

1329

1330 def self_group(

1331 self, against: Optional[OperatorType] = None

1332 ) -> FromGrouping:

1333 return FromGrouping(self)

1334

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

1336 def _populate_column_collection(

1337 self,

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

1339 primary_key: ColumnSet,

1340 foreign_keys: Set[KeyedColumnElement[Any]],

1341 ) -> None:

1342 sqlutil = util.preloaded.sql_util

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

1344 c for c in self.right.c

1345 ]

1346

1347 primary_key.extend(

1348 sqlutil.reduce_columns(

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

1350 )

1351 )

1352 columns._populate_separate_keys(

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

1354 )

1355 foreign_keys.update(

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

1357 )

1358

1359 def _copy_internals(

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

1361 ) -> None:

1362 # see Select._copy_internals() for similar concept

1363

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

1365 # determine the new FROM clauses

1366 all_the_froms = set(

1367 itertools.chain(

1368 _from_objects(self.left),

1369 _from_objects(self.right),

1370 )

1371 )

1372

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

1374 # cache used by the clone function

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

1376

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

1378 # ColumnClause with parent table referring to those

1379 # replaced FromClause objects

1380 def replace(

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

1382 **kw: Any,

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

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

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

1386 return newelem

1387 return None

1388

1389 kw["replace"] = replace

1390

1391 # run normal _copy_internals. the clones for

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

1393 # cache

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

1395

1396 self._reset_memoizations()

1397

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

1399 super()._refresh_for_new_column(column)

1400 self.left._refresh_for_new_column(column)

1401 self.right._refresh_for_new_column(column)

1402

1403 def _match_primaries(

1404 self,

1405 left: FromClause,

1406 right: FromClause,

1407 ) -> ColumnElement[bool]:

1408 if isinstance(left, Join):

1409 left_right = left.right

1410 else:

1411 left_right = None

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

1413

1414 @classmethod

1415 def _join_condition(

1416 cls,

1417 a: FromClause,

1418 b: FromClause,

1419 *,

1420 a_subset: Optional[FromClause] = None,

1421 consider_as_foreign_keys: Optional[

1422 AbstractSet[ColumnClause[Any]]

1423 ] = None,

1424 ) -> ColumnElement[bool]:

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

1426

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

1428

1429 """

1430 constraints = cls._joincond_scan_left_right(

1431 a, a_subset, b, consider_as_foreign_keys

1432 )

1433

1434 if len(constraints) > 1:

1435 cls._joincond_trim_constraints(

1436 a, b, constraints, consider_as_foreign_keys

1437 )

1438

1439 if len(constraints) == 0:

1440 if isinstance(b, FromGrouping):

1441 hint = (

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

1443 "subquery using alias()?"

1444 )

1445 else:

1446 hint = ""

1447 raise exc.NoForeignKeysError(

1448 "Can't find any foreign key relationships "

1449 "between '%s' and '%s'.%s"

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

1451 )

1452

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

1454 if len(crit) == 1:

1455 return crit[0]

1456 else:

1457 return and_(*crit)

1458

1459 @classmethod

1460 def _can_join(

1461 cls,

1462 left: FromClause,

1463 right: FromClause,

1464 *,

1465 consider_as_foreign_keys: Optional[

1466 AbstractSet[ColumnClause[Any]]

1467 ] = None,

1468 ) -> bool:

1469 if isinstance(left, Join):

1470 left_right = left.right

1471 else:

1472 left_right = None

1473

1474 constraints = cls._joincond_scan_left_right(

1475 a=left,

1476 b=right,

1477 a_subset=left_right,

1478 consider_as_foreign_keys=consider_as_foreign_keys,

1479 )

1480

1481 return bool(constraints)

1482

1483 @classmethod

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

1485 def _joincond_scan_left_right(

1486 cls,

1487 a: FromClause,

1488 a_subset: Optional[FromClause],

1489 b: FromClause,

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

1491 ) -> collections.defaultdict[

1492 Optional[ForeignKeyConstraint],

1493 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1494 ]:

1495 sql_util = util.preloaded.sql_util

1496

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

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

1499

1500 constraints: collections.defaultdict[

1501 Optional[ForeignKeyConstraint],

1502 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1503 ] = collections.defaultdict(list)

1504

1505 for left in (a_subset, a):

1506 if left is None:

1507 continue

1508 for fk in sorted(

1509 b.foreign_keys,

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

1511 ):

1512 if (

1513 consider_as_foreign_keys is not None

1514 and fk.parent not in consider_as_foreign_keys

1515 ):

1516 continue

1517 try:

1518 col = fk.get_referent(left)

1519 except exc.NoReferenceError as nrte:

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

1521 if nrte.table_name in table_names:

1522 raise

1523 else:

1524 continue

1525

1526 if col is not None:

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

1528 if left is not b:

1529 for fk in sorted(

1530 left.foreign_keys,

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

1532 ):

1533 if (

1534 consider_as_foreign_keys is not None

1535 and fk.parent not in consider_as_foreign_keys

1536 ):

1537 continue

1538 try:

1539 col = fk.get_referent(b)

1540 except exc.NoReferenceError as nrte:

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

1542 if nrte.table_name in table_names:

1543 raise

1544 else:

1545 continue

1546

1547 if col is not None:

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

1549 if constraints:

1550 break

1551 return constraints

1552

1553 @classmethod

1554 def _joincond_trim_constraints(

1555 cls,

1556 a: FromClause,

1557 b: FromClause,

1558 constraints: Dict[Any, Any],

1559 consider_as_foreign_keys: Optional[Any],

1560 ) -> None:

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

1562 # to include just those FKCs that match exactly to

1563 # "consider_as_foreign_keys".

1564 if consider_as_foreign_keys:

1565 for const in list(constraints):

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

1567 consider_as_foreign_keys

1568 ):

1569 del constraints[const]

1570

1571 # if still multiple constraints, but

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

1573 if len(constraints) > 1:

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

1575 if len(dedupe) == 1:

1576 key = list(constraints)[0]

1577 constraints = {key: constraints[key]}

1578

1579 if len(constraints) != 1:

1580 raise exc.AmbiguousForeignKeysError(

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

1582 "tables have more than one foreign key "

1583 "constraint relationship between them. "

1584 "Please specify the 'onclause' of this "

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

1586 )

1587

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

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

1590 :class:`_expression.Join`.

1591

1592 E.g.::

1593

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

1595

1596 stmt = stmt.select()

1597

1598 The above will produce a SQL string resembling:

1599

1600 .. sourcecode:: sql

1601

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

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

1604

1605 """

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

1607

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

1609 def _anonymous_fromclause(

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

1611 ) -> TODO_Any:

1612 sqlutil = util.preloaded.sql_util

1613 if flat:

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

1615 left_name = name # will recurse

1616 else:

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

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

1619 else:

1620 left_name = name

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

1622 right_name = name # will recurse

1623 else:

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

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

1626 else:

1627 right_name = name

1628 left_a, right_a = (

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

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

1631 )

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

1633 sqlutil.ClauseAdapter(right_a)

1634 )

1635

1636 return left_a.join(

1637 right_a,

1638 adapter.traverse(self.onclause),

1639 isouter=self.isouter,

1640 full=self.full,

1641 )

1642 else:

1643 return (

1644 self.select()

1645 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1646 .correlate(None)

1647 .alias(name)

1648 )

1649

1650 @util.ro_non_memoized_property

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

1652 return itertools.chain(

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

1654 )

1655

1656 @util.ro_non_memoized_property

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

1658 self_list: List[FromClause] = [self]

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

1660

1661

1662class NoInit:

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

1664 raise NotImplementedError(

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

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

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

1668 "selectable objects."

1669 % (

1670 self.__class__.__name__,

1671 self.__class__.__name__.lower(),

1672 self.__class__.__name__.lower(),

1673 )

1674 )

1675

1676

1677class LateralFromClause(NamedFromClause):

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

1679

1680

1681# FromClause ->

1682# AliasedReturnsRows

1683# -> Alias only for FromClause

1684# -> Subquery only for SelectBase

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

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

1687# w/ non-deprecated coercion

1688# -> TableSample -> only for FromClause

1689

1690

1691class AliasedReturnsRows(NoInit, NamedFromClause):

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

1693 selectables."""

1694

1695 _is_from_container = True

1696

1697 _supports_derived_columns = False

1698

1699 element: ReturnsRows

1700

1701 _traverse_internals: _TraverseInternalsType = [

1702 ("element", InternalTraversal.dp_clauseelement),

1703 ("name", InternalTraversal.dp_anon_name),

1704 ]

1705

1706 @classmethod

1707 def _construct(

1708 cls,

1709 selectable: Any,

1710 *,

1711 name: Optional[str] = None,

1712 **kw: Any,

1713 ) -> Self:

1714 obj = cls.__new__(cls)

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

1716 return obj

1717

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

1719 self.element = coercions.expect(

1720 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1721 )

1722 self.element = selectable

1723 self._orig_name = name

1724 if name is None:

1725 if (

1726 isinstance(selectable, FromClause)

1727 and selectable.named_with_column

1728 ):

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

1730 if isinstance(name, _anonymous_label):

1731 name = None

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

1733 self.name = name

1734

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

1736 super()._refresh_for_new_column(column)

1737 self.element._refresh_for_new_column(column)

1738

1739 def _populate_column_collection(

1740 self,

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

1742 primary_key: ColumnSet,

1743 foreign_keys: Set[KeyedColumnElement[Any]],

1744 ) -> None:

1745 self.element._generate_fromclause_column_proxies(

1746 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1747 )

1748

1749 @util.ro_non_memoized_property

1750 def description(self) -> str:

1751 name = self.name

1752 if isinstance(name, _anonymous_label):

1753 return "anon_1"

1754

1755 return name

1756

1757 @util.ro_non_memoized_property

1758 def implicit_returning(self) -> bool:

1759 return self.element.implicit_returning # type: ignore

1760

1761 @property

1762 def original(self) -> ReturnsRows:

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

1764 return self.element

1765

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

1767 if fromclause in self._cloned_set:

1768 return True

1769 return self.element.is_derived_from(fromclause)

1770

1771 def _copy_internals(

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

1773 ) -> None:

1774 existing_element = self.element

1775

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

1777

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

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

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

1781 # performance.

1782 if existing_element is not self.element:

1783 self._reset_column_collection()

1784

1785 @property

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

1787 return [self]

1788

1789

1790class FromClauseAlias(AliasedReturnsRows):

1791 element: FromClause

1792

1793 @util.ro_non_memoized_property

1794 def description(self) -> str:

1795 name = self.name

1796 if isinstance(name, _anonymous_label):

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

1798

1799 return name

1800

1801

1802class Alias(roles.DMLTableRole, FromClauseAlias):

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

1804

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

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

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

1808

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

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

1811 method available

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

1813

1814 .. seealso::

1815

1816 :meth:`_expression.FromClause.alias`

1817

1818 """

1819

1820 __visit_name__ = "alias"

1821

1822 inherit_cache = True

1823

1824 element: FromClause

1825

1826 @classmethod

1827 def _factory(

1828 cls,

1829 selectable: FromClause,

1830 name: Optional[str] = None,

1831 flat: bool = False,

1832 ) -> NamedFromClause:

1833 return coercions.expect(

1834 roles.FromClauseRole, selectable, allow_select=True

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

1836

1837

1838class TableValuedAlias(LateralFromClause, Alias):

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

1840

1841 This construct provides for a SQL function that returns columns

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

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

1844 method, e.g.:

1845

1846 .. sourcecode:: pycon+sql

1847

1848 >>> from sqlalchemy import select, func

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

1850 ... "value"

1851 ... )

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

1853 {printsql}SELECT anon_1.value

1854 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1855

1856 .. versionadded:: 1.4.0b2

1857

1858 .. seealso::

1859

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

1861

1862 """ # noqa: E501

1863

1864 __visit_name__ = "table_valued_alias"

1865

1866 _supports_derived_columns = True

1867 _render_derived = False

1868 _render_derived_w_types = False

1869 joins_implicitly = False

1870

1871 _traverse_internals: _TraverseInternalsType = [

1872 ("element", InternalTraversal.dp_clauseelement),

1873 ("name", InternalTraversal.dp_anon_name),

1874 ("_tableval_type", InternalTraversal.dp_type),

1875 ("_render_derived", InternalTraversal.dp_boolean),

1876 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1877 ]

1878

1879 def _init(

1880 self,

1881 selectable: Any,

1882 *,

1883 name: Optional[str] = None,

1884 table_value_type: Optional[TableValueType] = None,

1885 joins_implicitly: bool = False,

1886 ) -> None:

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

1888

1889 self.joins_implicitly = joins_implicitly

1890 self._tableval_type = (

1891 type_api.TABLEVALUE

1892 if table_value_type is None

1893 else table_value_type

1894 )

1895

1896 @HasMemoized.memoized_attribute

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

1898 """Return a column expression representing this

1899 :class:`_sql.TableValuedAlias`.

1900

1901 This accessor is used to implement the

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

1903 method for further details.

1904

1905 E.g.:

1906

1907 .. sourcecode:: pycon+sql

1908

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

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

1911

1912 .. seealso::

1913

1914 :meth:`_functions.FunctionElement.column_valued`

1915

1916 """

1917

1918 return TableValuedColumn(self, self._tableval_type)

1919

1920 def alias(

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

1922 ) -> TableValuedAlias:

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

1924

1925 This creates a distinct FROM object that will be distinguished

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

1927

1928 """

1929

1930 tva: TableValuedAlias = TableValuedAlias._construct(

1931 self,

1932 name=name,

1933 table_value_type=self._tableval_type,

1934 joins_implicitly=self.joins_implicitly,

1935 )

1936

1937 if self._render_derived:

1938 tva._render_derived = True

1939 tva._render_derived_w_types = self._render_derived_w_types

1940

1941 return tva

1942

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

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

1945 set, so that it renders as LATERAL.

1946

1947 .. seealso::

1948

1949 :func:`_expression.lateral`

1950

1951 """

1952 tva = self.alias(name=name)

1953 tva._is_lateral = True

1954 return tva

1955

1956 def render_derived(

1957 self,

1958 name: Optional[str] = None,

1959 with_types: bool = False,

1960 ) -> TableValuedAlias:

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

1962

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

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

1965

1966 .. sourcecode:: pycon+sql

1967

1968 >>> print(

1969 ... select(

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

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

1972 ... .render_derived()

1973 ... )

1974 ... )

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

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

1977

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

1979 the alias expression (this syntax currently applies to the

1980 PostgreSQL database):

1981

1982 .. sourcecode:: pycon+sql

1983

1984 >>> print(

1985 ... select(

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

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

1988 ... .render_derived(with_types=True)

1989 ... )

1990 ... )

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

1992 AS anon_1(a INTEGER, b VARCHAR)

1993

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

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

1996

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

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

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

2000

2001 """ # noqa: E501

2002

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

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

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

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

2007 # (just saw it happen on CI)

2008

2009 # construct against original to prevent memory growth

2010 # for repeated generations

2011 new_alias: TableValuedAlias = TableValuedAlias._construct(

2012 self.element,

2013 name=name,

2014 table_value_type=self._tableval_type,

2015 joins_implicitly=self.joins_implicitly,

2016 )

2017 new_alias._render_derived = True

2018 new_alias._render_derived_w_types = with_types

2019 return new_alias

2020

2021

2022class Lateral(FromClauseAlias, LateralFromClause):

2023 """Represent a LATERAL subquery.

2024

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

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

2027 method available

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

2029

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

2031 PostgreSQL versions provide support for this keyword.

2032

2033 .. seealso::

2034

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

2036

2037 """

2038

2039 __visit_name__ = "lateral"

2040 _is_lateral = True

2041

2042 inherit_cache = True

2043

2044 @classmethod

2045 def _factory(

2046 cls,

2047 selectable: Union[SelectBase, _FromClauseArgument],

2048 name: Optional[str] = None,

2049 ) -> LateralFromClause:

2050 return coercions.expect(

2051 roles.FromClauseRole, selectable, explicit_subquery=True

2052 ).lateral(name=name)

2053

2054

2055class TableSample(FromClauseAlias):

2056 """Represent a TABLESAMPLE clause.

2057

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

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

2060 method

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

2062

2063 .. seealso::

2064

2065 :func:`_expression.tablesample`

2066

2067 """

2068

2069 __visit_name__ = "tablesample"

2070

2071 _traverse_internals: _TraverseInternalsType = (

2072 AliasedReturnsRows._traverse_internals

2073 + [

2074 ("sampling", InternalTraversal.dp_clauseelement),

2075 ("seed", InternalTraversal.dp_clauseelement),

2076 ]

2077 )

2078

2079 @classmethod

2080 def _factory(

2081 cls,

2082 selectable: _FromClauseArgument,

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

2084 name: Optional[str] = None,

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

2086 ) -> TableSample:

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

2088 sampling, name=name, seed=seed

2089 )

2090

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

2092 def _init( # type: ignore[override]

2093 self,

2094 selectable: Any,

2095 *,

2096 name: Optional[str] = None,

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

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

2099 ) -> None:

2100 assert sampling is not None

2101 functions = util.preloaded.sql_functions

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

2103 sampling = functions.func.system(sampling)

2104

2105 self.sampling: Function[Any] = sampling

2106 self.seed = seed

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

2108

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

2110 return self.sampling

2111

2112

2113class CTE(

2114 roles.DMLTableRole,

2115 roles.IsCTERole,

2116 Generative,

2117 HasPrefixes,

2118 HasSuffixes,

2119 AliasedReturnsRows,

2120):

2121 """Represent a Common Table Expression.

2122

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

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

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

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

2127 :class:`_sql.Update` and

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

2129 usage details on CTEs.

2130

2131 .. seealso::

2132

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

2134

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

2136

2137 """

2138

2139 __visit_name__ = "cte"

2140

2141 _traverse_internals: _TraverseInternalsType = (

2142 AliasedReturnsRows._traverse_internals

2143 + [

2144 ("_cte_alias", InternalTraversal.dp_clauseelement),

2145 ("_restates", InternalTraversal.dp_clauseelement),

2146 ("recursive", InternalTraversal.dp_boolean),

2147 ("nesting", InternalTraversal.dp_boolean),

2148 ]

2149 + HasPrefixes._has_prefixes_traverse_internals

2150 + HasSuffixes._has_suffixes_traverse_internals

2151 )

2152

2153 element: HasCTE

2154

2155 @classmethod

2156 def _factory(

2157 cls,

2158 selectable: HasCTE,

2159 name: Optional[str] = None,

2160 recursive: bool = False,

2161 ) -> CTE:

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

2163 or Common Table Expression instance.

2164

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

2166

2167 """

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

2169 name=name, recursive=recursive

2170 )

2171

2172 def _init(

2173 self,

2174 selectable: Select[Any],

2175 *,

2176 name: Optional[str] = None,

2177 recursive: bool = False,

2178 nesting: bool = False,

2179 _cte_alias: Optional[CTE] = None,

2180 _restates: Optional[CTE] = None,

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

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

2183 ) -> None:

2184 self.recursive = recursive

2185 self.nesting = nesting

2186 self._cte_alias = _cte_alias

2187 # Keep recursivity reference with union/union_all

2188 self._restates = _restates

2189 if _prefixes:

2190 self._prefixes = _prefixes

2191 if _suffixes:

2192 self._suffixes = _suffixes

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

2194

2195 def _populate_column_collection(

2196 self,

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

2198 primary_key: ColumnSet,

2199 foreign_keys: Set[KeyedColumnElement[Any]],

2200 ) -> None:

2201 if self._cte_alias is not None:

2202 self._cte_alias._generate_fromclause_column_proxies(

2203 self,

2204 columns,

2205 primary_key=primary_key,

2206 foreign_keys=foreign_keys,

2207 )

2208 else:

2209 self.element._generate_fromclause_column_proxies(

2210 self,

2211 columns,

2212 primary_key=primary_key,

2213 foreign_keys=foreign_keys,

2214 )

2215

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

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

2218 :class:`_expression.CTE`.

2219

2220 This method is a CTE-specific specialization of the

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

2222

2223 .. seealso::

2224

2225 :ref:`tutorial_using_aliases`

2226

2227 :func:`_expression.alias`

2228

2229 """

2230 return CTE._construct(

2231 self.element,

2232 name=name,

2233 recursive=self.recursive,

2234 nesting=self.nesting,

2235 _cte_alias=self,

2236 _prefixes=self._prefixes,

2237 _suffixes=self._suffixes,

2238 )

2239

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

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

2242 of the original CTE against the given selectables provided

2243 as positional arguments.

2244

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

2246 UNION.

2247

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

2249

2250 .. seealso::

2251

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

2253

2254 """

2255 assert is_select_statement(

2256 self.element

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

2258

2259 return CTE._construct(

2260 self.element.union(*other),

2261 name=self.name,

2262 recursive=self.recursive,

2263 nesting=self.nesting,

2264 _restates=self,

2265 _prefixes=self._prefixes,

2266 _suffixes=self._suffixes,

2267 )

2268

2269 def union_all(

2270 self, *other: _SelectStatementForCompoundArgument[Any]

2271 ) -> CTE:

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

2273 of the original CTE against the given selectables provided

2274 as positional arguments.

2275

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

2277 UNION.

2278

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

2280

2281 .. seealso::

2282

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

2284

2285 """

2286

2287 assert is_select_statement(

2288 self.element

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

2290

2291 return CTE._construct(

2292 self.element.union_all(*other),

2293 name=self.name,

2294 recursive=self.recursive,

2295 nesting=self.nesting,

2296 _restates=self,

2297 _prefixes=self._prefixes,

2298 _suffixes=self._suffixes,

2299 )

2300

2301 def _get_reference_cte(self) -> CTE:

2302 """

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

2304 Updated CTEs should still refer to the original CTE.

2305 This function returns this reference identifier.

2306 """

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

2308

2309

2310class _CTEOpts(NamedTuple):

2311 nesting: bool

2312

2313

2314class _ColumnsPlusNames(NamedTuple):

2315 required_label_name: Optional[str]

2316 """

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

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

2319 """

2320

2321 proxy_key: Optional[str]

2322 """

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

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

2325 select.selected_columns collection

2326 """

2327

2328 fallback_label_name: Optional[str]

2329 """

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

2331 we have to render a label even though

2332 required_label_name was not given

2333 """

2334

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

2336 """

2337 the ColumnElement itself

2338 """

2339

2340 repeated: bool

2341 """

2342 True if this is a duplicate of a previous column

2343 in the list of columns

2344 """

2345

2346

2347class SelectsRows(ReturnsRows):

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

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

2350

2351 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2352

2353 def _generate_columns_plus_names(

2354 self,

2355 anon_for_dupe_key: bool,

2356 cols: Optional[_SelectIterable] = None,

2357 ) -> List[_ColumnsPlusNames]:

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

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

2360 on a :class:`.FromClause`.

2361

2362 This is distinct from the _column_naming_convention generator that's

2363 intended for population of the Select.selected_columns collection,

2364 different rules. the collection returned here calls upon the

2365 _column_naming_convention as well.

2366

2367 """

2368

2369 if cols is None:

2370 cols = self._all_selected_columns

2371

2372 key_naming_convention = SelectState._column_naming_convention(

2373 self._label_style

2374 )

2375

2376 names = {}

2377

2378 result: List[_ColumnsPlusNames] = []

2379 result_append = result.append

2380

2381 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2382 label_style_none = self._label_style is LABEL_STYLE_NONE

2383

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

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

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

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

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

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

2390 dedupe_hash = 1

2391

2392 for c in cols:

2393 repeated = False

2394

2395 if not c._render_label_in_columns_clause:

2396 effective_name = required_label_name = fallback_label_name = (

2397 None

2398 )

2399 elif label_style_none:

2400 if TYPE_CHECKING:

2401 assert is_column_element(c)

2402

2403 effective_name = required_label_name = None

2404 fallback_label_name = c._non_anon_label or c._anon_name_label

2405 else:

2406 if TYPE_CHECKING:

2407 assert is_column_element(c)

2408

2409 if table_qualified:

2410 required_label_name = effective_name = (

2411 fallback_label_name

2412 ) = c._tq_label

2413 else:

2414 effective_name = fallback_label_name = c._non_anon_label

2415 required_label_name = None

2416

2417 if effective_name is None:

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

2419 # not need _expression_label but it isn't

2420 # giving us a clue when to use anon_label instead

2421 expr_label = c._expression_label

2422 if expr_label is None:

2423 repeated = c._anon_name_label in names

2424 names[c._anon_name_label] = c

2425 effective_name = required_label_name = None

2426

2427 if repeated:

2428 # here, "required_label_name" is sent as

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

2430 if table_qualified:

2431 fallback_label_name = (

2432 c._dedupe_anon_tq_label_idx(dedupe_hash)

2433 )

2434 dedupe_hash += 1

2435 else:

2436 fallback_label_name = c._dedupe_anon_label_idx(

2437 dedupe_hash

2438 )

2439 dedupe_hash += 1

2440 else:

2441 fallback_label_name = c._anon_name_label

2442 else:

2443 required_label_name = effective_name = (

2444 fallback_label_name

2445 ) = expr_label

2446

2447 if effective_name is not None:

2448 if TYPE_CHECKING:

2449 assert is_column_element(c)

2450

2451 if effective_name in names:

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

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

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

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

2456 # different column under the same name. apply

2457 # disambiguating label

2458 if table_qualified:

2459 required_label_name = fallback_label_name = (

2460 c._anon_tq_label

2461 )

2462 else:

2463 required_label_name = fallback_label_name = (

2464 c._anon_name_label

2465 )

2466

2467 if anon_for_dupe_key and required_label_name in names:

2468 # here, c._anon_tq_label is definitely unique to

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

2470 # this should always be true.

2471 # this is also an infrequent codepath because

2472 # you need two levels of duplication to be here

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

2474

2475 # the column under the disambiguating label is

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

2477 # subsequent occurrences of the column so that the

2478 # original stays non-ambiguous

2479 if table_qualified:

2480 required_label_name = fallback_label_name = (

2481 c._dedupe_anon_tq_label_idx(dedupe_hash)

2482 )

2483 dedupe_hash += 1

2484 else:

2485 required_label_name = fallback_label_name = (

2486 c._dedupe_anon_label_idx(dedupe_hash)

2487 )

2488 dedupe_hash += 1

2489 repeated = True

2490 else:

2491 names[required_label_name] = c

2492 elif anon_for_dupe_key:

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

2494 # label so that the original stays non-ambiguous

2495 if table_qualified:

2496 required_label_name = fallback_label_name = (

2497 c._dedupe_anon_tq_label_idx(dedupe_hash)

2498 )

2499 dedupe_hash += 1

2500 else:

2501 required_label_name = fallback_label_name = (

2502 c._dedupe_anon_label_idx(dedupe_hash)

2503 )

2504 dedupe_hash += 1

2505 repeated = True

2506 else:

2507 names[effective_name] = c

2508

2509 result_append(

2510 _ColumnsPlusNames(

2511 required_label_name,

2512 key_naming_convention(c),

2513 fallback_label_name,

2514 c,

2515 repeated,

2516 )

2517 )

2518

2519 return result

2520

2521

2522class HasCTE(roles.HasCTERole, SelectsRows):

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

2524

2525 _has_ctes_traverse_internals: _TraverseInternalsType = [

2526 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2527 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2528 ]

2529

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

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

2532

2533 name_cte_columns: bool = False

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

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

2536

2537 .. versionadded:: 2.0.42

2538

2539 """

2540

2541 @_generative

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

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

2544

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

2546 the parent statement such that they will each be unconditionally

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

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

2549

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

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

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

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

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

2555 statement.

2556

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

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

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

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

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

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

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

2564 larger statement.

2565

2566 E.g.::

2567

2568 from sqlalchemy import table, column, select

2569

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

2571

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

2573

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

2575

2576 Would render:

2577

2578 .. sourcecode:: sql

2579

2580 WITH anon_1 AS (

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

2582 )

2583 SELECT t.c1, t.c2

2584 FROM t

2585

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

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

2588 statement.

2589

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

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

2592

2593 from sqlalchemy import table, column

2594 from sqlalchemy.dialects.postgresql import insert

2595

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

2597

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

2599

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

2601 update_statement = insert_stmt.on_conflict_do_update(

2602 index_elements=[t.c.c1],

2603 set_={

2604 "c1": insert_stmt.excluded.c1,

2605 "c2": insert_stmt.excluded.c2,

2606 },

2607 ).add_cte(delete_statement_cte)

2608

2609 print(update_statement)

2610

2611 The above statement renders as:

2612

2613 .. sourcecode:: sql

2614

2615 WITH deletions AS (

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

2617 )

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

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

2620

2621 .. versionadded:: 1.4.21

2622

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

2624

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

2626

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

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

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

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

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

2632 this statement when this flag is given.

2633

2634 .. versionadded:: 2.0

2635

2636 .. seealso::

2637

2638 :paramref:`.HasCTE.cte.nesting`

2639

2640

2641 """ # noqa: E501

2642 opt = _CTEOpts(nest_here)

2643 for cte in ctes:

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

2645 self._independent_ctes += (cte,)

2646 self._independent_ctes_opts += (opt,)

2647 return self

2648

2649 def cte(

2650 self,

2651 name: Optional[str] = None,

2652 recursive: bool = False,

2653 nesting: bool = False,

2654 ) -> CTE:

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

2656 or Common Table Expression instance.

2657

2658 Common table expressions are a SQL standard whereby SELECT

2659 statements can draw upon secondary statements specified along

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

2661 Special semantics regarding UNION can also be employed to

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

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

2664

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

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

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

2668 CTE rows.

2669

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

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

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

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

2674

2675 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2677 method may be

2678 used to establish these.

2679

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

2681 In particular - MATERIALIZED and NOT MATERIALIZED.

2682

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

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

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

2686 compile time.

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

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

2689 conjunction with UNION ALL in order to derive rows

2690 from those already selected.

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

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

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

2694 :paramref:`.HasCTE.add_cte.nest_here`

2695 parameter may also be used to more carefully

2696 control the exact placement of a particular CTE.

2697

2698 .. versionadded:: 1.4.24

2699

2700 .. seealso::

2701

2702 :meth:`.HasCTE.add_cte`

2703

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

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

2706 as well as additional examples.

2707

2708 Example 1, non recursive::

2709

2710 from sqlalchemy import (

2711 Table,

2712 Column,

2713 String,

2714 Integer,

2715 MetaData,

2716 select,

2717 func,

2718 )

2719

2720 metadata = MetaData()

2721

2722 orders = Table(

2723 "orders",

2724 metadata,

2725 Column("region", String),

2726 Column("amount", Integer),

2727 Column("product", String),

2728 Column("quantity", Integer),

2729 )

2730

2731 regional_sales = (

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

2733 .group_by(orders.c.region)

2734 .cte("regional_sales")

2735 )

2736

2737

2738 top_regions = (

2739 select(regional_sales.c.region)

2740 .where(

2741 regional_sales.c.total_sales

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

2743 )

2744 .cte("top_regions")

2745 )

2746

2747 statement = (

2748 select(

2749 orders.c.region,

2750 orders.c.product,

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

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

2753 )

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

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

2756 )

2757

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

2759

2760 Example 2, WITH RECURSIVE::

2761

2762 from sqlalchemy import (

2763 Table,

2764 Column,

2765 String,

2766 Integer,

2767 MetaData,

2768 select,

2769 func,

2770 )

2771

2772 metadata = MetaData()

2773

2774 parts = Table(

2775 "parts",

2776 metadata,

2777 Column("part", String),

2778 Column("sub_part", String),

2779 Column("quantity", Integer),

2780 )

2781

2782 included_parts = (

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

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

2785 .cte(recursive=True)

2786 )

2787

2788

2789 incl_alias = included_parts.alias()

2790 parts_alias = parts.alias()

2791 included_parts = included_parts.union_all(

2792 select(

2793 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2795 )

2796

2797 statement = select(

2798 included_parts.c.sub_part,

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

2800 ).group_by(included_parts.c.sub_part)

2801

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

2803

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

2805

2806 from datetime import date

2807 from sqlalchemy import (

2808 MetaData,

2809 Table,

2810 Column,

2811 Integer,

2812 Date,

2813 select,

2814 literal,

2815 and_,

2816 exists,

2817 )

2818

2819 metadata = MetaData()

2820

2821 visitors = Table(

2822 "visitors",

2823 metadata,

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

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

2826 Column("count", Integer),

2827 )

2828

2829 # add 5 visitors for the product_id == 1

2830 product_id = 1

2831 day = date.today()

2832 count = 5

2833

2834 update_cte = (

2835 visitors.update()

2836 .where(

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

2838 )

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

2840 .returning(literal(1))

2841 .cte("update_cte")

2842 )

2843

2844 upsert = visitors.insert().from_select(

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

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

2847 ~exists(update_cte.select())

2848 ),

2849 )

2850

2851 connection.execute(upsert)

2852

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

2854

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

2856

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

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

2859 "value_a", nesting=True

2860 )

2861

2862 # Nesting CTEs takes ascendency locally

2863 # over the CTEs at a higher level

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

2865

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

2867

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

2869 shown with inline parameters below as:

2870

2871 .. sourcecode:: sql

2872

2873 WITH

2874 value_a AS

2875 (SELECT 'root' AS n),

2876 value_b AS

2877 (WITH value_a AS

2878 (SELECT 'nesting' AS n)

2879 SELECT value_a.n AS n FROM value_a)

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

2881 FROM value_a, value_b

2882

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

2884 as follows (SQLAlchemy 2.0 and above)::

2885

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

2887

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

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

2890

2891 # Nesting CTEs takes ascendency locally

2892 # over the CTEs at a higher level

2893 value_b = (

2894 select(value_a_nested.c.n)

2895 .add_cte(value_a_nested, nest_here=True)

2896 .cte("value_b")

2897 )

2898

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

2900

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

2902

2903 edge = Table(

2904 "edge",

2905 metadata,

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

2907 Column("left", Integer),

2908 Column("right", Integer),

2909 )

2910

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

2912

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

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

2915 )

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

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

2918 )

2919

2920 subgraph_cte = root_node.union(left_edge, right_edge)

2921

2922 subgraph = select(subgraph_cte)

2923

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

2925

2926 .. sourcecode:: sql

2927

2928 WITH RECURSIVE nodes(node) AS (

2929 SELECT 1 AS node

2930 UNION

2931 SELECT edge."left" AS "left"

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

2933 UNION

2934 SELECT edge."right" AS "right"

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

2936 )

2937 SELECT nodes.node FROM nodes

2938

2939 .. seealso::

2940

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

2942 :meth:`_expression.HasCTE.cte`.

2943

2944 """ # noqa: E501

2945 return CTE._construct(

2946 self, name=name, recursive=recursive, nesting=nesting

2947 )

2948

2949

2950class Subquery(AliasedReturnsRows):

2951 """Represent a subquery of a SELECT.

2952

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

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

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

2956 :class:`_expression.SelectBase` subclass

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

2958 :class:`_expression.CompoundSelect`, and

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

2960 it represents the

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

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

2963

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

2965 :class:`_expression.Alias`

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

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

2968 :class:`_expression.Alias` always

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

2970 :class:`.Subquery`

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

2972

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

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

2975 statement.

2976

2977 """

2978

2979 __visit_name__ = "subquery"

2980

2981 _is_subquery = True

2982

2983 inherit_cache = True

2984

2985 element: SelectBase

2986

2987 @classmethod

2988 def _factory(

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

2990 ) -> Subquery:

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

2992

2993 return coercions.expect(

2994 roles.SelectStatementRole, selectable

2995 ).subquery(name=name)

2996

2997 @util.deprecated(

2998 "1.4",

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

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

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

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

3003 ":func:`_expression.select` "

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

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

3006 )

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

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

3009

3010

3011class FromGrouping(GroupedElement, FromClause):

3012 """Represent a grouping of a FROM clause"""

3013

3014 _traverse_internals: _TraverseInternalsType = [

3015 ("element", InternalTraversal.dp_clauseelement)

3016 ]

3017

3018 element: FromClause

3019

3020 def __init__(self, element: FromClause):

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

3022

3023 @util.ro_non_memoized_property

3024 def columns(

3025 self,

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

3027 return self.element.columns

3028

3029 @util.ro_non_memoized_property

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

3031 return self.element.columns

3032

3033 @property

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

3035 return self.element.primary_key

3036

3037 @property

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

3039 return self.element.foreign_keys

3040

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

3042 return self.element.is_derived_from(fromclause)

3043

3044 def alias(

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

3046 ) -> NamedFromGrouping:

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

3048

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

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

3051

3052 @util.ro_non_memoized_property

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

3054 return self.element._hide_froms

3055

3056 @util.ro_non_memoized_property

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

3058 return self.element._from_objects

3059

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

3061 return {"element": self.element}

3062

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

3064 self.element = state["element"]

3065

3066 if TYPE_CHECKING:

3067

3068 def self_group(

3069 self, against: Optional[OperatorType] = None

3070 ) -> Self: ...

3071

3072

3073class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3075

3076 .. versionadded:: 2.0

3077

3078 """

3079

3080 inherit_cache = True

3081

3082 if TYPE_CHECKING:

3083

3084 def self_group(

3085 self, against: Optional[OperatorType] = None

3086 ) -> Self: ...

3087

3088

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

3090 """Represents a minimal "table" construct.

3091

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

3093 collection of columns, which are typically produced

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

3095

3096 from sqlalchemy import table, column

3097

3098 user = table(

3099 "user",

3100 column("id"),

3101 column("name"),

3102 column("description"),

3103 )

3104

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

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

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

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

3109

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

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

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

3113 It's useful

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

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

3116 is not on hand.

3117

3118 """

3119

3120 __visit_name__ = "table"

3121

3122 _traverse_internals: _TraverseInternalsType = [

3123 (

3124 "columns",

3125 InternalTraversal.dp_fromclause_canonical_column_collection,

3126 ),

3127 ("name", InternalTraversal.dp_string),

3128 ("schema", InternalTraversal.dp_string),

3129 ]

3130

3131 _is_table = True

3132

3133 fullname: str

3134

3135 implicit_returning = False

3136 """:class:`_expression.TableClause`

3137 doesn't support having a primary key or column

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

3139

3140 @util.ro_memoized_property

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

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

3143 return None

3144

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

3146 super().__init__()

3147 self.name = name

3148 self._columns = DedupeColumnCollection()

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

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

3151 for c in columns:

3152 self.append_column(c)

3153

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

3155 if schema is not None:

3156 self.schema = schema

3157 if self.schema is not None:

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

3159 else:

3160 self.fullname = self.name

3161 if kw:

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

3163

3164 if TYPE_CHECKING:

3165

3166 @util.ro_non_memoized_property

3167 def columns(

3168 self,

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

3170

3171 @util.ro_non_memoized_property

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

3173

3174 def __str__(self) -> str:

3175 if self.schema is not None:

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

3177 else:

3178 return self.name

3179

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

3181 pass

3182

3183 @util.ro_memoized_property

3184 def description(self) -> str:

3185 return self.name

3186

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

3188 existing = c.table

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

3190 raise exc.ArgumentError(

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

3192 % (c.key, existing)

3193 )

3194

3195 self._columns.add(c)

3196 c.table = self

3197

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

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

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

3201 :class:`_expression.TableClause`.

3202

3203 E.g.::

3204

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

3206

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

3208

3209 """

3210

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

3212

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

3214 def update(self) -> Update:

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

3216 :class:`_expression.TableClause`.

3217

3218 E.g.::

3219

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

3221

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

3223

3224 """

3225 return util.preloaded.sql_dml.Update(

3226 self,

3227 )

3228

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

3230 def delete(self) -> Delete:

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

3232 :class:`_expression.TableClause`.

3233

3234 E.g.::

3235

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

3237

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

3239

3240 """

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

3242

3243 @util.ro_non_memoized_property

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

3245 return [self]

3246

3247

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

3249

3250

3251class ForUpdateArg(ClauseElement):

3252 _traverse_internals: _TraverseInternalsType = [

3253 ("of", InternalTraversal.dp_clauseelement_list),

3254 ("nowait", InternalTraversal.dp_boolean),

3255 ("read", InternalTraversal.dp_boolean),

3256 ("skip_locked", InternalTraversal.dp_boolean),

3257 ("key_share", InternalTraversal.dp_boolean),

3258 ]

3259

3260 of: Optional[Sequence[ClauseElement]]

3261 nowait: bool

3262 read: bool

3263 skip_locked: bool

3264

3265 @classmethod

3266 def _from_argument(

3267 cls, with_for_update: ForUpdateParameter

3268 ) -> Optional[ForUpdateArg]:

3269 if isinstance(with_for_update, ForUpdateArg):

3270 return with_for_update

3271 elif with_for_update in (None, False):

3272 return None

3273 elif with_for_update is True:

3274 return ForUpdateArg()

3275 else:

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

3277

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

3279 return (

3280 isinstance(other, ForUpdateArg)

3281 and other.nowait == self.nowait

3282 and other.read == self.read

3283 and other.skip_locked == self.skip_locked

3284 and other.key_share == self.key_share

3285 and other.of is self.of

3286 )

3287

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

3289 return not self.__eq__(other)

3290

3291 def __hash__(self) -> int:

3292 return id(self)

3293

3294 def __init__(

3295 self,

3296 *,

3297 nowait: bool = False,

3298 read: bool = False,

3299 of: Optional[_ForUpdateOfArgument] = None,

3300 skip_locked: bool = False,

3301 key_share: bool = False,

3302 ):

3303 """Represents arguments specified to

3304 :meth:`_expression.Select.for_update`.

3305

3306 """

3307

3308 self.nowait = nowait

3309 self.read = read

3310 self.skip_locked = skip_locked

3311 self.key_share = key_share

3312 if of is not None:

3313 self.of = [

3314 coercions.expect(roles.ColumnsClauseRole, elem)

3315 for elem in util.to_list(of)

3316 ]

3317 else:

3318 self.of = None

3319

3320

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

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

3323 in a statement.

3324

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

3326 :func:`_expression.values` function.

3327

3328 .. versionadded:: 1.4

3329

3330 """

3331

3332 __visit_name__ = "values"

3333

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

3335

3336 _unnamed: bool

3337 _traverse_internals: _TraverseInternalsType = [

3338 ("_column_args", InternalTraversal.dp_clauseelement_list),

3339 ("_data", InternalTraversal.dp_dml_multi_values),

3340 ("name", InternalTraversal.dp_string),

3341 ("literal_binds", InternalTraversal.dp_boolean),

3342 ] + HasCTE._has_ctes_traverse_internals

3343

3344 name_cte_columns = True

3345

3346 def __init__(

3347 self,

3348 *columns: ColumnClause[Any],

3349 name: Optional[str] = None,

3350 literal_binds: bool = False,

3351 ):

3352 super().__init__()

3353 self._column_args = columns

3354

3355 if name is None:

3356 self._unnamed = True

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

3358 else:

3359 self._unnamed = False

3360 self.name = name

3361 self.literal_binds = literal_binds

3362 self.named_with_column = not self._unnamed

3363

3364 @property

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

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

3367

3368 @util.ro_non_memoized_property

3369 def _all_selected_columns(self) -> _SelectIterable:

3370 return self._column_args

3371

3372 @_generative

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

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

3375 construct that is a copy of this

3376 one with the given name.

3377

3378 This method is a VALUES-specific specialization of the

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

3380

3381 .. seealso::

3382

3383 :ref:`tutorial_using_aliases`

3384

3385 :func:`_expression.alias`

3386

3387 """

3388 non_none_name: str

3389

3390 if name is None:

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

3392 else:

3393 non_none_name = name

3394

3395 self.name = non_none_name

3396 self.named_with_column = True

3397 self._unnamed = False

3398 return self

3399

3400 @_generative

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

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

3403 so that

3404 it renders as LATERAL.

3405

3406 .. seealso::

3407

3408 :func:`_expression.lateral`

3409

3410 """

3411 non_none_name: str

3412

3413 if name is None:

3414 non_none_name = self.name

3415 else:

3416 non_none_name = name

3417

3418 self._is_lateral = True

3419 self.name = non_none_name

3420 self._unnamed = False

3421 return self

3422

3423 @_generative

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

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

3426 adding the given data to the data list.

3427

3428 E.g.::

3429

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

3431

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

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

3434 constructor.

3435

3436 """

3437

3438 self._data += (values,)

3439 return self

3440

3441 def scalar_values(self) -> ScalarValues:

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

3443 COLUMN element in a statement.

3444

3445 .. versionadded:: 2.0.0b4

3446

3447 """

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

3449

3450 def _populate_column_collection(

3451 self,

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

3453 primary_key: ColumnSet,

3454 foreign_keys: Set[KeyedColumnElement[Any]],

3455 ) -> None:

3456 for c in self._column_args:

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

3458 _, c = c._make_proxy(

3459 self, primary_key=primary_key, foreign_keys=foreign_keys

3460 )

3461 else:

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

3463 # no memoizations of other FROM clauses.

3464 # see test_values.py -> test_auto_proxy_select_direct_col

3465 c._reset_memoizations()

3466 columns.add(c)

3467 c.table = self

3468

3469 @util.ro_non_memoized_property

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

3471 return [self]

3472

3473

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

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

3476 COLUMN element in a statement.

3477

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

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

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

3481 an ``IN`` or ``NOT IN`` condition.

3482

3483 .. versionadded:: 2.0.0b4

3484

3485 """

3486

3487 __visit_name__ = "scalar_values"

3488

3489 _traverse_internals: _TraverseInternalsType = [

3490 ("_column_args", InternalTraversal.dp_clauseelement_list),

3491 ("_data", InternalTraversal.dp_dml_multi_values),

3492 ("literal_binds", InternalTraversal.dp_boolean),

3493 ]

3494

3495 def __init__(

3496 self,

3497 columns: Sequence[ColumnClause[Any]],

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

3499 literal_binds: bool,

3500 ):

3501 super().__init__()

3502 self._column_args = columns

3503 self._data = data

3504 self.literal_binds = literal_binds

3505

3506 @property

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

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

3509

3510 def __clause_element__(self) -> ScalarValues:

3511 return self

3512

3513 if TYPE_CHECKING:

3514

3515 def self_group(

3516 self, against: Optional[OperatorType] = None

3517 ) -> Self: ...

3518

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

3520

3521

3522class SelectBase(

3523 roles.SelectStatementRole,

3524 roles.DMLSelectRole,

3525 roles.CompoundElementRole,

3526 roles.InElementRole,

3527 HasCTE,

3528 SupportsCloneAnnotations,

3529 Selectable,

3530):

3531 """Base class for SELECT statements.

3532

3533

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

3535 :class:`_expression.CompoundSelect` and

3536 :class:`_expression.TextualSelect`.

3537

3538

3539 """

3540

3541 _is_select_base = True

3542 is_select = True

3543

3544 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3545

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

3547 self._reset_memoizations()

3548

3549 @util.ro_non_memoized_property

3550 def selected_columns(

3551 self,

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

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

3554 representing the columns that

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

3556

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

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

3559 within this collection cannot be directly nested inside another SELECT

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

3561 necessary parenthesization required by SQL.

3562

3563 .. note::

3564

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

3566 include expressions established in the columns clause using the

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

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

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

3570 construct.

3571

3572 .. seealso::

3573

3574 :attr:`_sql.Select.selected_columns`

3575

3576 .. versionadded:: 1.4

3577

3578 """

3579 raise NotImplementedError()

3580

3581 def _generate_fromclause_column_proxies(

3582 self,

3583 subquery: FromClause,

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

3585 primary_key: ColumnSet,

3586 foreign_keys: Set[KeyedColumnElement[Any]],

3587 *,

3588 proxy_compound_columns: Optional[

3589 Iterable[Sequence[ColumnElement[Any]]]

3590 ] = None,

3591 ) -> None:

3592 raise NotImplementedError()

3593

3594 @util.ro_non_memoized_property

3595 def _all_selected_columns(self) -> _SelectIterable:

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

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

3598 constructs.

3599

3600 .. versionadded:: 1.4.12

3601

3602 .. seealso::

3603

3604 :attr:`_sql.SelectBase.exported_columns`

3605

3606 """

3607 raise NotImplementedError()

3608

3609 @property

3610 def exported_columns(

3611 self,

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

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

3614 that represents the "exported"

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

3616 :class:`_sql.TextClause` constructs.

3617

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

3619 object are synonymous

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

3621

3622 .. versionadded:: 1.4

3623

3624 .. seealso::

3625

3626 :attr:`_expression.Select.exported_columns`

3627

3628 :attr:`_expression.Selectable.exported_columns`

3629

3630 :attr:`_expression.FromClause.exported_columns`

3631

3632

3633 """

3634 return self.selected_columns.as_readonly()

3635

3636 @property

3637 @util.deprecated(

3638 "1.4",

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

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

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

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

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

3644 "first in order to create "

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

3646 "columns that this SELECT object SELECTs "

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

3648 "attribute.",

3649 )

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

3651 return self._implicit_subquery.columns

3652

3653 @property

3654 def columns(

3655 self,

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

3657 return self.c

3658

3659 def get_label_style(self) -> SelectLabelStyle:

3660 """

3661 Retrieve the current label style.

3662

3663 Implemented by subclasses.

3664

3665 """

3666 raise NotImplementedError()

3667

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

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

3670

3671 Implemented by subclasses.

3672

3673 """

3674

3675 raise NotImplementedError()

3676

3677 @util.deprecated(

3678 "1.4",

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

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

3681 "creates a subquery that should be explicit. "

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

3683 "first in order to create "

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

3685 )

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

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

3688

3689 @HasMemoized.memoized_attribute

3690 def _implicit_subquery(self) -> Subquery:

3691 return self.subquery()

3692

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

3694 raise NotImplementedError()

3695

3696 @util.deprecated(

3697 "1.4",

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

3699 "method is deprecated and will be "

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

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

3702 )

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

3704 return self.scalar_subquery()

3705

3706 def exists(self) -> Exists:

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

3708 which can be used as a column expression.

3709

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

3711

3712 .. seealso::

3713

3714 :func:`_sql.exists`

3715

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

3717

3718 .. versionadded:: 1.4

3719

3720 """

3721 return Exists(self)

3722

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

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

3725 used as a column expression.

3726

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

3728

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

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

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

3732 an enclosing SELECT.

3733

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

3735 subquery that can be produced using the

3736 :meth:`_expression.SelectBase.subquery`

3737 method.

3738

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

3740 :meth:`_expression.SelectBase.scalar_subquery`.

3741

3742 .. seealso::

3743

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

3745

3746 """

3747 if self._label_style is not LABEL_STYLE_NONE:

3748 self = self.set_label_style(LABEL_STYLE_NONE)

3749

3750 return ScalarSelect(self)

3751

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

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

3754 subquery with a label.

3755

3756 .. seealso::

3757

3758 :meth:`_expression.SelectBase.scalar_subquery`.

3759

3760 """

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

3762

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

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

3765

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

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

3768

3769 .. seealso::

3770

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

3772

3773 """

3774 return Lateral._factory(self, name)

3775

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

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

3778

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

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

3781 SELECT statement.

3782

3783 Given a SELECT statement such as::

3784

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

3786

3787 The above statement might look like:

3788

3789 .. sourcecode:: sql

3790

3791 SELECT table.id, table.name FROM table

3792

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

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

3795 a named sub-element::

3796

3797 subq = stmt.subquery()

3798 new_stmt = select(subq)

3799

3800 The above renders as:

3801

3802 .. sourcecode:: sql

3803

3804 SELECT anon_1.id, anon_1.name

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

3806

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

3808 is equivalent to calling

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

3810 method on a FROM object; however,

3811 as a :class:`_expression.SelectBase`

3812 object is not directly FROM object,

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

3814 method provides clearer semantics.

3815

3816 .. versionadded:: 1.4

3817

3818 """

3819

3820 return Subquery._construct(

3821 self._ensure_disambiguated_names(), name=name

3822 )

3823

3824 def _ensure_disambiguated_names(self) -> Self:

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

3826 disambiguated in some way, if possible.

3827

3828 """

3829

3830 raise NotImplementedError()

3831

3832 def alias(

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

3834 ) -> Subquery:

3835 """Return a named subquery against this

3836 :class:`_expression.SelectBase`.

3837

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

3839 :class:`_expression.FromClause`),

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

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

3842 :class:`_expression.FromClause`.

3843

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

3845 method is now

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

3847

3848 """

3849 return self.subquery(name=name)

3850

3851

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

3853

3854

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

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

3857

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

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

3860 compound selects.

3861

3862 """

3863

3864 __visit_name__ = "select_statement_grouping"

3865 _traverse_internals: _TraverseInternalsType = [

3866 ("element", InternalTraversal.dp_clauseelement)

3867 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3868

3869 _is_select_container = True

3870

3871 element: _SB

3872

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

3874 self.element = cast(

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

3876 )

3877

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

3879 new_element = self.element._ensure_disambiguated_names()

3880 if new_element is not self.element:

3881 return SelectStatementGrouping(new_element)

3882 else:

3883 return self

3884

3885 def get_label_style(self) -> SelectLabelStyle:

3886 return self.element.get_label_style()

3887

3888 def set_label_style(

3889 self, label_style: SelectLabelStyle

3890 ) -> SelectStatementGrouping[_SB]:

3891 return SelectStatementGrouping(

3892 self.element.set_label_style(label_style)

3893 )

3894

3895 @property

3896 def select_statement(self) -> _SB:

3897 return self.element

3898

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

3900 return self

3901

3902 if TYPE_CHECKING:

3903

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

3905

3906 # def _generate_columns_plus_names(

3907 # self, anon_for_dupe_key: bool

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

3909 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3910

3911 def _generate_fromclause_column_proxies(

3912 self,

3913 subquery: FromClause,

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

3915 primary_key: ColumnSet,

3916 foreign_keys: Set[KeyedColumnElement[Any]],

3917 *,

3918 proxy_compound_columns: Optional[

3919 Iterable[Sequence[ColumnElement[Any]]]

3920 ] = None,

3921 ) -> None:

3922 self.element._generate_fromclause_column_proxies(

3923 subquery,

3924 columns,

3925 proxy_compound_columns=proxy_compound_columns,

3926 primary_key=primary_key,

3927 foreign_keys=foreign_keys,

3928 )

3929

3930 @util.ro_non_memoized_property

3931 def _all_selected_columns(self) -> _SelectIterable:

3932 return self.element._all_selected_columns

3933

3934 @util.ro_non_memoized_property

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

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

3937 representing the columns that

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

3939 :class:`_sql.TextClause` constructs.

3940

3941 .. versionadded:: 1.4

3942

3943 .. seealso::

3944

3945 :attr:`_sql.Select.selected_columns`

3946

3947 """

3948 return self.element.selected_columns

3949

3950 @util.ro_non_memoized_property

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

3952 return self.element._from_objects

3953

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

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

3956 raise NotImplementedError

3957

3958

3959class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3961 added.

3962

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

3964 :class:`_expression.CompoundSelect`

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

3966 rendering can be controlled. Compare to

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

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

3969 and is also a SELECT construct,

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

3971 only wrapped as a subquery.

3972

3973 """

3974

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

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

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

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

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

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

3981 _for_update_arg: Optional[ForUpdateArg] = None

3982

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

3984 self._label_style = _label_style

3985

3986 @_generative

3987 def with_for_update(

3988 self,

3989 *,

3990 nowait: bool = False,

3991 read: bool = False,

3992 of: Optional[_ForUpdateOfArgument] = None,

3993 skip_locked: bool = False,

3994 key_share: bool = False,

3995 ) -> Self:

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

3997 :class:`_expression.GenerativeSelect`.

3998

3999 E.g.::

4000

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

4002

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

4004 render a statement like:

4005

4006 .. sourcecode:: sql

4007

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

4009

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

4011 would produce:

4012

4013 .. sourcecode:: sql

4014

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

4016

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

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

4019 provided which allow for common database-specific

4020 variants.

4021

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

4023 Database and PostgreSQL dialects.

4024

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

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

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

4028

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

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

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

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

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

4034 table or as a column depending on backend.

4035

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

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

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

4039

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

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

4042 on the PostgreSQL dialect.

4043

4044 """

4045 self._for_update_arg = ForUpdateArg(

4046 nowait=nowait,

4047 read=read,

4048 of=of,

4049 skip_locked=skip_locked,

4050 key_share=key_share,

4051 )

4052 return self

4053

4054 def get_label_style(self) -> SelectLabelStyle:

4055 """

4056 Retrieve the current label style.

4057

4058 .. versionadded:: 1.4

4059

4060 """

4061 return self._label_style

4062

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

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

4065

4066 There are three "label styles" available,

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

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

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

4070 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4071

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

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

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

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

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

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

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

4079 that the impact of this labeling is minimal.

4080

4081 The rationale for disambiguation is mostly so that all column

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

4083 collection when a subquery is created.

4084

4085 .. versionadded:: 1.4 - the

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

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

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

4089

4090 .. seealso::

4091

4092 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4093

4094 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4095

4096 :data:`_sql.LABEL_STYLE_NONE`

4097

4098 :data:`_sql.LABEL_STYLE_DEFAULT`

4099

4100 """

4101 if self._label_style is not style:

4102 self = self._generate()

4103 self._label_style = style

4104 return self

4105

4106 @property

4107 def _group_by_clause(self) -> ClauseList:

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

4109 return ClauseList._construct_raw(

4110 operators.comma_op, self._group_by_clauses

4111 )

4112

4113 @property

4114 def _order_by_clause(self) -> ClauseList:

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

4116 return ClauseList._construct_raw(

4117 operators.comma_op, self._order_by_clauses

4118 )

4119

4120 def _offset_or_limit_clause(

4121 self,

4122 element: _LimitOffsetType,

4123 name: Optional[str] = None,

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

4125 ) -> ColumnElement[Any]:

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

4127

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

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

4130

4131 """

4132 return coercions.expect(

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

4134 )

4135

4136 @overload

4137 def _offset_or_limit_clause_asint(

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

4139 ) -> NoReturn: ...

4140

4141 @overload

4142 def _offset_or_limit_clause_asint(

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

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

4145

4146 def _offset_or_limit_clause_asint(

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

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

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

4150 integer.

4151

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

4153 parameter. Otherwise, a compilation error is raised.

4154

4155 """

4156 if clause is None:

4157 return None

4158 try:

4159 value = clause._limit_offset_value

4160 except AttributeError as err:

4161 raise exc.CompileError(

4162 "This SELECT structure does not use a simple "

4163 "integer value for %s" % attrname

4164 ) from err

4165 else:

4166 return util.asint(value)

4167

4168 @property

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

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

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

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

4173 isn't currently set to an integer.

4174

4175 """

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

4177

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

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

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

4181 """

4182 return isinstance(clause, _OffsetLimitParam)

4183

4184 @property

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

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

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

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

4189 offset isn't currently set to an integer.

4190

4191 """

4192 return self._offset_or_limit_clause_asint(

4193 self._offset_clause, "offset"

4194 )

4195

4196 @property

4197 def _has_row_limiting_clause(self) -> bool:

4198 return (

4199 self._limit_clause is not None

4200 or self._offset_clause is not None

4201 or self._fetch_clause is not None

4202 )

4203

4204 @_generative

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

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

4207 applied.

4208

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

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

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

4212 functionality.

4213

4214 .. note::

4215

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

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

4218

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

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

4221

4222 .. seealso::

4223

4224 :meth:`_sql.GenerativeSelect.fetch`

4225

4226 :meth:`_sql.GenerativeSelect.offset`

4227

4228 """

4229

4230 self._fetch_clause = self._fetch_clause_options = None

4231 self._limit_clause = self._offset_or_limit_clause(limit)

4232 return self

4233

4234 @_generative

4235 def fetch(

4236 self,

4237 count: _LimitOffsetType,

4238 with_ties: bool = False,

4239 percent: bool = False,

4240 **dialect_kw: Any,

4241 ) -> Self:

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

4243 applied.

4244

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

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

4247 select. This functionality is is currently implemented for Oracle

4248 Database, PostgreSQL, MSSQL.

4249

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

4251

4252 .. note::

4253

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

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

4256

4257 .. versionadded:: 1.4

4258

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

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

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

4262 Pass ``None`` to reset it.

4263

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

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

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

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

4268

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

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

4271

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

4273 may be accepted by dialects.

4274

4275 .. versionadded:: 2.0.41

4276

4277 .. seealso::

4278

4279 :meth:`_sql.GenerativeSelect.limit`

4280

4281 :meth:`_sql.GenerativeSelect.offset`

4282

4283 """

4284 self._validate_dialect_kwargs(dialect_kw)

4285 self._limit_clause = None

4286 if count is None:

4287 self._fetch_clause = self._fetch_clause_options = None

4288 else:

4289 self._fetch_clause = self._offset_or_limit_clause(count)

4290 self._fetch_clause_options = {

4291 "with_ties": with_ties,

4292 "percent": percent,

4293 }

4294 return self

4295

4296 @_generative

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

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

4299 applied.

4300

4301

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

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

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

4305 functionality.

4306

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

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

4309

4310 .. seealso::

4311

4312 :meth:`_sql.GenerativeSelect.limit`

4313

4314 :meth:`_sql.GenerativeSelect.fetch`

4315

4316 """

4317

4318 self._offset_clause = self._offset_or_limit_clause(offset)

4319 return self

4320

4321 @_generative

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

4323 def slice(

4324 self,

4325 start: int,

4326 stop: int,

4327 ) -> Self:

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

4329

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

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

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

4333 query.

4334

4335 For example, ::

4336

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

4338

4339 renders as

4340

4341 .. sourcecode:: sql

4342

4343 SELECT users.id AS users_id,

4344 users.name AS users_name

4345 FROM users ORDER BY users.id

4346 LIMIT ? OFFSET ?

4347 (2, 1)

4348

4349 .. note::

4350

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

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

4353

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

4355 method generalized from the ORM.

4356

4357 .. seealso::

4358

4359 :meth:`_sql.GenerativeSelect.limit`

4360

4361 :meth:`_sql.GenerativeSelect.offset`

4362

4363 :meth:`_sql.GenerativeSelect.fetch`

4364

4365 """

4366 sql_util = util.preloaded.sql_util

4367 self._fetch_clause = self._fetch_clause_options = None

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

4369 self._limit_clause, self._offset_clause, start, stop

4370 )

4371 return self

4372

4373 @_generative

4374 def order_by(

4375 self,

4376 __first: Union[

4377 Literal[None, _NoArg.NO_ARG],

4378 _ColumnExpressionOrStrLabelArgument[Any],

4379 ] = _NoArg.NO_ARG,

4380 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4381 ) -> Self:

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

4383 criteria applied.

4384

4385 e.g.::

4386

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

4388

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

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

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

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

4393

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

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

4396

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

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

4399

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

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

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

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

4404 label matching in ORDER BY and GROUP BY expressions.

4405

4406 .. seealso::

4407

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

4409

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

4411

4412 """

4413

4414 if not clauses and __first is None:

4415 self._order_by_clauses = ()

4416 elif __first is not _NoArg.NO_ARG:

4417 self._order_by_clauses += tuple(

4418 coercions.expect(

4419 roles.OrderByRole, clause, apply_propagate_attrs=self

4420 )

4421 for clause in (__first,) + clauses

4422 )

4423 return self

4424

4425 @_generative

4426 def group_by(

4427 self,

4428 __first: Union[

4429 Literal[None, _NoArg.NO_ARG],

4430 _ColumnExpressionOrStrLabelArgument[Any],

4431 ] = _NoArg.NO_ARG,

4432 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4433 ) -> Self:

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

4435 criterion applied.

4436

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

4438

4439 e.g.::

4440

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

4442

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

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

4445

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

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

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

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

4450 label matching in ORDER BY and GROUP BY expressions.

4451

4452 .. seealso::

4453

4454 :ref:`tutorial_group_by_w_aggregates` - in the

4455 :ref:`unified_tutorial`

4456

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

4458

4459 """ # noqa: E501

4460

4461 if not clauses and __first is None:

4462 self._group_by_clauses = ()

4463 elif __first is not _NoArg.NO_ARG:

4464 self._group_by_clauses += tuple(

4465 coercions.expect(

4466 roles.GroupByRole, clause, apply_propagate_attrs=self

4467 )

4468 for clause in (__first,) + clauses

4469 )

4470 return self

4471

4472

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

4474class CompoundSelectState(CompileState):

4475 @util.memoized_property

4476 def _label_resolve_dict(

4477 self,

4478 ) -> Tuple[

4479 Dict[str, ColumnElement[Any]],

4480 Dict[str, ColumnElement[Any]],

4481 Dict[str, ColumnElement[Any]],

4482 ]:

4483 # TODO: this is hacky and slow

4484 hacky_subquery = self.statement.subquery()

4485 hacky_subquery.named_with_column = False

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

4487 return d, d, d

4488

4489

4490class _CompoundSelectKeyword(Enum):

4491 UNION = "UNION"

4492 UNION_ALL = "UNION ALL"

4493 EXCEPT = "EXCEPT"

4494 EXCEPT_ALL = "EXCEPT ALL"

4495 INTERSECT = "INTERSECT"

4496 INTERSECT_ALL = "INTERSECT ALL"

4497

4498

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

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

4501 SELECT-based set operations.

4502

4503

4504 .. seealso::

4505

4506 :func:`_expression.union`

4507

4508 :func:`_expression.union_all`

4509

4510 :func:`_expression.intersect`

4511

4512 :func:`_expression.intersect_all`

4513

4514 :func:`_expression.except`

4515

4516 :func:`_expression.except_all`

4517

4518 """

4519

4520 __visit_name__ = "compound_select"

4521

4522 _traverse_internals: _TraverseInternalsType = (

4523 [

4524 ("selects", InternalTraversal.dp_clauseelement_list),

4525 ("_limit_clause", InternalTraversal.dp_clauseelement),

4526 ("_offset_clause", InternalTraversal.dp_clauseelement),

4527 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4528 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4529 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4530 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4531 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4532 ("keyword", InternalTraversal.dp_string),

4533 ]

4534 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4535 + HasCTE._has_ctes_traverse_internals

4536 + DialectKWArgs._dialect_kwargs_traverse_internals

4537 + Executable._executable_traverse_internals

4538 )

4539

4540 selects: List[SelectBase]

4541

4542 _is_from_container = True

4543 _auto_correlate = False

4544

4545 def __init__(

4546 self,

4547 keyword: _CompoundSelectKeyword,

4548 *selects: _SelectStatementForCompoundArgument[_TP],

4549 ):

4550 self.keyword = keyword

4551 self.selects = [

4552 coercions.expect(

4553 roles.CompoundElementRole, s, apply_propagate_attrs=self

4554 ).self_group(against=self)

4555 for s in selects

4556 ]

4557

4558 GenerativeSelect.__init__(self)

4559

4560 @classmethod

4561 def _create_union(

4562 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4563 ) -> CompoundSelect[_TP]:

4564 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4565

4566 @classmethod

4567 def _create_union_all(

4568 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4569 ) -> CompoundSelect[_TP]:

4570 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4571

4572 @classmethod

4573 def _create_except(

4574 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4575 ) -> CompoundSelect[_TP]:

4576 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4577

4578 @classmethod

4579 def _create_except_all(

4580 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4581 ) -> CompoundSelect[_TP]:

4582 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4583

4584 @classmethod

4585 def _create_intersect(

4586 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4587 ) -> CompoundSelect[_TP]:

4588 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4589

4590 @classmethod

4591 def _create_intersect_all(

4592 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4593 ) -> CompoundSelect[_TP]:

4594 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4595

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

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

4598

4599 def self_group(

4600 self, against: Optional[OperatorType] = None

4601 ) -> GroupedElement:

4602 return SelectStatementGrouping(self)

4603

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

4605 for s in self.selects:

4606 if s.is_derived_from(fromclause):

4607 return True

4608 return False

4609

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

4611 if self._label_style is not style:

4612 self = self._generate()

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

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

4615

4616 return self

4617

4618 def _ensure_disambiguated_names(self) -> Self:

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

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

4621 self = self._generate()

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

4623

4624 return self

4625

4626 def _generate_fromclause_column_proxies(

4627 self,

4628 subquery: FromClause,

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

4630 primary_key: ColumnSet,

4631 foreign_keys: Set[KeyedColumnElement[Any]],

4632 *,

4633 proxy_compound_columns: Optional[

4634 Iterable[Sequence[ColumnElement[Any]]]

4635 ] = None,

4636 ) -> None:

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

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

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

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

4641 # generate below.

4642 select_0 = self.selects[0]

4643

4644 if self._label_style is not LABEL_STYLE_DEFAULT:

4645 select_0 = select_0.set_label_style(self._label_style)

4646

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

4648 # derived columns place a 'weight' annotation corresponding

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

4650 # that the corresponding_column() operation can resolve

4651 # conflicts

4652 extra_col_iterator = zip(

4653 *[

4654 [

4655 c._annotate(dd)

4656 for c in stmt._all_selected_columns

4657 if is_column_element(c)

4658 ]

4659 for dd, stmt in [

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

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

4662 ]

4663 ]

4664 )

4665

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

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

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

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

4670 # compound

4671 select_0._generate_fromclause_column_proxies(

4672 subquery,

4673 columns,

4674 proxy_compound_columns=extra_col_iterator,

4675 primary_key=primary_key,

4676 foreign_keys=foreign_keys,

4677 )

4678

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

4680 super()._refresh_for_new_column(column)

4681 for select in self.selects:

4682 select._refresh_for_new_column(column)

4683

4684 @util.ro_non_memoized_property

4685 def _all_selected_columns(self) -> _SelectIterable:

4686 return self.selects[0]._all_selected_columns

4687

4688 @util.ro_non_memoized_property

4689 def selected_columns(

4690 self,

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

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

4693 representing the columns that

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

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

4696

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

4698 :attr:`_expression.CompoundSelect.selected_columns`

4699 attribute returns the selected

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

4701 statements within the set operation.

4702

4703 .. seealso::

4704

4705 :attr:`_sql.Select.selected_columns`

4706

4707 .. versionadded:: 1.4

4708

4709 """

4710 return self.selects[0].selected_columns

4711

4712

4713# backwards compat

4714for elem in _CompoundSelectKeyword:

4715 setattr(CompoundSelect, elem.name, elem)

4716

4717

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

4719class SelectState(util.MemoizedSlots, CompileState):

4720 __slots__ = (

4721 "from_clauses",

4722 "froms",

4723 "columns_plus_names",

4724 "_label_resolve_dict",

4725 )

4726

4727 if TYPE_CHECKING:

4728 default_select_compile_options: CacheableOptions

4729 else:

4730

4731 class default_select_compile_options(CacheableOptions):

4732 _cache_key_traversal = []

4733

4734 if TYPE_CHECKING:

4735

4736 @classmethod

4737 def get_plugin_class(

4738 cls, statement: Executable

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

4740

4741 def __init__(

4742 self,

4743 statement: Select[Any],

4744 compiler: SQLCompiler,

4745 **kw: Any,

4746 ):

4747 self.statement = statement

4748 self.from_clauses = statement._from_obj

4749

4750 for memoized_entities in statement._memoized_select_entities:

4751 self._setup_joins(

4752 memoized_entities._setup_joins, memoized_entities._raw_columns

4753 )

4754

4755 if statement._setup_joins:

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

4757

4758 self.froms = self._get_froms(statement)

4759

4760 self.columns_plus_names = statement._generate_columns_plus_names(True)

4761

4762 @classmethod

4763 def _plugin_not_implemented(cls) -> NoReturn:

4764 raise NotImplementedError(

4765 "The default SELECT construct without plugins does not "

4766 "implement this method."

4767 )

4768

4769 @classmethod

4770 def get_column_descriptions(

4771 cls, statement: Select[Any]

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

4773 return [

4774 {

4775 "name": name,

4776 "type": element.type,

4777 "expr": element,

4778 }

4779 for _, name, _, element, _ in (

4780 statement._generate_columns_plus_names(False)

4781 )

4782 ]

4783

4784 @classmethod

4785 def from_statement(

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

4787 ) -> ExecutableReturnsRows:

4788 cls._plugin_not_implemented()

4789

4790 @classmethod

4791 def get_columns_clause_froms(

4792 cls, statement: Select[Any]

4793 ) -> List[FromClause]:

4794 return cls._normalize_froms(

4795 itertools.chain.from_iterable(

4796 element._from_objects for element in statement._raw_columns

4797 )

4798 )

4799

4800 @classmethod

4801 def _column_naming_convention(

4802 cls, label_style: SelectLabelStyle

4803 ) -> _LabelConventionCallable:

4804 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4805

4806 dedupe = label_style is not LABEL_STYLE_NONE

4807

4808 pa = prefix_anon_map()

4809 names = set()

4810

4811 def go(

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

4813 col_name: Optional[str] = None,

4814 ) -> Optional[str]:

4815 if is_text_clause(c):

4816 return None

4817 elif TYPE_CHECKING:

4818 assert is_column_element(c)

4819

4820 if not dedupe:

4821 name = c._proxy_key

4822 if name is None:

4823 name = "_no_label"

4824 return name

4825

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

4827

4828 if name is None:

4829 name = "_no_label"

4830 if name in names:

4831 return c._anon_label(name) % pa

4832 else:

4833 names.add(name)

4834 return name

4835

4836 elif name in names:

4837 return (

4838 c._anon_tq_key_label % pa

4839 if table_qualified

4840 else c._anon_key_label % pa

4841 )

4842 else:

4843 names.add(name)

4844 return name

4845

4846 return go

4847

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

4849 ambiguous_table_name_map: _AmbiguousTableNameMap

4850 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4851

4852 return self._normalize_froms(

4853 itertools.chain(

4854 self.from_clauses,

4855 itertools.chain.from_iterable(

4856 [

4857 element._from_objects

4858 for element in statement._raw_columns

4859 ]

4860 ),

4861 itertools.chain.from_iterable(

4862 [

4863 element._from_objects

4864 for element in statement._where_criteria

4865 ]

4866 ),

4867 ),

4868 check_statement=statement,

4869 ambiguous_table_name_map=ambiguous_table_name_map,

4870 )

4871

4872 @classmethod

4873 def _normalize_froms(

4874 cls,

4875 iterable_of_froms: Iterable[FromClause],

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

4877 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4878 ) -> List[FromClause]:

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

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

4881

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

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

4884 etc.

4885

4886 """

4887 seen: Set[FromClause] = set()

4888 froms: List[FromClause] = []

4889

4890 for item in iterable_of_froms:

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

4892 raise exc.InvalidRequestError(

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

4894 )

4895

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

4897 froms.append(item)

4898 seen.update(item._cloned_set)

4899

4900 if froms:

4901 toremove = set(

4902 itertools.chain.from_iterable(

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

4904 )

4905 )

4906 if toremove:

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

4908 # using a list to maintain ordering

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

4910

4911 if ambiguous_table_name_map is not None:

4912 ambiguous_table_name_map.update(

4913 (

4914 fr.name,

4915 _anonymous_label.safe_construct(

4916 hash(fr.name), fr.name

4917 ),

4918 )

4919 for item in froms

4920 for fr in item._from_objects

4921 if is_table(fr)

4922 and fr.schema

4923 and fr.name not in ambiguous_table_name_map

4924 )

4925

4926 return froms

4927

4928 def _get_display_froms(

4929 self,

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

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

4932 ) -> List[FromClause]:

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

4934

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

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

4937 may want to leave those absent if it is automatically

4938 correlating.

4939

4940 """

4941

4942 froms = self.froms

4943

4944 if self.statement._correlate:

4945 to_correlate = self.statement._correlate

4946 if to_correlate:

4947 froms = [

4948 f

4949 for f in froms

4950 if f

4951 not in _cloned_intersection(

4952 _cloned_intersection(

4953 froms, explicit_correlate_froms or ()

4954 ),

4955 to_correlate,

4956 )

4957 ]

4958

4959 if self.statement._correlate_except is not None:

4960 froms = [

4961 f

4962 for f in froms

4963 if f

4964 not in _cloned_difference(

4965 _cloned_intersection(

4966 froms, explicit_correlate_froms or ()

4967 ),

4968 self.statement._correlate_except,

4969 )

4970 ]

4971

4972 if (

4973 self.statement._auto_correlate

4974 and implicit_correlate_froms

4975 and len(froms) > 1

4976 ):

4977 froms = [

4978 f

4979 for f in froms

4980 if f

4981 not in _cloned_intersection(froms, implicit_correlate_froms)

4982 ]

4983

4984 if not len(froms):

4985 raise exc.InvalidRequestError(

4986 "Select statement '%r"

4987 "' returned no FROM clauses "

4988 "due to auto-correlation; "

4989 "specify correlate(<tables>) "

4990 "to control correlation "

4991 "manually." % self.statement

4992 )

4993

4994 return froms

4995

4996 def _memoized_attr__label_resolve_dict(

4997 self,

4998 ) -> Tuple[

4999 Dict[str, ColumnElement[Any]],

5000 Dict[str, ColumnElement[Any]],

5001 Dict[str, ColumnElement[Any]],

5002 ]:

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

5004 c._tq_label or c.key: c

5005 for c in self.statement._all_selected_columns

5006 if c._allow_label_resolve

5007 }

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

5009 c.key: c # type: ignore

5010 for c in _select_iterables(self.froms)

5011 if c._allow_label_resolve

5012 }

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

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

5015 with_cols.setdefault(key, value)

5016

5017 return with_cols, only_froms, only_cols

5018

5019 @classmethod

5020 def determine_last_joined_entity(

5021 cls, stmt: Select[Any]

5022 ) -> Optional[_JoinTargetElement]:

5023 if stmt._setup_joins:

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

5025 else:

5026 return None

5027

5028 @classmethod

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

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

5031

5032 def _setup_joins(

5033 self,

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

5035 raw_columns: List[_ColumnsClauseElement],

5036 ) -> None:

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

5038 if TYPE_CHECKING:

5039 if onclause is not None:

5040 assert isinstance(onclause, ColumnElement)

5041

5042 isouter = flags["isouter"]

5043 full = flags["full"]

5044

5045 if left is None:

5046 (

5047 left,

5048 replace_from_obj_index,

5049 ) = self._join_determine_implicit_left_side(

5050 raw_columns, left, right, onclause

5051 )

5052 else:

5053 (replace_from_obj_index) = self._join_place_explicit_left_side(

5054 left

5055 )

5056

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

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

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

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

5061 # used instead.

5062 if TYPE_CHECKING:

5063 assert isinstance(right, FromClause)

5064 if onclause is not None:

5065 assert isinstance(onclause, ColumnElement)

5066

5067 if replace_from_obj_index is not None:

5068 # splice into an existing element in the

5069 # self._from_obj list

5070 left_clause = self.from_clauses[replace_from_obj_index]

5071

5072 self.from_clauses = (

5073 self.from_clauses[:replace_from_obj_index]

5074 + (

5075 Join(

5076 left_clause,

5077 right,

5078 onclause,

5079 isouter=isouter,

5080 full=full,

5081 ),

5082 )

5083 + self.from_clauses[replace_from_obj_index + 1 :]

5084 )

5085 else:

5086 assert left is not None

5087 self.from_clauses = self.from_clauses + (

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

5089 )

5090

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

5092 def _join_determine_implicit_left_side(

5093 self,

5094 raw_columns: List[_ColumnsClauseElement],

5095 left: Optional[FromClause],

5096 right: _JoinTargetElement,

5097 onclause: Optional[ColumnElement[Any]],

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

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

5100 determine if an existing FROM or entity in this query

5101 can serve as the left hand side.

5102

5103 """

5104

5105 sql_util = util.preloaded.sql_util

5106

5107 replace_from_obj_index: Optional[int] = None

5108

5109 from_clauses = self.from_clauses

5110

5111 if from_clauses:

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

5113 from_clauses, right, onclause

5114 )

5115

5116 if len(indexes) == 1:

5117 replace_from_obj_index = indexes[0]

5118 left = from_clauses[replace_from_obj_index]

5119 else:

5120 potential = {}

5121 statement = self.statement

5122

5123 for from_clause in itertools.chain(

5124 itertools.chain.from_iterable(

5125 [element._from_objects for element in raw_columns]

5126 ),

5127 itertools.chain.from_iterable(

5128 [

5129 element._from_objects

5130 for element in statement._where_criteria

5131 ]

5132 ),

5133 ):

5134 potential[from_clause] = ()

5135

5136 all_clauses = list(potential.keys())

5137 indexes = sql_util.find_left_clause_to_join_from(

5138 all_clauses, right, onclause

5139 )

5140

5141 if len(indexes) == 1:

5142 left = all_clauses[indexes[0]]

5143

5144 if len(indexes) > 1:

5145 raise exc.InvalidRequestError(

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

5147 "from, there are multiple FROMS which can "

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

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

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

5151 "help resolve the ambiguity."

5152 )

5153 elif not indexes:

5154 raise exc.InvalidRequestError(

5155 "Don't know how to join to %r. "

5156 "Please use the .select_from() "

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

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

5159 "help resolve the ambiguity." % (right,)

5160 )

5161 return left, replace_from_obj_index

5162

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

5164 def _join_place_explicit_left_side(

5165 self, left: FromClause

5166 ) -> Optional[int]:

5167 replace_from_obj_index: Optional[int] = None

5168

5169 sql_util = util.preloaded.sql_util

5170

5171 from_clauses = list(self.statement._iterate_from_elements())

5172

5173 if from_clauses:

5174 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5175 self.from_clauses, left

5176 )

5177 else:

5178 indexes = []

5179

5180 if len(indexes) > 1:

5181 raise exc.InvalidRequestError(

5182 "Can't identify which entity in which to assign the "

5183 "left side of this join. Please use a more specific "

5184 "ON clause."

5185 )

5186

5187 # have an index, means the left side is already present in

5188 # an existing FROM in the self._from_obj tuple

5189 if indexes:

5190 replace_from_obj_index = indexes[0]

5191

5192 # no index, means we need to add a new element to the

5193 # self._from_obj tuple

5194

5195 return replace_from_obj_index

5196

5197

5198class _SelectFromElements:

5199 __slots__ = ()

5200

5201 _raw_columns: List[_ColumnsClauseElement]

5202 _where_criteria: Tuple[ColumnElement[Any], ...]

5203 _from_obj: Tuple[FromClause, ...]

5204

5205 def _iterate_from_elements(self) -> Iterator[FromClause]:

5206 # note this does not include elements

5207 # in _setup_joins

5208

5209 seen = set()

5210 for element in self._raw_columns:

5211 for fr in element._from_objects:

5212 if fr in seen:

5213 continue

5214 seen.add(fr)

5215 yield fr

5216 for element in self._where_criteria:

5217 for fr in element._from_objects:

5218 if fr in seen:

5219 continue

5220 seen.add(fr)

5221 yield fr

5222 for element in self._from_obj:

5223 if element in seen:

5224 continue

5225 seen.add(element)

5226 yield element

5227

5228

5229class _MemoizedSelectEntities(

5230 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5231):

5232 """represents partial state from a Select object, for the case

5233 where Select.columns() has redefined the set of columns/entities the

5234 statement will be SELECTing from. This object represents

5235 the entities from the SELECT before that transformation was applied,

5236 so that transformations that were made in terms of the SELECT at that

5237 time, such as join() as well as options(), can access the correct context.

5238

5239 In previous SQLAlchemy versions, this wasn't needed because these

5240 constructs calculated everything up front, like when you called join()

5241 or options(), it did everything to figure out how that would translate

5242 into specific SQL constructs that would be ready to send directly to the

5243 SQL compiler when needed. But as of

5244 1.4, all of that stuff is done in the compilation phase, during the

5245 "compile state" portion of the process, so that the work can all be

5246 cached. So it needs to be able to resolve joins/options2 based on what

5247 the list of entities was when those methods were called.

5248

5249

5250 """

5251

5252 __visit_name__ = "memoized_select_entities"

5253

5254 _traverse_internals: _TraverseInternalsType = [

5255 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5256 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5257 ("_with_options", InternalTraversal.dp_executable_options),

5258 ]

5259

5260 _is_clone_of: Optional[ClauseElement]

5261 _raw_columns: List[_ColumnsClauseElement]

5262 _setup_joins: Tuple[_SetupJoinsElement, ...]

5263 _with_options: Tuple[ExecutableOption, ...]

5264

5265 _annotations = util.EMPTY_DICT

5266

5267 def _clone(self, **kw: Any) -> Self:

5268 c = self.__class__.__new__(self.__class__)

5269 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5270

5271 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5272 return c

5273

5274 @classmethod

5275 def _generate_for_statement(cls, select_stmt: Select[Any]) -> None:

5276 if select_stmt._setup_joins or select_stmt._with_options:

5277 self = _MemoizedSelectEntities()

5278 self._raw_columns = select_stmt._raw_columns

5279 self._setup_joins = select_stmt._setup_joins

5280 self._with_options = select_stmt._with_options

5281

5282 select_stmt._memoized_select_entities += (self,)

5283 select_stmt._raw_columns = []

5284 select_stmt._setup_joins = select_stmt._with_options = ()

5285

5286

5287class Select(

5288 HasPrefixes,

5289 HasSuffixes,

5290 HasHints,

5291 HasCompileState,

5292 _SelectFromElements,

5293 GenerativeSelect,

5294 TypedReturnsRows[_TP],

5295):

5296 """Represents a ``SELECT`` statement.

5297

5298 The :class:`_sql.Select` object is normally constructed using the

5299 :func:`_sql.select` function. See that function for details.

5300

5301 .. seealso::

5302

5303 :func:`_sql.select`

5304

5305 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5306

5307 """

5308

5309 __visit_name__ = "select"

5310

5311 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5312 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5313

5314 _raw_columns: List[_ColumnsClauseElement]

5315

5316 _distinct: bool = False

5317 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5318 _correlate: Tuple[FromClause, ...] = ()

5319 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5320 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5321 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5322 _from_obj: Tuple[FromClause, ...] = ()

5323 _auto_correlate = True

5324 _is_select_statement = True

5325 _compile_options: CacheableOptions = (

5326 SelectState.default_select_compile_options

5327 )

5328

5329 _traverse_internals: _TraverseInternalsType = (

5330 [

5331 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5332 (

5333 "_memoized_select_entities",

5334 InternalTraversal.dp_memoized_select_entities,

5335 ),

5336 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5337 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5338 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5339 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5340 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5341 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5342 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5343 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5344 ("_limit_clause", InternalTraversal.dp_clauseelement),

5345 ("_offset_clause", InternalTraversal.dp_clauseelement),

5346 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5347 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5348 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5349 ("_distinct", InternalTraversal.dp_boolean),

5350 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5351 ("_label_style", InternalTraversal.dp_plain_obj),

5352 ]

5353 + HasCTE._has_ctes_traverse_internals

5354 + HasPrefixes._has_prefixes_traverse_internals

5355 + HasSuffixes._has_suffixes_traverse_internals

5356 + HasHints._has_hints_traverse_internals

5357 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5358 + Executable._executable_traverse_internals

5359 + DialectKWArgs._dialect_kwargs_traverse_internals

5360 )

5361

5362 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5363 ("_compile_options", InternalTraversal.dp_has_cache_key)

5364 ]

5365

5366 _compile_state_factory: Type[SelectState]

5367

5368 @classmethod

5369 def _create_raw_select(cls, **kw: Any) -> Select[Any]:

5370 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5371

5372 Used internally to build up :class:`.Select` constructs with

5373 pre-established state.

5374

5375 """

5376

5377 stmt = Select.__new__(Select)

5378 stmt.__dict__.update(kw)

5379 return stmt

5380

5381 def __init__(

5382 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5383 ):

5384 r"""Construct a new :class:`_expression.Select`.

5385

5386 The public constructor for :class:`_expression.Select` is the

5387 :func:`_sql.select` function.

5388

5389 """

5390 self._raw_columns = [

5391 coercions.expect(

5392 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5393 )

5394 for ent in entities

5395 ]

5396 GenerativeSelect.__init__(self)

5397

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

5399 if not self._raw_columns:

5400 return NULLTYPE

5401 elem = self._raw_columns[0]

5402 cols = list(elem._select_iterable)

5403 return cols[0].type

5404

5405 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5406 """A synonym for the :meth:`_sql.Select.where` method."""

5407

5408 return self.where(*criteria)

5409

5410 def _filter_by_zero(

5411 self,

5412 ) -> Union[

5413 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5414 ]:

5415 if self._setup_joins:

5416 meth = SelectState.get_plugin_class(

5417 self

5418 ).determine_last_joined_entity

5419 _last_joined_entity = meth(self)

5420 if _last_joined_entity is not None:

5421 return _last_joined_entity

5422

5423 if self._from_obj:

5424 return self._from_obj[0]

5425

5426 return self._raw_columns[0]

5427

5428 if TYPE_CHECKING:

5429

5430 @overload

5431 def scalar_subquery(

5432 self: Select[Tuple[_MAYBE_ENTITY]],

5433 ) -> ScalarSelect[Any]: ...

5434

5435 @overload

5436 def scalar_subquery(

5437 self: Select[Tuple[_NOT_ENTITY]],

5438 ) -> ScalarSelect[_NOT_ENTITY]: ...

5439

5440 @overload

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

5442

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

5444

5445 def filter_by(self, **kwargs: Any) -> Self:

5446 r"""apply the given filtering criterion as a WHERE clause

5447 to this select.

5448

5449 """

5450 from_entity = self._filter_by_zero()

5451

5452 clauses = [

5453 _entity_namespace_key(from_entity, key) == value

5454 for key, value in kwargs.items()

5455 ]

5456 return self.filter(*clauses)

5457

5458 @property

5459 def column_descriptions(self) -> Any:

5460 """Return a :term:`plugin-enabled` 'column descriptions' structure

5461 referring to the columns which are SELECTed by this statement.

5462

5463 This attribute is generally useful when using the ORM, as an

5464 extended structure which includes information about mapped

5465 entities is returned. The section :ref:`queryguide_inspection`

5466 contains more background.

5467

5468 For a Core-only statement, the structure returned by this accessor

5469 is derived from the same objects that are returned by the

5470 :attr:`.Select.selected_columns` accessor, formatted as a list of

5471 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5472 which indicate the column expressions to be selected::

5473

5474 >>> stmt = select(user_table)

5475 >>> stmt.column_descriptions

5476 [

5477 {

5478 'name': 'id',

5479 'type': Integer(),

5480 'expr': Column('id', Integer(), ...)},

5481 {

5482 'name': 'name',

5483 'type': String(length=30),

5484 'expr': Column('name', String(length=30), ...)}

5485 ]

5486

5487 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5488 attribute returns a structure for a Core-only set of entities,

5489 not just ORM-only entities.

5490

5491 .. seealso::

5492

5493 :attr:`.UpdateBase.entity_description` - entity information for

5494 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5495

5496 :ref:`queryguide_inspection` - ORM background

5497

5498 """

5499 meth = SelectState.get_plugin_class(self).get_column_descriptions

5500 return meth(self)

5501

5502 def from_statement(

5503 self, statement: roles.ReturnsRowsRole

5504 ) -> ExecutableReturnsRows:

5505 """Apply the columns which this :class:`.Select` would select

5506 onto another statement.

5507

5508 This operation is :term:`plugin-specific` and will raise a not

5509 supported exception if this :class:`_sql.Select` does not select from

5510 plugin-enabled entities.

5511

5512

5513 The statement is typically either a :func:`_expression.text` or

5514 :func:`_expression.select` construct, and should return the set of

5515 columns appropriate to the entities represented by this

5516 :class:`.Select`.

5517

5518 .. seealso::

5519

5520 :ref:`orm_queryguide_selecting_text` - usage examples in the

5521 ORM Querying Guide

5522

5523 """

5524 meth = SelectState.get_plugin_class(self).from_statement

5525 return meth(self, statement)

5526

5527 @_generative

5528 def join(

5529 self,

5530 target: _JoinTargetArgument,

5531 onclause: Optional[_OnClauseArgument] = None,

5532 *,

5533 isouter: bool = False,

5534 full: bool = False,

5535 ) -> Self:

5536 r"""Create a SQL JOIN against this :class:`_expression.Select`

5537 object's criterion

5538 and apply generatively, returning the newly resulting

5539 :class:`_expression.Select`.

5540

5541 E.g.::

5542

5543 stmt = select(user_table).join(

5544 address_table, user_table.c.id == address_table.c.user_id

5545 )

5546

5547 The above statement generates SQL similar to:

5548

5549 .. sourcecode:: sql

5550

5551 SELECT user.id, user.name

5552 FROM user

5553 JOIN address ON user.id = address.user_id

5554

5555 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5556 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5557 source that is within the FROM clause of the existing SELECT,

5558 and a given target :class:`_sql.FromClause`, and then adds

5559 this :class:`_sql.Join` to the FROM clause of the newly generated

5560 SELECT statement. This is completely reworked from the behavior

5561 in 1.3, which would instead create a subquery of the entire

5562 :class:`_expression.Select` and then join that subquery to the

5563 target.

5564

5565 This is a **backwards incompatible change** as the previous behavior

5566 was mostly useless, producing an unnamed subquery rejected by

5567 most databases in any case. The new behavior is modeled after

5568 that of the very successful :meth:`_orm.Query.join` method in the

5569 ORM, in order to support the functionality of :class:`_orm.Query`

5570 being available by using a :class:`_sql.Select` object with an

5571 :class:`_orm.Session`.

5572

5573 See the notes for this change at :ref:`change_select_join`.

5574

5575

5576 :param target: target table to join towards

5577

5578 :param onclause: ON clause of the join. If omitted, an ON clause

5579 is generated automatically based on the :class:`_schema.ForeignKey`

5580 linkages between the two tables, if one can be unambiguously

5581 determined, otherwise an error is raised.

5582

5583 :param isouter: if True, generate LEFT OUTER join. Same as

5584 :meth:`_expression.Select.outerjoin`.

5585

5586 :param full: if True, generate FULL OUTER join.

5587

5588 .. seealso::

5589

5590 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5591

5592 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5593

5594 :meth:`_expression.Select.join_from`

5595

5596 :meth:`_expression.Select.outerjoin`

5597

5598 """ # noqa: E501

5599 join_target = coercions.expect(

5600 roles.JoinTargetRole, target, apply_propagate_attrs=self

5601 )

5602 if onclause is not None:

5603 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5604 else:

5605 onclause_element = None

5606

5607 self._setup_joins += (

5608 (

5609 join_target,

5610 onclause_element,

5611 None,

5612 {"isouter": isouter, "full": full},

5613 ),

5614 )

5615 return self

5616

5617 def outerjoin_from(

5618 self,

5619 from_: _FromClauseArgument,

5620 target: _JoinTargetArgument,

5621 onclause: Optional[_OnClauseArgument] = None,

5622 *,

5623 full: bool = False,

5624 ) -> Self:

5625 r"""Create a SQL LEFT OUTER JOIN against this

5626 :class:`_expression.Select` object's criterion and apply generatively,

5627 returning the newly resulting :class:`_expression.Select`.

5628

5629 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5630

5631 """

5632 return self.join_from(

5633 from_, target, onclause=onclause, isouter=True, full=full

5634 )

5635

5636 @_generative

5637 def join_from(

5638 self,

5639 from_: _FromClauseArgument,

5640 target: _JoinTargetArgument,

5641 onclause: Optional[_OnClauseArgument] = None,

5642 *,

5643 isouter: bool = False,

5644 full: bool = False,

5645 ) -> Self:

5646 r"""Create a SQL JOIN against this :class:`_expression.Select`

5647 object's criterion

5648 and apply generatively, returning the newly resulting

5649 :class:`_expression.Select`.

5650

5651 E.g.::

5652

5653 stmt = select(user_table, address_table).join_from(

5654 user_table, address_table, user_table.c.id == address_table.c.user_id

5655 )

5656

5657 The above statement generates SQL similar to:

5658

5659 .. sourcecode:: sql

5660

5661 SELECT user.id, user.name, address.id, address.email, address.user_id

5662 FROM user JOIN address ON user.id = address.user_id

5663

5664 .. versionadded:: 1.4

5665

5666 :param from\_: the left side of the join, will be rendered in the

5667 FROM clause and is roughly equivalent to using the

5668 :meth:`.Select.select_from` method.

5669

5670 :param target: target table to join towards

5671

5672 :param onclause: ON clause of the join.

5673

5674 :param isouter: if True, generate LEFT OUTER join. Same as

5675 :meth:`_expression.Select.outerjoin`.

5676

5677 :param full: if True, generate FULL OUTER join.

5678

5679 .. seealso::

5680

5681 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5682

5683 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5684

5685 :meth:`_expression.Select.join`

5686

5687 """ # noqa: E501

5688

5689 # note the order of parsing from vs. target is important here, as we

5690 # are also deriving the source of the plugin (i.e. the subject mapper

5691 # in an ORM query) which should favor the "from_" over the "target"

5692

5693 from_ = coercions.expect(

5694 roles.FromClauseRole, from_, apply_propagate_attrs=self

5695 )

5696 join_target = coercions.expect(

5697 roles.JoinTargetRole, target, apply_propagate_attrs=self

5698 )

5699 if onclause is not None:

5700 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5701 else:

5702 onclause_element = None

5703

5704 self._setup_joins += (

5705 (

5706 join_target,

5707 onclause_element,

5708 from_,

5709 {"isouter": isouter, "full": full},

5710 ),

5711 )

5712 return self

5713

5714 def outerjoin(

5715 self,

5716 target: _JoinTargetArgument,

5717 onclause: Optional[_OnClauseArgument] = None,

5718 *,

5719 full: bool = False,

5720 ) -> Self:

5721 """Create a left outer join.

5722

5723 Parameters are the same as that of :meth:`_expression.Select.join`.

5724

5725 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5726 creates a :class:`_sql.Join` object between a

5727 :class:`_sql.FromClause` source that is within the FROM clause of

5728 the existing SELECT, and a given target :class:`_sql.FromClause`,

5729 and then adds this :class:`_sql.Join` to the FROM clause of the

5730 newly generated SELECT statement. This is completely reworked

5731 from the behavior in 1.3, which would instead create a subquery of

5732 the entire

5733 :class:`_expression.Select` and then join that subquery to the

5734 target.

5735

5736 This is a **backwards incompatible change** as the previous behavior

5737 was mostly useless, producing an unnamed subquery rejected by

5738 most databases in any case. The new behavior is modeled after

5739 that of the very successful :meth:`_orm.Query.join` method in the

5740 ORM, in order to support the functionality of :class:`_orm.Query`

5741 being available by using a :class:`_sql.Select` object with an

5742 :class:`_orm.Session`.

5743

5744 See the notes for this change at :ref:`change_select_join`.

5745

5746 .. seealso::

5747

5748 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5749

5750 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5751

5752 :meth:`_expression.Select.join`

5753

5754 """

5755 return self.join(target, onclause=onclause, isouter=True, full=full)

5756

5757 def get_final_froms(self) -> Sequence[FromClause]:

5758 """Compute the final displayed list of :class:`_expression.FromClause`

5759 elements.

5760

5761 This method will run through the full computation required to

5762 determine what FROM elements will be displayed in the resulting

5763 SELECT statement, including shadowing individual tables with

5764 JOIN objects, as well as full computation for ORM use cases including

5765 eager loading clauses.

5766

5767 For ORM use, this accessor returns the **post compilation**

5768 list of FROM objects; this collection will include elements such as

5769 eagerly loaded tables and joins. The objects will **not** be

5770 ORM enabled and not work as a replacement for the

5771 :meth:`_sql.Select.select_froms` collection; additionally, the

5772 method is not well performing for an ORM enabled statement as it

5773 will incur the full ORM construction process.

5774

5775 To retrieve the FROM list that's implied by the "columns" collection

5776 passed to the :class:`_sql.Select` originally, use the

5777 :attr:`_sql.Select.columns_clause_froms` accessor.

5778

5779 To select from an alternative set of columns while maintaining the

5780 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5781 pass the

5782 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5783 parameter.

5784

5785 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5786 method replaces the previous :attr:`_sql.Select.froms` accessor,

5787 which is deprecated.

5788

5789 .. seealso::

5790

5791 :attr:`_sql.Select.columns_clause_froms`

5792

5793 """

5794 compiler = self._default_compiler()

5795

5796 return self._compile_state_factory(self, compiler)._get_display_froms()

5797

5798 @property

5799 @util.deprecated(

5800 "1.4.23",

5801 "The :attr:`_expression.Select.froms` attribute is moved to "

5802 "the :meth:`_expression.Select.get_final_froms` method.",

5803 )

5804 def froms(self) -> Sequence[FromClause]:

5805 """Return the displayed list of :class:`_expression.FromClause`

5806 elements.

5807

5808

5809 """

5810 return self.get_final_froms()

5811

5812 @property

5813 def columns_clause_froms(self) -> List[FromClause]:

5814 """Return the set of :class:`_expression.FromClause` objects implied

5815 by the columns clause of this SELECT statement.

5816

5817 .. versionadded:: 1.4.23

5818

5819 .. seealso::

5820

5821 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5822 statement into account

5823

5824 :meth:`_sql.Select.with_only_columns` - makes use of this

5825 collection to set up a new FROM list

5826

5827 """

5828

5829 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5830 self

5831 )

5832

5833 @property

5834 def inner_columns(self) -> _SelectIterable:

5835 """An iterator of all :class:`_expression.ColumnElement`

5836 expressions which would

5837 be rendered into the columns clause of the resulting SELECT statement.

5838

5839 This method is legacy as of 1.4 and is superseded by the

5840 :attr:`_expression.Select.exported_columns` collection.

5841

5842 """

5843

5844 return iter(self._all_selected_columns)

5845

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

5847 if fromclause is not None and self in fromclause._cloned_set:

5848 return True

5849

5850 for f in self._iterate_from_elements():

5851 if f.is_derived_from(fromclause):

5852 return True

5853 return False

5854

5855 def _copy_internals(

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

5857 ) -> None:

5858 # Select() object has been cloned and probably adapted by the

5859 # given clone function. Apply the cloning function to internal

5860 # objects

5861

5862 # 1. keep a dictionary of the froms we've cloned, and what

5863 # they've become. This allows us to ensure the same cloned from

5864 # is used when other items such as columns are "cloned"

5865

5866 all_the_froms = set(

5867 itertools.chain(

5868 _from_objects(*self._raw_columns),

5869 _from_objects(*self._where_criteria),

5870 _from_objects(*[elem[0] for elem in self._setup_joins]),

5871 )

5872 )

5873

5874 # do a clone for the froms we've gathered. what is important here

5875 # is if any of the things we are selecting from, like tables,

5876 # were converted into Join objects. if so, these need to be

5877 # added to _from_obj explicitly, because otherwise they won't be

5878 # part of the new state, as they don't associate themselves with

5879 # their columns.

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

5881

5882 # 2. copy FROM collections, adding in joins that we've created.

5883 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5884 add_froms = (

5885 {f for f in new_froms.values() if isinstance(f, Join)}

5886 .difference(all_the_froms)

5887 .difference(existing_from_obj)

5888 )

5889

5890 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5891

5892 # 3. clone everything else, making sure we use columns

5893 # corresponding to the froms we just made.

5894 def replace(

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

5896 **kw: Any,

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

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

5899 newelem = new_froms[obj.table].corresponding_column(obj)

5900 return newelem

5901 return None

5902

5903 kw["replace"] = replace

5904

5905 # copy everything else. for table-ish things like correlate,

5906 # correlate_except, setup_joins, these clone normally. For

5907 # column-expression oriented things like raw_columns, where_criteria,

5908 # order by, we get this from the new froms.

5909 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5910

5911 self._reset_memoizations()

5912

5913 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5914 return itertools.chain(

5915 super().get_children(

5916 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5917 **kw,

5918 ),

5919 self._iterate_from_elements(),

5920 )

5921

5922 @_generative

5923 def add_columns(

5924 self, *entities: _ColumnsClauseArgument[Any]

5925 ) -> Select[Any]:

5926 r"""Return a new :func:`_expression.select` construct with

5927 the given entities appended to its columns clause.

5928

5929 E.g.::

5930

5931 my_select = my_select.add_columns(table.c.new_column)

5932

5933 The original expressions in the columns clause remain in place.

5934 To replace the original expressions with new ones, see the method

5935 :meth:`_expression.Select.with_only_columns`.

5936

5937 :param \*entities: column, table, or other entity expressions to be

5938 added to the columns clause

5939

5940 .. seealso::

5941

5942 :meth:`_expression.Select.with_only_columns` - replaces existing

5943 expressions rather than appending.

5944

5945 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5946 example

5947

5948 """

5949 self._reset_memoizations()

5950

5951 self._raw_columns = self._raw_columns + [

5952 coercions.expect(

5953 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5954 )

5955 for column in entities

5956 ]

5957 return self

5958

5959 def _set_entities(

5960 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5961 ) -> None:

5962 self._raw_columns = [

5963 coercions.expect(

5964 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5965 )

5966 for ent in util.to_list(entities)

5967 ]

5968

5969 @util.deprecated(

5970 "1.4",

5971 "The :meth:`_expression.Select.column` method is deprecated and will "

5972 "be removed in a future release. Please use "

5973 ":meth:`_expression.Select.add_columns`",

5974 )

5975 def column(self, column: _ColumnsClauseArgument[Any]) -> Select[Any]:

5976 """Return a new :func:`_expression.select` construct with

5977 the given column expression added to its columns clause.

5978

5979 E.g.::

5980

5981 my_select = my_select.column(table.c.new_column)

5982

5983 See the documentation for

5984 :meth:`_expression.Select.with_only_columns`

5985 for guidelines on adding /replacing the columns of a

5986 :class:`_expression.Select` object.

5987

5988 """

5989 return self.add_columns(column)

5990

5991 @util.preload_module("sqlalchemy.sql.util")

5992 def reduce_columns(self, only_synonyms: bool = True) -> Select[Any]:

5993 """Return a new :func:`_expression.select` construct with redundantly

5994 named, equivalently-valued columns removed from the columns clause.

5995

5996 "Redundant" here means two columns where one refers to the

5997 other either based on foreign key, or via a simple equality

5998 comparison in the WHERE clause of the statement. The primary purpose

5999 of this method is to automatically construct a select statement

6000 with all uniquely-named columns, without the need to use

6001 table-qualified labels as

6002 :meth:`_expression.Select.set_label_style`

6003 does.

6004

6005 When columns are omitted based on foreign key, the referred-to

6006 column is the one that's kept. When columns are omitted based on

6007 WHERE equivalence, the first column in the columns clause is the

6008 one that's kept.

6009

6010 :param only_synonyms: when True, limit the removal of columns

6011 to those which have the same name as the equivalent. Otherwise,

6012 all columns that are equivalent to another are removed.

6013

6014 """

6015 woc: Select[Any]

6016 woc = self.with_only_columns(

6017 *util.preloaded.sql_util.reduce_columns(

6018 self._all_selected_columns,

6019 only_synonyms=only_synonyms,

6020 *(self._where_criteria + self._from_obj),

6021 )

6022 )

6023 return woc

6024

6025 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6026

6027 # code within this block is **programmatically,

6028 # statically generated** by tools/generate_tuple_map_overloads.py

6029

6030 @overload

6031 def with_only_columns(

6032 self, __ent0: _TCCA[_T0], *, maintain_column_froms: bool = ...

6033 ) -> Select[Tuple[_T0]]: ...

6034

6035 @overload

6036 def with_only_columns(

6037 self,

6038 __ent0: _TCCA[_T0],

6039 __ent1: _TCCA[_T1],

6040 *,

6041 maintain_column_froms: bool = ...,

6042 ) -> Select[Tuple[_T0, _T1]]: ...

6043

6044 @overload

6045 def with_only_columns(

6046 self,

6047 __ent0: _TCCA[_T0],

6048 __ent1: _TCCA[_T1],

6049 __ent2: _TCCA[_T2],

6050 *,

6051 maintain_column_froms: bool = ...,

6052 ) -> Select[Tuple[_T0, _T1, _T2]]: ...

6053

6054 @overload

6055 def with_only_columns(

6056 self,

6057 __ent0: _TCCA[_T0],

6058 __ent1: _TCCA[_T1],

6059 __ent2: _TCCA[_T2],

6060 __ent3: _TCCA[_T3],

6061 *,

6062 maintain_column_froms: bool = ...,

6063 ) -> Select[Tuple[_T0, _T1, _T2, _T3]]: ...

6064

6065 @overload

6066 def with_only_columns(

6067 self,

6068 __ent0: _TCCA[_T0],

6069 __ent1: _TCCA[_T1],

6070 __ent2: _TCCA[_T2],

6071 __ent3: _TCCA[_T3],

6072 __ent4: _TCCA[_T4],

6073 *,

6074 maintain_column_froms: bool = ...,

6075 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...

6076

6077 @overload

6078 def with_only_columns(

6079 self,

6080 __ent0: _TCCA[_T0],

6081 __ent1: _TCCA[_T1],

6082 __ent2: _TCCA[_T2],

6083 __ent3: _TCCA[_T3],

6084 __ent4: _TCCA[_T4],

6085 __ent5: _TCCA[_T5],

6086 *,

6087 maintain_column_froms: bool = ...,

6088 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...

6089

6090 @overload

6091 def with_only_columns(

6092 self,

6093 __ent0: _TCCA[_T0],

6094 __ent1: _TCCA[_T1],

6095 __ent2: _TCCA[_T2],

6096 __ent3: _TCCA[_T3],

6097 __ent4: _TCCA[_T4],

6098 __ent5: _TCCA[_T5],

6099 __ent6: _TCCA[_T6],

6100 *,

6101 maintain_column_froms: bool = ...,

6102 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...

6103

6104 @overload

6105 def with_only_columns(

6106 self,

6107 __ent0: _TCCA[_T0],

6108 __ent1: _TCCA[_T1],

6109 __ent2: _TCCA[_T2],

6110 __ent3: _TCCA[_T3],

6111 __ent4: _TCCA[_T4],

6112 __ent5: _TCCA[_T5],

6113 __ent6: _TCCA[_T6],

6114 __ent7: _TCCA[_T7],

6115 *,

6116 maintain_column_froms: bool = ...,

6117 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...

6118

6119 # END OVERLOADED FUNCTIONS self.with_only_columns

6120

6121 @overload

6122 def with_only_columns(

6123 self,

6124 *entities: _ColumnsClauseArgument[Any],

6125 maintain_column_froms: bool = False,

6126 **__kw: Any,

6127 ) -> Select[Any]: ...

6128

6129 @_generative

6130 def with_only_columns(

6131 self,

6132 *entities: _ColumnsClauseArgument[Any],

6133 maintain_column_froms: bool = False,

6134 **__kw: Any,

6135 ) -> Select[Any]:

6136 r"""Return a new :func:`_expression.select` construct with its columns

6137 clause replaced with the given entities.

6138

6139 By default, this method is exactly equivalent to as if the original

6140 :func:`_expression.select` had been called with the given entities.

6141 E.g. a statement::

6142

6143 s = select(table1.c.a, table1.c.b)

6144 s = s.with_only_columns(table1.c.b)

6145

6146 should be exactly equivalent to::

6147

6148 s = select(table1.c.b)

6149

6150 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6151 will also dynamically alter the FROM clause of the

6152 statement if it is not explicitly stated.

6153 To maintain the existing set of FROMs including those implied by the

6154 current columns clause, add the

6155 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6156 parameter::

6157

6158 s = select(table1.c.a, table2.c.b)

6159 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6160

6161 The above parameter performs a transfer of the effective FROMs

6162 in the columns collection to the :meth:`_sql.Select.select_from`

6163 method, as though the following were invoked::

6164

6165 s = select(table1.c.a, table2.c.b)

6166 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6167

6168 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6169 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6170 collection and performs an operation equivalent to the following::

6171

6172 s = select(table1.c.a, table2.c.b)

6173 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6174

6175 :param \*entities: column expressions to be used.

6176

6177 :param maintain_column_froms: boolean parameter that will ensure the

6178 FROM list implied from the current columns clause will be transferred

6179 to the :meth:`_sql.Select.select_from` method first.

6180

6181 .. versionadded:: 1.4.23

6182

6183 """ # noqa: E501

6184

6185 if __kw:

6186 raise _no_kw()

6187

6188 # memoizations should be cleared here as of

6189 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6190 # is the case for now.

6191 self._assert_no_memoizations()

6192

6193 if maintain_column_froms:

6194 self.select_from.non_generative( # type: ignore

6195 self, *self.columns_clause_froms

6196 )

6197

6198 # then memoize the FROMs etc.

6199 _MemoizedSelectEntities._generate_for_statement(self)

6200

6201 self._raw_columns = [

6202 coercions.expect(roles.ColumnsClauseRole, c)

6203 for c in coercions._expression_collection_was_a_list(

6204 "entities", "Select.with_only_columns", entities

6205 )

6206 ]

6207 return self

6208

6209 @property

6210 def whereclause(self) -> Optional[ColumnElement[Any]]:

6211 """Return the completed WHERE clause for this

6212 :class:`_expression.Select` statement.

6213

6214 This assembles the current collection of WHERE criteria

6215 into a single :class:`_expression.BooleanClauseList` construct.

6216

6217

6218 .. versionadded:: 1.4

6219

6220 """

6221

6222 return BooleanClauseList._construct_for_whereclause(

6223 self._where_criteria

6224 )

6225

6226 _whereclause = whereclause

6227

6228 @_generative

6229 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6230 """Return a new :func:`_expression.select` construct with

6231 the given expression added to

6232 its WHERE clause, joined to the existing clause via AND, if any.

6233

6234 """

6235

6236 assert isinstance(self._where_criteria, tuple)

6237

6238 for criterion in whereclause:

6239 where_criteria: ColumnElement[Any] = coercions.expect(

6240 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6241 )

6242 self._where_criteria += (where_criteria,)

6243 return self

6244

6245 @_generative

6246 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6247 """Return a new :func:`_expression.select` construct with

6248 the given expression added to

6249 its HAVING clause, joined to the existing clause via AND, if any.

6250

6251 """

6252

6253 for criterion in having:

6254 having_criteria = coercions.expect(

6255 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6256 )

6257 self._having_criteria += (having_criteria,)

6258 return self

6259

6260 @_generative

6261 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6262 r"""Return a new :func:`_expression.select` construct which

6263 will apply DISTINCT to the SELECT statement overall.

6264

6265 E.g.::

6266

6267 from sqlalchemy import select

6268

6269 stmt = select(users_table.c.id, users_table.c.name).distinct()

6270

6271 The above would produce an statement resembling:

6272

6273 .. sourcecode:: sql

6274

6275 SELECT DISTINCT user.id, user.name FROM user

6276

6277 The method also accepts an ``*expr`` parameter which produces the

6278 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6279 parameter on other backends which don't support this syntax will

6280 raise an error.

6281

6282 :param \*expr: optional column expressions. When present,

6283 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6284 construct. A deprecation warning and/or :class:`_exc.CompileError`

6285 will be raised on other backends.

6286

6287 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6288 and will raise :class:`_exc.CompileError` in a future version.

6289

6290 """

6291 if expr:

6292 self._distinct = True

6293 self._distinct_on = self._distinct_on + tuple(

6294 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6295 for e in expr

6296 )

6297 else:

6298 self._distinct = True

6299 return self

6300

6301 @_generative

6302 def select_from(self, *froms: _FromClauseArgument) -> Self:

6303 r"""Return a new :func:`_expression.select` construct with the

6304 given FROM expression(s)

6305 merged into its list of FROM objects.

6306

6307 E.g.::

6308

6309 table1 = table("t1", column("a"))

6310 table2 = table("t2", column("b"))

6311 s = select(table1.c.a).select_from(

6312 table1.join(table2, table1.c.a == table2.c.b)

6313 )

6314

6315 The "from" list is a unique set on the identity of each element,

6316 so adding an already present :class:`_schema.Table`

6317 or other selectable

6318 will have no effect. Passing a :class:`_expression.Join` that refers

6319 to an already present :class:`_schema.Table`

6320 or other selectable will have

6321 the effect of concealing the presence of that selectable as

6322 an individual element in the rendered FROM list, instead

6323 rendering it into a JOIN clause.

6324

6325 While the typical purpose of :meth:`_expression.Select.select_from`

6326 is to

6327 replace the default, derived FROM clause with a join, it can

6328 also be called with individual table elements, multiple times

6329 if desired, in the case that the FROM clause cannot be fully

6330 derived from the columns clause::

6331

6332 select(func.count("*")).select_from(table1)

6333

6334 """

6335

6336 self._from_obj += tuple(

6337 coercions.expect(

6338 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6339 )

6340 for fromclause in froms

6341 )

6342 return self

6343

6344 @_generative

6345 def correlate(

6346 self,

6347 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6348 ) -> Self:

6349 r"""Return a new :class:`_expression.Select`

6350 which will correlate the given FROM

6351 clauses to that of an enclosing :class:`_expression.Select`.

6352

6353 Calling this method turns off the :class:`_expression.Select` object's

6354 default behavior of "auto-correlation". Normally, FROM elements

6355 which appear in a :class:`_expression.Select`

6356 that encloses this one via

6357 its :term:`WHERE clause`, ORDER BY, HAVING or

6358 :term:`columns clause` will be omitted from this

6359 :class:`_expression.Select`

6360 object's :term:`FROM clause`.

6361 Setting an explicit correlation collection using the

6362 :meth:`_expression.Select.correlate`

6363 method provides a fixed list of FROM objects

6364 that can potentially take place in this process.

6365

6366 When :meth:`_expression.Select.correlate`

6367 is used to apply specific FROM clauses

6368 for correlation, the FROM elements become candidates for

6369 correlation regardless of how deeply nested this

6370 :class:`_expression.Select`

6371 object is, relative to an enclosing :class:`_expression.Select`

6372 which refers to

6373 the same FROM object. This is in contrast to the behavior of

6374 "auto-correlation" which only correlates to an immediate enclosing

6375 :class:`_expression.Select`.

6376 Multi-level correlation ensures that the link

6377 between enclosed and enclosing :class:`_expression.Select`

6378 is always via

6379 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6380 correlation to take place.

6381

6382 If ``None`` is passed, the :class:`_expression.Select`

6383 object will correlate

6384 none of its FROM entries, and all will render unconditionally

6385 in the local FROM clause.

6386

6387 :param \*fromclauses: one or more :class:`.FromClause` or other

6388 FROM-compatible construct such as an ORM mapped entity to become part

6389 of the correlate collection; alternatively pass a single value

6390 ``None`` to remove all existing correlations.

6391

6392 .. seealso::

6393

6394 :meth:`_expression.Select.correlate_except`

6395

6396 :ref:`tutorial_scalar_subquery`

6397

6398 """

6399

6400 # tests failing when we try to change how these

6401 # arguments are passed

6402

6403 self._auto_correlate = False

6404 if not fromclauses or fromclauses[0] in {None, False}:

6405 if len(fromclauses) > 1:

6406 raise exc.ArgumentError(

6407 "additional FROM objects not accepted when "

6408 "passing None/False to correlate()"

6409 )

6410 self._correlate = ()

6411 else:

6412 self._correlate = self._correlate + tuple(

6413 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6414 )

6415 return self

6416

6417 @_generative

6418 def correlate_except(

6419 self,

6420 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6421 ) -> Self:

6422 r"""Return a new :class:`_expression.Select`

6423 which will omit the given FROM

6424 clauses from the auto-correlation process.

6425

6426 Calling :meth:`_expression.Select.correlate_except` turns off the

6427 :class:`_expression.Select` object's default behavior of

6428 "auto-correlation" for the given FROM elements. An element

6429 specified here will unconditionally appear in the FROM list, while

6430 all other FROM elements remain subject to normal auto-correlation

6431 behaviors.

6432

6433 If ``None`` is passed, or no arguments are passed,

6434 the :class:`_expression.Select` object will correlate all of its

6435 FROM entries.

6436

6437 :param \*fromclauses: a list of one or more

6438 :class:`_expression.FromClause`

6439 constructs, or other compatible constructs (i.e. ORM-mapped

6440 classes) to become part of the correlate-exception collection.

6441

6442 .. seealso::

6443

6444 :meth:`_expression.Select.correlate`

6445

6446 :ref:`tutorial_scalar_subquery`

6447

6448 """

6449

6450 self._auto_correlate = False

6451 if not fromclauses or fromclauses[0] in {None, False}:

6452 if len(fromclauses) > 1:

6453 raise exc.ArgumentError(

6454 "additional FROM objects not accepted when "

6455 "passing None/False to correlate_except()"

6456 )

6457 self._correlate_except = ()

6458 else:

6459 self._correlate_except = (self._correlate_except or ()) + tuple(

6460 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6461 )

6462

6463 return self

6464

6465 @HasMemoized_ro_memoized_attribute

6466 def selected_columns(

6467 self,

6468 ) -> ColumnCollection[str, ColumnElement[Any]]:

6469 """A :class:`_expression.ColumnCollection`

6470 representing the columns that

6471 this SELECT statement or similar construct returns in its result set,

6472 not including :class:`_sql.TextClause` constructs.

6473

6474 This collection differs from the :attr:`_expression.FromClause.columns`

6475 collection of a :class:`_expression.FromClause` in that the columns

6476 within this collection cannot be directly nested inside another SELECT

6477 statement; a subquery must be applied first which provides for the

6478 necessary parenthesization required by SQL.

6479

6480 For a :func:`_expression.select` construct, the collection here is

6481 exactly what would be rendered inside the "SELECT" statement, and the

6482 :class:`_expression.ColumnElement` objects are directly present as they

6483 were given, e.g.::

6484

6485 col1 = column("q", Integer)

6486 col2 = column("p", Integer)

6487 stmt = select(col1, col2)

6488

6489 Above, ``stmt.selected_columns`` would be a collection that contains

6490 the ``col1`` and ``col2`` objects directly. For a statement that is

6491 against a :class:`_schema.Table` or other

6492 :class:`_expression.FromClause`, the collection will use the

6493 :class:`_expression.ColumnElement` objects that are in the

6494 :attr:`_expression.FromClause.c` collection of the from element.

6495

6496 A use case for the :attr:`_sql.Select.selected_columns` collection is

6497 to allow the existing columns to be referenced when adding additional

6498 criteria, e.g.::

6499

6500 def filter_on_id(my_select, id):

6501 return my_select.where(my_select.selected_columns["id"] == id)

6502

6503

6504 stmt = select(MyModel)

6505

6506 # adds "WHERE id=:param" to the statement

6507 stmt = filter_on_id(stmt, 42)

6508

6509 .. note::

6510

6511 The :attr:`_sql.Select.selected_columns` collection does not

6512 include expressions established in the columns clause using the

6513 :func:`_sql.text` construct; these are silently omitted from the

6514 collection. To use plain textual column expressions inside of a

6515 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6516 construct.

6517

6518

6519 .. versionadded:: 1.4

6520

6521 """

6522

6523 # compare to SelectState._generate_columns_plus_names, which

6524 # generates the actual names used in the SELECT string. that

6525 # method is more complex because it also renders columns that are

6526 # fully ambiguous, e.g. same column more than once.

6527 conv = cast(

6528 "Callable[[Any], str]",

6529 SelectState._column_naming_convention(self._label_style),

6530 )

6531

6532 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6533 [

6534 (conv(c), c)

6535 for c in self._all_selected_columns

6536 if is_column_element(c)

6537 ]

6538 )

6539 return cc.as_readonly()

6540

6541 @HasMemoized_ro_memoized_attribute

6542 def _all_selected_columns(self) -> _SelectIterable:

6543 meth = SelectState.get_plugin_class(self).all_selected_columns

6544 return list(meth(self))

6545

6546 def _ensure_disambiguated_names(self) -> Select[Any]:

6547 if self._label_style is LABEL_STYLE_NONE:

6548 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6549 return self

6550

6551 def _generate_fromclause_column_proxies(

6552 self,

6553 subquery: FromClause,

6554 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6555 primary_key: ColumnSet,

6556 foreign_keys: Set[KeyedColumnElement[Any]],

6557 *,

6558 proxy_compound_columns: Optional[

6559 Iterable[Sequence[ColumnElement[Any]]]

6560 ] = None,

6561 ) -> None:

6562 """Generate column proxies to place in the exported ``.c``

6563 collection of a subquery."""

6564

6565 if proxy_compound_columns:

6566 extra_col_iterator = proxy_compound_columns

6567 prox = [

6568 c._make_proxy(

6569 subquery,

6570 key=proxy_key,

6571 name=required_label_name,

6572 name_is_truncatable=True,

6573 compound_select_cols=extra_cols,

6574 primary_key=primary_key,

6575 foreign_keys=foreign_keys,

6576 )

6577 for (

6578 (

6579 required_label_name,

6580 proxy_key,

6581 fallback_label_name,

6582 c,

6583 repeated,

6584 ),

6585 extra_cols,

6586 ) in (

6587 zip(

6588 self._generate_columns_plus_names(False),

6589 extra_col_iterator,

6590 )

6591 )

6592 if is_column_element(c)

6593 ]

6594 else:

6595 prox = [

6596 c._make_proxy(

6597 subquery,

6598 key=proxy_key,

6599 name=required_label_name,

6600 name_is_truncatable=True,

6601 primary_key=primary_key,

6602 foreign_keys=foreign_keys,

6603 )

6604 for (

6605 required_label_name,

6606 proxy_key,

6607 fallback_label_name,

6608 c,

6609 repeated,

6610 ) in (self._generate_columns_plus_names(False))

6611 if is_column_element(c)

6612 ]

6613

6614 columns._populate_separate_keys(prox)

6615

6616 def _needs_parens_for_grouping(self) -> bool:

6617 return self._has_row_limiting_clause or bool(

6618 self._order_by_clause.clauses

6619 )

6620

6621 def self_group(

6622 self, against: Optional[OperatorType] = None

6623 ) -> Union[SelectStatementGrouping[Self], Self]:

6624 """Return a 'grouping' construct as per the

6625 :class:`_expression.ClauseElement` specification.

6626

6627 This produces an element that can be embedded in an expression. Note

6628 that this method is called automatically as needed when constructing

6629 expressions and should not require explicit use.

6630

6631 """

6632 if (

6633 isinstance(against, CompoundSelect)

6634 and not self._needs_parens_for_grouping()

6635 ):

6636 return self

6637 else:

6638 return SelectStatementGrouping(self)

6639

6640 def union(

6641 self, *other: _SelectStatementForCompoundArgument[_TP]

6642 ) -> CompoundSelect[_TP]:

6643 r"""Return a SQL ``UNION`` of this select() construct against

6644 the given selectables provided as positional arguments.

6645

6646 :param \*other: one or more elements with which to create a

6647 UNION.

6648

6649 .. versionchanged:: 1.4.28

6650

6651 multiple elements are now accepted.

6652

6653 :param \**kwargs: keyword arguments are forwarded to the constructor

6654 for the newly created :class:`_sql.CompoundSelect` object.

6655

6656 """

6657 return CompoundSelect._create_union(self, *other)

6658

6659 def union_all(

6660 self, *other: _SelectStatementForCompoundArgument[_TP]

6661 ) -> CompoundSelect[_TP]:

6662 r"""Return a SQL ``UNION ALL`` of this select() construct against

6663 the given selectables provided as positional arguments.

6664

6665 :param \*other: one or more elements with which to create a

6666 UNION.

6667

6668 .. versionchanged:: 1.4.28

6669

6670 multiple elements are now accepted.

6671

6672 :param \**kwargs: keyword arguments are forwarded to the constructor

6673 for the newly created :class:`_sql.CompoundSelect` object.

6674

6675 """

6676 return CompoundSelect._create_union_all(self, *other)

6677

6678 def except_(

6679 self, *other: _SelectStatementForCompoundArgument[_TP]

6680 ) -> CompoundSelect[_TP]:

6681 r"""Return a SQL ``EXCEPT`` of this select() construct against

6682 the given selectable provided as positional arguments.

6683

6684 :param \*other: one or more elements with which to create a

6685 UNION.

6686

6687 .. versionchanged:: 1.4.28

6688

6689 multiple elements are now accepted.

6690

6691 """

6692 return CompoundSelect._create_except(self, *other)

6693

6694 def except_all(

6695 self, *other: _SelectStatementForCompoundArgument[_TP]

6696 ) -> CompoundSelect[_TP]:

6697 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6698 the given selectables provided as positional arguments.

6699

6700 :param \*other: one or more elements with which to create a

6701 UNION.

6702

6703 .. versionchanged:: 1.4.28

6704

6705 multiple elements are now accepted.

6706

6707 """

6708 return CompoundSelect._create_except_all(self, *other)

6709

6710 def intersect(

6711 self, *other: _SelectStatementForCompoundArgument[_TP]

6712 ) -> CompoundSelect[_TP]:

6713 r"""Return a SQL ``INTERSECT`` of this select() construct against

6714 the given selectables provided as positional arguments.

6715

6716 :param \*other: one or more elements with which to create a

6717 UNION.

6718

6719 .. versionchanged:: 1.4.28

6720

6721 multiple elements are now accepted.

6722

6723 :param \**kwargs: keyword arguments are forwarded to the constructor

6724 for the newly created :class:`_sql.CompoundSelect` object.

6725

6726 """

6727 return CompoundSelect._create_intersect(self, *other)

6728

6729 def intersect_all(

6730 self, *other: _SelectStatementForCompoundArgument[_TP]

6731 ) -> CompoundSelect[_TP]:

6732 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6733 against the given selectables provided as positional arguments.

6734

6735 :param \*other: one or more elements with which to create a

6736 UNION.

6737

6738 .. versionchanged:: 1.4.28

6739

6740 multiple elements are now accepted.

6741

6742 :param \**kwargs: keyword arguments are forwarded to the constructor

6743 for the newly created :class:`_sql.CompoundSelect` object.

6744

6745 """

6746 return CompoundSelect._create_intersect_all(self, *other)

6747

6748

6749class ScalarSelect(

6750 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6751):

6752 """Represent a scalar subquery.

6753

6754

6755 A :class:`_sql.ScalarSelect` is created by invoking the

6756 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6757 then participates in other SQL expressions as a SQL column expression

6758 within the :class:`_sql.ColumnElement` hierarchy.

6759

6760 .. seealso::

6761

6762 :meth:`_sql.SelectBase.scalar_subquery`

6763

6764 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6765

6766 """

6767

6768 _traverse_internals: _TraverseInternalsType = [

6769 ("element", InternalTraversal.dp_clauseelement),

6770 ("type", InternalTraversal.dp_type),

6771 ]

6772

6773 _from_objects: List[FromClause] = []

6774 _is_from_container = True

6775 if not TYPE_CHECKING:

6776 _is_implicitly_boolean = False

6777 inherit_cache = True

6778

6779 element: SelectBase

6780

6781 def __init__(self, element: SelectBase) -> None:

6782 self.element = element

6783 self.type = element._scalar_type()

6784 self._propagate_attrs = element._propagate_attrs

6785

6786 def __getattr__(self, attr: str) -> Any:

6787 return getattr(self.element, attr)

6788

6789 def __getstate__(self) -> Dict[str, Any]:

6790 return {"element": self.element, "type": self.type}

6791

6792 def __setstate__(self, state: Dict[str, Any]) -> None:

6793 self.element = state["element"]

6794 self.type = state["type"]

6795

6796 @property

6797 def columns(self) -> NoReturn:

6798 raise exc.InvalidRequestError(

6799 "Scalar Select expression has no "

6800 "columns; use this object directly "

6801 "within a column-level expression."

6802 )

6803

6804 c = columns

6805

6806 @_generative

6807 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6808 """Apply a WHERE clause to the SELECT statement referred to

6809 by this :class:`_expression.ScalarSelect`.

6810

6811 """

6812 self.element = cast("Select[Any]", self.element).where(crit)

6813 return self

6814

6815 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6816 return self

6817

6818 def _ungroup(self) -> Self:

6819 return self

6820

6821 @_generative

6822 def correlate(

6823 self,

6824 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6825 ) -> Self:

6826 r"""Return a new :class:`_expression.ScalarSelect`

6827 which will correlate the given FROM

6828 clauses to that of an enclosing :class:`_expression.Select`.

6829

6830 This method is mirrored from the :meth:`_sql.Select.correlate` method

6831 of the underlying :class:`_sql.Select`. The method applies the

6832 :meth:_sql.Select.correlate` method, then returns a new

6833 :class:`_sql.ScalarSelect` against that statement.

6834

6835 .. versionadded:: 1.4 Previously, the

6836 :meth:`_sql.ScalarSelect.correlate`

6837 method was only available from :class:`_sql.Select`.

6838

6839 :param \*fromclauses: a list of one or more

6840 :class:`_expression.FromClause`

6841 constructs, or other compatible constructs (i.e. ORM-mapped

6842 classes) to become part of the correlate collection.

6843

6844 .. seealso::

6845

6846 :meth:`_expression.ScalarSelect.correlate_except`

6847

6848 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6849

6850

6851 """

6852 self.element = cast("Select[Any]", self.element).correlate(

6853 *fromclauses

6854 )

6855 return self

6856

6857 @_generative

6858 def correlate_except(

6859 self,

6860 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6861 ) -> Self:

6862 r"""Return a new :class:`_expression.ScalarSelect`

6863 which will omit the given FROM

6864 clauses from the auto-correlation process.

6865

6866 This method is mirrored from the

6867 :meth:`_sql.Select.correlate_except` method of the underlying

6868 :class:`_sql.Select`. The method applies the

6869 :meth:_sql.Select.correlate_except` method, then returns a new

6870 :class:`_sql.ScalarSelect` against that statement.

6871

6872 .. versionadded:: 1.4 Previously, the

6873 :meth:`_sql.ScalarSelect.correlate_except`

6874 method was only available from :class:`_sql.Select`.

6875

6876 :param \*fromclauses: a list of one or more

6877 :class:`_expression.FromClause`

6878 constructs, or other compatible constructs (i.e. ORM-mapped

6879 classes) to become part of the correlate-exception collection.

6880

6881 .. seealso::

6882

6883 :meth:`_expression.ScalarSelect.correlate`

6884

6885 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6886

6887

6888 """

6889

6890 self.element = cast("Select[Any]", self.element).correlate_except(

6891 *fromclauses

6892 )

6893 return self

6894

6895

6896class Exists(UnaryExpression[bool]):

6897 """Represent an ``EXISTS`` clause.

6898

6899 See :func:`_sql.exists` for a description of usage.

6900

6901 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6902 instance by calling :meth:`_sql.SelectBase.exists`.

6903

6904 """

6905

6906 inherit_cache = True

6907

6908 def __init__(

6909 self,

6910 __argument: Optional[

6911 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6912 ] = None,

6913 ):

6914 s: ScalarSelect[Any]

6915

6916 # TODO: this seems like we should be using coercions for this

6917 if __argument is None:

6918 s = Select(literal_column("*")).scalar_subquery()

6919 elif isinstance(__argument, SelectBase):

6920 s = __argument.scalar_subquery()

6921 s._propagate_attrs = __argument._propagate_attrs

6922 elif isinstance(__argument, ScalarSelect):

6923 s = __argument

6924 else:

6925 s = Select(__argument).scalar_subquery()

6926

6927 UnaryExpression.__init__(

6928 self,

6929 s,

6930 operator=operators.exists,

6931 type_=type_api.BOOLEANTYPE,

6932 )

6933

6934 @util.ro_non_memoized_property

6935 def _from_objects(self) -> List[FromClause]:

6936 return []

6937

6938 def _regroup(

6939 self,

6940 fn: Callable[[Select[Any]], Select[Any]],

6941 ) -> ScalarSelect[Any]:

6942

6943 assert isinstance(self.element, ScalarSelect)

6944 element = self.element.element

6945 if not isinstance(element, Select):

6946 raise exc.InvalidRequestError(

6947 "Can only apply this operation to a plain SELECT construct"

6948 )

6949 new_element = fn(element)

6950

6951 return_value = new_element.scalar_subquery()

6952 return return_value

6953

6954 def select(self) -> Select[Tuple[bool]]:

6955 r"""Return a SELECT of this :class:`_expression.Exists`.

6956

6957 e.g.::

6958

6959 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6960

6961 This will produce a statement resembling:

6962

6963 .. sourcecode:: sql

6964

6965 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6966

6967 .. seealso::

6968

6969 :func:`_expression.select` - general purpose

6970 method which allows for arbitrary column lists.

6971

6972 """ # noqa

6973

6974 return Select(self)

6975

6976 def correlate(

6977 self,

6978 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6979 ) -> Self:

6980 """Apply correlation to the subquery noted by this

6981 :class:`_sql.Exists`.

6982

6983 .. seealso::

6984

6985 :meth:`_sql.ScalarSelect.correlate`

6986

6987 """

6988 e = self._clone()

6989 e.element = self._regroup(

6990 lambda element: element.correlate(*fromclauses)

6991 )

6992 return e

6993

6994 def correlate_except(

6995 self,

6996 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6997 ) -> Self:

6998 """Apply correlation to the subquery noted by this

6999 :class:`_sql.Exists`.

7000

7001 .. seealso::

7002

7003 :meth:`_sql.ScalarSelect.correlate_except`

7004

7005 """

7006 e = self._clone()

7007 e.element = self._regroup(

7008 lambda element: element.correlate_except(*fromclauses)

7009 )

7010 return e

7011

7012 def select_from(self, *froms: _FromClauseArgument) -> Self:

7013 """Return a new :class:`_expression.Exists` construct,

7014 applying the given

7015 expression to the :meth:`_expression.Select.select_from`

7016 method of the select

7017 statement contained.

7018

7019 .. note:: it is typically preferable to build a :class:`_sql.Select`

7020 statement first, including the desired WHERE clause, then use the

7021 :meth:`_sql.SelectBase.exists` method to produce an

7022 :class:`_sql.Exists` object at once.

7023

7024 """

7025 e = self._clone()

7026 e.element = self._regroup(lambda element: element.select_from(*froms))

7027 return e

7028

7029 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7030 """Return a new :func:`_expression.exists` construct with the

7031 given expression added to

7032 its WHERE clause, joined to the existing clause via AND, if any.

7033

7034

7035 .. note:: it is typically preferable to build a :class:`_sql.Select`

7036 statement first, including the desired WHERE clause, then use the

7037 :meth:`_sql.SelectBase.exists` method to produce an

7038 :class:`_sql.Exists` object at once.

7039

7040 """

7041 e = self._clone()

7042 e.element = self._regroup(lambda element: element.where(*clause))

7043 return e

7044

7045

7046class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7047 """Wrap a :class:`_expression.TextClause` construct within a

7048 :class:`_expression.SelectBase`

7049 interface.

7050

7051 This allows the :class:`_expression.TextClause` object to gain a

7052 ``.c`` collection

7053 and other FROM-like capabilities such as

7054 :meth:`_expression.FromClause.alias`,

7055 :meth:`_expression.SelectBase.cte`, etc.

7056

7057 The :class:`_expression.TextualSelect` construct is produced via the

7058 :meth:`_expression.TextClause.columns`

7059 method - see that method for details.

7060

7061 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7062 class was renamed

7063 from ``TextAsFrom``, to more correctly suit its role as a

7064 SELECT-oriented object and not a FROM clause.

7065

7066 .. seealso::

7067

7068 :func:`_expression.text`

7069

7070 :meth:`_expression.TextClause.columns` - primary creation interface.

7071

7072 """

7073

7074 __visit_name__ = "textual_select"

7075

7076 _label_style = LABEL_STYLE_NONE

7077

7078 _traverse_internals: _TraverseInternalsType = (

7079 [

7080 ("element", InternalTraversal.dp_clauseelement),

7081 ("column_args", InternalTraversal.dp_clauseelement_list),

7082 ]

7083 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7084 + HasCTE._has_ctes_traverse_internals

7085 + Executable._executable_traverse_internals

7086 )

7087

7088 _is_textual = True

7089

7090 is_text = True

7091 is_select = True

7092

7093 def __init__(

7094 self,

7095 text: TextClause,

7096 columns: List[_ColumnExpressionArgument[Any]],

7097 positional: bool = False,

7098 ) -> None:

7099 self._init(

7100 text,

7101 # convert for ORM attributes->columns, etc

7102 [

7103 coercions.expect(roles.LabeledColumnExprRole, c)

7104 for c in columns

7105 ],

7106 positional,

7107 )

7108

7109 def _init(

7110 self,

7111 text: TextClause,

7112 columns: List[NamedColumn[Any]],

7113 positional: bool = False,

7114 ) -> None:

7115 self.element = text

7116 self.column_args = columns

7117 self.positional = positional

7118

7119 @HasMemoized_ro_memoized_attribute

7120 def selected_columns(

7121 self,

7122 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7123 """A :class:`_expression.ColumnCollection`

7124 representing the columns that

7125 this SELECT statement or similar construct returns in its result set,

7126 not including :class:`_sql.TextClause` constructs.

7127

7128 This collection differs from the :attr:`_expression.FromClause.columns`

7129 collection of a :class:`_expression.FromClause` in that the columns

7130 within this collection cannot be directly nested inside another SELECT

7131 statement; a subquery must be applied first which provides for the

7132 necessary parenthesization required by SQL.

7133

7134 For a :class:`_expression.TextualSelect` construct, the collection

7135 contains the :class:`_expression.ColumnElement` objects that were

7136 passed to the constructor, typically via the

7137 :meth:`_expression.TextClause.columns` method.

7138

7139

7140 .. versionadded:: 1.4

7141

7142 """

7143 return ColumnCollection(

7144 (c.key, c) for c in self.column_args

7145 ).as_readonly()

7146

7147 @util.ro_non_memoized_property

7148 def _all_selected_columns(self) -> _SelectIterable:

7149 return self.column_args

7150

7151 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7152 return self

7153

7154 def _ensure_disambiguated_names(self) -> TextualSelect:

7155 return self

7156

7157 @_generative

7158 def bindparams(

7159 self,

7160 *binds: BindParameter[Any],

7161 **bind_as_values: Any,

7162 ) -> Self:

7163 self.element = self.element.bindparams(*binds, **bind_as_values)

7164 return self

7165

7166 def _generate_fromclause_column_proxies(

7167 self,

7168 fromclause: FromClause,

7169 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7170 primary_key: ColumnSet,

7171 foreign_keys: Set[KeyedColumnElement[Any]],

7172 *,

7173 proxy_compound_columns: Optional[

7174 Iterable[Sequence[ColumnElement[Any]]]

7175 ] = None,

7176 ) -> None:

7177 if TYPE_CHECKING:

7178 assert isinstance(fromclause, Subquery)

7179

7180 if proxy_compound_columns:

7181 columns._populate_separate_keys(

7182 c._make_proxy(

7183 fromclause,

7184 compound_select_cols=extra_cols,

7185 primary_key=primary_key,

7186 foreign_keys=foreign_keys,

7187 )

7188 for c, extra_cols in zip(

7189 self.column_args, proxy_compound_columns

7190 )

7191 )

7192 else:

7193 columns._populate_separate_keys(

7194 c._make_proxy(

7195 fromclause,

7196 primary_key=primary_key,

7197 foreign_keys=foreign_keys,

7198 )

7199 for c in self.column_args

7200 )

7201

7202 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7203 return self.column_args[0].type

7204

7205

7206TextAsFrom = TextualSelect

7207"""Backwards compatibility with the previous name"""

7208

7209

7210class AnnotatedFromClause(Annotated):

7211 def _copy_internals(self, **kw: Any) -> None:

7212 super()._copy_internals(**kw)

7213 if kw.get("ind_cols_on_fromclause", False):

7214 ee = self._Annotated__element # type: ignore

7215

7216 self.c = ee.__class__.c.fget(self) # type: ignore

7217

7218 @util.ro_memoized_property

7219 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7220 """proxy the .c collection of the underlying FromClause.

7221

7222 Originally implemented in 2008 as a simple load of the .c collection

7223 when the annotated construct was created (see d3621ae961a), in modern

7224 SQLAlchemy versions this can be expensive for statements constructed

7225 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7226 it, which works just as well.

7227

7228 Two different use cases seem to require the collection either copied

7229 from the underlying one, or unique to this AnnotatedFromClause.

7230

7231 See test_selectable->test_annotated_corresponding_column

7232

7233 """

7234 ee = self._Annotated__element # type: ignore

7235 return ee.c # type: ignore