我如何使用Python在Google融合表中插入一行 [英] How do I insert a row in my google fusion table using Python

问题描述

我正在研究一个项目，其中一部分涉及从python脚本向该项目的Google Fusion Table中插入行.在过去的几天里，我一直试图弄清楚该怎么做，我对此感到非常困惑.

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.

我的研究似乎表明我需要使用Oauth 2.0来访问API.这样做可以成功获取访问令牌，但似乎无法成功获取刷新令牌.我不确定这是否会妨碍我成功整合对Fusion Table和Python代码的访问的能力.

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.

我遇到的第二个问题是我不太了解如何正确编码在表中插入行的方式.我从中找到的大多数材料都来自不推荐使用的Fusion Tables SQL API，而且我还不完全了解这种新方法.

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!

因此，到目前为止，我一直在工作的代码如下:

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)

运行此命令我会得到

HTTP错误400:HTTP GET仅可用于选择查询.

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

我知道应该为INSERT做POST而不是GET，但我不确定是否需要更改代码中的内容才能实现. 很抱歉成为菜鸟.

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.

第二

很抱歉延长此时间，但我觉得有必要显示到目前为止的目标.我切换到库请求，事情变得有些容易，但是我仍然没有成功进行POST.我用于导入行的新代码如下:

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

哪个给我

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

推荐答案

如果您的应用程序需要脱机访问Google API，则授权码请求应包含access_type参数，该参数的值处于脱机状态.

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

然后，要使用刷新令牌获取访问令牌，您发送POST请求，其中包括值为refresh_token的grant_type.

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

基本上，SQL的工作方式是使用SQL语句的子集https://www.googleapis.com/fusiontables/v1/query?sql=STATEMENT_HERE

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

参考

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

由于使用的是不带数据参数的urllib2，因此默认为GET.要解决此问题，您应该使用允许明确指定方法的另一个HTTP库(例如 requests 或httplib)或执行以下操作:

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

重要提示:

1. 用猴子修补该方法以执行我们想要的操作(带有空主体的POST)，否则我们将收到HTTP Error 400: HTTP GET can only be used for SELECT queries.

手动指定我们没有正文(Content-Length为0)，否则我们将收到HTTP Error 411: Length Required.

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

必须使用双引号和单引号，或转义内部引号，才能通过查询提交字符串.换句话说，"INSERT INTO %s(EXAMPLE_COL1,EXAMPLE_COL2) VALUES(EXAMPLE_INFO1,EXAMPLE_INFO2)" % table_id不起作用.

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.

如果我们尝试使用上一行，我们将得到类似HTTP Error 400: Parse error near 'SOME_STRING' (line X, position Y)

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)

有关使用urllib2更改方法的信息，请参见:

See for info on changing method with urllib2:

是否有任何方法可以在python

这篇关于我如何使用Python在Google融合表中插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！