How to fix "near 'with': syntax error" in recursive CTE query (flask/sqlalchemy)

Question

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

Answer 1

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.