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

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

1200 FROM table1 JOIN table2 ON true

1201

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

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

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

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

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

1207

1208

1209 .. versionadded:: 1.4

1210

1211 """ # noqa: E501

1212

1213 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1216 when generating the columns clause of a SELECT statement.

1217

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

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

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

1221

1222 .. sourcecode:: pycon+sql

1223

1224 >>> from sqlalchemy import (

1225 ... table,

1226 ... column,

1227 ... select,

1228 ... true,

1229 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1230 ... )

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

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

1233 >>> print(

1234 ... select(table1, table2)

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

1236 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1237 ... )

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

1239 FROM table1 JOIN table2 ON true

1240

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

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

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

1244

1245 .. versionadded:: 1.4

1246

1247 """ # noqa: E501

1248

1249 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1250 """The default label style, refers to

1251 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1252

1253 .. versionadded:: 1.4

1254

1255 """

1256

1257 LABEL_STYLE_LEGACY_ORM = 3

1258

1259

1260(

1261 LABEL_STYLE_NONE,

1262 LABEL_STYLE_TABLENAME_PLUS_COL,

1263 LABEL_STYLE_DISAMBIGUATE_ONLY,

1264 _,

1265) = list(SelectLabelStyle)

1266

1267LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1268

1269

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

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

1272 :class:`_expression.FromClause`

1273 elements.

1274

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

1276 is the module-level

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

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

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

1280 :class:`_schema.Table`).

1281

1282 .. seealso::

1283

1284 :func:`_expression.join`

1285

1286 :meth:`_expression.FromClause.join`

1287

1288 """

1289

1290 __visit_name__ = "join"

1291

1292 _traverse_internals: _TraverseInternalsType = [

1293 ("left", InternalTraversal.dp_clauseelement),

1294 ("right", InternalTraversal.dp_clauseelement),

1295 ("onclause", InternalTraversal.dp_clauseelement),

1296 ("isouter", InternalTraversal.dp_boolean),

1297 ("full", InternalTraversal.dp_boolean),

1298 ]

1299

1300 _is_join = True

1301

1302 left: FromClause

1303 right: FromClause

1304 onclause: Optional[ColumnElement[bool]]

1305 isouter: bool

1306 full: bool

1307

1308 def __init__(

1309 self,

1310 left: _FromClauseArgument,

1311 right: _FromClauseArgument,

1312 onclause: Optional[_OnClauseArgument] = None,

1313 isouter: bool = False,

1314 full: bool = False,

1315 ):

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

1317

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

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

1320 :class:`_expression.FromClause` object.

1321

1322 """

1323

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

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

1326 # annotated objects. test_orm.py -> test_fetch_results

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

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

1329 # callcounts for a single compilation in that particular test

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

1331 # 29200 -> 30373

1332

1333 self.left = coercions.expect(

1334 roles.FromClauseRole,

1335 left,

1336 )

1337 self.right = coercions.expect(

1338 roles.FromClauseRole,

1339 right,

1340 ).self_group()

1341

1342 if onclause is None:

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

1344 else:

1345 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1346 # not merged yet

1347 self.onclause = coercions.expect(

1348 roles.OnClauseRole, onclause

1349 ).self_group(against=operators._asbool)

1350

1351 self.isouter = isouter

1352 self.full = full

1353

1354 @util.ro_non_memoized_property

1355 def description(self) -> str:

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

1357 self.left.description,

1358 id(self.left),

1359 self.right.description,

1360 id(self.right),

1361 )

1362

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

1364 return (

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

1366 # as well

1367 hash(fromclause) == hash(self)

1368 or self.left.is_derived_from(fromclause)

1369 or self.right.is_derived_from(fromclause)

1370 )

1371

1372 def self_group(

1373 self, against: Optional[OperatorType] = None

1374 ) -> FromGrouping:

1375 return FromGrouping(self)

1376

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

1378 def _populate_column_collection(

1379 self,

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

1381 primary_key: ColumnSet,

1382 foreign_keys: Set[KeyedColumnElement[Any]],

1383 ) -> None:

1384 sqlutil = util.preloaded.sql_util

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

1386 c for c in self.right.c

1387 ]

1388

1389 primary_key.extend(

1390 sqlutil.reduce_columns(

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

1392 )

1393 )

1394 columns._populate_separate_keys(

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

1396 )

1397 foreign_keys.update(

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

1399 )

1400

1401 def _copy_internals(

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

1403 ) -> None:

1404 # see Select._copy_internals() for similar concept

1405

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

1407 # determine the new FROM clauses

1408 all_the_froms = set(

1409 itertools.chain(

1410 _from_objects(self.left),

1411 _from_objects(self.right),

1412 )

1413 )

1414

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

1416 # cache used by the clone function

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

1418

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

1420 # ColumnClause with parent table referring to those

1421 # replaced FromClause objects

1422 def replace(

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

1424 **kw: Any,

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

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

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

1428 return newelem

1429 return None

1430

1431 kw["replace"] = replace

1432

1433 # run normal _copy_internals. the clones for

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

1435 # cache

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

1437

1438 self._reset_memoizations()

1439

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

1441 super()._refresh_for_new_column(column)

1442 self.left._refresh_for_new_column(column)

1443 self.right._refresh_for_new_column(column)

1444

1445 def _match_primaries(

1446 self,

1447 left: FromClause,

1448 right: FromClause,

1449 ) -> ColumnElement[bool]:

1450 if isinstance(left, Join):

1451 left_right = left.right

1452 else:

1453 left_right = None

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

1455

1456 @classmethod

1457 def _join_condition(

1458 cls,

1459 a: FromClause,

1460 b: FromClause,

1461 *,

1462 a_subset: Optional[FromClause] = None,

1463 consider_as_foreign_keys: Optional[

1464 AbstractSet[ColumnClause[Any]]

1465 ] = None,

1466 ) -> ColumnElement[bool]:

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

1468

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

1470

1471 """

1472 constraints = cls._joincond_scan_left_right(

1473 a, a_subset, b, consider_as_foreign_keys

1474 )

1475

1476 if len(constraints) > 1:

1477 cls._joincond_trim_constraints(

1478 a, b, constraints, consider_as_foreign_keys

1479 )

1480

1481 if len(constraints) == 0:

1482 if isinstance(b, FromGrouping):

1483 hint = (

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

1485 "subquery using alias()?"

1486 )

1487 else:

1488 hint = ""

1489 raise exc.NoForeignKeysError(

1490 "Can't find any foreign key relationships "

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

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

1493 )

1494

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

1496 if len(crit) == 1:

1497 return crit[0]

1498 else:

1499 return and_(*crit)

1500

1501 @classmethod

1502 def _can_join(

1503 cls,

1504 left: FromClause,

1505 right: FromClause,

1506 *,

1507 consider_as_foreign_keys: Optional[

1508 AbstractSet[ColumnClause[Any]]

1509 ] = None,

1510 ) -> bool:

1511 if isinstance(left, Join):

1512 left_right = left.right

1513 else:

1514 left_right = None

1515

1516 constraints = cls._joincond_scan_left_right(

1517 a=left,

1518 b=right,

1519 a_subset=left_right,

1520 consider_as_foreign_keys=consider_as_foreign_keys,

1521 )

1522

1523 return bool(constraints)

1524

1525 @classmethod

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

1527 def _joincond_scan_left_right(

1528 cls,

1529 a: FromClause,

1530 a_subset: Optional[FromClause],

1531 b: FromClause,

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

1533 ) -> collections.defaultdict[

1534 Optional[ForeignKeyConstraint],

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

1536 ]:

1537 sql_util = util.preloaded.sql_util

1538

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

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

1541

1542 constraints: collections.defaultdict[

1543 Optional[ForeignKeyConstraint],

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

1545 ] = collections.defaultdict(list)

1546

1547 for left in (a_subset, a):

1548 if left is None:

1549 continue

1550 for fk in sorted(

1551 b.foreign_keys,

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

1553 ):

1554 if (

1555 consider_as_foreign_keys is not None

1556 and fk.parent not in consider_as_foreign_keys

1557 ):

1558 continue

1559 try:

1560 col = fk.get_referent(left)

1561 except exc.NoReferenceError as nrte:

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

1563 if nrte.table_name in table_names:

1564 raise

1565 else:

1566 continue

1567

1568 if col is not None:

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

1570 if left is not b:

1571 for fk in sorted(

1572 left.foreign_keys,

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

1574 ):

1575 if (

1576 consider_as_foreign_keys is not None

1577 and fk.parent not in consider_as_foreign_keys

1578 ):

1579 continue

1580 try:

1581 col = fk.get_referent(b)

1582 except exc.NoReferenceError as nrte:

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

1584 if nrte.table_name in table_names:

1585 raise

1586 else:

1587 continue

1588

1589 if col is not None:

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

1591 if constraints:

1592 break

1593 return constraints

1594

1595 @classmethod

1596 def _joincond_trim_constraints(

1597 cls,

1598 a: FromClause,

1599 b: FromClause,

1600 constraints: Dict[Any, Any],

1601 consider_as_foreign_keys: Optional[Any],

1602 ) -> None:

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

1604 # to include just those FKCs that match exactly to

1605 # "consider_as_foreign_keys".

1606 if consider_as_foreign_keys:

1607 for const in list(constraints):

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

1609 consider_as_foreign_keys

1610 ):

1611 del constraints[const]

1612

1613 # if still multiple constraints, but

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

1615 if len(constraints) > 1:

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

1617 if len(dedupe) == 1:

1618 key = list(constraints)[0]

1619 constraints = {key: constraints[key]}

1620

1621 if len(constraints) != 1:

1622 raise exc.AmbiguousForeignKeysError(

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

1624 "tables have more than one foreign key "

1625 "constraint relationship between them. "

1626 "Please specify the 'onclause' of this "

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

1628 )

1629

1630 @overload

1631 def select(

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

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

1634 @overload

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

1636

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

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

1639 :class:`_expression.Join`.

1640

1641 E.g.::

1642

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

1644

1645 stmt = stmt.select()

1646

1647 The above will produce a SQL string resembling:

1648

1649 .. sourcecode:: sql

1650

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

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

1653

1654 """

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

1656

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

1658 def _anonymous_fromclause(

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

1660 ) -> TODO_Any:

1661 sqlutil = util.preloaded.sql_util

1662 if flat:

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

1664 left_name = name # will recurse

1665 else:

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

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

1668 else:

1669 left_name = name

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

1671 right_name = name # will recurse

1672 else:

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

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

1675 else:

1676 right_name = name

1677 left_a, right_a = (

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

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

1680 )

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

1682 sqlutil.ClauseAdapter(right_a)

1683 )

1684

1685 return left_a.join(

1686 right_a,

1687 adapter.traverse(self.onclause),

1688 isouter=self.isouter,

1689 full=self.full,

1690 )

1691 else:

1692 return (

1693 self.select()

1694 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1695 .correlate(None)

1696 .alias(name)

1697 )

1698

1699 @util.ro_non_memoized_property

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

1701 return itertools.chain(

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

1703 )

1704

1705 @util.ro_non_memoized_property

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

1707 self_list: List[FromClause] = [self]

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

1709

1710 if TYPE_CHECKING:

1711

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

1713

1714

1715class NoInit:

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

1717 raise NotImplementedError(

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

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

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

1721 "selectable objects."

1722 % (

1723 self.__class__.__name__,

1724 self.__class__.__name__.lower(),

1725 self.__class__.__name__.lower(),

1726 )

1727 )

1728

1729

1730class LateralFromClause(NamedFromClause):

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

1732

1733

1734# FromClause ->

1735# AliasedReturnsRows

1736# -> Alias only for FromClause

1737# -> Subquery only for SelectBase

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

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

1740# w/ non-deprecated coercion

1741# -> TableSample -> only for FromClause

1742

1743

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

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

1746 selectables."""

1747

1748 _is_from_container = True

1749

1750 _supports_derived_columns = False

1751

1752 element: ReturnsRows

1753

1754 _traverse_internals: _TraverseInternalsType = [

1755 ("element", InternalTraversal.dp_clauseelement),

1756 ("name", InternalTraversal.dp_anon_name),

1757 ]

1758

1759 @classmethod

1760 def _construct(

1761 cls,

1762 selectable: Any,

1763 *,

1764 name: Optional[str] = None,

1765 **kw: Any,

1766 ) -> Self:

1767 obj = cls.__new__(cls)

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

1769 return obj

1770

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

1772 self.element = coercions.expect(

1773 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1774 )

1775 self.element = selectable

1776 self._orig_name = name

1777 if name is None:

1778 if (

1779 isinstance(selectable, FromClause)

1780 and selectable.named_with_column

1781 ):

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

1783 if isinstance(name, _anonymous_label):

1784 name = None

1785 name = _anonymous_label.safe_construct(

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

1787 )

1788 self.name = name

1789

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

1791 super()._refresh_for_new_column(column)

1792 self.element._refresh_for_new_column(column)

1793

1794 def _populate_column_collection(

1795 self,

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

1797 primary_key: ColumnSet,

1798 foreign_keys: Set[KeyedColumnElement[Any]],

1799 ) -> None:

1800 self.element._generate_fromclause_column_proxies(

1801 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1802 )

1803

1804 @util.ro_non_memoized_property

1805 def description(self) -> str:

1806 name = self.name

1807 if isinstance(name, _anonymous_label):

1808 return "anon_1"

1809

1810 return name

1811

1812 @util.ro_non_memoized_property

1813 def implicit_returning(self) -> bool:

1814 return self.element.implicit_returning # type: ignore

1815

1816 @property

1817 def original(self) -> ReturnsRows:

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

1819 return self.element

1820

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

1822 if fromclause in self._cloned_set:

1823 return True

1824 return self.element.is_derived_from(fromclause)

1825

1826 def _copy_internals(

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

1828 ) -> None:

1829 existing_element = self.element

1830

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

1832

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

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

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

1836 # performance.

1837 if existing_element is not self.element:

1838 self._reset_column_collection()

1839

1840 @property

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

1842 return [self]

1843

1844

1845class FromClauseAlias(AliasedReturnsRows[_KeyColCC_co]):

1846 element: FromClause[_KeyColCC_co]

1847

1848 @util.ro_non_memoized_property

1849 def description(self) -> str:

1850 name = self.name

1851 if isinstance(name, _anonymous_label):

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

1853

1854 return name

1855

1856

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

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

1859

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

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

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

1863

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

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

1866 method available

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

1868

1869 .. seealso::

1870

1871 :meth:`_expression.FromClause.alias`

1872

1873 """

1874

1875 __visit_name__ = "alias"

1876

1877 inherit_cache = True

1878

1879 element: FromClause[_KeyColCC_co]

1880

1881 @classmethod

1882 def _factory(

1883 cls,

1884 selectable: FromClause[_KeyColCC_co],

1885 name: Optional[str] = None,

1886 flat: bool = False,

1887 ) -> NamedFromClause[_KeyColCC_co]:

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

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

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

1891 name=name, flat=flat

1892 )

1893

1894

1895class TableValuedAlias(LateralFromClause, Alias):

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

1897

1898 This construct provides for a SQL function that returns columns

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

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

1901 method, e.g.:

1902

1903 .. sourcecode:: pycon+sql

1904

1905 >>> from sqlalchemy import select, func

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

1907 ... "value"

1908 ... )

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

1910 {printsql}SELECT anon_1.value

1911 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1912

1913 .. versionadded:: 1.4.0b2

1914

1915 .. seealso::

1916

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

1918

1919 """ # noqa: E501

1920

1921 __visit_name__ = "table_valued_alias"

1922

1923 _supports_derived_columns = True

1924 _render_derived = False

1925 _render_derived_w_types = False

1926 joins_implicitly = False

1927

1928 _traverse_internals: _TraverseInternalsType = [

1929 ("element", InternalTraversal.dp_clauseelement),

1930 ("name", InternalTraversal.dp_anon_name),

1931 ("_tableval_type", InternalTraversal.dp_type),

1932 ("_render_derived", InternalTraversal.dp_boolean),

1933 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1934 ]

1935

1936 def _init(

1937 self,

1938 selectable: Any,

1939 *,

1940 name: Optional[str] = None,

1941 table_value_type: Optional[TableValueType] = None,

1942 joins_implicitly: bool = False,

1943 ) -> None:

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

1945

1946 self.joins_implicitly = joins_implicitly

1947 self._tableval_type = (

1948 type_api.TABLEVALUE

1949 if table_value_type is None

1950 else table_value_type

1951 )

1952

1953 @HasMemoized.memoized_attribute

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

1955 """Return a column expression representing this

1956 :class:`_sql.TableValuedAlias`.

1957

1958 This accessor is used to implement the

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

1960 method for further details.

1961

1962 E.g.:

1963

1964 .. sourcecode:: pycon+sql

1965

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

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

1968

1969 .. seealso::

1970

1971 :meth:`_functions.FunctionElement.column_valued`

1972

1973 """

1974

1975 return TableValuedColumn(self, self._tableval_type)

1976

1977 def alias(

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

1979 ) -> TableValuedAlias:

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

1981

1982 This creates a distinct FROM object that will be distinguished

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

1984

1985 """

1986

1987 tva: TableValuedAlias = TableValuedAlias._construct(

1988 self,

1989 name=name,

1990 table_value_type=self._tableval_type,

1991 joins_implicitly=self.joins_implicitly,

1992 )

1993

1994 if self._render_derived:

1995 tva._render_derived = True

1996 tva._render_derived_w_types = self._render_derived_w_types

1997

1998 return tva

1999

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

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

2002 set, so that it renders as LATERAL.

2003

2004 .. seealso::

2005

2006 :func:`_expression.lateral`

2007

2008 """

2009 tva = self.alias(name=name)

2010 tva._is_lateral = True

2011 return tva

2012

2013 def render_derived(

2014 self,

2015 name: Optional[str] = None,

2016 with_types: bool = False,

2017 ) -> TableValuedAlias:

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

2019

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

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

2022

2023 .. sourcecode:: pycon+sql

2024

2025 >>> print(

2026 ... select(

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

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

2029 ... .render_derived()

2030 ... )

2031 ... )

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

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

2034

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

2036 the alias expression (this syntax currently applies to the

2037 PostgreSQL database):

2038

2039 .. sourcecode:: pycon+sql

2040

2041 >>> print(

2042 ... select(

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

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

2045 ... .render_derived(with_types=True)

2046 ... )

2047 ... )

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

2049 AS anon_1(a INTEGER, b VARCHAR)

2050

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

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

2053

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

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

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

2057

2058 """ # noqa: E501

2059

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

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

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

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

2064 # (just saw it happen on CI)

2065

2066 # construct against original to prevent memory growth

2067 # for repeated generations

2068 new_alias: TableValuedAlias = TableValuedAlias._construct(

2069 self.element,

2070 name=name,

2071 table_value_type=self._tableval_type,

2072 joins_implicitly=self.joins_implicitly,

2073 )

2074 new_alias._render_derived = True

2075 new_alias._render_derived_w_types = with_types

2076 return new_alias

2077

2078

2079class Lateral(FromClauseAlias, LateralFromClause):

2080 """Represent a LATERAL subquery.

2081

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

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

2084 method available

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

2086

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

2088 PostgreSQL versions provide support for this keyword.

2089

2090 .. seealso::

2091

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

2093

2094 """

2095

2096 __visit_name__ = "lateral"

2097 _is_lateral = True

2098

2099 inherit_cache = True

2100

2101 @classmethod

2102 def _factory(

2103 cls,

2104 selectable: Union[SelectBase, _FromClauseArgument],

2105 name: Optional[str] = None,

2106 ) -> LateralFromClause:

2107 return coercions.expect(

2108 roles.FromClauseRole, selectable, explicit_subquery=True

2109 ).lateral(name=name)

2110

2111

2112class TableSample(FromClauseAlias):

2113 """Represent a TABLESAMPLE clause.

2114

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

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

2117 method

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

2119

2120 .. seealso::

2121

2122 :func:`_expression.tablesample`

2123

2124 """

2125

2126 __visit_name__ = "tablesample"

2127

2128 _traverse_internals: _TraverseInternalsType = (

2129 AliasedReturnsRows._traverse_internals

2130 + [

2131 ("sampling", InternalTraversal.dp_clauseelement),

2132 ("seed", InternalTraversal.dp_clauseelement),

2133 ]

2134 )

2135

2136 @classmethod

2137 def _factory(

2138 cls,

2139 selectable: _FromClauseArgument,

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

2141 name: Optional[str] = None,

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

2143 ) -> TableSample:

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

2145 sampling, name=name, seed=seed

2146 )

2147

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

2149 def _init( # type: ignore[override]

2150 self,

2151 selectable: Any,

2152 *,

2153 name: Optional[str] = None,

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

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

2156 ) -> None:

2157 assert sampling is not None

2158 functions = util.preloaded.sql_functions

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

2160 sampling = functions.func.system(sampling)

2161

2162 self.sampling: Function[Any] = sampling

2163 self.seed = seed

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

2165

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

2167 return self.sampling

2168

2169

2170class CTE(

2171 roles.DMLTableRole,

2172 roles.IsCTERole,

2173 Generative,

2174 HasPrefixes,

2175 HasSuffixes,

2176 AliasedReturnsRows[_KeyColCC_co],

2177):

2178 """Represent a Common Table Expression.

2179

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

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

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

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

2184 :class:`_sql.Update` and

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

2186 usage details on CTEs.

2187

2188 .. seealso::

2189

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

2191

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

2193

2194 """

2195

2196 __visit_name__ = "cte"

2197

2198 _traverse_internals: _TraverseInternalsType = (

2199 AliasedReturnsRows._traverse_internals

2200 + [

2201 ("_cte_alias", InternalTraversal.dp_clauseelement),

2202 ("_restates", InternalTraversal.dp_clauseelement),

2203 ("recursive", InternalTraversal.dp_boolean),

2204 ("nesting", InternalTraversal.dp_boolean),

2205 ]

2206 + HasPrefixes._has_prefixes_traverse_internals

2207 + HasSuffixes._has_suffixes_traverse_internals

2208 )

2209

2210 element: HasCTE

2211

2212 @classmethod

2213 def _factory(

2214 cls,

2215 selectable: HasCTE,

2216 name: Optional[str] = None,

2217 recursive: bool = False,

2218 ) -> CTE:

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

2220 or Common Table Expression instance.

2221

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

2223

2224 """

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

2226 name=name, recursive=recursive

2227 )

2228

2229 def _init(

2230 self,

2231 selectable: HasCTE,

2232 *,

2233 name: Optional[str] = None,

2234 recursive: bool = False,

2235 nesting: bool = False,

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

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

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

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

2240 ) -> None:

2241 self.recursive = recursive

2242 self.nesting = nesting

2243 self._cte_alias = _cte_alias

2244 # Keep recursivity reference with union/union_all

2245 self._restates = _restates

2246 if _prefixes:

2247 self._prefixes = _prefixes

2248 if _suffixes:

2249 self._suffixes = _suffixes

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

2251

2252 def _populate_column_collection(

2253 self,

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

2255 primary_key: ColumnSet,

2256 foreign_keys: Set[KeyedColumnElement[Any]],

2257 ) -> None:

2258 if self._cte_alias is not None:

2259 self._cte_alias._generate_fromclause_column_proxies(

2260 self,

2261 columns,

2262 primary_key=primary_key,

2263 foreign_keys=foreign_keys,

2264 )

2265 else:

2266 self.element._generate_fromclause_column_proxies(

2267 self,

2268 columns,

2269 primary_key=primary_key,

2270 foreign_keys=foreign_keys,

2271 )

2272

2273 def alias(

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

2275 ) -> CTE[_KeyColCC_co]:

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

2277 :class:`_expression.CTE`.

2278

2279 This method is a CTE-specific specialization of the

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

2281

2282 .. seealso::

2283

2284 :ref:`tutorial_using_aliases`

2285

2286 :func:`_expression.alias`

2287

2288 """

2289 return CTE._construct(

2290 self.element,

2291 name=name,

2292 recursive=self.recursive,

2293 nesting=self.nesting,

2294 _cte_alias=self,

2295 _prefixes=self._prefixes,

2296 _suffixes=self._suffixes,

2297 )

2298

2299 def union(

2300 self, *other: _SelectStatementForCompoundArgument[Any]

2301 ) -> CTE[_KeyColCC_co]:

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

2303 of the original CTE against the given selectables provided

2304 as positional arguments.

2305

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

2307 UNION.

2308

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

2310

2311 .. seealso::

2312

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

2314

2315 """

2316 assert is_select_statement(

2317 self.element

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

2319

2320 return CTE._construct(

2321 self.element.union(*other),

2322 name=self.name,

2323 recursive=self.recursive,

2324 nesting=self.nesting,

2325 _restates=self,

2326 _prefixes=self._prefixes,

2327 _suffixes=self._suffixes,

2328 )

2329

2330 def union_all(

2331 self, *other: _SelectStatementForCompoundArgument[Any]

2332 ) -> CTE[_KeyColCC_co]:

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

2334 of the original CTE against the given selectables provided

2335 as positional arguments.

2336

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

2338 UNION.

2339

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

2341

2342 .. seealso::

2343

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

2345

2346 """

2347

2348 assert is_select_statement(

2349 self.element

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

2351

2352 return CTE._construct(

2353 self.element.union_all(*other),

2354 name=self.name,

2355 recursive=self.recursive,

2356 nesting=self.nesting,

2357 _restates=self,

2358 _prefixes=self._prefixes,

2359 _suffixes=self._suffixes,

2360 )

2361

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

2363 """

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

2365 Updated CTEs should still refer to the original CTE.

2366 This function returns this reference identifier.

2367 """

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

2369

2370 if TYPE_CHECKING:

2371

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

2373

2374

2375class _CTEOpts(NamedTuple):

2376 nesting: bool

2377

2378

2379class _ColumnsPlusNames(NamedTuple):

2380 required_label_name: Optional[str]

2381 """

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

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

2384 """

2385

2386 proxy_key: Optional[str]

2387 """

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

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

2390 select.selected_columns collection

2391 """

2392

2393 fallback_label_name: Optional[str]

2394 """

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

2396 we have to render a label even though

2397 required_label_name was not given

2398 """

2399

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

2401 """

2402 the ColumnElement itself

2403 """

2404

2405 repeated: bool

2406 """

2407 True if this is a duplicate of a previous column

2408 in the list of columns

2409 """

2410

2411

2412class SelectsRows(ReturnsRows):

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

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

2415

2416 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2417

2418 def _generate_columns_plus_names(

2419 self,

2420 anon_for_dupe_key: bool,

2421 cols: Optional[_SelectIterable] = None,

2422 ) -> List[_ColumnsPlusNames]:

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

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

2425 on a :class:`.FromClause`.

2426

2427 This is distinct from the _column_naming_convention generator that's

2428 intended for population of the Select.selected_columns collection,

2429 different rules. the collection returned here calls upon the

2430 _column_naming_convention as well.

2431

2432 """

2433

2434 if cols is None:

2435 cols = self._all_selected_columns

2436

2437 key_naming_convention = SelectState._column_naming_convention(

2438 self._label_style

2439 )

2440

2441 names = {}

2442

2443 result: List[_ColumnsPlusNames] = []

2444 result_append = result.append

2445

2446 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2447 label_style_none = self._label_style is LABEL_STYLE_NONE

2448

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

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

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

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

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

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

2455 dedupe_hash = 1

2456

2457 for c in cols:

2458 repeated = False

2459

2460 if not c._render_label_in_columns_clause:

2461 effective_name = required_label_name = fallback_label_name = (

2462 None

2463 )

2464 elif label_style_none:

2465 if TYPE_CHECKING:

2466 assert is_column_element(c)

2467

2468 effective_name = required_label_name = None

2469 fallback_label_name = c._non_anon_label or c._anon_name_label

2470 else:

2471 if TYPE_CHECKING:

2472 assert is_column_element(c)

2473

2474 if table_qualified:

2475 required_label_name = effective_name = (

2476 fallback_label_name

2477 ) = c._tq_label

2478 else:

2479 effective_name = fallback_label_name = c._non_anon_label

2480 required_label_name = None

2481

2482 if effective_name is None:

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

2484 # not need _expression_label but it isn't

2485 # giving us a clue when to use anon_label instead

2486 expr_label = c._expression_label

2487 if expr_label is None:

2488 repeated = c._anon_name_label in names

2489 names[c._anon_name_label] = c

2490 effective_name = required_label_name = None

2491

2492 if repeated:

2493 # here, "required_label_name" is sent as

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

2495 if table_qualified:

2496 fallback_label_name = (

2497 c._dedupe_anon_tq_label_idx(dedupe_hash)

2498 )

2499 dedupe_hash += 1

2500 else:

2501 fallback_label_name = c._dedupe_anon_label_idx(

2502 dedupe_hash

2503 )

2504 dedupe_hash += 1

2505 else:

2506 fallback_label_name = c._anon_name_label

2507 else:

2508 required_label_name = effective_name = (

2509 fallback_label_name

2510 ) = expr_label

2511

2512 if effective_name is not None:

2513 if TYPE_CHECKING:

2514 assert is_column_element(c)

2515

2516 if effective_name in names:

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

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

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

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

2521 # different column under the same name. apply

2522 # disambiguating label

2523 if table_qualified:

2524 required_label_name = fallback_label_name = (

2525 c._anon_tq_label

2526 )

2527 else:

2528 required_label_name = fallback_label_name = (

2529 c._anon_name_label

2530 )

2531

2532 if anon_for_dupe_key and required_label_name in names:

2533 # here, c._anon_tq_label is definitely unique to

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

2535 # this should always be true.

2536 # this is also an infrequent codepath because

2537 # you need two levels of duplication to be here

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

2539

2540 # the column under the disambiguating label is

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

2542 # subsequent occurrences of the column so that the

2543 # original stays non-ambiguous

2544 if table_qualified:

2545 required_label_name = fallback_label_name = (

2546 c._dedupe_anon_tq_label_idx(dedupe_hash)

2547 )

2548 dedupe_hash += 1

2549 else:

2550 required_label_name = fallback_label_name = (

2551 c._dedupe_anon_label_idx(dedupe_hash)

2552 )

2553 dedupe_hash += 1

2554 repeated = True

2555 else:

2556 names[required_label_name] = c

2557 elif anon_for_dupe_key:

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

2559 # label so that the original stays non-ambiguous

2560 if table_qualified:

2561 required_label_name = fallback_label_name = (

2562 c._dedupe_anon_tq_label_idx(dedupe_hash)

2563 )

2564 dedupe_hash += 1

2565 else:

2566 required_label_name = fallback_label_name = (

2567 c._dedupe_anon_label_idx(dedupe_hash)

2568 )

2569 dedupe_hash += 1

2570 repeated = True

2571 else:

2572 names[effective_name] = c

2573

2574 result_append(

2575 _ColumnsPlusNames(

2576 required_label_name,

2577 key_naming_convention(c),

2578 fallback_label_name,

2579 c,

2580 repeated,

2581 )

2582 )

2583

2584 return result

2585

2586

2587class HasCTE(roles.HasCTERole, SelectsRows):

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

2589

2590 _has_ctes_traverse_internals: _TraverseInternalsType = [

2591 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2592 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2593 ]

2594

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

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

2597

2598 name_cte_columns: bool = False

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

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

2601

2602 .. versionadded:: 2.0.42

2603

2604 """

2605

2606 @_generative

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

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

2609

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

2611 the parent statement such that they will each be unconditionally

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

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

2614

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

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

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

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

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

2620 statement.

2621

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

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

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

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

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

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

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

2629 larger statement.

2630

2631 E.g.::

2632

2633 from sqlalchemy import table, column, select

2634

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

2636

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

2638

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

2640

2641 Would render:

2642

2643 .. sourcecode:: sql

2644

2645 WITH anon_1 AS (

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

2647 )

2648 SELECT t.c1, t.c2

2649 FROM t

2650

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

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

2653 statement.

2654

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

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

2657

2658 from sqlalchemy import table, column

2659 from sqlalchemy.dialects.postgresql import insert

2660

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

2662

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

2664

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

2666 update_statement = insert_stmt.on_conflict_do_update(

2667 index_elements=[t.c.c1],

2668 set_={

2669 "c1": insert_stmt.excluded.c1,

2670 "c2": insert_stmt.excluded.c2,

2671 },

2672 ).add_cte(delete_statement_cte)

2673

2674 print(update_statement)

2675

2676 The above statement renders as:

2677

2678 .. sourcecode:: sql

2679

2680 WITH deletions AS (

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

2682 )

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

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

2685

2686 .. versionadded:: 1.4.21

2687

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

2689

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

2691

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

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

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

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

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

2697 this statement when this flag is given.

2698

2699 .. versionadded:: 2.0

2700

2701 .. seealso::

2702

2703 :paramref:`.HasCTE.cte.nesting`

2704

2705

2706 """ # noqa: E501

2707 opt = _CTEOpts(nest_here)

2708 for cte in ctes:

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

2710 self._independent_ctes += (cte,)

2711 self._independent_ctes_opts += (opt,)

2712 return self

2713

2714 def cte(

2715 self,

2716 name: Optional[str] = None,

2717 recursive: bool = False,

2718 nesting: bool = False,

2719 ) -> CTE:

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

2721 or Common Table Expression instance.

2722

2723 Common table expressions are a SQL standard whereby SELECT

2724 statements can draw upon secondary statements specified along

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

2726 Special semantics regarding UNION can also be employed to

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

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

2729

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

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

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

2733 CTE rows.

2734

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

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

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

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

2739

2740 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2742 method may be

2743 used to establish these.

2744

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

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

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

2748 compile time.

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

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

2751 conjunction with UNION ALL in order to derive rows

2752 from those already selected.

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

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

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

2756 :paramref:`.HasCTE.add_cte.nest_here`

2757 parameter may also be used to more carefully

2758 control the exact placement of a particular CTE.

2759

2760 .. versionadded:: 1.4.24

2761

2762 .. seealso::

2763

2764 :meth:`.HasCTE.add_cte`

2765

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

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

2768 as well as additional examples.

2769

2770 Example 1, non recursive::

2771

2772 from sqlalchemy import (

2773 Table,

2774 Column,

2775 String,

2776 Integer,

2777 MetaData,

2778 select,

2779 func,

2780 )

2781

2782 metadata = MetaData()

2783

2784 orders = Table(

2785 "orders",

2786 metadata,

2787 Column("region", String),

2788 Column("amount", Integer),

2789 Column("product", String),

2790 Column("quantity", Integer),

2791 )

2792

2793 regional_sales = (

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

2795 .group_by(orders.c.region)

2796 .cte("regional_sales")

2797 )

2798

2799

2800 top_regions = (

2801 select(regional_sales.c.region)

2802 .where(

2803 regional_sales.c.total_sales

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

2805 )

2806 .cte("top_regions")

2807 )

2808

2809 statement = (

2810 select(

2811 orders.c.region,

2812 orders.c.product,

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

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

2815 )

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

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

2818 )

2819

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

2821

2822 Example 2, WITH RECURSIVE::

2823

2824 from sqlalchemy import (

2825 Table,

2826 Column,

2827 String,

2828 Integer,

2829 MetaData,

2830 select,

2831 func,

2832 )

2833

2834 metadata = MetaData()

2835

2836 parts = Table(

2837 "parts",

2838 metadata,

2839 Column("part", String),

2840 Column("sub_part", String),

2841 Column("quantity", Integer),

2842 )

2843

2844 included_parts = (

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

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

2847 .cte(recursive=True)

2848 )

2849

2850

2851 incl_alias = included_parts.alias()

2852 parts_alias = parts.alias()

2853 included_parts = included_parts.union_all(

2854 select(

2855 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2857 )

2858

2859 statement = select(

2860 included_parts.c.sub_part,

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

2862 ).group_by(included_parts.c.sub_part)

2863

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

2865

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

2867

2868 from datetime import date

2869 from sqlalchemy import (

2870 MetaData,

2871 Table,

2872 Column,

2873 Integer,

2874 Date,

2875 select,

2876 literal,

2877 and_,

2878 exists,

2879 )

2880

2881 metadata = MetaData()

2882

2883 visitors = Table(

2884 "visitors",

2885 metadata,

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

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

2888 Column("count", Integer),

2889 )

2890

2891 # add 5 visitors for the product_id == 1

2892 product_id = 1

2893 day = date.today()

2894 count = 5

2895

2896 update_cte = (

2897 visitors.update()

2898 .where(

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

2900 )

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

2902 .returning(literal(1))

2903 .cte("update_cte")

2904 )

2905

2906 upsert = visitors.insert().from_select(

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

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

2909 ~exists(update_cte.select())

2910 ),

2911 )

2912

2913 connection.execute(upsert)

2914

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

2916

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

2918

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

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

2921 "value_a", nesting=True

2922 )

2923

2924 # Nesting CTEs takes ascendency locally

2925 # over the CTEs at a higher level

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

2927

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

2929

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

2931 shown with inline parameters below as:

2932

2933 .. sourcecode:: sql

2934

2935 WITH

2936 value_a AS

2937 (SELECT 'root' AS n),

2938 value_b AS

2939 (WITH value_a AS

2940 (SELECT 'nesting' AS n)

2941 SELECT value_a.n AS n FROM value_a)

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

2943 FROM value_a, value_b

2944

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

2946 as follows (SQLAlchemy 2.0 and above)::

2947

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

2949

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

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

2952

2953 # Nesting CTEs takes ascendency locally

2954 # over the CTEs at a higher level

2955 value_b = (

2956 select(value_a_nested.c.n)

2957 .add_cte(value_a_nested, nest_here=True)

2958 .cte("value_b")

2959 )

2960

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

2962

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

2964

2965 edge = Table(

2966 "edge",

2967 metadata,

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

2969 Column("left", Integer),

2970 Column("right", Integer),

2971 )

2972

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

2974

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

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

2977 )

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

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

2980 )

2981

2982 subgraph_cte = root_node.union(left_edge, right_edge)

2983

2984 subgraph = select(subgraph_cte)

2985

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

2987

2988 .. sourcecode:: sql

2989

2990 WITH RECURSIVE nodes(node) AS (

2991 SELECT 1 AS node

2992 UNION

2993 SELECT edge."left" AS "left"

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

2995 UNION

2996 SELECT edge."right" AS "right"

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

2998 )

2999 SELECT nodes.node FROM nodes

3000

3001 .. seealso::

3002

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

3004 :meth:`_expression.HasCTE.cte`.

3005

3006 """ # noqa: E501

3007 return CTE._construct(

3008 self, name=name, recursive=recursive, nesting=nesting

3009 )

3010

3011

3012class Subquery(AliasedReturnsRows[_KeyColCC_co]):

3013 """Represent a subquery of a SELECT.

3014

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

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

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

3018 :class:`_expression.SelectBase` subclass

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

3020 :class:`_expression.CompoundSelect`, and

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

3022 it represents the

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

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

3025

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

3027 :class:`_expression.Alias`

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

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

3030 :class:`_expression.Alias` always

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

3032 :class:`.Subquery`

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

3034

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

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

3037 statement.

3038

3039 """

3040

3041 __visit_name__ = "subquery"

3042

3043 _is_subquery = True

3044

3045 inherit_cache = True

3046

3047 element: SelectBase

3048

3049 @classmethod

3050 def _factory(

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

3052 ) -> Subquery:

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

3054

3055 return coercions.expect(

3056 roles.SelectStatementRole, selectable

3057 ).subquery(name=name)

3058

3059 @util.deprecated(

3060 "1.4",

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

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

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

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

3065 ":func:`_expression.select` "

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

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

3068 )

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

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

3071

3072

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

3074 """Represent a grouping of a FROM clause"""

3075

3076 _traverse_internals: _TraverseInternalsType = [

3077 ("element", InternalTraversal.dp_clauseelement)

3078 ]

3079

3080 element: FromClause[_KeyColCC_co]

3081

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

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

3084

3085 @util.ro_non_memoized_property

3086 def columns(self) -> _KeyColCC_co:

3087 return self.element.columns

3088

3089 @util.ro_non_memoized_property

3090 def c(self) -> _KeyColCC_co:

3091 return self.element.columns

3092

3093 @property

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

3095 return self.element.primary_key

3096

3097 @property

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

3099 return self.element.foreign_keys

3100

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

3102 return self.element.is_derived_from(fromclause)

3103

3104 def alias(

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

3106 ) -> NamedFromGrouping[_KeyColCC_co]:

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

3108

3109 def _anonymous_fromclause(

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

3111 ) -> FromGrouping:

3112 return FromGrouping(

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

3114 )

3115

3116 @util.ro_non_memoized_property

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

3118 return self.element._hide_froms

3119

3120 @util.ro_non_memoized_property

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

3122 return self.element._from_objects

3123

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

3125 return {"element": self.element}

3126

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

3128 self.element = state["element"]

3129

3130 if TYPE_CHECKING:

3131

3132 def self_group(

3133 self, against: Optional[OperatorType] = None

3134 ) -> Self: ...

3135

3136

3137class NamedFromGrouping(

3138 FromGrouping[_KeyColCC_co], NamedFromClause[_KeyColCC_co]

3139):

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

3141

3142 .. versionadded:: 2.0

3143

3144 """

3145

3146 inherit_cache = True

3147

3148 if TYPE_CHECKING:

3149

3150 def self_group(

3151 self, against: Optional[OperatorType] = None

3152 ) -> Self: ...

3153

3154

3155class TableClause(

3156 roles.DMLTableRole, Immutable, NamedFromClause[_ColClauseCC_co]

3157):

3158 """Represents a minimal "table" construct.

3159

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

3161 collection of columns, which are typically produced

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

3163

3164 from sqlalchemy import table, column

3165

3166 user = table(

3167 "user",

3168 column("id"),

3169 column("name"),

3170 column("description"),

3171 )

3172

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

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

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

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

3177

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

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

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

3181 It's useful

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

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

3184 is not on hand.

3185

3186 """

3187

3188 __visit_name__ = "table"

3189

3190 _traverse_internals: _TraverseInternalsType = [

3191 (

3192 "columns",

3193 InternalTraversal.dp_fromclause_canonical_column_collection,

3194 ),

3195 ("name", InternalTraversal.dp_string),

3196 ("schema", InternalTraversal.dp_string),

3197 ]

3198

3199 _is_table = True

3200

3201 fullname: str

3202

3203 implicit_returning = False

3204 """:class:`_expression.TableClause`

3205 doesn't support having a primary key or column

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

3207

3208 _columns: DedupeColumnCollection[ColumnClause[Any]]

3209

3210 @util.ro_memoized_property

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

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

3213 return None

3214

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

3216 super().__init__()

3217 self.name = name

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

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

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

3221 for c in columns:

3222 self.append_column(c)

3223

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

3225 if schema is not None:

3226 self.schema = schema

3227 if self.schema is not None:

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

3229 else:

3230 self.fullname = self.name

3231 if kw:

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

3233

3234 if TYPE_CHECKING:

3235

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

3237

3238 def __str__(self) -> str:

3239 if self.schema is not None:

3240 return self.schema + "." + self.name

3241 else:

3242 return self.name

3243

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

3245 pass

3246

3247 @util.ro_memoized_property

3248 def description(self) -> str:

3249 return self.name

3250

3251 def _insert_col_impl(

3252 self,

3253 c: ColumnClause[Any],

3254 *,

3255 index: Optional[int] = None,

3256 ) -> None:

3257 existing = c.table

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

3259 raise exc.ArgumentError(

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

3261 % (c.key, existing)

3262 )

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

3264 c.table = self

3265

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

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

3268 self._insert_col_impl(column)

3269

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

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

3272 a specific position.

3273

3274 .. versionadded:: 2.1

3275

3276 """

3277 self._insert_col_impl(column, index=index)

3278

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

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

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

3282 :class:`_expression.TableClause`.

3283

3284 E.g.::

3285

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

3287

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

3289

3290 """

3291

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

3293

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

3295 def update(self) -> Update:

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

3297 :class:`_expression.TableClause`.

3298

3299 E.g.::

3300

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

3302

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

3304

3305 """

3306 return util.preloaded.sql_dml.Update(

3307 self,

3308 )

3309

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

3311 def delete(self) -> Delete:

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

3313 :class:`_expression.TableClause`.

3314

3315 E.g.::

3316

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

3318

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

3320

3321 """

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

3323

3324 @util.ro_non_memoized_property

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

3326 return [self]

3327

3328

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

3330

3331

3332class ForUpdateArg(ClauseElement):

3333 _traverse_internals: _TraverseInternalsType = [

3334 ("of", InternalTraversal.dp_clauseelement_list),

3335 ("nowait", InternalTraversal.dp_boolean),

3336 ("read", InternalTraversal.dp_boolean),

3337 ("skip_locked", InternalTraversal.dp_boolean),

3338 ("key_share", InternalTraversal.dp_boolean),

3339 ]

3340

3341 of: Optional[Sequence[ClauseElement]]

3342 nowait: bool

3343 read: bool

3344 skip_locked: bool

3345

3346 @classmethod

3347 def _from_argument(

3348 cls, with_for_update: ForUpdateParameter

3349 ) -> Optional[ForUpdateArg]:

3350 if isinstance(with_for_update, ForUpdateArg):

3351 return with_for_update

3352 elif with_for_update in (None, False):

3353 return None

3354 elif with_for_update is True:

3355 return ForUpdateArg()

3356 else:

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

3358

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

3360 return (

3361 isinstance(other, ForUpdateArg)

3362 and other.nowait == self.nowait

3363 and other.read == self.read

3364 and other.skip_locked == self.skip_locked

3365 and other.key_share == self.key_share

3366 and other.of is self.of

3367 )

3368

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

3370 return not self.__eq__(other)

3371

3372 def __hash__(self) -> int:

3373 return id(self)

3374

3375 def __init__(

3376 self,

3377 *,

3378 nowait: bool = False,

3379 read: bool = False,

3380 of: Optional[_ForUpdateOfArgument] = None,

3381 skip_locked: bool = False,

3382 key_share: bool = False,

3383 ):

3384 """Represents arguments specified to

3385 :meth:`_expression.Select.for_update`.

3386

3387 """

3388

3389 self.nowait = nowait

3390 self.read = read

3391 self.skip_locked = skip_locked

3392 self.key_share = key_share

3393 if of is not None:

3394 self.of = [

3395 coercions.expect(roles.ColumnsClauseRole, elem)

3396 for elem in util.to_list(of)

3397 ]

3398 else:

3399 self.of = None

3400

3401

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

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

3404 in a statement.

3405

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

3407 :func:`_expression.values` function.

3408

3409 .. versionadded:: 1.4

3410

3411 """

3412

3413 __visit_name__ = "values"

3414

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

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

3417

3418 _unnamed: bool

3419 _traverse_internals: _TraverseInternalsType = [

3420 ("_column_args", InternalTraversal.dp_clauseelement_list),

3421 ("_data", InternalTraversal.dp_dml_multi_values),

3422 ("name", InternalTraversal.dp_string),

3423 ("literal_binds", InternalTraversal.dp_boolean),

3424 ] + HasCTE._has_ctes_traverse_internals

3425

3426 name_cte_columns = True

3427

3428 def __init__(

3429 self,

3430 *columns: _OnlyColumnArgument[Any],

3431 name: Optional[str] = None,

3432 literal_binds: bool = False,

3433 ):

3434 super().__init__()

3435 self._column_args = tuple(

3436 coercions.expect(roles.LabeledColumnExprRole, col)

3437 for col in columns

3438 )

3439

3440 if name is None:

3441 self._unnamed = True

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

3443 else:

3444 self._unnamed = False

3445 self.name = name

3446 self.literal_binds = literal_binds

3447 self.named_with_column = not self._unnamed

3448

3449 @property

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

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

3452

3453 @util.ro_non_memoized_property

3454 def _all_selected_columns(self) -> _SelectIterable:

3455 return self._column_args

3456

3457 @_generative

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

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

3460 construct that is a copy of this

3461 one with the given name.

3462

3463 This method is a VALUES-specific specialization of the

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

3465

3466 .. seealso::

3467

3468 :ref:`tutorial_using_aliases`

3469

3470 :func:`_expression.alias`

3471

3472 """

3473 non_none_name: str

3474

3475 if name is None:

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

3477 else:

3478 non_none_name = name

3479

3480 self.name = non_none_name

3481 self.named_with_column = True

3482 self._unnamed = False

3483 return self

3484

3485 @_generative

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

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

3488 so that

3489 it renders as LATERAL.

3490

3491 .. seealso::

3492

3493 :func:`_expression.lateral`

3494

3495 """

3496 non_none_name: str

3497

3498 if name is None:

3499 non_none_name = self.name

3500 else:

3501 non_none_name = name

3502

3503 self._is_lateral = True

3504 self.name = non_none_name

3505 self._unnamed = False

3506 return self

3507

3508 @_generative

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

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

3511 adding the given data to the data list.

3512

3513 E.g.::

3514

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

3516

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

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

3519 constructor.

3520

3521 """

3522

3523 self._data += (values,)

3524 return self

3525

3526 def scalar_values(self) -> ScalarValues:

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

3528 COLUMN element in a statement.

3529

3530 .. versionadded:: 2.0.0b4

3531

3532 """

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

3534

3535 def _populate_column_collection(

3536 self,

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

3538 primary_key: ColumnSet,

3539 foreign_keys: Set[KeyedColumnElement[Any]],

3540 ) -> None:

3541 for c in self._column_args:

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

3543 _, c = c._make_proxy(

3544 self, primary_key=primary_key, foreign_keys=foreign_keys

3545 )

3546 else:

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

3548 # no memoizations of other FROM clauses.

3549 # see test_values.py -> test_auto_proxy_select_direct_col

3550 c._reset_memoizations()

3551 columns.add(c)

3552 c.table = self

3553

3554 @util.ro_non_memoized_property

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

3556 return [self]

3557

3558

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

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

3561 COLUMN element in a statement.

3562

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

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

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

3566 an ``IN`` or ``NOT IN`` condition.

3567

3568 .. versionadded:: 2.0.0b4

3569

3570 """

3571

3572 __visit_name__ = "scalar_values"

3573

3574 _traverse_internals: _TraverseInternalsType = [

3575 ("_column_args", InternalTraversal.dp_clauseelement_list),

3576 ("_data", InternalTraversal.dp_dml_multi_values),

3577 ("literal_binds", InternalTraversal.dp_boolean),

3578 ]

3579

3580 def __init__(

3581 self,

3582 columns: Sequence[NamedColumn[Any]],

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

3584 literal_binds: bool,

3585 ):

3586 super().__init__()

3587 self._column_args = columns

3588 self._data = data

3589 self.literal_binds = literal_binds

3590

3591 @property

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

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

3594

3595 def __clause_element__(self) -> ScalarValues:

3596 return self

3597

3598 if TYPE_CHECKING:

3599

3600 def self_group(

3601 self, against: Optional[OperatorType] = None

3602 ) -> Self: ...

3603

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

3605

3606

3607class SelectBase(

3608 roles.SelectStatementRole,

3609 roles.DMLSelectRole,

3610 roles.CompoundElementRole,

3611 roles.InElementRole,

3612 HasCTE,

3613 SupportsCloneAnnotations,

3614 Selectable,

3615):

3616 """Base class for SELECT statements.

3617

3618

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

3620 :class:`_expression.CompoundSelect` and

3621 :class:`_expression.TextualSelect`.

3622

3623

3624 """

3625

3626 _is_select_base = True

3627 is_select = True

3628

3629 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3630

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

3632 self._reset_memoizations()

3633

3634 @util.ro_non_memoized_property

3635 def selected_columns(

3636 self,

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

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

3639 representing the columns that

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

3641

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

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

3644 within this collection cannot be directly nested inside another SELECT

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

3646 necessary parenthesization required by SQL.

3647

3648 .. note::

3649

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

3651 include expressions established in the columns clause using the

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

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

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

3655 construct.

3656

3657 .. seealso::

3658

3659 :attr:`_sql.Select.selected_columns`

3660

3661 .. versionadded:: 1.4

3662

3663 """

3664 raise NotImplementedError()

3665

3666 def _generate_fromclause_column_proxies(

3667 self,

3668 subquery: FromClause,

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

3670 primary_key: ColumnSet,

3671 foreign_keys: Set[KeyedColumnElement[Any]],

3672 *,

3673 proxy_compound_columns: Optional[

3674 Iterable[Sequence[ColumnElement[Any]]]

3675 ] = None,

3676 ) -> None:

3677 raise NotImplementedError()

3678

3679 @util.ro_non_memoized_property

3680 def _all_selected_columns(self) -> _SelectIterable:

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

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

3683 constructs.

3684

3685 .. versionadded:: 1.4.12

3686

3687 .. seealso::

3688

3689 :attr:`_sql.SelectBase.exported_columns`

3690

3691 """

3692 raise NotImplementedError()

3693

3694 @property

3695 def exported_columns(

3696 self,

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

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

3699 that represents the "exported"

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

3701 :class:`_sql.TextClause` constructs.

3702

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

3704 object are synonymous

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

3706

3707 .. versionadded:: 1.4

3708

3709 .. seealso::

3710

3711 :attr:`_expression.Select.exported_columns`

3712

3713 :attr:`_expression.Selectable.exported_columns`

3714

3715 :attr:`_expression.FromClause.exported_columns`

3716

3717

3718 """

3719 return self.selected_columns._as_readonly()

3720

3721 def get_label_style(self) -> SelectLabelStyle:

3722 """

3723 Retrieve the current label style.

3724

3725 Implemented by subclasses.

3726

3727 """

3728 raise NotImplementedError()

3729

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

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

3732

3733 Implemented by subclasses.

3734

3735 """

3736

3737 raise NotImplementedError()

3738

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

3740 raise NotImplementedError()

3741

3742 @util.deprecated(

3743 "1.4",

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

3745 "method is deprecated and will be "

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

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

3748 )

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

3750 return self.scalar_subquery()

3751

3752 def exists(self) -> Exists:

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

3754 which can be used as a column expression.

3755

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

3757

3758 .. seealso::

3759

3760 :func:`_sql.exists`

3761

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

3763

3764 .. versionadded:: 1.4

3765

3766 """

3767 return Exists(self)

3768

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

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

3771 used as a column expression.

3772

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

3774

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

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

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

3778 an enclosing SELECT.

3779

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

3781 subquery that can be produced using the

3782 :meth:`_expression.SelectBase.subquery`

3783 method.

3784

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

3786 :meth:`_expression.SelectBase.scalar_subquery`.

3787

3788 .. seealso::

3789

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

3791

3792 """

3793 if self._label_style is not LABEL_STYLE_NONE:

3794 self = self.set_label_style(LABEL_STYLE_NONE)

3795

3796 return ScalarSelect(self)

3797

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

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

3800 subquery with a label.

3801

3802 .. seealso::

3803

3804 :meth:`_expression.SelectBase.scalar_subquery`.

3805

3806 """

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

3808

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

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

3811

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

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

3814

3815 .. seealso::

3816

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

3818

3819 """

3820 return Lateral._factory(self, name)

3821

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

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

3824

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

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

3827 SELECT statement.

3828

3829 Given a SELECT statement such as::

3830

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

3832

3833 The above statement might look like:

3834

3835 .. sourcecode:: sql

3836

3837 SELECT table.id, table.name FROM table

3838

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

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

3841 a named sub-element::

3842

3843 subq = stmt.subquery()

3844 new_stmt = select(subq)

3845

3846 The above renders as:

3847

3848 .. sourcecode:: sql

3849

3850 SELECT anon_1.id, anon_1.name

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

3852

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

3854 is equivalent to calling

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

3856 method on a FROM object; however,

3857 as a :class:`_expression.SelectBase`

3858 object is not directly FROM object,

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

3860 method provides clearer semantics.

3861

3862 .. versionadded:: 1.4

3863

3864 """

3865

3866 return Subquery._construct(

3867 self._ensure_disambiguated_names(), name=name

3868 )

3869

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

3871 def into(

3872 self,

3873 target: str,

3874 *,

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

3876 schema: Optional[str] = None,

3877 temporary: bool = False,

3878 if_not_exists: bool = False,

3879 ) -> CreateTableAs:

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

3881

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

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

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

3885

3886 Supported on all included backends, the construct emits

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

3888 emits a ``SELECT..INTO`` statement.

3889

3890 e.g.::

3891

3892 from sqlalchemy import select

3893

3894 # Create a new table from a SELECT

3895 stmt = (

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

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

3898 .into("active_users")

3899 )

3900

3901 with engine.begin() as conn:

3902 conn.execute(stmt)

3903

3904 # With optional flags

3905 stmt = (

3906 select(users.c.id)

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

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

3909 )

3910

3911 .. versionadded:: 2.1

3912

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

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

3915

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

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

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

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

3920 is created.

3921

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

3923

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

3925

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

3927

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

3929

3930 .. seealso::

3931

3932 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3933

3934 :class:`_schema.CreateTableAs`

3935

3936 """

3937 sql_ddl = util.preloaded.sql_ddl

3938

3939 return sql_ddl.CreateTableAs(

3940 self,

3941 target,

3942 metadata=metadata,

3943 schema=schema,

3944 temporary=temporary,

3945 if_not_exists=if_not_exists,

3946 )

3947

3948 def _ensure_disambiguated_names(self) -> Self:

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

3950 disambiguated in some way, if possible.

3951

3952 """

3953

3954 raise NotImplementedError()

3955

3956 def alias(

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

3958 ) -> Subquery:

3959 """Return a named subquery against this

3960 :class:`_expression.SelectBase`.

3961

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

3963 :class:`_expression.FromClause`),

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

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

3966 :class:`_expression.FromClause`.

3967

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

3969 method is now

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

3971

3972 """

3973 return self.subquery(name=name)

3974

3975

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

3977

3978

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

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

3981

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

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

3984 compound selects.

3985

3986 """

3987

3988 __visit_name__ = "select_statement_grouping"

3989 _traverse_internals: _TraverseInternalsType = [

3990 ("element", InternalTraversal.dp_clauseelement)

3991 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3992

3993 _is_select_container = True

3994

3995 element: _SB

3996

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

3998 self.element = cast(

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

4000 )

4001

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

4003 new_element = self.element._ensure_disambiguated_names()

4004 if new_element is not self.element:

4005 return SelectStatementGrouping(new_element)

4006 else:

4007 return self

4008

4009 def get_label_style(self) -> SelectLabelStyle:

4010 return self.element.get_label_style()

4011

4012 def set_label_style(

4013 self, label_style: SelectLabelStyle

4014 ) -> SelectStatementGrouping[_SB]:

4015 return SelectStatementGrouping(

4016 self.element.set_label_style(label_style)

4017 )

4018

4019 @property

4020 def select_statement(self) -> _SB:

4021 return self.element

4022

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

4024 return self

4025

4026 if TYPE_CHECKING:

4027

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

4029

4030 # def _generate_columns_plus_names(

4031 # self, anon_for_dupe_key: bool

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

4033 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

4034

4035 def _generate_fromclause_column_proxies(

4036 self,

4037 subquery: FromClause,

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

4039 primary_key: ColumnSet,

4040 foreign_keys: Set[KeyedColumnElement[Any]],

4041 *,

4042 proxy_compound_columns: Optional[

4043 Iterable[Sequence[ColumnElement[Any]]]

4044 ] = None,

4045 ) -> None:

4046 self.element._generate_fromclause_column_proxies(

4047 subquery,

4048 columns,

4049 proxy_compound_columns=proxy_compound_columns,

4050 primary_key=primary_key,

4051 foreign_keys=foreign_keys,

4052 )

4053

4054 @util.ro_non_memoized_property

4055 def _all_selected_columns(self) -> _SelectIterable:

4056 return self.element._all_selected_columns

4057

4058 @util.ro_non_memoized_property

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

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

4061 representing the columns that

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

4063 :class:`_sql.TextClause` constructs.

4064

4065 .. versionadded:: 1.4

4066

4067 .. seealso::

4068

4069 :attr:`_sql.Select.selected_columns`

4070

4071 """

4072 return self.element.selected_columns

4073

4074 @util.ro_non_memoized_property

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

4076 return self.element._from_objects

4077

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

4079 return self.element._scalar_type()

4080

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

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

4083 raise NotImplementedError

4084

4085

4086class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4088 added.

4089

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

4091 :class:`_expression.CompoundSelect`

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

4093 rendering can be controlled. Compare to

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

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

4096 and is also a SELECT construct,

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

4098 only wrapped as a subquery.

4099

4100 """

4101

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

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

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

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

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

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

4108 _for_update_arg: Optional[ForUpdateArg] = None

4109

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

4111 self._label_style = _label_style

4112

4113 @_generative

4114 def with_for_update(

4115 self,

4116 *,

4117 nowait: bool = False,

4118 read: bool = False,

4119 of: Optional[_ForUpdateOfArgument] = None,

4120 skip_locked: bool = False,

4121 key_share: bool = False,

4122 ) -> Self:

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

4124 :class:`_expression.GenerativeSelect`.

4125

4126 E.g.::

4127

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

4129

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

4131 render a statement like:

4132

4133 .. sourcecode:: sql

4134

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

4136

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

4138 would produce:

4139

4140 .. sourcecode:: sql

4141

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

4143

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

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

4146 provided which allow for common database-specific

4147 variants.

4148

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

4150 Database and PostgreSQL dialects.

4151

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

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

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

4155

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

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

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

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

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

4161 table or as a column depending on backend.

4162

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

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

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

4166

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

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

4169 on the PostgreSQL dialect.

4170

4171 """

4172 self._for_update_arg = ForUpdateArg(

4173 nowait=nowait,

4174 read=read,

4175 of=of,

4176 skip_locked=skip_locked,

4177 key_share=key_share,

4178 )

4179 return self

4180

4181 def get_label_style(self) -> SelectLabelStyle:

4182 """

4183 Retrieve the current label style.

4184

4185 .. versionadded:: 1.4

4186

4187 """

4188 return self._label_style

4189

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

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

4192

4193 There are three "label styles" available,

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

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

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

4197 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4198

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

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

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

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

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

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

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

4206 that the impact of this labeling is minimal.

4207

4208 The rationale for disambiguation is mostly so that all column

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

4210 collection when a subquery is created.

4211

4212 .. versionadded:: 1.4 - the

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

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

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

4216

4217 .. seealso::

4218

4219 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4220

4221 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4222

4223 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4224

4225 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4226

4227 """

4228 if self._label_style is not style:

4229 self = self._generate()

4230 self._label_style = style

4231 return self

4232

4233 @property

4234 def _group_by_clause(self) -> ClauseList:

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

4236 return ClauseList._construct_raw(

4237 operators.comma_op, self._group_by_clauses

4238 )

4239

4240 @property

4241 def _order_by_clause(self) -> ClauseList:

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

4243 return ClauseList._construct_raw(

4244 operators.comma_op, self._order_by_clauses

4245 )

4246

4247 def _offset_or_limit_clause(

4248 self,

4249 element: _LimitOffsetType,

4250 name: Optional[str] = None,

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

4252 ) -> ColumnElement[Any]:

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

4254

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

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

4257

4258 """

4259 return coercions.expect(

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

4261 )

4262

4263 @overload

4264 def _offset_or_limit_clause_asint(

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

4266 ) -> NoReturn: ...

4267

4268 @overload

4269 def _offset_or_limit_clause_asint(

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

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

4272

4273 def _offset_or_limit_clause_asint(

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

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

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

4277 integer.

4278

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

4280 parameter. Otherwise, a compilation error is raised.

4281

4282 """

4283 if clause is None:

4284 return None

4285 try:

4286 value = clause._limit_offset_value

4287 except AttributeError as err:

4288 raise exc.CompileError(

4289 "This SELECT structure does not use a simple "

4290 "integer value for %s" % attrname

4291 ) from err

4292 else:

4293 return util.asint(value)

4294

4295 @property

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

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

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

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

4300 isn't currently set to an integer.

4301

4302 """

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

4304

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

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

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

4308 """

4309 return isinstance(clause, _OffsetLimitParam)

4310

4311 @property

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

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

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

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

4316 offset isn't currently set to an integer.

4317

4318 """

4319 return self._offset_or_limit_clause_asint(

4320 self._offset_clause, "offset"

4321 )

4322

4323 @property

4324 def _has_row_limiting_clause(self) -> bool:

4325 return (

4326 self._limit_clause is not None

4327 or self._offset_clause is not None

4328 or self._fetch_clause is not None

4329 )

4330

4331 @_generative

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

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

4334 applied.

4335

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

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

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

4339 functionality.

4340

4341 .. note::

4342

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

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

4345

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

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

4348

4349 .. seealso::

4350

4351 :meth:`_sql.GenerativeSelect.fetch`

4352

4353 :meth:`_sql.GenerativeSelect.offset`

4354

4355 """

4356

4357 self._fetch_clause = self._fetch_clause_options = None

4358 self._limit_clause = self._offset_or_limit_clause(limit)

4359 return self

4360

4361 @_generative

4362 def fetch(

4363 self,

4364 count: _LimitOffsetType,

4365 with_ties: bool = False,

4366 percent: bool = False,

4367 **dialect_kw: Any,

4368 ) -> Self:

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

4370 applied.

4371

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

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

4374 select. This functionality is is currently implemented for Oracle

4375 Database, PostgreSQL, MSSQL.

4376

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

4378

4379 .. note::

4380

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

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

4383

4384 .. versionadded:: 1.4

4385

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

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

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

4389 Pass ``None`` to reset it.

4390

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

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

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

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

4395

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

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

4398

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

4400 may be accepted by dialects.

4401

4402 .. versionadded:: 2.0.41

4403

4404 .. seealso::

4405

4406 :meth:`_sql.GenerativeSelect.limit`

4407

4408 :meth:`_sql.GenerativeSelect.offset`

4409

4410 """

4411 self._validate_dialect_kwargs(dialect_kw)

4412 self._limit_clause = None

4413 if count is None:

4414 self._fetch_clause = self._fetch_clause_options = None

4415 else:

4416 self._fetch_clause = self._offset_or_limit_clause(count)

4417 self._fetch_clause_options = {

4418 "with_ties": with_ties,

4419 "percent": percent,

4420 }

4421 return self

4422

4423 @_generative

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

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

4426 applied.

4427

4428

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

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

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

4432 functionality.

4433

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

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

4436

4437 .. seealso::

4438

4439 :meth:`_sql.GenerativeSelect.limit`

4440

4441 :meth:`_sql.GenerativeSelect.fetch`

4442

4443 """

4444

4445 self._offset_clause = self._offset_or_limit_clause(offset)

4446 return self

4447

4448 @_generative

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

4450 def slice(

4451 self,

4452 start: int,

4453 stop: int,

4454 ) -> Self:

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

4456

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

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

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

4460 query.

4461

4462 For example, ::

4463

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

4465

4466 renders as

4467

4468 .. sourcecode:: sql

4469

4470 SELECT users.id AS users_id,

4471 users.name AS users_name

4472 FROM users ORDER BY users.id

4473 LIMIT ? OFFSET ?

4474 (2, 1)

4475

4476 .. note::

4477

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

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

4480

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

4482 method generalized from the ORM.

4483

4484 .. seealso::

4485

4486 :meth:`_sql.GenerativeSelect.limit`

4487

4488 :meth:`_sql.GenerativeSelect.offset`

4489

4490 :meth:`_sql.GenerativeSelect.fetch`

4491

4492 """

4493 sql_util = util.preloaded.sql_util

4494 self._fetch_clause = self._fetch_clause_options = None

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

4496 self._limit_clause, self._offset_clause, start, stop

4497 )

4498 return self

4499

4500 @_generative

4501 def order_by(

4502 self,

4503 __first: Union[

4504 Literal[None, _NoArg.NO_ARG],

4505 _ColumnExpressionOrStrLabelArgument[Any],

4506 roles.OrderByRole,

4507 ] = _NoArg.NO_ARG,

4508 /,

4509 *clauses: Union[

4510 _ColumnExpressionOrStrLabelArgument[Any], roles.OrderByRole

4511 ],

4512 ) -> Self:

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

4514 criteria applied.

4515

4516 e.g.::

4517

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

4519

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

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

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

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

4524

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

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

4527

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

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

4530

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

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

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

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

4535 label matching in ORDER BY and GROUP BY expressions.

4536

4537 .. seealso::

4538

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

4540

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

4542

4543 """

4544

4545 if not clauses and __first is None:

4546 self._order_by_clauses = ()

4547 elif __first is not _NoArg.NO_ARG:

4548 self._order_by_clauses += tuple(

4549 coercions.expect(

4550 roles.OrderByRole, clause, apply_propagate_attrs=self

4551 )

4552 for clause in (__first,) + clauses

4553 )

4554 return self

4555

4556 @_generative

4557 def group_by(

4558 self,

4559 __first: Union[

4560 Literal[None, _NoArg.NO_ARG],

4561 _ColumnExpressionOrStrLabelArgument[Any],

4562 ] = _NoArg.NO_ARG,

4563 /,

4564 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4565 ) -> Self:

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

4567 criterion applied.

4568

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

4570

4571 e.g.::

4572

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

4574

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

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

4577

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

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

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

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

4582 label matching in ORDER BY and GROUP BY expressions.

4583

4584 .. seealso::

4585

4586 :ref:`tutorial_group_by_w_aggregates` - in the

4587 :ref:`unified_tutorial`

4588

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

4590

4591 """ # noqa: E501

4592

4593 if not clauses and __first is None:

4594 self._group_by_clauses = ()

4595 elif __first is not _NoArg.NO_ARG:

4596 self._group_by_clauses += tuple(

4597 coercions.expect(

4598 roles.GroupByRole, clause, apply_propagate_attrs=self

4599 )

4600 for clause in (__first,) + clauses

4601 )

4602 return self

4603

4604

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

4606class CompoundSelectState(CompileState):

4607 @util.memoized_property

4608 def _label_resolve_dict(

4609 self,

4610 ) -> Tuple[

4611 Dict[str, ColumnElement[Any]],

4612 Dict[str, ColumnElement[Any]],

4613 Dict[str, ColumnElement[Any]],

4614 ]:

4615 # TODO: this is hacky and slow

4616 hacky_subquery = self.statement.subquery()

4617 hacky_subquery.named_with_column = False

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

4619 return d, d, d

4620

4621

4622class _CompoundSelectKeyword(Enum):

4623 UNION = "UNION"

4624 UNION_ALL = "UNION ALL"

4625 EXCEPT = "EXCEPT"

4626 EXCEPT_ALL = "EXCEPT ALL"

4627 INTERSECT = "INTERSECT"

4628 INTERSECT_ALL = "INTERSECT ALL"

4629

4630

4631class CompoundSelect(

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

4633):

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

4635 SELECT-based set operations.

4636

4637

4638 .. seealso::

4639

4640 :func:`_expression.union`

4641

4642 :func:`_expression.union_all`

4643

4644 :func:`_expression.intersect`

4645

4646 :func:`_expression.intersect_all`

4647

4648 :func:`_expression.except`

4649

4650 :func:`_expression.except_all`

4651

4652 """

4653

4654 __visit_name__ = "compound_select"

4655

4656 _traverse_internals: _TraverseInternalsType = (

4657 [

4658 ("selects", InternalTraversal.dp_clauseelement_list),

4659 ("_limit_clause", InternalTraversal.dp_clauseelement),

4660 ("_offset_clause", InternalTraversal.dp_clauseelement),

4661 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4662 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4663 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4664 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4665 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4666 ("keyword", InternalTraversal.dp_string),

4667 ]

4668 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4669 + HasCTE._has_ctes_traverse_internals

4670 + DialectKWArgs._dialect_kwargs_traverse_internals

4671 + ExecutableStatement._executable_traverse_internals

4672 )

4673

4674 selects: List[SelectBase]

4675

4676 _is_from_container = True

4677 _auto_correlate = False

4678

4679 def __init__(

4680 self,

4681 keyword: _CompoundSelectKeyword,

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

4683 ):

4684 self.keyword = keyword

4685 self.selects = [

4686 coercions.expect(

4687 roles.CompoundElementRole, s, apply_propagate_attrs=self

4688 ).self_group(against=self)

4689 for s in selects

4690 ]

4691

4692 GenerativeSelect.__init__(self)

4693

4694 @classmethod

4695 def _create_union(

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

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

4698 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4699

4700 @classmethod

4701 def _create_union_all(

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

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

4704 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4705

4706 @classmethod

4707 def _create_except(

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

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

4710 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4711

4712 @classmethod

4713 def _create_except_all(

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

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

4716 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4717

4718 @classmethod

4719 def _create_intersect(

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

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

4722 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4723

4724 @classmethod

4725 def _create_intersect_all(

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

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

4728 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4729

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

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

4732

4733 def self_group(

4734 self, against: Optional[OperatorType] = None

4735 ) -> GroupedElement:

4736 return SelectStatementGrouping(self)

4737

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

4739 for s in self.selects:

4740 if s.is_derived_from(fromclause):

4741 return True

4742 return False

4743

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

4745 if self._label_style is not style:

4746 self = self._generate()

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

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

4749

4750 return self

4751

4752 def _ensure_disambiguated_names(self) -> Self:

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

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

4755 self = self._generate()

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

4757

4758 return self

4759

4760 def _generate_fromclause_column_proxies(

4761 self,

4762 subquery: FromClause,

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

4764 primary_key: ColumnSet,

4765 foreign_keys: Set[KeyedColumnElement[Any]],

4766 *,

4767 proxy_compound_columns: Optional[

4768 Iterable[Sequence[ColumnElement[Any]]]

4769 ] = None,

4770 ) -> None:

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

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

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

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

4775 # generate below.

4776 select_0 = self.selects[0]

4777

4778 if self._label_style is not LABEL_STYLE_DEFAULT:

4779 select_0 = select_0.set_label_style(self._label_style)

4780

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

4782 # derived columns place a 'weight' annotation corresponding

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

4784 # that the corresponding_column() operation can resolve

4785 # conflicts

4786 extra_col_iterator = zip(

4787 *[

4788 [

4789 c._annotate(dd)

4790 for c in stmt._all_selected_columns

4791 if is_column_element(c)

4792 ]

4793 for dd, stmt in [

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

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

4796 ]

4797 ]

4798 )

4799

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

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

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

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

4804 # compound

4805 select_0._generate_fromclause_column_proxies(

4806 subquery,

4807 columns,

4808 proxy_compound_columns=extra_col_iterator,

4809 primary_key=primary_key,

4810 foreign_keys=foreign_keys,

4811 )

4812

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

4814 super()._refresh_for_new_column(column)

4815 for select in self.selects:

4816 select._refresh_for_new_column(column)

4817

4818 @util.ro_non_memoized_property

4819 def _all_selected_columns(self) -> _SelectIterable:

4820 return self.selects[0]._all_selected_columns

4821

4822 @util.ro_non_memoized_property

4823 def selected_columns(

4824 self,

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

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

4827 representing the columns that

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

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

4830

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

4832 :attr:`_expression.CompoundSelect.selected_columns`

4833 attribute returns the selected

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

4835 statements within the set operation.

4836

4837 .. seealso::

4838

4839 :attr:`_sql.Select.selected_columns`

4840

4841 .. versionadded:: 1.4

4842

4843 """

4844 return self.selects[0].selected_columns

4845

4846

4847# backwards compat

4848for elem in _CompoundSelectKeyword:

4849 setattr(CompoundSelect, elem.name, elem)

4850

4851

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

4853class SelectState(util.MemoizedSlots, CompileState):

4854 __slots__ = (

4855 "from_clauses",

4856 "froms",

4857 "columns_plus_names",

4858 "_label_resolve_dict",

4859 )

4860

4861 if TYPE_CHECKING:

4862 default_select_compile_options: CacheableOptions

4863 else:

4864

4865 class default_select_compile_options(CacheableOptions):

4866 _cache_key_traversal = []

4867

4868 if TYPE_CHECKING:

4869

4870 @classmethod

4871 def get_plugin_class(

4872 cls, statement: Executable

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

4874

4875 def __init__(

4876 self,

4877 statement: Select[Unpack[TupleAny]],

4878 compiler: SQLCompiler,

4879 **kw: Any,

4880 ):

4881 self.statement = statement

4882 self.from_clauses = statement._from_obj

4883

4884 for memoized_entities in statement._memoized_select_entities:

4885 self._setup_joins(

4886 memoized_entities._setup_joins, memoized_entities._raw_columns

4887 )

4888

4889 if statement._setup_joins:

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

4891

4892 self.froms = self._get_froms(statement)

4893

4894 self.columns_plus_names = statement._generate_columns_plus_names(True)

4895

4896 @classmethod

4897 def _plugin_not_implemented(cls) -> NoReturn:

4898 raise NotImplementedError(

4899 "The default SELECT construct without plugins does not "

4900 "implement this method."

4901 )

4902

4903 @classmethod

4904 def get_column_descriptions(

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

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

4907 return [

4908 {

4909 "name": name,

4910 "type": element.type,

4911 "expr": element,

4912 }

4913 for _, name, _, element, _ in (

4914 statement._generate_columns_plus_names(False)

4915 )

4916 ]

4917

4918 @classmethod

4919 def from_statement(

4920 cls,

4921 statement: Select[Unpack[TupleAny]],

4922 from_statement: roles.ReturnsRowsRole,

4923 ) -> ExecutableReturnsRows:

4924 cls._plugin_not_implemented()

4925

4926 @classmethod

4927 def get_columns_clause_froms(

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

4929 ) -> List[FromClause]:

4930 return cls._normalize_froms(

4931 itertools.chain.from_iterable(

4932 element._from_objects for element in statement._raw_columns

4933 )

4934 )

4935

4936 @classmethod

4937 def _column_naming_convention(

4938 cls, label_style: SelectLabelStyle

4939 ) -> _LabelConventionCallable:

4940 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4941

4942 dedupe = label_style is not LABEL_STYLE_NONE

4943

4944 pa = prefix_anon_map()

4945 names = set()

4946

4947 def go(

4948 c: Union[ColumnElement[Any], AbstractTextClause],

4949 col_name: Optional[str] = None,

4950 ) -> Optional[str]:

4951 if is_text_clause(c):

4952 return None

4953 elif TYPE_CHECKING:

4954 assert is_column_element(c)

4955

4956 if not dedupe:

4957 name = c._proxy_key

4958 if name is None:

4959 name = "_no_label"

4960 return name

4961

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

4963

4964 if name is None:

4965 name = "_no_label"

4966 if name in names:

4967 return c._anon_label(name) % pa

4968 else:

4969 names.add(name)

4970 return name

4971

4972 elif name in names:

4973 return (

4974 c._anon_tq_key_label % pa

4975 if table_qualified

4976 else c._anon_key_label % pa

4977 )

4978 else:

4979 names.add(name)

4980 return name

4981

4982 return go

4983

4984 def _get_froms(

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

4986 ) -> List[FromClause]:

4987 ambiguous_table_name_map: _AmbiguousTableNameMap

4988 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4989

4990 return self._normalize_froms(

4991 itertools.chain(

4992 self.from_clauses,

4993 itertools.chain.from_iterable(

4994 [

4995 element._from_objects

4996 for element in statement._raw_columns

4997 ]

4998 ),

4999 itertools.chain.from_iterable(

5000 [

5001 element._from_objects

5002 for element in statement._where_criteria

5003 ]

5004 ),

5005 ),

5006 check_statement=statement,

5007 ambiguous_table_name_map=ambiguous_table_name_map,

5008 )

5009

5010 @classmethod

5011 def _normalize_froms(

5012 cls,

5013 iterable_of_froms: Iterable[FromClause],

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

5015 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

5016 ) -> List[FromClause]:

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

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

5019

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

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

5022 etc.

5023

5024 """

5025 seen: Set[FromClause] = set()

5026 froms: List[FromClause] = []

5027

5028 for item in iterable_of_froms:

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

5030 raise exc.InvalidRequestError(

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

5032 )

5033

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

5035 froms.append(item)

5036 seen.update(item._cloned_set)

5037

5038 if froms:

5039 toremove = set(

5040 itertools.chain.from_iterable(

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

5042 )

5043 )

5044 if toremove:

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

5046 # using a list to maintain ordering

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

5048

5049 if ambiguous_table_name_map is not None:

5050 ambiguous_table_name_map.update(

5051 (

5052 fr.name,

5053 _anonymous_label.safe_construct(

5054 hash(fr.name), fr.name

5055 ),

5056 )

5057 for item in froms

5058 for fr in item._from_objects

5059 if is_table(fr)

5060 and fr.schema

5061 and fr.name not in ambiguous_table_name_map

5062 )

5063

5064 return froms

5065

5066 def _get_display_froms(

5067 self,

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

5069 implicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5070 ) -> List[FromClause]:

5071 """Return the full list of 'from' clauses to be displayed.

5072

5073 Takes into account a set of existing froms which may be

5074 rendered in the FROM clause of enclosing selects; this Select

5075 may want to leave those absent if it is automatically

5076 correlating.

5077

5078 """

5079

5080 froms = self.froms

5081

5082 if self.statement._correlate:

5083 to_correlate = self.statement._correlate

5084 if to_correlate:

5085 froms = [

5086 f

5087 for f in froms

5088 if f

5089 not in _cloned_intersection(

5090 _cloned_intersection(

5091 froms, explicit_correlate_froms or ()

5092 ),

5093 to_correlate,

5094 )

5095 ]

5096

5097 if self.statement._correlate_except is not None:

5098 froms = [

5099 f

5100 for f in froms

5101 if f

5102 not in _cloned_difference(

5103 _cloned_intersection(

5104 froms, explicit_correlate_froms or ()

5105 ),

5106 self.statement._correlate_except,

5107 )

5108 ]

5109

5110 if (

5111 self.statement._auto_correlate

5112 and implicit_correlate_froms

5113 and len(froms) > 1

5114 ):

5115 froms = [

5116 f

5117 for f in froms

5118 if f

5119 not in _cloned_intersection(froms, implicit_correlate_froms)

5120 ]

5121

5122 if not len(froms):

5123 raise exc.InvalidRequestError(

5124 "Select statement '%r"

5125 "' returned no FROM clauses "

5126 "due to auto-correlation; "

5127 "specify correlate(<tables>) "

5128 "to control correlation "

5129 "manually." % self.statement

5130 )

5131

5132 return froms

5133

5134 def _memoized_attr__label_resolve_dict(

5135 self,

5136 ) -> Tuple[

5137 Dict[str, ColumnElement[Any]],

5138 Dict[str, ColumnElement[Any]],

5139 Dict[str, ColumnElement[Any]],

5140 ]:

5141 with_cols: Dict[str, ColumnElement[Any]] = {

5142 c._tq_label or c.key: c

5143 for c in self.statement._all_selected_columns

5144 if c._allow_label_resolve

5145 }

5146 only_froms: Dict[str, ColumnElement[Any]] = {

5147 c.key: c # type: ignore

5148 for c in _select_iterables(self.froms)

5149 if c._allow_label_resolve

5150 }

5151 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

5152 for key, value in only_froms.items():

5153 with_cols.setdefault(key, value)

5154

5155 return with_cols, only_froms, only_cols

5156

5157 @classmethod

5158 def _get_filter_by_entities(

5159 cls, statement: Select[Unpack[TupleAny]]

5160 ) -> Collection[

5161 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5162 ]:

5163 """Return all entities to search for filter_by() attributes.

5164

5165 This includes:

5166

5167 * All joined entities from _setup_joins

5168 * Memoized entities from previous operations (e.g.,

5169 before with_only_columns)

5170 * Explicit FROM objects from _from_obj

5171 * Entities inferred from _raw_columns

5172

5173 .. versionadded:: 2.1

5174

5175 """

5176 entities: set[

5177 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5178 ]

5179

5180 entities = set(

5181 join_element[0] for join_element in statement._setup_joins

5182 )

5183

5184 for memoized in statement._memoized_select_entities:

5185 entities.update(

5186 join_element[0] for join_element in memoized._setup_joins

5187 )

5188

5189 entities.update(statement._from_obj)

5190

5191 for col in statement._raw_columns:

5192 entities.update(col._from_objects)

5193

5194 return entities

5195

5196 @classmethod

5197 def all_selected_columns(

5198 cls, statement: Select[Unpack[TupleAny]]

5199 ) -> _SelectIterable:

5200 return [c for c in _select_iterables(statement._raw_columns)]

5201

5202 def _setup_joins(

5203 self,

5204 args: Tuple[_SetupJoinsElement, ...],

5205 raw_columns: List[_ColumnsClauseElement],

5206 ) -> None:

5207 for right, onclause, left, flags in args:

5208 if TYPE_CHECKING:

5209 if onclause is not None:

5210 assert isinstance(onclause, ColumnElement)

5211

5212 explicit_left = left

5213 isouter = flags["isouter"]

5214 full = flags["full"]

5215

5216 if left is None:

5217 (

5218 left,

5219 replace_from_obj_index,

5220 ) = self._join_determine_implicit_left_side(

5221 raw_columns, left, right, onclause

5222 )

5223 else:

5224 replace_from_obj_index = self._join_place_explicit_left_side(

5225 left

5226 )

5227

5228 # these assertions can be made here, as if the right/onclause

5229 # contained ORM elements, the select() statement would have been

5230 # upgraded to an ORM select, and this method would not be called;

5231 # orm.context.ORMSelectCompileState._join() would be

5232 # used instead.

5233 if TYPE_CHECKING:

5234 assert isinstance(right, FromClause)

5235 if onclause is not None:

5236 assert isinstance(onclause, ColumnElement)

5237

5238 if replace_from_obj_index is not None:

5239 # splice into an existing element in the

5240 # self._from_obj list

5241 left_clause = self.from_clauses[replace_from_obj_index]

5242

5243 if explicit_left is not None and onclause is None:

5244 onclause = Join._join_condition(explicit_left, right)

5245

5246 self.from_clauses = (

5247 self.from_clauses[:replace_from_obj_index]

5248 + (

5249 Join(

5250 left_clause,

5251 right,

5252 onclause,

5253 isouter=isouter,

5254 full=full,

5255 ),

5256 )

5257 + self.from_clauses[replace_from_obj_index + 1 :]

5258 )

5259 else:

5260 assert left is not None

5261 self.from_clauses = self.from_clauses + (

5262 Join(left, right, onclause, isouter=isouter, full=full),

5263 )

5264

5265 @util.preload_module("sqlalchemy.sql.util")

5266 def _join_determine_implicit_left_side(

5267 self,

5268 raw_columns: List[_ColumnsClauseElement],

5269 left: Optional[FromClause],

5270 right: _JoinTargetElement,

5271 onclause: Optional[ColumnElement[Any]],

5272 ) -> Tuple[Optional[FromClause], Optional[int]]:

5273 """When join conditions don't express the left side explicitly,

5274 determine if an existing FROM or entity in this query

5275 can serve as the left hand side.

5276

5277 """

5278

5279 sql_util = util.preloaded.sql_util

5280

5281 replace_from_obj_index: Optional[int] = None

5282

5283 from_clauses = self.from_clauses

5284

5285 if from_clauses:

5286 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5287 from_clauses, right, onclause

5288 )

5289

5290 if len(indexes) == 1:

5291 replace_from_obj_index = indexes[0]

5292 left = from_clauses[replace_from_obj_index]

5293 else:

5294 potential = {}

5295 statement = self.statement

5296

5297 for from_clause in itertools.chain(

5298 itertools.chain.from_iterable(

5299 [element._from_objects for element in raw_columns]

5300 ),

5301 itertools.chain.from_iterable(

5302 [

5303 element._from_objects

5304 for element in statement._where_criteria

5305 ]

5306 ),

5307 ):

5308 potential[from_clause] = ()

5309

5310 all_clauses = list(potential.keys())

5311 indexes = sql_util.find_left_clause_to_join_from(

5312 all_clauses, right, onclause

5313 )

5314

5315 if len(indexes) == 1:

5316 left = all_clauses[indexes[0]]

5317

5318 if len(indexes) > 1:

5319 raise exc.InvalidRequestError(

5320 "Can't determine which FROM clause to join "

5321 "from, there are multiple FROMS which can "

5322 "join to this entity. Please use the .select_from() "

5323 "method to establish an explicit left side, as well as "

5324 "providing an explicit ON clause if not present already to "

5325 "help resolve the ambiguity."

5326 )

5327 elif not indexes:

5328 raise exc.InvalidRequestError(

5329 "Don't know how to join to %r. "

5330 "Please use the .select_from() "

5331 "method to establish an explicit left side, as well as "

5332 "providing an explicit ON clause if not present already to "

5333 "help resolve the ambiguity." % (right,)

5334 )

5335 return left, replace_from_obj_index

5336

5337 @util.preload_module("sqlalchemy.sql.util")

5338 def _join_place_explicit_left_side(

5339 self, left: FromClause

5340 ) -> Optional[int]:

5341 replace_from_obj_index: Optional[int] = None

5342

5343 sql_util = util.preloaded.sql_util

5344

5345 from_clauses = list(self.statement._iterate_from_elements())

5346

5347 if from_clauses:

5348 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5349 self.from_clauses, left

5350 )

5351 else:

5352 indexes = []

5353

5354 if len(indexes) > 1:

5355 raise exc.InvalidRequestError(

5356 "Can't identify which entity in which to assign the "

5357 "left side of this join. Please use a more specific "

5358 "ON clause."

5359 )

5360

5361 # have an index, means the left side is already present in

5362 # an existing FROM in the self._from_obj tuple

5363 if indexes:

5364 replace_from_obj_index = indexes[0]

5365

5366 # no index, means we need to add a new element to the

5367 # self._from_obj tuple

5368

5369 return replace_from_obj_index

5370

5371

5372class _SelectFromElements:

5373 __slots__ = ()

5374

5375 _raw_columns: List[_ColumnsClauseElement]

5376 _where_criteria: Tuple[ColumnElement[Any], ...]

5377 _from_obj: Tuple[FromClause, ...]

5378

5379 def _iterate_from_elements(self) -> Iterator[FromClause]:

5380 # note this does not include elements

5381 # in _setup_joins

5382

5383 seen = set()

5384 for element in self._raw_columns:

5385 for fr in element._from_objects:

5386 if fr in seen:

5387 continue

5388 seen.add(fr)

5389 yield fr

5390 for element in self._where_criteria:

5391 for fr in element._from_objects:

5392 if fr in seen:

5393 continue

5394 seen.add(fr)

5395 yield fr

5396 for element in self._from_obj:

5397 if element in seen:

5398 continue

5399 seen.add(element)

5400 yield element

5401

5402

5403class _MemoizedSelectEntities(

5404 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5405):

5406 """represents partial state from a Select object, for the case

5407 where Select.columns() has redefined the set of columns/entities the

5408 statement will be SELECTing from. This object represents

5409 the entities from the SELECT before that transformation was applied,

5410 so that transformations that were made in terms of the SELECT at that

5411 time, such as join() as well as options(), can access the correct context.

5412

5413 In previous SQLAlchemy versions, this wasn't needed because these

5414 constructs calculated everything up front, like when you called join()

5415 or options(), it did everything to figure out how that would translate

5416 into specific SQL constructs that would be ready to send directly to the

5417 SQL compiler when needed. But as of

5418 1.4, all of that stuff is done in the compilation phase, during the

5419 "compile state" portion of the process, so that the work can all be

5420 cached. So it needs to be able to resolve joins/options2 based on what

5421 the list of entities was when those methods were called.

5422

5423

5424 """

5425

5426 __visit_name__ = "memoized_select_entities"

5427

5428 _traverse_internals: _TraverseInternalsType = [

5429 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5430 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5431 ("_with_options", InternalTraversal.dp_executable_options),

5432 ]

5433

5434 _is_clone_of: Optional[ClauseElement]

5435 _raw_columns: List[_ColumnsClauseElement]

5436 _setup_joins: Tuple[_SetupJoinsElement, ...]

5437 _with_options: Tuple[ExecutableOption, ...]

5438

5439 _annotations = util.EMPTY_DICT

5440

5441 def _clone(self, **kw: Any) -> Self:

5442 c = self.__class__.__new__(self.__class__)

5443 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5444

5445 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5446 return c

5447

5448 @classmethod

5449 def _generate_for_statement(

5450 cls, select_stmt: Select[Unpack[TupleAny]]

5451 ) -> None:

5452 if select_stmt._setup_joins or select_stmt._with_options:

5453 self = _MemoizedSelectEntities()

5454 self._raw_columns = select_stmt._raw_columns

5455 self._setup_joins = select_stmt._setup_joins

5456 self._with_options = select_stmt._with_options

5457

5458 select_stmt._memoized_select_entities += (self,)

5459 select_stmt._raw_columns = []

5460 select_stmt._setup_joins = select_stmt._with_options = ()

5461

5462

5463class Select(

5464 HasPrefixes,

5465 HasSuffixes,

5466 HasHints,

5467 HasCompileState,

5468 HasSyntaxExtensions[

5469 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5470 ],

5471 _SelectFromElements,

5472 GenerativeSelect,

5473 TypedReturnsRows[Unpack[_Ts]],

5474):

5475 """Represents a ``SELECT`` statement.

5476

5477 The :class:`_sql.Select` object is normally constructed using the

5478 :func:`_sql.select` function. See that function for details.

5479

5480 Available extension points:

5481

5482 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5483 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5484 keyword (if any) and the list of columns.

5485 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5486 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5487

5488 .. seealso::

5489

5490 :func:`_sql.select`

5491

5492 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5493

5494 """

5495

5496 __visit_name__ = "select"

5497

5498 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5499 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5500

5501 _raw_columns: List[_ColumnsClauseElement]

5502

5503 _distinct: bool = False

5504 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5505 _correlate: Tuple[FromClause, ...] = ()

5506 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5507 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5508 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5509 _from_obj: Tuple[FromClause, ...] = ()

5510

5511 _position_map = util.immutabledict(

5512 {

5513 "post_select": "_post_select_clause",

5514 "pre_columns": "_pre_columns_clause",

5515 "post_criteria": "_post_criteria_clause",

5516 "post_body": "_post_body_clause",

5517 }

5518 )

5519

5520 _post_select_clause: Optional[ClauseElement] = None

5521 """extension point for a ClauseElement that will be compiled directly

5522 after the SELECT keyword.

5523

5524 .. versionadded:: 2.1

5525

5526 """

5527

5528 _pre_columns_clause: Optional[ClauseElement] = None

5529 """extension point for a ClauseElement that will be compiled directly

5530 before the "columns" clause; after DISTINCT (if present).

5531

5532 .. versionadded:: 2.1

5533

5534 """

5535

5536 _post_criteria_clause: Optional[ClauseElement] = None

5537 """extension point for a ClauseElement that will be compiled directly

5538 after "criteria", following the HAVING clause but before ORDER BY.

5539

5540 .. versionadded:: 2.1

5541

5542 """

5543

5544 _post_body_clause: Optional[ClauseElement] = None

5545 """extension point for a ClauseElement that will be compiled directly

5546 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5547 of the SELECT.

5548

5549 .. versionadded:: 2.1

5550

5551 """

5552

5553 _auto_correlate = True

5554 _is_select_statement = True

5555 _compile_options: CacheableOptions = (

5556 SelectState.default_select_compile_options

5557 )

5558

5559 _traverse_internals: _TraverseInternalsType = (

5560 [

5561 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5562 (

5563 "_memoized_select_entities",

5564 InternalTraversal.dp_memoized_select_entities,

5565 ),

5566 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5567 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5568 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5569 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5570 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5571 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5572 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5573 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5574 ("_limit_clause", InternalTraversal.dp_clauseelement),

5575 ("_offset_clause", InternalTraversal.dp_clauseelement),

5576 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5577 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5578 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5579 ("_distinct", InternalTraversal.dp_boolean),

5580 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5581 ("_label_style", InternalTraversal.dp_plain_obj),

5582 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5583 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5584 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5585 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5586 ]

5587 + HasCTE._has_ctes_traverse_internals

5588 + HasPrefixes._has_prefixes_traverse_internals

5589 + HasSuffixes._has_suffixes_traverse_internals

5590 + HasHints._has_hints_traverse_internals

5591 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5592 + ExecutableStatement._executable_traverse_internals

5593 + DialectKWArgs._dialect_kwargs_traverse_internals

5594 )

5595

5596 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5597 ("_compile_options", InternalTraversal.dp_has_cache_key)

5598 ]

5599

5600 _compile_state_factory: Type[SelectState]

5601

5602 @classmethod

5603 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5604 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5605

5606 Used internally to build up :class:`.Select` constructs with

5607 pre-established state.

5608

5609 """

5610

5611 stmt = Select.__new__(Select)

5612 stmt.__dict__.update(kw)

5613 return stmt

5614

5615 def __init__(

5616 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5617 ):

5618 r"""Construct a new :class:`_expression.Select`.

5619

5620 The public constructor for :class:`_expression.Select` is the

5621 :func:`_sql.select` function.

5622

5623 """

5624 self._raw_columns = [

5625 coercions.expect(

5626 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5627 )

5628 for ent in entities

5629 ]

5630 GenerativeSelect.__init__(self)

5631

5632 def _apply_syntax_extension_to_self(

5633 self, extension: SyntaxExtension

5634 ) -> None:

5635 extension.apply_to_select(self)

5636

5637 def _scalar_type(self) -> TypeEngine[Any]:

5638 if not self._raw_columns:

5639 return NULLTYPE

5640 elem = self._raw_columns[0]

5641 cols = list(elem._select_iterable)

5642 return cols[0].type

5643

5644 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5645 """A synonym for the :meth:`_sql.Select.where` method."""

5646

5647 return self.where(*criteria)

5648

5649 if TYPE_CHECKING:

5650

5651 @overload

5652 def scalar_subquery(

5653 self: Select[_MAYBE_ENTITY],

5654 ) -> ScalarSelect[Any]: ...

5655

5656 @overload

5657 def scalar_subquery(

5658 self: Select[_NOT_ENTITY],

5659 ) -> ScalarSelect[_NOT_ENTITY]: ...

5660

5661 @overload

5662 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5663

5664 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5665

5666 def filter_by(self, **kwargs: Any) -> Self:

5667 r"""Apply the given filtering criterion as a WHERE clause

5668 to this select, using keyword expressions.

5669

5670 E.g.::

5671

5672 stmt = select(User).filter_by(name="some name")

5673

5674 Multiple criteria may be specified as comma separated; the effect

5675 is that they will be joined together using the :func:`.and_`

5676 function::

5677

5678 stmt = select(User).filter_by(name="some name", id=5)

5679

5680 The keyword expressions are extracted by searching across **all

5681 entities present in the FROM clause** of the statement. If a

5682 keyword name is present in more than one entity,

5683 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5684 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5685 explicit column references::

5686

5687 # both User and Address have an 'id' attribute

5688 stmt = select(User).join(Address).filter_by(id=5)

5689 # raises AmbiguousColumnError

5690

5691 # use filter() with explicit qualification instead

5692 stmt = select(User).join(Address).filter(Address.id == 5)

5693

5694 .. versionchanged:: 2.1

5695

5696 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5697 entities rather than only searching the last joined entity or first

5698 FROM entity. This allows the method to locate attributes

5699 unambiguously across multiple joined tables. The new

5700 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5701 is present in more than one entity.

5702

5703 See :ref:`change_8601` for migration notes.

5704

5705 .. seealso::

5706

5707 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

5708

5709 :meth:`_sql.Select.filter` - filter on SQL expressions.

5710

5711 :meth:`_sql.Select.where` - filter on SQL expressions.

5712

5713 """

5714 # Get all entities via plugin system

5715 all_entities = SelectState.get_plugin_class(

5716 self

5717 )._get_filter_by_entities(self)

5718

5719 clauses = [

5720 _entity_namespace_key_search_all(all_entities, key) == value

5721 for key, value in kwargs.items()

5722 ]

5723 return self.filter(*clauses)

5724

5725 @property

5726 def column_descriptions(self) -> Any:

5727 """Return a :term:`plugin-enabled` 'column descriptions' structure

5728 referring to the columns which are SELECTed by this statement.

5729

5730 This attribute is generally useful when using the ORM, as an

5731 extended structure which includes information about mapped

5732 entities is returned. The section :ref:`queryguide_inspection`

5733 contains more background.

5734

5735 For a Core-only statement, the structure returned by this accessor

5736 is derived from the same objects that are returned by the

5737 :attr:`.Select.selected_columns` accessor, formatted as a list of

5738 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5739 which indicate the column expressions to be selected::

5740

5741 >>> stmt = select(user_table)

5742 >>> stmt.column_descriptions

5743 [

5744 {

5745 'name': 'id',

5746 'type': Integer(),

5747 'expr': Column('id', Integer(), ...)},

5748 {

5749 'name': 'name',

5750 'type': String(length=30),

5751 'expr': Column('name', String(length=30), ...)}

5752 ]

5753

5754 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5755 attribute returns a structure for a Core-only set of entities,

5756 not just ORM-only entities.

5757

5758 .. seealso::

5759

5760 :attr:`.UpdateBase.entity_description` - entity information for

5761 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5762

5763 :ref:`queryguide_inspection` - ORM background

5764

5765 """

5766 meth = SelectState.get_plugin_class(self).get_column_descriptions

5767 return meth(self)

5768

5769 def from_statement(

5770 self, statement: roles.ReturnsRowsRole

5771 ) -> ExecutableReturnsRows:

5772 """Apply the columns which this :class:`.Select` would select

5773 onto another statement.

5774

5775 This operation is :term:`plugin-specific` and will raise a not

5776 supported exception if this :class:`_sql.Select` does not select from

5777 plugin-enabled entities.

5778

5779

5780 The statement is typically either a :func:`_expression.text` or

5781 :func:`_expression.select` construct, and should return the set of

5782 columns appropriate to the entities represented by this

5783 :class:`.Select`.

5784

5785 .. seealso::

5786

5787 :ref:`orm_queryguide_selecting_text` - usage examples in the

5788 ORM Querying Guide

5789

5790 """

5791 meth = SelectState.get_plugin_class(self).from_statement

5792 return meth(self, statement)

5793

5794 @_generative

5795 def join(

5796 self,

5797 target: _JoinTargetArgument,

5798 onclause: Optional[_OnClauseArgument] = None,

5799 *,

5800 isouter: bool = False,

5801 full: bool = False,

5802 ) -> Self:

5803 r"""Create a SQL JOIN against this :class:`_expression.Select`

5804 object's criterion

5805 and apply generatively, returning the newly resulting

5806 :class:`_expression.Select`.

5807

5808 E.g.::

5809

5810 stmt = select(user_table).join(

5811 address_table, user_table.c.id == address_table.c.user_id

5812 )

5813

5814 The above statement generates SQL similar to:

5815

5816 .. sourcecode:: sql

5817

5818 SELECT user.id, user.name

5819 FROM user

5820 JOIN address ON user.id = address.user_id

5821

5822 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5823 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5824 source that is within the FROM clause of the existing SELECT,

5825 and a given target :class:`_sql.FromClause`, and then adds

5826 this :class:`_sql.Join` to the FROM clause of the newly generated

5827 SELECT statement. This is completely reworked from the behavior

5828 in 1.3, which would instead create a subquery of the entire

5829 :class:`_expression.Select` and then join that subquery to the

5830 target.

5831

5832 This is a **backwards incompatible change** as the previous behavior

5833 was mostly useless, producing an unnamed subquery rejected by

5834 most databases in any case. The new behavior is modeled after

5835 that of the very successful :meth:`_orm.Query.join` method in the

5836 ORM, in order to support the functionality of :class:`_orm.Query`

5837 being available by using a :class:`_sql.Select` object with an

5838 :class:`_orm.Session`.

5839

5840 See the notes for this change at :ref:`change_select_join`.

5841

5842

5843 :param target: target table to join towards

5844

5845 :param onclause: ON clause of the join. If omitted, an ON clause

5846 is generated automatically based on the :class:`_schema.ForeignKey`

5847 linkages between the two tables, if one can be unambiguously

5848 determined, otherwise an error is raised.

5849

5850 :param isouter: if True, generate LEFT OUTER join. Same as

5851 :meth:`_expression.Select.outerjoin`.

5852

5853 :param full: if True, generate FULL OUTER join.

5854

5855 .. seealso::

5856

5857 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5858

5859 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5860

5861 :meth:`_expression.Select.join_from`

5862

5863 :meth:`_expression.Select.outerjoin`

5864

5865 """ # noqa: E501

5866 join_target = coercions.expect(

5867 roles.JoinTargetRole, target, apply_propagate_attrs=self

5868 )

5869 if onclause is not None:

5870 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5871 else:

5872 onclause_element = None

5873

5874 self._setup_joins += (

5875 (

5876 join_target,

5877 onclause_element,

5878 None,

5879 {"isouter": isouter, "full": full},

5880 ),

5881 )

5882 return self

5883

5884 def outerjoin_from(

5885 self,

5886 from_: _FromClauseArgument,

5887 target: _JoinTargetArgument,

5888 onclause: Optional[_OnClauseArgument] = None,

5889 *,

5890 full: bool = False,

5891 ) -> Self:

5892 r"""Create a SQL LEFT OUTER JOIN against this

5893 :class:`_expression.Select` object's criterion and apply generatively,

5894 returning the newly resulting :class:`_expression.Select`.

5895

5896 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5897

5898 """

5899 return self.join_from(

5900 from_, target, onclause=onclause, isouter=True, full=full

5901 )

5902

5903 @_generative

5904 def join_from(

5905 self,

5906 from_: _FromClauseArgument,

5907 target: _JoinTargetArgument,

5908 onclause: Optional[_OnClauseArgument] = None,

5909 *,

5910 isouter: bool = False,

5911 full: bool = False,

5912 ) -> Self:

5913 r"""Create a SQL JOIN against this :class:`_expression.Select`

5914 object's criterion

5915 and apply generatively, returning the newly resulting

5916 :class:`_expression.Select`.

5917

5918 E.g.::

5919

5920 stmt = select(user_table, address_table).join_from(

5921 user_table, address_table, user_table.c.id == address_table.c.user_id

5922 )

5923

5924 The above statement generates SQL similar to:

5925

5926 .. sourcecode:: sql

5927

5928 SELECT user.id, user.name, address.id, address.email, address.user_id

5929 FROM user JOIN address ON user.id = address.user_id

5930

5931 .. versionadded:: 1.4

5932

5933 :param from\_: the left side of the join, will be rendered in the

5934 FROM clause and is roughly equivalent to using the

5935 :meth:`.Select.select_from` method.

5936

5937 :param target: target table to join towards

5938

5939 :param onclause: ON clause of the join.

5940

5941 :param isouter: if True, generate LEFT OUTER join. Same as

5942 :meth:`_expression.Select.outerjoin`.

5943

5944 :param full: if True, generate FULL OUTER join.

5945

5946 .. seealso::

5947

5948 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5949

5950 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5951

5952 :meth:`_expression.Select.join`

5953

5954 """ # noqa: E501

5955

5956 # note the order of parsing from vs. target is important here, as we

5957 # are also deriving the source of the plugin (i.e. the subject mapper

5958 # in an ORM query) which should favor the "from_" over the "target"

5959

5960 from_ = coercions.expect(

5961 roles.FromClauseRole, from_, apply_propagate_attrs=self

5962 )

5963 join_target = coercions.expect(

5964 roles.JoinTargetRole, target, apply_propagate_attrs=self

5965 )

5966 if onclause is not None:

5967 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5968 else:

5969 onclause_element = None

5970

5971 self._setup_joins += (

5972 (

5973 join_target,

5974 onclause_element,

5975 from_,

5976 {"isouter": isouter, "full": full},

5977 ),

5978 )

5979 return self

5980

5981 def outerjoin(

5982 self,

5983 target: _JoinTargetArgument,

5984 onclause: Optional[_OnClauseArgument] = None,

5985 *,

5986 full: bool = False,

5987 ) -> Self:

5988 """Create a left outer join.

5989

5990 Parameters are the same as that of :meth:`_expression.Select.join`.

5991

5992 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5993 creates a :class:`_sql.Join` object between a

5994 :class:`_sql.FromClause` source that is within the FROM clause of

5995 the existing SELECT, and a given target :class:`_sql.FromClause`,

5996 and then adds this :class:`_sql.Join` to the FROM clause of the

5997 newly generated SELECT statement. This is completely reworked

5998 from the behavior in 1.3, which would instead create a subquery of

5999 the entire

6000 :class:`_expression.Select` and then join that subquery to the

6001 target.

6002

6003 This is a **backwards incompatible change** as the previous behavior

6004 was mostly useless, producing an unnamed subquery rejected by

6005 most databases in any case. The new behavior is modeled after

6006 that of the very successful :meth:`_orm.Query.join` method in the

6007 ORM, in order to support the functionality of :class:`_orm.Query`

6008 being available by using a :class:`_sql.Select` object with an

6009 :class:`_orm.Session`.

6010

6011 See the notes for this change at :ref:`change_select_join`.

6012

6013 .. seealso::

6014

6015 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

6016

6017 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

6018

6019 :meth:`_expression.Select.join`

6020

6021 """

6022 return self.join(target, onclause=onclause, isouter=True, full=full)

6023

6024 def get_final_froms(self) -> Sequence[FromClause]:

6025 """Compute the final displayed list of :class:`_expression.FromClause`

6026 elements.

6027

6028 This method will run through the full computation required to

6029 determine what FROM elements will be displayed in the resulting

6030 SELECT statement, including shadowing individual tables with

6031 JOIN objects, as well as full computation for ORM use cases including

6032 eager loading clauses.

6033

6034 For ORM use, this accessor returns the **post compilation**

6035 list of FROM objects; this collection will include elements such as

6036 eagerly loaded tables and joins. The objects will **not** be

6037 ORM enabled and not work as a replacement for the

6038 :meth:`_sql.Select.select_froms` collection; additionally, the

6039 method is not well performing for an ORM enabled statement as it

6040 will incur the full ORM construction process.

6041

6042 To retrieve the FROM list that's implied by the "columns" collection

6043 passed to the :class:`_sql.Select` originally, use the

6044 :attr:`_sql.Select.columns_clause_froms` accessor.

6045

6046 To select from an alternative set of columns while maintaining the

6047 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6048 pass the

6049 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6050 parameter.

6051

6052 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6053 method replaces the previous :attr:`_sql.Select.froms` accessor,

6054 which is deprecated.

6055

6056 .. seealso::

6057

6058 :attr:`_sql.Select.columns_clause_froms`

6059

6060 """

6061 compiler = self._default_compiler()

6062

6063 return self._compile_state_factory(self, compiler)._get_display_froms()

6064

6065 @property

6066 @util.deprecated(

6067 "1.4.23",

6068 "The :attr:`_expression.Select.froms` attribute is moved to "

6069 "the :meth:`_expression.Select.get_final_froms` method.",

6070 )

6071 def froms(self) -> Sequence[FromClause]:

6072 """Return the displayed list of :class:`_expression.FromClause`

6073 elements.

6074

6075

6076 """

6077 return self.get_final_froms()

6078

6079 @property

6080 def columns_clause_froms(self) -> List[FromClause]:

6081 """Return the set of :class:`_expression.FromClause` objects implied

6082 by the columns clause of this SELECT statement.

6083

6084 .. versionadded:: 1.4.23

6085

6086 .. seealso::

6087

6088 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6089 statement into account

6090

6091 :meth:`_sql.Select.with_only_columns` - makes use of this

6092 collection to set up a new FROM list

6093

6094 """

6095

6096 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6097 self

6098 )

6099

6100 @property

6101 def inner_columns(self) -> _SelectIterable:

6102 """An iterator of all :class:`_expression.ColumnElement`

6103 expressions which would

6104 be rendered into the columns clause of the resulting SELECT statement.

6105

6106 This method is legacy as of 1.4 and is superseded by the

6107 :attr:`_expression.Select.exported_columns` collection.

6108

6109 """

6110

6111 return iter(self._all_selected_columns)

6112

6113 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

6114 if fromclause is not None and self in fromclause._cloned_set:

6115 return True

6116

6117 for f in self._iterate_from_elements():

6118 if f.is_derived_from(fromclause):

6119 return True

6120 return False

6121

6122 def _copy_internals(

6123 self, clone: _CloneCallableType = _clone, **kw: Any

6124 ) -> None:

6125 # Select() object has been cloned and probably adapted by the

6126 # given clone function. Apply the cloning function to internal

6127 # objects

6128

6129 # 1. keep a dictionary of the froms we've cloned, and what

6130 # they've become. This allows us to ensure the same cloned from

6131 # is used when other items such as columns are "cloned"

6132

6133 all_the_froms = set(

6134 itertools.chain(

6135 _from_objects(*self._raw_columns),

6136 _from_objects(*self._where_criteria),

6137 _from_objects(*[elem[0] for elem in self._setup_joins]),

6138 )

6139 )

6140

6141 # do a clone for the froms we've gathered. what is important here

6142 # is if any of the things we are selecting from, like tables,

6143 # were converted into Join objects. if so, these need to be

6144 # added to _from_obj explicitly, because otherwise they won't be

6145 # part of the new state, as they don't associate themselves with

6146 # their columns.

6147 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6148

6149 # 2. copy FROM collections, adding in joins that we've created.

6150 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6151 add_froms = (

6152 {f for f in new_froms.values() if isinstance(f, Join)}

6153 .difference(all_the_froms)

6154 .difference(existing_from_obj)

6155 )

6156

6157 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6158

6159 # 3. clone everything else, making sure we use columns

6160 # corresponding to the froms we just made.

6161 def replace(

6162 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6163 **kw: Any,

6164 ) -> Optional[KeyedColumnElement[Any]]:

6165 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6166 newelem = new_froms[obj.table].corresponding_column(obj)

6167 return newelem

6168 return None

6169

6170 kw["replace"] = replace

6171

6172 # copy everything else. for table-ish things like correlate,

6173 # correlate_except, setup_joins, these clone normally. For

6174 # column-expression oriented things like raw_columns, where_criteria,

6175 # order by, we get this from the new froms.

6176 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6177

6178 self._reset_memoizations()

6179

6180 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6181 return itertools.chain(

6182 super().get_children(

6183 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6184 **kw,

6185 ),

6186 self._iterate_from_elements(),

6187 )

6188

6189 @_generative

6190 def add_columns(

6191 self, *entities: _ColumnsClauseArgument[Any]

6192 ) -> Select[Unpack[TupleAny]]:

6193 r"""Return a new :func:`_expression.select` construct with

6194 the given entities appended to its columns clause.

6195

6196 E.g.::

6197

6198 my_select = my_select.add_columns(table.c.new_column)

6199

6200 The original expressions in the columns clause remain in place.

6201 To replace the original expressions with new ones, see the method

6202 :meth:`_expression.Select.with_only_columns`.

6203

6204 :param \*entities: column, table, or other entity expressions to be

6205 added to the columns clause

6206

6207 .. seealso::

6208

6209 :meth:`_expression.Select.with_only_columns` - replaces existing

6210 expressions rather than appending.

6211

6212 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6213 example

6214

6215 """

6216 self._reset_memoizations()

6217

6218 self._raw_columns = self._raw_columns + [

6219 coercions.expect(

6220 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6221 )

6222 for column in entities

6223 ]

6224 return self

6225

6226 def _set_entities(

6227 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6228 ) -> None:

6229 self._raw_columns = [

6230 coercions.expect(

6231 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6232 )

6233 for ent in util.to_list(entities)

6234 ]

6235

6236 @util.deprecated(

6237 "1.4",

6238 "The :meth:`_expression.Select.column` method is deprecated and will "

6239 "be removed in a future release. Please use "

6240 ":meth:`_expression.Select.add_columns`",

6241 )

6242 def column(

6243 self, column: _ColumnsClauseArgument[Any]

6244 ) -> Select[Unpack[TupleAny]]:

6245 """Return a new :func:`_expression.select` construct with

6246 the given column expression added to its columns clause.

6247

6248 E.g.::

6249

6250 my_select = my_select.column(table.c.new_column)

6251

6252 See the documentation for

6253 :meth:`_expression.Select.with_only_columns`

6254 for guidelines on adding /replacing the columns of a

6255 :class:`_expression.Select` object.

6256

6257 """

6258 return self.add_columns(column)

6259

6260 @util.preload_module("sqlalchemy.sql.util")

6261 def reduce_columns(

6262 self, only_synonyms: bool = True

6263 ) -> Select[Unpack[TupleAny]]:

6264 """Return a new :func:`_expression.select` construct with redundantly

6265 named, equivalently-valued columns removed from the columns clause.

6266

6267 "Redundant" here means two columns where one refers to the

6268 other either based on foreign key, or via a simple equality

6269 comparison in the WHERE clause of the statement. The primary purpose

6270 of this method is to automatically construct a select statement

6271 with all uniquely-named columns, without the need to use

6272 table-qualified labels as

6273 :meth:`_expression.Select.set_label_style`

6274 does.

6275

6276 When columns are omitted based on foreign key, the referred-to

6277 column is the one that's kept. When columns are omitted based on

6278 WHERE equivalence, the first column in the columns clause is the

6279 one that's kept.

6280

6281 :param only_synonyms: when True, limit the removal of columns

6282 to those which have the same name as the equivalent. Otherwise,

6283 all columns that are equivalent to another are removed.

6284

6285 """

6286 woc: Select[Unpack[TupleAny]]

6287 woc = self.with_only_columns(

6288 *util.preloaded.sql_util.reduce_columns(

6289 self._all_selected_columns,

6290 only_synonyms=only_synonyms,

6291 *(self._where_criteria + self._from_obj),

6292 )

6293 )

6294 return woc

6295

6296 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6297

6298 # code within this block is **programmatically,

6299 # statically generated** by tools/generate_tuple_map_overloads.py

6300

6301 @overload

6302 def with_only_columns(

6303 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6304 ) -> Select[_T0]: ...

6305

6306 @overload

6307 def with_only_columns(

6308 self,

6309 __ent0: _TCCA[_T0],

6310 __ent1: _TCCA[_T1],

6311 /,

6312 *,

6313 maintain_column_froms: bool = ...,

6314 ) -> Select[_T0, _T1]: ...

6315

6316 @overload

6317 def with_only_columns(

6318 self,

6319 __ent0: _TCCA[_T0],

6320 __ent1: _TCCA[_T1],

6321 __ent2: _TCCA[_T2],

6322 /,

6323 *,

6324 maintain_column_froms: bool = ...,

6325 ) -> Select[_T0, _T1, _T2]: ...

6326

6327 @overload

6328 def with_only_columns(

6329 self,

6330 __ent0: _TCCA[_T0],

6331 __ent1: _TCCA[_T1],

6332 __ent2: _TCCA[_T2],

6333 __ent3: _TCCA[_T3],

6334 /,

6335 *,

6336 maintain_column_froms: bool = ...,

6337 ) -> Select[_T0, _T1, _T2, _T3]: ...

6338

6339 @overload

6340 def with_only_columns(

6341 self,

6342 __ent0: _TCCA[_T0],

6343 __ent1: _TCCA[_T1],

6344 __ent2: _TCCA[_T2],

6345 __ent3: _TCCA[_T3],

6346 __ent4: _TCCA[_T4],

6347 /,

6348 *,

6349 maintain_column_froms: bool = ...,

6350 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6351

6352 @overload

6353 def with_only_columns(

6354 self,

6355 __ent0: _TCCA[_T0],

6356 __ent1: _TCCA[_T1],

6357 __ent2: _TCCA[_T2],

6358 __ent3: _TCCA[_T3],

6359 __ent4: _TCCA[_T4],

6360 __ent5: _TCCA[_T5],

6361 /,

6362 *,

6363 maintain_column_froms: bool = ...,

6364 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6365

6366 @overload

6367 def with_only_columns(

6368 self,

6369 __ent0: _TCCA[_T0],

6370 __ent1: _TCCA[_T1],

6371 __ent2: _TCCA[_T2],

6372 __ent3: _TCCA[_T3],

6373 __ent4: _TCCA[_T4],

6374 __ent5: _TCCA[_T5],

6375 __ent6: _TCCA[_T6],

6376 /,

6377 *,

6378 maintain_column_froms: bool = ...,

6379 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6380

6381 @overload

6382 def with_only_columns(

6383 self,

6384 __ent0: _TCCA[_T0],

6385 __ent1: _TCCA[_T1],

6386 __ent2: _TCCA[_T2],

6387 __ent3: _TCCA[_T3],

6388 __ent4: _TCCA[_T4],

6389 __ent5: _TCCA[_T5],

6390 __ent6: _TCCA[_T6],

6391 __ent7: _TCCA[_T7],

6392 /,

6393 *entities: _ColumnsClauseArgument[Any],

6394 maintain_column_froms: bool = ...,

6395 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6396

6397 # END OVERLOADED FUNCTIONS self.with_only_columns

6398

6399 @overload

6400 def with_only_columns(

6401 self,

6402 *entities: _ColumnsClauseArgument[Any],

6403 maintain_column_froms: bool = False,

6404 **__kw: Any,

6405 ) -> Select[Unpack[TupleAny]]: ...

6406

6407 @_generative

6408 def with_only_columns(

6409 self,

6410 *entities: _ColumnsClauseArgument[Any],

6411 maintain_column_froms: bool = False,

6412 **__kw: Any,

6413 ) -> Select[Unpack[TupleAny]]:

6414 r"""Return a new :func:`_expression.select` construct with its columns

6415 clause replaced with the given entities.

6416

6417 By default, this method is exactly equivalent to as if the original

6418 :func:`_expression.select` had been called with the given entities.

6419 E.g. a statement::

6420

6421 s = select(table1.c.a, table1.c.b)

6422 s = s.with_only_columns(table1.c.b)

6423

6424 should be exactly equivalent to::

6425

6426 s = select(table1.c.b)

6427

6428 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6429 will also dynamically alter the FROM clause of the

6430 statement if it is not explicitly stated.

6431 To maintain the existing set of FROMs including those implied by the

6432 current columns clause, add the

6433 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6434 parameter::

6435

6436 s = select(table1.c.a, table2.c.b)

6437 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6438

6439 The above parameter performs a transfer of the effective FROMs

6440 in the columns collection to the :meth:`_sql.Select.select_from`

6441 method, as though the following were invoked::

6442

6443 s = select(table1.c.a, table2.c.b)

6444 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6445

6446 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6447 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6448 collection and performs an operation equivalent to the following::

6449

6450 s = select(table1.c.a, table2.c.b)

6451 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6452

6453 :param \*entities: column expressions to be used.

6454

6455 :param maintain_column_froms: boolean parameter that will ensure the

6456 FROM list implied from the current columns clause will be transferred

6457 to the :meth:`_sql.Select.select_from` method first.

6458

6459 .. versionadded:: 1.4.23

6460

6461 """ # noqa: E501

6462

6463 if __kw:

6464 raise _no_kw()

6465

6466 # memoizations should be cleared here as of

6467 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6468 # is the case for now.

6469 self._assert_no_memoizations()

6470

6471 if maintain_column_froms:

6472 self.select_from.non_generative( # type: ignore

6473 self, *self.columns_clause_froms

6474 )

6475

6476 # then memoize the FROMs etc.

6477 _MemoizedSelectEntities._generate_for_statement(self)

6478

6479 self._raw_columns = [

6480 coercions.expect(roles.ColumnsClauseRole, c)

6481 for c in coercions._expression_collection_was_a_list(

6482 "entities", "Select.with_only_columns", entities

6483 )

6484 ]

6485 return self

6486

6487 @property

6488 def whereclause(self) -> Optional[ColumnElement[Any]]:

6489 """Return the completed WHERE clause for this

6490 :class:`_expression.Select` statement.

6491

6492 This assembles the current collection of WHERE criteria

6493 into a single :class:`_expression.BooleanClauseList` construct.

6494

6495

6496 .. versionadded:: 1.4

6497

6498 """

6499

6500 return BooleanClauseList._construct_for_whereclause(

6501 self._where_criteria

6502 )

6503

6504 _whereclause = whereclause

6505

6506 @_generative

6507 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6508 """Return a new :func:`_expression.select` construct with

6509 the given expression added to

6510 its WHERE clause, joined to the existing clause via AND, if any.

6511

6512 """

6513

6514 assert isinstance(self._where_criteria, tuple)

6515

6516 for criterion in whereclause:

6517 where_criteria: ColumnElement[Any] = coercions.expect(

6518 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6519 )

6520 self._where_criteria += (where_criteria,)

6521 return self

6522

6523 @_generative

6524 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6525 """Return a new :func:`_expression.select` construct with

6526 the given expression added to

6527 its HAVING clause, joined to the existing clause via AND, if any.

6528

6529 """

6530

6531 for criterion in having:

6532 having_criteria = coercions.expect(

6533 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6534 )

6535 self._having_criteria += (having_criteria,)

6536 return self

6537

6538 @_generative

6539 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6540 r"""Return a new :func:`_expression.select` construct which

6541 will apply DISTINCT to the SELECT statement overall.

6542

6543 E.g.::

6544

6545 from sqlalchemy import select

6546

6547 stmt = select(users_table.c.id, users_table.c.name).distinct()

6548

6549 The above would produce an statement resembling:

6550

6551 .. sourcecode:: sql

6552

6553 SELECT DISTINCT user.id, user.name FROM user

6554

6555 The method also historically accepted an ``*expr`` parameter which

6556 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6557 This is now replaced using the :func:`_postgresql.distinct_on`

6558 extension::

6559

6560 from sqlalchemy import select

6561 from sqlalchemy.dialects.postgresql import distinct_on

6562

6563 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6564

6565 Using this parameter on other backends which don't support this

6566 syntax will raise an error.

6567

6568 :param \*expr: optional column expressions. When present,

6569 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6570 construct. A deprecation warning and/or :class:`_exc.CompileError`

6571 will be raised on other backends.

6572

6573 .. deprecated:: 2.1 Passing expressions to

6574 :meth:`_sql.Select.distinct` is deprecated, use

6575 :func:`_postgresql.distinct_on` instead.

6576

6577 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6578 and will raise :class:`_exc.CompileError` in a future version.

6579

6580 .. seealso::

6581

6582 :func:`_postgresql.distinct_on`

6583

6584 :meth:`.ext`

6585 """

6586 self._distinct = True

6587 if expr:

6588 warn_deprecated(

6589 "Passing expression to ``distinct`` to generate a "

6590 "DISTINCT ON clause is deprecated. Use instead the "

6591 "``postgresql.distinct_on`` function as an extension.",

6592 "2.1",

6593 )

6594 self._distinct_on = self._distinct_on + tuple(

6595 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6596 for e in expr

6597 )

6598 return self

6599

6600 @_generative

6601 def select_from(self, *froms: _FromClauseArgument) -> Self:

6602 r"""Return a new :func:`_expression.select` construct with the

6603 given FROM expression(s)

6604 merged into its list of FROM objects.

6605

6606 E.g.::

6607

6608 table1 = table("t1", column("a"))

6609 table2 = table("t2", column("b"))

6610 s = select(table1.c.a).select_from(

6611 table1.join(table2, table1.c.a == table2.c.b)

6612 )

6613

6614 The "from" list is a unique set on the identity of each element,

6615 so adding an already present :class:`_schema.Table`

6616 or other selectable

6617 will have no effect. Passing a :class:`_expression.Join` that refers

6618 to an already present :class:`_schema.Table`

6619 or other selectable will have

6620 the effect of concealing the presence of that selectable as

6621 an individual element in the rendered FROM list, instead

6622 rendering it into a JOIN clause.

6623

6624 While the typical purpose of :meth:`_expression.Select.select_from`

6625 is to

6626 replace the default, derived FROM clause with a join, it can

6627 also be called with individual table elements, multiple times

6628 if desired, in the case that the FROM clause cannot be fully

6629 derived from the columns clause::

6630

6631 select(func.count("*")).select_from(table1)

6632

6633 """

6634

6635 self._from_obj += tuple(

6636 coercions.expect(

6637 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6638 )

6639 for fromclause in froms

6640 )

6641 return self

6642

6643 @_generative

6644 def correlate(

6645 self,

6646 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6647 ) -> Self:

6648 r"""Return a new :class:`_expression.Select`

6649 which will correlate the given FROM

6650 clauses to that of an enclosing :class:`_expression.Select`.

6651

6652 Calling this method turns off the :class:`_expression.Select` object's

6653 default behavior of "auto-correlation". Normally, FROM elements

6654 which appear in a :class:`_expression.Select`

6655 that encloses this one via

6656 its :term:`WHERE clause`, ORDER BY, HAVING or

6657 :term:`columns clause` will be omitted from this

6658 :class:`_expression.Select`

6659 object's :term:`FROM clause`.

6660 Setting an explicit correlation collection using the

6661 :meth:`_expression.Select.correlate`

6662 method provides a fixed list of FROM objects

6663 that can potentially take place in this process.

6664

6665 When :meth:`_expression.Select.correlate`

6666 is used to apply specific FROM clauses

6667 for correlation, the FROM elements become candidates for

6668 correlation regardless of how deeply nested this

6669 :class:`_expression.Select`

6670 object is, relative to an enclosing :class:`_expression.Select`

6671 which refers to

6672 the same FROM object. This is in contrast to the behavior of

6673 "auto-correlation" which only correlates to an immediate enclosing

6674 :class:`_expression.Select`.

6675 Multi-level correlation ensures that the link

6676 between enclosed and enclosing :class:`_expression.Select`

6677 is always via

6678 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6679 correlation to take place.

6680

6681 If ``None`` is passed, the :class:`_expression.Select`

6682 object will correlate

6683 none of its FROM entries, and all will render unconditionally

6684 in the local FROM clause.

6685

6686 :param \*fromclauses: one or more :class:`.FromClause` or other

6687 FROM-compatible construct such as an ORM mapped entity to become part

6688 of the correlate collection; alternatively pass a single value

6689 ``None`` to remove all existing correlations.

6690

6691 .. seealso::

6692

6693 :meth:`_expression.Select.correlate_except`

6694

6695 :ref:`tutorial_scalar_subquery`

6696

6697 """

6698

6699 # tests failing when we try to change how these

6700 # arguments are passed

6701

6702 self._auto_correlate = False

6703 if not fromclauses or fromclauses[0] in {None, False}:

6704 if len(fromclauses) > 1:

6705 raise exc.ArgumentError(

6706 "additional FROM objects not accepted when "

6707 "passing None/False to correlate()"

6708 )

6709 self._correlate = ()

6710 else:

6711 self._correlate = self._correlate + tuple(

6712 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6713 )

6714 return self

6715

6716 @_generative

6717 def correlate_except(

6718 self,

6719 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6720 ) -> Self:

6721 r"""Return a new :class:`_expression.Select`

6722 which will omit the given FROM

6723 clauses from the auto-correlation process.

6724

6725 Calling :meth:`_expression.Select.correlate_except` turns off the

6726 :class:`_expression.Select` object's default behavior of

6727 "auto-correlation" for the given FROM elements. An element

6728 specified here will unconditionally appear in the FROM list, while

6729 all other FROM elements remain subject to normal auto-correlation

6730 behaviors.

6731

6732 If ``None`` is passed, or no arguments are passed,

6733 the :class:`_expression.Select` object will correlate all of its

6734 FROM entries.

6735

6736 :param \*fromclauses: a list of one or more

6737 :class:`_expression.FromClause`

6738 constructs, or other compatible constructs (i.e. ORM-mapped

6739 classes) to become part of the correlate-exception collection.

6740

6741 .. seealso::

6742

6743 :meth:`_expression.Select.correlate`

6744

6745 :ref:`tutorial_scalar_subquery`

6746

6747 """

6748

6749 self._auto_correlate = False

6750 if not fromclauses or fromclauses[0] in {None, False}:

6751 if len(fromclauses) > 1:

6752 raise exc.ArgumentError(

6753 "additional FROM objects not accepted when "

6754 "passing None/False to correlate_except()"

6755 )

6756 self._correlate_except = ()

6757 else:

6758 self._correlate_except = (self._correlate_except or ()) + tuple(

6759 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6760 )

6761

6762 return self

6763

6764 @HasMemoized_ro_memoized_attribute

6765 def selected_columns(

6766 self,

6767 ) -> ColumnCollection[str, ColumnElement[Any]]:

6768 """A :class:`_expression.ColumnCollection`

6769 representing the columns that

6770 this SELECT statement or similar construct returns in its result set,

6771 not including :class:`_sql.TextClause` constructs.

6772

6773 This collection differs from the :attr:`_expression.FromClause.columns`

6774 collection of a :class:`_expression.FromClause` in that the columns

6775 within this collection cannot be directly nested inside another SELECT

6776 statement; a subquery must be applied first which provides for the

6777 necessary parenthesization required by SQL.

6778

6779 For a :func:`_expression.select` construct, the collection here is

6780 exactly what would be rendered inside the "SELECT" statement, and the

6781 :class:`_expression.ColumnElement` objects are directly present as they

6782 were given, e.g.::

6783

6784 col1 = column("q", Integer)

6785 col2 = column("p", Integer)

6786 stmt = select(col1, col2)

6787

6788 Above, ``stmt.selected_columns`` would be a collection that contains

6789 the ``col1`` and ``col2`` objects directly. For a statement that is

6790 against a :class:`_schema.Table` or other

6791 :class:`_expression.FromClause`, the collection will use the

6792 :class:`_expression.ColumnElement` objects that are in the

6793 :attr:`_expression.FromClause.c` collection of the from element.

6794

6795 A use case for the :attr:`_sql.Select.selected_columns` collection is

6796 to allow the existing columns to be referenced when adding additional

6797 criteria, e.g.::

6798

6799 def filter_on_id(my_select, id):

6800 return my_select.where(my_select.selected_columns["id"] == id)

6801

6802

6803 stmt = select(MyModel)

6804

6805 # adds "WHERE id=:param" to the statement

6806 stmt = filter_on_id(stmt, 42)

6807

6808 .. note::

6809

6810 The :attr:`_sql.Select.selected_columns` collection does not

6811 include expressions established in the columns clause using the

6812 :func:`_sql.text` construct; these are silently omitted from the

6813 collection. To use plain textual column expressions inside of a

6814 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6815 construct.

6816

6817

6818 .. versionadded:: 1.4

6819

6820 """

6821

6822 # compare to SelectState._generate_columns_plus_names, which

6823 # generates the actual names used in the SELECT string. that

6824 # method is more complex because it also renders columns that are

6825 # fully ambiguous, e.g. same column more than once.

6826 conv = cast(

6827 "Callable[[Any], str]",

6828 SelectState._column_naming_convention(self._label_style),

6829 )

6830

6831 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6832 WriteableColumnCollection(

6833 [

6834 (conv(c), c)

6835 for c in self._all_selected_columns

6836 if is_column_element(c)

6837 ]

6838 )

6839 )

6840 return cc.as_readonly()

6841

6842 @HasMemoized_ro_memoized_attribute

6843 def _all_selected_columns(self) -> _SelectIterable:

6844 meth = SelectState.get_plugin_class(self).all_selected_columns

6845 return list(meth(self))

6846

6847 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6848 if self._label_style is LABEL_STYLE_NONE:

6849 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6850 return self

6851

6852 def _generate_fromclause_column_proxies(

6853 self,

6854 subquery: FromClause,

6855 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

6856 primary_key: ColumnSet,

6857 foreign_keys: Set[KeyedColumnElement[Any]],

6858 *,

6859 proxy_compound_columns: Optional[

6860 Iterable[Sequence[ColumnElement[Any]]]

6861 ] = None,

6862 ) -> None:

6863 """Generate column proxies to place in the exported ``.c``

6864 collection of a subquery."""

6865

6866 if proxy_compound_columns:

6867 extra_col_iterator = proxy_compound_columns

6868 prox = [

6869 c._make_proxy(

6870 subquery,

6871 key=proxy_key,

6872 name=required_label_name,

6873 name_is_truncatable=True,

6874 compound_select_cols=extra_cols,

6875 primary_key=primary_key,

6876 foreign_keys=foreign_keys,

6877 )

6878 for (

6879 (

6880 required_label_name,

6881 proxy_key,

6882 fallback_label_name,

6883 c,

6884 repeated,

6885 ),

6886 extra_cols,

6887 ) in (

6888 zip(

6889 self._generate_columns_plus_names(False),

6890 extra_col_iterator,

6891 )

6892 )

6893 if is_column_element(c)

6894 ]

6895 else:

6896 prox = [

6897 c._make_proxy(

6898 subquery,

6899 key=proxy_key,

6900 name=required_label_name,

6901 name_is_truncatable=True,

6902 primary_key=primary_key,

6903 foreign_keys=foreign_keys,

6904 )

6905 for (

6906 required_label_name,

6907 proxy_key,

6908 fallback_label_name,

6909 c,

6910 repeated,

6911 ) in (self._generate_columns_plus_names(False))

6912 if is_column_element(c)

6913 ]

6914

6915 columns._populate_separate_keys(prox)

6916

6917 def _needs_parens_for_grouping(self) -> bool:

6918 return self._has_row_limiting_clause or bool(

6919 self._order_by_clause.clauses

6920 )

6921

6922 def self_group(

6923 self, against: Optional[OperatorType] = None

6924 ) -> Union[SelectStatementGrouping[Self], Self]:

6925 """Return a 'grouping' construct as per the

6926 :class:`_expression.ClauseElement` specification.

6927

6928 This produces an element that can be embedded in an expression. Note

6929 that this method is called automatically as needed when constructing

6930 expressions and should not require explicit use.

6931

6932 """

6933 if (

6934 isinstance(against, CompoundSelect)

6935 and not self._needs_parens_for_grouping()

6936 ):

6937 return self

6938 else:

6939 return SelectStatementGrouping(self)

6940

6941 def union(

6942 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6943 ) -> CompoundSelect[Unpack[_Ts]]:

6944 r"""Return a SQL ``UNION`` of this select() construct against

6945 the given selectables provided as positional arguments.

6946

6947 :param \*other: one or more elements with which to create a

6948 UNION.

6949

6950 .. versionchanged:: 1.4.28

6951

6952 multiple elements are now accepted.

6953

6954 :param \**kwargs: keyword arguments are forwarded to the constructor

6955 for the newly created :class:`_sql.CompoundSelect` object.

6956

6957 """

6958 return CompoundSelect._create_union(self, *other)

6959

6960 def union_all(

6961 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6962 ) -> CompoundSelect[Unpack[_Ts]]:

6963 r"""Return a SQL ``UNION ALL`` of this select() construct against

6964 the given selectables provided as positional arguments.

6965

6966 :param \*other: one or more elements with which to create a

6967 UNION.

6968

6969 .. versionchanged:: 1.4.28

6970

6971 multiple elements are now accepted.

6972

6973 :param \**kwargs: keyword arguments are forwarded to the constructor

6974 for the newly created :class:`_sql.CompoundSelect` object.

6975

6976 """

6977 return CompoundSelect._create_union_all(self, *other)

6978

6979 def except_(

6980 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6981 ) -> CompoundSelect[Unpack[_Ts]]:

6982 r"""Return a SQL ``EXCEPT`` of this select() construct against

6983 the given selectable provided as positional arguments.

6984

6985 :param \*other: one or more elements with which to create a

6986 UNION.

6987

6988 .. versionchanged:: 1.4.28

6989

6990 multiple elements are now accepted.

6991

6992 """

6993 return CompoundSelect._create_except(self, *other)

6994

6995 def except_all(

6996 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6997 ) -> CompoundSelect[Unpack[_Ts]]:

6998 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6999 the given selectables provided as positional arguments.

7000

7001 :param \*other: one or more elements with which to create a

7002 UNION.

7003

7004 .. versionchanged:: 1.4.28

7005

7006 multiple elements are now accepted.

7007

7008 """

7009 return CompoundSelect._create_except_all(self, *other)

7010

7011 def intersect(

7012 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7013 ) -> CompoundSelect[Unpack[_Ts]]:

7014 r"""Return a SQL ``INTERSECT`` of this select() construct against

7015 the given selectables provided as positional arguments.

7016

7017 :param \*other: one or more elements with which to create a

7018 UNION.

7019

7020 .. versionchanged:: 1.4.28

7021

7022 multiple elements are now accepted.

7023

7024 :param \**kwargs: keyword arguments are forwarded to the constructor

7025 for the newly created :class:`_sql.CompoundSelect` object.

7026

7027 """

7028 return CompoundSelect._create_intersect(self, *other)

7029

7030 def intersect_all(

7031 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7032 ) -> CompoundSelect[Unpack[_Ts]]:

7033 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

7034 against the given selectables provided as positional arguments.

7035

7036 :param \*other: one or more elements with which to create a

7037 UNION.

7038

7039 .. versionchanged:: 1.4.28

7040

7041 multiple elements are now accepted.

7042

7043 :param \**kwargs: keyword arguments are forwarded to the constructor

7044 for the newly created :class:`_sql.CompoundSelect` object.

7045

7046 """

7047 return CompoundSelect._create_intersect_all(self, *other)

7048

7049

7050class ScalarSelect(

7051 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7052):

7053 """Represent a scalar subquery.

7054

7055

7056 A :class:`_sql.ScalarSelect` is created by invoking the

7057 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7058 then participates in other SQL expressions as a SQL column expression

7059 within the :class:`_sql.ColumnElement` hierarchy.

7060

7061 .. seealso::

7062

7063 :meth:`_sql.SelectBase.scalar_subquery`

7064

7065 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7066

7067 """

7068

7069 _traverse_internals: _TraverseInternalsType = [

7070 ("element", InternalTraversal.dp_clauseelement),

7071 ("type", InternalTraversal.dp_type),

7072 ]

7073

7074 _from_objects: List[FromClause] = []

7075 _is_from_container = True

7076 if not TYPE_CHECKING:

7077 _is_implicitly_boolean = False

7078 inherit_cache = True

7079

7080 element: SelectBase

7081

7082 def __init__(self, element: SelectBase) -> None:

7083 self.element = element

7084 self.type = element._scalar_type()

7085 self._propagate_attrs = element._propagate_attrs

7086

7087 def __getattr__(self, attr: str) -> Any:

7088 return getattr(self.element, attr)

7089

7090 def __getstate__(self) -> Dict[str, Any]:

7091 return {"element": self.element, "type": self.type}

7092

7093 def __setstate__(self, state: Dict[str, Any]) -> None:

7094 self.element = state["element"]

7095 self.type = state["type"]

7096

7097 @property

7098 def columns(self) -> NoReturn:

7099 raise exc.InvalidRequestError(

7100 "Scalar Select expression has no "

7101 "columns; use this object directly "

7102 "within a column-level expression."

7103 )

7104

7105 c = columns

7106

7107 @_generative

7108 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7109 """Apply a WHERE clause to the SELECT statement referred to

7110 by this :class:`_expression.ScalarSelect`.

7111

7112 """

7113 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7114 crit

7115 )

7116 return self

7117

7118 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7119 return self

7120

7121 def _ungroup(self) -> Self:

7122 return self

7123

7124 @_generative

7125 def correlate(

7126 self,

7127 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7128 ) -> Self:

7129 r"""Return a new :class:`_expression.ScalarSelect`

7130 which will correlate the given FROM

7131 clauses to that of an enclosing :class:`_expression.Select`.

7132

7133 This method is mirrored from the :meth:`_sql.Select.correlate` method

7134 of the underlying :class:`_sql.Select`. The method applies the

7135 :meth:_sql.Select.correlate` method, then returns a new

7136 :class:`_sql.ScalarSelect` against that statement.

7137

7138 .. versionadded:: 1.4 Previously, the

7139 :meth:`_sql.ScalarSelect.correlate`

7140 method was only available from :class:`_sql.Select`.

7141

7142 :param \*fromclauses: a list of one or more

7143 :class:`_expression.FromClause`

7144 constructs, or other compatible constructs (i.e. ORM-mapped

7145 classes) to become part of the correlate collection.

7146

7147 .. seealso::

7148

7149 :meth:`_expression.ScalarSelect.correlate_except`

7150

7151 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7152

7153

7154 """

7155 self.element = cast(

7156 "Select[Unpack[TupleAny]]", self.element

7157 ).correlate(*fromclauses)

7158 return self

7159

7160 @_generative

7161 def correlate_except(

7162 self,

7163 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7164 ) -> Self:

7165 r"""Return a new :class:`_expression.ScalarSelect`

7166 which will omit the given FROM

7167 clauses from the auto-correlation process.

7168

7169 This method is mirrored from the

7170 :meth:`_sql.Select.correlate_except` method of the underlying

7171 :class:`_sql.Select`. The method applies the

7172 :meth:_sql.Select.correlate_except` method, then returns a new

7173 :class:`_sql.ScalarSelect` against that statement.

7174

7175 .. versionadded:: 1.4 Previously, the

7176 :meth:`_sql.ScalarSelect.correlate_except`

7177 method was only available from :class:`_sql.Select`.

7178

7179 :param \*fromclauses: a list of one or more

7180 :class:`_expression.FromClause`

7181 constructs, or other compatible constructs (i.e. ORM-mapped

7182 classes) to become part of the correlate-exception collection.

7183

7184 .. seealso::

7185

7186 :meth:`_expression.ScalarSelect.correlate`

7187

7188 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7189

7190

7191 """

7192

7193 self.element = cast(

7194 "Select[Unpack[TupleAny]]", self.element

7195 ).correlate_except(*fromclauses)

7196 return self

7197

7198

7199class Exists(UnaryExpression[bool]):

7200 """Represent an ``EXISTS`` clause.

7201

7202 See :func:`_sql.exists` for a description of usage.

7203

7204 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7205 instance by calling :meth:`_sql.SelectBase.exists`.

7206

7207 """

7208

7209 inherit_cache = True

7210

7211 def __init__(

7212 self,

7213 __argument: Optional[

7214 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7215 ] = None,

7216 /,

7217 ):

7218 s: ScalarSelect[Any]

7219

7220 # TODO: this seems like we should be using coercions for this

7221 if __argument is None:

7222 s = Select(literal_column("*")).scalar_subquery()

7223 elif isinstance(__argument, SelectBase):

7224 s = __argument.scalar_subquery()

7225 s._propagate_attrs = __argument._propagate_attrs

7226 elif isinstance(__argument, ScalarSelect):

7227 s = __argument

7228 else:

7229 s = Select(__argument).scalar_subquery()

7230

7231 UnaryExpression.__init__(

7232 self,

7233 s,

7234 operator=operators.exists,

7235 type_=type_api.BOOLEANTYPE,

7236 )

7237

7238 @util.ro_non_memoized_property

7239 def _from_objects(self) -> List[FromClause]:

7240 return []

7241

7242 def _regroup(

7243 self,

7244 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7245 ) -> ScalarSelect[Any]:

7246

7247 assert isinstance(self.element, ScalarSelect)

7248 element = self.element.element

7249 if not isinstance(element, Select):

7250 raise exc.InvalidRequestError(

7251 "Can only apply this operation to a plain SELECT construct"

7252 )

7253 new_element = fn(element)

7254

7255 return_value = new_element.scalar_subquery()

7256 return return_value

7257

7258 def select(self) -> Select[bool]:

7259 r"""Return a SELECT of this :class:`_expression.Exists`.

7260

7261 e.g.::

7262

7263 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7264

7265 This will produce a statement resembling:

7266

7267 .. sourcecode:: sql

7268

7269 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7270

7271 .. seealso::

7272

7273 :func:`_expression.select` - general purpose

7274 method which allows for arbitrary column lists.

7275

7276 """ # noqa

7277

7278 return Select(self)

7279

7280 def correlate(

7281 self,

7282 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7283 ) -> Self:

7284 """Apply correlation to the subquery noted by this

7285 :class:`_sql.Exists`.

7286

7287 .. seealso::

7288

7289 :meth:`_sql.ScalarSelect.correlate`

7290

7291 """

7292 e = self._clone()

7293 e.element = self._regroup(

7294 lambda element: element.correlate(*fromclauses)

7295 )

7296 return e

7297

7298 def correlate_except(

7299 self,

7300 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7301 ) -> Self:

7302 """Apply correlation to the subquery noted by this

7303 :class:`_sql.Exists`.

7304

7305 .. seealso::

7306

7307 :meth:`_sql.ScalarSelect.correlate_except`

7308

7309 """

7310 e = self._clone()

7311 e.element = self._regroup(

7312 lambda element: element.correlate_except(*fromclauses)

7313 )

7314 return e

7315

7316 def select_from(self, *froms: _FromClauseArgument) -> Self:

7317 """Return a new :class:`_expression.Exists` construct,

7318 applying the given

7319 expression to the :meth:`_expression.Select.select_from`

7320 method of the select

7321 statement contained.

7322

7323 .. note:: it is typically preferable to build a :class:`_sql.Select`

7324 statement first, including the desired WHERE clause, then use the

7325 :meth:`_sql.SelectBase.exists` method to produce an

7326 :class:`_sql.Exists` object at once.

7327

7328 """

7329 e = self._clone()

7330 e.element = self._regroup(lambda element: element.select_from(*froms))

7331 return e

7332

7333 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7334 """Return a new :func:`_expression.exists` construct with the

7335 given expression added to

7336 its WHERE clause, joined to the existing clause via AND, if any.

7337

7338

7339 .. note:: it is typically preferable to build a :class:`_sql.Select`

7340 statement first, including the desired WHERE clause, then use the

7341 :meth:`_sql.SelectBase.exists` method to produce an

7342 :class:`_sql.Exists` object at once.

7343

7344 """

7345 e = self._clone()

7346 e.element = self._regroup(lambda element: element.where(*clause))

7347 return e

7348

7349

7350class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7351 """Wrap a :class:`_expression.TextClause` construct within a

7352 :class:`_expression.SelectBase`

7353 interface.

7354

7355 This allows the :class:`_expression.TextClause` object to gain a

7356 ``.c`` collection

7357 and other FROM-like capabilities such as

7358 :meth:`_expression.FromClause.alias`,

7359 :meth:`_expression.SelectBase.cte`, etc.

7360

7361 The :class:`_expression.TextualSelect` construct is produced via the

7362 :meth:`_expression.TextClause.columns`

7363 method - see that method for details.

7364

7365 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7366 class was renamed

7367 from ``TextAsFrom``, to more correctly suit its role as a

7368 SELECT-oriented object and not a FROM clause.

7369

7370 .. seealso::

7371

7372 :func:`_expression.text`

7373

7374 :meth:`_expression.TextClause.columns` - primary creation interface.

7375

7376 """

7377

7378 __visit_name__ = "textual_select"

7379

7380 _label_style = LABEL_STYLE_NONE

7381

7382 _traverse_internals: _TraverseInternalsType = (

7383 [

7384 ("element", InternalTraversal.dp_clauseelement),

7385 ("column_args", InternalTraversal.dp_clauseelement_list),

7386 ]

7387 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7388 + HasCTE._has_ctes_traverse_internals

7389 + ExecutableStatement._executable_traverse_internals

7390 )

7391

7392 _is_textual = True

7393

7394 is_text = True

7395 is_select = True

7396

7397 def __init__(

7398 self,

7399 text: TextClause,

7400 columns: List[_ColumnExpressionArgument[Any]],

7401 positional: bool = False,

7402 ) -> None:

7403 self._init(

7404 text,

7405 # convert for ORM attributes->columns, etc

7406 [

7407 coercions.expect(roles.LabeledColumnExprRole, c)

7408 for c in columns

7409 ],

7410 positional,

7411 )

7412

7413 def _init(

7414 self,

7415 text: AbstractTextClause,

7416 columns: List[NamedColumn[Any]],

7417 positional: bool = False,

7418 ) -> None:

7419 self.element = text

7420 self.column_args = columns

7421 self.positional = positional

7422

7423 @HasMemoized_ro_memoized_attribute

7424 def selected_columns(

7425 self,

7426 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7427 """A :class:`_expression.ColumnCollection`

7428 representing the columns that

7429 this SELECT statement or similar construct returns in its result set,

7430 not including :class:`_sql.TextClause` constructs.

7431

7432 This collection differs from the :attr:`_expression.FromClause.columns`

7433 collection of a :class:`_expression.FromClause` in that the columns

7434 within this collection cannot be directly nested inside another SELECT

7435 statement; a subquery must be applied first which provides for the

7436 necessary parenthesization required by SQL.

7437

7438 For a :class:`_expression.TextualSelect` construct, the collection

7439 contains the :class:`_expression.ColumnElement` objects that were

7440 passed to the constructor, typically via the

7441 :meth:`_expression.TextClause.columns` method.

7442

7443

7444 .. versionadded:: 1.4

7445

7446 """

7447 return WriteableColumnCollection(

7448 (c.key, c) for c in self.column_args

7449 ).as_readonly()

7450

7451 @util.ro_non_memoized_property

7452 def _all_selected_columns(self) -> _SelectIterable:

7453 return self.column_args

7454

7455 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7456 return self

7457

7458 def _ensure_disambiguated_names(self) -> TextualSelect:

7459 return self

7460

7461 @_generative

7462 def bindparams(

7463 self,

7464 *binds: BindParameter[Any],

7465 **bind_as_values: Any,

7466 ) -> Self:

7467 self.element = self.element.bindparams(*binds, **bind_as_values)

7468 return self

7469

7470 def _generate_fromclause_column_proxies(

7471 self,

7472 fromclause: FromClause,

7473 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

7474 primary_key: ColumnSet,

7475 foreign_keys: Set[KeyedColumnElement[Any]],

7476 *,

7477 proxy_compound_columns: Optional[

7478 Iterable[Sequence[ColumnElement[Any]]]

7479 ] = None,

7480 ) -> None:

7481 if TYPE_CHECKING:

7482 assert isinstance(fromclause, Subquery)

7483

7484 if proxy_compound_columns:

7485 columns._populate_separate_keys(

7486 c._make_proxy(

7487 fromclause,

7488 compound_select_cols=extra_cols,

7489 primary_key=primary_key,

7490 foreign_keys=foreign_keys,

7491 )

7492 for c, extra_cols in zip(

7493 self.column_args, proxy_compound_columns

7494 )

7495 )

7496 else:

7497 columns._populate_separate_keys(

7498 c._make_proxy(

7499 fromclause,

7500 primary_key=primary_key,

7501 foreign_keys=foreign_keys,

7502 )

7503 for c in self.column_args

7504 )

7505

7506 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7507 return self.column_args[0].type

7508

7509

7510TextAsFrom = TextualSelect

7511"""Backwards compatibility with the previous name"""

7512

7513

7514class AnnotatedFromClause(Annotated):

7515 def _copy_internals(

7516 self,

7517 _annotations_traversal: bool = False,

7518 ind_cols_on_fromclause: bool = False,

7519 **kw: Any,

7520 ) -> None:

7521 super()._copy_internals(**kw)

7522

7523 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7524 # the traversals used by annotations for these cases are not currently

7525 # designed around expecting that inner elements inside of

7526 # AnnotatedFromClause's element are also deep copied, so skip for these

7527 # cases. in other cases such as plain visitors.cloned_traverse(), we

7528 # expect this to happen. see issue #12915

7529 if not _annotations_traversal:

7530 ee = self._Annotated__element # type: ignore

7531 ee._copy_internals(**kw)

7532

7533 if ind_cols_on_fromclause:

7534 # passed from annotations._deep_annotate(). See that function

7535 # for notes

7536 ee = self._Annotated__element # type: ignore

7537 self.c = ee.__class__.c.fget(self) # type: ignore

7538

7539 @util.ro_memoized_property

7540 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7541 """proxy the .c collection of the underlying FromClause.

7542

7543 Originally implemented in 2008 as a simple load of the .c collection

7544 when the annotated construct was created (see d3621ae961a), in modern

7545 SQLAlchemy versions this can be expensive for statements constructed

7546 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7547 it, which works just as well.

7548

7549 Two different use cases seem to require the collection either copied

7550 from the underlying one, or unique to this AnnotatedFromClause.

7551

7552 See test_selectable->test_annotated_corresponding_column

7553

7554 """

7555 ee = self._Annotated__element # type: ignore

7556 return ee.c # type: ignore