class HasCTE(roles.HasCTERole):
Known subclasses: sqlalchemy.sql.dml.UpdateBase
, sqlalchemy.sql.selectable.SelectBase
Mixin that declares a class to include CTE support.
Method | add_cte |
Add a _sql.CTE to this statement object that will be independently rendered even if not referenced in the statement otherwise. |
Method | cte |
Return a new _expression.CTE , or Common Table Expression instance. |
Class Variable | _has_ctes_traverse_internals |
Undocumented |
Class Variable | _independent_ctes |
Undocumented |
Inherited from ReturnsRowsRole
(via HasCTERole
):
Class Variable | _role_name |
Undocumented |
Inherited from SQLRole
(via HasCTERole
, ReturnsRowsRole
):
Class Variable | allows_lambda |
Undocumented |
Class Variable | uses_inspection |
Undocumented |
Add a _sql.CTE
to this statement object that will be
independently rendered even if not referenced in the statement
otherwise.
This feature is useful for 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; while PostgreSQL is known to support this usage, it may not be supported by other backends.
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
_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
Return a new _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.
SQLAlchemy detects _expression.CTE
objects, which are treated
similarly to _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 _expression.CTE.prefix_with
method may be
used to establish these.
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
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
See Also
_orm.Query.cte
- ORM version of
_expression.HasCTE.cte
.
Parameters | |
name | name given to the common table expression. Like
_expression.FromClause.alias , the name can be left as
None in which case an anonymous symbol will be used at query
compile time. |
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. |
nesting | if True, will render the CTE locally to the actual statement.
New in version 1.4.24.
|