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