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: > 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`](http://docs.python-
requests.org/en/latest/) 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](http://stackoverflow.com/questions/111945/is-there-any-way-to-do-http-
put-in-python)

