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([{
