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
