SFTP - Getting started in Peliqan

dw = pq.dbconnect(pq.DW_NAME)     # your data warehouse
sftp = pq.sftpconnect("SFTP")

files = sftp.dir("/")
for file in files["detail"]:
    if file["type"] == "file": # type can be "file" or "folder"
        jsonStr = sftp.read_file(file["name"])  # assuming one object in file
        record = json.loads(jsonStr)
        dw.insert("my_db", "my_schema", "my_table", record)

import pysftp
import io
import csv

cnopts = pysftp.CnOpts()
cnopts.hostkeys = None # do not verify hostname

# Add a connection first of type "Peliqan Secret store"
password = pq.get_secret("Peliqan Secret Store - SFTP password")
sftpconn = pysftp.Connection(host = "sftp.eu.peliqan.io", username = "testuser", password = password, port = 2022, cnopts = cnopts)

dbconn = pq.dbconnect(pq.DW_NAME)
query = "SELECT * FROM odoo.accounts LIMIT 5"
df = dbconn.fetch(pq.DW_NAME, query = query, df = True)
st.write(df)

csv_buffer = io.StringIO()
df.to_csv(csv_buffer, index=False, quotechar='"', quoting=csv.QUOTE_ALL)
csv_buffer.seek(0)

with sftpconn:
    with sftpconn.open('data.csv', 'w') as remote_file:
        remote_file.write(csv_buffer.getvalue())