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

I reproduced your issue and reviewed the SQL Queries doc of pandas.io.sql at http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries. I tried to use read_sql_query to solve it, but failed.

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.

Hope it helps. Best Regards.