Data Ninjas! Delete Airtable Records efficiently with Python.

Photo by Ujesh Krishnan on Unsplash

I came across a situation recently where I had to delete records automatically based on the minimum date, prompting me to explore solutions to automate this process.

In this specific use case, imagine a scenario where you maintain a database in Airtable containing time-sensitive information, such as sales data, project milestones, or event schedules. Over time, as new data is added, older records become obsolete and need to be removed to maintain data relevance and efficiency. In such scenarios, manually identifying and deleting outdated records can be time-consuming and prone to errors. This article will guide you through the process of automating record deletion in Airtable using Python, providing a streamlined solution to efficiently manage your database and ensure data integrity.

Prerequisites

1. Basic understanding og Python Proframming language
2. Familiarity with airtable and its API

Setup

Before diving into the code, ensure you have the following:

1. An airtable account with base set up.
2. Python installed on your system
3. Necessary python libraries (“requests”)

pip install requests

You can explore a detailed guide on setting up the Airtable API by referring to the following article

Unlocking Data Magic: Supercharge Your Workflow with Python and Airtable API

Understanding the code

Let’s break down the key components of the script

1. Fetching Records:

• The fetch_all_records() function retrieves all records from the specified Airtable base and table using the Airtable API. It handles pagination to ensure all records are fetched.

import requests

api_key = ''
base_id = ''
table_name = ''
headers = {'Authorization': 'Bearer ' + api_key}

def fetch_all_records():
    records = []
    url = f'https://api.airtable.com/v0/{base_id}/{table_name}'

    while True:
        response = requests.get(url, headers=headers)
        response_json = response.json()
        records.extend(response_json.get('records', []))

        offset = response_json.get('offset')
        if not offset:
            break

        url = f'https://api.airtable.com/v0/{base_id}/{table_name}?offset={offset}'

    return records

all_records = fetch_all_records()
print(len(all_records))

2. Finding Minimum Date:

• The find_minimum_date(records) function identifies the minimum date among the fetched records. This is crucial for determining which records to delete based on a specified condition.

My table contains column “As Of Date” which will be used to grab the minimum of available dates. Replace it with your column name

def find_minimum_date(records):
    min_date = None
    for record in records:
        record_date = record['fields'].get('As Of Date')
        if record_date:
            # Use datetime.datetime.strptime
            record_date = datetime.datetime.strptime(record_date, '%Y-%m-%d')
            if not min_date or record_date < min_date:
                min_date = record_date
    return min_date

min_date = find_minimum_date(all_records)
print(min_date)

3. Deleting Records:

• The delete_records(records, min_date) function deletes records from the Airtable table based on a specified condition. In this script, records with a date earlier than or equal to the minimum date are deleted.

def delete_records(records, min_date):
    record_ids_to_delete = [record['id'] for record in records if datetime.datetime.strptime(record['fields'].get('As Of Date', '9999-12-31'), '%Y-%m-%d') <= min_date]
    batch_size = 10  # Airtable allows up to 10 records per batch deletion
    
    for i in range(0, len(record_ids_to_delete), batch_size):
        batch = record_ids_to_delete[i:i + batch_size]
        # Construct the URL with multiple record IDs
        url = f'https://api.airtable.com/v0/{base_id}/{table_name}?' + '&'.join([f'records[]={id}' for id in batch])
        response = requests.delete(url, headers=headers)
        
        # Handle response and errors
        if response.status_code == 200:
           print(f'Successfully deleted batch: {batch}')
        else:
           print(f'Error deleting batch: {batch}. Response: {response.text}')

if min_date:
    delete_records(all_records, min_date)
    print(f"Records deleted for {min_date}")

• The for loop iterates over the list of record IDs to delete (record_ids_to_delete) in batches. The batch_size variable specifies the number of record IDs to include in each batch.
• Within each iteration of the loop, a batch of record IDs is sliced from the record_ids_to_delete list. The slicing is done using Python's list slicing notation [i:i + batch_size], which selects elements from index i up to (but not including) index i + batch_size.
• For each batch of record IDs, a URL is constructed to delete multiple records in a single request. The URL is built using f-strings (formatted string literals) to insert the base_id and table_name.
• The record_ids parameter is appended to the URL to specify the IDs of records to be deleted. Multiple record IDs are joined together using the & separator and the join() method.
• Once the URL is constructed for the batch of record IDs, a DELETE request is made to the Airtable API endpoint specified by the URL.
• The requests.delete() function is used from the requests library to send the DELETE request. The headers parameter includes the authorization token required for authentication.

Replace placeholders (api_key, base_id, table_name) with your Airtable API key, base ID, and table name.

Access the python script using the following Github link

AirtableAPI/delete_records_minimum.py at c4e881d88f58ea4fc8f2e527abf7acfb7bb0596f ·…

Conclusion

Automating record deletion in Airtable using Python provides a streamlined approach to data management tasks. By leveraging the Airtable API and Python’s capabilities, you can efficiently manage your database and ensure data integrity. This approach helps optimize performance and reduce the number of HTTP requests made to the Airtable API, improving the efficiency of record deletion. Feel free to customize the provided script to suit your specific requirements and enhance your workflow.

References

• Airtable Api reference

Airtable | Everyone's app platform

• Python Requests Library

requests