自动更新表统计信息

#author yw_shan
import os,sys
import subprocess
import re
import time
import threading
import argparse
import testlog
if sys.version.startswith('2.7'):
    import Queue
else:
    import queue

class updateStatis(object):
    def __init__(self,host,user,pwd):
        self.host=host
        self.user=user
        self.pwd=pwd

    def get_dbconn(self):
        # traf="trafci.sh -h {} -u {} -p {} -q " .format(self.host,self.user,self.pwd)
        traf="trafci.sh -h {} -u {} -p {} -q " .format(num.host,num.user,num.password)
        return  traf

    def get_tab(self):
        gettab=[]
        with open('./conf/tab_loop_test.conf','r') as f:
            trss_sql=f.readlines()
            trss_sqls = ''.join(trss_sql)
            tab = re.split(r',|\n', trss_sqls)
            for tabs1 in tab:
                if tabs1 is None or tabs1 == '' or tabs1.startswith('#') or tabs1 =='':
                    continue
                tabss = tabs1.strip().replace('"', '').replace(',', '')
                gettab.append(tabss)
            return gettab

    def get_sql(self,pp,conn,tabs):
        try:
            for tabs1 in tabs:
                sql='"update statistics for table {} on every key sample"' .format(tabs1)
                esql=conn+sql
                q.put(esql)
                log.logger.info(pp+"-->"+esql)
                q.task_done()
        except Exception as e:
            log.logger.error("get_sql-->" + e)


    def exec_sql(self,cc):
        try:
            flag=3
            while 1:
                if not q.empty():
                    gesql=q.get()
                    # print("{}==>{}" .format(cc,gesql))
                    log.logger.info(cc+"==>"+gesql)
                    sp_out = subprocess.Popen(gesql, shell=True, stdout=subprocess.PIPE).communicate()[0]
                    log.logger.info(sp_out)
                    q.join()
                    if ' ERROR[' in sp_out:
                        log.logger.error(gesql)
                        rq = time.strftime("%Y%m%d%H%M", time.localtime())
                        with open('./logs/failUpdateSql.log', 'a+') as f2:
                            f2.write(str(rq)+' -- '+str(gesql)+'\n')
                else:
                    flag=flag-1
                    if flag>=0:
                        continue
                    if flag<0:
                        break
        except Exception as e:
            log.logger.error("exec_sql-->"+e)

    def parser(self):
        parser=argparse.ArgumentParser(prog='update_statis.py',description='Function: update table statistics')
        parser.add_argument('-t','--thread',type=int,default=2,help='set the parallel number of threads')
        parser.add_argument('-H', '--host', type=str, default='', help='set host ip address')
        parser.add_argument('-u', '--user',  type=str,default='', help='set user')
        parser.add_argument('-p', '--password', type=str,default='', help='set password')
        parser.add_argument('-q', '--queue', type=int, default=3, help='set the parallel number of queue')
        arg=parser.parse_args()
        return arg

def logs(self):
    su='statUpdate.log'
    fsu='failUpdateSql.log'
    rq = time.strftime("%Y%m%d%H%M", time.localtime())
    if not os.path.exists('logs'):
        os.mkdir('./logs')
    os.chdir(os.path.join(os.getcwd(), 'logs'))
    if os.path.exists(su):
        os.rename(su,su+'.'+rq)
    if os.path.exists(fsu):
        os.rename(fsu,fsu+'.'+rq)
    os.chdir(os.path.dirname(os.getcwd()))

def main(self):
    try:
        thrp = threading.Thread(target=us.get_sql, args=('procdure', conn, tab,))
        thrp.start()
        for i in range(int(num.thread)):
            ct=threading.Thread(target=us.exec_sql, args=('custmer-%d' % i,))
            log.logger.info(ct)
            ct.start()
        for i in range(int(num.thread)):
            ct.join()
        log.logger.info("run complete,waiting exit......")
    except Exception as e:
        log.logger.error("--main()--"+e)

import logging
from logging import handlers

class Logger(object):
    level_relations = {
        'debug':logging.DEBUG,
        'info':logging.INFO,
        'warning':logging.WARNING,
        'error':logging.ERROR,
        'crit':logging.CRITICAL
    }

    def __init__(self,filename,level='info',when='D',backCount=7,fmt='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s'):
        self.logger = logging.getLogger(filename)
        format_str = logging.Formatter(fmt)
        self.logger.setLevel(self.level_relations.get(level))
        sh = logging.StreamHandler()
        sh.setFormatter(format_str)
        th = handlers.TimedRotatingFileHandler(filename=filename,when=when,backupCount=backCount,encoding='utf-8')
        th.setFormatter(format_str)
        self.logger.addHandler(sh)
        self.logger.addHandler(th)

执行效果
执行效果