def export_to_airtable(data_type, obj_id):
"""
type: (int) -> Dict[str, Any]
Export Django model data to Airtable so administraters can analyze data.
"""
try:
if data_type == 'strikecircle':
# Passing strike_circle as list because serialization only works for iterable objects: https://code.djangoproject.com/ticket/11244
strike_circle = StrikeCircle.objects.get(pk=obj_id)
serialized_record = serialize("json", [strike_circle])
record = json.loads(serialized_record)[0]
if record["model"] == "strikecircle.strikecircle":
table_name = "Strike Circle"
airtable = Airtable(BASE_KEY, table_name, api_key=API_KEY)
return airtable.insert(record["fields"])
elif data_type == 'pledge':
pledge = Pledge.objects.get(pk=obj_id)
# Passing pledge as list because serialization only works for iterable objects: https://code.djangoproject.com/ticket/11244
serialized_record = serialize("json", [pledge])
record = json.loads(serialized_record)[0]
if record["model"] == "strikecircle.pledge":
table_name = "Pledge"
airtable = Airtable(BASE_KEY, table_name, api_key=API_KEY)
return airtable.insert(record["fields"])
except Exception as e:
logger.error(e)
def applications():
applications_table = Airtable(AIRTABLE_APP,
APPLICATIONS_TABLE,
api_key=AIRTABLE_API_KEY)
j = request.json
for event in j['event']:
fields = {
'applied_name': j['applied_name'],
'email': j['email'],
'Event': [event['id']],
'Agree to Attend': j['agree_to_attend'],
'Agree to Rate': j['agree_to_rate'],
'Agree to Pay Taxes': j['agree_to_pay_taxes'],
'Agree to Follow Instructions': j['agree_to_follow_instructions'],
'Assignment Type': event['assignment_type']
}
app.logger.debug('creating assignment application on Airtable: %s' %
str(fields))
try:
applications_table.insert(fields)
except HTTPError as error:
app.logger.error(error)
app.logger.error(error.response.text)
return jsonify({'message': 'error'}), 500
return jsonify({'message': 'ok'}), 201
def add_visit(page_id):
response = Response(True)
try:
airtable = Airtable(AIRTABLE_BASE_ID, PAGE_TABLES[int(page_id)])
today = datetime.date.today().strftime("%d-%m-%Y")
existing_day = airtable.match('Date', today)
if existing_day:
visits = existing_day['fields']['Visits']
visits = visits + 1
airtable.update(existing_day['id'], {'Visits': visits},
typecast=True)
else:
new_record = {'Date': today, 'Visits': 1}
airtable.insert(new_record)
except Exception as e:
response.status = False
response.message = str(e)
return response.compose()
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'],
})
class Api():
def __init__(self, *, config=None):
api_key = config['airtable_api_key']
base_key = config['airtable_base_key']
table_name_state = config['table_name_state']
table_name_timesheet = config['table_name_timesheet']
self.pricipal = config['principal']
self.lunch_break = config['lunch_break']
self.state = Airtable(base_key, table_name_state, api_key)
self.timesheet = Airtable(base_key, table_name_timesheet, api_key)
def get_state(self):
record = self.state.match('state_principal', self.pricipal)
return record['fields']['state_name']
def set_state(self, *, state_name=None):
record = {'state_name': state_name}
self.state.update_by_field('state_principal', self.pricipal, record)
def clock_in(self, *, day=None, time=None):
record = {
'record_day': day,
'clock_in_time': time,
'lunch_break': self.lunch_break
}
self.timesheet.insert(record)
def clock_out(self, *, day=None, time=None):
record = {'clock_out_time': time}
self.timesheet.update_by_field('record_day', day, record)
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 registeragent(agent, username, userid):
airtable = Airtable(BASE_KEY, 'member', api_key=API_KEY)
if airtable.match('Agent', agent): # 查重
return False
else:
record = {'Agent': agent, 'User name': username, 'User id': userid}
airtable.insert(record)
return True
def addEntry_in_airtable2(serialnumber, location, date, notes, typecast=False):
table = Airtable("app3lZZfvIqAj2lwc", "Log")
records = {
'serialnumber': serialnumber,
'location': location,
'date': date,
'notes': notes
}
table.insert(records)
def to_airtable(self, df):
working_table = Airtable(self.base_key,
self.working_table,
api_key=self.api_key)
self.clear_working_db(working_table)
print("Writing to Airtable db '{}'...".format(self.working_table))
for _, row in df.iterrows():
record = row.to_dict()
working_table.insert(record)
print("Writing finished! /n Done!")
def send_to_table(answer_dict, table_name):
intake_table = Airtable(at_base_lmm, table_name, api_key=at_api_key)
send_dict = {}
for key, value in answer_dict.iteritems():
if 'Attachment' in key:
log(value)
send_dict[key] = value['elements']['fullpath']
else:
send_dict[key] = str(value)
intake_table.insert(send_dict)
class Airtable_API:
def __init__(self, base_key, table_name, api_key):
self.__table = Airtable(base_key, table_name, api_key)
def search(self, field_name, field_value):
return self.__table.search(field_name, field_value)
def insert(self, fields):
self.__table.insert(fields)
def get_all(self):
return self.__table.get_all()
def insert(weathers):
table = Airtable(table_name='weather2',
base_key=os.getenv('SMHI_A_BASE'),
api_key=os.getenv('SMHI_A_KEY'))
for w in weathers:
record = table.insert(w, typecast=True)
logging.debug(record)
class AirtableClient:
def __init__(self):
"""Initialize AirtableClient."""
self.airtable_client = Airtable(os.getenv("airtable_base_id"),
app.config["AIRTABLE_TABLE_NAME"],
os.getenv("airtable_api_key"))
def register_asset(self, asset: Asset):
"""Register to Airtable.
Register a dictionary with the appropriate key and value to Airtable.
Args:
asset (Asset): Asset dataclass with field name of Assets table on Airtable.
Returns:
Dictionary registered in Airtable.
"""
try:
return self.airtable_client.insert(asdict(asset))
except requests.exceptions.HTTPError as he:
app.logger.error(he)
raise he
except TypeError as te:
app.logger.error(te)
raise te
async def insert_or_update_record(an_record: ATRecord):
"""
Given an AN record for an already-set context, see if there's an existing
AT record for the same key. If not, insert it.
"""
record_type = MC.get()
at_key, at_base, at_table, at_typecast = MC.at_connect_info()
at = Airtable(at_base, at_table, api_key=at_key)
record_dict = at.match(MC.at_key_field(), an_record.key)
if not record_dict:
prinl(f"Uploading new {record_type} record.")
at.insert(an_record.all_fields(), typecast=at_typecast)
return
prinlv(f"Retrieved matching Airtable {record_type} record.")
if at_record := ATRecord.from_record(record_dict):
an_record.at_match = at_record
def create_link():
domain_url = os.getenv('DOMAIN')
air_base = os.getenv('AIR_TABLE_BASE')
air_api_key = os.getenv('AIR_TABLE_API')
air_table_name = os.getenv('AIR_PROTOCOLO_TABLE_NAME')
try:
at = Airtable(air_base, air_table_name, api_key=air_api_key)
new_record_content = dict(request.args) if getattr(request, 'args') else dict(request.form)
new_record_content['query_string'] = urlencode(request.args) if getattr(request, 'args') else urlencode(request.form)
if '_createdTime' in new_record_content:
del new_record_content['_createdTime']
if 'date_created' in new_record_content:
del new_record_content['date_created']
if 'date_modified' in new_record_content:
del new_record_content['date_modified']
if 'id' in new_record_content:
del new_record_content['id']
new_record = at.insert(new_record_content)
short_url = {'short_url': new_record['id'].split('rec')[1],
'airtableID': new_record['id'], 'visits': 0}
at.update(new_record['id'], short_url)
return jsonify({'link_url': domain_url + '/' + short_url['short_url']})
except Exception as e:
return jsonify(error=str(e)), 403
async def get_or_create_airtable_record(
airtable: Airtable,
search_field: str,
search_value: Any,
new_fields: dict[str, Any],
) -> str:
results: list[AirtableRecordReadModel] = \
airtable.search( # type: ignore
search_field,
search_value,
)
if len(results) > 1:
raise RuntimeError((
f"{len(results)} records in {airtable.table_name} " # type: ignore
f"found for {search_field} equal to '{search_value}'"))
result = nth(results, 0)
if result is not None:
return result["id"]
record: AirtableRecordReadModel = airtable.insert( # type: ignore
new_fields, )
return record["id"]
def send_survey():
"""
a function that sends the SMS autoresponder
messages and writes to the AirTable
"""
airtable = Airtable(AIRTABLE_BASE_ID, 'Input')
incoming_msg = request.values.get('Body', 'message error').lower()
sender_phone_number = request.values.get('From', 'unknown_sender')
twilio_phone_number = request.values.get('To', 'unknown_number')
# reset session
if 'reset' in incoming_msg:
del session['sms_count']
session.pop(sender_phone_number, None)
return ("resetting the session")
if not 'sms_count' in session:
session['sms_count'] = 0
session[sender_phone_number] = {}
sms_count = session['sms_count']
sms_message = get_message(sms_count)
if sms_count >= 0 and sms_count <= 4:
if sms_count == 0:
session[sender_phone_number][
'Twilio_Phone_Number'] = twilio_phone_number
elif sms_count == 1:
session[sender_phone_number]['Score'] = int(incoming_msg)
elif sms_count == 2:
session[sender_phone_number]['Reason'] = incoming_msg
elif sms_count == 3:
session[sender_phone_number]['Comments'] = incoming_msg
elif sms_count == 4:
session[sender_phone_number]['Team'] = incoming_msg
# here is where we write to the airtable
airtable.insert(session[sender_phone_number])
session['sms_count'] += 1
resp = MessagingResponse()
msg = resp.message()
msg.body(sms_message)
return str(resp)
def create_checkout_session():
if request.data:
data = json.loads(request.data)
else:
data = create_line_items(request)
line_items = data.pop('line_items', None)
for item in line_items:
item['amount'] = int(item['amount']*100)
domain_url = os.getenv('DOMAIN')
try:
# Create new Checkout Session for the order
# Other optional params include:
# [billing_address_collection] - to display billing address details on the page
# [customer] - if you have an existing Stripe Customer ID
# [payment_intent_data] - lets capture the payment later
# [customer_email] - lets you prefill the email input in the form
# For full details see https:#stripe.com/docs/api/checkout/sessions/create
# ?session_id={CHECKOUT_SESSION_ID} means the redirect will have the session ID set as a query param
checkout_session = stripe.checkout.Session.create(
success_url=domain_url + "/success?session_id={CHECKOUT_SESSION_ID}", \
cancel_url=domain_url + "/canceled.html", \
line_items=line_items, \
metadata=data, \
payment_method_types=["card"], \
)
if request.data:
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)
new_record_content = dict(protocolo=["rec" + data['protocolo_id']],
shipping_name=data['shipping_name'],
shipping_email=data['shipping_email'],
shipping_phone=data['shipping_phone'],
shipping_address=data['shipping_address'],
shipping_city=data['shipping_city'],
shipping_provincia=data['shipping_provincia'],
shipping_postalcode=data['shipping_postalcode'],
status='unpaid',
stripe_session_id=checkout_session['id'],
)
new_record = at.insert(new_record_content)
at.update(new_record['id'], {'airtableID': new_record['id'] })
return jsonify({'sessionId': checkout_session['id']})
else:
return jsonify({'linkinfo': domain_url + '/checkout-session?sessionId=' + checkout_session['id']})
except Exception as e:
return jsonify(error=str(e)), 403
def sync(airtable: Airtable, local_books, logger):
logger.debug('Fetching Airtable books...')
airtable_books = airtable.get_all(view='Active')
logger.debug('Airtable books fetched')
airtable_book_ids = [
b['fields']['ID'] for b in airtable.get_all(view='Active')
]
local_book_ids = [b.id for b in local_books]
# --- RETURN ---
# Books that are in airtable but not in local
returned_books = find_airtable(diff(airtable_book_ids, local_book_ids),
airtable_books)
for returned_book in returned_books:
airtable.update(returned_book['id'], {'ReturnedAt': utc_now()})
if returned_books:
logger.info(f'User has {len(returned_books)} returned books')
# --- NEW BOOKS ---
# Books that are in local but not in airtable
new_books = find_local(diff(local_book_ids, airtable_book_ids),
local_books)
for new_book in new_books:
airtable_book = airtable_book_from_local_book(new_book)
airtable_book['BorrowedAt'] = utc_now()
airtable.insert(airtable_book)
if new_books:
logger.info(f'User has {len(new_books)} new books')
# --- STILL BOOKS ---
# Books that are in local and in airtable
still_book_ids = intersect(local_book_ids, airtable_book_ids)
still_airtable_books = find_airtable(still_book_ids, airtable_books)
still_local_books = find_local(still_book_ids, local_books)
for still_airtable_book in still_airtable_books:
corresponding_still_local_book = find_local(
[still_airtable_book['fields']['ID']], still_local_books)[0]
airtable.update(
still_airtable_book['id'],
airtable_book_from_local_book(corresponding_still_local_book))
if still_book_ids:
logger.info(f'User has {len(still_book_ids)} still books')
def main():
serial = getSerial()
airtable = Airtable('app9og4P1Z4iet5fT', 'Computers', 'keybFjYyk9LuWpxNw')
computers = []
records = airtable.search('SN', serial)
print(records)
cfgDict = cfgToDict()
# print(cfgDict)
if len(records) == 0:
c = Computer(serial)
airtable.insert(createFields(c, cfgDict))
for record in records:
curr = Computer(serial, record)
computers.append(curr)
for c in computers:
airtable.update(c.comp.get('id'), createFields(c, cfgDict))
def new_request(name, creator_record_id):
now = datetime.datetime.today().strftime('%Y-%m-%dT%H:%M:%S.000Z')
table = Airtable(BASE_ID, REQUESTS_TABLE_NAME, api_key=API_KEY)
record = {
'creator': [creator_record_id],
'name': name,
'creation_time': now
}
res_record = table.insert(record)
logging.info('new_request')
return res_record
def scraper():
# companies = [ISRACARD_STR, LEUMI_STR, CAL_CASHBACK_STR, AMERICANEXPRESS_STR]
companies = [ISRACARD_STR]
print('Started scraping:\n')
benefits = {}
for company in companies:
print('Started scraping ' + company + '.\n')
scrape_by_name(company, benefits)
print('Finished the current scrape, current number of benefits: ' +
str(len(benefits)) + '\n')
question = input("Press 0 for CSV and 1 for Airtable \t")
if question == str(0):
with open('benefits.csv', 'wb') as csvfile:
writer = csv.writer(csvfile)
print(
'Finished scraping, now Starting to write to CSV a total of ' +
str(len(benefits)) + '.')
for key, value in benefits.items():
try:
writer.writerow([str(key[0]), str(key[1]), str(value)])
except csv.Error:
print('CSV writing error', sys.exc_info()[0])
raise
csvfile.close()
elif question == str(1):
print("Updating airtable")
airtable = Airtable('app4iqBeamg7ClHPS',
'Benefits',
api_key='keyaVQTgUd3hczqsE')
benefit_str = 'Benefit'
company_str = 'Company'
description_str = 'Benefit description'
for key, value in benefits.items():
airtable.insert({
benefit_str: key[1],
company_str: key[0],
description_str: value
})
class SaveToAirtablePipeline(object):
""" pipeline that save data to airtable """
def __init__(self):
self.airtable = Airtable(base_key='<>', table_name='<>', api_key='<>')
def process_item(self, gif_item, spider):
if isinstance(gif_item, GifItem):
# time.sleep(2)
self.airtable.insert({
"id": gif_item['id'],
"gif": [{
"url": gif_item['file_url']
}],
"site": gif_item['site'],
"title": gif_item['title'],
"url": gif_item['url'],
"tags": gif_item['tags'],
"author": gif_item['author'],
"created": gif_item['created'],
"category": gif_item['category'],
"duration": gif_item['duration'],
"dimensions": gif_item['dimensions']
})
def write_to_airtable():
for item in time_list:
airtable = Airtable(base_key, item["table_name"], api_key)
data_from_air_table = airtable.get_all()
time = item["period_type"]
url = f"{base_url}id={path_id}&last={number_of_period}&format={output_type}&download={download}&data={original_data}&time={time}"
response = requests.request("GET", url)
res = response.json()
data_from_cbs = res['DataSet']['Series'][0]['obs']
for record in data_from_cbs:
check_list = list(
filter(
lambda period: period['fields']["TimePeriod"] == record[
"TimePeriod"], data_from_air_table))
print(check_list)
time_period = record["TimePeriod"]
employee = record["Value"]
# print(employee)
new_record = {"TimePeriod": time_period, "employee": employee}
if not check_list:
airtable.insert(new_record)
else:
airtable.update_by_field("TimePeriod", time_period, new_record)
def add_user_to_crm(chat_id):
if env('AIRTABLE_API_KEY', None) is not None:
logger.info('Saving new user to CRM')
user = user_get_by_chat_id(chat_id=chat_id)
logger.info(
f"created_at is {user.created_at.replace(tzinfo=datetime.timezone.utc).strftime('%Y-%m-%dT%H:%M:%S.000Z')}"
)
airtable = Airtable(env('AIRTABLE_BASE_KEY'),
env('AIRTABLE_CRM_TABLE'),
api_key=env('AIRTABLE_API_KEY'))
airtable.insert({
'Имя':
user.full_name,
'Юзернейм':
user.user_name,
'ID чата':
user.chat_id,
'Зарегистрирован':
user.created_at.replace(tzinfo=datetime.timezone.utc).strftime(
'%Y-%m-%dT%H:%M:%S.000Z'),
})
def get_manufacturer_id(name):
"""
Get the Manufacturer ID for a given name
If the manfacturer doesn't exist, create it
"""
manufacturers = Airtable(OPERATIONS_ID, "Manufacturers")
results = manufacturers.search("Name", name)
if len(results) == 0:
# Create manufacturer
result = manufacturers.insert({
"Name": name
})
return result['id']
else:
return results[0]['id']
def get_supplier_id(name):
"""
Get the Supplier ID for a given name
If the supplier doesn't exist, create it
"""
suppliers = Airtable(OPERATIONS_ID, "Suppliers")
results = suppliers.search("Name", name)
if len(results) == 0:
# Create supplier
result = suppliers.insert({
"Name": name
})
return result['id']
else:
return results[0]['id']
def comparte_gana_sms():
domain_url = os.getenv('DOMAIN')
air_base = os.getenv('AIR_TABLE_BASE')
air_api_key = os.getenv('AIR_TABLE_API')
air_table_name = os.getenv('AIR_PROTOCOLO_TABLE_NAME')
try:
#at = Airtable(air_base, air_table_name, api_key=air_api_key)
email = request.form.get('Email')
iban = request.form.get('IBAN')
name = request.form.get('Name')
codigo_pais_movil = request.form.get('Código País')
movil = request.form.get('Número de Teléfono Móvil')
protocolo = request.form.get('Protocolo')[2:-2]
at = Airtable(air_base, air_table_name, api_key=air_api_key)
lookup_record = at.search('airtableID', protocolo)
if 'imagen_protocolo' in lookup_record[0]['fields']:
del lookup_record[0]['fields']['imagen_protocolo']
del lookup_record[0]['fields']['date_created']
del lookup_record[0]['fields']['date_modified']
if 'pedidos_pagados_clientes' in lookup_record[0]['fields']:
del lookup_record[0]['fields']['pedidos_pagados_clientes']
del lookup_record[0]['fields']['airtableID']
del lookup_record[0]['fields']['short_url']
base_encoded_email = urlencode({"email_login": lookup_record[0]['fields']['email_login']})
lookup_record[0]['fields']['email_login'] = email
lookup_record[0]['fields']['query_string'] = lookup_record[0]['fields']['query_string'].replace(base_encoded_email, urlencode({"email_login": email}))
new_record = at.insert(lookup_record[0]['fields'])
short_url = {'short_url': new_record['id'].split('rec')[1],
'airtableID': new_record['id'], 'visits': 0}
at.update(new_record['id'], short_url)
mensaje = "Este es tu link para compartir el protocolo {} de Prescriptum: {}/{}/".format(lookup_record[0]['fields']['nombre_protocolo'], domain_url, short_url['short_url'])
send_sms(codigo_pais_movil+movil, mensaje, "Prescriptum")
return request.args
except Exception as e:
return jsonify(error=str(e)), 403
def main():
# get run time
run_time = pendulum.now('UTC').to_w3c_string()
# get API key from environment
airtable_api_key = os.environ['AIRTABLE_API_KEY']
# get arguments
argparser = init_argparse()
args = argparser.parse_args()
# set up Airtable connections
bases_table = Airtable(args.destination, 'Bases', api_key=airtable_api_key)
tables_table = Airtable(args.destination,
'Tables',
api_key=airtable_api_key)
fields_table = Airtable(args.destination,
'Fields',
api_key=airtable_api_key)
# create base record
data = {
'Name': args.name,
'Base ID': args.id,
'Base URL': 'https://airtable.com/' + args.id,
'Last Imported': run_time
}
bases_table.insert(data)
# parse JSON, write to base
with open(args.json) as json_file:
schema = json.load(json_file)
for table in schema:
# write table record
table = schema[table]
data = {
'Name': table['name'],
'Base': args.name,
'Last Imported': run_time
}
tables_table.insert(data)
for field in table['columns']:
# write field record
data = {
'Name': field['name'],
'Table': table['name'],
'Type': field['type'],
'Last Imported': run_time
}
fields_table.insert(data)
def submit_to_airtable(data, table_name):
"""
Wrapper function to submit to airtable
:param data: data to submit
:param table_name: table to submit to
:return: on success returns new entry, on error returns error message
"""
logger.info('## Attempting to submit to {} Table. data: {}'.format(table_name, data))
table = Airtable(intake_form_constants.AIRTABLE_BASE_KEY, table_name, api_key=os.environ['AIRTABLE_KEY'])
rec = table.insert(data)
if 'id' not in rec:
logger.warning(intake_form_constants.UNABLE_TO_CREATE_RECORD.format(rec))
return {
'statusCode': 400,
'body': json.dumps({
'message': intake_form_constants.UNABLE_TO_CREATE_RECORD.format(rec),
'received_data': data
})
}
logger.info('## Successfully submitted to {} Table. New id: {}'.format(table_name, rec['id']))
return rec
