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 /XX/XX/XX/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 /XX/XX/XX/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.

iCodeOneHanded · Accepted Answer · 2017-01-15 17:45:03Z

I had a similar issue as you and eventually wrote a library to do this. Here's a snippet of 'psuedo psuedo code' that should point you in the right direction. Keep in mind these are methods of a class and you'll need to adapt this pseudo code to your needs. Keep in mind you'll already need a SSHConnection from paramiko here.

   def sqlplus_cmd(self, command):
        # Create string which exports environmental variables from OracleEnv class ()
        if 'CYGWIN' not in <return from 'uname' on the host>:
            # If NOT Cygwin, concatenate environmental variable exports
            oracle_exports = 'export PATH={0}:$PATH;' \
                             'export ORACLE_HOME={1};' \
                             'export ORACLE_SID={2}'.format(<oracle_path>, <oracle_home>, <oracle_sid>)
        else:
            # If Cygwin, need to source environmental variables for shell session from script
            # TODO: May need to get Oracle Home and Path as well for some systems.
            self.cmd('echo "export ORACLE_SID={0}" > /tmp/sid'.format(<oracle_sid>))
            oracle_exports = 'source /tmp/sid'

        # Issue concatinated one line command which exports variables, opens sqlplus, and issues a sqlplus statement
        # final_command = oracle_exports + ';' + 'echo "' + command + '" | sqlplus -S / as sysdba'
        final_command = '{0};echo "{1}" | sqlplus -S / as sysdba'.format(oracle_exports, command)
        stdout, stderr, rc = <paramiko_SSHConnection.exec_command>(final_command)

That should do it. Have fun parsing the output and catching the ORA-xxx and SP2-xxx errors in stdout.

Tharindu Sathischandra · Accepted Answer · 2021-12-03 08:31:36Z

0

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))

edited Dec 3, 2021 at 8:31

Tharindu Sathischandra

2,0521 gold badge19 silver badges38 bronze badges

answered Mar 18, 2015 at 15:36

Tui Popenoe

2,1242 gold badges23 silver badges45 bronze badges

Collectives™ on Stack Overflow

Use python to connect to sqlplus in a remote host and execute sql commands

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related