class documentation

class HasCTE(roles.HasCTERole):

Known subclasses: sqlalchemy.sql.dml.UpdateBase, sqlalchemy.sql.selectable.SelectBase

View In Hierarchy

Mixin that declares a class to include CTE support.

New in version 1.1.
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
@_generative
def add_cte(self, cte):

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
New in version 1.4.21.
def cte(self, name=None, recursive=False, nesting=False):

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.

Changed in version 1.1: Added support for UPDATE/INSERT/DELETE as CTE, CTEs added to UPDATE/INSERT/DELETE.

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.

Changed in version 1.3.13: Added support for prefixes. In particular - MATERIALIZED and NOT MATERIALIZED.

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
namename 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.
recursiveif 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.
_has_ctes_traverse_internals =

Undocumented

_independent_ctes: tuple =

Undocumented