community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

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

Atom

Thanks, Paul!

 

The below you pointed to worked fine:

 

if not exist %userprofile%\.jupyter mkdir %userprofile%\.jupyter

 

echo c.ExecutePreprocessor.timeout = None > %userprofile%\.jupyter\jupyter_nbconvert_config.py

 

Regards,

Yann

Atom

Hi, Paul - I'm having the same problem and it doesn't seem to be related to either of the problems above. I'm trying to use the BLS API, so my python tool isn't connected to anything as it's making an API call to retrieve data. It's the only thing in my workflow currently. Here's my script:

 

 
from ayx import Package
from ayx import Alteryx
import requests
import pandas as pd
import bls
 
bls_API_key = '14f4fc6cad9d4317a5e21842a53c2604'
sm_employees_thousands = pd.DataFrame(bls.get_series('SMS01000003000000001', 2014, 2018, bls_API_key))
sm_employees_thousands.columns
sm_employees_thousands.reset_index(inplace=True)
sm_employees_thousands.head()

 
Out[5]:
  date SMS0100000300000000101234
2014-01251.1
2014-02250.9
2014-03251.6
2014-04251.8
2014-05252.6
In [6]:
 
Alteryx.write(sm_employees_thousands,1)
 
Error: unable to write output table "data" (C:\Users\nspare\AppData\Local\Temp\0c571dd40d9cd7d1d88a1a9c97070ebd\1\output_1.sqlite)
ERROR: writing outgoing connection data 1
 
---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-6-51e8a29ee518> in <module>
----> 1 Alteryx.write(sm_employees_thousands,1)

c:\program files\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\export.py in write(pandas_df, outgoing_connection_number, columns, debug, **kwargs)
     78     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.
     79     '''
---> 80     return __CachedData__(debug=debug).write(pandas_df, outgoing_connection_number, columns=columns, **kwargs)
     81 
     82 def getIncomingConnectionNames(debug=None, **kwargs):

c:\program files\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\CachedData.py in write(self, pandas_df, outgoing_connection_number, columns)
   1354             try:
   1355                 # get the data from the sql db (if only one table exists, no need to specify the table name)
-> 1356                 data = db.writeData(pandas_df_out, 'data', dtype=dtypes)
   1357                 # print success message
   1358                 print(''.join(['SUCCESS: ', msg_action]))

c:\program files\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\CachedData.py in writeData(self, pandas_df, table, dtype)
    957             print('Attempting to write data to table "{}"'.format(table))
    958         try:
--> 959             pandas_df.to_sql(table, self.connection, if_exists='replace', index=False, dtype=dtype)
    960             if self.debug:
    961                 print(fileErrorMsg(

c:\program files\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',

c:\program files\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 

c:\program files\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):

c:\program files\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,

c:\program files\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.

 
 
Moderator
Moderator

Hi @nafferly,

 

Thank you for posting!

 

Looking at your example, I believe that error is caused by the fact that Period() (datatype of column date) is not recognised by Alteryx Engine.

 

Example:

If we check the type of the first row of date:

 

 

type(sm_employees_thousands.iloc[0][0])

> pandas._libs.tslibs.period.Period

 

 

As a workaround, you could cast date to str, or any relevant type.

 

Example:

 

 

sm_employees_thousands = sm_employees_thousands.astype({"date":str})
Alteryx.write(sm_employees_thousands,1)

 

 

Thanks,

 

Paul Noirel

Sr Customer Support, Alteryx

 

 

Atom

Works perfectly now - thank you so much!

Highlighted
Asteroid

Old question but I ran into a similar problem and solved it by changing all datatypes in the dataframe to strings

 

df = df.applymap(str)

 

Labels