def main(): """Main entry point of airtable_bot""" airtable = Airtable(config["base-key"], "Sources", api_key=config["api-key"]) new_records = airtable.get_all(view="New records", maxRecords=2000) logger.info("Deleting empty records") empty_records = [x["id"] for x in new_records if empty_record(x)] if empty_records: logger.info("Deleted %s empty records" % len(empty_records)) airtable.batch_delete(empty_records) new_records = [x for x in new_records if not empty_record(x)] logger.info("Deduplicating") deduplicate(new_records, airtable=airtable) logger.info("Adding background information") new_records = airtable.get_all(view="New records", maxRecords=2000) # List of functions that return background information data_sources = [crowdtangle_engagement] add_background_information(new_records, data_sources=data_sources, airtable=airtable) all_records = airtable.get_all(view="All records", maxRecords=2000) logger.info("Updating coding status") update_coding_status(all_records, airtable=airtable)
def getAirTableETLRecords(): try: key = getLoginDetails() # Get all the information from AirTable - USE YOUR OWN API KEY HERE ######################################################################################################## srcAirTbl = Airtable(baseKey, srcTblNme, api_key=key) famAirTbl = Airtable(baseKey, famTblNme, api_key=key) prdAirTbl = Airtable(baseKey, prdTblNme, api_key=key) tpeAirTbl = Airtable(baseKey, tpeTblNme, api_key=key) pmdAirTbl = Airtable(baseKey, pmdTblNme, api_key=key) ######################################################################################################## # Get all the table data from Airtable srcDat = srcAirTbl.get_all() famDat = famAirTbl.get_all() prdDat = prdAirTbl.get_all() tpeDat = tpeAirTbl.get_all() pmdDat = pmdAirTbl.get_all() # Convert the table data into DataFrame format #srcDat = pd.DataFrame.from_records((r['fields'] for r in srcDat)) # ------------------------------------------------------------------------------------------------------------- retTbls = [srcDat, famDat, prdDat, tpeDat, pmdDat] except Exception as e: retTbls = ['F', str(e), '', '', ''] return retTbls
def fetch_all_records(keys_only: bool = False) -> Dict[str, ATRecord]: """ Get all records from Airtable, returning a map from key to record. If multiple records are found for a given key, the preferred record (as determined by the formats module) is kept, and all the non-preferred records are deleted from Airtable. """ context = MC.get() objects = "keys" if keys_only else "records" prinl(f"Loading all {context} {objects} from Airtable...") at_key, at_base, at_table, _ = MC.at_connect_info() at = Airtable(at_base, at_table, api_key=at_key) if keys_only: field_map = MC.core_field_map() fields = [field_map[MC.an_key_field()], field_map["Timestamp (EST)"]] all_records = at.get_all(fields=fields) else: all_records = at.get_all() prinlv( f"Found {len(all_records)} {objects}; looking for duplicates...") results: Dict[str, ATRecord] = {} to_delete = [] for record_dict in all_records: record = ATRecord.from_record(record_dict) if record: if not keys_only and (existing := results.get(record.key)): if record.is_preferred_to(existing): results[record.key] = record to_delete.append(existing.record_id) else: to_delete.append(record.record_id) else: results[record.key] = record
def handleRouteDelete(request): """ Handler for Salesforce JSON request to delete route in Salesforce, returns None. Argument request of the form: request = { "Method" : "ROUTE_DELETE", "Date" : Date, "Base Name" : Base_Name } """ # Retrieves all records associated with the Base for the date. routeStops = AirtableEntry.objects.filter( base_name__exact=request["Base Name"], stop_date__exact=request["Date"]) # Deletes the records from the Django Database. updateDatabase(request["Base Name"], jsonize(routeStops, method="Stop Removed")) # Deletes the records from Airtable. if datetime.strptime(request["Date"], "%Y-%m-%d").date() == datetime.now( pytz.timezone("US/Eastern")).date(): air = Airtable(request["Base Name"], "Table 1", os.environ['AIRTABLE_APIKEY']) for stop in air.get_all(): deleteStopInAirtable(air, stop["id"]) return True
class AirtableExporter(DatasetExporter): class Meta: resource = PersonResource def init(self): self.airtable = Airtable(settings.AIRTABLE_BASE_ID, settings.AIRTABLE_TABLE_NAME, api_key=settings.AIRTABLE_API_KEY) self.members = self.airtable.get_all() def export_page(self, page, dry_run=False): for row in page.dict: self.export_person(row, dry_run=dry_run) def export_person(self, row, dry_run): rowEmail = row['email'].strip().lower() for m in self.members: memberEmail = m['fields'].get(settings.AIRTABLE_EMAIL_COLUMN, '').strip().lower() if memberEmail == rowEmail: return log.info('Creating %s <%s>', row['name'], row['email']) if not dry_run: self.airtable.insert({ settings.AIRTABLE_NAME_COLUMN: row['name'], settings.AIRTABLE_EMAIL_COLUMN: row['email'], })
def updateOrderStatus(): airtable = Airtable(base_key, 'Order Status', api_key) records = airtable.get_all() airOrders = [] counter = 2 for rec in records: print(rec['fields'].keys())
def getagentinfo_byrecordid(recordid): airtable = Airtable(BASE_KEY, 'member', api_key=API_KEY) if isinstance(recordid, list): recordlist = airtable.get_all() agentlist = [ i['fields']['Agent'] for i in recordlist if i['id'] in recordid ] agent = (', ').join(agentlist) agent_with_at = '@{}'.format((', @').join(agentlist)) usernamelist = [ i['fields']['User name'] for i in recordlist if i['id'] in recordid ] username = (', ').join(usernamelist) username_with_at = '@{}'.format((', @').join(usernamelist)) combinelist = [ '{} (@{})'.format(agentlist[i], usernamelist[i]) for i in range(0, len(agentlist)) ] combine = (', ').join(combinelist) else: record = airtable.get(recordid) agent = record['fields']['Agent'] agent_with_at = '@{}'.format(agent) username = record['fields']['User name'] username_with_at = '@{}'.format(username) combine = '{}({})'.format(agent, username_with_at) return agent, agent_with_at, username, username_with_at, combine, combinelist
def gettodayevent(): airtable = Airtable(BASE_KEY, 'events', api_key=API_KEY) records = airtable.get_all( filterByFormula="IS_SAME({Date}, TODAY(), 'day')") return records
def searcher(y): ts = int(os.path.getmtime('/home/scottcarlson/api/api.pickle')) then = datetime.utcfromtimestamp(ts) now = datetime.now() tdelta = now - then if tdelta.days < 1: with open('/home/scottcarlson/api/api.pickle', 'rb') as f: results = pickle.load(f) else: table_name = 'xxx' base_key = 'xxx' airtable = Airtable(base_key, table_name, api_key='xxx') results = airtable.get_all() with open('/home/scottcarlson/api/api.pickle', 'wb') as f: pickle.dump(results, f) potential_matches = [] for result in results: data = result['fields'] fuzzy_ratio = fuzz.token_sort_ratio(y, data['name']) if fuzzy_ratio >= 95: if data['type'] == 'primary': potential_matches.append({ 'match': data['name'], 'ratio': fuzzy_ratio }) elif data['type'] == 'variant': potential_matches.append({ 'match': data['use'], 'ratio': fuzzy_ratio }) newlist = sorted(potential_matches, key=itemgetter('ratio'), reverse=True) return newlist
def art(): base_key = os.environ['BASE'] # Airtable base key table_name = 'Art' # Airtable table name airtable = Airtable( base_key, table_name, api_key=os.environ['AIR_API'] ) # Airtable authentication with personal Airtable Account key # at = Airtable('BASE', 'AIR_API') # at.get('Art') records = airtable.get_all( sort="Business Name" ) # Get all the records from the table and sort them (default sort is ascending) # pprint(records) #this will print all the records from the table to the terminal in better format that print # TODO: code below from Coach Mike # add classes to build a filter for each page # updated_records = list() # # Loop through every record, then add a new property # for record in records: # categories = record['fields']['Things Sold'] # record['classNames'] = ' '.join(categories) # art_classes = () # art_classes.append(airtable.get) return render_template( "art.html", art_businesses=records ) # render the art.html page and create a variable called businesses and store the records from the airtable base
def fetch_dropboxes(self): redis = KSVotesRedis() airtable = Airtable(os.getenv('AIRTABLE_EV_BASE_ID'), os.getenv('AIRTABLE_DROPBOX_TABLE'), os.getenv('AIRTABLE_EV_KEY')) response = airtable.get_all( formula="AND( COUNTY = '{}' )".format(self.county.upper())) if response is None or len(response) == 0: return # some counties do not have actual locations if 'LOCATION' not in response[0]['fields']: return boxes = [] for db in response: evl = { 'location': db['fields']['LOCATION'], 'hours': db['fields']['HOURS'] } boxes.append(evl) redis.set(self.cache_key(), json.dumps(boxes).encode(), os.getenv('EVL_TTL', '3600')) return boxes
def fetch_locations(self): redis = KSVotesRedis() airtable = Airtable(os.getenv('AIRTABLE_EV_BASE_ID'), os.getenv('AIRTABLE_EV_TABLE'), os.getenv('AIRTABLE_EV_KEY')) response = airtable.get_all( formula="AND( COUNTY = '{}' )".format(self.county.upper())) if response is None or len(response) == 0: return # some counties do not have actual locations if 'LOCATION' not in response[0]['fields']: return locations = [] for loc in response: evl = {'location': loc['fields']['LOCATION'], 'hours': []} for field, value in loc['fields'].items(): if 'DAY' in field: evl['hours'].append({'day': field, 'time': value}) locations.append(evl) redis.set(self.cache_key(), json.dumps(locations).encode(), os.getenv('EVL_TTL', '3600')) return locations
def handleRouteUpdate(request): air = Airtable(request["Base Name"], "Table 1", os.environ['AIRTABLE_APIKEY']) (routeStops := list( AirtableEntry.objects.filter( base_name__exact=request["Base Name"], stop_date__exact=datetime.now( pytz.timezone("US/Eastern")).date()))).sort( key=lambda x: x.stop_number) # Retrieves all records from the database associated with the table and # the route date. for stop in air.get_all(): match = next( (route_stop for route_stop in routeStops if route_stop.stop_salesforce_id == stop["fields"]["Salesforce Id"]), None) stop["fields"]["Airtable Id"] = stop["id"] stop = stop["fields"] if match: updateEntryInDatabaseFromMatch(stop, match) deleteStopInAirtable(air, stop["Airtable Id"]) print(routeStops) for routeStop in jsonize(routeStops): addStopInAirtable(air, routeStop) return True
def mark_posted(): at_orange = Airtable(os.getenv('AT_APP_KEY_ORANGE'), 'Session hosting', os.getenv('AT_API_KEY')) for row in at_orange.get_all(): for num in range(3): col = f'videoid_{num}' if col in row['fields']: if checked(f'posted_{num}', row['fields']): continue url = "https://youtube.googleapis.com/youtube/v3/videos" url += f'?part=snippet,status,recordingDetails' url += f"&id={row['fields'][col]}" response = requests.get(url, headers=get_auth_header(access_token)) assert response.status_code == 200 body = response.json() assert len(body['items']) == 1 item = body['items'][0] if item['status']['privacyStatus'] == 'public': at_orange.update(row['id'], {f'posted_{num}': True})
def to_airtable(df_data_xpt, table_name, apikey, base_key, view): prefix = '!k4p4D4T4+' if prefix in apikey: enc_token = apikey.replace(prefix, "") apikey = base64.b64decode(enc_token).decode("utf-8") airtable = Airtable(base_key, table_name, api_key=apikey) # connect to table recs = airtable.get_all(view=view) # get all records in table #print(recs) if recs != []: df_tbl = create_dataframe(recs) # dataframe of records in the table remove_list = list(df_tbl['KEY']) df_cln = df_data_xpt[~df_data_xpt['KEY'].isin(remove_list)] #df_cln.fillna("", inplace=True) else: df_cln = df_data_xpt for i in df_cln.index.values: for col in df_cln.columns: value = df_cln.at[i, col] if date_check(value) == True and is_number(value) == False: value = timezone_sast(value) df_cln.at[i, col] = value records = df_cln.loc[[i]].dropna(axis=1).to_dict(orient='records')[0] #print(records) airtable.insert(records, typecast=True)
def get_emails(base, table): airtable = Airtable(base, table) airtable_emails = airtable.get_all(fields='Email') emails = [] for email in airtable_emails: emails.append(email['fields']['Email']) return emails
def getcleandata(apikey,base,table,view,field_coname,field_jobsurl, field_searchcount): # Load Airtable class and pass args airtable = Airtable(base, table, api_key=apikey) raw_records = airtable.get_all(view=view) clean_records = [] # print(raw_records) for record in raw_records: try: data = { 'recordid': record['id'], 'coname': record['fields'][field_coname][0], 'jobsurl': record['fields'][field_jobsurl], 'searchcount': record['fields'][field_searchcount] } clean_records.append(data) except KeyError: print( 'A KeyError has ocurred. Skipping record.', 'A mapping key is empty for the record with Company Name:', record['fields'][field_coname][0]+'.' ) return clean_records
def init(request): # connection with Airtable airtable = Airtable(base_key, 'previous_courses_data', api_key) # data processing template = get_template('diploma.html') for script in airtable.get_all(): is_project = script['fields'].get('is_project', None) current_course = script['fields'].get('course', None) if is_project == True and current_course == '16': name = script['fields']['first_name'] surname = script['fields']['last_name'] date = script['createdTime'] date = datetime.strptime(date, "%Y-%m-%dT%H:%M:%S.%fZ") locale.setlocale(locale.LC_TIME, "ru_RU") date = datetime.strftime(date, "%d %B %Y") html = render(request, 'diploma.html', {'name': name, 'surname': surname, 'date': date}) result = (html.content).decode('utf-8') config = pdfkit.configuration(wkhtmltopdf='/usr/local/bin/wkhtmltopdf') options = { 'dpi': 300, 'page-size': 'A4', 'orientation': 'landscape', 'margin-top': '0', 'margin-right': '0.', 'margin-bottom': '0.', 'margin-left': '0', 'encoding': "UTF-8", 'custom-header': [ ('Accept-Encoding', 'gzip') ], 'no-outline': None } pdfkit.from_string(result, 'result/' + '{}-{}.pdf'.format(name, surname), configuration=config, options=options) return render(request, 'diploma.html')
class ATable(): def __init__(self, api_key: str, base_id: str, table: str): self.tbl_obj = Airtable(base_id, table, api_key=api_key) self.table: list = self.tbl_obj.get_all() self.fields: list = list(self.table[0]['fields'].keys()) def table_search(self, column_name, search_value) -> (list, None): if column_name not in self.fields: print('"{}" is not a valid field name') return None results = self.tbl_obj.search(column_name, search_value) return results def get_contact_suggestions(self, n: int = 10, pers=True, prof=True, srch: str = "Chat > 0"): # TODO: Catch cases where format of srch string is incorrect records = self.tbl_obj.get_all(formula=srch) both_records = [ i for i in records if i['fields']['Category'] == 'Both' ] pers_records = [] prof_records = [] if pers: pers_records = [ i for i in records if i['fields']['Category'] == 'Personal' ] if prof: prof_records = [ i for i in records if i['fields']['Category'] == 'Professional' ] all_recs = both_records + pers_records + prof_records all_recs = sorted(all_recs, key=lambda rec: rec['fields']['Chat'], reverse=True) if n and len(all_recs) > n: return (all_recs[:n]) return all_recs
def get_volunteers_from_airtable(airtable_base, airtable_key): # connect to airtable and get volunteer records from airtable api airtable = Airtable(airtable_base, 'Volunteers', api_key=airtable_key) airtable_volunteers = airtable.get_all() if len(airtable_volunteers) == 0: logging.warning('no records in table') return airtable_volunteers
def connect_to_db(key, tablename, api_key, columns, row_conditions): airtable = Airtable(key, tablename, api_key = api_key) record_list = airtable.get_all(fields=columns, maxRecords=500) df = pd.DataFrame([record['fields'] for record in record_list]) for row in row_conditions: df = df.loc[df[row] == row_conditions[row]] df = df[columns] return df
def getevent(): airtable = Airtable(BASE_KEY, 'events', api_key=API_KEY) records = airtable.get_all( sort=[('Date', 'asc')], filterByFormula="DATETIME_DIFF({Date}, NOW(), 'hours')>=8") eventList = [record['fields'] for record in records] return eventList
def puncher(y): table_name = 'xxx' base_key = 'xxx' airtable = Airtable(base_key, table_name, api_key='xxx') results = airtable.batch_insert(y) new_thesaurus = airtable.get_all() with open('make_api.pickle', 'wb') as f: pickle.dump(new_thesaurus, f) return results
def get_rations_data_from_airtable(): AIRTABLE_API_KEY = "keygCUTG6e5DvySOR" AIRTABLE_BASE_ID = "appXanlsMeENo7O1N" AIRTABLE_TABLE_NAME = "Ration Announcements" airtable = Airtable(api_key=AIRTABLE_API_KEY, base_key=AIRTABLE_BASE_ID, table_name=AIRTABLE_TABLE_NAME) rations_data_from_airtable = airtable.get_all() return rations_data_from_airtable
def getactivity(): airtable = Airtable(BASE_KEY, 'activity', api_key=API_KEY) records = airtable.get_all( maxRecords=5, sort=[('Start date', 'asc'), ('End date', 'asc')], filterByFormula="DATETIME_DIFF({End date}, NOW(), 'hours')>=8") activityList = [record['fields'] for record in records] return activityList
def getadminlist(): airtable = Airtable(BASE_KEY, 'member', api_key=API_KEY) records = airtable.get_all() agentlist = [ record['fields']['User id'] for record in records if 'Admin' in record['fields'] ] return agentlist
def get_caloric_values_from_airtable(): AIRTABLE_API_KEY = "keygCUTG6e5DvySOR" AIRTABLE_BASE_ID = "appXanlsMeENo7O1N" AIRTABLE_TABLE_NAME = "Caloric Value" airtable = Airtable(api_key=AIRTABLE_API_KEY, base_key=AIRTABLE_BASE_ID, table_name=AIRTABLE_TABLE_NAME) caloric_values_from_airtable = airtable.get_all() return caloric_values_from_airtable
def get_overdue_requests(date): # date - datetime.date format table = Airtable(BASE_ID, REQUESTS_TABLE_NAME, api_key=API_KEY) date_string = date.strftime('%d-%m-%Y') date_formula_string = 'DATETIME_PARSE("{today}", "DD-MM-YYYY")'.format( today=date_string) formula = 'AND(OR(AND(date2, date2<{date_formula}), AND(NOT(date2), date1<{date_formula})), date1)'.format( date_formula=date_formula_string) res = table.get_all(formula=formula) return res
def delete_all_from_airtable(): for table in tables: airtable = Airtable(base_key, table, api_key) data = airtable.get_all() # for item in data: # record = item['id'] # print(record) print(data) print("finish table " + table)
def get_all_urls(): airtable = Airtable(base_key, url_table_name, api_key=os.environ['AIRTABLE_KEY']) all_urls = airtable.get_all(fields=['_URL']) base_array = [] for url in all_urls: base_array.append(url['fields']['_URL']) return base_array