Paramiko is a python library that allows you to make SSH connections. Useful when you want to automate things as you can easily ssh into remote server from your local computer.

The code down below will let you ssh to a remote server via Paramiko and then SCP files from that server to some other remote server ie. copying/transferring files from one remote server to another remote server.

Here’s what it does:

  • SSH to remote-server-1
  • Execute a psql command to export entries as a CSV file on the server
  • SCP that generated file to remote-server-2
  • Remove the generated file from remote-server-1 once its successfully copied.
import paramiko
from scp import SCPClient
from paramiko_expect import SSHClientInteraction
remote_server_1 = "remote-server-1.com"
remote_server_1_username = "ssh_username"
remote_server_1_password = "ssh_password"
remote_server_2 = "remote-server-2.com"
remote_server_2_username = "ssh_username2"
remote_server_2_password = "ssh_password2"
p = paramiko.SSHClient()
p.set_missing_host_key_policy(paramiko.AutoAddPolicy())
filename = 'filename_whatever'
export = "psql -U username database -c 'COPY (SELECT * FROM some_table WHERE id=1) TO " + filename + ".csv" +" With CSV HEADER;'"
p.connect(remote_server_1, port=22, username=remote_server_1_username, password=remote_server_1_password)
stdin, stdout, stderr = p.exec_command(export) # executes psql command once connected
stdin.write("psql_passwordn") # password for psql user
print("Entering")
stdin.flush()
status = stdout.channel.recv_exit_status() # wait till file has been exported
if (status==0): # if successful in generating file scp it to remote server
   print("Copying..")
   stdin_, stdout_, stderr_ = p.exec_command("expect -c 'spawn scp ./" + filename + ".csv " + remote_server_2_username + "@" + remote_server_2 + ":~;expect password;send \"" + remote_server_2_password + "\r\";interact'", get_pty=True)
   status_ = stdout_.channel.recv_exit_status()
   stdin_, stdout_, stderr_ = p.exec_command('rm ' + str(filename) + '.csv') #remove file after copying
   status_ = stdout_.channel.recv_exit_status()
   print("Done: ", str(filename))
else:
   print("Error")
   print(stderr.readlines())
p.close()

Here we are copying files from remote_server_1 to remote_server_2. We ssh into remote_server_1 and then with the help of paramiko’s exec_command method, execute the terminal command to log into psql and generate a file (ie. whatever’s in ‘export’ variable). Once we execute the psql command, it will ask for password of the user. We provide the password by writing it to the stdin: stdin.write(“psql_passwordn”).

We then wait until the command has finished executing: stdout.channel.recv_exit_status(). If the execution was successful (ie status == 0), we execute another command to copy the file to remote_server_2. This command may look complex, but all we are doing here is executing scp with the help of expect. Then we finally delete the file from remote_server_1: p.exec_command(‘rm ‘ + str(ids) + ‘.csv’) .

Hope this was helpful!


Related Posts: