4

For the part of my code you can find below, sometimes I get the error in the title. Is it related with heavy data or poor internet connection maybe? I try to get 14k line from one google spreadsheet to another by using gspread.

Is there any way to prevent this? Would appreciate any help.

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']
    }
)

Full error log: 

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'))
zeppelin11
  • 103
  • 2
  • 3
  • 9
  • Hey @zeppelin11 I provided an answer that should fix your issue. Let me know if it does or if you have any doubt. Cheers – carlesgg97 Nov 19 '19 at 15:23

1 Answers1

2

The issue you are getting, BrokenPipeError can be found in the official Python documentation (see here). It is usually thrown when a connection is cut, either because of the other-end of it intentionally did so, or because the connection is unstable. If you want to avoid getting this error, you can handle the Exception and issue a retry when it happens. Your code modifications would look like the following:

from requests.exceptions import ConnectionError

def retry_on_connectionerror(f, max_retries=5):
  retries = 0
  while retries < max_retries:
    try:
      return f()
    except ConnectionError:
      retries += 1
  raise Exception("Maximum retries exceeded")


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')

def update_values():
  worksheet1.values_update(
      "tc id!A:B"
      ,
      params={
          'valueInputOption': 'USER_ENTERED'
      } ,
      body={
          'values': new_values_tc['values']
      }
  )

retry_on_connectionerror(update_values)

The modification points are the following:

  • Imported ConnectionError so that it can be caught when executing your function.
  • Created retry_on_connectionerror() function. It takes a function as an argument, and optionally a number of maximum retries it should issue (defaulted to 5). It executes the function as many times as max_retries, until it works or this number is exceeded.
  • Wrapped the values_update function in another function or closure. This allows us to pass this code to the newly created retry_on_connectionerror() function.
  • Call to retry_on_connectionerror(update_values) to apply aforementioned behaviour.
carlesgg97
  • 4,184
  • 1
  • 8
  • 24