r/learnpython • • Aug 07 '19

Today I started writing my first API call using Python and I've never felt more energized.

I've had Python on my radar for about a year and a half, but I've had so much other stuff to learn as well - database design, ETL fundamentals, statistics, etc. Because of that, I haven't given it much attention until I started my new job a few months ago. So far I've only been able to dedicate a few hours a week to learning and building out small scripts. I just finished going through Automate the Boring Stuff this week after getting through most of Jose Portilla's Python bootcamp.

My clients use a digital survey platform that's really tedious to get data out of. The platform has a public API and an FTP protocol, but I knew that automating API requests would be the better way to go. In the past, I've used APIs via different BI platform UIs that helped you generate scripts, but I've never written the script myself.

Today, I started my first big project. I'm generating the API call, parsing the JSON, and dumping the data into a csv (eventually I will send this directly to a MySQL database) all with Python. The best part is that I'm writing it all myself - much to my chagrin (or benefit?), I couldn't find any prewritten scripts.

I'm so grateful for this community that's constantly encouraging and supportive of itself. I'm still working through the script but I'd be happy to share it for a review once I get it on my (currently empty) Github assuming I'm allowed to put it up.

330 Upvotes

27 comments sorted by

29

u/IdealizedDesign Aug 07 '19

That’s great to hear. Were you using pandas or any other particular library?

23

u/Deray22 Aug 07 '19

Pandas is what I'm going to use for the data analysis side of things once I've figured out the exact output I want, but the actual API call and data processing script used the json and base64 modules and requests library.

Once I get past my upcoming deadline next week, I'm going to probably use SQLAlchemy and pass the data directly into a database instead of going to csv. Just need more time to figure out exactly how to do that haha.

3

u/tangenttabby Aug 07 '19

If you don’t mind some help/advise, you can use pandas to put the loaded JSON data in a data frame, and then pass your SQL alchemy engine to the to_sql method with the desired ’schema.tablename’

Getting pandas to parse the JSON data can take some learning, and requires you knowing the JSON data well, but I’ve had the best luck using the JSON library to convert it to a dictionary and then convert it to a data frame.

4

u/Bosco_stix Aug 07 '19

Be careful with this method. Out of the box, to_sql will likely make the wrong assumptions for your column data types. You can create the table with desired data types beforehand in your database to prevent this.

Also, it really depends what you're doing. If you're not using pandas to manipulate or compute your data, it can be overkill for simple ETL tasks like this.

1

u/Deray22 Aug 07 '19

I don't think I'll use Pandas for the ETL initially, but my ultimate goal is to run the open response portions of the survey through a machine learning algorithm to categorize them by product/pain point during ETL. I'll check back in a few months once I've figured that out lol

14

u/khalido Aug 07 '19 edited Aug 07 '19

It's a good idea to save the incoming csv/json data as is into a folder, then have a func which reads the CSV files, clean into the form you want then save the final result into the db.

So if you change stuff you still have the original Csv. Think a series of small functions which do one thing each. Then finally you chain them together.

Makes it easier to fix bugs and test stuff.

Also if you don't have gigs of data just save the pandas data frame to disk as a feather file, see

https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html

4

u/CraigAT Aug 07 '19

Before you upload make sure there is nothing private in your code like a private API key, email etc. These can go in a separate file that doesn't get uploaded, maybe upload a sample file instead with generic or placeholder info.

2

u/Deray22 Aug 07 '19

Yes, that's the plan!

5

u/khalido Aug 07 '19

If you're doing lots of API calls, look into using multithreads with the requests library or using an async library like https://aiohttp.readthedocs.io/en/stable/

I drastically sped up a project using threads with requests to send out multiple calls in one go.

But if only bother if getting the data takes a while.

4

u/smokedfishfriday Aug 07 '19

Ya but this is an easy way to blow through a rate limit haha

1

u/Deray22 Aug 07 '19

yeah, this is what I was being careful of. I don't need a streaming pipeline, so I'll probably just do it in weekly batches going forward once I get the last couple years of data together. Thankfully we're talking about thousands of survey responses a month, not millions.

2

u/d2un Aug 07 '19

I'm really interested in how you went about automating this. I understand how to make API calls and what to do with the data but I'm lost on how you'd automate that. Did you learn this from ATBS or some other resource? I'd love to take a look and learn (for a very similar need!)

2

u/Deray22 Aug 07 '19

Automation is still under way! I learned the automation kind of out of necessity. In my last job, I was responsible for regular insights and analysis and I was tired of manually extracting the data twice a week to keep my info fresh. So I used the business intelligence platform (Qlik Sense) that we had to generate API call scripts.

I stored a list of all the dates in my dataset in a file and then had my ETL script check the current day vs. the most recent date in that list. Then the script would execute an API call for each date missing. And then I used the BI platform's scheduler (just a UI for cron) to create an extract job that runs every morning at 4am.

I'm not sure if I'll use that exact same trigger (date comparison) here, but I'll use a similar approach.

1

u/[deleted] Aug 07 '19

Qualtrics API by chance?

2

u/Deray22 Aug 07 '19

No, but I have another client that uses Qualtrics. I'll have to deal with that another day haha.

1

u/[deleted] Aug 07 '19

Ah good stuff - I am also in the Python-learning phase and recently automated our ingestion of data from Qualtrics. It was a fun project, their documentation is pretty clean. Still a pain in the ass to deal with survey data though!

3

u/alkol6 Aug 07 '19

This web based guide about Flask and Sqlalchemy can give a lot of things (ok, not for these days but for someday).

https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-i-hello-world

5

u/iggy555 Aug 07 '19

What’s api?

9

u/Bosco_stix Aug 07 '19

My favorite explanation, by /u/Intrexa:

The term "API" itself is general, it's not really any particular code, style, format, protocol, anything. It just says "If you want your program to interact with mine, this is how you do it".

Let's use a real simple example. I want to find my program to find my external IP address, as the world sees it. I need to reach out to another server, and ask it what IP address it sees me using. So I use an API. This is a very typical RESTful (type of API specification) API. I send a web request to that url, I send specifics to say give me the results in json format, the server does that, my program now knows it's getting json formatted ip address, and I can use that.

3

u/l2ighty Aug 07 '19

A really quick example is Reddit's API. You can go to any subreddit and add ".json" and view the API from there. So for example, the python subreddit in JSON format looks like this: https://www.reddit.com/r/python.json

It's probably going to look like a bunch of gibberish when you first open it, so you might need a JSON viewer extension, but that's the gist of it. In this example, the API is basically a program that handles requests, and returns information from a server.

I'm still learning too so my explanation might be a bit iffy in some parts but that's the general idea

5

u/charmangel_ Aug 07 '19

it’s a kind of a software which can receive interaction via clients, but clients never really see what’s happening to get the request of their own user (all of that — data, passwords, the way it behaves — is managed “behind the scenes”)

1

u/mac-0 Aug 07 '19 edited Aug 07 '19

That's awesome. Can't wait to see the code as I'll need to learn how to do just this in a few months.

Part of my job is creating these exact types of workflows, but we use KNIME (which uses visual nodes for each event, e.g., you'd use a "Write CSV" node to save something as CSV then "Database Executor Node" to input a SQL copy command from CSV or S3 bucket). We're switching to Python for consistency because of a company merger and I'd be interested to see some sample code if you're willing to share.

0

u/[deleted] Aug 07 '19

Awesome! Please edit your post if you decide to push your project!

0

u/LennyKrabigs Aug 07 '19

worst part of python currently iis the inexistance support of http2 to any http client,

from serverside you have multiple things but from client side you are done. ALl projjects are left or httpx but its so new.

Needed to change to nodejs 2 days ago already have 3000 lines of python written in my project, but i need http2 so badly

lucky you dont.

2

u/thebadassets Aug 07 '19

I feel you. I am moving over to Node for work because I need HTTP2 API integration. Good luck to you on the switch!

2

u/AnthonyRolfeIBM Aug 07 '19

Look at this article. Not sure if will work for what you're doing, but from a quick skim of it, it seems like it might. Hoping that it does and you don't have to rewrite 3000 lines of code....

https://medium.com/python-pandemonium/how-to-serve-http-2-using-python-5e5bbd1e7ff1

1

u/LennyKrabigs Aug 07 '19

Thaths for server side. I need support for client side too. Httpx so new not a lot of features Hyper so bugged not working Requests III release in Early 2020 Libcno didnt tested but 1 year witouth comments on git plus bugs