6

I want to transfer a Python dataframe directly as a .csv file to a remote server using Paramiko module. Currently, I save the dataframe as a .csv then I push that .csv file to the server. I stumbled by this similar question How to write pandas dataframe to csv/xls on FTP directly, but is it possible using Paramiko module? Thanks in advance!

This is the simple script I use to transport a .csv file from my directory to the remote server:

import pandas as pd
import paramiko

# Save DataFrame as CSV
file_name = 'file.csv'
df.to_csv(file_name,index=False)

# Connect to Server Via FTP
ssh_client = paramiko.SSHClient()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh_client.connect(hostname='host',username='user_name',password='password')
ftp_client= ssh_client.open_sftp()

# Upload 'file.csv' to Remote Server
ftp_client.put('path_to_file.csv','path_to_remote_file')
Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
Albert Sosa
  • 61
  • 1
  • 1
  • 2

1 Answers1

14

Just use SFTPClient.open

with sftp.open('path_to_remote_file', "w") as f:
    f.write(df.to_csv(index=False))
Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
fn.
  • 2,788
  • 2
  • 27
  • 29
  • worked like a charm. thanks man! I just changed it for my script to "with ftp_client.open" for future references – Albert Sosa Apr 09 '19 at 20:46
  • You should enable buffering, otherwise the code can be very slow. See [Writing to a file on SFTP server opened using pysftp “open” method is slow](https://stackoverflow.com/q/58111798/850848). – Martin Prikryl Oct 09 '19 at 05:00
  • 2
    It's probably more efficient to use `df.to_csv(f, index=False)`, as with that whole contests does not have be to serialized to memory first. Instead, it can be written line by line. See [Read CSV/Excel files from SFTP file, make some changes in those files using Pandas, and save back](https://stackoverflow.com/q/61817226/850848). That also negates my previous comment, as actually with your approach the slow problem with writing line by line does not happen, as you write whole file at once. – Martin Prikryl May 16 '20 at 04:52