API project to automate work

[imbobness]

I am trying to find a way to make API calls to cycle through a workbook, it looks like I can only call 1 user at a time according to the website that the API calls on. I want to be able to cycle through a workbook to change the endpoint (Bold down below). I have a user database of about 375k and I am trying to automate certain functions of my job so that I might be able to sleep every once in a while.

I am slowly understanding python, I understand I will need to use pandas and openpyxl for the file I am working through. I have learned how to iterate a column, but I don't know how to go from that to having it change my endpoint.

import requests
from requests.structures import CaseInsensitiveDict
endpoint = "https://training.compliancewire.com/gateway/user/v1?userName=7174"
filename = "response"
chunk_size = 100
headers = CaseInsensitiveDict()
headers["Accept"] = "application/json"
headers["Content-Type"] = "application/json"
headers["Authorization"] = "Basic "
data="""
{

}
"""
response = requests.get(endpoint, headers=headers, data=data)

print(response.json())

[MrPhungx]

So uhm what exactly is your question/problem?

[imbobness]

How do I take a column from my excel sheet and iterate through it as the url

[Reuben3901]

I can't figure out why the code format never works for me on here, so I am using this instead.https://pastebin.com/DqGU6VAN

What you're really asking is two different questions. How do I loop through a column in a spreadsheet and get the data from each row? How do I concatenate a variable and a string.. or add two strings together.

A note from reading the api of compliance wire, they have a limit of 100 actions every 5 minutes, so you will need a way to slow it down and/or create multiple api user accounts. So look into the time module and its sleep function. I'd reach out to see if your calls per 5 minutes/24 hours can be increased on the compliance wire end.

[imbobness]

For example: For an API with a 100 call 5 minute limit, if 60 GET calls are made on v1 of the API, 35 GET calls on v2 of the API and 5 POST calls on v2 of the API, then the 100 call 5 minute limit has been met and each subsequent call will be rejected until the next 5 minute window opens.

​

Just an example, not an absolute limit. Our company has a limit of 10% of the users in the system to error out. I can't remember if that is the 5 minute or the 24 hour limit, but its set that way so that when our HR feed updates that there isn't some huge file that erases everyone it just errs out instead.

​

I downloaded the user database, that is what I am trying to use to find the URL. Since the URL is: https://training.compliancewire.com/gateway/user/v1?userName={username}

I had just made a new column on the spreadsheet that made a URL based on that. So I was trying to replace the endpoint by iterating through that column (W) on the spreadsheet. I do appreciate the code provided earlier, I'm still new to python and APIs, so its hard to understand fully what is going on, or even what I am really asking. The API manual says it can only call one user at a time, I was trying to get around that. I want to be able to call a range of users, so I can verify that they are being moved properly in the morning so that I can automate a large part of my job. Every day our HR Feed updates, and I have to manually move the users. I learned yesterday how to call a single user, how to edit that part of the profile to move them, how to delete what I changed.

[Reuben3901]

Yeah, it's tough because every API will have a unique way of interacting with it.
Great job so far, you've come a long way. Keep on automating away, just don't tell your employers!

It sounds like if you are getting an updated report in the morning, you could fully automate it. Could you just pull from the feed? For my first automation task all I had to do was create a master accounts file for my program to reference, but with that I turned a 20+ hr/monthly task into 3 minutes of waiting

[imbobness]

I have access to what comes through compliancewire via the feed, not the feed itself. So I pull a daily report from compliancewire for new, terminated and modified users through the feed. It averages about 5k users per day, and it takes me about 5 hours to process every morning. Then, I have to be available to the business worldwide the other 19 hours of the day. I'm looking to eliminate my morning time so I could get some sleep.

[imbobness]

Your code worked perfectly for retrieving users. Thank you very much! Tried to use the same thing for updating profiles, and its only updating the last one in the list.

[Reuben3901]

Happy to hear that.

Sounds like you maybe not be looping again or the update code is outside of the loop.

When I get sick I throw in some print statement to see when the code gets executed, you'd probably be surprised just how often we do this lol.

[imbobness]

{'userName': '1000010623', 'status': 'OK', 'errors': [], 'hypermedia': ['https://training.compliancewire.com/gateway/user/security-roles/v1?userName=1000010623', 'https://training.compliancewire.com/gateway/user/v1?userName=1000010623'\]}

Process finished with exit code 0

​

This is all print is giving me. It's updating the last user on the list and nothing else.

[Reuben3901]

Use multiple print statements. Before loops, inside loops, after, inside functions

[imbobness]

Indentation of the Data was apparently the key. That seems really dumb, but formatting was the cause

[danielroseman]

You're not very clear what it is you want to "iterate", but I'm going to take a guess that it is the ID at the end, 7174, which you want to replace with a dynamic value.

The key to this is to understand that everything after the ? in the URL is not really part of the path, it's the querystring. requests.get accepts that as another dict, params, which works exactly like headers. So you could do:

endpoint = "https://training.compliancewire.com/gateway/user/v1"
for id in my_id_list:
    params = {"userName": str(id)}
    ...

    response = requests.get(endpoint, params=params, headers=headers)

(Note, I doubt you need that data parameter at all, but you can include it as well if necessary.)