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)

AITextField 🔒

AI Text

AttachmentsField

Attachments

AutoNumberField 🔒

Auto number

BarcodeField

Barcode

ButtonField 🔒

Button

CheckboxField

Checkbox

CollaboratorField

Collaborator

CountField 🔒

Count

CreatedByField 🔒

Created by

CreatedTimeField 🔒

Created time

CurrencyField

Currency

DateField

Date

DatetimeField

Date and time

DurationField

Duration

EmailField

Email

ExternalSyncSourceField 🔒

Sync source

FloatField

Number

IntegerField

Number

LastModifiedByField 🔒

Last modified by

LastModifiedTimeField 🔒

Last modified time

LinkField

Link to another record

LookupField 🔒

Lookup

ManualSortField 🔒

(undocumented)

MultilineTextField

Long text

MultipleCollaboratorsField

Multiple collaborators

MultipleSelectField

Multiple select

NumberField

Number

PercentField

Percent

PhoneNumberField

Phone

RatingField

Rating

RichTextField

Rich text

SelectField

Single select

SingleLineTextField

Single line text

SingleLinkField

Link to another record

TextField

Single line text, Long text

UrlField

Url

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)

RequiredAITextField 🔒

AI Text

RequiredBarcodeField

Barcode

RequiredCollaboratorField

Collaborator

RequiredCountField 🔒

Count

RequiredCurrencyField

Currency

RequiredDateField

Date

RequiredDatetimeField

Date and time

RequiredDurationField

Duration

RequiredEmailField

Email

RequiredFloatField

Number

RequiredIntegerField

Number

RequiredMultilineTextField

Long text

RequiredNumberField

Number

RequiredPercentField

Percent

RequiredPhoneNumberField

Phone

RequiredRatingField

Rating

RequiredRichTextField

Rich text

RequiredSelectField

Single select

RequiredSingleLineTextField

Single line text

RequiredTextField

Single line text, Long text

RequiredUrlField

Url

Type Annotations

pyAirtable uses type annotations to provide hints to type checkers like mypy. Type annotations improve code readability and help catch errors during development.

Basic field types like TextField and IntegerField will have their types inferred from the field’s configuration. For example:

from pyairtable.orm import Model, fields as F

class Person(Model):
    class Meta: ...

    name = F.TextField("Name")
    account_id = F.IntegerField("Account ID")
    edited_by = F.LastModifiedByField("Last Modified By")

record = Person()
reveal_type(record.name)  # Revealed type is 'builtins.str*'
reveal_type(record.account_id)  # Revealed type is 'builtins.int*'
reveal_type(record.edited_by)  # Revealed type is 'pyairtable.api.types.CollaboratorDict'

You may need to provide type hints to complex fields that involve lists. See below for examples.

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:

  1. If you try to set its value to None (or, sometimes, to the empty string).

  2. 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.

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.

If you always want to memoize models retrieved from the API, you can set memoize = True in the Meta configuration for your model:

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 to control whether the ORM saves the models it creates for later reuse. If a model is configured to memoize by default, pass memoize=False to override it.

Retrieval function

Will it reuse saved models?

Will it call the API?

Model.all

Never

Always

Model.first

Never

Always

Model.from_record

Never

Never

Model.from_id

Yes

Yes, unless fetch=True

Model.from_ids

Yes

Yes, unless fetch=True

LinkField.populate

Yes

Yes, unless lazy=True

SingleLinkField.populate

Yes

Yes, unless lazy=True

Comments

You can use Model.comments and Model.add_comment to interact with comments on a particular record, just like their Table equivalents:

>>> record = YourModel.from_id("recMNxslc6jG0XedV")
>>> comment = record.add_comment("Hello, @[usrVMNxslc6jG0Xed]!")
>>> record.comments()
[
    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()
>>> record.comments()[0].text
'Never mind!'
>>> comment.delete()

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 AttachmentDict for 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 Metadata

Access to the configuration of a model and the schema of its underlying base/table are available through the meta attribute:

>>> model = YourModel()
>>> model.meta.base_id
'appaPqizdsNHDvlEm'
>>> model.meta.table_name
'YourModel'
>>> model.meta.table.schema()
TableSchema(id='appaPqizdsNHDvlEm', name='YourModel', ...)

For convenience, the schema of ORM-defined fields can be accessed via those field definitions:

>>> YourModel.name.field_schema()
FieldSchema(id='fldMNxslc6jG0XedV', name='Name', type='singleLineText', ...)

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().