找回密码
 立即注册

扫一扫,访问微社区

QQ登录

只需一步,快速开始

查看: 105|回复: 4

[求助] python 连接有组织的google spreadsheet

1

主题

4

帖子

4

积分

贫民

积分
4
ayj 发表于 2020-9-10 11:21:21 | 显示全部楼层 |阅读模式
我需要对google spreadsheet的数据进行处理更新,文件是有组织管理的,但是在连接登陆读写数据遇到了错误,问题可能出在授权的地方,请大咖帮忙看看怎么修改?
目前使用VPN连接的google

import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('C:\connect.json',scope)
client = gspread.authorize(creds)
gfile = client.open_by_key("1smjwKrfWDkNTvCBmT3NLiQHdSP_OUswX--Ud2iccjlM")
worksheet = gfile.sheet1
Test = worksheet.get_all_records()
print(Test)
报错如下
Traceback (most recent call last):
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 677, in urlopen
    chunked=chunked,
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 381, in _make_request
    self._validate_conn(conn)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 978, in _validate_conn
    conn.connect()
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connection.py", line 371, in connect
    ssl_context=context,
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\util\ssl_.py", line 384, in ssl_wrap_socket
    return context.wrap_socket(sock, server_hostname=server_hostname)
  File "C:\Users\ftjm196\AppData\Local\Programs\Python\Python37\lib\ssl.py", line 423, in wrap_socket
    session=session
  File "C:\Users\ftjm196\AppData\Local\Programs\Python\Python37\lib\ssl.py", line 870, in _create
    self.do_handshake()
  File "C:\Users\ftjm196\AppData\Local\Programs\Python\Python37\lib\ssl.py", line 1139, in do_handshake
    self._sslobj.do_handshake()
ConnectionResetError: [WinError 10054] An existing connection was forcibly closed by the remote host


During handling of the above exception, another exception occurred:


Traceback (most recent call last):
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\requests\adapters.py", line 449, in send
    timeout=timeout
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 727, in urlopen
    method, url, error=e, _pool=self, _stacktrace=sys.exc_info()[2]
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\util\retry.py", line 403, in increment
    raise six.reraise(type(error), error, _stacktrace)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\packages\six.py", line 734, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 677, in urlopen
    chunked=chunked,
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 381, in _make_request
    self._validate_conn(conn)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connectionpool.py", line 978, in _validate_conn
    conn.connect()
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\connection.py", line 371, in connect
    ssl_context=context,
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\urllib3\util\ssl_.py", line 384, in ssl_wrap_socket
    return context.wrap_socket(sock, server_hostname=server_hostname)
  File "C:\Users\ftjm196\AppData\Local\Programs\Python\Python37\lib\ssl.py", line 423, in wrap_socket
    session=session
  File "C:\Users\ftjm196\AppData\Local\Programs\Python\Python37\lib\ssl.py", line 870, in _create
    self.do_handshake()
  File "C:\Users\ftjm196\AppData\Local\Programs\Python\Python37\lib\ssl.py", line 1139, in do_handshake
    self._sslobj.do_handshake()
urllib3.exceptions.ProtocolError: ('Connection aborted.', ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))


During handling of the above exception, another exception occurred:


Traceback (most recent call last):
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\auth\transport\requests.py", line 183, in __call__
    method, url, data=body, headers=headers, timeout=timeout, **kwargs
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\requests\sessions.py", line 530, in request
    resp = self.send(prep, **send_kwargs)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\requests\sessions.py", line 643, in send
    r = adapter.send(request, **kwargs)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\requests\adapters.py", line 498, in send
    raise ConnectionError(err, request=request)
requests.exceptions.ConnectionError: ('Connection aborted.', ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))


The above exception was the direct cause of the following exception:


Traceback (most recent call last):
  File "C:/Users/ftjm196/PycharmProjects/pythonProject/sheet.py", line 9, in <module>
    worksheet = gfile.sheet1
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\gspread\models.py", line 123, in sheet1
    return self.get_worksheet(0)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\gspread\models.py", line 283, in get_worksheet
    sheet_data = self.fetch_sheet_metadata()
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\gspread\models.py", line 265, in fetch_sheet_metadata
    r = self.client.request('get', url, params=params)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\gspread\client.py", line 67, in request
    headers=headers,
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\requests\sessions.py", line 543, in get
    return self.request('GET', url, **kwargs)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\auth\transport\requests.py", line 460, in request
    self.credentials.before_request(auth_request, method, url, request_headers)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\auth\credentials.py", line 133, in before_request
    self.refresh(request)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\oauth2\service_account.py", line 361, in refresh
    access_token, expiry, _ = _client.jwt_grant(request, self._token_uri, assertion)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\oauth2\_client.py", line 153, in jwt_grant
    response_data = _token_endpoint_request(request, token_uri, body)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\oauth2\_client.py", line 105, in _token_endpoint_request
    response = request(method="POST", url=token_uri, headers=headers, body=body)
  File "C:\Users\ftjm196\PycharmProjects\pythonProject\venv\lib\site-packages\google\auth\transport\requests.py", line 188, in __call__
    six.raise_from(new_exc, caught_exc)
  File "<string>", line 3, in raise_from
google.auth.exceptions.TransportError: ('Connection aborted.', ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))


Process finished with exit code 1



回复

使用道具 举报

0

主题

709

帖子

709

积分

圣骑士

积分
709
sheeboard 发表于 2020-9-11 15:26:46 | 显示全部楼层
在墙外吗?
回复

使用道具 举报

1

主题

4

帖子

4

积分

贫民

积分
4
ayj  楼主| 发表于 2020-9-11 16:27:06 | 显示全部楼层
在的,我在公司网上,可以自由连接google文档,工作文档都在那里
回复 支持 反对

使用道具 举报

1

主题

4

帖子

4

积分

贫民

积分
4
ayj  楼主| 发表于 2020-9-14 14:26:52 | 显示全部楼层
现在感觉就是授权问题,导致无法连接google工作表,希望大咖指点
回复 支持 反对

使用道具 举报

1

主题

4

帖子

4

积分

贫民

积分
4
ayj  楼主| 发表于 2020-9-16 11:35:40 | 显示全部楼层
看来国内用Google文件的不多呀,找个了解的人不易
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表