upload csv file from GCS bucket to remote sftp location using python

Question

I am trying to send a csv file from google cloud gcs bucket to remote sftp location using python.

import pysftp
from google.cloud import storage
from google.cloud.storage import Blob

client = storage.Client()
bucket = client.bucket("bucket_path")
blob = bucket.blob("FILE.csv")
cnopts = pysftp.CnOpts()
cnopts.hostkeys = None
with  pysftp.Connection(host='remote_server', username='user', password='password',
                             port=22,
                             cnopts=cnopts) as sftp:
  print("Connection succesfully established ... ")
  remote_file=sftp.open('remote_location/sample.csv', 'w+')
  blob.download_to_file(remote_file)

I am getting the following errors :

Connection succesfully established ... 
Traceback (most recent call last):
  File "/dirvenv/lib/python3.8/site-packages/google/cloud/storage/blob.py", line 997, in download_to_file
    self._do_download(
  File "/dirvenv/lib/python3.8/site-packages/google/cloud/storage/blob.py", line 872, in _do_download
    response = download.consume(transport, timeout=timeout)
  File "/dirvenv/lib/python3.8/site-packages/google/resumable_media/requests/download.py", line 168, in consume
    self._process_response(result)
  File "/dirvenv/lib/python3.8/site-packages/google/resumable_media/_download.py", line 185, in _process_response
    _helpers.require_status_code(
  File "/dirvenv/lib/python3.8/site-packages/google/resumable_media/_helpers.py", line 106, in require_status_code
    raise common.InvalidResponse(
google.resumable_media.common.InvalidResponse: ('Request failed with status code', 404, 'Expected one of', <HTTPStatus.OK: 200>, <HTTPStatus.PARTIAL_CONTENT: 206>)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/dirPycharmProjects/leanplum/file_ftp.py", line 15, in <module>
    blob.download_to_file(remote_file)
  File "/dirvenv/lib/python3.8/site-packages/google/cloud/storage/blob.py", line 1008, in download_to_file
    _raise_from_invalid_response(exc)
  File "/dirvenv/lib/python3.8/site-packages/google/cloud/storage/blob.py", line 3262, in _raise_from_invalid_response
    raise exceptions.from_http_status(response.status_code, message, response=response)
google.api_core.exceptions.NotFound: 404 GET https://storage.googleapis.com/download/storage/v1/b/gs://bucket_name/o/FILE.csv?alt=media: ('Request failed with status code', 404, 'Expected one of', <HTTPStatus.OK: 200>, <HTTPStatus.PARTIAL_CONTENT: 206>)

Process finished with exit code 1

Any suggestion?

404 means that the object cannot be found. Make sure that you are specifying the bucket and object name correctly. Test by downloading the object after this line `blob = bucket.blob("FILE.csv")` by adding `blob.download_to_filename(destination_file_name)` — John Hanley, Aug 24 '20 at 23:57
Neo Anderson reformatted the errors. Now I can see that you are specifying the bucket name wrong. Edit your question with actual values. — John Hanley, Aug 25 '20 at 00:04
After removing the bucket name with slash I get issue as : Traceback (most recent call last): File "/PycharmProjects/leanplum/file_ftp.py", line 20, in blob.download_to_filename(remote_file) File "/venv/lib/python3.8/site-packages/google/cloud/storage/blob.py", line 1077, in download_to_filename with open(filename, "wb") as file_obj: TypeError: expected str, bytes or os.PathLike object, not SFTPFile. Any suggestion? — Prithwiraj Samanta, Aug 25 '20 at 01:10
@JohnHanley yes , I was wrong on bucket name, I can download the file now but I can't do sftp to remote as I am getting error as : File "/Users/prithwiraj_samanta/venv/lib/python3.8/site-packages/google/cloud/storage/blob.py", line 1077, in download_to_filename with open(filename, "wb") as file_obj: TypeError: expected str, bytes or os.PathLike object, not SFTPFile. Any suggestion ? — Prithwiraj Samanta, Aug 25 '20 at 01:20
Do you have the latest version of the Google API? [`Blob.download_to_file` claims to support file-like objects](https://googleapis.dev/python/storage/latest/blobs.html#google.cloud.storage.blob.Blob.download_to_file). — Martin Prikryl, Aug 25 '20 at 17:57

score 0 · Answer 1 · answered Aug 25 '20 at 08:13

The above error "TypeError: expected str, bytes or os.PathLike object, not SFTPFile" means that you are trying to download a SFTPFile-type object, and the method download_to_filename() is expecting str, bytes or os.PathLike objects.

I understand that your use case involves to upload a CSV format file to a remote SFTP location, and this CSV file is currently in a Cloud Storage.

Therefore, I would recommend that you first download the contents of this blob into a file-like object from your Cloud Storage bucket using the following example:

from google.cloud import storage


def download_blob(bucket_name, source_blob_name, destination_file_name):
    """Downloads a blob from the bucket."""
    # bucket_name = "your-bucket-name"
    # source_blob_name = "storage-object-name"
    # destination_file_name = "local/path/to/file"

    storage_client = storage.Client()

    bucket = storage_client.bucket(bucket_name)
    blob = bucket.blob(source_blob_name)
    blob.download_to_filename(destination_file_name)

    print(
        "Blob {} downloaded to {}.".format(
            source_blob_name, destination_file_name
        )
    )

Then, once you have downloaded the contents of this blob locally, you can upload it to a remote STFP location using the following sample code:

import pysftp

with pysftp.Connection('hostname', username='[YOUR_USERNAME]', password='[YOUR_PASSWORD]') as sftp:
  with sftp.cd('public'):             # temporarily chdir to public
     sftp.put('/my/local/filename')  # upload file to public/ on remote

Refer to this Stackoverflow question for more examples.

upload csv file from GCS bucket to remote sftp location using python

1 Answers1