Working with Tables¶

Note

Airtable imposes a rate limit of 5 QPS per base. If you exceed that limit, their API will return 429 errors for a moment. By default, pyAirtable will retry 429 errors up to five times, but retrieving many pages of records might take several seconds. Read more at pyairtable.retry_strategy().

Supported Endpoints¶

The Airtable API exposes a number of endpoints for manipulating data within tables. The grid below maps Airtable’s official API endpoints to this library’s methods.

Airtable Endpoint	pyAirtable Method
Get a record	`get()`
Get all records	`all()`
Get matching records	`all(formula=...)`
Get first match	`first()`
Create a record	`create()`
Update a record	`update()`
Replace a record	`update(replace=True)`
Delete a record	`delete()`
Create multiple records	`batch_create()`
Update multiple records	`batch_update()`
Upsert multiple records	`batch_upsert()`
Delete multiple records	`batch_delete()`

Fetching Records¶

iterate()

Iterate over a set of records of size page_size, up until max_records or end of table.

>>> for records in table.iterate(page_size=100, max_records=1000):
...     print(records)
...
[{'id': 'rec123asa23', 'fields': {'Last Name': 'Alfred', 'Age': 84}, ...}, ...]
[{'id': 'rec123asa23', 'fields': {'Last Name': 'Jameson', 'Age': 42}, ...}, ...]

all()

This method returns a single list with all records in a table. Note that under the hood it uses iterate() to fetch records so it might make multiple requests.

>>> table.all(sort=["Name", "-Age"])
[{'id': 'rec123asa23', 'fields': {'Last Name': 'Alfred', 'Age': 84}, ...}, ...]

Parameters¶

Airtable’s API offers a variety of options to control how you fetch data.

Most options in the Airtable API (e.g. sort, fields, etc.) have a corresponding keyword argument that can be used with fetching methods like iterate() or all().

Keyword Argument	Notes
`max_records`	The maximum total number of records that will be returned. If this value is larger than `page_size`, multiple requests will be needed to fetch all records.
`sort`	List of fields to sort by. Default order is ascending. This parameter specifies how the records will be ordered. If you set the view parameter, the returned records in that view will be sorted by these fields. If sorting by multiple columns, column names can be passed as a list. Sorting Direction is ascending by default, but can be reversed by prefixing the column name with a minus sign `-`.
`view`	The name or ID of a view. If set, only the records in that view will be returned. The records will be sorted according to the order of the view.
`page_size`	The number of records returned in each request. Must be less than or equal to 100. If no value given, Airtable’s default is 100.
`formula`	An Airtable formula. The formula will be evaluated for each record, and if the result is none of `0`, `false`, `""`, `NaN`, `[]`, or `#Error!` the record will be included in the response. If combined with view, only records in that view which satisfy the formula will be returned. Read more at Building Formulas.
`fields`	Name of field or fields to be retrieved. Default is all fields. Only data for fields whose names are in this list will be included in the records. If you don’t need every field, you can use this parameter to reduce the amount of data transferred.
`cell_format`	The cell format to request from the Airtable API. Supported options are json (the default) and string. json will return cells as a JSON object. string will return the cell as a string. user_locale and time_zone must be set when using string.
`user_locale`	The user locale that should be used to format dates when using string as the cell_format. See Supported SET_LOCALE modifiers for valid values.
`time_zone`	The time zone that should be used to format dates when using string as the cell_format. See Supported SET_TIMEZONE timezones for valid values.
`use_field_ids`	An optional boolean value that lets you return field objects where the key is the field id. This defaults to `False`, which returns field objects where the key is the field name. This behavior can be overridden by passing `use_field_ids=True` to `Api`.

Return Values¶

This library will return records as RecordDict.

>>> table.all()
[
    {
        'id': 'recwPQIfs4wKPyc9D',
        'createdTime': '2017-03-14T22:04:31.000Z',
        'fields': {
            'Name': 'Alice',
        },
    },
    {
        'id': 'rechOLltN9SpPHq5o',
        'createdTime': '2017-03-20T15:21:50.000Z',
        'fields': {
            'Name': 'Bob',
        },
    },
    {
        'id': 'rec5eR7IzKSAOBHCz',
        'createdTime': '2017-08-05T21:47:52.000Z',
        'fields': {
            'Name': 'Carol',
        },
    }
]

Formulas¶

Methods like all() or first() accept a formula= keyword argument so you can filter results using an Airtable formula.

The simplest option is to pass your formula as a string; however, if your use case is complex and you want to avoid lots of f-strings and escaping, use match() to check field values from a dict:

>>> from pyairtable.formulas import match
>>> table.first(formula=match({"First Name": "John", "Age": 21}))
{"id": "recUwKa6lbNSMsetH", "fields": {"First Name": "John", "Age": 21}}

For more on generating formulas, read the Building Formulas documentation.

Retries¶

As of 2.0.0, the default behavior is to retry requests up to five times if the Airtable API responds with a 429 status code, indicating you’ve exceeded their per-base QPS limit. To adjust the default behavior, you can use the retry_strategy() function.

Creating Records¶

create()

Creates a single record from a dictionary representing the table’s fields.

>>> table.create({'Name': 'John'})
{'id': 'rec123asa23', 'fields': {'Name': 'John', ...}}

batch_create()

Create multiple records from a list of WritableFields dicts.

>>> table.batch_create([{'Name': 'John'}, ...])
[{'id': 'rec123asa23', 'fields': {'Name': 'John'}}, ...]

Updating Records¶

update()

Updates a single record for the provided record_id using a dictionary representing the table’s fields.

>>> table.update('recwPQIfs4wKPyc9D', {"Age": 21})
[{'id': 'recwPQIfs4wKPyc9D', 'fields': {"Name": "John", "Age": 21}}, ...]

batch_update()

Update multiple records from a list of UpdateRecordDict.

>>> table.batch_update([{"id": "recwPQIfs4wKPyc9D", "fields": {"Name": "Matt"}}, ...])
[{'id': 'recwPQIfs4wKPyc9D', 'fields': {"Name": "Matt", ...}}, ...]

batch_upsert()

New in version 1.5.0.

Batch upsert (create or update) records from a list of records. For details on the behavior of this Airtable API endpoint, see Update multiple records.

>>> table.batch_upsert(
...     [{"id": "recwPQIfs4wKPyc9D", "fields": {"Name": "Matt"}}, ...],
...     key_fields=["Name"]
... )
[{'id': 'recwPQIfs4wKPyc9D', 'fields': {'Name': 'Matt', ...}}, ...]

Deleting Records¶

delete()

Deletes a single record using the provided record_id.

>>> table.delete('recwPQIfs4wKPyc9D')
{'deleted': True, 'id': 'recwPQIfs4wKPyc9D'}

batch_delete()

Batch delete records using a list of record ids.

>>> table.batch_delete(['recwPQIfs4wKPyc9D', 'recwAcQdqwe21asdf'])
[{'deleted': True, 'id': 'recwPQIfs4wKPyc9D'},
 {'deleted': True, 'id': 'recwAcQdqwe21asdf'}]

Commenting on Records¶

pyAirtable allows you to access, create, and modify comments on records through the Table class. Both the comments() and add_comment() methods will return instances of Comment, which can be modified, saved, or deleted.

>>> table = Api.table("appNxslc6jG0XedVM", "tblslc6jG0XedVMNx")
>>> comment = table.add_comment("recMNxslc6jG0XedV", "Hello, @[usrVMNxslc6jG0Xed]!")
>>> table.comments("recMNxslc6jG0XedV")
[
    Comment(
        id='comdVMNxslc6jG0Xe',
        text='Hello, @[usrVMNxslc6jG0Xed]!',
        created_time=datetime.datetime(...),
        last_updated_time=None,
        mentioned={
            'usrVMNxslc6jG0Xed': Mentioned(
                display_name='Alice',
                email='alice@example.com',
                id='usrVMNxslc6jG0Xed',
                type='user'
            )
        },
        author=Collaborator(
            id='usr0000pyairtable',
            email='pyairtable@example.com',
            name='Your pyairtable access token'
        )
    )
]
>>> comment.text = "Never mind!"
>>> comment.save()
>>> table.comments("recMNxslc6jG0XedV")[0].text
'Never mind!'
>>> comment.delete()

Testing Your Code¶

pyAirtable provides a MockAirtable class that can be used to test your code without making real requests to Airtable.

class pyairtable.testing.MockAirtable[source]

This class acts as a context manager which mocks several pyAirtable APIs, so that your tests can operate against tables without making network requests.

from pyairtable import Api
from pyairtable.testing import MockAirtable

table = Api.base("baseId").table("tableName")

with MockAirtable() as m:
    m.add_records(table, [{"Name": "Alice"}])
    records = table.all()
    assert len(table.all()) == 1

If you use pytest, you might want to include this as a fixture.

import pytest
from pyairtable.testing import MockAirtable

@pytest.fixture(autouse=True)
def mock_airtable():
    with MockAirtable() as m:
        yield m

def test_your_function():
    ...

Not all API methods are supported; if your test calls a method that would make a network request, a RuntimeError will be raised instead.

>>> with MockAirtable() as m:
...     table.schema()
...
Traceback (most recent call last): ...
RuntimeError: unhandled call to Api.request

You can allow unhandled requests by setting the passthrough argument to True, either on the constructor or temporarily on the MockAirtable instance. This is useful when using another library, like requests-mock, to prepare responses for complex cases (like code that retrieves the schema).

def test_your_function(requests_mock, mock_airtable, monkeypatch):
    base = Api.base("baseId")

    # load and cache our mock schema
    requests_mock.get(
        base.meta_url("tables"),
        json={"tables": [...]}
    )
    with mock_airtable.enable_passthrough():
        base.schema()

    # code below will fail if any more unhandled requests are made
    ...

__init__(passthrough=False)[source]

Parameters: passthrough (bool, default: False) – if True, unmocked methods will still be allowed to perform real network requests. If False, they will raise an error.

For more information, see pyairtable.testing.