Python 3.9 – reading google sheets file using pandas results in traceback error

Question

I'm trying to read data from a publicly available google spreadsheet using pandas.

I've been following this tutorial, yet after running my code:

#!/Library/Frameworks/Python.framework/Versions/3.9/bin/python3
import pandas as pd

#  Import data from Google Sheets


sheet_id = "1ierEhD6gcq51HAm433knjnVwey4ZE5DCnu1bW7PRG3E"

df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?forat=csv")

print(df)

I'm getting these traceback errors:

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 1342, in do_open h.request(req.get_method(), req.selector, req.data, headers, File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1255, in request self._send_request(method, url, body, headers, encode_chunked) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1301, in _send_request self.endheaders(body, encode_chunked=encode_chunked) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1250, in endheaders self._send_output(message_body, encode_chunked=encode_chunked) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1010, in _send_output self.send(msg) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 950, in send self.connect() File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1424, in connect self.sock = self._context.wrap_socket(self.sock, File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 500, in wrap_socket return self.sslsocket_class._create( File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1040, in _create self.do_handshake() File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1309, in do_handshake self._sslobj.do_handshake() ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1123)

During handling of the above exception, another exception occurred:

Traceback (most recent call last): df = pd.read_csv( File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 605, in read_csv return _read(filepath_or_buffer, kwds) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 457, in _read parser = TextFileReader(filepath_or_buffer, **kwds) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 814, in init self._engine = self._make_engine(self.engine) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 1045, in _make_engine return mapping[engine](self.f, **self.options) # type: ignore[call-arg] File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 1862, in init self._open_handles(src, kwds) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 1357, in _open_handles self.handles = get_handle( File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/common.py", line 558, in get_handle ioargs = _get_filepath_or_buffer( File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/common.py", line 288, in _get_filepath_or_buffer req = urlopen(filepath_or_buffer) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/common.py", line 194, in urlopen return urllib.request.urlopen(*args, **kwargs) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 214, in urlopen return opener.open(url, data, timeout) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 517, in open response = self._open(req, data) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 534, in _open result = self._call_chain(self.handle_open, protocol, protocol + File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 494, in _call_chain result = func(*args) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 1385, in https_open return self.do_open(http.client.HTTPSConnection, req, File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py", line 1345, in do_open raise URLError(err) urllib.error.URLError: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1123)>

Could someone please help me?

EDIT: @Code-Apprentice's answear partially fixed my issue, now I'm only getting these errors:

Traceback (most recent call last):
    df = pd.read_csv(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 605, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 457, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 814, in __init__
    self._engine = self._make_engine(self.engine)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 1045, in _make_engine
    return mapping[engine](self.f, **self.options)  # type: ignore[call-arg]
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/parsers.py", line 1893, in __init__
    self._reader = parsers.TextReader(self.handles.handle, **kwds)
  File "pandas/_libs/parsers.pyx", line 518, in pandas._libs.parsers.TextReader.__cinit__
  File "pandas/_libs/parsers.pyx", line 620, in pandas._libs.parsers.TextReader._get_header
  File "pandas/_libs/parsers.pyx", line 814, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas/_libs/parsers.pyx", line 1943, in pandas._libs.parsers.raise_parser_error
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xc3 in position 54: invalid continuation byte

Does this answer your question? certificate verify failed: unable to get local issuer certificate — Code-Apprentice
@Code-Apprentice partially, most of the errors are gone, but not all of them. I'll add an edit to my post. — wbrydak
For future reference, you should accept the linked question when it solves your current error then post a new question about the new error instead of editing your question to completely replace the original error message. This helps to build a library that other people can find in the future to help solve similar problems. — Code-Apprentice

Shay Nehmad Shay Nehmad · Accepted Answer · 2021-01-26T21:07:10

You have a typo in the parameter, forat instead of format. While I'm not sure that's the issue, that's probably not good.

Python 3.9 – reading google sheets file using pandas results in traceback error

1 Answers