带插入的SQLAlchemy With子句(CTE)未针对Oracle正确编译

3bygqnnd 于 2023-02-11 发布在 Oracle

关注(0)|答案(1)|浏览(129)

我尝试在SQLAlchemy中为Oracle 12CR1数据库生成以下人为的DML语句：

INSERT INTO baz (name, qty)
WITH bar AS (
    SELECT bar.name, bar.qty
    FROM bar
)
SELECT foo.name, bar.qty
FROM foo JOIN bar ON foo.name = bar.name

但是，SQLAlchemy似乎没有按顺序生成它，如下所示：

WITH bar AS (
    SELECT bar.name, bar.qty
    FROM bar
)
INSERT INTO baz (name, qty)
SELECT foo.name, bar.qty
FROM foo JOIN bar ON foo.name = bar.name

无序执行此命令失败，并返回ORA-00923: FROM keyword not found where expected。
我的理论是SQLAlchemy假设PostgreSQL语法是WITH ... INSERT INTO ... SELECT ...，而不考虑Oracle语法是INSERT INTO ... WITH ... SELECT ...。
有没有什么方法可以让它在Oracle中工作，或者至少有一种方法可以修改insert().from_select()的编译方式？
重现：

from sqlalchemy import (Table, Column, String, Integer,
                    MetaData, select, func)
from sqlalchemy.dialects import oracle

metadata = MetaData()

foo = Table('foo', metadata, Column('name', String))
bar = Table('bar', metadata, Column('name', String), Column('qty', Integer))
baz = Table('baz', metadata, Column('name', String), Column('qty', Integer))

with_bar = select([bar.c.name, bar.c.qty]).cte('bar')
sel = select([foo.c.name, with_bar.c.qty])
sel = sel.select_from(
        foo.join(with_bar, 
                 foo.c.name == with_bar.c.name
                 )
)

ins = baz.insert().from_select([baz.c.name, baz.c.qty], sel)
print ins.compile(dialect=oracle.dialect())

oracle

来源：https://stackoverflow.com/questions/45899044/sqlalchemy-with-clause-cte-with-insert-is-not-compiling-correctly-for-oracle

1条答案

按热度按时间

fxnxkyjh1#

您需要对语句进行自定义呈现，以下代码帮助我在另一种方言（我已经将其更改为oracle）上解决了这个问题。

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement, Executable

class InsertFromSelect(Executable, ClauseElement):
    def __init__(self, table, select):
        self.table = table
        self.select = select

@compiles(InsertFromSelect, "oracle")
def visit_insert_from_select(element, compiler, **kw):
    return "INSERT INTO %s (%s)" % (
        compiler.process(element.table, asfrom=True, **kw),
        compiler.process(element.select, **kw)
    )

#target table to insert
table = sqlalchemy.Table('foo' MetaData=())
#statement with a CTE / WITH clause
baz = select().select_from(sqlalchemy.text('select * from bar').columns().cte())

#print or run the new statement, correctly ordered
print(
    InsertFromSelect(table,baz)
)

实际上，您需要创建一个新元素InsertFromSelect，它分别保存查询和插入，然后创建一个编译访问器，它将它们呈现在正确的位置。

赞(0）回复(0）举报 2023-02-11

我来回答

带插入的SQLAlchemy With子句(CTE)未针对Oracle正确编译

1条答案

相关问题

热门标签

最新问答