#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os, sys
from subprocess import Popen, PIPE

def query(sql, pdb_name=None, oracle_sid=None, hostname=None, sqlplus='sysdba', ora_user='oracle'):
    shell_cmd = 'su - {0} <<EOF'.format(ora_user)+os.linesep
    if oracle_sid:
        shell_cmd += 'export ORACLE_SID={0}'.format(oracle_sid)+os.linesep
    shell_cmd += 'sqlplus -S / as {0}'.format(sqlplus)+os.linesep
    shell_cmd += 'SET FEEDBACK OFF'+os.linesep
    shell_cmd += 'SET HEADING OFF'+os.linesep
    shell_cmd += 'SET LINE 10000'+os.linesep
    shell_cmd += 'SET PAGESIZE 10000'+os.linesep
    if pdb_name and pdb_name.strip() != '':
        shell_cmd += 'ALTER SESSION SET CONTAINER={0};'.format(pdb_name.strip())+os.linesep
    shell_cmd += sql+os.linesep
    shell_cmd += 'quit;'+os.linesep
    shell_cmd += 'EOF'+os.linesep
    if hostname:
        p = Popen('/usr/bin/ssh -Tq '+hostname, shell=True, stdout=PIPE, stdin=PIPE)
        p.stdin.write(str.encode(shell_cmd))
        p.stdin.flush()
    else:
        p = Popen(shell_cmd, shell=True, stdout=PIPE, stderr=PIPE)
    stdout, stderr = p.communicate()
    try:
        if stdout and stdout.strip() != '':
            stdout = stdout.decode()
            if 'Last login:' in stdout:
                lines = stdout.split(os.linesep)
                for i in range(0, len(lines)):
                    if 'Last login:' in lines[i]:
                        del lines[i] # delete only once
                        break
                stdout = os.linesep.join(lines)
        else:
            stdout = None
    except Exception as e:
        stdout = None
    return stdout
#####
sql = "select * from v\$tablespace;"
# run remote sql
res = query(sql, hostname='pgylinux03', oracle_sid='ora11gbk')
print(res)

# run local sql
res = query(sql, oracle_sid='cattydb', pdb_name='CATTY_PDB01')
print(res)