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
