Category Archives: Python

Unknown's avatar zhi yan 11:37 pm on May 23, 2021  

How to setup Oracle Autonomous DB connection for python in Ubuntu

Following blog shows how can we setup the connections between Python and Oracle Autonomous DB

Python and Pandas to get datasets from Oracle Autonomous DB

Once your Oracle client is setup correctly, you can use following query to test out the connection

Please replace the query to the ones that suits you

import cx_Oracle

con = cx_Oracle.connect("USERNAME","PASSWORD","CONNECTION_SERVICE_FROM_WALLET")

print("Database version: ", con.version)

query= """
select * from EMP
"""
cur = con.cursor()
cur.execute(query)
res=cur.fetchall()
for row in res:
  print(row)
Unknown's avatar zhi yan 10:08 am on April 6, 2020  

How to use Python to connect to sftp and download file

We can use pysftp to connect to python and get the files from an sftp server.

Before that, we need to get the sftp server’s key using following command.

$ ssh-keyscan example.com
# example.com SSH-2.0-OpenSSH_5.3
example.com ssh-rsa AAAAB3NzaC1yc2EAAAADAQAB...

When fetched the key, put it to file  C:/Users/username/.ssh/known_hosts
– You can also save the key in a folder and pass in using the cnopts variable shown below in the code

import pysftp
import io
import csv

myHostname = "HOST"
myUsername = "USERNAME"
myPassword = "PASSWORD"

cnopts = pysftp.CnOpts() # C:/Users/USERNAME/.ssh/known_hosts
cnopts.hostkeys.load('keys/testSftp.key')

with pysftp.Connection(host=myHostname, username= myUsername, password=myPassword, cnopts = cnopts) as sftp:
    print("Connection successfully stablished...")
    flo = io.BytesIO()
    sftp.getfo('/PATH_TO_DATA_FILE',flo)
    fileStr=flo.getvalue()
    textStr = fileStr.decode('UTF-8')
    print(textStr)

If everything works fine, you should see your file print in the command line