Im trying to make the below script work, so as to read a list of CSV files in a specific directory and from a remote server(server 1) and Port the data into a PostgreSQL database of another server.
I have created a rsa SSH Key and copied it to the remote server,Now i'm able to connect the server using user name only.
I have tried copying the rsa Pub key/private key to a folder(.ssh folder is not taking while i run the PY script) and gave it's location, even tried pasting the whole private key but it is not working and below exception is raised.
I'm getting error in this line "sftp = t.open_sftp_client()", But no useful information is showing in the error.
Can anyone please help me with, Suggestions will be very helpful.
Exception:
Database connected
SFTP Connected
Oops, unhandled type 3 ('unimplemented')
Traceback (most recent call last):
File "/Users/sandeep/Documents/paramiko test.py", line 45, in <module>
sftp = paramiko.SFTPClient.from_transport(t)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/paramiko/sftp_client.py", line 165, in from_transport
window_size=window_size, max_packet_size=max_packet_size
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/paramiko/transport.py", line 879, in open_session
timeout=timeout,
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/paramiko/transport.py", line 1006, in open_channel
raise e
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/paramiko/transport.py", line 2055, in run
ptype, m = self.packetizer.read_message()
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/paramiko/packet.py", line 459, in read_message
header = self.read_all(self.__block_size_in, check_rekey=True)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/paramiko/packet.py", line 303, in read_all
raise EOFError()
EOFError
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/Users/sandeep/Documents/paramiko test.py", line 50, in <module>
self._logger.error(msg)
Code:
import paramiko
import psycopg2
import psycopg2.extras
import os
import inspect
import re
import sys
SECTION_SRC_STAR = "DEFAULT"
SSH_HOST = '*****.com'
SSH_USERNAME = 'root'
#SSH_KEYFILE = os.path.realpath(os.path.abspath(os.path.join(os.path.split(inspect.getfile(inspect.currentframe()))[0], "ssh.key")))
#SSH_KEYFILE = os.path.realpath(os.path.abspath(os.path.join(os.path.split(inspect.getfile(inspect.currentframe()))[0], "id_rsa")))
SSH_KEYFILE ="/Users/sand/Documents/ssh_pub"
SSH_DIR = '/opt/prod_odoo_out/carrierfiles/dhl/'
SSH_PORT = 22
SSH_MASK = 'file_pattern.*\.csv'
DSN = "dbname='postgres' user='postgres' host='localhost' password='postgres' port='5432'"
TABLE_NAME = 'staging.dhl_tracking_details'
conn = psycopg2.connect(DSN)
print("Database connected")
conn.set_client_encoding('latin-1')
cur = conn.cursor()
ssh = paramiko.SSHClient()
t = paramiko.Transport((SSH_HOST, SSH_PORT))
#key = paramiko.RSAKey.from_private_key_file(SSH_KEYFILE)
#key='***************...........'
#t.connect(username=SSH_USERNAME, pkey=key)
t.connect(username=SSH_USERNAME)
print("SFTP Connected")
try:
#sftp = paramiko.SFTPClient.from_transport(t)
sftp = t.open_sftp_client()
print("SFTP Client : Open")
except Exception as e:
msg = "Error connecting via ssh: %s" % e
self._logger.error(msg)
raise paramiko.SSHException(msg)
for filename in sftp.listdir(SSH_DIR):
if re.match(SSH_MASK, filename):
path = '/%s/%s' % (SSH_DIR, filename)
fobj = sftp.file(path, 'rb')
#cur.execute('TRUNCATE TABLE %s' % TABLE_NAME)
#fobj2 = RowsIO(fobj)
#cur.copy_expert("COPY %s FROM STDOUT WITH DELIMITER ';' NULL 'null' CSV HEADER" % (TABLE_NAME, ), fobj2)
sql = "COPY %s FROM STDIN WITH DELIMITER AS ';' csv header"
table = 'staging.dhl_tracking_details'
cur.copy_expert(sql=sql % table, file=fobj)
conn.commit()
sftp.remove(path)
t.close()
