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:
from sqlalchemy import MetaData, Table, Column, Integer, select
metadata = MetaData()
table_A = Table("table_A", metadata,
Column("int", Integer))
table_B = Table("table_B", metadata,
Column("int", Integer))
table_C = Table("table_C", metadata,
Column("int", Integer))
cte = select(
columns=[table_A.c.int],
from_obj=table_A).cte("cte")
query = select(
columns=[table_B.c.int],
from_obj=table_B.join(cte, onclause=table_B.c.int == cte.c.int))
insert = table_C.insert().from_select(['int'], query)
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
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].
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.
