SQLite create view syntax error in ArcPy Python script tool only

Question

I'm working with a SQLite database in a python script tool in ArcGIS Desktop 10.4. When I try to create a view on the database in SQLite Studio (using the sql_file pasted below) or from the command line (using the code and the sql_file below), the SQL statement works fine. When running from a Python script tool (using the code and the sql_file below), I get a syntax error ("near WITH: syntax error").

Here is the code that executes the sql statement:

import sqlite3
from sqlite3 import Error
import sys

with open(sql_file, 'r') as file:
        sql = file.read()
    try:
        c = conn.cursor()
        c.execute(sql)
    except Error as e:
        conn.close()
        AddMessage(e)
        sys.exit(0)

The contents of the sql_file are:

CREATE VIEW IF NOT EXISTS view_desktop_results AS
WITH precip_unstack AS (
        SELECT map_unit_id,
               (CASE WHEN precip = 'Arid' THEN proportion ELSE 0 END) AS arid,
               (CASE WHEN precip = 'Mesic' THEN proportion ELSE 0 END) AS mesic
          FROM project_precip
    ),
    mgmt_cat_unstack AS (
        SELECT map_unit_id,
               (CASE WHEN mgmt_cat = 'PHMA' THEN proportion ELSE 0 END) AS phma,
               (CASE WHEN mgmt_cat = 'GHMA' THEN proportion ELSE 0 END) AS ghma,
               (CASE WHEN mgmt_cat = 'OHMA' THEN proportion ELSE 0 END) AS ohma
          FROM project_mgmt_cats
    ),
    wmz_unstack AS (
        SELECT map_unit_id,
               (CASE WHEN wmz = 'MZ III' THEN proportion ELSE 0 END) AS mz3,
               (CASE WHEN wmz = 'MZ IV' THEN proportion ELSE 0 END) AS mz4,
               (CASE WHEN wmz = 'MZ V' THEN proportion ELSE 0 END) AS mz5
          FROM project_wmz
    )
    SELECT m.map_unit_id,
           m.map_unit_name,
           m.meadow,
           (CASE WHEN m.conifer_phase IS NULL THEN 'None' END) AS conifer_phase,
           m.indirect_benefits_area,
           m.map_unit_area,
           m.no_transects,
           m.spring_hsi,
           m.summer_hsi,
           m.winter_hsi,
           cl.ls_breed as current_breed,
           cl.ls_summer as current_summer,
           cl.ls_winter as current_winter,
           pl.ls_breed as projected_breed,
           pl.ls_summer as projected_summer,
           pl.ls_winter as projected_winter,
           mgmt.phma,
           mgmt.ghma,
           mgmt.ohma,
           wmz.mz3,
           wmz.mz4,
           wmz.mz5,
           precip.arid,
           precip.mesic
      FROM map_units AS m
           LEFT JOIN
           current_ls AS cl ON m.map_unit_id = cl.map_unit_id
           LEFT JOIN
           projected_ls AS pl ON m.map_unit_id = pl.map_unit_id
           LEFT JOIN
           mgmt_cat_unstack AS mgmt ON m.map_unit_id = mgmt.map_unit_id
           LEFT JOIN
           wmz_unstack AS wmz ON m.map_unit_id = wmz.map_unit_id
           LEFT JOIN
           precip_unstack AS precip ON m.map_unit_id = precip.map_unit_id;

Note that I'm running a bunch of other SQL statements to create the tables referenced in the SQL statement above. Creating tables and inserting rows all works fine. Only views seem to be affected. Again, this statement works outside of ArcGIS.

I'm thinking this may be a version issue between what is distributed with ArcGIS Desktop and the more recent SQLite distributions I'm using outside of the ArcGIS environment.

On ArcGIS Desktop 10.4 Basic, running Python version: 2.7.10 (default, May 23 2015, 09:40:32) [MSC v.1500 32 bit (Intel)] with Windows 8. sqlite3.version has given me 2.6.0 and sqlite3.sqlite_version has given 3.6.21

Answer 1

This is due to an older version of SQLite distributed with ArcDesktop 10.4 (SQLite version 3.6.21) where 'WITH' statements are not implemented (they were implemented in 3.8.3)

If updating SQLite is not an option (as it isn't for me), any SQL using a 'WITH' statement must be rewritten as a subquery. Subqueries may be used in the SELECT, FROM, WHERE, and JOIN clauses.

Here is how I re-wrote the SQL to accomplish this:

CREATE VIEW IF NOT EXISTS view_desktop_results AS
    SELECT m.map_unit_id,
           m.map_unit_name,
           m.meadow,
           (CASE WHEN m.conifer_phase IS NULL THEN 'None' END) AS conifer_phase,
           m.indirect_benefits_area,
           m.map_unit_area,
           m.no_transects,
           m.spring_hsi,
           m.summer_hsi,
           m.winter_hsi,
           cl.ls_breed AS current_breed,
           cl.ls_summer AS current_summer,
           cl.ls_winter AS current_winter,
           pl.ls_breed AS projected_breed,
           pl.ls_summer AS projected_summer,
           pl.ls_winter AS projected_winter,
           (
               SELECT (CASE WHEN mgmt_cat = 'PHMA' THEN proportion ELSE 0 END) 
                 FROM project_mgmt_cats
                WHERE project_mgmt_cats.map_unit_id = m.map_unit_id
           )
           AS phma,
           (
               SELECT (CASE WHEN mgmt_cat = 'GHMA' THEN proportion ELSE 0 END) 
                 FROM project_mgmt_cats
                WHERE project_mgmt_cats.map_unit_id = m.map_unit_id
           )
           AS ghma,
           (
               SELECT (CASE WHEN mgmt_cat = 'OHMA' THEN proportion ELSE 0 END) 
                 FROM project_mgmt_cats
                WHERE project_mgmt_cats.map_unit_id = m.map_unit_id
           )
           AS ohma,
           (
               SELECT (CASE WHEN wmz = 'MZ III' THEN proportion ELSE 0 END) 
                 FROM project_wmz
                WHERE project_wmz.map_unit_id = m.map_unit_id
           )
           AS mz3,
           (
               SELECT (CASE WHEN wmz = 'MZ IV' THEN proportion ELSE 0 END) 
                 FROM project_wmz
                WHERE project_wmz.map_unit_id = m.map_unit_id
           )
           AS mz4,
           (
               SELECT (CASE WHEN wmz = 'MZ V' THEN proportion ELSE 0 END) 
                 FROM project_wmz
                WHERE project_wmz.map_unit_id = m.map_unit_id
           )
           AS mz5,
           (
               SELECT (CASE WHEN precip = 'Arid' THEN proportion ELSE 0 END) 
                 FROM project_precip
                WHERE project_precip.map_unit_id = m.map_unit_id
           )
           AS arid,
           (
               SELECT (CASE WHEN precip = 'Mesic' THEN proportion ELSE 0 END) 
                 FROM project_precip
                WHERE project_precip.map_unit_id = m.map_unit_id
           )
           AS mesic
      FROM map_units AS m
           LEFT JOIN
           current_ls AS cl ON m.map_unit_id = cl.map_unit_id
           LEFT JOIN
           projected_ls AS pl ON m.map_unit_id = pl.map_unit_id;

Credit to user30184 for the tip.