Connect to Airtable template

Connect to Airtable

You can query data from Airtable bases with Workspace. Follow the steps in this workspace to connect to Airtable and query Airtable data.

Setup

You need to take a couple of setup steps in Airtable and Workspace to set up a so-called personal access token that you can use to programmatically access data in Airtable.

Create an Airtable account if you haven't already here.
Make sure your Airtable account contains a 'base' (a database). If you don't have data lying around, click "Start with templates" on your Airtable Homepage.
Create the personal access token:
- Click on your avatar icon top right, and select "Developer Hub"
- In the "Personal access tokens" tab, click "Create new token"
- Specify a name for your token, e.g. 'DataCamp Workspace Access'
- Add a scope. data.records:read is enough if you want to fetch data from Airtable. If you also want to write back results of your calculations to Airtable, add data.records:write as well.
- Add a base to access. You can select just one base or all current and future bases in your workspace. Make sure you at least select the base that you want to access from inside Workspace.
- Click "Create token"
- Copy the generated personal access token (PAT) to your clipboard for later use.

Securely store the PAT in Workspace as an environment variable:
- In the Workspace editor, open the 'Environment' side panel and click on "+" next to "Environment variables"
- Set Name to AIRTABLE_PAT
- In Value paste the PAT that you copied to your clipboard in the previous step.
- Set the "Environment Variable Set Name" to something meaningful, e.g. "Airtable PAT"
- Click "Create", "Next" and finally, "Connect". Your workspace session will restart, and AIRTABLE_PAT will now be available as an environment variable in your workspace.

If you've done all of the steps above successfully, you're now ready to run the code below.

Install the airtable package

This package is an easy-to-use Python wrapper around all of the calls you can do to the Airtable API. The wrapper makes it easier to interact with your Airtable data.

%%capture
!pip install airtable

Fetch the PAT and set some variables

Fetch the Airtable PAT that was securely stored as an environment variable.

Set the base ID. Get this base ID by visiting your Airtable base, and taking the URL. It has the following structure:

https://airtable.com/appA2VwEzfjwHWoW9/tbldFYer69FB4P5kG/viwQc7WUb2vAe63mA

The baseID starts with app; in this case it's appA2VwEzfjwHWoW9, but it will be different for your Airtable account and base.

import os
PAT = os.environ["AIRTABLE_PAT"]
BASE_ID = 'appA2VwEzfjwHWoW9' # Change this!

Import data from a table

Use PAT and BASE_ID to create an Airtable client
Use the client to fetch all the records from a table; "Employee directory" in this example, but make sure to change this to the table data you want to fetch.
Restructure the records (an list of dictionaries) to fit them into a pandas DataFrame.

import airtable
import pandas as pd

at = airtable.Airtable(BASE_ID, PAT)
records = at.get("Employee directory")['records']  # Change "Employee directory"!
pd.DataFrame([record["fields"] for record in records])