Question&Problem -> Python Tool : Cannot write Output data from tool

[kantawee]

Python Tool : I cannot write Output data from tool.

My Variable type is pandas data frames, but cannot write output? !!!

How to config this problem? please, help T^T

Error MSG
""

Error: Python (1): [NbConvertApp] Converting notebook C:\Users\User\AppData\Local\Temp\17cfdbd9-ec9b-4afd-b81e-053557665be6\1\workbook.ipynb to html
[NbConvertApp] Executing notebook with kernel: python3
2018-09-18 15:30:26.755508: I C:\tf_jenkins\workspace\rel-win\M\windows\PY\36\tensorflow\core\platform\cpu_feature_guard.cc:137] Your CPU supports instructions that this TensorFlow binary was not compiled to use: AVX AVX2
[NbConvertApp] ERROR | Error while converting 'C:\Users\User\AppData\Local\Temp\17cfdbd9-ec9b-4afd-b81e-053557665be6\1\workbook.ipynb'
Traceback (most recent call last):
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\nbconvertapp.py", line 393, in export_single_notebook
output, resources = self.exporter.from_filename(notebook_filename, resources=resources)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 174, in from_filename
return self.from_file(f, resources=resources, **kw)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 192, in from_file
return self.from_notebook_node(nbformat.read(file_stream, as_version=4), resources=resources, **kw)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\html.py", line 85, in from_notebook_node
return super(HTMLExporter, self).from_notebook_node(nb, resources, **kw)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\templateexporter.py", line 280, in from_notebook_node
nb_copy, resources = super(TemplateExporter, self).from_notebook_node(nb, resources, **kw)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 134, in from_notebook_node
nb_copy, resources = self._preprocess(nb_copy, resources)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 311, in _preprocess
nbc, resc = preprocessor(nbc, resc)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\base.py", line 47, in __call__
return self.preprocess(nb, resources)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 262, in preprocess
nb, resources = super(ExecutePreprocessor, self).preprocess(nb, resources)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\base.py", line 69, in preprocess
nb.cells[index], resources = self.preprocess_cell(cell, resources, index)
File "d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 286, in preprocess_cell
raise CellExecutionError.from_cell_and_msg(cell, out)
nbconvert.preprocessors.execute.CellExecutionError: An error occurred while executing the following cell:
------------------
Alteryx.write(tokenResult2,1)
------------------

---------------------------------------------------------------------------
InterfaceError Traceback (most recent call last)
<ipython-input-4-49de8691a69c> in <module>()
----> 1 Alteryx.write(tokenResult2,1)

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\export.py in write(pandas_df, outgoing_connection_number)
21 When running the workflow in Alteryx, this function will convert a pandas data frame to an Alteryx data stream and pass it out through one of the tool's five output anchors. When called from the Jupyter notebook interactively, it will display a preview of the pandas dataframe.
22 '''
---> 23 return __CachedData__().write(pandas_df, outgoing_connection_number)
24 
25 def getIncomingConnectionNames():

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\CachedData.py in write(self, pandas_df, outgoing_connection_number)
330 try:
331 # get the data from the sql db (if only one table exists, no need to specify the table name)
--> 332 data = db.writeData(pandas_df, 'data')
333 # print success message
334 print(''.join(['SUCCESS: ', msg_action]))

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\CachedData.py in writeData(self, pandas_df, table)
170 print('Attempting to write data to table "{}"'.format(table))
171 try:
--> 172 pandas_df.to_sql(table, self.connection, if_exists='replace', index=False)
173 if self.debug:
174 print(fileErrorMsg(

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype)
2128 sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
2129 index=index, index_label=index_label, chunksize=chunksize,
-> 2130 dtype=dtype)
2131 
2132 def to_pickle(self, path, compression='infer',

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype)
448 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
449 index_label=index_label, schema=schema,
--> 450 chunksize=chunksize, dtype=dtype)
451 
452

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1479 dtype=dtype)
1480 table.create()
-> 1481 table.insert(chunksize)
1482 
1483 def has_table(self, name, schema=None):

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\pandas\io\sql.py in insert(self, chunksize)
639 
640 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
--> 641 self._execute_insert(conn, keys, chunk_iter)
642 
643 def _query_iterator(self, result, chunksize, columns, coerce_float=True,

d:\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\pandas\io\sql.py in _execute_insert(self, conn, keys, data_iter)
1268 def _execute_insert(self, conn, keys, data_iter):
1269 data_list = list(data_iter)
-> 1270 conn.executemany(self.insert_statement(), data_list)
1271 
1272 def _create_table_setup(self):

InterfaceError: Error binding parameter 0 - probably unsupported type.
InterfaceError: Error binding parameter 0 - probably unsupported type.

 ""

[PaulN]

Hi @kantawee,

Thank you for the post.

Could you please share your workflow ?

Kind regards,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[kantawee]

@PaulN

This my alteryx workflow . 

Thanks PaulN

[PaulN]

Hi @kantawee,

Thank you for the workflow. Tensorflow installation took me some time but, ultimately it worked and I am able to recreate this behaviour.

Could you please email your issue to support@alteryx.com as I would like to move forward with the investigations offline ?

Many thanks,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[PaulN]

Hi @kantawee,

As this particular type of DataFrame with embedded list doesn't not seem supported by current version of Python tool, I would suggest the following workaround:

tokenResult2 = pd.DataFrame(tokenResult2[tokenResult2.columns[0]].values.tolist(), index=tokenResult2.index)
Alteryx.write(tokenResult2,1)

Example:

The idea is to split the cell into multiple columns.

Thanks,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[PaulN]

Hi @kantawee,

Just to clarify the issue : even if  tokenResult2 is a DataFrame, it contains some lists. List is  not a primitive type in the Alteryx world.

Kind regards,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[kantawee]

@PaulN 

Nested lists --> DataFrame ::

Nested lists in python tool, even if the result is a DataFrame. not supported to write output from python tool.

OK,  Good for discussion.

Thanks sir.  

[PaulN · Accepted solution]

Hey @kantawee,

Just to clarify why this issue is happening and why it is/was expected:

Contrary to Jupyter tool alone, Python tool exist in the context of Designer. So what goes in and out of the tool has to be understood by Alteryx Engine.

If we consider the 2 following examples:

import pandas as pd

df1 = pd.DataFrame({"test1":[1,2,3]})

df2 =pd.DataFrame({"test":[[1,2,3],[2,3,4],[5,6,7]]})

for df1, data are integers, which is a standard data type for Alteryx, so result will show 1 column with 3 values

test1
0	1
1	2
2	3

for df2, data are 3 lists. So, when engine will try to parse each row, it will not be able to interpret them, because list don't exist out of the Python tool.

test
0	[1, 2, 3]
1	[2, 3, 4]
2	[5, 6, 7]

I hope it clarifies.

Thanks,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[Vardane]

Hi Paul,

re Python Tool : Cannot write Output data from tool,

I have a similar issue as @kantawee.

It does not seem the final Panda Data Frame contains lists this time though.

Still, the Frame seems to be returned properly within the tool configuration, but cannot be output to Workflow.

My workaround is to write to csv file for now, and read back. But is there a way to get an uninterrupted workflow?

Thanks,

Yann

Error Message:

Python (1) [NbConvertApp] Converting notebook C:\Users\YFOUCA~1\AppData\Local\Temp\9dd77681-36e1-440a-8853-ad0f49e99d29\1\workbook.ipynb to html ¶[NbConvertApp] Executing notebook with kernel: python3 ¶[NbConvertApp] ERROR | Timeout waiting for execute reply (30s). ¶Traceback (most recent call last): ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 324, in _wait_for_reply ¶ msg = self.kc.shell_channel.get_msg(timeout=timeout) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\jupyter_client\blocking\channels.py", line 57, in get_msg ¶ raise Empty ¶queue.Empty ¶ ¶During handling of the above exception, another exception occurred: ¶ ¶Traceback (most recent call last): ¶ File "C:\Users\yfoucault002\AppData\Local\Alteryx\bin\Miniconda3\lib\runpy.py", line 193, in _run_module_as_main ¶ "__main__", mod_spec) ¶ File "C:\Users\yfoucault002\AppData\Local\Alteryx\bin\Miniconda3\lib\runpy.py", line 85, in _run_code ¶ exec(code, run_globals) ¶ File "C:\Users\yfoucault002\AppData\Local\Alteryx\bin\Miniconda3\PythonTool_Venv\Scripts\jupyter-nbconvert.EXE\__main__.py", line 9, in <module> ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\jupyter_core\application.py", line 266, in launch_instance ¶ return super(JupyterApp, cls).launch_instance(argv=argv, **kwargs) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\traitlets\config\application.py", line 658, in launch_instance ¶ app.start() ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\nbconvertapp.py", line 325, in start ¶ self.convert_notebooks() ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\nbconvertapp.py", line 493, in convert_notebooks ¶ self.convert_single_notebook(notebook_filename) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\nbconvertapp.py", line 464, in convert_single_notebook ¶ output, resources = self.export_single_notebook(notebook_filename, resources, input_buffer=input_buffer) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\nbconvertapp.py", line 393, in export_single_notebook ¶ output, resources = self.exporter.from_filename(notebook_filename, resources=resources) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 174, in from_filename ¶ return self.from_file(f, resources=resources, **kw) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 192, in from_file ¶ return self.from_notebook_node(nbformat.read(file_stream, as_version=4), resources=resources, **kw) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\html.py", line 85, in from_notebook_node ¶ return super(HTMLExporter, self).from_notebook_node(nb, resources, **kw) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\templateexporter.py", line 280, in from_notebook_node ¶ nb_copy, resources = super(TemplateExporter, self).from_notebook_node(nb, resources, **kw) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 134, in from_notebook_node ¶ nb_copy, resources = self._preprocess(nb_copy, resources) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\exporters\exporter.py", line 311, in _preprocess ¶ nbc, resc = preprocessor(nbc, resc) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\base.py", line 47, in __call__ ¶ return self.preprocess(nb, resources) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 262, in preprocess ¶ nb, resources = super(ExecutePreprocessor, self).preprocess(nb, resources) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\base.py", line 69, in preprocess ¶ nb.cells[index], resources = self.preprocess_cell(cell, resources, index) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 280, in preprocess_cell ¶ reply, outputs = self.run_cell(cell, cell_index) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 348, in run_cell ¶ exec_reply = self._wait_for_reply(msg_id, cell) ¶ File "c:\users\yfoucault002\appdata\local\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\nbconvert\preprocessors\execute.py", line 337, in _wait_for_reply ¶ raise exception("Cell execution timed out") ¶TimeoutError: Cell execution timed out ¶

code in Python Tool (in case it disappears from Alteryx file):

from ayx import Alteryx

simulParameters = Alteryx.read("#1")

import numpy as np
import pandas as pd

nSim = int(simulParameters.nSimul_.values)
nDay = int(simulParameters.nDays_.values)

U_0 = float(simulParameters.U_0_.values)
E_0 = float(simulParameters.E_0_.values)

alpha_U =float(simulParameters.alpha_U_.values)
beta_U = float(simulParameters.beta_U_.values)
sigma_U = float(simulParameters.sigma_U_.values)

alpha_E = float(simulParameters.alpha_E_.values)
beta_E = float(simulParameters.beta_E_.values)
sigma_E = float(simulParameters.sigma_E_.values)

rho = float(simulParameters.rho_.values)

#projectedDays = Base_Data.Projected_Day.values

phi_U = np.exp( -alpha_U / 365 )
phi_E = np.exp( -alpha_E / 365 )

#nSimul = 10000
#
#nDays = 365

#Generate random variables
randVarU = np.random.normal(0, 1, (nSim, nDay))
randVarE = np.random.normal(0, 1, (nSim, nDay))

#Calculate FX rates, assumed to follow O-U dynamics

USD_Rate = np.zeros( (nSim, nDay+1) )
EUR_Rate = np.zeros( (nSim, nDay+1) )

USD_Rate[:,0] = U_0
EUR_Rate[:,0] = E_0

#for k :
for k in range(0, nSim):
for j in range(1, nDay+1):
USD_Rate[k, j] = beta_U + ( USD_Rate[k, j-1] - beta_U
) * phi_U + randVarU[k,j-1] * sigma_U * np.sqrt( (1 - phi_U * phi_U) / ( 2 * alpha_U)
)
EUR_Rate[k, j] = beta_E + ( EUR_Rate[k, j-1] - beta_E
) * phi_E + sigma_E * np.sqrt( (1 - phi_E * phi_E) / ( 2 * alpha_E)
) * ( rho * randVarU[k,j-1] + np.sqrt( 1 - rho * rho ) * randVarE[k,j-1] )

#reshape sim by day to column:
USD_Rate_As_Col = np.reshape( USD_Rate, (nSim * (nDay+1), 1) )
EUR_Rate_As_Col = np.reshape( EUR_Rate, (nSim * (nDay+1), 1) )

# remove first rate, which was the rate of previous year last day:
USD_Rate_As_Trimmed_Col = np.delete(USD_Rate_As_Col, list(range(0, USD_Rate_As_Col.shape[0], 366)), axis=0)
EUR_Rate_As_Trimmed_Col = np.delete(EUR_Rate_As_Col, list(range(0, EUR_Rate_As_Col.shape[0], 366)), axis=0)

USD_Rate_As_Col_2dim = USD_Rate_As_Trimmed_Col[:, np.newaxis]
EUR_Rate_As_Col_2dim = USD_Rate_As_Trimmed_Col[:, np.newaxis]

Scenario_Array = np.zeros( (nSim * nDay, 1) )

# Use a mathematical sequence to assign proper scenario index (ie simulation index) to each row of FX rates:
for a in range(0, nSim * nDay ):
Scenario_Array[a] = np.ceil( (a+1) / nDay )


Simulated_Rates = np.hstack( (USD_Rate_As_Trimmed_Col, EUR_Rate_As_Trimmed_Col, Scenario_Array) )

Simulated_Rates_As_Pandas_Frame = pd.DataFrame(Simulated_Rates)

#print(Simulated_Rates_As_Pandas_Frame )

Alteryx.write(Simulated_Rates_As_Pandas_Frame, 1)

# Workaround:

Simulated_Rates_As_Pandas_Frame.to_csv(".\\Simul_Output.csv")

[PaulN]

Hey @Vardane,

Thank you for posting!

From the last line of your log, error is caused by timeout default value for Jupyter (30 seconds).

As a workaround, please use one of the solutions from following articles:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Python-Tool-Timeout-waiting-for-execut...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Python-SDK-timeout-error-cell-executio...

Kind regards,

Paul Noirel

Sr Customer Support Engineer, Alteryx