如何将Python Sheets文件从Python 3（或2）保存为CSV？ [英] How do you save a Google Sheets file as CSV from Python 3 (or 2)?

问题描述

我正在寻找一种简单的方法来保存源自已发布的Google表格文档的csv文件？自发布以来，它可以通过直接链接访问（在下面的示例中进行了修改）。

我的所有浏览器都会提示我一旦保存csv文件
$ b 没有：

//docs.google.com/spreadsheet/ccc?key=0AoOWveO-dNo5dFNrWThhYmdYW9UT1lQQkE&output=csv'

f = urllib.request.urlopen（DOC_URL）
cont = f.read（SIZE ）
f.close（）
cont = str（cont，'utf-8'）
print（cont）

，也不是：

  req = urllib.request.Request（DOC_URL） 
 req.add_header（'User-Agent'，'Mozilla / 5.0（Windows NT 6.1）AppleWebKit / 537.13（KHTML，如Gecko）Chrome / 24.0.1284.0 Safari / 537.13'）
f = urllib.request .urlopen（req）
 print（f.read（）。decode（'utf-8'））
  

只会打印html内容。

（在阅读这篇文章后试用了第二个版本：）

关于我在做什么错误的任何想法？我从我的Google帐户注销，如果这值得任何东西，但这可以从我尝试的任何浏览器中运行。据我了解，Google Docs API尚未在Python 3上移植，并且由于我个人使用的小项目具有玩具的大小，如果从一开始就使用它，它甚至没有太大意义，如果我可以绕过它。

在第二次尝试中，我离开了'用户代理'，因为我认为可能请求被认为是来自脚本（B / C，不存在识别信息） ）可能会被忽略，但它并没有什么不同。

解决方案

Google通过一系列cookie响应最初的请求设置302重定向。如果您不存储并在请求之间重新提交cookie，它会将您重定向到登录页面。

因此，这个问题与User-Agent头不符，事实上，默认情况下， urllib.request.urlopen


以下代码在公共电子表格中的工作正常，该位置可用于 DOC_URL ：

 >>> from http.cookiejar import CookieJar 
>>> from urllib.request import build_opener，HTTPCookieProcessor 
>>> opener = build_opener（HTTPCookieProcessor（CookieJar（）））
>>> resp = opener.open（DOC_URL）
>>> ＃应该真正解析resp.getheader（'content-type'）进行编码。 
>>> csv_content = resp.read（）。decode（'utf-8'）
  





---





在向你展示了如何在香草python中做到这一点之后，我现在说正确的方法是去使用最优秀的 请求库 。这是 非常详细的文档 ，使这些任务令人难以置信的愉快完成。

例如，要使用请求获得与上面相同的 csv_content code> library很简单：

 >>>导入请求
>>> csv_content = requests.get（DOC_URL）.text 
  

单行表示您的意图更清晰。编写起来更容易，阅读起来也更容易。做你自己 - 和任何分享你的代码库的人 - 帮忙，只用请求。

I am looking for a simple way to save a csv file originating from a published Google Sheets document? Since it's published, it's accessible through a direct link (modified on purpose in the example below).

All my browsers will prompt me to save the csv file as soon as I launch the link.

Neither:

DOC_URL = 'https://docs.google.com/spreadsheet/ccc?key=0AoOWveO-dNo5dFNrWThhYmdYW9UT1lQQkE&output=csv'    

f = urllib.request.urlopen(DOC_URL)
cont = f.read(SIZE)
f.close()
cont = str(cont, 'utf-8')
print(cont)

, nor:

req = urllib.request.Request(DOC_URL)
req.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.13 (KHTML, like Gecko) Chrome/24.0.1284.0 Safari/537.13')
f = urllib.request.urlopen(req)
print(f.read().decode('utf-8'))

print anything but html content.

(Tried the 2nd version after reading this other post: Download google docs public spreadsheet to csv with python .)

Any idea on what I am doing wrong? I am logged out of my Google account, if that worths to anything, but this works from any browser that I tried. As far as I understood, the Google Docs API is not yet ported on Python 3 and given the "toy" magnitude of my little project for personal use, it would not even make too much sense to use it from the get-go, if I can circumvent it.

In the 2nd attempt, I left the 'User-Agent', as I was thinking that maybe requests thought as coming from scripts (b/c no identification info is present) might be ignored, but it didn't make a difference.

解决方案

Google responds to the initial request with a series of cookie-setting 302 redirects. If you don't store and resubmit the cookies between requests, it redirects you to the login page.

So, the problem is not with the User-Agent header, it's the fact that by default, urllib.request.urlopen doesn't store cookies, but it will follow the HTTP 302 redirects.

The following code works just fine on a public spreadsheet available at the location specified by DOC_URL:

>>> from http.cookiejar import CookieJar
>>> from urllib.request import build_opener, HTTPCookieProcessor
>>> opener = build_opener(HTTPCookieProcessor(CookieJar()))
>>> resp = opener.open(DOC_URL)
>>> # should really parse resp.getheader('content-type') for encoding.
>>> csv_content = resp.read().decode('utf-8')

---

Having shown you how to do it in vanilla python, I'll now say that the Right Way™ to go about this is to use the most-excellent requests library. It is extremely well documented and makes these sorts of tasks incredibly pleasant to complete.

For instance, to get the same csv_content as above using the requests library is as simple as:

>>> import requests
>>> csv_content = requests.get(DOC_URL).text

That single line expresses your intent more clearly. It's easier to write and easier to read. Do yourself - and anyone else who shares your codebase - a favor and just use requests.

这篇关于如何将Python Sheets文件从Python 3（或2）保存为CSV？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！