Skip to content

Instantly share code, notes, and snippets.

@mrcoles
Last active Sep 20, 2021
Embed
What would you like to do?
A helper function for escaping variables passed into formulas in the Airtable API
from typing import Any, Optional, Union
from decimal import Decimal
class AirtableBadTypeException(Exception):
def __init__(self, val: Any):
self.val = val
super().__init__(f"unexpected type variable type: {type(val)}")
def airtable_escape_variable(val: Optional[Union[str, int, float, bool]]) -> str:
"""
Escape `val` so it can be passed into an Airtable formula, e.g.,
None:
> None -> "BLANK()"
strings:
> 'hello,\nit\'s "me"' -> '"hello,\\nit\'s \\"me\\""'
Notice that the string is wrapped in quotes (similar to how
JSON.stringify works), so it can be used directly in a formula.
numbers:
> 1 -> '1'
> 1.2 -> '1.2'
> Decimal('1.23') -> '1.23'
booleans:
> True -> '1'
> False -> '0'
It may be used like: `f'{{email}} = {airtable_escape_variable(email)}'`
"""
if val is None:
return "BLANK()"
if isinstance(val, str):
escaped_val = airtable_escape_str(val)
return f'"{escaped_val}"'
if isinstance(val, bool):
return str(1 if val else 0)
if isinstance(val, (int, float, Decimal)):
return str(val)
raise AirtableBadTypeException(val)
def airtable_escape_str(val: str, wrap_is_double_quotes=True) -> str:
"""
Escape special characters so val can be used as a string in an
Airtable formula
"""
val = (
val.replace("\r", "") # strip windows carriage returns
.replace("\\", "\\\\") # escape backslashes
.replace("\n", "\\n") # escape line feeds
.replace("\t", "\\t") # escape tabs
)
# escape quotes, can't indiscriminately escape both, have to pick
# just one and wrap with the other
if wrap_is_double_quotes:
return val.replace('"', '\\"')
else:
return val.replace("'", "\\'")
from airtable_escape import airtable_escape_variable, AirtableBadTypeException
if __name__ == "__main__":
tests = [
(None, "BLANK()"),
(1, "1"),
(1.2, "1.2"),
(Decimal("1.23"), "1.23"),
(True, "1"),
(False, "0"),
("hey you", '"hey you"'),
("hey you\\", '"hey you\\\\"'),
("hey\nyou", '"hey\\nyou"'),
("hey\\\nyou", '"hey\\\\\\nyou"'),
('hey\\\nyou "it\'s me"', '"hey\\\\\\nyou \\"it\'s me\\""'),
]
for val, expected in tests:
actual = airtable_escape_variable(val)
assert (
expected == actual
), f"fail for {val}\n\texpected: {expected}\n\tactual: {actual}"
print(f"✅ {val} -> {actual}")
try:
val = airtable_escape_variable({})
except AirtableBadTypeException as e:
print(f"✅ expected {e}")
else:
assert False, f"expected bad type exception, got: {val}"
print("Passed all tests!")
@mrcoles
Copy link
Author

mrcoles commented Sep 20, 2021

You may use this like:

from airtable import Airtable

# given the variables: base, table, email

client = Airtable(base, table, api_key=settings.AIRTABLE_KEY)
client.get_all(formula=f'{{email}} = {airtable_escape_variable(email)}')

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment