Python Tool Won't Output JSON (Interface Error)

[ajcooper35]

When attempting to output a POST call using the PYthon tool, I get an InterfaceError every time. I am able to print the results, but it will not write the results to output them back in to the workflow .

My code is as follows:

# List all non-standard packages to be imported by your
# script here (only missing packages will be installed)
from ayx import Package
from ayx import Alteryx
import json
import requests
import pandas as pd

requestMethod = "POST"

clientCrt = "C:/Python37/--REDACTED--   .cer"
clientKey = "C:/Python37/--REDACTED--   .key"
url = "https://--REDACTED--   "

payload = {}

headers = {
'x-auth-scheme': 'api',
'x-auth-apikey': '--REDACTED--   ',
'Content-Type': 'application/json'
}

r = requests.post(url, data=json.dumps(payload), headers=headers, cert=(clientCrt, clientKey))

output = r.json()
print(output)

df = pd.DataFrame(data=output)

Alteryx.write(df, 1)

The error I get is:

InterfaceError                            Traceback (most recent call last)
<ipython-input-9-c78192272a63> in <module>
     31 df = pd.DataFrame(data=output)
     32 
---> 33 Alteryx.write(df, 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)
     84     """
     85     return __CachedData__(debug=debug).write(
---> 86         pandas_df, outgoing_connection_number, columns=columns, **kwargs
     87     )
     88 

c:\program files\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\CachedData.py in write(self, pandas_df, outgoing_connection_number, columns, output_filepath)
    571             try:
    572                 # get the data from the sql db (if only one table exists, no need to specify the table name)
--> 573                 data = db.writeData(pandas_df_out, "data", metadata=write_metadata)
    574                 # print success message
    575                 if outgoing_connection_number is not None:

c:\program files\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\ayx\Datafiles.py in writeData(self, pandas_df, table, metadata)
    776                     if_exists="replace",
    777                     index=False,
--> 778                     dtype=dtypes,
    779                 )
    780 

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, method)
   2529         sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
   2530                    index=index, index_label=index_label, chunksize=chunksize,
-> 2531                    dtype=dtype, method=method)   2532 
   2533     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, method)
    458     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    459                       index_label=index_label, schema=schema,
--> 460                       chunksize=chunksize, dtype=dtype, method=method)    461 
    462 

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, method)
   1545                             dtype=dtype)
   1546         table.create()
-> 1547         table.insert(chunksize, method)
   1548 
   1549     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, method)
    684 
    685                 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
--> 686                 exec_insert(conn, keys, chunk_iter)
    687 
    688     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)
   1317     def _execute_insert(self, conn, keys, data_iter):
   1318         data_list = list(data_iter)
-> 1319         conn.executemany(self.insert_statement(), data_list)
   1320 
   1321     def _create_table_setup(self):

InterfaceError: Error binding parameter 0 - probably unsupported type.

The strangest thing is it was working for a moment, and then stopped after maybe 2-3 runs. 

Any ideas? I have tried everything I can think of. 

[joshuaburkhow]

@SydneyF  @NeilR is this something you guys have seen? Seems like something going on with the CachedData.py? 

[SydneyF]

Hi @ajcooper35,

Can you try checking the data type of each of the columns in the data frame what you are attempting to write back out to Alteryx? It looks like something you are trying to write out is an unsupported data type for Alteryx - like a list or a dictionary. Although Pandas will allow you to store these data types in the cells of a data frame, Alteryx doesn't recognize these data types, and can't translate them to a supported data type, so the write out fails. If you cast your outputs to a supported data types (e.g., a string) you should be able to write the pandas data frame back out to Alteryx. 

I believe this thread addresses the same behavior: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Question-amp-Problem-gt-Python-Tool-Ca...

Please let me know if this is not the issue you are encountering or if you get stuck!

Sydney

[ajcooper35]

It is only one column, but it is showing as a dictionary. When I convert it to a string, I get a ValueError here:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-48-ef9aab546e3e> in <module>
     32 
     33 
---> 34 df = pd.DataFrame(data=rJson)
     35 
     36 Alteryx.write(df, 1)

c:\program files\alteryx\bin\miniconda3\pythontool_venv\lib\site-packages\pandas\core\frame.py in __init__(self, data, index, columns, dtype, copy)
    466                                    dtype=values.dtype, copy=False)
    467             else:
--> 468                 raise ValueError('DataFrame constructor not properly called!')
    469 
    470         NDFrame.__init__(self, mgr, fastpath=True)

ValueError: DataFrame constructor not properly called!

The output is below of the String is below. 

<class 'str'>
{"faces": [{"alert": false, "createdAtMillis": 1576754473873, "faceId": "--REDACTED--", "labels": [], "name": "Sonya", "orgUuid": "--REDACTED--", "thumbnailS3Key": "faces/--REDACTED--/frame_22_896_385_120_121.jpg", "trust": true}, {"alert": false, "createdAtMillis": 1576866862110, "faceId": "--REDACTED--", "labels": [], ***** Skipping middle where it repeats ********** "name": "--REDACTED--", "orgUuid": "--REDACTED--", "thumbnailS3Key": "faces/--REDACTED--/frame_16_1283_57_142_143.jpg", "trust": true}]}

What is confusing me the most is that this worked exactly how I needed it to at one point, and now I can't for the life of me get it back.

[SydneyF · Accepted solution]

@ajcooper35,

Have you tried converting your data to a pandas data frame first, and then converting the data type of the column in your pandas data frame to a string? When your data is in a dictionary format, pandas knows how to convert your data to a data frame. With your data set as a string, pandas is not sure how to create a data frame with your data, which is why you are getting the ValueError. 

You try testing the data type of your column of data after it has been put into a data frame with the pandas method dtypes. You can try using the method astype to cast the column to a string - this StackOverflow post might be helpful to you for that. I also found this StackOverflow thread that references a pandas function called json_normalize that might be helpful to you. 

Thanks,

Sydney

[ajcooper35]

Sydney, 

Converting the data type to a string after making it a dataframe worked. Final code below.

Thanks for all of the help!

[ricslator]

ValueError: DataFrame constructor not properly called!

It seems a string representation isn't satisfying enough for the DataFrame constructor. This means that you are providing a string representation of a dict to DataFrame constructor, and not a dict itself. So this is the reason you get that error. Just simply adding the list() around the dictionary items,:

df=pd.DataFrame(list(test_dict.items()),columns=['col1','col2'])

which will solve the error.