ORM¶
Defining Models¶
The Model class allows you create ORM-style classes for your Airtable tables.
from pyairtable.orm import Model, fields as F
class Contact(Model):
first_name = F.TextField("First Name")
last_name = F.TextField("Last Name")
email = F.EmailField("Email")
is_registered = F.CheckboxField("Registered")
company = F.SingleLinkField("Company", Company, lazy=False)
class Meta:
base_id = "appaPqizdsNHDvlEm"
table_name = "Contact"
api_key = "keyapikey"
Once you have a model, you can query for existing records using the
first() and all() methods, which take the same arguments as
Table.first and Table.all.
You can also create new objects to represent Airtable records you wish
to create and save. Call save() to save the
newly created object back to Airtable.
>>> contact = Contact(
... first_name="Mike",
... last_name="McDonalds",
... email="mike@mcd.com",
... is_registered=False
... )
>>> assert contact.id is None
>>> contact.exists()
False
>>> assert contact.save()
>>> contact.exists()
True
>>> contact.id
'recS6qSLw0OCA6Xul'
You can read and modify attributes, then call save()
when you’re ready to save your changes to the API.
>>> contact = Contact.from_id("recS6qSLw0OCA6Xul")
>>> assert contact.is_registered is False
>>> contact.is_registered = True
>>> contact.save()
To refresh a record from the API, use fetch():
>>> contact.is_registered = False
>>> contact.fetch()
>>> contact.is_registered
True
Use delete() to delete the record:
>>> contact.delete()
True
There are also batch_save() and
batch_delete() for when you need to
create, modify, or delete several records at once:
>>> contacts = Contact.all()
>>> contacts.append(Contact(first_name="Alice", email="alice@example.com"))
>>> Contact.batch_save(contacts)
>>> Contact.batch_delete(contacts)
You can use your model’s fields in formula expressions.
ORM models’ fields also provide shortcut methods
eq(),
ne(),
gt(),
gte(),
lt(), and
lte():
>>> formula = Contact.last_name.eq("Smith") & Contact.is_registered
>>> str(formula)
"AND({Last Name}='Smith', {Registered})"
>>> results = Contact.all(formula=formula)
[...]
Supported Field Types¶
The following grid maps each of the supported field types in pyAirtable to the Airtable field type. Any field with a lock icon is read-only by default. For more information on how the Airtable API represents each of its field types, read Field types and cell values.
ORM field class |
Airtable field type(s) |
|---|---|
(undocumented) |
|
Airtable does not have a concept of fields that require values, but pyAirtable allows you to enforce that concept within code using one of the following field classes.
See Required Values for more details.
ORM field class |
Airtable field type(s) |
|---|---|
Formula, Rollup, and Lookup Fields¶
The data type of “formula”, “rollup”, and “lookup” fields will depend on the underlying fields they reference, and pyAirtable cannot easily guess at those fields’ types.
If you need to refer to one of these fields in the ORM, you need to know what type of value you expect it to contain. You can then declare that as a read-only field:
from pyairtable.orm import fields as F
class MyTable(Model):
class Meta: ...
formula_field = F.TextField("My Formula", readonly=True)
rollup_field = F.IntegerField("Row Count", readonly=True)
lookup_field = F.LookupField[str]("My Lookup", readonly=True)
Note
LookupField will always return a list of values,
even if there is only a single value shown in the Airtable UI.
Error Values¶
Airtable will return special values to represent errors from invalid formulas, division by zero, or other sorts of issues. These will be returned by the ORM as-is. Read more at Common formula errors and how to fix them.
You can check for errors using the is_airtable_error() function:
>>> record = MyTable.from_id("recyhb9UNkEMaZtYA")
>>> record.formula_field
{'error': '#ERROR!'}
>>> record.rollup_field
{'specialValue': 'NaN'}
>>> record.lookup_field
[{'error': '#ERROR!'}]
>>> from pyairtable.api.types import is_airtable_error
>>> is_airtable_error(record.formula_field)
True
>>> is_airtable_error(record.rollup_field)
True
>>> is_airtable_error(record.lookup_field[0])
True
Required Values¶
Airtable does not generally have a concept of fields that require values, but pyAirtable allows you to enforce that a field must have a value before saving it. To do this, use one of the “Required” field types, which will raise an exception if either of the following occur:
If you try to set its value to
None(or, sometimes, to the empty string).If the API returns a
None(or empty string) as the field’s value.
For example, given this code:
from pyairtable.orm import Model, fields as F
class MyTable(Model):
class Meta:
...
name = F.RequiredTextField("Name")
The following will all raise an exception:
>>> MyTable(name=None)
Traceback (most recent call last):
...
MissingValue: MyTable.name does not accept empty values
>>> r = MyTable.from_record(fake_record(Name="Alice"))
>>> r.name
'Alice'
>>> r.name = None
Traceback (most recent call last):
...
MissingValue: MyTable.name does not accept empty values
>>> r = MyTable.from_record(fake_record(Name=None))
>>> r.name
Traceback (most recent call last):
...
MissingValue: MyTable.name received an empty value
One reason to use these fields (sparingly!) might be to avoid adding defensive null-handling checks all over your code, if you are confident that the workflows around your Airtable base will not produce an empty value (or that an empty value is enough of a problem that your code should raise an exception).
Linked Records¶
In addition to standard data type fields, the LinkField
and SingleLinkField classes will fetch linked records
upon access, so that you can traverse between related records.
from pyairtable.orm import Model, fields as F
class Person(Model):
class Meta: ...
name = F.TextField("Name")
company = F.SingleLinkField("Company", "Company")
class Company(Model):
class Meta: ...
name = F.TextField("Name")
people = F.LinkField("People", Person)
>>> person = Person.from_id("recZ6qSLw0OCA61ul")
>>> person.company
<Company id='recqSk20OCrB13lZ7'>
>>> person.company.name
'Acme Corp'
>>> person.company.people
[<Person id='recZ6qSLw0OCA61ul'>, ...]
pyAirtable will not retrieve field values for a model’s linked records until the
first time you access a field. So in the example above, the fields for Company
were loaded when person.company was called for the first time. Linked models
are persisted after being created, and won’t be refreshed until you call
fetch().
Note
LinkField will always return a list of values,
even if there is only a single value shown in the Airtable UI. It will not
respect the prefersSingleRecordLink
field configuration option. If you expect a field to only ever return a single
linked record, use SingleLinkField.
Cyclical links¶
If you need to model bidirectional links between two tables, you’ll need to create one of the fields before the linked model is created. pyAirtable provides a few options to address this:
You can provide a
strthat is the fully qualified module and class name. For example,model="your.module.Model"will import theModelclass fromyour.module.You can provide a
strthat is just the class name, and it will be imported from the same module as the model class.You can provide the sentinel value
LinkSelf, and the link field will point to the same model where the link field is created.
from pyairtable.orm import Model, fields as F
class Company(Model):
class Meta: ...
name = F.TextField("Name")
employees = F.LinkField("Employees", "path.to.Person") # option 1
class Person(Model):
class Meta: ...
name = F.TextField("Name")
company = F.SingleLinkField[Company]("Company", Company)
manager = F.SingleLinkField["Person"]("Manager", "Person") # option 2
reports = F.LinkField["Person"]("Reports", F.LinkSelf) # option 3
>>> person = Person.from_id("recZ6qSLw0OCA61ul")
>>> person.manager
<Person id='recSLw0OCA61ulZ6q'>
>>> person.manager.reports
[<Person id='recZ6qSLw0OCA61ul'>, ...]
>>> person.company.employees
[<Person id='recZ6qSLw0OCA61ul'>, <Person id='recSLw0OCA61ulZ6q'>, ...]
Breaking down the LinkField invocation above,
there are four components:
manager = F.LinkField["Person"]("Manager", "path.to.Person")
#^^^^^^^ ^^^^^^^^ ^^^^^^^^^ ^^^^^^^^^^^^^^^^
# (1) (2) (3) (4)
The name of the attribute on the model
Type annotation (optional, for mypy users)
Airtable’s field name for the API
The model class, the path to the model class, or
LinkSelf
Memoizing linked records¶
There are cases where your application may need to retrieve hundreds of nested models through the ORM, and you don’t want to make hundreds of Airtable API calls. pyAirtable provides a way to pre-fetch and memoize instances for each record, which will then be reused later by record link fields.
The usual way to do this is passing memoize=True to a retrieval method
at the beginning of your code to pre-fetch any records you might need.
For example, you might have the following:
from pyairtable.orm import Model, fields as F
from operator import attrgetter
class Book(Model):
class Meta: ...
title = F.TextField("Title")
published = F.DateField("Publication Date")
class Author(Model):
class Meta: ...
name = F.TextField("Name")
books = F.LinkField("Books", Book)
def main():
books = Book.all(memoize=True)
authors = Author.all(memoize=True)
for author in authors:
print(f"* {author.name}")
for book in sorted(author.books, key=attrgetter("published")):
print(f" - {book.title} ({book.published.isoformat()})")
This code will perform a series of API calls at the beginning to fetch
all records from the Books and Authors tables, so that author.books
does not need to request linked records one at a time during the loop.
Note
Memoization does not affect whether pyAirtable will make an API call.
It only affects whether pyAirtable will reuse a model instance that
was already created, or create a new one. For example, calling
model.all(memoize=True) N times will still result in N calls to the API.
You can also set memoize = True in the Meta configuration for your model,
which indicates that you always want to memoize models retrieved from the API:
class Book(Model):
Meta = {..., "memoize": True}
title = F.TextField("Title")
class Author(Model):
Meta = {...}
name = F.TextField("Name")
books = F.LinkField("Books", Book)
Book.first() # this will memoize the book it creates
Author.first().books # this will memoize all books created
Book.all(memoize=False) # this will skip memoization
The following methods support the memoize= keyword argument.
You can pass memoize=False to override memoization that is
enabled on the model configuration.
Attachments in the ORM¶
When retrieving attachments from the API, pyAirtable will return a list of
AttachmentDict.
>>> model = YourModel.from_id("recMNxslc6jG0XedV")
>>> model.attachments
[
{
'id': 'attMNxslc6jG0XedV',
'url': 'https://dl.airtable.com/...',
'filename': 'example.jpg',
'size': 12345,
'type': 'image/jpeg'
},
...
]
You can append your own values to this list, and as long as they have
either a "id" or "url" key, they will be saved back to the API.
>>> model.attachments.append({"url": "https://example.com/example.jpg"})
>>> model.save()
You can also use upload() to
directly upload content to Airtable:
- AttachmentsList.upload(filename, content=None, content_type=None)[source]¶
Upload an attachment to the Airtable API and refresh the field’s values.
This method will replace the current list with the response from the server, which will contain a list of
AttachmentDictfor all attachments in the field (not just the ones uploaded).You do not need to call
save(); the new attachment will be saved immediately. Note that this means any other unsaved changes to this field will be lost.Example
>>> model.attachments.upload("example.jpg", b"...", "image/jpeg") >>> model.attachments[-1]["filename"] 'example.jpg' >>> model.attachments[-1]["url"] 'https://v5.airtableusercontent.com/...'
- Return type
None
ORM Limitations¶
Linked records don’t get saved automatically¶
pyAirtable will not attempt to recursively save any linked records. Because of this, you cannot save a record via ORM unless you’ve first created all of its linked records:
>>> alice = Person.from_id("recWcnG8712AqNuHw")
>>> alice.manager = [Person()]
>>> alice.save()
Traceback (most recent call last):
...
ValueError: Person.manager contains an unsaved record
Field values don’t get refreshed after saving a record¶
pyAirtable will not refresh models when calling save(),
since certain field types (like LinkField) return
lists of objects which you might not want pyAirtable to modify or discard. If you
want to reload the values of all fields after saving (for example, to refresh the
value of formula fields) then you need to call fetch().
For example:
from pyairtable.orm import fields as F
class Person(Model):
class Meta: ...
name = F.TextField("Name")
manager = F.SingleLinkField["Person"]("Manager", F.LinkSelf)
# This field is a formula: {Manager} != BLANK()
has_manager = F.IntegerField("Has Manager?", readonly=True)
bob = Person.from_id("rec2AqNuHwWcnG871")
assert bob.manager is None
assert bob.has_manager == 0
alice = Person.from_id("recAB2AqNuHwWcnG8")
bob.manager = alice
bob.save()
assert bob.has_manager == 0
bob.fetch()
assert bob.has_manager == 1
Type annotations don’t account for possible formula errors¶
The ORM module does its best to give meaningful type annotations for each field. However, it is not feasible for the ORM to determine which fields’ underlying types might return an error code, and to annotate it accordingly.
Taking the same example as above…
class Person(Model):
class Meta: ...
name = F.TextField("Name")
has_manager = F.IntegerField("Has Manager?", readonly=True) # formula
…the type annotation of Person().has_manager will appear as int to mypy
and to most type-aware code editors. It is nonetheless possible that if the formula
becomes invalid, person.has_manager will return {'error': '#ERROR!'}
(which is obviously not an int).
In most cases you probably want your code to fail quickly and loudly if there is an
error value coming back from the Airtable API. In the unusual cases where you want
to gracefully handle an error and move on, use is_airtable_error().
Comments¶
You can use
Model.commentsandModel.add_commentto interact with comments on a particular record, just like theirTableequivalents: