def get_scores():
"""
function that gets the scores from the airtable base
"""
phone_number = str(request.args.get('number'))
airtable = Airtable(AIRTABLE_BASE_ID, 'Input')
airtable_data_dict = {}
score_list = []
for page in airtable.get_iter(view='nps',
filterByFormula="({Twilio_Phone_Number}=" +
phone_number + ")"):
for record in page:
num_id = record['fields']['ID']
airtable_data_dict[num_id] = {}
airtable_data_dict[num_id]['score'] = record['fields']['Score']
airtable_data_dict[num_id]['reason'] = record['fields']['Reason']
airtable_data_dict[num_id]['comments'] = record['fields'][
'Comments']
score_list.append(record['fields']['Score'])
nps_total_score = calc_nps(score_list)
return {'overallNPS': nps_total_score, 'airtableData': airtable_data_dict}
def main():
try:
# these keys have been set up Github /websites repo secrets already
airtable = Airtable(
os.environ["AIRTABLE_TEAM_BASE_KEY"],
TABLE_NAME,
api_key=os.environ["AIRTABLE_API_KEY"],
)
except KeyError:
print("Couldn't find airtable base key or api key")
exit(1)
team_list = {}
for page in airtable.get_iter(view=VIEW_NAME):
for record in page:
new_values = record["fields"]
print(new_values)
# print(new_values.get("team"))
if valid_entry(new_values):
if team_list.get(new_values.get("team")) is not None:
team_list[new_values.get("team")].append(
new_values.get("name"))
else:
team_list[new_values.get("team")] = []
with open(OUTPUT_PATH, "w") as f:
json.dump(team_list, f, indent=2, sort_keys=True)
def fetchall(self, api: Airtable, **kwargs) -> GenAny:
"""Given an airtable api object, generate all of the records in the
associated table
"""
for page in api.get_iter(**kwargs):
for record in page:
yield Model.from_airtable(record)
class AirtableImporter(DatasetImporter):
class Meta:
resource = PersonResource()
def init(self):
self.__airtable = Airtable(self.configuration['base_id'],
self.configuration['table_name'],
api_key=settings.AIRTABLE_API_KEY)
self.__pages = self.__airtable.get_iter()
def options_form(self, *args, **kwargs):
return AirtableImporterForm(*args, **kwargs)
def next_page(self):
#FIXME: These field names are hardcoded, very EBFE specific
COLUMNMAP = dict(email=self.configuration['email_column'],
name=self.configuration['name_column'],
address=self.configuration['address_column'])
ret = tablib.Dataset(headers=COLUMNMAP.keys())
page = self.__pages.next()
for row in page:
rowData = ()
for importKey, airtableKey in COLUMNMAP.iteritems():
rowData += (row['fields'].get(airtableKey), )
ret.append(rowData)
return ret
class AirtableImporter(DatasetImporter):
class Meta:
resource = PersonResource()
def __init__(self):
super(AirtableImporter, self).__init__()
self.__airtable = Airtable(
settings.AIRTABLE_BASE_ID,
settings.AIRTABLE_TABLE_NAME,
api_key=settings.AIRTABLE_API_KEY)
def init(self):
self.__pages = self.__airtable.get_iter()
def next_page(self):
#FIXME: These field names are hardcoded, very EBFE specific
COLUMNMAP = dict(
email = settings.AIRTABLE_EMAIL_COLUMN,
name = settings.AIRTABLE_NAME_COLUMN,
address = settings.AIRTABLE_ADDRESS_COLUMN,
)
ret = tablib.Dataset(headers=COLUMNMAP.keys())
page = self.__pages.next()
for row in page:
rowData = ()
for importKey, airtableKey in COLUMNMAP.iteritems():
rowData += (row['fields'].get(airtableKey),)
ret.append(rowData)
return ret
def create_lookup(base_id, table_name, view_name):
lookup = {}
airtable = Airtable(base_id, table_name)
for page in airtable.get_iter(view=view_name):
for record in page:
lookup[record['id']] = record['fields']
return lookup
def make_list_of_table(baseKey="app2VbjON28CVSlD1", table="Tasks", view='Grid view', key_column="Name", value_column="ID"):
airtable = Airtable(baseKey, table, api_key=AIRTABLE_API_KEY)
list = []
for page in airtable.get_iter(view=view, sort=key_column):
for record in page:
list.append((record['fields'][key_column], record['fields'][value_column]))
return list
def make_dictionary_of_table(baseKey="app2VbjON28CVSlD1", table="Tasks", view='Grid view', key_column="Name", value_column="ID"):
airtable = Airtable(baseKey, table, api_key=AIRTABLE_API_KEY)
dict = {}
for page in airtable.get_iter(view=view, sort=key_column):
for record in page:
dict.update({record['fields'][key_column]: record['fields'][value_column]})
return dict
def build(self):
api_key = 'keyq710GTffnK4vfA'
table_name = 'Config'
base_key = 'appdqzfZoeTcXC7VD'
airtable = Airtable(base_key=base_key, table_name=table_name, api_key=api_key)
self.title = 'Resonance Menu'
Config.set('kivy', 'window_icon', 'logo.ico')
pages = airtable.get_iter(maxRecords=100)
menus = generate_data_model(pages)
return generate_layout(BoxLayout(orientation='vertical'), menus, False)
def get_projecta_docs(projects: Airtable, people: Airtable,
institutions: Airtable, add_info: dict,
**options) -> Generator:
"""Generate the projecta documents and the documents of contacts and institutions from airtbale database.
Parameters
----------
projects : Airtable
The Projects database.
people : Airtable
The People database.
institutions : Airtable
The institutions database.
add_info : dict
A dictionary of the additional information.
options : dict
The view and filter options. Include
max_records (``int``, optional): The maximum total number of
records that will be returned. See :any:`MaxRecordsParam`
view (``str``, optional): The name or ID of a view.
See :any:`ViewParam`.
page_size (``int``, optional ): The number of records returned
in each request. Must be less than or equal to 100.
Default is 100. See :any:`PageSizeParam`.
fields (``str``, ``list``, optional): Name of field or fields to
be retrieved. Default is all fields. See :any:`FieldsParam`.
sort (``list``, optional): List of fields to sort by.
Default order is ascending. See :any:`SortParam`.
formula (``str``, optional): Airtable formula.
See :any:`FormulaParam`.
Yields
------
project : tuple
The key-value pair of project document.
people : list
The list of the key-value pairs of the people in the collaborators list.
institutions : list
The list of the key-value pairs of the institutions of those collaborators.
"""
for page in projects.get_iter(**options):
for record in page:
denormalize_project(record, people, institutions, inplace=True)
_project, _people, _institutions = parser.parse_project(
record, add_info)
yield _project, _people, _institutions
def get_cas_from_airtable(secret: str, app: str, table: str):
""" Get CAS from airtable. """
cas = CollectiveActions()
airtable = Airtable(app, table, api_key=secret)
pages = airtable.get_iter(maxRecords=1000)
records = []
for page in pages:
for record in page:
records.append(record["fields"])
for record in records:
if bool(record):
cas.append(
CollectiveAction(**record)
)
return cas
def get_table(table_name, base_id, api_key):
if request.method == 'POST':
# noinspection PyInterpreter
try:
# Set this to true to include linked table records.
# This will acts as a switch to quickly turn the functionality on and off.
include_linked = False
at = Airtable(base_id, table_name, api_key=api_key)
columns = dict()
# Get 20 records from the Airtable table and get their column names
for page in at.get_iter(maxRecords=5):
for record in page:
for field in record['fields']:
if include_linked and isinstance(record['fields'][field], list) and len(
record['fields'][field]) > 0 and isinstance(record['fields'][field][0], str) and \
record['fields'][field][0].startswith('rec'):
try:
linked_record = at.get(
record['fields'][field][0])
for linked_field in linked_record['fields']:
if not (
isinstance(
linked_record['fields']
[linked_field], list)
and len(linked_record['fields']
[linked_field]) > 0
and isinstance(
linked_record['fields']
[linked_field][0], str)
and linked_record['fields']
[linked_field][0].startswith('rec')):
events.update({
field + '::' + linked_field:
field + '::' + linked_field
})
except Exception as e:
pass
else:
columns.update({field: field})
except Exception as e:
print_traceback(e)
return None
def init(self):
membersTable = Airtable(settings.AIRTABLE_BASE_ID,
settings.AIRTABLE_TABLE_NAME,
api_key=settings.AIRTABLE_API_KEY)
eventsTable = Airtable(settings.AIRTABLE_BASE_ID,
'Events',
api_key=settings.AIRTABLE_API_KEY)
self.eventPages = iter(
eventsTable.get_iter(fields=[
'Name', 'Date/Time', 'Google Calendar ID', 'Volunteers'
]))
members = iter(membersTable.get_all(fields=['Email', 'Name']))
self.emailForAirtableId = {}
for member in members:
self.emailForAirtableId[member['id']] = member['fields'].get(
'Email')
class RemoveDuplicates:
'''
compares are given csv to airtable main database and
removes duplicates.
The script will compare 'Group name' and 'Resources' colums for similarities
'''
def __init__(self, csv_name):
self.api_key = 'keyVuEhB1SvC5cDQj'
self.base_key = 'app4FKBWUILUmUsE1'
self.main_table_name = 'Groups' # primary database
self.main_airtable = None
self.data_path = Path(Path(__file__).resolve().parent, "csv")
self.csv_name = csv_name
# check if the input table is main_table. if it is exit the script
# self._is_main_db() # <---*****be careful removing this line****
self.fetch_tables()
def _is_main_db(self):
'''
Protects the maind db from accidental editing
'''
try:
assert self.in_table_name != self.main_table_name
except AssertionError as e:
e.args += ("You're Trying to modify the main database! This is dangerous! Terminated!",)
raise
def fetch_tables(self):
self.main_airtable = Airtable(self.base_key, self.main_table_name, api_key=self.api_key)
def delete_dupes(self):
df = pd.read_csv(os.path.join(self.data_path, self.csv_name))
len_begin = len(df)
records_to_delete = []
for page in self.main_airtable.get_iter():
for record in page:
url_ids = record['fields'].get('Resources')
grp_name = record['fields'].get('Group name')
if url_ids:
for url in [self.main_airtable.get(i) for i in url_ids if i]:
df = df[~df['Resources'].str.contains(url['fields'].get('Url'))]
df = df[~df['Group name'].str.contains(grp_name)]
df.to_csv(os.path.join(self.data_path, 'helpwithcovid_clean_no_dupe.csv'), header=True, index=False)
print('Removed {} duplicate entriies and saved helpwithcovid_clean_no_dupe.csv in {}!'.format((len_begin-len(df)), self.data_path))
def getMenuData():
AT = Airtable(settings.AIRTABLE_DATABASE_ID,
'Config',
api_key=settings.AIRTABLE_API_KEY)
all_data = AT.get_all(formula="{Live}=TRUE()")
main = AT.get_iter(fields=['Main Menu'])
menu = {}
no_name_num = 1
for page in main:
for record in page:
menu[record['fields']['Main Menu']] = {}
for item in all_data:
aux = menu[item['fields']['Main Menu']]
if 'Name' in item['fields'].keys():
link_name = item['fields']['Name']
link = item['fields']['URL']
else:
print(item, no_name_num)
link_name = 'No Name URL #' + str(no_name_num)
link = item['fields']['URL']
no_name_num += 1
try:
s_menu = item['fields']['Sub-menu']
if s_menu not in aux.keys():
aux[s_menu] = {link_name: link}
else:
aux[s_menu][link_name] = link
except:
if 'No Name Sub-Menu' not in aux.keys():
aux["No Name Sub-Menu"] = {link_name: link}
else:
aux["No Name Sub-Menu"][link_name] = link
return menu
def update_data_file(airtable_base_key, view_name, table_name, needed_fields,
path):
try:
airtable = Airtable(
airtable_base_key,
table_name,
api_key=os.environ["AIRTABLE_API_KEY"],
)
except KeyError:
print("Couldn't find airtable api key environment variable")
exit(1)
value_list = []
for page in airtable.get_iter(view=view_name):
for record in page:
new_values = record["fields"]
if valid_entry(new_values, needed_fields):
value_list.append(new_values)
with open(path, "w") as f:
json.dump(value_list, f, indent=2, sort_keys=True)
def main():
try:
# these keys have been set up Github /websites repo secrets already
airtable = Airtable(
os.environ["AIRTABLE_BASE_KEY"],
"news",
api_key=os.environ["AIRTABLE_API_KEY"],
)
except KeyError:
print("Couldn't find airtable base key or api key")
exit(1)
media_list = []
for page in airtable.get_iter(view="^^dont_change_news_name"):
for record in page:
new_values = record["fields"]
if valid_entry(new_values):
media_list.append(new_values)
with open("../assets/data/medialist.json", "w") as f:
json.dump(media_list, f, indent=2)
def main():
try:
# these keys will need to be set up in your Github repo as secrets
airtable = Airtable(
os.environ["AIRTABLE_CONTENT_BASE_KEY"],
TABLE_NAME,
api_key=os.environ["AIRTABLE_API_KEY"],
)
except KeyError:
print("Couldn't find airtable base key or api key")
exit(1)
media_list = []
for page in airtable.get_iter(view=VIEW_NAME):
for record in page:
new_values = record["fields"]
if valid_entry(new_values):
media_list.append(new_values)
with open(OUTPUT_PATH, "w") as f:
json.dump(media_list, f, indent=2, sort_keys=True)
# print(finance_19.get_all(view = 'Withholding', sort = 'Purchase ID'))
page = 0
record = 0
i = 0
record_item = []
id = 0
arr_cutting = []
account_payable = []
payable_data = []
#Getting all the record of Account Payable
account_payable = account_payable_table.get_all(view='Grid view', sort='Name')
#Getting all the record of Purchase Item with withholding tax
for page in finance_19.get_iter(view='Withholding', sort='Purchase ID'):
for record in page:
record_item.append(record)
print(len(record_item)) #checking len of record in purchase item
main_view = finance_19.get_all(
view='Main View',
sort='Purchase ID') #Get all the record from main view for get the data
#Cutting text (Short Description) and delete whitespace
for i in range(len(record_item)):
if round(record_item[i]['fields']['Tax Withholding Amount'], 2) > 0:
word_cutting = word_tokenize(
record_item[i]['fields']['Short Description'],
engine="deepcut",
for index, row in input_file.iterrows():
base_key = row['Application_ID']
table_name = row['Table_Name']
legacy = row['Legacy']
application_name = row['Application_name']
view_name = row['View_Name']
column_names = json.loads(row['Column_Names'])
print(application_name)
columnvalues = list(column_names.values())
airtable_data = Airtable(base_key, table_name, api_key=API_Key)
rows = []
for page in airtable_data.get_iter(view=view_name,
formula="FIND('Published', {" +
column_names['Status'] + "})=1",
fields=columnvalues):
for record in page:
row = {}
row['Application_Name'] = application_name
row['Legacy'] = legacy
row['Table_Name'] = table_name
row['View_Name'] = view_name
for key in list(column_names):
if column_names[key] in record['fields']:
row[key] = record['fields'][column_names[key]]
if column_names['Publish Date'] in record['fields']:
dt = datetime.strptime(
record['fields'][column_names['Publish Date']], '%Y-%m-%d')
class AirtableClient:
def __init__(self, conf: AirtableConfig, airtable_name, table_spec,
read_only):
self.read_only = read_only
self.client = Airtable(
conf.base_id,
airtable_name,
conf.api_key,
)
self.table_spec = table_spec
def get_all(self, formula=None):
return (self.table_spec.model_cls.from_airtable(raw)
for page in self.client.get_iter(formula=formula)
for raw in page)
def get_all_with_new_status(self):
# TODO : sort by creation time asc
# NOTE here is a formula for querying on a blank status
# TODO : get rid of this if we don't need it
# "IF("
# "{{Status}} = BLANK(),"
# # If blank...
# "{{_meta_last_seen_status}} != \"{blank_sentinel}\","
# # If not blank...
# "{{Status}} != {{_meta_last_seen_status}}"
# ")"
return self.get_all(formula=("AND({Status} != BLANK(), "
"{Status} != {_meta_last_seen_status})"))
def update(self, model):
if self.read_only:
logger.info(f"Not updating {model.id} in read-only mode")
return
self.client.update(
model.id,
model.to_airtable()["fields"],
)
# TODO : handle missing statuses (e.g. airtable field was updated)
def poll_table(self,
conf,
max_num_retries=DEFAULT_POLL_TABLE_MAX_NUM_RETRIES):
logger.info("Polling table: {}".format(self.table_spec.name))
success = True
callbacks = {
status: cb(conf)
for status, cb in self.table_spec.status_to_cb.items()
}
for record in self.get_all_with_new_status():
assert record.status is not None
logger.info(
f"Processing '{self.table_spec.name}' record: {record}")
try:
original_id = record.id
original_status = record.status
cb = callbacks.get(record.status)
if cb is None:
logger.info("No callback for record with status "
f"'{record.status}': {record.id}")
continue
for num_retries in range(max_num_retries):
try:
cb(record) # noqa: F841
break
except Exception:
logger.exception(
f"Callback '{cb.__qualname__}' for record failed "
f"(num retries {num_retries}): {record.id}")
else:
logger.error(
f"Callback '{cb.__qualname__}' for record did not "
f"succeed: {record.id}")
success = False
if original_id != record.id:
raise ValueError(
f"Callback '{cb.__qualname__}' modified the ID of the "
f"record: original={original_id}, new={record.id}")
finally:
record.meta_last_seen_status = original_status
# Update the record in airtable to reflect local modifications
self.update(record)
return success
import pandas as pd
from sklearn.model_selection import train_test_split
from airtable import Airtable
airtable = Airtable('appwyZMLKcg4sYvpC', 'RC', api_key='keyMqApx7X0uKHXu6')
rc_data = []
for page in airtable.get_iter():
for record in page:
rc_data.append({
'code':
record['fields']['2'],
'uf':
record['fields']['3'],
'type':
record['fields']['4'],
'rapporteur':
record['fields']['5'],
'date':
record['fields']['6'],
'class':
record['fields']['7'],
'court_decision':
record['fields']['10'],
'process_type':
record['fields']['13'],
'process_number':
record['fields']['15'],
'court_type':
record['fields']['15'],
'city':
record['fields']['18'],
else:
print("Creating source")
source = Source(**source_details)
session.add(source)
## Drop existing data from
## Connect to AirTable
base_id = 'appKjedGx8WllrhjV'
# Get our lookup tables
topics = create_lookup(base_id, "Topics", "Grid view")
user_need = create_lookup(base_id, "User Needs", "Grid view")
airtable = Airtable(base_id, "Charity Messages")
for page in airtable.get_iter(view="MASTER"):
for record in page:
need = {}
need['technologyNeeds'] = record['fields'].get('Message', '')
need['requesterName'] = record['fields'].get('From', '')
try:
need['requestDate'] = dateutil.parser.parse(
record['fields'].get('Date', None))
except TypeError:
pass
need['requestState'] = map_request_state(record['fields'].get(
'Status', ''))
need['source'] = source
if (record['fields'].get('From', None)):
organisation = session.query(Organisation).filter(
