airtable-db-api

A Python DB API 2.0 for Airtable

This module allows you to query Airtable using SQL. It exposes:

• a Python DB API 2.0 (per PEP 249)
• a SQLAlchemy Dialect (see also "Developing new Dialects")
• a Superset Engine Spec

SQLAlchemy support

This module provides a SQLAlchemy dialect.

from sqlalchemy.engine import create_engine

engine = create_engine(
    'airtable://:keyXXXX@appYYY?peek_rows=10&tables=tableA&tables=tableB',
    date_columns={"tableA": ["My Date Field"]},
)

Metadata

At various points we need to know:

1. The list of Tables supported in the Base
2. The list of columns (Fields) supported on a given Table
3. The type information for each Field

As of now we solve 1) by passing in a list of Tables using the tables query parameter on the URL. We solve 2) and 3) using some combination of the peek_rows query parameter specifying the number of rows to fetch from Airtable to guess Field types and a date_columns engine parameter to specify which columns should be parsed as Dates.

Alternatively, 1-3 could all be solved with a comprehensive base_metadata engine parameter that specifies the Tables and Fields. There are a number of ways to generate this, but one approach is scraping the Base's API docs page using a technique like this.

Further options are documented here

Development

Python

$ pip install -r requirements-dev.txt

pre-commit

$ pre-commit install

black

Can be run manually as:

black --target-version py37

Roadmap

• Support for Airtable's Metadata API
• Support passed in Airtable Metadata (w/ types)
• Cleanup configuration (passed as query param on URL vs engine parameters)
• Built in Metadata scraper (not using Metadata API)
• Caching of field type "peeking"
• Datetime support
• More comprehensive testing