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 |
|---|---|
Fetching Records¶
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}, ...}, ...]
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 |
|---|---|
|
The maximum total number of
records that will be returned. If this value is larger than
|
|
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 |
|
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. |
|
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. |
|
An Airtable formula. The formula will be evaluated for each record, and if the result
is none of |
|
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. |
|
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. |
|
The user locale that should be used to format dates when using string as the cell_format. See https://support.airtable.com/hc/en-us/articles/220340268-Supported-locale-modifiers-for-SET-LOCALE for valid values. |
|
The time zone that should be used to format dates when using string as the cell_format. See https://support.airtable.com/hc/en-us/articles/216141558-Supported-timezones-for-SET-TIMEZONE for valid values. |
|
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. |
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¶
The pyairtable.formulas module provides functionality to help you compose
Airtable formulas.
match()checks field values from a Pythondict:>>> from pyairtable.formulas import match >>> formula = match({"First Name": "John", "Age": 21}) >>> formula "AND({First Name}='John',{Age}=21)" >>> table.first(formula=formula) {"id": "recUwKa6lbNSMsetH", "fields": {"First Name": "John", "Age": 21}}
to_airtable_value()converts a Python value to an expression that can be included in a formula:>>> from pyairtable.formulas import to_airtable_value >>> to_airtable_value(1) 1 >>> to_airtable_value(datetime.date.today()) '2023-06-13'
For more on generating formulas, look over the pyairtable.formulas API reference.
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¶
Creates a single record from a dictionary representing the table’s fields.
>>> table.create({'Name': 'John'})
{'id': 'rec123asa23', 'fields': {'Name': 'John', ...}}
Create multiple records from a list of WritableFields dicts.
>>> table.batch_create([{'Name': 'John'}, ...])
[{'id': 'rec123asa23', 'fields': {'Name': 'John'}}, ...]
Updating Records¶
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}}, ...]
Update multiple records from a list of UpdateRecordDict.
>>> table.batch_update([{"id": "recwPQIfs4wKPyc9D", "fields": {"Name": "Matt"}}, ...])
[{'id': 'recwPQIfs4wKPyc9D', 'fields': {"Name": "Matt", ...}}, ...]
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¶
Deletes a single record using the provided record_id.
>>> table.delete('recwPQIfs4wKPyc9D')
{'deleted': True, 'id': 'recwPQIfs4wKPyc9D'}
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='2023-06-07T17:46:24.435891',
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()