Stop Fearing APIs: A Data Engineer’s Field Guide

Bill Donofrio
6 days ago
7 min read

Your manager asks for data from an app that has no export button, no CSV, and no shared drive. The only way in is the app’s API, and if you have never called one before, that request can feel like being handed a locked door with no visible key. This article is that key.

Data engineers run into this constantly. Depending on the company, you may need to pull data from Facebook, LinkedIn, or TikTok for social reporting, Calendly for meeting activity, Asana or ServiceNow for ticket volume, or Microsoft Graph for details about your Azure environment, among many other sources. If you collect and organize data for a living, APIs are not optional knowledge. You will run into them constantly.

This first piece covers the basics of how APIs work, using simple, hands-on examples you can run yourself. All code is written in Python, so you can copy it directly into Jupyter Notebook or Google Colab. Postman is a great tool for testing API calls, but this article skips it. Most data engineering projects live in Python notebooks rather than GUI tools, and Postman does not scale well for large integrations.

An Application Programming Interface, or API, is simply a way for one system to talk to another. You can retrieve data from a call, or you can send data through one. Think of Facebook as an example: you can submit a post directly through the API, or you can retrieve data on how many likes or comments a post received. If you want to automate a process, an API is usually how it gets done.

For this article we will use the Rick and Morty API, found at this URL: https://rickandmortyapi.com/documentation

CRUD Operations (Create, Read, Update, and Delete)

Like a relational database that uses SELECT, MERGE, UPDATE, INSERT, ALTER, or

DELETE commands, APIs have a similar pattern.

● GET: This retrieves data from the server. It is like a SELECT statement.

● POST: This creates a new resource or submits data for processing. It is like an INSERT statement.

● PUT: This replaces an existing resource entirely with the data you send. It is like a MERGE statement.

● PATCH: This partially updates an existing resource, changing only the fields included. It is like an UPDATE statement that only sets specific columns, leaving the rest untouched.

● DELETE: This removes a resource from the server. It is like a DELETE statement.

In the data engineering world, you generally only focus on the GET statement, while leaving the other actions to the software engineering team. For this reason, we will focus solely on the GET action going forward.

Base URL and Endpoint

● Base URL: This is the direct path to the webpage. Think of this as going to Macy’s department store.

● Endpoint: This is where you go to a specific directory on a webpage. This would be like going into the toy department inside Macy’s.

Now, go to this URL: https://rickandmortyapi.com/documentation#get-all-characters. You should see the image below at the top of your screen.

The Rick and Morty API docs showing the “Get all characters” GET request.

In the example above, https://rickandmortyapi.com/api is the base URL, followed by character as the endpoint.

If you click on the links on the left-hand panel, you will see that locations and episodes are also endpoints for this API.

The left-hand navigation panel, showing Character, Location, and Episode as separate endpoints.

Parameters

Once you’ve found the base URL and endpoint, you can add a parameter to get to a more granular state. This is like adding a WHERE clause in a relational database. You can also think of it as going directly to the LEGO aisle inside the toy department in Macy’s.

If you click on this link: https://rickandmortyapi.com/documentation#get-a-single-character, you will see the image below. This shows you how to search for character 2. In this situation, you add 2 to the original endpoint.

Adding an id parameter to the endpoint to look up a single character.

Now, if you click on this link: https://rickandmortyapi.com/documentation#filter-characters, you will notice that you can add parameters after the endpoint. As you can see below, the parameters name and status are added after the endpoint. Notice that the question mark works like a WHERE clause, and the ampersand works like the AND clause in SQL.

BASE URL ENDPOINT PARAMETERS

https://rickandmortyapi.com/api /character ?name=rick&status=alive

Writing the Code

Importing Your Libraries

The first step is to open a Python editor. For this you can use a notebook like Jupyter Notebook, Google Colab, or your own editor like PyCharm. Then use the code below to import the requests and pandas libraries. The requests library is used to call the API directly. The pandas library will be used to transform the data into a DataFrame.

import requests

import pandas as pd

Run the API Request Directly

Now, we will set up our base URL, endpoint, and parameters. I then use an f-string to join the base URL to the endpoint and call it URL. Finally, I run the requests.get() command to retrieve a response. We are focusing on the GET request. However, POST, PUT, PATCH, and DELETE could also be options.

BASE_URL = "https://rickandmortyapi.com/api"

ENDPOINT = "character"

PARAMS = {

"name": "rick",

"status": "alive"

}

URL = f"{BASE_URL}/{ENDPOINT}"

response = requests.get(URL, params=PARAMS)

Check for Status

Next, you will want to check the status to make sure you have a successful result.

status = response.status_code

print(status)

Below are all the possible statuses that you could receive. If you did not get a 200 status, check to make sure you entered all the values correctly above. You will rarely come across 1xx or 3xx status codes. 400-level errors mean there is an error on your side, like an incorrect token or a permission issue. 500-level errors mean the server you are connecting to has an issue. For those errors, you can try re-running your request in a few minutes.

Code	Category	Name	Meaning
100	Informational	Continue	The server received the request headers and the client should proceed to send the body.
101	Informational	Switching Protocols	The server is switching to a different protocol as requested by the client, e.g. upgrading to WebSocket.
200	Success	OK	The request succeeded and the response body contains the requested data.
201	Success	Created	The request succeeded and a new resource was created as a result, typically returned after a POST.
202	Success	Accepted	The request was accepted for processing, but the processing isn't complete yet.
204	Success	No Content	The request succeeded, but there's nothing to return in the response body, common after a DELETE.
301	Redirection	Moved Permanently	The resource has permanently moved to a new URL, and future requests should use that URL instead.
302	Redirection	Found	The resource temporarily resides at a different URL, but the original URL should still be used going forward.
304	Redirection	Not Modified	The cached version of the resource the client already has is still valid, so nothing new is sent.
400	Client Error	Bad Request	The server couldn't understand the request because of invalid syntax or malformed parameters.
401	Client Error	Unauthorized	Authentication is required and either wasn't provided or failed.
403	Client Error	Forbidden	The server understood the request but refuses to authorize it, even if you're authenticated.
404	Client Error	Not Found	The requested resource doesn't exist at that URL.
405	Client Error	Method Not Allowed	The HTTP method used (GET, POST, etc.) isn't supported for this particular resource.
409	Client Error	Conflict	The request conflicts with the current state of the resource, e.g. a duplicate record.
422	Client Error	Unprocessable Entity	The request was well-formed, but the data inside it failed validation.
429	Client Error	Too Many Requests	The client has sent too many requests in a given time window, this is what rate limiting looks like.
500	Server Error	Internal Server Error	The server hit an unexpected condition and couldn't complete the request, a generic catch-all failure.
502	Server Error	Bad Gateway	A server acting as a gateway or proxy got an invalid response from the upstream server it was relying on.
503	Server Error	Service Unavailable	The server is temporarily unable to handle the request, often due to overload or maintenance.
504	Server Error	Gateway Timeout	A server acting as a gateway didn't get a response from the upstream server in time.

Create JSON Data

If you received a 200 status, then you can run the code below. This will convert the response into JSON.

page_data = response.json()

print(page_data)

Below is a preview of the final results.

{

"info": {

"count": 29,

"pages": 2,

"next": "https://rickandmortyapi.com/api/character?page=2&name=rick&status=alive",

"prev": null

"results": [

{

"id": 1,

"name": "Rick Sanchez",

"status": "Alive",

"species": "Human",

"type": "",

"gender": "Male",

"origin": {

"name": "Earth (C-137)",

"url": "https://rickandmortyapi.com/api/location/1"

// ...

]

}

Pagination

Now, you will notice that you did not get all the data you requested all at once. To prevent the server from crashing, APIs generally limit how much data you can get per call. Often this limit can be between 100 and 1000 rows. In this case, it is only 20 rows. In order to get more data, you will have to paginate through it one page at a time. The code below shows you how to find the next page URL. It then passes it back into the request to give you a new response.

next_page_url = page_data["info"]["next"]

print(next_page_url)

new_response = requests.get(next_page_url)

print(new_response.json())

In the snippet below, you can see the next URL highlighted.

{

"info": {

"count": 29,

"pages": 2,

"next": "https://rickandmortyapi.com/api/character?page=2&name=rick&status=alive",

"prev": null

// ...

}

Joining All Your Data Together

The code below will loop through all the pages until the response is complete. This combines all the code shown before, with one addition: an all_results list that collects every page’s records. The extend method joins each page’s results together to make one large list.

BASE_URL = "https://rickandmortyapi.com/api"

ENDPOINT = "character"

PARAMS = {

"name": "rick",

"status": "alive"

}

url = f"{BASE_URL}/{ENDPOINT}"

all_results = []

page_number = 1

max_pages = 10

while url is not None and page_number <= max_pages:

response = requests.get(url, params=PARAMS if page_number == 1 else None)

response.raise_for_status()

data = response.json()

all_results.extend(data["results"])

print(f"Page {page_number}: {len(data['results'])} characters")

url = data["info"]["next"]

print(url)

page_number += 1

Creating Your DataFrame

Finally, you will want to convert the raw JSON into a DataFrame. There are a couple of ways to do this.

If you use the code below, you will get a DataFrame, but you may have objects inside some of your rows.

df = pd.DataFrame(all_results)

display(df)

In order to resolve this, use the json_normalize method. This will break up these objects into their own columns.

df = pd.json_normalize(all_results)