Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Ever wondered how you can use the Python tool to import data into a database and turn that tool into a Macro that allows for dynamic variables to allow for reusing it with ease? Then look no further as I explore this specific use case!
Setting the Scene
Recently while working away on a client job, a requirement came up to import data into a database using Alteryx. Easy I hear you all say, just use the Write Data In-DB tool and be done with it! Well, I wish it had been that easy, but it was a Graph Database, and the preferred method of import was via API. For those who may not know a Graph Database stores nodes and relationships instead of tables, or documents. Data is stored without restricting it to a pre-defined model, allowing a very flexible way of using it [1].
Source: Neo4j
One of the accepted file formats was .csv, which also required a Macro building to ensure the layout and formatting of the .csv matched the requirement for the database. I will cover this in a separate blog for anyone who is interested in learning more.
First Steps
So, to start, I turned to the Alteryx Community for answers! Searching previous posts on a similar subject turned up a few suggestions such as using the Blob tools or using cURL but these were unsuccessful in the environment I was using. Another post suggested the use of Postman (the software tool, not Royal Mail) to build out the query, and leverage Python to perform the import.
Armed with this knowledge I opened Postman to build out an example that would import data into the database and, more importantly, import my data! After a little bit of fiddling on both Postman and on the database, I was able to get this to work, and with a handy feature in Postman, I could extract a ready-made Python script to use.
Import requests
payload = {}
files = [
('file',(Filename, open(Filepath+Filename,'rb'),'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'))
]
headers = {
'UserID' : UserID,
'Token' : Token
}
response = requests.request("POST",URL,headers=headers,data=payload, files=files)
print(response.text)
Obviously, you have not come here to read about my adventures in Postman land. Let us get to the good stuff and explore how I got this to work in Alteryx.
Initial Build
Moving to Alteryx, I started to build out a workflow that used the Python tool at its heart. In fact, all it needed was a Python tool, and the job was done; such a quick win….but where is the fun in that?
Oh, and did I mention I needed to obtain an Authorisation token prior to being able to import data? A minor complication but easily dealt with. After a bit of work, I ended up with a workflow that looked like this:
A simple workflow that allows the user to input their URL for the Token request, along with their User ID. It then runs through the Download tool and JSON Parse tool to extract the Authorisation code and finally pushes the code into the Python tool.
One of the key features I was looking for was to make this Macro as dynamic as possible, to allow for re-use with ease without the need to update any hard-coded sections, and additional inputs can easily be built in using more Input tools as required.
This successfully ran, and my file was getting imported into the database. I then realised that the variables were hard-coded inside the Python script, which would have required users to change them manually, so my next step was to make it even more dynamic!
Dynamic Workflow
At this stage, I must mention my Python knowledge was limited, so a lot of frantic research went on to find out a) how I could bring in variables and b) how easy it would be. Luckily, I discovered it was pretty easy! And so, I set about incorporating additional inputs for the variables that would be required into the workflow.
As you can see, the Filename input has a Control Parameter, as I wanted it to have the functionality to process multiple files if required, thus turning it into a Batch Macro. If the file(s) were in the same folder, of course—it is dynamic but not that dynamic!
The next step was to add the Python code to read in all the inputs and include them in the script, which looks like this:
from ayx import Package
from ayx import Alteryx
import requests
dat = Alteryx.read("#1")
Token = dat['Token'].iloc[0]
dat = Alteryx.read("#1")
UserID = dat['UserID'].iloc[0]
dat = Alteryx.read("#2")
URL = dat['URL'].iloc[0]
dat = Alteryx.read("#3")
Filepath = dat['Filepath'].iloc[0]
dat = Alteryx.read("#4")
Filename = dat['Filename'].iloc[0]
payload = {}
files = [
('file',(Filename, open(Filepath+Filename,'rb'),'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'))
]
headers = {
'UserID' : UserID,
'Token' : Token
}
response = requests.request("POST",URL,headers=headers,data=payload, files=files)
print(response.text)
The #number in double quotes represents the incoming connection the variable is in, and the name in single quotes represents the field name that the data is located in. These inputs are then re-used in the Files and Headers sections.
My final Macro looked like this:
All nice and tidy and ready to be used!
Final Thoughts
And there we go, a Batch Macro that can be used to dynamically read in variables to import data into a database via API. There may be irrelevant code in my Python Script, so I would be happy to be corrected in any part or even be provided with a more efficient script!
I hope this has been useful and I look forward to any questions that you may have.
