Python中BrokenPipeError求助（gspread迁移谷歌表格数据）

问题背景 #

我在使用gspread将某谷歌表格中的14k行数据迁移至另一表格时，偶尔会触发BrokenPipeError: [Errno 32] Broken pipe错误。想请教这个错误是否和数据量大或网络连接不佳有关？有没有预防方法？

相关代码 #

worksheet1.values_clear("tc id!A:B")
source_tc= client.open('tc_sheet')
source_tc.sheet1.delete_row(1)
new_values_tc = source_tc.values_get('Sheet1!A:B')
worksheet1.values_update(
    "tc id!A:B" ,
    params={ 'valueInputOption': 'USER_ENTERED' } ,
    body={ 'values': new_values_tc['values'] }
)

完整错误日志 #

Traceback (most recent call last): File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py", line 672, in urlopen chunked=chunked, File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py", line 387, in _make_request conn.request(method, url, **httplib_request_kw) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1252, in request self._send_request(method, url, body, headers, encode_chunked) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1298, in _send_request self.endheaders(body, encode_chunked=encode_chunked) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1247, in endheaders self._send_output(message_body, encode_chunked=encode_chunked) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1065, in _send_output self.send(chunk) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 987, in send self.sock.sendall(data) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/ssl.py", line 1034, in sendall v = self.send(byte_view[count:]) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/ssl.py", line 1003, in send return self._sslobj.write(data) BrokenPipeError: [Errno 32] Broken pipe During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/adapters.py", line 449, in send timeout=timeout File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py", line 720, in urlopen method, url, error=e, _pool=self, _stacktrace=sys.exc_info()[2] File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/util/retry.py", line 400, in increment raise six.reraise(type(error), error, _stacktrace) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/packages/six.py", line 734, in reraise raise value.with_traceback(tb) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py", line 672, in urlopen chunked=chunked, File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py", line 387, in _make_request conn.request(method, url, **httplib_request_kw) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1252, in request self._send_request(method, url, body, headers, encode_chunked) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1298, in _send_request self.endheaders(body, encode_chunked=encode_chunked) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1247, in endheaders self._send_output(message_body, encode_chunked=encode_chunked) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 1065, in _send_output self.send(chunk) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/http/client.py", line 987, in send self.sock.sendall(data) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/ssl.py", line 1034, in sendall v = self.send(byte_view[count:]) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/ssl.py", line 1003, in send return self._sslobj.write(data) urllib3.exceptions.ProtocolError: ('Connection aborted.', BrokenPipeError(32, 'Broken pipe')) During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/Users/ali.ugurlu/Documents/gsheets/main.py", line 161, in <module> 'values': new_values_tc['values'] File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/gspread/models.py", line 176, in values_update r = self.client.request('put', url, params=params, json=body) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/gspread/client.py", line 73, in request headers=headers File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/sessions.py", line 593, in put return self.request('PUT', url, data=data, **kwargs) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/sessions.py", line 533, in request resp = self.send(prep, **send_kwargs) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/sessions.py", line 646, in send r = adapter.send(request, **kwargs) File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/adapters.py", line 498, in send raise ConnectionError(err, request=request) requests.exceptions.ConnectionError: ('Connection aborted.', BrokenPipeError(32, 'Broken pipe'))

回答 #

错误原因分析 #

没错，这个BrokenPipeError确实和数据量大以及网络连接稳定性直接相关：

• 一次性传输14k行数据时，请求体体积会比较大，谷歌API服务器可能因负载过高或超时提前关闭连接；
• 不稳定的网络环境下，数据传输过程中连接中断也会触发该错误——本质是客户端还在往已经被服务器关闭的连接写数据。

预防和解决方法 #

这里有几个实用的方案，按优先级推荐：

1. 分批传输数据

把14k行数据拆分成小批量（比如每次500-1000行），分多次调用values_update。这样每个请求的体积变小，既不容易触发服务器的连接限制，也降低了网络波动的影响。示例代码：

worksheet1.values_clear("tc id!A:B")
source_tc = client.open('tc_sheet')
source_tc.sheet1.delete_row(1)
new_values_tc = source_tc.values_get('Sheet1!A:B')['values']

# 分批处理，每批1000行
batch_size = 1000
for i in range(0, len(new_values_tc), batch_size):
    batch = new_values_tc[i:i+batch_size]
    # 计算目标范围，比如A1:B1000，A1001:B2000等
    start_row = i + 1
    end_row = i + len(batch)
    range_str = f"tc id!A{start_row}:B{end_row}"
    worksheet1.values_update(
        range_str,
        params={'valueInputOption': 'USER_ENTERED'},
        body={'values': batch}
    )

2. 添加重试机制

针对网络波动导致的偶发错误，添加带指数退避的重试逻辑。gspread底层用的是requests库，可以结合tenacity实现：

from tenacity import retry, stop_after_attempt, wait_exponential, retry_if_exception_type
import requests

# 定义重试装饰器，针对连接错误重试3次，每次等待时间指数增长
@retry(
    stop=stop_after_attempt(3),
    wait=wait_exponential(multiplier=1, min=2, max=10),
    retry=retry_if_exception_type((requests.exceptions.ConnectionError, BrokenPipeError))
)
def update_values_with_retry(worksheet, range_str, values):
    worksheet.values_update(
        range_str,
        params={'valueInputOption': 'USER_ENTERED'},
        body={'values': values}
    )

# 使用这个重试函数替代直接调用values_update
update_values_with_retry(worksheet1, "tc id!A:B", new_values_tc['values'])

3. 调整请求超时时间

默认的超时时间可能不够，你可以在创建gspread客户端时设置更长的超时时间，给大体积请求足够的传输时间：

from gspread import Client
import requests

# 自定义会话，设置超时
session = requests.Session()
session.timeout = 30  # 30秒超时，可根据实际情况调整
client = Client(auth, session=session)

4. 优化网络环境

如果是本地运行脚本，尽量连接稳定的网络；如果是服务器上运行，确保服务器到谷歌API的网络链路通畅，必要时可考虑使用合规的代理服务。

内容的提问来源于stack exchange，提问作者zeppelin11