CODEWITHSUNDEEP
google-app-enginegoogle-fusion-tablesgoogle-api-python-client
SteveShaffer
SteveShaffer

Reputation: 1567

How to write more than ~1,500 characters to a fusion table cell via the SQL API

I have an AppEngine app using the Google API Python Client to access the Fusion Tables API over OAuth. When trying to run UPDATE commands, the API client is putting my whole SQL statement into the query string in the URL.

So when I write a SQL statement like this...

UPDATE <table ID> SET <column> = 'some really long piece of text...' WHERE ROWID = '1'

...I get an API call like this:

POST https://www.googleapis.com/fusiontables/v1/query?sql=UPDATE+<table ID>+SET+<column>+%3D+%27some+really+long+piece+of+text...%27+WHERE+ROWID+%3D+%271%27&alt=json

All this works fine for most things. But I'm encountering errors when writing more than ~1,500 characters (depending on how many of those are special characters I have to escape) to that cell. The answer to another question says the limit to the number of characters in a cell is 1,000,000. I'm assuming this may be because the URL is getting just way too long (for something in the pipeline from AppEngine to the Fusion Tables API servers), maybe kind of like the issue addressed in this question.

With other APIs, I'm used to sending parameters in form data for POST requests not the query string, which keeps the URL a manageable size. But the Fusion Tables API docs seem to suggest that the query string is the proper place and that nothing should be sent in the request body. The API client seems to be dutifully following this pattern (and in fact using that as the default behavior for ALL Google APIs??).

So my question is threefold:

  1. Does anyone know if the URL can get too long as I suspect is happening?
  2. Is the query string really the only place to send the SQL statement or will if I find a way to include it in the request body will the API accept that?
  3. If the query string really is the only way and it really can get too long, is there another way to post large strings to fusion table cells?

Upvotes: 1

Views: 132

Answers (1)

SteveShaffer
SteveShaffer

Reputation: 1567

So I tried it. The answer to number 2 is that you CAN put the query in the request body as form data and the API will take it (contrary to what the docs suggest). My request to Fusion Tables from App Engine now looks like this:

import httplib2
import urllib

value = 'Some really long string...'

# http is an instance of httplib2.Http
http.request('https://www.googleapis.com/fusiontables/v1/query?alt=json',
             method='POST',
             body=urllib.urlencode({
               'sql': unicode('UPDATE <table ID>' +
                              ' SET <column>=\'' + value + '\'' + 
                              ' WHERE ROWID = \'1\'').encode('utf-8')
             }),
             headers={'Content-Type': 'application/x-www-form-urlencoded'})

Note that I believe the Content-Type header is necessary, but I haven't tried it without it.

I also left some unicode and UTF-8 encoding stuff in there because chances are, for anyone who needs to support a few thousand characters, a few of those characters might be non-ascii and urllib.urlencode doesn't like non-ascii characters...

I'd still appreciate an answer to numbers 1 and 3 if anyone has any more information, but this seems to work for me for now. I'm curious as to why using form data in the request body wasn't the default approach from the beginning for the Fusion Tables team...

Upvotes: 1

Related Questions