summaryrefslogtreecommitdiff
path: root/lib/sqlalchemy/sql
diff options
context:
space:
mode:
Diffstat (limited to 'lib/sqlalchemy/sql')
-rw-r--r--lib/sqlalchemy/sql/coercions.py4
-rw-r--r--lib/sqlalchemy/sql/compiler.py19
-rw-r--r--lib/sqlalchemy/sql/crud.py13
-rw-r--r--lib/sqlalchemy/sql/dml.py3
-rw-r--r--lib/sqlalchemy/sql/roles.py4
-rw-r--r--lib/sqlalchemy/sql/selectable.py75
6 files changed, 114 insertions, 4 deletions
diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
index 1da1fee86..16a68c8ff 100644
--- a/lib/sqlalchemy/sql/coercions.py
+++ b/lib/sqlalchemy/sql/coercions.py
@@ -871,6 +871,10 @@ class HasCTEImpl(ReturnsRowsImpl):
__slots__ = ()
+class IsCTEImpl(RoleImpl):
+ __slots__ = ()
+
+
class JoinTargetImpl(RoleImpl):
__slots__ = ()
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index e92ffcd9a..e31a3839e 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2314,8 +2314,7 @@ class SQLCompiler(Compiled):
) and not existing.proxy_set.intersection(bindparam.proxy_set):
raise exc.CompileError(
"Bind parameter '%s' conflicts with "
- "unique bind parameter of the same name"
- % bindparam.key
+ "unique bind parameter of the same name" % name
)
elif existing._is_crud or bindparam._is_crud:
raise exc.CompileError(
@@ -3076,6 +3075,10 @@ class SQLCompiler(Compiled):
else:
byfrom = None
+ if select_stmt._independent_ctes:
+ for cte in select_stmt._independent_ctes:
+ cte._compiler_dispatch(self, **kwargs)
+
if select_stmt._prefixes:
text += self._generate_prefixes(
select_stmt, select_stmt._prefixes, **kwargs
@@ -3552,6 +3555,10 @@ class SQLCompiler(Compiled):
if insert_stmt._hints:
_, table_text = self._setup_crud_hints(insert_stmt, table_text)
+ if insert_stmt._independent_ctes:
+ for cte in insert_stmt._independent_ctes:
+ cte._compiler_dispatch(self, **kw)
+
text += table_text
if crud_params_single or not supports_default_values:
@@ -3701,6 +3708,10 @@ class SQLCompiler(Compiled):
else:
dialect_hints = None
+ if update_stmt._independent_ctes:
+ for cte in update_stmt._independent_ctes:
+ cte._compiler_dispatch(self, **kw)
+
text += table_text
text += " SET "
@@ -3809,6 +3820,10 @@ class SQLCompiler(Compiled):
else:
dialect_hints = None
+ if delete_stmt._independent_ctes:
+ for cte in delete_stmt._independent_ctes:
+ cte._compiler_dispatch(self, **kw)
+
text += table_text
if delete_stmt._returning:
diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index de847fb7f..74f5a1d05 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -224,7 +224,17 @@ def _handle_values_anonymous_param(compiler, col, value, name, **kw):
# rather than having
# compiler.visit_bindparam()->compiler._truncated_identifier make up a
# name. Saves on call counts also.
- if value.unique and isinstance(value.key, elements._truncated_label):
+
+ # for INSERT/UPDATE that's a CTE, we don't need names to match to
+ # external parameters and these would also conflict in the case where
+ # multiple insert/update are combined together using CTEs
+ is_cte = "visiting_cte" in kw
+
+ if (
+ not is_cte
+ and value.unique
+ and isinstance(value.key, elements._truncated_label)
+ ):
compiler.truncated_names[("bindparam", value.key)] = name
if value.type._isnull:
@@ -460,7 +470,6 @@ def _append_param_parameter(
values,
kw,
):
-
value = parameters.pop(col_key)
col_value = compiler.preparer.format_column(
diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py
index dd012ac86..048475040 100644
--- a/lib/sqlalchemy/sql/dml.py
+++ b/lib/sqlalchemy/sql/dml.py
@@ -829,6 +829,7 @@ class Insert(ValuesBase):
+ HasPrefixes._has_prefixes_traverse_internals
+ DialectKWArgs._dialect_kwargs_traverse_internals
+ Executable._executable_traverse_internals
+ + HasCTE._has_ctes_traverse_internals
)
@ValuesBase._constructor_20_deprecations(
@@ -1119,6 +1120,7 @@ class Update(DMLWhereBase, ValuesBase):
+ HasPrefixes._has_prefixes_traverse_internals
+ DialectKWArgs._dialect_kwargs_traverse_internals
+ Executable._executable_traverse_internals
+ + HasCTE._has_ctes_traverse_internals
)
@ValuesBase._constructor_20_deprecations(
@@ -1357,6 +1359,7 @@ class Delete(DMLWhereBase, UpdateBase):
+ HasPrefixes._has_prefixes_traverse_internals
+ DialectKWArgs._dialect_kwargs_traverse_internals
+ Executable._executable_traverse_internals
+ + HasCTE._has_ctes_traverse_internals
)
@ValuesBase._constructor_20_deprecations(
diff --git a/lib/sqlalchemy/sql/roles.py b/lib/sqlalchemy/sql/roles.py
index a5eefc7b5..b9010397c 100644
--- a/lib/sqlalchemy/sql/roles.py
+++ b/lib/sqlalchemy/sql/roles.py
@@ -182,6 +182,10 @@ class HasCTERole(ReturnsRowsRole):
pass
+class IsCTERole(SQLRole):
+ _role_name = "CTE object"
+
+
class CompoundElementRole(AllowsLambdaRole, SQLRole):
"""SELECT statements inside a CompoundSelect, e.g. UNION, EXTRACT, etc."""
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 42cb6e5ae..d947aed37 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -1986,6 +1986,7 @@ class TableSample(AliasedReturnsRows):
class CTE(
roles.DMLTableRole,
+ roles.IsCTERole,
Generative,
HasPrefixes,
HasSuffixes,
@@ -2110,6 +2111,79 @@ class HasCTE(roles.HasCTERole):
"""
+ _has_ctes_traverse_internals = [
+ ("_independent_ctes", InternalTraversal.dp_clauseelement_list),
+ ]
+
+ _independent_ctes = ()
+
+ @_generative
+ def add_cte(self, cte):
+ """Add a :class:`_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
+ :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
+
+ """
+ cte = coercions.expect(roles.IsCTERole, cte)
+ self._independent_ctes += (cte,)
+
def cte(self, name=None, recursive=False):
r"""Return a new :class:`_expression.CTE`,
or Common Table Expression instance.
@@ -4622,6 +4696,7 @@ class Select(
("_distinct_on", InternalTraversal.dp_clauseelement_tuple),
("_label_style", InternalTraversal.dp_plain_obj),
]
+ + HasCTE._has_ctes_traverse_internals
+ HasPrefixes._has_prefixes_traverse_internals
+ HasSuffixes._has_suffixes_traverse_internals
+ HasHints._has_hints_traverse_internals
View Lorry Controller Status