sqlalchemy-airtable 0.0.1.dev2

Python DB-API and SQLAlchemy interface for Airtable.

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

Installation

I was having issues with apsw-3.9.2.post1 (the newest version of apsw that would install for me from PyPI) and ended up needing to follow the instructions here to build / install apsw from source. There is an open ticket on the APSW project to provide newer wheels. The issue might be triggered if the table name needs escaping and the error looked like:

SystemError: <method 'execute' of 'apsw.Cursor' objects> returned NULL without setting an exception

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