class HasCTE(roles.HasCTERole, SelectsRows): (source)
Known subclasses: sqlalchemy.sql.dml.UpdateBase
, sqlalchemy.sql.selectable.SelectBase
Mixin that declares a class to include CTE support. .. versionadded:: 1.1
Method | add |
Add one or more :class:`_sql.CTE` constructs to this statement. |
Method | cte |
Return a new :class:`_expression.CTE`, or Common Table Expression instance. |
Class Variable | _has |
Undocumented |
Class Variable | _independent |
Undocumented |
Class Variable | _independent |
Undocumented |
Inherited from HasCTERole
:
Class Variable | __slots__ |
Undocumented |
Inherited from SelectsRows
(via HasCTERole
):
Method | _generate |
Generate column names as rendered in a SELECT statement by the compiler. |
Inherited from ReturnsRows
(via HasCTERole
, SelectsRows
):
Method | is |
Return ``True`` if this :class:`.ReturnsRows` is 'derived' from the given :class:`.FromClause`. |
Property | exported |
A :class:`_expression.ColumnCollection` that represents the "exported" columns of this :class:`_expression.ReturnsRows`. |
Property | selectable |
Undocumented |
Method | _generate |
Populate columns into an :class:`.AliasedReturnsRows` object. |
Method | _refresh |
reset internal collections for an incoming column being added. |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Property | _all |
A sequence of column expression objects that represents the "selected" columns of this :class:`_expression.ReturnsRows`. |
Inherited from ReturnsRowsRole
(via HasCTERole
, SelectsRows
, ReturnsRows
):
Class Variable | _role |
Undocumented |
Inherited from SQLRole
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
):
Class Variable | allows |
Undocumented |
Class Variable | uses |
Undocumented |
Inherited from DQLDMLClauseElement
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
):
Method | compile |
Compile this SQL expression. |
Method | _compiler |
Return a compiler appropriate for this ClauseElement, given a Dialect. |
Inherited from ClauseElement
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
):
Method | __bool__ |
Undocumented |
Method | __getstate__ |
Undocumented |
Method | __invert__ |
Undocumented |
Method | __repr__ |
Undocumented |
Method | compare |
Compare this :class:`_expression.ClauseElement` to the given :class:`_expression.ClauseElement`. |
Method | get |
Return immediate child :class:`.visitors.HasTraverseInternals` elements of this :class:`.visitors.HasTraverseInternals`. |
Method | params |
Return a copy with :func:`_expression.bindparam` elements replaced. |
Method | self |
Apply a 'grouping' to this :class:`_expression.ClauseElement`. |
Method | unique |
Return a copy with :func:`_expression.bindparam` elements replaced. |
Class Variable | __visit |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | negation |
Undocumented |
Property | description |
Undocumented |
Property | entity |
Undocumented |
Method | _clone |
Create a shallow copy of this ClauseElement. |
Method | _cloned |
Return the set consisting all cloned ancestors of this ClauseElement. |
Method | _compile |
Undocumented |
Method | _execute |
Undocumented |
Method | _execute |
an additional hook for subclasses to provide a different implementation for connection.scalar() vs. connection.execute(). |
Method | _negate |
Undocumented |
Method | _negate |
a hook to allow the right side of a binary expression to respond to a negation of the binary expression. |
Method | _replace |
Undocumented |
Method | _set |
Undocumented |
Method | _ungroup |
Return this :class:`_expression.ClauseElement` without any groupings. |
Method | _with |
in the context of binary expression, convert the type of this object to the one given. |
Class Variable | _cache |
Undocumented |
Class Variable | _gen |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Instance Variable | _propagate |
like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time. |
Property | _constructor |
return the 'constructor' for this ClauseElement. |
Property | _from |
Undocumented |
Property | _order |
Undocumented |
Inherited from SupportsWrappingAnnotations
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
):
Method | _annotate |
return a copy of this ClauseElement with annotations updated by the given dictionary. |
Method | _deannotate |
return a copy of this :class:`_expression.ClauseElement` with annotations removed. |
Method | _with |
return a copy of this ClauseElement with annotations replaced by the given dictionary. |
Inherited from SupportsAnnotations
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
):
Class Variable | proxy |
Undocumented |
Method | _gen |
Undocumented |
Property | _annotations |
Undocumented |
Inherited from MemoizedHasCacheKey
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
):
Method | _generate |
return a cache key. |
Inherited from HasCacheKey
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
):
Class Variable | inherit |
Indicate if this :class:`.HasCacheKey` instance should make use of the cache key generation scheme used by its immediate superclass. |
Class Method | _generate |
generate cache key dispatcher for a new class. |
Class Method | _generate |
Undocumented |
Method | _gen |
return an optional cache key. |
Class Variable | _generated |
Undocumented |
Class Variable | _hierarchy |
private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses. |
Class Variable | _is |
Undocumented |
Inherited from HasCopyInternals
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
):
Method | _copy |
Reassign internal elements to be clones of themselves. |
Inherited from HasTraverseInternals
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
, HasCopyInternals
, ExternallyTraversible
):
Class Variable | _traverse |
Undocumented |
Inherited from CompilerElement
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
, HasCopyInternals
, ExternallyTraversible
, HasTraverseInternals
):
Method | __str__ |
Undocumented |
Class Variable | stringify |
Undocumented |
Class Variable | supports |
Undocumented |
Inherited from Visitable
(via HasCTERole
, SelectsRows
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
, HasCopyInternals
, ExternallyTraversible
, HasTraverseInternals
, CompilerElement
):
Method | __class |
Undocumented |
Method | __init |
Undocumented |
Class Method | _generate |
Undocumented |
Method | _compiler |
Undocumented |
Class Variable | _original |
Undocumented |
Add one or more :class:`_sql.CTE` constructs to this statement. This method will associate the given :class:`_sql.CTE` constructs with the parent statement such that they will each be unconditionally rendered in the WITH clause of the final statement, even if not referenced elsewhere within the statement or any sub-selects. The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set to True will have the effect that each given :class:`_sql.CTE` will render in a WITH clause rendered directly along with this statement, rather than being moved to the top of the ultimate rendered statement, even if this statement is rendered as a subquery within a larger statement. This method has two general uses. One is to embed CTE statements that serve some purpose without being referenced explicitly, such as the use case of embedding a DML statement such as an INSERT or UPDATE as a CTE inline with a primary statement that may draw from its results indirectly. The other is to provide control over the exact placement of a particular series of CTE constructs that should remain rendered directly in terms of a particular statement that may be nested in a larger statement. E.g.:: from sqlalchemy import table, column, select t = table('t', column('c1'), column('c2')) ins = t.insert().values({"c1": "x", "c2": "y"}).cte() stmt = select(t).add_cte(ins) Would render:: WITH anon_1 AS (INSERT INTO t (c1, c2) VALUES (:param_1, :param_2)) SELECT t.c1, t.c2 FROM t Above, the "anon_1" CTE is not referred towards in the SELECT statement, however still accomplishes the task of running an INSERT statement. Similarly in a DML-related context, using the PostgreSQL :class:`_postgresql.Insert` construct to generate an "upsert":: from sqlalchemy import table, column from sqlalchemy.dialects.postgresql import insert t = table("t", column("c1"), column("c2")) delete_statement_cte = ( t.delete().where(t.c.c1 < 1).cte("deletions") ) insert_stmt = insert(t).values({"c1": 1, "c2": 2}) update_statement = insert_stmt.on_conflict_do_update( index_elements=[t.c.c1], set_={ "c1": insert_stmt.excluded.c1, "c2": insert_stmt.excluded.c2, }, ).add_cte(delete_statement_cte) print(update_statement) The above statement renders as:: WITH deletions AS (DELETE FROM t WHERE t.c1 < %(c1_1)s) INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s) ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2 .. versionadded:: 1.4.21 :param \*ctes: zero or more :class:`.CTE` constructs. .. versionchanged:: 2.0 Multiple CTE instances are accepted :param nest_here: if True, the given CTE or CTEs will be rendered as though they specified the :paramref:`.HasCTE.cte.nesting` flag to ``True`` when they were added to this :class:`.HasCTE`. Assuming the given CTEs are not referenced in an outer-enclosing statement as well, the CTEs given should render at the level of this statement when this flag is given. .. versionadded:: 2.0 .. seealso:: :paramref:`.HasCTE.cte.nesting`
Optional[ str]
= None, recursive: bool
= False, nesting: bool
= False) -> CTE
:
(source)
¶
Return a new :class:`_expression.CTE`, or Common Table Expression instance. Common table expressions are a SQL standard whereby SELECT statements can draw upon secondary statements specified along with the primary statement, using a clause called "WITH". Special semantics regarding UNION can also be employed to allow "recursive" queries, where a SELECT statement can draw upon the set of rows that have previously been selected. CTEs can also be applied to DML constructs UPDATE, INSERT and DELETE on some databases, both as a source of CTE rows when combined with RETURNING, as well as a consumer of CTE rows. .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as CTE, CTEs added to UPDATE/INSERT/DELETE. SQLAlchemy detects :class:`_expression.CTE` objects, which are treated similarly to :class:`_expression.Alias` objects, as special elements to be delivered to the FROM clause of the statement as well as to a WITH clause at the top of the statement. For special prefixes such as PostgreSQL "MATERIALIZED" and "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with` method may be used to establish these. .. versionchanged:: 1.3.13 Added support for prefixes. In particular - MATERIALIZED and NOT MATERIALIZED. :param name: name given to the common table expression. Like :meth:`_expression.FromClause.alias`, the name can be left as ``None`` in which case an anonymous symbol will be used at query compile time. :param recursive: if ``True``, will render ``WITH RECURSIVE``. A recursive common table expression is intended to be used in conjunction with UNION ALL in order to derive rows from those already selected. :param nesting: if ``True``, will render the CTE locally to the statement in which it is referenced. For more complex scenarios, the :meth:`.HasCTE.add_cte` method using the :paramref:`.HasCTE.add_cte.nest_here` parameter may also be used to more carefully control the exact placement of a particular CTE. .. versionadded:: 1.4.24 .. seealso:: :meth:`.HasCTE.add_cte` The following examples include two from PostgreSQL's documentation at https://www.postgresql.org/docs/current/static/queries-with.html, as well as additional examples. Example 1, non recursive:: from sqlalchemy import (Table, Column, String, Integer, MetaData, select, func) metadata = MetaData() orders = Table('orders', metadata, Column('region', String), Column('amount', Integer), Column('product', String), Column('quantity', Integer) ) regional_sales = select( orders.c.region, func.sum(orders.c.amount).label('total_sales') ).group_by(orders.c.region).cte("regional_sales") top_regions = select(regional_sales.c.region).\ where( regional_sales.c.total_sales > select( func.sum(regional_sales.c.total_sales) / 10 ) ).cte("top_regions") statement = select( orders.c.region, orders.c.product, func.sum(orders.c.quantity).label("product_units"), func.sum(orders.c.amount).label("product_sales") ).where(orders.c.region.in_( select(top_regions.c.region) )).group_by(orders.c.region, orders.c.product) result = conn.execute(statement).fetchall() Example 2, WITH RECURSIVE:: from sqlalchemy import (Table, Column, String, Integer, MetaData, select, func) metadata = MetaData() parts = Table('parts', metadata, Column('part', String), Column('sub_part', String), Column('quantity', Integer), ) included_parts = select(\ parts.c.sub_part, parts.c.part, parts.c.quantity\ ).\ where(parts.c.part=='our part').\ cte(recursive=True) incl_alias = included_parts.alias() parts_alias = parts.alias() included_parts = included_parts.union_all( select( parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity ).\ where(parts_alias.c.part==incl_alias.c.sub_part) ) statement = select( included_parts.c.sub_part, func.sum(included_parts.c.quantity). label('total_quantity') ).\ group_by(included_parts.c.sub_part) result = conn.execute(statement).fetchall() Example 3, an upsert using UPDATE and INSERT with CTEs:: from datetime import date from sqlalchemy import (MetaData, Table, Column, Integer, Date, select, literal, and_, exists) metadata = MetaData() visitors = Table('visitors', metadata, Column('product_id', Integer, primary_key=True), Column('date', Date, primary_key=True), Column('count', Integer), ) # add 5 visitors for the product_id == 1 product_id = 1 day = date.today() count = 5 update_cte = ( visitors.update() .where(and_(visitors.c.product_id == product_id, visitors.c.date == day)) .values(count=visitors.c.count + count) .returning(literal(1)) .cte('update_cte') ) upsert = visitors.insert().from_select( [visitors.c.product_id, visitors.c.date, visitors.c.count], select(literal(product_id), literal(day), literal(count)) .where(~exists(update_cte.select())) ) connection.execute(upsert) Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above):: value_a = select( literal("root").label("n") ).cte("value_a") # A nested CTE with the same name as the root one value_a_nested = select( literal("nesting").label("n") ).cte("value_a", nesting=True) # Nesting CTEs takes ascendency locally # over the CTEs at a higher level value_b = select(value_a_nested.c.n).cte("value_b") value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b")) The above query will render the second CTE nested inside the first, shown with inline parameters below as:: WITH value_a AS (SELECT 'root' AS n), value_b AS (WITH value_a AS (SELECT 'nesting' AS n) SELECT value_a.n AS n FROM value_a) SELECT value_a.n AS a, value_b.n AS b FROM value_a, value_b The same CTE can be set up using the :meth:`.HasCTE.add_cte` method as follows (SQLAlchemy 2.0 and above):: value_a = select( literal("root").label("n") ).cte("value_a") # A nested CTE with the same name as the root one value_a_nested = select( literal("nesting").label("n") ).cte("value_a") # Nesting CTEs takes ascendency locally # over the CTEs at a higher level value_b = ( select(value_a_nested.c.n). add_cte(value_a_nested, nest_here=True). cte("value_b") ) value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b")) Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above):: edge = Table( "edge", metadata, Column("id", Integer, primary_key=True), Column("left", Integer), Column("right", Integer), ) root_node = select(literal(1).label("node")).cte( "nodes", recursive=True ) left_edge = select(edge.c.left).join( root_node, edge.c.right == root_node.c.node ) right_edge = select(edge.c.right).join( root_node, edge.c.left == root_node.c.node ) subgraph_cte = root_node.union(left_edge, right_edge) subgraph = select(subgraph_cte) The above query will render 2 UNIONs inside the recursive CTE:: WITH RECURSIVE nodes(node) AS ( SELECT 1 AS node UNION SELECT edge."left" AS "left" FROM edge JOIN nodes ON edge."right" = nodes.node UNION SELECT edge."right" AS "right" FROM edge JOIN nodes ON edge."left" = nodes.node ) SELECT nodes.node FROM nodes .. seealso:: :meth:`_orm.Query.cte` - ORM version of :meth:`_expression.HasCTE.cte`.