Intermediate

Airtable has proven to be a one-stop solution to automate and manage business processes. It provides the usability of a database in a much simpler spreadsheet familiar operations. Airtable has a compelling and intuitive GUI interface to perform the CRUD operations in your data table. But for you power users, it has also exposed its API. This how-to guide will focus on how you can set up your Airtable account, access the API keys, and perform basic CRUD operations over the API using Python3 scripts.

Getting Started with Airtable

The core functionality provided by Airtable is the spreadsheet-like table, which stores multiple formats of data. Each column in the table is termed field, and each row in the table is termed record. Records are all the data that are associated with a particular field. To know more about Airtable, do follow this step by step guide.

Step 1: Creating your Airtable account

• Visit the official Airtable website from your browser.
• On the top right corner of the homepage, click the “Sign Up” button.

• On the sign-up page, you have two options to create your account. You can either click on the “Sign up with Google” button to create the account using your existing Google credentials or fill the sign-up form. In the sign-up form, enter your first name, last name, email address, and password in their respective fields. Next, click the “Sign up for free” button.

• Click on “Continue” on the Welcome page.
• On the “Tell us a bit about yourself” pop-up window, select your options based on your preference, requirement, experience, and intend for using Airtable.
• On the “Let’s set up your workspace” pop-up window, enter a workspace name. You can also invite your teammates from this window to collaborate in the workspace. Alternatively, you can skip this process by clicking on the “Skip” button.
• Follow along with the walkthrough video to explore the product, or you can skip it.
• Congratulations!! You have successfully created your Airtable account. Now you can explore the Airtable interface for your intended purpose.

• The tables where you store data and perform operations are termed “Base”. Airtable provides you with some default base templates for getting you started quickly on your task. Based on your requirement and business need, you can select among the listed default base templates: Expense Tracking, Nonprofit program management, Inventory Tracking, Simple application tracker, and Business Development CRM. You can also create your custom table base by clicking on “Add a base”.

Step 2: Accessing your Airtable API key

Airtable has exposed its API to perform system functionalities beyond the product GUI. You can use the Airtable API to perform the four significant actions:

• To retrieve records from your Airtable base.
• Add new rows (records) to your table fields.
• Edit the existing rows (records) in your table fields.
• Delete rows (records) from your table fields.

Airtable APIs complies with the Representational State Transfer (REST) semantics. It supports JSON with UTF-8 encoding. The APIs can be used using any REST client browser or cURL tool. For developing custom applications or scripts on top of the Airtable, programming language HTTP libraries can be used. For example, in Python, ‘requests’ can be used to work with the APIs.

NOTE: Airtable API has a request limit of 5 requests per second, per base.

The Airtable APIs use token-based authentication to authenticate and authorize you to perform the API actions. You can generate an API key that identifies and validates you as an authentic and authorized user. You will have the same privilege and authority as to your account. Follow these steps to access the API key:

• On the top right corner of your main Airtable interface, click on your account avatar icon.
• From the drop-down, click on “Account”.

• From the “Account Overview” page, click on the “Generate API key” button.

• A secret-key will be generated. Click on the secret-key box to view your API key. Secure the API key and use it while accessing the Airtable API. You can also delete and regenerate the API key.

Step 3: Accessing your Base ID

Base ID is another mandatory parameter that is required while working with the Airtable APIs. Base ID is a unique identifier for your base. The API actions available for each base are unique. Thus Base ID determines the actions that are available when using the Airtable APIs. Follow these steps to access the Base ID for your base.

• Click on your desired base. For this example, we will be using the default base template “Expense Tracking”.

• Your default base template window opens. The table consists of dummy fields and records. You can customize these tables and views as per your preference. Click the “HELP” button at the top-right corner of this window. From the drop-down, select “API documentation”.

• An interactive API documentation window opens. This API documentation is unique for each base. In the introduction section of this document, the Base ID is present in a green text color formatting.

Using the Airtable Python Wrapper

A prevalent concept in object-oriented programming is “DRY”, short for “Don’t Repeat Yourself”. As such, python incorporates decorators, also known as wrappers, that provide additional functionality and processing capability to an existing function. Following the principle of “Not reinventing the wheel”, we will be using a wrapper called Airtable Python Wrapper. It was released under the MIT license by Gui Talarico.

Step 1: Installing the Airtable Python Wrapper

• Create a new folder named “Airtable” as your main working directory. Create a file named main.py. Open the Terminal (Linux / Mac), Command Prompt (Windows), or Integrated Shell in any Integrated Development Environment (IDE).
• We will be using PIP (Package Installer for Python) to install this wrapper.

NOTE: Make sure you already have installed Python 3 in your system.

• Insert the following code in your Terminal (Linux / Mac), Command Prompt (Windows), or Integrated Shell in any Integrated Development Environment (IDE).

[Shell]
pip3 install airtable-python-wrapper

OUTPUT

• You need to install the ‘requests’ module of python. So install the request using PIP by inserting the following code in your command line terminal.

[Shell]
pip3 install requests

Step 2: Using Airtable Python Wrapper

• Open your main.py file using an IDE. Insert the following code.

#[insert to main.py]
import os
from pprint import pprint
from airtable import Airtable

base_key = 'Base_Key' # Insert the Base ID of your working base
table_name = 'Table_Name' #Insert the name of the table in your working base
api_key = 'API_Key' #Insert your API Key
airtable = Airtable(base_key, table_name, api_key)
print(airtable)

NOTE: In the above code, base_key, table_name, and api_key are the Base ID, the table’s name in your base, and your API key, respectively. All these arguments are string so needs to be enclosed in single or double inverted commas.

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

Step 3: Viewing the records

In our example base, there are eight fields (columns) with three records (rows). Using the following code, we will be receiving the first two records (rows) from the base. Here “pprint” has been used to print the JSON data in a structured order.

[insert to main.py]
pages = airtable.get_iter(maxRecords=2)

for page in pages:
    for record in page:
        pprint(record) #pprints short for pretty print, prints the data in a structured format

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

Besides each field’s records, the received JSON response also consists of the date and time-stamp when the data was inserted in the table.

Step 4: Searching for records

The most frequent operation that we perform on any data records is the search. A ‘search’ function is defined in the wrapper, which takes parameters to search fields for any specific record. In this example, we will be first searching in the Category field for any record consisting of “Health & Personal Care”. When the search is complete and if the query matches with any record, it displays all the fields and records associated only with the “Health & Personal Care” category. If no record is found, an empty list is printed.

#[Insert to main.py]
records = airtable.search('Category', "Health & Personal Care")
pprint([r['fields'] for r in records])

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

Step 5: Sorting the records

With tables consisting of large numbers of rows, it is vital to change the rows’ order. In the default base template, the dummy data has Maritza records followed by two records of Quinns. In this example, we will sort the data in the reverse order.

#[Insert to main.py]
records = airtable.get_all(maxRecords=3, sort=[("Who Paid?", 'desc')])
pprint(records)

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

Step 6: Deleting the records

Data are volatile and are constantly changing. There is a constant need for updating data. There are multiple circumstances in which rows need to be removed from the table. Here in our example, we have three records. Using the API, we will be removing the top entry in the base.

#[Insert in main.py]
rec = airtable.get_all()[0]
airtable.batch_delete([rec['id']])

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

Step 6: Inserting new records

As mentioned earlier, data are in a continually changing state. It is more important to keep a record for new data on a priority scale than deleting an out-dated one. In this example, we will insert a new record to the base.

#[Insert in main.py]
airtable.insert(
 {'Category': 'Interior Decor',
             'Date & Time': '2015-11-06T14:22:00.000Z',
             'Notes': 'A cute blue cactus with golden spines, will go great in '
                      'the dining room.',
             'Short Description': 'Cactus',
             'Total': 11.5,
             'Who Paid?': 'Maritza'})

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

In a real-case scenario, it is less likely that you will be inserting only a single record at the time. There will be a need to add multiple records in a single batch. In the next example, you will learn how to insert multiple records.

#[Insert in main.py]
airtable.batch_insert([{'Category': 'Interior Decor',
            'Date & Time': '2019-11-06T14:22:00.000Z',
            'Notes': 'A wall painting for the hall.',
            'Short Description': 'Wall painting',
            'Total': 20.5,
            'Who Paid?': 'John'},

            {'Category': 'Kitchen Appliances',
            'Date & Time': '2018-01-06T14:22:00.000Z',
            'Notes': 'A five in one mixer and jucier set.',
            'Short Description': 'Multi-purpose mixture',
            'Total': 30.5,
            'Who Paid?': 'Bob'},
            
            {'Category': 'Electronics',
            'Date & Time': '2019-01-06T14:22:00.000Z',
            'Notes': 'A philips Smart LED TV',
            'Short Description': 'Smart LED TV',
            'Total': 100.5,
            'Who Paid?': 'Martha'}])

[Shell]
python3 main.py #Execute the main.py python file

OUTPUT

Ending Remarks

I hope you were able to follow along with this step-to-step How-to guide. By now, you might have got a much deeper understanding of the Airtable as a product and the various operations you can perform upon. After confidently working with these CRUD operations using the Airtable API, you can excel deeper. Initially, try on your own by changing the parameters and variables from the above example. Subsequently, excel through the Airtable API documentation, Airtable Python Wrapper documentation, and other resources to build your expertise over Airtable.

Subscribe to our newsletter