python 操作数据库

目的：通过excel定义检查指标项，然后通过python读取指标，通过Oracle sqlplus工具去执行获取具体巡检结果。

#unicode=UTF-8
#-*- coding: UTF-8 -*- 

import os
import sys
import xlrd
import paramiko
reload(sys)
sys.setdefaultencoding('UTF-8')

from subprocess import Popen, PIPE
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'


sqlCommand = ''
tns=''

strCommand = ["sqlplus", "dbmgr/123456@XX.XX.XX.XX:1521/dbmgr"]
FormatStr = 'set linesize 120\n'
FormatStr = FormatStr + 'set pagesize 100\n'
FormatStr = FormatStr + 'set term off verify off feedback off tab off \n'
FormatStr = FormatStr + 'set numwidth 40\n'
FormatStr = FormatStr + sqlCommand+'\n'

def gettns():
    print ReadExcels('E:\\DB_checklist.xlsx','Sheet2')


#打开一个workbook
workbook = xlrd.open_workbook('E:\\DB_checklist.xlsx')

#抓取所有sheet页的名称
worksheets = workbook.sheet_names()
#print('worksheets is %s' %worksheets)

#定位到sheet1
worksheet1 = workbook.sheet_by_name('Sheet1')

#遍历sheet1中所有行row
def excelrows():
    num_rows = worksheet1.nrows
    for curr_row in range(num_rows):
        row = worksheet1.row_values(curr_row)
    #print('row%s is %s' %(curr_row,row))

#遍历sheet1中所有列col
def excelcols():
    num_cols = worksheet1.ncols
    for curr_col in range(num_cols):
        col = worksheet1.col_values(curr_col)
    #print('col%s is %s' %(curr_col,col))

#遍历sheet1中所有单元格cell
def Reader(worksheet):
    sqls=[]
    num_rows = worksheet.nrows
    for rown in range(num_rows):
    #for coln in range(num_cols):
        cell = worksheet.cell_value(rown,0)
        if str(cell).startswith('*.'):
            cell='show parameter '+cell[2:]
            sqls.append(cell)
            continue
        elif(str(cell).startswith('select')):
            sqls.append(cell)
            continue
        else:
            continue
    #print type(sqls)
    return sqls

def ReadExcels(path,ename):
    workbook = xlrd.open_workbook(path)
    worksheet = workbook.sheet_by_name(ename)
    #print Reader(worksheet1)
    return Reader(worksheet)  

def red(file):
    files=open(file,'r')
    lines=files.readlines()
    for line in lines:
        print line+'\n'
    files.close()
    return lines

def write(Content):
    f=open(os.getcwd()+'\check.txt','a')
    f.write(Content)
    f.close()


def getsqls():
    for i,sql in enumerate (ReadExcels('E:\\DB_checklist.xlsx','Sheet1')):
        print '\n'+'The index is %s, sql is %s ' %(i,sql)
    return sql

def ssh2(ip,username,passwd,cmd):  
    try:  
        paramiko.util.log_to_file(os.getcwd()+'\paramiko.log') 
        ssh = paramiko.SSHClient()  
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())  
        ssh.connect(ip,22,username,passwd,timeout=5)  
        for m in cmd:  
            stdin, stdout, stderr = ssh.exec_command(m)  
#           stdin.write("Y")   #
            out = stdout.readlines()
            print out
            #stdin, stdout, stderr = ssh.exec_command('su - oracle')
            #getsqlplus()
        ssh.close()
    except :  
        print '%s\tError\n'%(ip)

"""
#批量提取服务器信息
"""


def getsqlplus():    

    if (os.path.exists(os.getcwd()+'\check.txt')):
        os.remove(os.getcwd()+'\check.txt')

    for i,sql in enumerate (ReadExcels('E:\\DB_checklist.xlsx','Sheet1')):
        print '\n'+'The index is %s, sql is %s ' %(i,sql)
        proc = Popen(strCommand, stdout=PIPE, stdin=PIPE, stderr=PIPE)
        proc.stdin.write(sql)
        (out, err) = proc.communicate()    
        if proc.returncode != 0:
            print err
            sys.exit(proc.returncode)
        else:
            #print 'end'
            print out.split('SQL>')[1]
            write(out.split('SQL>')[1])
    print os.getcwd()+'\check.txt is created!'

getsqlplus()