I have to disagree with you though about my code being incorrect. :stuck_out_tongue_winking_eye: My example was tested in Python3 using the airtable-python-wrapper against a live API. The escaping of newline characters and tabs is necessary, otherwise—as I previously mentioned—it raises an error:
Hmm, I thought it had to be a literal newline, maybe I was mistaken, or maybe they changed it since then. It seems it will interpret "\n" and "\\n" as a newline, so that one is optional. It doesn’t do the same for the tab, though, so the tab should not be escaped.
Here is my test using your code (I deleted the comments and blank lines, but did not change the code):
# ===== Code from here: https://mrcoles.com/escape-variables-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:
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:
val = (
val.replace("\r", "") # strip windows carriage returns
.replace("\\", "\\\\") # escape backslashes
.replace("\n", "\\n") # escape line feeds
.replace("\t", "\\t") # escape tabs
)
if wrap_is_double_quotes:
return val.replace('"', '\\"')
else:
return val.replace("'", "\\'")
# ===== My code to test it =====
import os
import urllib.request
import urllib.parse
base = os.environ.get('AIRTABLE_BASE')
key = os.environ.get('AIRTABLE_API_KEY')
url = f'https://api.airtable.com/v0/{base}/testing%20escape%20functions'
# These are the values that I have in airtable. If I escape them correctly,
# they will match and return the record with it as a value
airtable_values = [
"abc def",
"abc\ndef",
"abc\tdef",
'abc "def" ghi',
"abc 'def' ghi",
"abc \\ def",
"abc\\\ndef",
"abc\\ndef",
]
for airtable_value in airtable_values:
escaped = airtable_escape_variable(airtable_value)
print(f'===== {repr(airtable_value)} -> {repr(escaped)} =====')
params = urllib.parse.urlencode({'filterByFormula': '{Value}=%s' % escaped})
request = urllib.request.Request(f'{url}?{params}')
request.add_header("Authorization", f'Bearer {key}')
print(urllib.request.urlopen(request).read().decode('utf-8'))
print()
Here is the data I’m testing it against:
$ curl "https://api.airtable.com/v0/$AIRTABLE_BASE/testing%20escape%20functions" \
-H "Authorization: Bearer $AIRTABLE_API_KEY" \
--silent \
| jq -c '.records[].fields'
{"Value":"abc\tdef"}
{"Value":"abc \"def\" ghi"}
{"Value":"abc def"}
{"Value":"abc\\\ndef"}
{"Value":"abc \\ def"}
{"Value":"abc\ndef"}
{"Value":"abc 'def' ghi"}
{"Value":"abc\\ndef"}
And here is the output, to my surprise, the newline worked, to yours, the tab doesn’t, so I guess we’re all surprised here :stuck_out_tongue_closed_eyes: Anyway, if you remove the .replace("\t", "\\t") it will work.
$ python3 omg.py
===== 'abc def' -> '"abc def"' =====
{"records":[{"id":"recBibipUDVVydD0K","fields":{"Value":"abc def"},"createdTime":"2021-09-25T14:42:13.000Z"}]}
===== 'abc\ndef' -> '"abc\\ndef"' =====
{"records":[{"id":"recUzj6267kQDoS1b","fields":{"Value":"abc\ndef"},"createdTime":"2021-09-25T13:56:38.000Z"}]}
===== 'abc\tdef' -> '"abc\\tdef"' =====
{"records":[]}
===== 'abc "def" ghi' -> '"abc \\"def\\" ghi"' =====
{"records":[{"id":"recBINRpQ8W99yOfa","fields":{"Value":"abc \"def\" ghi"},"createdTime":"2021-09-25T13:56:38.000Z"}]}
===== "abc 'def' ghi" -> '"abc \'def\' ghi"' =====
{"records":[{"id":"recx8KmvWuDkhEPPT","fields":{"Value":"abc 'def' ghi"},"createdTime":"2021-09-25T15:04:59.000Z"}]}
===== 'abc \\ def' -> '"abc \\\\ def"' =====
{"records":[{"id":"recMLYuYH3bNzH3X9","fields":{"Value":"abc \\ def"},"createdTime":"2021-09-25T13:56:38.000Z"}]}
===== 'abc\\\ndef' -> '"abc\\\\\\ndef"' =====
{"records":[{"id":"recKB7WYh4Rn7fyCB","fields":{"Value":"abc\\\ndef"},"createdTime":"2022-01-13T03:07:21.000Z"}]}
===== 'abc\\ndef' -> '"abc\\\\ndef"' =====
{"records":[{"id":"recy5ApyF2LWREZPj","fields":{"Value":"abc\\ndef"},"createdTime":"2022-01-13T03:12:12.000Z"}]}
