Use python to connect to sqlplus in a remote host and execute sql commands
Question: Here is my situation : We have sqlplus set up in a remote machine and I want
to connect to that remote machine and then run sqlplus to execute sql queries.
I am trying to write a python script to do that.

Here is my code:

    
    
    import sys
    
    import getpass
    
    import paramiko
    
    import time
    
    user=raw_input('Enter User Name :')
    
     #host_name=raw_input('Enter Host Name:')
    
    psswd=getpass.getpass()
    
    ssh = paramiko.SSHClient()
    
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    
    ssh.connect('xxx.hostname.xxx',port=22, username=user, password=psswd)
    
    command='export ORACLE_HOME=/opt/app/oracle/product/10.2.0.2/client export LD_LIBRARY_PATH=$ORACLE_HOME/lib \
     sudo -S -H /apollo/env/envImprovement/bin/sqlplus'
    
    
    
    print 'running remote command'
    
    print(command) 
    
    stdin, stdout, stderr=ssh.exec_command(command)
    
    stdin.write(psswd+'\n')
    
    stdin.flush()
    
    for out in stdout.readlines():
    
        print out
    
    ssh.close() 
    

I have two issues here First is if i pass command like this

    
    
    'export ORACLE_HOME=/opt/app/oracle/product/10.2.0.2/client export LD_LIBRARY_PATH=$ORACLE_HOME/lib \
    sudo -S -H /apollo/env/envImprovement/bin/sqlplus' +' echo $ORACLE_HOME'
    

I get an empty response even if I have added echo that means that variable is
not set correctly.

Secondly, I can't figure out what next to do here. How to provide
`username/password` to sqlplus to allow executing sql queries and then how to
supply sql statements.


Answer: Why don't you split your command into a function, and use subprocess.Popen()
to execute it in a subprocess?

    
    
    from subprocess import *
    
    def run_sql_query(sql_command, connection_string):
        session = Popen([‘sqlplus’, ‘-S’, connection_string], stdin=PIPE, stdout=PIPE, stderr=PIPE)
        session.stdin.write(sql_command)
        return session.communicate()
    

Then you can pass your connection string and command as arguments to your
function:

    
    
    con_str = 'xxx.hostname.xxx',port=22, username=user, password=psswd'
    cmd = ''export ORACLE_HOME=/opt/app/oracle/product/10.2.0.2/client export LD_LIBRARY_PATH=$ORACLE_HOME/lib sudo -S -H /apollo/env/envImprovement/bin/sqlplus'
    
    print(run_sql_query(con_str, cmd))
    

