On 12/12/2016 11:10 AM, Joerg Rittinger wrote:
I was thinking of an idea to tell the select to include the WITH
statement if it is only embeded in an INSERT/UPDATE/DELETE for the
sqlalchemy-exasol dialect. Since the variable `toplevel` affects this, a
method controlling this variable was my first guess. I'm open for
different ideas and would contribute something if this helps. With the
current implementation I see no way to fix this sqlalchemy-exasol bug
without copy the complete visit_insert/select/... methods.
the visit_insert() method will be opened up to support this while
attempting to add little to no method overhead.
Here's one way, similar to how we support Oracle's RETURNING, just use a
flag that tells it to assemble the pieces differently:
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index b8c897c..011e4e8 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1991,6 +1991,9 @@ class SQLCompiler(Compiled):
text = "INSERT "
+ if self.cte_precedes_insert and self.ctes and toplevel:
+ text = self._render_cte_clause() + text
+
if insert_stmt._prefixes:
text += self._generate_prefixes(insert_stmt,
insert_stmt._prefixes, **kw)
@@ -2045,7 +2048,7 @@ class SQLCompiler(Compiled):
if returning_clause and not self.returning_precedes_values:
text += " " + returning_clause
- if self.ctes and toplevel:
+ if not self.cte_precedes_insert and self.ctes and toplevel:
text = self._render_cte_clause() + text
self.stack.pop(-1)
Here's another, keep the code to construct all the pieces in
visit_insert(), but put the composition into a new method you can override:
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index b8c897c..e5476d4 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2013,18 +2013,15 @@ class SQLCompiler(Compiled):
if self.returning or insert_stmt._returning:
returning_clause = self.returning_clause(
insert_stmt, self.returning or insert_stmt._returning)
-
- if self.returning_precedes_values:
- text += " " + returning_clause
else:
returning_clause = None
if insert_stmt.select is not None:
- text += " %s" % self.process(self._insert_from_select, **kw)
+ values_clause = " %s" %
self.process(self._insert_from_select, **kw)
elif not crud_params and supports_default_values:
- text += " DEFAULT VALUES"
+ values_clause = " DEFAULT VALUES"
elif insert_stmt._has_multi_parameters:
- text += " VALUES %s" % (
+ values_clause = " VALUES %s" % (
", ".join(
"(%s)" % (
', '.join(c[1] for c in crud_param_set)
@@ -2033,20 +2030,17 @@ class SQLCompiler(Compiled):
)
)
else:
- text += " VALUES (%s)" % \
+ values_clause = " VALUES (%s)" % \
', '.join([c[1] for c in crud_params])
- if insert_stmt._post_values_clause is not None:
- post_values_clause = self.process(
+ text = self._compose_insert_text(
+ text, values_clause,
+ self.process(
insert_stmt._post_values_clause, **kw)
- if post_values_clause:
- text += " " + post_values_clause
-
- if returning_clause and not self.returning_precedes_values:
- text += " " + returning_clause
-
- if self.ctes and toplevel:
- text = self._render_cte_clause() + text
+ if insert_stmt._post_values_clause is not None else None,
+ returning_clause,
+ self._render_cte_clause() if self.ctes and toplevel else None
+ )
self.stack.pop(-1)
the second version might be more flexible and future proof.
I'm not following how that would work. It looks like this merely
impacts the assembly of the insert() construct as far as
insert_stmt.select and self.ctes, if we look around line 3028 of
compiler.py in visit_insert(). It would be cheaper to provide some
hook here for alternate assembly of the insert(). Turning self.stack
into a method adds overhead to visit_select() which is considered to be
more performance critical than visit_insert().
However, if this database has similar non-standard arrangements for
other kinds of constructs like UPDATE and DELETE, those should be
considered as well.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
