文章/答案/技术大牛

发布

社区首页 >问答首页 >用Python连接

问用Python连接
EN

Stack Overflow用户

提问于 2022-08-30 01:55:56

回答 1查看 103关注 0票数 0

我正试图通过python将数据上传到google。

但是我发现我必须使用管理员来运行脚本。

我的电脑操作系统是Window10。

下面是我的python脚本。

import gspread
from google.oauth2.service_account import Credentials

scopes = ["https://www.googleapis.com/auth/spreadsheets"]
credentials = Credentials.from_service_account_file(filename='C:/Users/KCL47/Desktop/googleSheetUpdate/credentials.json', scopes=scopes)
client = gspread.authorize(credentials)
sheet = client.open_by_key("1DjCtgGxJapLNI44KMppmJZGCMjMVLgjmT__3xPdE0Sk").sheet1
sheet.clear()
sheet.append_row(values=header)
sheet.append_rows(values=records)

文件credentials.json，我按照本网站创建json文件。

我的Python版本是3.10.6。

大多数图书馆都更新为最新版本。

当我使用管理员运行脚本时，没有任何问题。

但是当我使用普通用户运行脚本时，我得到了以下错误。

Traceback (most recent call last):
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 703, in urlopen
    httplib_response = self._make_request(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 386, in _make_request
    self._validate_conn(conn)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 1042, in _validate_conn
    conn.connect()
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connection.py", line 414, in connect
    self.sock = ssl_wrap_socket(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\util\ssl_.py", line 449, in ssl_wrap_socket
    ssl_sock = _ssl_wrap_socket_impl(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\util\ssl_.py", line 493, in _ssl_wrap_socket_impl
    return ssl_context.wrap_socket(sock, server_hostname=server_hostname)
  File "C:\Python\lib\ssl.py", line 513, in wrap_socket
    return self.sslsocket_class._create(
  File "C:\Python\lib\ssl.py", line 1071, in _create
    self.do_handshake()
  File "C:\Python\lib\ssl.py", line 1342, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:997)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\requests\adapters.py", line 489, in send
    resp = conn.urlopen(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 815, in urlopen
    return self.urlopen(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 815, in urlopen
    return self.urlopen(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 815, in urlopen
    return self.urlopen(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\connectionpool.py", line 787, in urlopen
    retries = retries.increment(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\urllib3\util\retry.py", line 592, in increment
    raise MaxRetryError(_pool, url, error or ResponseError(cause))
urllib3.exceptions.MaxRetryError: HTTPSConnectionPool(host='oauth2.googleapis.com', port=443): Max retries exceeded with url: /token (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:997)')))

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\auth\transport\requests.py", line 193, in __call__
    response = self.session.request(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\requests\sessions.py", line 587, in request
    resp = self.send(prep, **send_kwargs)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\requests\sessions.py", line 701, in send
    r = adapter.send(request, **kwargs)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\requests\adapters.py", line 563, in send
    raise SSLError(e, request=request)
requests.exceptions.SSLError: HTTPSConnectionPool(host='oauth2.googleapis.com', port=443): Max retries exceeded with url: /token (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:997)')))

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

Traceback (most recent call last):
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\updateGoogleSheet.py", line 58, in <module>
    sheet = client.open_by_key("1VakExMG9DNG4bKaQk9_2zHCRg-cd3_V-ypdHboE1zsk").sheet1
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\gspread\client.py", line 164, in open_by_key
    return Spreadsheet(self, {"id": key})
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\gspread\spreadsheet.py", line 33, in __init__
    metadata = self.fetch_sheet_metadata()
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\gspread\spreadsheet.py", line 253, in fetch_sheet_metadata
    r = self.client.request("get", url, params=params)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\gspread\client.py", line 73, in request
    response = getattr(self.session, method)(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\requests\sessions.py", line 600, in get
    return self.request("GET", url, **kwargs)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\auth\transport\requests.py", line 545, in request
    self.credentials.before_request(auth_request, method, url, request_headers)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\auth\credentials.py", line 133, in before_request
    self.refresh(request)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\oauth2\service_account.py", line 410, in refresh
    access_token, expiry, _ = _client.jwt_grant(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\oauth2\_client.py", line 217, in jwt_grant
    response_data = _token_endpoint_request(request, token_uri, body)
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\oauth2\_client.py", line 185, in _token_endpoint_request
    response_status_ok, response_data = _token_endpoint_request_no_throw(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\oauth2\_client.py", line 124, in _token_endpoint_request_no_throw
    response = request(
  File "C:\Users\KCL47\Desktop\googleSheetUpdate\venv\lib\site-packages\google\auth\transport\requests.py", line 199, in __call__
    six.raise_from(new_exc, caught_exc)
  File "<string>", line 3, in raise_from
google.auth.exceptions.TransportError: HTTPSConnectionPool(host='oauth2.googleapis.com', port=443): Max retries exceeded with url: /token (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:997)')))

这个问题出现在sheet = client.open_by_key("1DjCtgGxJapLNI44KMppmJZGCMjMVLgjmT__3xPdE0Sk").sheet1上

我已经尝试更新证书了。但它已经是最新版本了。

Python是由管理员用户安装的。

在没有计算机管理员的情况下，如何运行此脚本？

是否可能由于代理设置而无法连接到google？

python

google-sheets

gspread

回答 1

Stack Overflow用户

发布于 2022-08-30 02:00:09

你试过添加会话吗？

示例：

import requests
from gspread import Client

session = requests.Session()
session.verify = False

gc = Client(None, session)

gspread示例：

import json
from gspread import Client
from authlib.integrations.requests_client import AssertionSession

def create_assertion_session(conf_file, scopes, subject=None):
    with open(conf_file, 'r') as f:
        conf = json.load(f)

    token_url = conf['token_uri']
    issuer = conf['client_email']
    key = conf['private_key']
    key_id = conf.get('private_key_id')

    header = {'alg': 'RS256'}
    if key_id:
        header['kid'] = key_id

    # Google puts scope in payload
    claims = {'scope': ' '.join(scopes)}
    return AssertionSession(
        grant_type=AssertionSession.JWT_BEARER_GRANT_TYPE,
        token_url=token_url,
        issuer=issuer,
        audience=token_url,
        claims=claims,
        subject=subject,
        key=key,
        header=header,
    )

scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive',
]
session = create_assertion_session('your-google-conf.json', scopes)
gc = Client(None, session)

wks = gc.open("Where is the money Lebowski?").sheet1

wks.update_acell('B2', "it's down there somewhere, let me take another look.")

# Fetch a cell range
cell_list = wks.range('A1:B7')

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/73536363

复制

相似问题

问用Python连接
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用Python连接EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用Python连接
EN