vilarneto / pyrtable Goto Github PK

View Code? Open in Web Editor NEW

9.0 1.0 1.0 250 KB

Django-inspired, Python 3.x library to interface with Airtable

License: MIT License

Python 100.00%

python airtable airtable-api python-3 python3 orm python38 python-3-8 python-3-9 python39

pyrtable's Introduction

Pyrtable: Python framework for interfacing with Airtable

Pyrtable is a Python 3 library to interface with Airtable's REST API.

There are other Python projects to deal with Airtable. However, most of them basically offer a thin layer to ease authentication and filtering – at the end, the programmer still has to manually deal with JSON encoding/decoding, pagination, request rate limits, and so on.

Pyrtable is a high-level, ORM-like library that hides all these details. It performs automatic mapping between Airtable records and Python objects, allowing CRUD operations while aiming to be intuitive and fun. Programmers used to Django will find many similarities and will (hopefully) be able to interface with Airtable bases in just a couple of minutes.

What does it look like?

Ok, let's have a taste of how one can define a class that maps onto records of a table:

import enum
from pyrtable.record import BaseRecord
from pyrtable.fields import StringField, DateField, SingleSelectionField, \
        SingleRecordLinkField, MultipleRecordLinkField

class Role(enum.Enum):
    DEVELOPER = 'Developer'
    MANAGER = 'Manager'
    CEO = 'C.E.O.'

class EmployeeRecord(BaseRecord):
    class Meta:
        # Open “Help > API documentation” in Airtable and search for a line
        # starting with “The ID of this base is XXX”.
        base_id = 'appABCDE12345'
        table_id = 'Employees'

    @classmethod
    def get_api_key(cls):
        # The API Key can be generated in you Airtable Account page.
        # DO NOT COMMIT THIS STRING!
        return 'keyABCDE12345'

    name = StringField('Name')
    birth_date = DateField('Birth date')
    office = SingleRecordLinkField('Office', linked_class='OfficeRecord')
    projects = MultipleRecordLinkField(
            'Allocated in projects', linked_class='ProjectRecord')
    role = SingleSelectionField('Role', choices=Role)

After that, common operations are pretty simple:

# Iterating over all records
for employee in EmployeeRecord.objects.all():
    print("%s is currently working on %d project(s)" % (
        employee.name, len(employee.projects)))

# Filtering
for employee in EmployeeRecord.objects.filter(
        birth_date__gte=datetime.datetime(2001, 1, 1)):
    print("%s was born in this century!" % employee.name)

# Creating, updating and deleting a record
new_employee = EmployeeRecord(
    name='John Doe',
    birth_date=datetime.date(1980, 5, 10),
    role=Role.DEVELOPER)
new_employee.save()

new_employee.role = Role.MANAGER
new_employee.save()

new_employee.delete()

Notice that we don't deal with Airtable column or table names once record classes are defined.

Beyond the basics

Keep in mind that Airtable is not a database system and is not really designed for tasks that need changing tons of data. In fact, only fetch (list) operations are batched – insert/update/delete operations are limited to a single record per request, and Airtable imposes a 5 requests per second limit even for paid accounts. You will need a full minute to update 300 records!

That said, Pyrtable will respect that limit. In fact, it will track dirty fields to avoid unnecessary server requests and will render .save() calls as no-ops for unchanged objects. That also works with multiple threads, so the following pattern can be used to update and/or create several records:

from concurrent.futures.thread import ThreadPoolExecutor

all_records = list(EmployeeRecord.objects.all())

# Do operations that change some records here
# No need to keep track of which records were changed

with ThreadPoolExecutor(max_workers=10) as executor:
    for record in all_records:
        executor.submit(record.save)

Or, if you want a really nice tqdm progress bar:

from tqdm import tqdm

with ThreadPoolExecutor(max_workers=10) as executor:
    for _ in tqdm(executor.map(lambda record: record.save(), all_records),
                  total=len(all_records), dynamic_ncols=True, unit='',
                  desc='Updating Airtable records'):
        pass

Pyrtable also has some extra tools to cache data and to store authentication keys in JSON/YAML files or in an environment variable. Remember to never commit sensitive data to your repository, as Airtable authentication allows full R/W access to all your bases with a single API Key!

Compatibility

Pyrtable is compatible with Python 3.8 and above. Python 2.x is not supported at all.

Documentation

Technical documentation is available at https://pyrtable.readthedocs.io.

Questions, bug reports, improvements

Want to try it out, contribute, suggest, offer a hand? Great! The project is available at https://github.com/vilarneto/pyrtable.

License

Pyrtable is released under MIT license.

pyrtable's People

Contributors

Stargazers

Watchers

Forkers

bellyfat

pyrtable's Issues

Python 3.9 DeprecationWarning for Python 3.10

While using pyrtable in 3.9 the following warning is issued. Python 3.10 was just released and, as it states, this will not work.

python3.9/site-packages/pyrtable/fields/valueset.py:12: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated since Python 3.3, and in 3.10 it will stop working
    class ValueSet(collections.MutableSet, collections.Iterable, Generic[T]):

P.S. really enjoy using this in our integration projects with Airtable!

Attachment upload

Is there / will there be attachment upload support?

Error on update (pyrtable==0.7.10)

save method works but I'm getting an error on update:

class Devices(MyBaseRecord):
    class Meta:
        table_id = 'Devices'

    name = StringField('Name')
    description = StringField('Description')


class Ips(MyBaseRecord):
    class Meta:
        table_id = 'Ips'

    id = IntegerField('Id')
    internal = StringField('Internal')
    external = StringField('External')
    device = SingleRecordLinkField('Device', linked_class=Devices)


device = Devices(name="pc", description="My pc")
device.save()

ip = Ips(internal="192.168.100.15", external="181.143.142.160", device=device)
ip.save() # It works

ip.internal = "192.168.100.16"
ip.save() # Error

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-50-7a5a8299798b> in <module>
----> 1 ip.save()

/usr/local/lib/python3.9/site-packages/pyrtable/record.py in save(self)
    214         """
    215         from pyrtable.context import get_default_context
--> 216         get_default_context().save(self.__class__, self)
    217
    218     def encode_to_airtable(self, include_non_dirty_fields=False) -> Dict[str, Any]:

/usr/local/lib/python3.9/site-packages/pyrtable/context/base.py in save(self, record_cls, record)
    158             self._create(record_cls, record)
    159         else:
--> 160             self._update(record_cls, record)
    161
    162     def delete_id(self, *,

/usr/local/lib/python3.9/site-packages/pyrtable/context/base.py in _update(self, record_cls, record)
    141             if 400 <= response.status_code < 500:
    142                 error = response.json().get('error', {})
--> 143                 error_message = error.get('message', '')
    144                 error_type = error.get('type', '')
    145

AttributeError: 'str' object has no attribute 'get'

Escaping apostrophes in Table.objects.filter(stringColumnName="a string with ' apostrophes in it" may not be necessary

In filterutils.py, the quote value routine. I have apostrophes in the data of one of my string columns in one of my AirTable
tables. The return '"%s"' % re.sub(r'(["\'\\])', lambda ch: '\\' + ch.group(0), value) is escaping apostrophes.

An example is "augmentin '400'" becomes "augmentin \'400\' before being sent in the filterByFormula querystring field
to AirTable and then MyTable.objects.filter(stringColumnName="augmentin '400'") yields no results. I don't know if the newer AirTable API automatically escapes apostrophes itself so you don't have to. Looks like we are using pyrtable==0.7.11 from pip freeze.

def quote_value(value) -> str:
    import datetime

    if isinstance(value, str):
        return '"%s"' % re.sub(r'(["\'\\])', lambda ch: '\\' + ch.group(0), value)
    if isinstance(value, bool):
        return 'TRUE()' if value else 'FALSE()'
    if isinstance(value, (int, float)):
        return str(value)
    if isinstance(value, Enum):
        return quote_value(value.value)
    if isinstance(value, datetime.datetime):
        return format(value, '"%Y-%m-%dT%H:%M:%S.%fZ"')
    if isinstance(value, datetime.date):
        return format(value, '"%Y-%m-%d"')
    raise ValueError(value)

I'm doing this absolutely filthy patch of the code below for the moment to exclude apostrophes from being escaped. When you have some time, perhaps you could create a small Airtable with a single column and put a record in there with an apostrophe in the string to see if your objects.filter function also fails to find the record with an apostrophe in it.

    def my_quote_value(value) -> str:
        import datetime
        import re
        from enum import Enum
        if isinstance(value, str):
            return '"%s"' % re.sub(r'(["\\])', lambda ch: '\\' + ch.group(0), value)
        if isinstance(value, bool):
            return 'TRUE()' if value else 'FALSE()'
        if isinstance(value, (int, float)):
            return str(value)
        if isinstance(value, Enum):
            return my_quote_value(value.value)
        if isinstance(value, datetime.datetime):
            return format(value, '"%Y-%m-%dT%H:%M:%S.%fZ"')
        if isinstance(value, datetime.date):
            return format(value, '"%Y-%m-%d"')
        raise ValueError(value)
    import pyrtable.filterutils
    pyrtable.filterutils.quote_value=my_quote_value

ValueError: Cannot use base_id

Hey, I'm following your example but I'm getting the following error:

    266                     function = functools.partial(function, base_id=base_id)
    267                 else:
--> 268                     raise ValueError('Cannot use base_id')
    269
    270             result['Authorization'] = 'Bearer %s' % function()

ValueError: Cannot use base_id

I'm sure the API key and base id are correct because I tried the same using curl

Batch record creation/updating/deleting

Currently BaseRecord.save() and BaseRecord.delete() operations operate on single records.

It's interesting to offer an alternative form to these operations that take advantage of the batching capabilities of Airtable API for these operations (currently limited to 10 records per request).