How do I insert a row in my google fusion table using Python

Question

I am working on a project and part of it involves inserting rows in to a Google Fusion Table for the Project from a python script. I have spent the last couple days trying to figure out just how to do that and I am officially confused.

My research seems to indicate that I need to use Oauth 2.0 to access the API. In doing so I can successfully get an access token but I can't seem to successfully get a refresh token. I'm not sure if this is going to hamper my ability to successfully integrate access to my Fusion Table with my Python code.

The second problem I am having is that I don't really understand how exactly to code inserting a row in my table. Most of the material I have found on it is from the deprecated Fusion Tables SQL API and I don't fully understand the new way of doing it.

I'm a beginner at this sort of thing and any direction to help me is very much appreciated!

Edit: So the code I have working so far looks like this:

client_id = "<client_i>"
client_secret = "<client_secret>"
table_id = "<table_id>"

access_token = ""
refresh_token = "<refresh_token>"

#   the refresh token is used to request a new access token
data = urllib.urlencode({
  'client_id': client_id,
  'client_secret': client_secret,
  'refresh_token': refresh_token,
  'grant_type': 'refresh_token'})
request = urllib2.Request(
  url='https://accounts.google.com/o/oauth2/token',
  data=data)
request_open = urllib2.urlopen(request)
response = request_open.read()
request_open.close()
tokens = json.loads(response)
access_token = tokens['access_token']

#   Read the table
request_read = urllib2.Request(
  url='https://www.google.com/fusiontables/api/query?%s' % \
    (urllib.urlencode({'access_token': access_token,
                       'sql': 'SELECT * FROM table_id'})))
request_open = urllib2.urlopen(request_read)
response = request_open.read()
request_open.close()
print response

And my code for trying to insert a new row into my table:

date = str(datetime.now().date())
time = str(datetime.now().time())
query = 'INSERT INTO table_id (Date,Time,Saskatoon,Regina,MeadowLake)VALUES(date,time,60.01,60.02,59.99)'
data = urllib2.Request(
  url='https://www.google.com/fusiontables/api/query?%s' % \
    (urllib.urlencode({'access_token': access_token,
                       'sql': query})))
request_open = urllib2.urlopen(data)

When I run this i get

HTTP Error 400: HTTP GET can only be used for select queries.

I am know I'm supposed to be making a POST not a GET for the INSERT, I'm just not sure what needs to change in my code for that to happen. Sorry for being a noob.

2ND EDIT:

Sorry for making this longer but I feel it is pertinent to show where I've gotten so far. I switched to the library requests and things have gotten somewhat easier however I still haven't successfully made a POST. My new code for importing rows is as follows:

def importRows(self):
    print 'IMPORT ROWS'
    date = str(datetime.now().date())
    time = str(datetime.now().time())
    data = {'Date': date,
            'Time': time,
            'Saskatoon': '60.01',
            'Regina': '59.95'}
    url = 'https://www.googleapis.com/upload/fusiontables/v1/tables/%s/import/%s' % \
          (tableid, self.params) # self.params is access token
    importRow = requests.post(url, params=data)

    print importRow.status_code
    print importRow.text

Which gives me

400
{
 "error": {
  "errors": [
   {
    "domain": "fusiontables",
    "reason": "badImportInputEmpty",
    "message": "Content is empty."
   }
  ],
  "code": 400,
  "message": "Content is empty."
 }
}

Answer 1

If your application needs offline access to a Google API, then the request for an authorization code should include the access_type parameter, where the value of that parameter is offline.

https://developers.google.com/accounts/docs/OAuth2WebServer#offline

Then, to obtain an access token using the refresh token you send a POST request including grant_type with value refresh_token.

Basically, the way SQL works is you send POST requests using a subset of SQL statements https://www.googleapis.com/fusiontables/v1/query?sql=STATEMENT_HERE

Refer to

https://developers.google.com/fusiontables/docs/v1/reference/query https://developers.google.com/fusiontables/docs/v1/sql-reference

Edit:

Since you are using urllib2 without a data parameter, it defaults to GET. To fix this you should either use another HTTP library that allows for explicitly specifying method (like requests or httplib) or do something like this:

query = "INSERT INTO %s(EXAMPLE_COL1,EXAMPLE_COL2) VALUES"\
        "('EXAMPLE_INFO1','EXAMPLE_INFO2')" % table_id # Single quotes
opener = urllib2.build_opener(urllib2.HTTPHandler)
request = urllib2.Request('https://www.google.com/fusiontables/api/query?%s' % \
    (urllib.urlencode({'access_token': access_token,
                       'sql': query})),
    headers={'Content-Length':0})      # Manually set length to avoid 411 error
request.get_method = lambda: 'POST'    # Change HTTP request method
response = opener.open(request).read()
print response

Important to notice:

1. Monkey patch the method to do what we want (POST with an empty body) otherwise we would receive HTTP Error 400: HTTP GET can only be used for SELECT queries.

2. Manually specify that we do not have a body (Content-Length is 0) otherwise we would receive HTTP Error 411: Length Required.

3. Must use double quotes with single quotes inside or escape the inner quotes to submit strings via the query. In other words, "INSERT INTO %s(EXAMPLE_COL1,EXAMPLE_COL2) VALUES(EXAMPLE_INFO1,EXAMPLE_INFO2)" % table_id does not work.

   If we tried to use the previous line we would get something like HTTP Error 400: Parse error near 'SOME_STRING' (line X, position Y)

See for info on changing method with urllib2:

Is there any way to do HTTP PUT in python