On 12/10/2016 05:28 PM, Joerg Rittinger wrote:
Hello,
I'm working with an EXASOL database
(https://github.com/blue-yonder/sqlalchemy_exasol) and have an insert
statement containing a common table expression. In a very simplified way:
|
If you print the insert statement you will end up with
|WITH cte AS
(SELECT "table_A".int AS int
FROM "table_A")
INSERT INTO "table_C" (int) SELECT "table_B".int
FROM "table_B" JOIN cte ON "table_B".int = cte.int||
|
But the EXASOL dialect will complain (syntax error, unexpected INSERT_,
expecting SELECT_ or TABLE_ or VALUES_ or '(') because the expected
syntax would be the WITH statement before the SELECT directly:
|INSERT INTO "table_C" (int)
|||WITH cte AS
(SELECT "table_A".int AS int
FROM "table_A")
|SELECT "table_B".int
FROM "table_B" JOIN cte ON "table_B".int = cte.int
|The sqlalchemy-exasol dialect should handle this difference to standard
SQL syntax. But it would be quite hard to handle this differently
because the section in the code is embeded deep into complex methods:
* before INSERT:
https://bitbucket.org/zzzeek/sqlalchemy/src/55ad10370f9eb50795e136aac595193168982e92/lib/sqlalchemy/sql/compiler.py?at=master#compiler.py-2049
* before SELECT:
https://bitbucket.org/zzzeek/sqlalchemy/src/55ad10370f9eb50795e136aac595193168982e92/lib/sqlalchemy/sql/compiler.py?at=master#compiler.py-1750
One option to handle this would be to refactor the following line
https://bitbucket.org/zzzeek/sqlalchemy/src/55ad10370f9eb50795e136aac595193168982e92/lib/sqlalchemy/sql/compiler.py?at=master#compiler.py-1664
with
|
toplevel = self._is_toplevel_select()
|
and a method containing
|
def _is_toplevel_select(self):
return not self.stack
|
which could be overwritten in sqlalchemy-exasol with something like:
|
def _is_toplevel_select(self):
stack = [element for element in self.stack
if isinstance(element["selectable"], FromClause)]
return not stack
|
and with the visit_insert method accordingly:
https://bitbucket.org/zzzeek/sqlalchemy/src/55ad10370f9eb50795e136aac595193168982e92/lib/sqlalchemy/sql/compiler.py?at=master#compiler.py-1960
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.
This might not a proper solution since I do not know the compiler module
very good.
I would be very happy if you have some thoughts about this and if you
think it would be of some use to allowing the sqlalchemy-exasol dialect
to handle this without reimplementing the big
visit_insert/select/update/delete methods for the corner case.
Best regards,
Joerg
--
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.
