Pass results as parameters from Alteryx to Python script

[silvia_wang]

Hi Experts,

I want to embed my python script in an alteryx workflow for deployment. I wonder if there is a way to pass the alteryx results as parameters that can be later used by python. I attached a simple example here where I want to pass three parameters:  ID = "AAA", Target = "BBB", and Region = "CCC". Then in my python script, I have three corresponding variables named "ID", "Target", and "Region". How do I automatically set the three variables in python to have the values specified from the previous workflow? I prefer to put everything under the alteryx WF rather than saving the alteryx table to an external file (e.g., csv file) before loading into python. Is there a decent solution? I would expect this should work similar to passing command line arguments to python. 

Thanks!!

[tcroberts]

I believe that the Python tool will read input data as a pandas dataframe when you use Alteryx.read().

This means that you could do some indexing to set your variables in the python script:

dat = Alteryx.read('#1')
id_var = dat['ID'].iloc[0]
target = dat['target'].iloc[0]
region = dat['region'].iloc[0]

dat['field_name'] tells python to look only at that column, `,iloc[0]` says look at the value in index location 0.

You could the do `.astype(<whatevertypeyouwant>)` to further transform this.

Note that you can't (and shouldnt) name something "ID", as id is a keyword in Python.

Let me know if this helps or I've missed something,

Cheers!

EDIT: Provided that these are the only 3 variables in the #1 input, I think you could also do the following, however I don't have a python environment on this computer so I cant test it right now:

id_var, target, region = dat.iloc[0]

[silvia_wang]

How do I read the alteryx table in if the table is named ("%TEMP%dummy.yxdb")?

[tcroberts]

It shouldn't matter what the file is named, what's important is the connection name. If you have only 1 connection into the Python Tool, as in your screen shot, then the table should be named '#1' in Python.

EDIT: Sorry, I misunderstood the screenshot, you're running a Script using the Run Command Tool, not the Python Tool. If you're on Alteryx 2018.3 you should try out the new Python Tool.

Otherwise, I don't think Python has support for .yxdb files. I think your best bet would be saving to a .csv or a SQL table, however if this script will be running many times, this could certainly be a bottleneck and I can see why you're trying to avoid it.

Let me know if you have access to 2018.3, otherwise I'll try to find more information about .yxdb files and Python

[silvia_wang]

Thanks for your clarification. I am using Alteryx Designer 11.7 and the python 3 miniconda version comes with the alteryx distribution. 

[tcroberts · Accepted solution]

I see, in that case I don't think you'll be able to avoid writing it to a file that Python can read in first.

Either that or you could try to turn your script into an Alteryx macro using the Python SDK.

The only other solution that comes to mind would be reading it into R, turning it to something like a binary .feather file which has very I/O speeds, and then reading that feather into Python, however that doesn't help much to avoid the bottleneck that comes with reading/writing to disk.

Here's a link from StackOverflow: https://stackoverflow.com/questions/41750487/open-alteryx-yxdb-file-in-python

[silvia_wang]

Thank you for your detailed explanation. Very helpful!