Using Python and Airtable
Tags: python, airtable, tutorial
Published: 2021-10-21 12:07:04

---
title: Using Python and Airtable
published: true
description: Examples of how to use Airtable's REST API with Python
tags: python, airtable, tutorial
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mk9t3t7nufe3zoutk1tm.png
---

[Airtable](https://airtable.com/) is a cloud-based relational database that simplifies data storage without having to write SQL. Airtable has a [REST API](https://airtable.com/api) that can be used to perform common operations on your Airtable. The API documentation only contains curl and Javascript examples, so this tutorial will look at how you can interact with Airtable using Python complete with examples. You'll learn how to add, retrieve, update, and delete records in Airtable through the Airtable REST API and also talk about some of the common gotchas and limitations.

For this tutorial, we will be using scores from a round of golf as a toy dataset. You will need to create a table in Airtable with the following four columns and data types:

- Date (string)
- Hole (integer)
- Par (integer)
- Score (integer)

All the code referenced here is available in a [GitHub repo](https://github.com/mvielkind/python-airtable-tutorial). In the repo you will find a file `airtable.py` with example functions you'll build here. There is also a Jupyer notebook, `AirtablePractice.ipynb` in the repo you can use to follow along.

## Authentication

First, let's look at the authentication for submitting a request.

Submitting a request to the Airtable REST API requires three pieces of information:

- An API authentication token.
- The ID of your Airtable base.
- The name of the sheet.

Airtable uses a bearer token to authenticate each API request, which should be saved in a `.env` file to keep it secret.

You can find your API token by going to your account page. Scroll down to the API section of your account page, and you will see your token.

![Where to find your Airtable API token](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ssgahq21kphmm7fpjvj6.png)

Copy the token value and paste it into your `.env` file naming it `AIRTABLE_TOKEN`. Make sure you keep this token private and do not share it with anyone! If you're committing files to a GitHub repo (or any other source control system), make sure you exclude the `.env` to avoid accidentally exposing this information!

Next, let's find the Airtable base ID. Make sure you are logged into your Airtable account and go to the [API docs](https://airtable.com/api). Select the base you want to work with from the list. On the next page, you will see the ID of your Airtable Base. Copy the value and add it to your `.env` file naming it `AIRTABLE_BASE_ID`.

![Finding your Airtable Base ID](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7tw1x14wmtnrsylyfk7r.png)

Finally, make a note of the sheet name you want to modify via the API. You'll need that to complete the URL for making requests.

With all the variables assembled to make our requests, it's time to put it together into a python file. Create a new file named `airtable.py`. Include the following at the top of the file:

```python
import os
from python_dotenv import load_dotenv
load_dotenv()

AIRTABLE_TOKEN = os.getenv("AIRTABLE_TOKEN")
AIRTABLE_BASE_ID = os.getenv("AIRTABLE_BASE_ID")
```

Using the `python_dotenv` library, the variables you included in your `.env` are read into two variables, `AIRTABLE_TOKEN` and `AIRTABLE_BASE_ID`, allowing you to utilize them in the rest of your script.

With the authentication sorted out, we need to build the endpoint you'll send requests to. The basic structure of an Airtable API endpoint has three components:

1) The root of the URL, `api.airtable.com/v0`
2) The Airtable Base ID
3) The sheet name

The root URL, `api.airtable.com/v0`, will remain constant across all requests. The Base ID will depend on the specific Airtable Base you are using. Since this tutorial will use the same base, we can create another variable, `AIRTABLE_URL`, at the top of the file that you can use to construct the endpoint for each request. The code at the top of `airtable.py` should now look like this:

```python
import os
from python_dotenv import load_dotenv
load_dotenv()

AIRTABLE_TOKEN = os.getenv("AIRTABLE_TOKEN")
AIRTABLE_BASE_ID = os.getenv("AIRTABLE_BASE_ID")

AIRTABLE_URL = f"https://api.airtable.com/v0/{AIRTABLE_BASE_ID}"
```

## Using the REST API
The Airtable REST API allows you to perform a few core actions: add a record, retrieve records, update records, and delete records. A nice feature of the https://airtable.com/api Airtable API documentation is after you log in to your account, you can view the documentation specific to the Airtable base you want to use. The documentation provides API usage documentation for curl and Javascript. Below we'll look at examples of how to use the API using Python.

To demonstrate how the API works, we will use Airtable to track golf scores. We'll have a sheet named `golf-scorecard` containing four columns: the date, hole number, par, and the number of strokes for the hole.

## Adding Records to Airtable
You can add records to your Airtable by sending a POST request. The request includes a dictionary with a key, `records`, defining an array of the records that will be added to the sheet. Each record in the array is a dictionary with a key `fields`, containing a dictionary that maps values to the columns in the sheet. Here's an example data payload for adding scores to the Airtable for the first two holes.
```json
new_data = {
	"records": [
		{
			"fields": {
				"Date": "2021-09-22",
				"Hole": 1,
				"Par": 4,
				"Score": 5
			}
		},
		{
			"fields": {
				"Date": "2021-09-22",
				"Hole": 2,
				"Par": 4,
				"Score": 4
			}
		}
	]
}
```
When you define records to add to Airtable, make sure the data types of the data you want to add match the data types for the column in Airtable. If the data types do not match, you will get an error.

Next, you need to write an API request to add this data to the Airtable. Create a new function in `airtable.py` called `add_new_scores` that will take the `new_data` scores as input and add them to the Airtable.
```python
def add_new_scores(scores):
    """Add scores to the Airtable."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
      'Authorization': f'Bearer {AIRTABLE_TOKEN}',
      'Content-Type': 'application/json'
    }

    response = requests.request("POST", url, headers=headers, data=json.dumps(scores))

    return response
```
The first part is building the URL referencing the `AIRTABLE_URL` variable we created earlier. The only addition is the name of our sheet, `golf-scores`, which has been added to the URL to instruct Airtable that we want to add records to the `golf-scores` sheet of our base. If you want to add scores to a different sheet, you can substitute the sheet's name in the URL.

Next, the request header is defined. The header passes along the `AIRTABLE_TOKEN` so that Airtable can confirm the request is authorized.

Finally, the POST request is submitted using the `request` module, passing the scores payload along with the request.

Let's run the code and look at the outcome.

One last note about adding records, each request can add a maximum of 10 records to your sheet. If you want to add more than 10 items, you will have to do so across multiple requests. Let's look at an example implementation below. 

```python
def chunk(arr, n):
    for i in range(0, len(arr), n):
        yield arr[i:i + n]

for c in chunk(data["records"], 10):
    chunk_data = {
        "records": c
    }

    response = airtable.add_new_scores(chunk_data)
```
The `chunk` function returns a generator that will break down the records into groups of size `n` where `n` should not be greater than 10. Next, you can iterate over the `n` sized chunks from your data and call the `add_new_scores` function during each iteration to add the records to Airtable.

## Reading Records from Airtable
You can read records from Airtable by issuing a GET request to the API endpoint of a sheet. Let's create a new function in your `airtable.py` file to read records from the `golf-scores` sheet.

```python
def get_golf_scores():
    """Add scores to the Airtable."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
      'Authorization': f'Bearer {AIRTABLE_TOKEN}',
      'Content-Type': 'application/json'
    }

    response = requests.request("GET", url, headers=headers)

    return response
```
Like adding records, you define the URL endpoint to the `golf-scores` sheet and include your authentication header information. The two differences are that you will submit a GET request instead of a POST request, and you do not need to pass a data payload along with the request.

The API will respond with an array of records in your Airtable sheet. Each record has an `id` uniquely identifying the record, a field dictionary with the values for the record, and a `createdTime` key. An example response is below.
```json
{
	"records":
		[
			{
				"id": "<AIRTABLE_RECORD_ID>",
				"fields": {
					"Date": 2021-09-22,
					"Hole": 1,
					"Par": 4,
					"Score": 5
				},
				"createdTime": <some_time>
			}
			...
		]
}
```
You may want only to retrieve records matching specific criteria from your Airtable. Airtable provides several options to customize querying your table. You can instruct Airtable to return particular fields, specify filtering criteria, sort records, and more. For a complete list of functionality, consult the Airtable API.

To demonstrate how to customize API calls to apply a filter to results, let's create a new function, `get_scores_for_hole`, to your file.  In this function, you will query the `golf-scores` sheet in Airtable and return only the records for the hole number you send to the function. Instead of returning all table fields, your response will only contain the `Hole` and `score` fields for each record. Enter the following function in your file.
```python
def get_scores_for_hole(hole):
    """Get scores for a specific hole."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
      'Authorization': f'Bearer {AIRTABLE_TOKEN}',
      'Content-Type': 'application/json'
    }

    params = {
        "fields": ["Hole", "Score"],
        "filterByFormula": f"Hole={hole}"
    }

    response = requests.request("GET", url, headers=headers, params=params)

    return response

```
The difference with this function is the inclusion of the `params` object in your API request. The `fields` key instructs Airtable what fields to return in the response, and `filterByFormula` specifies the filter to apply to the sheet.

Airtable allows you to build more complex filters. For a complete list of functionality available to filter records and manipulate the Airtable response for retrieving records, you can check out the Airtable documentation.

By default, when you submit a GET request, Airtable will return a maximum of 100 records. If your response contains more than 100 records, Airtable will provide an `offset` value in its response. To retrieve more than 100 records, you will need to issue a new request that includes the value of `offset`. The function `get_scores_by_page` below accepts a parameter `offset` to instruct Airtable to retrieve the next page of results.
```python
def get_golf_scores_by_page(offset=None):
    """Retrieve records from Airtable and apply offset is necessary."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
      'Authorization': f'Bearer {AIRTABLE_TOKEN}',
      'Content-Type': 'application/json'
    }

    params = {
        "pageSize": 100
    }

    if offset:
        params["offset"] = offset

    response = requests.request("GET", url, headers=headers, params=params)

    return response
```
As long as your response contains the `offset` value, you will iteratively retrieve more records. When all records are retrieved, Airtable will stop sending `offset` in the response. An example implementation is below.
```python
results = airtable.get_golf_scores_by_page()

while "offset" in results.json():
    results = airtable.get_golf_scores_by_page(response.json()["offset"])
```
## Update a Record
You may need to change your data after adding it to Airtable. Updating existing records can be done in two different ways, PATCH or POST.

PATCH: to update specific fields in a record.
PUT: to clear the record and replace all values with new values.

First, let's look at the PATCH request. A PATCH request allows you to update specific fields of a record while keeping other values unchanged. You can pass a list of records with the values you'd like to change as a payload to the request. For example, if you wanted to update the score for a record, your payload would look something like this:

```json
updated_records = {
	"records": [
		{
			"id": <record_id>,
			"fields": {
				"Score": 4
			},
		}
	]
}
```
To update a record, you must send the record ID you wish to update as a part of the payload. You can find the record ID in the response from retrieving records that you looked at in the previous section. 

Let's put this all together. Create a new function `update_record_fields` in your Python file:

```python
def update_record_fields(updated_records):
    """Update specific field values for a record."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
        'Authorization': f'Bearer {AIRTABLE_TOKEN}',
        'Content-Type': 'application/json'
    }

    response = requests.request("PATCH", url, headers=headers, data=json.dumps(updated_records))

    return response
```
The function accepts a parameter, `updated_records`, defining the records and values to be updated. Replace `<record_id>` in the `updated_records` object with an ID from your table, then use it as input to `update_record_fields`. If you look at the record in Airtable, the value for the `Hole_Number` field should have changed, and all other values should remain the same.

Another way you can update records is by using a PUT request. The main difference between PUT and PATCH is that a PATCH request will update the field values you specify in your payload and keep all other values the same. A PUT request will update the field values you specify but delete values for the other fields.

Let's look at the difference in action. Create a new function `replace_record_fields`:

```python
def replace_record_fields(updated_records):
    """Updates the records."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
        'Authorization': f'Bearer {AIRTABLE_TOKEN}',
        'Content-Type': 'application/json'
    }

    response = requests.request("PUT", url, headers=headers, data=json.dumps(updated_records))

    return response
```
The function is nearly identical to `update_record_fields`. The only difference is that we are using a PUT request instead of a PATCH here. This time let's try to change the score of the first hole to 6. Use the following payload as input to `replace_record_fields`, use the same record ID as the previous request and let's see what happens.

```json
updated_records = {
	"records": [
		{
			"id": <record_id>,
			"fields": {
				"Score": 6
			},
		}
	]
}
```
When you look at Airtable, you should notice that the score for the hole has updated to 6, but all the other record values are empty. The additional fields are empty because they are not included in the payload, representing the difference between updating records with a PUT versus PATCH request. A PATCH request will preserve the values for fields not specified in the update. PUT will clear values that are a part of the update payload.

One last note, similar to adding records, you can only update 10 records per request.

## Delete a Record
The last action you can take via the Airtable API is deleting records. To delete a record, you will need to know the IDs of the records you want to delete. You can include the list of IDs as a query string parameter in a DELETE request to instruct Airtable to delete those records. Create a new function `delete_records` to your file.

```python
def delete_records(records):
    """Delete the records."""
    url = f"{AIRTABLE_URL}/golf-scores"
    headers = {
        'Authorization': f'Bearer {AIRTABLE_TOKEN}',
        'Content-Type': 'application/json'
    }

    params = {
        "records[]": records
    }

    response = requests.request("DELETE", url, headers=headers, params=params)

    return response
```
Pass a list of record IDs as input to the function. When you look at your Airtable, the records should no longer be in the sheet.

Similar to previous actions you've taken with Airtable, you can only delete up to 10 records in a single request.

## Conclusion
There it is! This tutorial showed a few of the things you're able to do with the Airtable REST API using Python.

Happy building!