def handle_subgroup_logic(data, problem_id): subgroup_table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, 'Sub Group', api_key=os.environ['AIRTABLE_KEY']) # Existing subgroup if 'sponsor_subgroup' in data: logger.info('Using existing subgroup {}'.format(data['sponsor_subgroup'])) existing_subgroup_data = subgroup_table.get(data['sponsor_subgroup']) subgroup_update_data = {} # Add Problem to subgroup based on whether subgroup is already associated with problems if 'Problems' in existing_subgroup_data['fields']: subgroup_update_data['Problems'] = existing_subgroup_data['fields']['Problems'] if problem_id not in subgroup_update_data['Problems']: subgroup_update_data['Problems'].append(problem_id) else: subgroup_update_data['Problems'] = [problem_id] logger.info('Submitting to Airtable') rec_subgroup = update_in_airtable(existing_subgroup_data['id'], 'Sub Group', subgroup_update_data) if 'statusCode' in rec_subgroup: return False, rec_subgroup # Add Group and Organization to Problem if needed if 'Group' in existing_subgroup_data['fields']: problem_update_data = {'Group': existing_subgroup_data['fields']['Group']} group_table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, 'Group', api_key=os.environ['AIRTABLE_KEY']) rec_group = group_table.get(existing_subgroup_data['fields']['Group'][0]) if 'Organization' in rec_group['fields']: problem_update_data['Organization'] = rec_group['fields']['Organization'] logger.info('Successfully added Group and Org') rec_problem = update_in_airtable(problem_id, 'Problems', problem_update_data) if 'statusCode' in rec_problem: return False, rec_problem logger.info('Successfully updated subgroup') return True, existing_subgroup_data # New subgroup entry elif 'sponsor_org' in data: logger.info('Creating new subgroup entry: {}'.format(data['sponsor_org'])) data_subgroup = { 'Name': data['sponsor_org'], 'Problems': [problem_id] } # Handle city,state or international physical location if 'physical_location' in data: logger.info('adding physical location') if ',' in data['physical_location']: logger.info('city, state') city, state = data['physical_location'].split(',') if city: data_subgroup['City'] = city.strip() if state: data_subgroup['State'] = state.strip() elif '-' in data['sp_physical_location']: logger.info('international') data_subgroup['State'] = data['physical_location'] rec_subgroup = submit_to_airtable(data_subgroup, 'Sub Group') if 'statusCode' in rec_subgroup: return False, rec_subgroup return True, rec_subgroup return True, None
def get_user_by_base_id(base_id): users_table = Airtable(BASE_ID, USERS_TABLE_NAME, api_key=API_KEY) user = users_table.get(base_id) if not user: raise UserNotFound logging.info('get_user_by_base_id') return user
def get_email_and_phone_number_from_airtable(app_id, secret_key, record_id): try: print('##### Getting email and phone number from airtable started #####') # initialize airtable tables tbl_uploads = Airtable(app_id, 'Uploads', secret_key) upload = tbl_uploads.get(record_id) if 'Customer Email ID' in upload['fields']: email = upload['fields']['Customer Email ID'] else: print('Customer doesn\'t have email.') raise ValueError('Customer doesn\'t have email.') if 'Phone Number' in upload['fields']: phone_number = upload['fields']['Phone Number'] else: phone_number = None print('##### Getting email and phone number from airtable finished #####') print('email:', email, 'phone_number:', phone_number) return email, phone_number except Exception as e: print('Error getting customer information from Airtable: ' + str(e)) raise ValueError('Error getting customer information from Airtable: ' + str(e))
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 test_get_new_manufacturer_id(): mfg_name = "GenericTestManufacturerNameDONOTUSE" manufacturer_id = airtable_api.get_manufacturer_id(mfg_name) manufacturer = Airtable(airtable_api.OPERATIONS_ID, "Manufacturers") result = manufacturer.get(manufacturer_id) assert result is not None manufacturer.delete(manufacturer_id) assert result['fields']['Name'] == mfg_name
def test_get_new_supplier_id(): mfg_name = "GenericSupplierNameDONOTUSE" supplier_id = airtable_api.get_supplier_id(mfg_name) supplier = Airtable(airtable_api.OPERATIONS_ID, "Suppliers") result = supplier.get(supplier_id) assert result is not None supplier.delete(supplier_id) assert result['fields']['Name'] == mfg_name
def test_add_part(): user, org = create_user_and_organization() create_some_fake_parts(org) part = Part.objects.first() part_id = airtable_api.add_part(part) inventory = Airtable(airtable_api.OPERATIONS_ID, "Inventory") result = inventory.get(part_id) assert result is not None inventory.delete(part_id)
def test_add_new_mfg_part(): user, org = create_user_and_organization() create_some_fake_parts(org) mfg_part = ManufacturerPart.objects.first() created_id = airtable_api.add_mfg_part(mfg_part) manufacturer_parts = Airtable(airtable_api.OPERATIONS_ID, "Manufacturer Parts") result = manufacturer_parts.get(created_id) assert result is not None manufacturer_parts.delete(created_id)
def getevent_byrecordid(recordid): airtable = Airtable(BASE_KEY, 'events', api_key=API_KEY) record = airtable.get(recordid) event = record['fields']['Event'] if record['fields']['Countdown'] >= 0: state = 'not_yet_started' else: state = 'finished' return event, state, record
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 upload_packaging_list_to_airtable(app_id, secret_key, record_id, list_url): try: # initialize airtable tables print('##### Uploading packaging list to Airtable started #####') tbl_shipment_group = Airtable(app_id, 'ShipmentGroup', secret_key) shipment_group_record = tbl_shipment_group.get(record_id) packing_lists = [] if 'PackingLists Generated' in shipment_group_record['fields']: packing_lists = shipment_group_record['fields']['PackingLists Generated'] packing_lists.append({'url': list_url}) tbl_shipment_group.update(shipment_group_record['id'], { 'PackingLists Generated': packing_lists }) print('##### Uploading packaging list to Airtable finished #####') except Exception as e: print('Error uploading packaging list to Airtable: ' + str(e)) raise ValueError('Error uploading packaging list to Airtable: ' + str(e))
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 determine_data_to_update_for_problem(problem_id, data_new): logger.info('## Determining which fields need to be updated for {}'.format(problem_id)) table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, 'Problems', api_key=os.environ['AIRTABLE_KEY']) data_old = table.get(problem_id) logger.info('Data From Retool: {}'.format(data_old)) data_to_update = {} for field, value in data_new.items(): if field not in data_old: data_to_update[field] = value elif data_old['fields'][field] != data_new[field]: data_to_update[field] = value if not data_to_update: return False, { 'statusCode': 500, 'body': json.dumps({ 'message': 'The data provided is no different than the data in Airtable' }) } return True, data_to_update
def query(db: Airtable, uid: str, only_data: bool): """Query the data by the unique id from airtable. Parameters ---------- db : Airtable The airtable client. uid : str The airtbale style uid. only_data : bool If True, only the value for the 'fields' in record will be returned. Otherwise, return whole record. Returns ------- record : Record or Fields The dictionary of the data. """ record = db.get(uid) if only_data: return record.get('fields') return record
def success(): air_base = os.getenv('AIR_TABLE_BASE') air_api_key = os.getenv('AIR_TABLE_API') air_table_name = os.getenv('AIR_PEDIDOS_TABLE_NAME') at = Airtable(air_base, air_table_name, api_key=air_api_key) at_prot = Airtable(air_base, os.getenv('AIR_PROTOCOLO_TABLE_NAME'), api_key=air_api_key) session_id = request.args.get('session_id', '') session = stripe.checkout.Session.retrieve(session_id) payment = stripe.PaymentIntent.retrieve(session['payment_intent']) at.update_by_field('stripe_session_id', session_id, {'stripe_payment_id': payment['id']}) at.update_by_field('stripe_session_id', session_id, {'status': payment['status']}) at.update_by_field('stripe_session_id', session_id, {'paid_amount': payment['amount']/100}) pedido = at.search('stripe_session_id', session_id)[0]['fields'] protocolo = at_prot.get(pedido['protocolo'][0])['fields'] email = {'nombre_consulta': protocolo['nombre_consulta'], 'nombre_protocolo': protocolo['nombre_protocolo'], 'shipping_name': pedido['shipping_name'], 'shipping_email': pedido['shipping_email'], 'shipping_phone': pedido['shipping_phone'], 'preciofinal': protocolo['preciofinal'], 'comisionesfinal': protocolo['comisionesfinal'], 'modo_empleo': protocolo['modo_empleo'] if protocolo['modo_empleo'] else '' } email_login = protocolo['email_login'] subject = 'Tienes una nueva venta de tu protocolo {}'.format(email['nombre_protocolo']) send_mail(subject, email_login, 'email.html', **email) return render_template('success.html', **locals())
def get_domestic_shipments_from_airtable(app_id, secret_key, shipment_group_id): try: # initialize airtable tables tbl_domestic_shipments = Airtable(app_id, 'Domestic Shipments', secret_key) tbl_fclist = Airtable(app_id, 'FCList', secret_key) tbl_domestic_shipment_line_item = Airtable(app_id, 'DomesticShipmentLineItem', secret_key) tbl_skus = Airtable(app_id, 'SKUS', secret_key) tbl_packaging_profile = Airtable(app_id, 'PackagingProfile', secret_key) tbl_shipment_group = Airtable(app_id, 'ShipmentGroup', secret_key) print('##### Getting data from Airtable started #####') shipment_group = tbl_shipment_group.get(shipment_group_id) # get all domestic shipments domestic_shipments = [] for domestic_shipment_id in shipment_group['fields']['DomesticShipments']: domestic_shipment = tbl_domestic_shipments.get(domestic_shipment_id) if 'Cosignee Name' in shipment_group['fields']: domestic_shipment['cosignee'] = shipment_group['fields']['Cosignee Name'] # get shipment information if 'FCID' in domestic_shipment['fields']: domestic_shipment['shipment'] = tbl_fclist.get(domestic_shipment['fields']['FCID'][0]) # get line items line_items = [] for domestic_shipment_line_item in domestic_shipment['fields']['LineItems']: line_item = tbl_domestic_shipment_line_item.get(domestic_shipment_line_item) line_item['sku'] = tbl_skus.get(line_item['fields']['SKU'][0]) line_item['packaging_profile'] = tbl_packaging_profile.get(line_item['fields']['PackagingProfile'][0]) line_items.append(line_item) domestic_shipment['line_items'] = line_items domestic_shipments.append(domestic_shipment) print('##### Getting data from Airtable finished #####') return domestic_shipments except Exception as e: print('Error getting domestic shipments from Airtable: ' + str(e)) raise ValueError('Error getting domestic shipments from Airtable: ' + str(e))
'score_min': row['score'], 'score_max': row['score'], 'score_sum': row['score'], 'title_list': row['title'], 'last_title': row['title'], 'permalink_list': row['permalink'], 'last_permalink': row['permalink'], } airtable.insert(record_fields) print('... inserted ...') inserted_count += 1 elif len(airtable_records) > 0: for record in airtable_records: print('... update record ({})...'.format(row['link'])) record_id = record['id'] old = airtable.get(record_id)['fields'] new_score_avg = (old['score_sum'] + row['score']) / (old['times_seen'] + 1) new_score_min = old['score_min'] if old['score_min'] <= row['score'] else row['score'] new_score_max = old['score_max'] if old['score_max'] >= row['score'] else row['score'] new_score_sum = old['score_sum'] + row['score'] new_times_seen = old['times_seen'] + 1 if row['title'] not in old['title_list']: new_title_list = '{}|{}'.format(old['title_list'], row['title']) else: new_title_list = old['title_list'] if row['permalink'] not in old['permalink_list']: new_permalink_list = '{}|{}'.format(old['permalink_list'], row['permalink']) else: new_permalink_list = old['permalink_list'] record_fields = { 'link': old['link'],
def getuserid_byrecordid(recordid): airtable = Airtable(BASE_KEY, 'member', api_key=API_KEY) userid = airtable.get(recordid)['fields']['User id'] return userid
airtable = Airtable(base_key, table_name, api_key=os.environ['AIRTABLE_API_KEY']) def _slugify_for_dict_keys(lower_dict): upper_dict = {} for k, v in lower_dict.items(): if isinstance(v, dict): v = _slugify_for_dict_keys(v) k = slugify(k, replacements=[['-', '_']]) upper_dict[k] = v return upper_dict pages = airtable.get_all(view='Published', ) playbooks = [] for page in pages: playbook = page['fields'] playbook['Organization'] = airtable.get( playbook['Organization'][0])['fields'] newPlaybook = _slugify_for_dict_keys(playbook) playbooks.append(newPlaybook) with open(datafile, 'w') as outfile: yaml.dump(playbooks, outfile, default_flow_style=False)
def run(): base_key = 'app0bcm2GEEGdlC7K' coaches_table_name = 'Coaches' events_table_name = 'Events' coaches_table = Airtable(base_key, coaches_table_name, api_key='keyiLfzA6XNxVuMw1') events_table = Airtable(base_key, events_table_name, api_key='keyiLfzA6XNxVuMw1') all_events = events_table.get_all() all_coaches = coaches_table.get_all() final_data = [] print('Getting Headers') #Set headers dynamically so as columns are added the demographics update headers = sorted({key for coach in all_coaches for key in coach['fields'].keys()}) \ + ['Program','Event Type','Event Date', 'Duration','Event Role'] #Get rid of event columns in coach table for column in [ 'Chair', 'Head TF', 'TF', 'Mentor', 'Presenter', 'Facilitator', 'Participant' ]: try: headers.remove(column) except: pass #Dynamically find the most recent survey column to compute on fire status later max_survey_column = sorted( [h if h.find("DWN Survey") != -1 else '' for h in headers])[-1] on_fire_index = headers.index('On Fire Status') final_data.append(headers) print('Starting to run through coaches') for coach_dict in all_coaches: #Create a container for coach rows while we calculate on fire status coach_new_rows = [] coach = coach_dict['fields'] #On fire status calculation #Calculate dynamically as you loop through on_fire_status = 0 major_event_count = 0 took_DWN_survey = False if max_survey_column in coach and coach[max_survey_column] == "Yes": took_DWN_survey = True #Set up demographics row demographics = [coach[field] if field in coach else ''\ for field in headers[:len(headers)-5]] #Gather all events in a dictionary to loop through coach_events = {} print('Current coach: {0} {1}'.format(coach['First'].encode(), \ coach['Last'].encode())) #Use this to see if a coach hasn't been to any events. If that is the case #we need to add a row for them to all data anyway so that we can access their #info distinct_event_count = 0 if 'Chair' in coach: coach_events['Chair'] = coach['Chair'] distinct_event_count += 1 if 'Head TF' in coach: coach_events['head_tf'] = coach['Head TF'] distinct_event_count += 1 if 'TF' in coach: coach_events['TF'] = coach['TF'] distinct_event_count += 1 if 'Mentor' in coach: coach_events['Mentor'] = coach['Mentor'] distinct_event_count += 1 if 'Presenter' in coach: coach_events['Presenter'] = coach['Presenter'] distinct_event_count += 1 if 'Facilitator' in coach: coach_events['Facilitator'] = coach['Facilitator'] distinct_event_count += 1 if 'Participant' in coach: coach_events['Participant'] = coach['Participant'] distinct_event_count += 1 #Coach hasn't been to any events. Add one row to final_data for them #anyway so they are included in the data if distinct_event_count == 0: #Calculate on fire status, update airtable on_fire_status = 1 if took_DWN_survey else 0 fields = {'On Fire Status': str(on_fire_status)} coaches_table.update(coach_dict['id'], fields) #and update demographics row new_row = demographics + ['', '', '', '', ''] new_row[on_fire_index] = on_fire_status final_data.append(new_row) continue #Loop through events, get info about event from events table today = date.today() for event_role in coach_events: event_list = coach_events[event_role] for event_id in event_list: event_record = events_table.get(event_id)['fields'] event_type = event_record['Event Type'] event_date = event_record['Date'] event_program = event_record['Program'] #Check if the event and role count as a major event #Use the event date to compute whether it was in the last three years if event_program not in ["DWN", "DWCC" ] and event_role != "Participant": date_split = event_date.split('-') event_date_object = date(int(date_split[0]), int(date_split[1]), int(date_split[2].split('T')[0])) date_diff = today - event_date_object if date_diff.days / 365 <= 3.0: major_event_count += 1 duration = compute_duration(event_program, event_type, event_role) #Only add independent columns to coach new rows until we have calculated On Fire new_row = [event_program, event_type, event_date,\ duration, event_role] coach_new_rows.append(new_row) #Calculate on fire status, update airtable, update demos on_fire_status = compute_on_fire(took_DWN_survey, major_event_count) fields = {'On Fire Status': str(on_fire_status)} coaches_table.update(coach_dict['id'], fields) demographics[on_fire_index] = on_fire_status #When we're done with all one coach, add demographics and add all their rows final_data += [demographics + row for row in coach_new_rows] #Get rid of after testing #break appendToSheets(final_data) #Local solution '''with open('final_data.csv', 'w', newline='') as file:
def handle_people_logic(data, problem_id, rec_subgroup): people_table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, 'People', api_key=os.environ['AIRTABLE_KEY']) data_people = people_table.search('email', data['sponsor_email']) # Existing Person if len(data_people): people_update_data = {} logger.info(data_people) # Add this problem to this person based on whether they are already associated with problems if 'Problems' in data_people[0]['fields']: people_update_data['Problems'] = data_people[0]['fields']['Problems'] if problem_id not in people_update_data['Problems']: people_update_data['Problems'].append(problem_id) logger.info('Added Problem List to update data') else: people_update_data['Problems'] = [problem_id] logger.info('Created New Problem List for update data') # Add subgroup/group/org to person if needed if 'Sub Group' in data_people[0]['fields']: people_update_data['Sub Group'] = data_people[0]['fields']['Sub Group'] logger.info('Added sub group to the update data') if rec_subgroup['id'] not in data_people[0]['fields']['Sub Group']: people_update_data['Sub Group'].append(rec_subgroup['id']) logger.info('Added new subgroup to the update data') if 'Group' in rec_subgroup['fields']: if 'Group' not in data_people[0]['fields']: people_update_data['Group'] = rec_subgroup['fields']['Group'] logger.info('Added Group to the update data') else: people_update_data['Group'] = data_people[0]['fields']['Group'] for grp in rec_subgroup['fields']['Group']: if grp not in people_update_data['Group']: people_update_data['Group'].append(grp) logger.info('Added new groups to the update data') group_table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, 'Group', api_key=os.environ['AIRTABLE_KEY']) if 'Organization' in data_people[0]['fields']: people_update_data['Organization'] = data_people[0]['fields']['Organization'] logger.info('Added Organization to the update data') else: people_update_data['Organization'] = [] for grp in people_update_data['Group']: rec_group = group_table.get(grp) if 'Organization' in rec_group['fields']: for org in rec_group['fields']['Organization']: if org not in people_update_data['Organization']: people_update_data['Organization'].append(org) logger.info('Added organization to the update data') if not people_update_data['Organization']: people_update_data.pop('Organization') logger.info('Removing organization from update data because there are none') rec_people = update_in_airtable(data_people[0]['id'], 'People', people_update_data) logger.info('Updated record in airtable: {}'.format(rec_people)) if 'statusCode' in rec_people: return False, rec_people return True, rec_people else: logger.info('Creating new person entry {}'.format(data)) data_people = {'email': data['sponsor_email']} if len(data['sponsor_name'].split(' ', 1)) > 1: data_people['first_name'] = data['sponsor_name'].split(' ', 1)[0] data_people['last_name'] = data['sponsor_name'].split(' ', 1)[1] else: data_people['first_name'] = data['sponsor_name'] if 'Group' in rec_subgroup['fields']: data_people['Group'] = rec_subgroup['fields']['Group'] group_table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, 'Group', api_key=os.environ['AIRTABLE_KEY']) data_people['Organization'] = [] for grp in data_people['Group']: rec_group = group_table.get(grp) if 'Organization' in rec_group['fields']: for org in rec_group['fields']['Organization']: if org not in data_people['Organization']: data_people['Organization'].append(org) data_people['Sub Group'] = [rec_subgroup['id']] data_people['Problems'] = [problem_id] if 'sponsor_division' in data: data_people['Division'] = data['sponsor_division'] rec_people = submit_to_airtable(data_people, 'People') logger.info('New person entry {}'.format(rec_people)) if 'statusCode' in rec_people: return False, rec_people return True, rec_people
def get_request_by_base_id(request_base_id): table = Airtable(BASE_ID, REQUESTS_TABLE_NAME, api_key=API_KEY) request_elem = table.get(request_base_id) logging.info('get_request_by_base_id') return request_elem
def update_airtable(df_links, airtable_base_key, airtable_table_name): logging.info('... send to airtable ...') # connect to airtable airtable = Airtable(airtable_base_key, airtable_table_name, api_key=os.environ['AIRTABLE_KEY']) inserted_count = 0 updated_count = 0 for i, row in df_links.iterrows(): airtable_records = airtable.search('link', row['link']) if len(airtable_records) == 0: logging.info('... new record ({})...'.format(row['link'])) record_fields = { 'link': row['link'], 'domain': row['domain'], 'first_seen': row['created_utc'], 'last_seen': row['created_utc'], 'first_added': row['last_seen'], 'last_added': row['last_seen'], 'times_seen': 1, 'score_avg': row['score'], 'score_min': row['score'], 'score_max': row['score'], 'score_sum': row['score'], 'title_list': row['title'], 'last_title': row['title'], 'permalink_list': row['permalink'], 'last_permalink': row['permalink'], } airtable.insert(record_fields) logging.info('... inserted ...') inserted_count += 1 elif len(airtable_records) > 0: for record in airtable_records: logging.info('... update record ({})...'.format(row['link'])) record_id = record['id'] old = airtable.get(record_id)['fields'] new_score_avg = (old['score_sum'] + row['score']) / (old['times_seen'] + 1) new_score_min = old['score_min'] if old['score_min'] <= row[ 'score'] else row['score'] new_score_max = old['score_max'] if old['score_max'] >= row[ 'score'] else row['score'] new_score_sum = old['score_sum'] + row['score'] new_times_seen = old['times_seen'] + 1 if row['title'] not in old['title_list']: new_title_list = '{}|{}'.format(old['title_list'], row['title']) else: new_title_list = old['title_list'] if row['permalink'] not in old['permalink_list']: new_permalink_list = '{}|{}'.format( old['permalink_list'], row['permalink']) else: new_permalink_list = old['permalink_list'] record_fields = { 'link': old['link'], 'domain': old.get('domain', 'N/A'), 'first_seen': old['first_seen'], 'last_seen': row['created_utc'], 'first_added': old['first_added'], 'last_added': row['last_seen'], 'times_seen': new_times_seen, 'score_avg': new_score_avg, 'score_min': new_score_min, 'score_max': new_score_max, 'score_sum': new_score_sum, 'title_list': new_title_list, 'last_title': row['title'], 'permalink_list': new_permalink_list, 'last_permalink': row['permalink'], } airtable.update(record_id, record_fields) logging.info('... updated ...') updated_count += 1 logging.info('... inserted_count = {} ...'.format(inserted_count)) logging.info('... updated_count = {} ...'.format(updated_count)) return inserted_count, updated_count
class Airtable(object): """ `Args:` base_key: str The key of the Airtable base that you will interact with. table_name: str The name of the table in the base. The table name is the equivilant of the sheet name in Excel or GoogleDocs. api_key: str The Airtable provided api key. Not required if ``AIRTABLE_API_KEY`` env variable set. """ def __init__(self, base_key, table_name, api_key=None): self.api_key = check_env.check('AIRTABLE_API_KEY', api_key) self.at = AT(base_key, table_name, self.api_key) def get_record(self, record_id): """ Returns a single record. `Args:` record_id: str The Airtable record id `Returns:` A dictionary of the record """ return self.at.get(record_id) def get_records(self, fields=None, max_records=None, view=None, formula=None, sort=None): """ `Args:` fields: str or lst Only return specified column or list of columns. The column name is case sensitive max_records: int The maximum total number of records that will be returned. view: str If set, only the records in that view will be returned. The records will be sorted according to the order of the view. formula: str The formula will be evaluated for each record, and if the result is not 0, false, "", NaN, [], or #Error! the record will be included in the response. If combined with view, only records in that view which satisfy the formula will be returned. For example, to only include records where ``COLUMN_A`` isn't empty, pass in: ``"NOT({COLUMN_A}='')"`` For more information see `Airtable Docs on formulas. <https://airtable.com/api>`_ Usage - Text Column is not empty: ``airtable.get_all(formula="NOT({COLUMN_A}='')")`` Usage - Text Column contains: ``airtable.get_all(formula="FIND('SomeSubText', {COLUMN_STR})=1")`` sort: str or lst Specifies how the records will be ordered. If you set the view parameter, the returned records in that view will be sorted by these fields. If sorting by multiple columns, column names can be passed as a list. Sorting Direction is ascending by default, but can be reversed by prefixing the column name with a minus sign -. Example usage: ``airtable.get(sort=['ColumnA', '-ColumnB'])`` `Returns:` Parsons Table See :ref:`parsons-table` for output options. """ # Raises an error if sort is None type. Thus, only adding if populated. kwargs = {'fields': fields, 'max_records': max_records, 'formula': formula} if sort: kwargs['sort'] = sort tbl = Table(self.at.get_all(**kwargs)) return tbl.unpack_dict(column='fields', prepend=False) def insert_record(self, row): """ Insert a single record into an Airtable. `Args:` row: dict Fields to insert. Must be dictionary with Column names as Key. typecast: boolean Automatic data conversion from string values. `Returns:` Dictionary of inserted row """ resp = self.at.insert(row) logger.info('Record inserted') return resp def insert_records(self, table): """ Insert multiple records into an Airtable. The columns in your Parsons table must exist in the Airtable. The method will attempt to map based on column name, so the order of the columns is irrelevant. `Args:` table: A Parsons Table Insert a Parsons table typecast: boolean Automatic data conversion from string values. `Returns:` List of dictionaries of inserted rows """ resp = self.at.batch_insert(table) logger.info(f'{table.num_rows} records inserted.') return resp def update_record(self, record_id, fields, typecast=False): """ Updates a record by its record id. Only Fields passed are updated, the rest are left as is. `Args:` record_id: str The Airtable record id fields: dict Fields to insert. Must be dictionary with Column names as Key. typecast: boolean Automatic data conversion from string values. `Returns:` ``None`` """ resp = self.at.update(record_id, fields, typecast=typecast) logger.info(f'{record_id} updated') return resp
API_KEY = 'keyVuEhB1SvC5cDQj' # your API KEY groups = Airtable('app4FKBWUILUmUsE1', 'Groups', api_key=API_KEY) groups_records = groups.get_all() country = Airtable('app4FKBWUILUmUsE1', 'Country', api_key=API_KEY) f = open('inserts.sql', 'w') for record in groups_records: if 'Group name' in record['fields']: insert = "INSERT INTO `group` (`name`, `description`, `country_id`) VALUES (`" \ + record['fields']['Group name'] + '`, `' if 'Description' in record['fields']: insert += record['fields']['Description'] else: insert += "NULL" insert += '`, `' if 'Country' in record['fields']: country_records = country.get(record['fields']['Country'][0]) country_id = get_key(country_records['fields']['Name']) if country_id: insert += country_id else: insert += "NULL" else: insert += "NULL" insert += '`);' #print(insert) f.write(insert + '\n') f.close()
import os from airtable import Airtable test_base = "appaPqizdsNHDvlEm" test_table = "table" airtable = Airtable(test_base, test_table, api_key=os.environ["AIRTABLE_API_KEY"]) # Insert rec = airtable.insert({"text": "A", "number": 1, "boolean": True}) # Get assert airtable.get(rec["id"]) # Update rv = airtable.update(rec["id"], {"text": "B"}) assert rv["fields"]["text"] == "B" # Replace rv = airtable.replace(rec["id"], {"text": "C"}) assert rv["fields"]["text"] == "C" # Get all assert airtable.get_all() # Delete assert airtable.delete(rec["id"]) # Batch Insert records = airtable.batch_insert([{