2

I have a requirement to read all the CSV files from a remote server and insert the data to a remote PostgreSQL database. I wrote the code to read all the CSV files and insert it to the database, but it is working and tested only in local and it is working fine.

When i tried with paramiko to read it from the remote server, I was not able to do it, have tried few things as mentioned in google and could not able to read the file from the source and port it to destination.

Any guidance will be of great help to resolve this. 

import csv
import psycopg2
import os
import paramiko

try:
   ssh = paramiko.SSHClient()
   ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
   target_host = 'site_name.com'
   usr_name = 'root'
   ssh.connect( hostname = target_host , username = usr_name )

   print("SSH connection succesful")
except (Exception, psycopg2.Error) as error:
            print (error)
            quit()

csv_filepath ="/users/lins/home/csv"

try:
     conn = psycopg2.connect(host="localhost", database="postgres", user="postgres",password="*******", port="5432")
     print('Database connected succesfully',"\n")
     cur = conn.cursor()
except (Exception, psycopg2.Error) as error:
            print (error)
            quit()

EXT = "*.csv"
all_csv_files = [file
                 for path, subdir, files in os.walk(csv_filepath)
                 for file in glob(os.path.join(path, EXT))]
for fp in all_csv_files: 
    filePath=fp 

    if os.path.isfile(filePath):
     try:
         sql = "COPY %s FROM STDIN WITH DELIMITER AS ';'  csv header"
         file = open(filePath, "r" , encoding="latin-1")

         table = 'dl.dhl_tracking'

         cur.copy_expert(sql=sql % table, file=file)
         print('Data porting done',"\n")
     except (Exception, psycopg2.Error) as error:
            print (error)
            quit()

    else:
            quit()

conn.commit()   
cur.close()
conn.close()
Linu
  • 589
  • 1
  • 10
  • 23
  • 1
    You should look into the [SFTP-Client](http://docs.paramiko.org/en/2.6/api/sftp.html) built into Paramiko. Be aware though, some have reported [slow read speeds](https://stackoverflow.com/questions/7563496/open-a-remote-file-using-paramiko-in-python-slow) using it. – Hampus Larsson Aug 27 '19 at 15:48
  • This is quite broad question. But the first step is: [How to list all the folders and files in the directory after connecting through sftp in python](https://stackoverflow.com/q/12295551/850848). – Martin Prikryl Aug 28 '19 at 14:09

0 Answers0