def test_clean_data_obj_success(self):
rows = [{
'name': 'John',
'age': '40',
'active': '1957-1980',
'rate': '9.7'
}]
expected_data = {
'name': 'John',
'age': 40,
'active': '1957-1980',
'rate': 9.7
}
result = clean_data_obj(rows)
self.assertEqual(result[0], expected_data)
def test_clean_data_obj_javascript(self):
rows = [{
'name': 'John',
'age': '40',
'active': '<script src=/hacker/bad.js></script>',
'rate': '9.7'
}]
expected_data = {
'name': 'John',
'age': 40,
'active': '<script src=/hacker/bad.js></script>',
'rate': 9.7
}
result = clean_data_obj(rows)
self.assertEqual(result[0], expected_data)
def test_clean_data_obj_list_data(self):
row = ['John', '40', '1957-1980', '9.7']
expected_data = ['John', 40, '1957-1980', 9.7]
result = clean_data_obj(row)
self.assertEqual(result, expected_data)
def test_clean_data_obj_empty_obj(self):
result = clean_data_obj({})
self.assertEqual(result, {})
def import_from_gsheet_helper(user,
silo_id,
silo_name,
spreadsheet_id,
sheet_id=None,
partialcomplete=False):
msgs = []
if spreadsheet_id is None:
msgs.append({
'level': messages.ERROR,
'msg': 'A Google Spreadsheet is not selected to import data from.',
'redirect': reverse('index')
})
credential_obj = _get_credential_object(user)
if not isinstance(credential_obj, OAuth2Credentials):
msgs.append(credential_obj)
return msgs
defaults = {
'name': silo_name,
'description': 'Google Sheet Import',
'public': False,
'owner': user
}
silo, created = Silo.objects.get_or_create(
pk=None if silo_id == '0' else silo_id, defaults=defaults)
msgs.append({'silo_id': silo.id})
metadata, error = _get_gsheet_metadata(credential_obj, spreadsheet_id,
user)
if error:
if 'credential' in error:
return error.get('credential')
else:
msgs.append(error)
return msgs
spreadsheet_name = metadata.get('properties', {}).get('title', '')
gsheet_read = _get_or_create_read('GSheet Import', spreadsheet_name,
'Google Spreadsheet Import',
spreadsheet_id, user, silo)
if sheet_id:
gsheet_read.gsheet_id = sheet_id
gsheet_read.save()
sheet_name = 'Sheet1'
if gsheet_read.gsheet_id:
sheets = metadata.get('sheets', None)
for sheet in sheets:
properties = sheet.get('properties', None)
if properties:
if str(properties.get('sheetId')) == str(
gsheet_read.gsheet_id):
sheet_name = properties.get('title')
values, error = _fetch_data_gsheet(credential_obj, spreadsheet_id,
sheet_name)
if error:
msgs.append(error)
return msgs
unique_fields = silo.unique_fields.all()
skipped_rows = set()
headers = []
lvss = []
# get the column names
header = values.pop(0)
for h in header:
h = cleanKey(h)
headers.append(h)
data = _convert_gsheet_data(headers, values)
for r, row in enumerate(data):
filter_criteria = {}
# build filter_criteria if unique field(s) have been setup for this silo
for uf in unique_fields:
try:
if str(row[uf.name]).isdigit():
filter_criteria.update({str(uf.name): int(row[uf.name])})
else:
filter_criteria.update({str(uf.name): str(row[uf.name])})
except AttributeError as e:
logger.warning(e)
if filter_criteria:
filter_criteria.update({'silo_id': silo.id})
try:
lvs = LabelValueStore.objects.get(**filter_criteria)
lvs.edit_date = timezone.now()
except LabelValueStore.DoesNotExist:
lvs = LabelValueStore()
except LabelValueStore.MultipleObjectsReturned:
for k, v in filter_criteria.iteritems():
skipped_rows.add('{}={}'.format(k, v))
continue
else:
lvs = LabelValueStore()
# add the key and values to the document
row = clean_data_obj(row)
for key, val in row.iteritems():
val = smart_str(val, strings_only=True)
setattr(lvs, key, val)
lvs.silo_id = silo.id
lvs.read_id = gsheet_read.id
lvs.create_date = timezone.now()
lvs = calculateFormulaCell(lvs, silo)
if partialcomplete:
lvss.append(lvs)
else:
lvs.save()
addColsToSilo(silo, headers)
if skipped_rows:
skipped_str = ','.join(str(s) for s in skipped_rows)
msg = 'Skipped updating/adding records where {} because there are ' \
'already multiple records.'.format(skipped_str)
msgs.append({'level': messages.WARNING, 'msg': msg})
msgs.append({'level': messages.SUCCESS, 'msg': 'Operation successful'})
if partialcomplete:
return lvss, msgs
return msgs