我在尝试让下面的脚本生效,从而读取位于远程服务器(server 1)中一个特定目录下的CSV文件列表,并将数据传输到另一个服务器中的PostgreSQL数据库。
我已创建一个RSA SSH密钥并将其复制到远程服务器,现在我只能使用用户名连接到服务器。
我已尝试将RSA Pub密钥/私钥复制到文件夹中(当我运行PY脚本时,.ssh文件夹无法接受),并提供了其位置,甚至尝试粘贴整个私钥,但不起作用,会引发以下异常。
我在这一行"sftp = t.open_sftp_client()"遇到错误,但错误信息没有任何有用的提示。
请问有谁能帮忙解决?建议将非常有帮助。
异常:
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)
代码:
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()
t.connect
这一行。看起来它正在使用SSH代理,认证有效。 - Thomas