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 make_record_updates( comparison_map: Dict[str, Dict[str, ATRecord]], assume_newer: bool = False, delete_unmatched_except: Optional[Tuple] = None, ): """Update Airtable from newer source records""" 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) an_only = comparison_map["an_only"] did_update = False if an_only: did_update = True prinl(f"Doing updates for {record_type} records...") prinl(f"Uploading {len(an_only)} new record(s)...") records = [r.all_fields() for r in an_only.values()] at.batch_insert(records, typecast=at_typecast) an_newer: Dict[str, ATRecord] = comparison_map["an_newer"] if an_newer: update_map: Dict[str, Dict[str, Any]] = {} for key, record in an_newer.items(): updates = record.find_at_field_updates(assume_newer=assume_newer) if updates: update_map[record.at_match.record_id] = updates if update_map: if not did_update: prinl(f"Doing updates for {record_type} records...") did_update = True prinl(f"Updating {len(update_map)} existing record(s)...") for i, (record_id, updates) in enumerate(update_map.items()): at.update(record_id, updates, typecast=at_typecast) if (i + 1) % 25 == 0: prinlv(f"Processed {i+1}/{len(update_map)}...") if not did_update: prinlv(f"No updates required for {record_type} records.") at_only = comparison_map["at_only"] if at_only and delete_unmatched_except: field_name = delete_unmatched_except[0] field_val = delete_unmatched_except[1] record_ids = [ record.record_id for record in at_only.values() if record.custom_fields.get(field_name) != field_val ] if record_ids: prinl( f"Deleting {len(record_ids)} unmatched Airtable record(s)...") at.batch_delete(record_ids)
# 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([{ "text": "A", "number": 1, "boolean": True } for _ in range(100)]) # Batch Delete records = airtable.batch_delete([r["id"] for r in records]) assert len(records) == 100
def copy_to_airtable(self): """ copies the DB to airtables """ try: logger.debug('Starting Airtable copy...') # set the ready state to false to indicate that it's not ready state_table = Airtable(self.airtable_basekey, 'State', self.airtable_apikey) ready_id = state_table.match('key', 'ready')['id'] fields = {'key': 'ready', 'value': 'false'} state_table.replace(ready_id, fields) # delete previous table entries logger.debug("Deleting previous table entries...") pilot_table = Airtable(self.airtable_basekey, 'Pilots', self.airtable_apikey) character_table = Airtable(self.airtable_basekey, 'Characters', self.airtable_apikey) attribute_table = Airtable(self.airtable_basekey, 'Attributes', self.airtable_apikey) attribute_groups_table = Airtable(self.airtable_basekey, 'AttributeGroups', self.airtable_apikey) pilot_table.batch_delete( [entry['id'] for entry in pilot_table.get_all()]) character_table.batch_delete( [entry['id'] for entry in character_table.get_all()]) attribute_table.batch_delete( [entry['id'] for entry in attribute_table.get_all()]) attribute_groups_table.batch_delete( [entry['id'] for entry in attribute_groups_table.get_all()]) logger.debug("Previous table entries deleted!") # copy pilots table logger.debug("Copying Pilots table...") pilots = session.query(Pilot) pilot_records = [] for pilot in pilots: pilot_records.append({ 'id': pilot.id, 'discord_id': pilot.discord_id, 'discord_name': pilot.discord_name, 'discord_discriminator': pilot.discord_discriminator }) pilot_table.batch_insert(pilot_records) logger.debug("Pilots table copied!") # copy characters table logger.debug("Copying Characters table...") characters = session.query(Character) character_records = [] for character in characters: character_records.append({ 'id': character.id, 'pilot_id': character.pilot_id, 'name': character.name }) character_table.batch_insert(character_records) logger.debug("Characters table copied!") # copy attributes logger.debug("Copying Attribute table...") attributes = session.query(Attribute) attribute_records = [] for attribute in attributes: attribute_records.append({ 'id': attribute.id, 'attribute_group_id': attribute.attribute_group_id, 'key': attribute.key, 'value': attribute.value, 'friendly_name': attribute.friendly_name }) attribute_table.batch_insert(attribute_records) logger.debug("Attribute table copied!") # copy attributegroups logger.debug("Copying AttributeGroup table...") attribute_groups = session.query(AttributeGroup) attribute_group_records = [] for attribute_group in attribute_groups: attribute_group_records.append({ 'id': attribute_group.id, 'pilot_id': attribute_group.pilot_id, 'name': attribute_group.name, 'description': attribute_group.description }) attribute_groups_table.batch_insert(attribute_group_records) logger.debug("AttributeGroup table copied!") # set the ready state to true to indicate that it's ready state_table = Airtable(self.airtable_basekey, 'State', self.airtable_apikey) ready_id = state_table.match('key', 'ready')['id'] fields = {'key': 'ready', 'value': 'true'} state_table.replace(ready_id, fields) logger.debug('Airtable copy complete!') except: logger.error( f"Failed to copy to airtable:\n{traceback.format_exc()}")
class Table: """ Represents an Airbase table. """ def __init__(self, base, table_name, relations): self.base = base self.table_name = table_name self.relations = relations self.airtable = Airtable(base.id, table_name, base.api_key) self.load() def load(self): self.data = self.airtable.get_all() def insert(self, row): result = self.airtable.insert(row) self.data.append(result) self.link() return result def update(self, id, row): return self.airtable.update(id, row) def get(self, id): for row in self.data: if row['id'] == id: return row return None def find(self, fields, first=False): results = [] for row in self.data: match = True for k, v in fields.items(): if k not in row['fields']: if v is not None: match = False elif row['fields'][k] != v: match = False if match: results.append(row) if first: if len(results) == 0: return None else: return results[0] return results def get_or_insert(self, fields, extra=None): """ Get or insert and get the first record that matches the fields. When inserting you can add additional things using the extras value which should be a dictionary of names and values to set in addition to the supplied fields. """ r = self.find(fields, first=True) if not r: f = fields if extra: f.update(extra) r = self.insert(f) return r def link(self): """ Use the table's schema relations to turn IDs into objects. """ for row in self.data: for prop, other_table_name in self.relations.items(): other_table = self.base.tables[other_table_name] if prop in row['fields']: value = row['fields'][prop] if type(value) == list: new_value = [] for v in value: new_value.append(other_table.get(v)) row['fields'][prop] = new_value else: row['fields'][prop] = other_table.get(value) def wipe(self): """ Remove all rows from the table. """ self.load() ids = [row['id'] for row in self.data] self.airtable.batch_delete(ids)
def handle(self, *args, **options): airtable = Airtable(settings.AIRTABLE_BASE_ID, settings.AIRTABLE_TABLE_NAME, api_key=settings.AIRTABLE_API_KEY) members = airtable.get_all() by_email = {} by_name = {} for m in members: email = m['fields'].get('Email', '').strip().lower() name = m['fields'].get('Name', '').strip().lower() by_email[email] = by_email.get(email, ()) + (m, ) by_name[name] = by_name.get(name, ()) + (m, ) allDupes = list(by_email.iteritems()) + list(by_name.iteritems()) for (email, rows) in allDupes: if len(email) == 0: continue if len(rows) > 1: print '%s:' % (email) idx = 1 conflictingFields = [] for r in rows: for (fieldName, fieldValue) in r['fields'].iteritems(): for row in rows: if fieldName not in COMPUTED_FIELDS and row[ 'fields'].get(fieldName) != fieldValue: conflictingFields.append(fieldName) for row in rows: print "\t%s" % (idx) for fieldName in set(conflictingFields): fieldPad = ' ' * (25 - len(fieldName)) print "\t\t%s:%s%s" % (fieldName, fieldPad, row['fields'].get(fieldName)) idx += 1 chosenIdx = input("Select the row to save, enter 0 to skip: ") if chosenIdx == 0: continue i = 0 deleted = [] merged = {} for f in set(conflictingFields): merged[f] = rows[chosenIdx - 1]['fields'].get(f, None) for row in rows: if i != chosenIdx - 1: for f in set(conflictingFields): curVal = merged.get(f, None) if curVal is None or curVal == '': merged[f] = row['fields'].get(f, curVal) if type(curVal) is list: merged[f] += row['fields'].get(f, []) deleted.append(row['id']) i += 1 print "\tmerged:" for fieldName in set(conflictingFields): fieldPad = ' ' * (25 - len(fieldName)) print "\t\t%s:%s%s" % (fieldName, fieldPad, merged.get(fieldName)) doCommit = raw_input("Commit? [N/y]") if doCommit is None: continue if doCommit.strip().lower() == "y": airtable.batch_delete(deleted) airtable.update(rows[chosenIdx - 1]['id'], merged) pass