fusion table creation using service account in python

Question

I'm trying to create a fusion table from my server running django with the following code but it keeps failing with a 500 server error.

scope = ['https://www.googleapis.com/auth/fusiontables']
credentials = ServiceAccountCredentials.from_json_keyfile_name(EE_CREDENTIALS, scope)
http_auth = credentials.authorize(Http())

def create_table(name, description, columns, data=None):
    ft_service = build('fusiontables', 'v2', http_auth)
    body = dict(name=name, description=description, columns=columns)
    table = ft_service.table()
    result = table.insert(body=body).execute(num_retries=3) # failing here
    if data is not None:
        if not os.path.exists(TEMP_DIRPATH):
            os.makedirs(TEMP_DIRPATH)
        keys = data[0].keys()
        if len(columns) != len(keys):
            raise ValueError("mismatch in number of columns")
        filename = TEMP_DIRPATH + str(result.tableId) + ".csv"
        with open(filename, 'wb') as upload_file:
            dict_writer = csv.DictWriter(upload_file, keys)
            dict_writer.writeheader()
            dict_writer.writerows(data)
        ft_service.importRows(tableId=result.tableId, media_body=filename, startLine=1, isStrict=True,
                              encoding="auto-detect", delimiter=",").execute(num_retries=3)
    return result.tableId


def test_create_table(filename):
    data = []
    columns = []
    with open(filename, 'rb') as csvfile:
        reader = csv.reader(csvfile)
        for row_index, row in enumerate(reader):
            if row_index == 0:
                header = list(row)
                for col_index, col in enumerate(row):
                    if col_index == 24 or col_index == 25:
                        columns.append({"name": header[col_index], "type": "LOCATION"})
                    else:
                        columns.append({"name": header[col_index], "type": "STRING"})
            else:
                # 24 and 25 are latitude and longitude.
                if caught(float, row[24]) or caught(float, row[25]):
                    continue
                properties = {}
                for col_index, col in enumerate(row):
                    # 21 is ch4
                    if col_index == 21:
                        properties[header[col_index]] = 0 if caught(float, col) else float(col)
                    else:
                        properties[header[col_index]] = col
                data.append(properties)
    table_id = create_table('chino-20150110', 'locality = chino and date = 20150110', columns, None)
    print "created fusion table id is " + str(table_id)


test_create_table('C:/Users/JohnsonCharles/Desktop/chino-20150110.csv')

And the error I get is this:

googleapiclient.errors.HttpError: <HttpError 500 when requesting https://www.googleapis.com/fusiontables/v2/tables?alt=json returned "Backend Error">

I'm also wondering on how does it know which google drive to use. Since I'm creating the fusion tables from the backend how can I make my application use a specific google drive for fusion tables creation?

Jo Kachikaran · Accepted Answer · 2016-05-21 09:48:57Z

This post helped solve the issue. isExportable attribute must be set to true explicitly when trying to create a fusion table using service account. The post also explained my concern on which drive would be used. By default, it would be the drive of the user who created the fusion table - in this case it is the service account. It clearly explained how to give permission to other users but the post is in Java. I tried implementing the same in python and here is my updated code for anyone facing the same issue.

from googleapiclient.discovery import build
from httplib2 import Http
from oauth2client.client import GoogleCredentials
from googleapiclient.http import MediaFileUpload

scopes = ['https://www.googleapis.com/auth/fusiontables', 'https://www.googleapis.com/auth/drive']
credentials = GoogleCredentials.from_stream(EE_CREDENTIALS).create_scoped(scopes=scopes)
http_auth = credentials.authorize(Http())


def create_table(name, description, columns, data=None):
    ft_service = build(serviceName='fusiontables', version='v2', http=http_auth, credentials=credentials)
    drive_service = build(serviceName='drive', version='v3', http=http_auth, credentials=credentials)
    body = dict(name=name, description=description, columns=columns, isExportable=True)
    table = ft_service.table()
    result = table.insert(body=body).execute()
    permissions = drive_service.permissions()
    permissions.create(fileId=result["tableId"],
                       body={"emailAddress": "<your email id>@gmail.com", "type": "user", "role": "writer"},
                       sendNotificationEmail=False).execute()
    if data is not None:
        if not os.path.exists(TEMP_DIRPATH):
            os.makedirs(TEMP_DIRPATH)
        keys = [column["name"] for column in columns]
        filename = TEMP_DIRPATH + str(result["tableId"]) + ".csv"
        with open(filename, 'wb') as upload_file:
            dict_writer = csv.DictWriter(upload_file, keys)
            dict_writer.writeheader()
            dict_writer.writerows(data)
        media_body = MediaFileUpload(filename=filename, mimetype="application/octet-stream")
        table.importRows(tableId=result["tableId"], media_body=media_body, startLine=1,
                         isStrict=True, encoding="UTF-8", delimiter=",").execute()
    return result["tableId"]

See this link for drive service argument details. Note that the drive api I used here is v3 and if you want to change the visibility of the created fusion table, you have to set allowFileDiscovery to False and the type to domain or anyone. By default, it would be private but accessible to the users you have shared with.

By the way, I was concerned with the space that the fusion tables would occupy on the google drive and was the reason behind my question on creating it in a specific drive. But from the details of the fusion table in my google drive, they are free as of this writing.

Collectives™ on Stack Overflow

fusion table creation using service account in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related