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. 

SydneyF

@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