Copy all CSV files from a specific path and it's subdirectories from a remote server using Paramiko library

Question

I want to read a list of CSV files from a specific path in a remote server.

I use the below script for the same and it is working fine in my local when i specify a particular path, But in that path there are many subfolders and each sub folders contains many CSV files , So i want to take the CSV files from the directory (/opt/prod_odoo_out/carrierfiles/dhl/) and all it's subdirectories recursively and do the porting.

How can i do it via paramiko?

I have tried os.walk but it is not working as it works only on local machine. Any suggestions will be of greta help.

Note : currently i have set the folder path as "/opt/prod_odoo_out/carrierfiles/dhl/4/export/" and inside the main path(dhl) folder there are upto 20 folders and each contains many subfolders.

Code:

import paramiko
import psycopg2
from tendo import singleton
import os
import inspect
import re
import sys
import logging    


SECTION_SRC_STAR = "DEFAULT"

SSH_HOST = 'name.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 ="/Users/sandeep/Documents/ssh_pub"

SSH_DIR = '/opt/prod_odoo_out/carrierfiles/dhl/4/export/'
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()
cur.execute("SET datestyle='German'")

ssh = paramiko.SSHClient()

t = paramiko.Transport((SSH_HOST, SSH_PORT))

pk = paramiko.RSAKey.from_private_key(open('/Users/sandeep/Documents/ssh_pub/id_rsa'))
t.connect(username=SSH_USERNAME,pkey=pk)
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
    raise paramiko.SSHException(msg)

XT = "*.csv"
for filename in sftp.listdir(SSH_DIR):
    print(filename)

    #if re.match(SSH_MASK, filename):
    print("entered loop")
    path = '/%s/%s' % (SSH_DIR, filename)
    fobj = sftp.file(SSH_DIR+filename, 'rb')
    #cur.execute('TRUNCATE TABLE %s' % TABLE_NAME)
    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()

t.close()