Azure ML in “Execute Python Script” module :Common table expressions is not supported in sqlite3

Question

I ran into this issue yesterday while trying to use the same SQLite script I used in the "Apply SQL Transformation" module in Azure ML, in SQLite over Python module in Azure ML:

with tbl as (select * from t1)

select * from tbl

Here is the error I got:

[Critical]     Error: Error 0085: The following error occurred during script evaluation, please view the output log for more information:

---------- Start of error message from Python interpreter ----------

  File "C:\server\invokepy.py", line 169, in batch

data:text/plain,Caught exception while executing function: Traceback (most recent call last):

    odfs = mod.azureml_main(*idfs)

  File "C:\pyhome\lib\site-packages\pandas\io\sql.py", line 388, in read_sql

  File "C:\temp\azuremod.py", line 193, in azureml_main

    results = pd.read_sql(query,con)

    coerce_float=coerce_float, parse_dates=parse_dates)

  File "C:\pyhome\lib\site-packages\pandas\io\sql.py", line 1017, in execute

  File "C:\pyhome\lib\site-packages\pandas\io\sql.py", line 1022, in read_sql

    cursor = self.execute(*args)

    raise_with_traceback(ex)

  File "C:\pyhome\lib\site-packages\pandas\io\sql.py", line 1006, in execute

---------- End of error message from Python  interpreter  ----------

    cur.execute(*args)

DatabaseError: Execution failed on sql:  with tbl as (select * from t1)

                    select * from tbl

and the Python code:

def azureml_main(dataframe1 = None, dataframe2 = None):

    import pandas as pd

    import sqlite3 as lite

    import sys

    con = lite.connect('data1.db')

    con.text_factory = str

    with con:

        cur = con.cursor()

        if (dataframe1 is not None):

            cur.execute("DROP TABLE IF EXISTS t1")

            dataframe1.to_sql('t1',con)

        query = '''with tbl as (select * from t1)

                    select * from tbl'''                      

        results = pd.read_sql(query,con)    

    return results,

When replacing the query with:

select * from t1

It worked as expected. As you probably know, Common table expressions is a key feature in SQLite, the ability to run recursive code is a "must have" in any functional language such as SQLite.

I also tried to run my Python script in Jupyter Notebook in Azure, that also worked as expected.

Is it possible we have a different configuration for SQLite in the Python module than in Jupyter Notebook and in "Apply SQL Transformation" module?

Answer 1

According to the pandas doc, tt seems that Pandas not support the usage for this SQL syntax.
Base on my experience and according to your SQL, I tried to do the SQL select * from (select * from t1) as tbl instead of your SQL that work for Pandas.

Take reference from this: http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries.