如何修正“near”与“：语法错误”在递归CTE查询（flask / sqlalchemy） [英] How to fix "near 'with': syntax error" in recursive CTE query (flask/sqlalchemy)

问题描述

我试图复制这个查询在SqlAlchemy文档页面找到。在 models.py 中设置 Part 类后，这就是我所拥有的：

  from app import app，db，models 
 from models import * 
 
 @ app.route（'/'） 
 def test（）：
 included_pa​​rts = db.session.query（
 Part.sub_part，
 Part.part，
 Part.quantity）.\ 
 filter（Part.part ==our part）。\ 
 cte（name =included_pa​​rts，recursive = True）
 
 incl_alias = db.aliased（included_pa​​rts，name =pr）
 parts_alias = db.aliased（Part，name =p）
 included_pa​​rts = included_pa​​rts.union_all（
 db.session.query（
 parts_alias.sub_part ，
 parts_alias.part，
 parts_alias.quantity）.\ 
 filter（parts_alias.part == incl_alias.c.sub_part）
）
 
q = db.session.query（
 included_pa​​r ts.csub_part，
 db.func.sum（included_pa​​rts.c.quantity）。 
 label（'total_quantity'）
）.\ 
 group_by（included_pa​​rts.c.sub_part）.all（）
 
 return'test complete'
  

但是，这给出了一个错误：



 （sqlite3.OperationalError）接近WITH：语法错误[SQL：u'WITH RECURSIVE included_pa​​rts（sub_part，part，quantity）AS \\\
（SELECT parts.sub_part AS sub_part，parts.part AS零件数量零件数量\自由零件\所有零件。部件=？UNION ALL选择p.sub_part AS p_sub_part，p.part AS p_part，p.quantity AS p_quantity \\\
FROM零件AS p，included_pa​​rts AS pr \ nWHERE p.part = pr.sub_part）\ n SELECT included_pa​​rts.sub_part AS included_pa​​rts_sub_part，sum（included_pa​​rts.quantity）AS total_quantity \\\
FROM included_pa​​rts GROUP BY included_pa​​rts.sub_part'] [parameters：（'our part'，） ] 
  

生成的查询（从错误信息中复制）如下所示：

$ b $ pre $ WITH RECURSIVE included_pa​​rts（sub_part，part，quantity）AS \\\
（SELECT parts.sub_part AS sub_part， parts.part AS part，parts.quantity AS quantity \\\
FROM parts \\\
WHERE parts.part =？ UNION ALL SELECT p.sub_part AS p_sub_part，p.part AS p_part，p.quantity AS p_quantity \\\
FROM parts AS p，included_pa​​rts AS pr \ nWHERE p.part = pr.sub_part）\\\
SELECT selected_pa​​rts.sub_part AS included_pa​​rts_sub_part ，sum（included_pa​​rts.quantity）AS total_quantity \\\
FROM included_pa​​rts GROUP BY included_pa​​rts.sub_part


格式化（with换行符在不同的地方换行）：
$ b $ pre $ WITH RECURSIVE included_pa​​rts（sub_part，part，quantity）AS（
SELECT parts.sub_part AS sub_part，parts.part AS part，parts.quantity AS quantity
FROM parts
WHERE parts.part =？
UNION ALL
SELECT p.sub_part AS p_sub_part， p.part AS p_part，p.quantity AS p_quantity
FROM parts AS p，included_pa​​rts AS pr
WHERE p.part = pr.sub_part
）
SELECT included_pa​​rts.sub_part AS included_pa​​rts_sub_part ，sum（included_pa​​rts.quantity）AS total_quantity
FROM included_pa​​r ts
GROUP BY included_pa​​rts.sub_part


为了比较，这里是纯PostgreSQL查询sqlalchemy文档链接到：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' b UNION ALL
SELECT p.sub_part，p.part，p.quantity
FROM included_pa​​rts pr，parts p
WHERE p.part = pr.sub_part
）
SELECT sub_part，SUM（quantity）as total_quantity
FROM included_pa​​rts
GROUP BY sub_part

Postgre查询（我假设应该工作）和我正在生成的查询之间唯一的区别是：
$ b $ ol

所有额外的AS语句（ SELECT parts.sub_part AS sub_part vs SELECT sub_part ）

不同的格式（生成的查询在奇怪的地方有换行符 - 例如， UNION ALL 和 SELECT
$ /

$ /我也尝试执行Postgre查询作为原始SQL（尽管SQLAlchemy使用sqlite3，显然，但仍然）：

$ $ p $ code查询= db.engine.execute（\ $ b $'''WITH RECURSIVE included_pa​​rts（sub_part，part，quantity）AS（
SELECT sub_part，part，quantity FROM parts WHERE part ='our_product'
UNION ALL
SELECT p.sub_part，p.part，p.quantity
FROM included_pa​​rts pr，parts p
WHERE p.part = pr.sub_part
）
SELECT sub_part ，SUM（quantity）as total_quantity
FROM included_pa​​rts
GROUP BY sub_part'''）。all（）


但我仍然得到一个语法错误。

pre $ OperationalError：（sqlite3.OperationalError）nea rWITH：语法错误[SQL：WITH RECURSIVE included_pa​​rts（sub_part，part，quantity）AS（\ nSELECT sub_part，part，quantity FROM parts WHERE part ='our_product'\\\
UNION ALL\\\
SELECT p.sub_part， p.part，p.quantity \\\
FROM included_pa​​rts pr，parts p \ nWHERE p.part = pr.sub_part\\\
）\ nSELECT sub_part，SUM（quantity）as total_quantity \\\
FROM included_pa​​rts\\\
GROUP BY sub_part ]


我也尝试重新格式化生成的查询，并将其作为原始SQL执行，结果类似。

$ b

最后，我尝试在SQLite中编写查询并执行它：

  WITH RECURSIVE included_pa​​rts（sub_part，part，quantity）AS（
 SELECT sub_part，part，quantity FROM parts WHERE part =our_product
 UNION ALL 
 SELECT parts.sub_part，parts .part，parts.quantity FROM parts，included_pa​​rts WHERE parts.part = included_pa​​rts.sub_part 
）
 SELECT sub_part，SUM（quantity）AS total_quantity $ b $ FROM FROM included_pa​​rts 
 GROUP BY sub_part 
  

这也会引发语法错误。

在这一点上，我真的不知道该怎么办...它似乎甚至是一个 WITH 查询与正确的语法仍然会引发错误。我知道在Sqlalchemy支持递归CTE查询，根据文档和根据这个（_http：//stackoverflow.com/a/24780445）答案在这里。我真的不知道为什么所有这些查询被认为是不好的语法。我的python代码和文档中的例子几乎一样。

我需要为安装一些东西和RECURSIVE 在SQLAlchemy中工作？我的语法实际上错了吗？

（编辑把这个回到头版，我希望我能说我已经取得了一些进展，但是我这是一个PostgreSQL vs SQLite的问题吗？有没有人有我想要做的工作的例子，我可以看看？）

问题是，你正在运行一个旧版本的sqlite3你有3.8.2，并且在3.8.3中增加了CTE支持。

I'm trying to reproduce this query found on the SqlAlchemy docs page. After setting up the Part class in models.py, this is what I have:

from app import app, db, models
from models import *

@app.route('/')
def test():
    included_parts = db.session.query(
                    Part.sub_part,
                    Part.part,
                    Part.quantity).\
                        filter(Part.part=="our part").\
                        cte(name="included_parts", recursive=True)

    incl_alias = db.aliased(included_parts, name="pr")
    parts_alias = db.aliased(Part, name="p")
    included_parts = included_parts.union_all(
        db.session.query(
            parts_alias.sub_part,
            parts_alias.part,
            parts_alias.quantity).\
                filter(parts_alias.part==incl_alias.c.sub_part)
        )

    q = db.session.query(
            included_parts.c.sub_part,
            db.func.sum(included_parts.c.quantity).
                label('total_quantity')
        ).\
        group_by(included_parts.c.sub_part).all()

    return 'test complete'

But this gives an error:

OperationalError: (sqlite3.OperationalError) near "WITH": syntax error [SQL: u'WITH RECURSIVE included_parts(sub_part, part, quantity) AS \n(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity \nFROM parts \nWHERE parts.part = ? UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM parts AS p, included_parts AS pr \nWHERE p.part = pr.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part'] [parameters: ('our part',)]

The generated query (copy pasted from the error message) looks like this:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS \n(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity \nFROM parts \nWHERE parts.part = ? UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM parts AS p, included_parts AS pr \nWHERE p.part = pr.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part

Formatted (with line breaks in different places for readability):

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity 
    FROM parts
    WHERE parts.part = ? 
    UNION ALL 
    SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity
    FROM parts AS p, included_parts AS pr 
    WHERE p.part = pr.sub_part
)
SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity 
FROM included_parts 
GROUP BY included_parts.sub_part

And, for comparison, here's the pure PostgreSQL query that the sqlalchemy docs link to:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

The only differences that I can see between the Postgre query (which I assume is supposed to work) and the one that I'm generating are:

1. All the extra "AS" statements (SELECT parts.sub_part AS sub_part vs SELECT sub_part)
2. Different formatting (generated query has line breaks in weird spots - for example, no line break between UNION ALL and SELECT)

But, as far as I can tell, neither of those should cause a syntax error... I've also tried executing the Postgre query as raw SQL (although SQLAlchemy uses sqlite3, apparently, but still):

    query = db.engine.execute(\
    '''WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part''').all()

But I still get a syntax error.

OperationalError: (sqlite3.OperationalError) near "WITH": syntax error [SQL: "WITH RECURSIVE included_parts(sub_part, part, quantity) AS (\nSELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'\nUNION ALL\nSELECT p.sub_part, p.part, p.quantity\nFROM included_parts pr, parts p\nWHERE p.part = pr.sub_part\n)\nSELECT sub_part, SUM(quantity) as total_quantity\nFROM included_parts\nGROUP BY sub_part"]

I also tried reformatting the generated query and executing it as raw SQL with similar results.

And, finally, I tried writing a query in SQLite and executing it:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part="our_product"
    UNION ALL
    SELECT parts.sub_part, parts.part, parts.quantity FROM parts, included_parts WHERE parts.part=included_parts.sub_part
)
SELECT sub_part, SUM(quantity) AS total_quantity
FROM included_parts
GROUP BY sub_part

This also throws a syntax error.

At this point, I'm not really sure what to do... it seems like even a WITH query with correct syntax will still throw an error. I know that recursive CTE queries are supported in Sqlalchemy, according to the docs and according to this (_http://stackoverflow.com/a/24780445) answer here. I honestly have no idea why all of these queries are considered to have bad syntax. My python code is practically identical to the example in the docs.

Do I need to install something for WITH RECURSIVE to work in SQLAlchemy? Is my syntax actually wrong? Pretty much lost here, any help is appreciated.

(Editing to bump this back to the front page. I wish I could say I've made some progress, but I have literally no idea what to do at this point. Is this a PostgreSQL vs SQLite problem? Does anyone have a working example of what I'm trying to do that I can look at?)

解决方案

The problem is that you're running an older version of sqlite3 (as discussed on #sqlalchemy); you have 3.8.2 and CTE support was added in 3.8.3.

这篇关于如何修正“near”与“：语法错误”在递归CTE查询（flask / sqlalchemy）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！