如何解决“近‘与’：语法错误”

Question

我试图重现SQLAlchemy的文档页面上找到this查询递归查询CTE（瓶/ SQLAlchemy的）。设立在models.py的Part下课后，这是我有：

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' 

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

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',)] 

生成的查询（复制从错误信息粘贴）看起来是这样的：

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 

格式化（在不同的地方换行的可读性）：

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 

而且，作为比较，这里的SQLAlchemy的文档link纯PostgreSQL的查询：

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 

，我可以在Postgre查询之间看到的唯一差别（我认为应该工作）和我正在生成的一个是：

1. 所有额外的 “AS” 语句（SELECT parts.sub_part AS sub_part VS SELECT sub_part）
2. 不同的格式（生成的查询^ h换行符怪异点 - 例如，UNION ALL和SELECT）

但是，据我可以告诉之间没有断行，这些都不应该导致语法错误... 我也试过执行Postgre查询作为原始SQL（虽然SQLAlchemy使用sqlite3，显然，但仍然）：

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() 

但我仍然收到语法错误。

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"] 

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

最后，我试图写的SQLite查询和执行它：

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 

这也抛出一个语法错误。

在这一点上，我真的不知道该怎么办...好像连WITH查询有正确的语法仍然会抛出一个错误。我知道，递归CTE查询在SQLAlchemy的支持，根据文档，并根据此（_http：//*.com/a/24780445）回答这里。老实说，我不知道为什么所有这些疑问都被认为有错误的语法。我的python代码与文档中的示例几乎完全相同。

我需要安装的东西WITH RECURSIVE在SQLAlchemy的工作？我的语法实际上错了吗？在这里失去了很多，任何帮助表示赞赏。我希望我可以说我已经取得了一些进展，但我几乎不知道该怎么做，这是PostgreSQL vs SQLite的问题吗？有没有人有我想要做的，我可以看看的工作示例？）

Answer 1

问题是，您正在运行sqlite3的旧版本（如#sqlalchemy讨论）;你有3.8.2，并且在3.8.3中增加了CTE支持。