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(