3
\$\begingroup\$

I'm using the old Google Data API to access several spreadsheets and put their contents in a database using Flask and SQLAlchemy. Each run deletes the contents of the database tables and repopulates them with results of the API call.

I'm trying to detect API changes and abort the operation to prevent leaving the tables empty or filling them with garbage. I do this first by checking the status code of the API request. If it's anything other than 200 I abort the operation. To catch when the URL is valid but the JSON result has a new format, I catch KeyErrors when parsing the JSON.

Each time one of these conditions occurs I rollback the database session and exit the function with 1.

I'm worried that my detection coverage is spotty and I'm left feeling there should be a better way...

def get_google_content(key, db_table):
    if DEBUG:
        print 'Fetching content from Google Drive'
    spreadsheet_url = ('https://spreadsheets.google.com/feeds/worksheets/'
                       '{}/public/basic?alt=json'.format(key))
    spreadsheet_req = requests.get(spreadsheet_url)
    if spreadsheet_req.status_code != 200:
        print 'Failed to retrieve spreadsheet from {}'.format(spreadsheet_url)
        print 'Google Spreadsheet returned {}'.format(
                spreadsheet_req.status_code)
        db.session.rollback()
        return 1

    sheet_ids = list(OrderedDict.fromkeys(re.findall(r'/public/basic/(\w*)',
                     spreadsheet_req.text, flags=0)))

    # The only trivial way to catch removed spreadsheet data is to delete the
    # table and fill it all out again
    db.session.query(db_table).delete()

    for sheet_id in sheet_ids:
        worksheet_url = ('https://spreadsheets.google.com/feeds/list/{}/{}/'
                         'public/values?alt=json'.format(key, sheet_id))
        worksheet_req = requests.get(worksheet_url)
        if worksheet_req.status_code != 200:
            print 'Failed to retrieve spreadsheet from {}'.format(worksheet_url)
            print 'Google Spreadsheet returned {}'.format(
                    worksheet_req.status_code)
            db.session.rollback()
            return 1

        try:
            sheet_name = worksheet_req.json()['feed']['title']['$t']
            for row in worksheet_req.json()['feed']['entry']:
                db_row = db_table(column1=sheet_name,
                                  column1=row['gsx$location']['$t'],
                                  column2=row['gsx$caption']['$t'])
                db.session.add(db_row)
        except KeyError:
            print 'KeyError accessing Google Spreadsheet JSON from {}'.format(
                worksheet_url)
            print 'API changed?'
            db.session.rollback()
            return 1

        db.session.commit()
        return 0

Here's the relevant route:

@app.route('/admin/refresh_content')
def refresh_content():
    spreadsheet_key1 = 'key_1'
    spreadsheet_key2 = 'key_2'
    spreadsheet_key3 = 'key_3'

    sheet1_status = get_google_content(spreadsheet_key1, Table1)
    sheet2_status = get_google_content(spreadsheet_key2, Table2)
    sheet3_status = get_google_content(spreadsheet_key3, Table3)

    if sheet1_status + sheet2_status + sheet3_status > 0:
        return '<h1>Failure</h1><br>Unable to update spreadsheets'
    else:
        return '<h1>Success</h1><br>Updated spreadsheets'
\$\endgroup\$
2
\$\begingroup\$

Some comments:

  • Instead of using print statements, the logging module could be used
  • The return value of get_google_content should be a boolean value (True on success, False on failure)

Regarding ways to make the refresh_content method a little bit more pythonic, I would probably use a for loop and the all built-in function to make sure that all the calls to get_google_content were successful.

@app.route('/admin/refresh_content')
def refresh_content():
    spreadsheet_keys = ['key_1', 'key_2', 'key_3']
    tables = [Table1, Table2, Table3]
    sheet_statuses = [
      get_google_content(spreadsheet_key, table)
      for spreadsheet_key, table
      in zip(spreadsheet_keys, tables)
    ]

    if all(sheet_statuses):
        return '<h1>Success</h1><br>Updated spreadsheets'
    else:
        return '<h1>Failure</h1><br>Unable to update all spreadsheets'

There are other strategies, but that depends on different things like whether you want to stop on the first failure or not.

I hope this helps.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the python tips---this is helpful. However, I'm mostly interested in advice on way of detecting and handling unforeseen API changes in a safe way. \$\endgroup\$ – drs Jul 27 '14 at 17:38
  • \$\begingroup\$ It looks like it's possible to specify a version in your request. As long as the version you're working on is supported, you can stick to it to avoid any of the problems of API changes. \$\endgroup\$ – jcollado Jul 28 '14 at 11:43
  • \$\begingroup\$ It's confusing but the link you provided is for the new Google Apps API. I'm consuming a legacy API that is specific to only a few Google apps. \$\endgroup\$ – drs Jul 28 '14 at 12:31
  • \$\begingroup\$ Ok, now I understand better what is the problem. I'm not familiar with that API, but I'd look into fields that can be use to check the response is as you expect. If there's no such a field, I'd recommend validation through a schema to make sure all the fields that you need are there with the kind of values that expect. You can use either a json schema or a validation library in python that provides its own way to define schemas. \$\endgroup\$ – jcollado Jul 28 '14 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.