By

文章目录

写了一个小工具，oracle 查询结果列展示，就像mysql的select 之后 添加 \G

# author        : ruichao.lin(baiqiao) 
# lang          : python 
# version       : python 2.7
# date          : 2013-10-10
# for           : GNU - linux 
# encoding      : UTF-8
#!/home/oracle/dbapython/bin/python
# -*- coding: utf-8 -*- 
import re
import sys,os
import math
import time
import commands,string
from signal import SIGTERM
import cx_Oracle
from optparse import OptionParser
import ConfigParser
from termcolor import colored   
def getOraConnTns(tns):
    #conn = cx_Oracle.connect('devdba','devdba',tns,threaded = True)
    conn = cx_Oracle.connect('/', mode=cx_Oracle.SYSDBA)
    return conn   
def getowner(cur3,mtable):
    sql = '''
    select owner  from dba_tables where table_name = upper('%s') 
    '''
    try:
        cur3.execute(sql%mtable)
        rs3 = cur3.fetchall()
        owner = rs3[0][0]
        return owner 
    except Exception, e:
        raise e
    finally:
        cur3.close()    
def getcolumns(cur1 , mtable ):
    colist=[]
    sql1 = '''
    select COLUMN_NAME  from dba_tab_columns  where TABLE_NAME = upper('%s')  order by COLUMN_ID  '''
    try: 
        cur1.execute(sql1%(mtable))
        rs1 = cur1.fetchall()
        for col in rs1: 
            colist.append(col[0])
        return colist
    except Exception, e:
        raise e
    finally:
        cur1.close()    
def doexecute(cur,sql,colist) :
    try:
        cur.execute(sql)
        rs=cur.fetchall()
        lens = len(colist)
        for x in rs : 
            print '---------------------------- column ------------------------'
            for i in xrange(lens): 
                print   ' ::--> ' ,colist[i].ljust(32),'  :  ', x[i] 
    except Exception, e:
        raise e
    finally:
        cur.close()   
def help():
    help_str = ''' 
Usage:
    process the query result : row --- to -----> column 
For example:
    colquery.py -t table_name -p pridicatexxx -c cloumns
    colquery.py -t table_name -p pridicatexxx
Options include:
  --t       : the table_name
  --p       : the where clouse like "a = 'xxx' and b = 'xxx' ... "
  --c       : the cloumns  "aa,bb,cc" '''   
    return help_str   
def main():
    leng = len(sys.argv)
    mtable = ''
    icol = ''
    pridicate=''
    if leng == 5:
         if sys.argv[1].strip() == '-t' : 
              mtable  = sys.argv[2].strip()
         elif sys.argv[1].strip() == '-p' : 
              pridicate  = sys.argv[2].strip()
         else : 
            print '[ERROR] Invalid option. Exit.'
            print help()
            sys.exit()      
         if sys.argv[3].strip() == '-t' : 
              mtable  = sys.argv[4].strip()
         elif sys.argv[3].strip() == '-p' : 
              pridicate  = sys.argv[4].strip()
         else : 
            print '[ERROR] Invalid option. Exit.'
            print help()
            sys.exit()    
    elif leng == 7:
         if sys.argv[1].strip() == '-t' : 
              mtable  = sys.argv[2].strip()
         elif sys.argv[1].strip() == '-p' : 
              pridicate  = sys.argv[2].strip()
         else : 
            print '[ERROR] Invalid option. Exit.'
            print help()
            sys.exit()  
         if sys.argv[3].strip() == '-t' : 
              mtable  = sys.argv[4].strip()
         elif sys.argv[3].strip() == '-p' : 
              pridicate  = sys.argv[4].strip()
         else : 
            print '[ERROR] Invalid option. Exit.'
            print help()
            sys.exit()   
         if sys.argv[5].strip() == '-c' : 
            icol = sys.argv[6].strip()   
    else : 
        print '[ERROR] Invalid option. Exit.'
        print help()
        sys.exit() 
    print  '--- : ' ,icol,' ==== : ',mtable,' =====: ',pridicate 
    try:
        conn = getOraConnTns('tradehis_pri')
        cur = conn.cursor() 
        cur1 = conn.cursor()
        cur2 = conn.cursor()
        owner = getowner(cur2,mtable)
        #construct sql
        cols = ''
        if len(icol)  <= 1 :
            colist= getcolumns(cur1,mtable)
            for x in colist :
                    cols = cols + x +','
            cols = cols[:-1]
        else : 
            cols = icol
        clist = cols.split(',') 
        sql2 = ''' 
            select %s from %s where  %s
            '''
        tbs = str(owner) +'.'+ str(mtable)
        sql  = sql2%(cols,tbs,pridicate)
        doexecute(cur,sql,clist)
    except Exception, e:
        print 'ERROR : >>> : ', e
    finally:
        conn.close()
        
    main()
文章目录