Пример #1
0
 def __init__(self,db_name="",path=""):
     if len(path) > 0:
         self.dbconf_filename = os.path.join(path,"db_conf.ini")
     else:
         self.dbconf_filename = "db_conf.ini"
     self.dbconfig = MyBaseClass(self.dbconf_filename)
     if len(db_name) > 0:
         conf = self.db_choose_value(db_name)
         self.conn = self.Connect_db(conf)
Пример #2
0
 def __init__(self,filename="data_export.ini",db_name=""):
     self.filename = filename
     self.dbfile_dir = "lib"
     self.db_name = db_name
     self.dbconfig = MyBaseClass(self.filename)
     self.mydbclass = MyDBClass(self.db_name,self.dbfile_dir)
     if len(db_name) > 0:
         self.one_option = self.dbconfig.get_min_value() #获取export.ini 的首个选项配置
     self.process_num = 5 #定义线程的数量
Пример #3
0
class MyDBClass():
    def __init__(self,db_name="",path=""):
        if len(path) > 0:
            self.dbconf_filename = os.path.join(path,"db_conf.ini")
        else:
            self.dbconf_filename = "db_conf.ini"
        self.dbconfig = MyBaseClass(self.dbconf_filename)
        if len(db_name) > 0:
            conf = self.db_choose_value(db_name)
            self.conn = self.Connect_db(conf)

    #重新生成配置文件
    def gernal_dbconf_again(self,members):
        os.rename(self.dbconf_filename,'%s.bak' % self.dbconf_filename)
        self.init_db_conf(members)

    #返回和组合邮件参数
    def get_mail_info(self,subject,attach_file,db_name):
        conf = self.db_choose_value(db_name)
        args = self.dbconfig.db_get_value(conf)
        args['subject'] = subject
        args['export_file'] = attach_file
        return args

    def init_db_conf(self,members):
        db_conflist=[]
        if not os.path.exists(self.dbconf_filename):
            db_conf_default = {}
            if members > 0:
                for i in range(0,members):
                    db_name = raw_input("请输入数据库名称......").strip()
                    while True:
                        if  len(db_name.strip()) == 0:
                            db_name = raw_input("请输入数据库名称......").strip()
                        else:
                            break
                    db_conf_default = self.set_default_value(db_name)
                    self.dbconfig.set_db_value(db_conf_default)
                    if self.dbconfig.recive_db('db'):
                        print "数据库 %s 配置生成成功!" % db_name
                path = self.dbconfig.save_db('lib/')
                print "数据库保存配置成功!路径如下:当前目录:%s, 文件保存在:%s" % (os.getcwd(),path)
        else:
            print "数据库配置文件 %s 已存在。" % self.dbconf_filename
            sys.exit(1)

    def set_default_value(self,db_name):
        args = {
        'db_num':'tms_100199273309',
        'host':'10.0.199.27',
        'port':3309,
        'db_user':'******',
        'db_password':'******',
        'db_name':db_name,
        'db_type':'mysql',
        'sender_mailbox' : '*****@*****.**',
        'sender_mailbox_password' : 'jr123456',
        'reciver_mailbox' : '*****@*****.**',
        'smtp_server' : 'smtp.juren.com'
        }
        return args

    #Choose DataBase
    def db_choose_value(self,db_name):
        if db_name == "tms":
            db_conf = "tms_db_conf"
        elif db_name == "jurenlong":
            db_conf = "jurenlong_db_conf"
        elif db_name == "card":
            db_conf = "card_db_conf"
        else:
            print "Must be input db_name"
            return False
        return db_conf


    def check_db(self,args):
        try:
            conn = MySQLdb.connect(host=args['host'],port=args['port'],user=['user'],password=['password'],db=args['db_name'],charset='utf8')
        except Exception, e:
            print e
            return False
        return True
Пример #4
0
class MyExportClass():
    def __init__(self,filename="data_export.ini",db_name=""):
        self.filename = filename
        self.dbfile_dir = "lib"
        self.db_name = db_name
        self.dbconfig = MyBaseClass(self.filename)
        self.mydbclass = MyDBClass(self.db_name,self.dbfile_dir)
        if len(db_name) > 0:
            self.one_option = self.dbconfig.get_min_value() #获取export.ini 的首个选项配置
        self.process_num = 5 #定义线程的数量

    #重新生成配置文件
    def gernal_mailconf_again(self,members):
        os.rename(self.filename,'%s.bak' % self.filename)
        self.init_export_conf(members)

    #获取导出数据配置值
    def get_export_conf_value(self):
        args_list = {}
        args_list = self.dbconfig.export_get_value()
        return args_list

    #处理数据配置信息
    def deal_export_conf(self,item):

        mail_list = {}
        title = item['title']
        sql_file = item['sql_file']
        data_dir = "%s/%s/%s" % (item['excel_data_dir'],time.strftime("%Y%m%d"),item['jira_case'])
        filename = "%s.xlsx" % title
        if not os.path.exists("sql_dir"):
            os.makedirs("sql_dir")
        elif not os.path.exists(data_dir):
            os.makedirs(data_dir)
        else:
            pass
        if os.path.exists(sql_file):
              #得出sql语句
              sqldetail = self.get_sql_value(sql_file)
              #通过sql语句得出查询结果
              result = self.deal_sql(sqldetail,0)
              print result
              #写入excel
              self.deal_excel(data_dir,filename,result)
        else:
            print "%s is not exsit!sql文件不存在!" % sql_file

    #打包数据,清理数据并且发送邮件
    def tar_data(self,root_dir):
        args_list = {}
        args_list = self.dbconfig.export_get_single_value(self.one_option)
        data_dir =  "%s/%s" % (args_list['excel_data_dir'],time.strftime("%Y%m%d"))
        zip_password = args_list['zip_password']
        #压缩excel
        outfilename = self.deal_exported_file(data_dir,root_dir,zip_password)

        # #发送收件人邮箱
        mail_list = self.mydbclass.get_mail_info("%s-%s" % (time.strftime("%Y-%m-%d"),'导出数据'),outfilename,self.db_name)
        mail_class = Send_Email(mail_list)
        mail_class.sendAttachMail()


    #返回sql文件内容
    def get_sql_value(self,file_name):
        if os.path.exists(file_name):
            with open (file_name,'rb') as f:
                f.seek(-2,2)
                if f.read(1).strip() == ";":
                    pass
                else:
                    f.seek(0,0)
                    sqlstr = ""
                    for line in f.readlines():
                        line = line.strip("\n")
                        if line.startswith("#"):
                            pass
                        else:
                            sqlstr = sqlstr + " " + line
                    sqlstr = sqlstr + ";"
            return sqlstr
        else:
            print "%s is not exist!" % file_name

    #处理sql语句,并插入excel中
    def deal_sql(self,sql,tag):
#        mydbclass = MyDBClass(self.db_name,self.dbfile_dir)
        result = self.mydbclass.Query(sql,tag)
        return result

    #写入excel
    def deal_excel(self,data_dir,filename,data):
        list1 = []
        list2 = []
        list3 = []

        file_name = os.path.join(data_dir,filename)
        workbook = xlsxwriter.Workbook(file_name)
        worksheet = workbook.add_worksheet()
        # for i in data['field_names']:
        #     for key,value in i.items():
        #         if key in list1:
        #             pass
        #         else:
        #             list1.append(key)
        #         list2.append(value)
        #     list3.append(list2)
        #     list2 = []

        #添加列名
        red = workbook.add_format({'color': 'red','bold': True})

        i = 0
        for value in data['field_names']:
            #worksheet.write(0,i,value.deco  de('utf-8'))
            worksheet.write_rich_string(0,i,red,value.decode('utf-8'))
            i = i + 1
        j = 1
        col = 0
        #添加数值
        for row_data in data['returnData']:
            for col_data in row_data:
                if type(col_data) == datetime.datetime:
                    col_data = col_data.strftime("%Y-%m-%d %H:%M:%S")
                worksheet.write(j,col,col_data)
                col = col + 1
            j = j + 1
            col = 0


    #使用7z压缩excel文件
    def deal_exported_file(self,dir_name,root_dir,zip_password):
        import subprocess
        import shutil
        import re
        tempfile = ""
        tempdirname = "tempdir"
        temp_dir = os.path.join(root_dir,dir_name,tempdirname)

        templist = []
        new_filename = '%s.7z' % time.strftime("%Y%m%d")

        if os.path.exists(dir_name):
            #创建临时文件夹,删除临时文件夹中的文件
            if not os.path.exists(temp_dir):
                os.makedirs(temp_dir)
            else:
                if os.path.isdir(temp_dir):
                    shutil.rmtree(temp_dir)
                    os.makedirs(temp_dir)
                else:
                    os.remove(temp_dir)


            #转移文件到临时文件夹
            for r,d,f in os.walk(dir_name):
                for filename in f:
                    if len(filename) > 0:
                        if filename.split(".")[-1] == "xlsx":
                            templist.append(os.path.join(r,filename))

            for tempfile in templist:
                shutil.move(tempfile,temp_dir)

            tempfile = ""
            #拼接临时文件夹中的文件
            for name in os.listdir(temp_dir):
                  tempfile = tempfile + "    "+ name

            os.chdir(temp_dir)
            cmd = "%s %s %s%s %s %s" % ('7za','a','-p',zip_password,new_filename,tempfile)
            p1 = subprocess.Popen(cmd,stdout=subprocess.PIPE,shell=True)
            output = p1.communicate()[0]


            #清空临时文件夹
            if os.path.exists(temp_dir):
                for tempfile in os.listdir(temp_dir):
                    if tempfile.split(".")[-1] == "xlsx":
                        os.remove(os.path.join(temp_dir,os.path.basename(tempfile)))
            last_new_filename = os.path.abspath(os.path.join(temp_dir,new_filename))
            return last_new_filename
        else:
            print "%s/%s文件不存在" % (dir_name,dir_name)
            sys.exit(1)


    #初始化导出数据的配置
    def init_export_conf(self,members):
        if not os.path.exists(self.filename):
            if members > 0:
                db_conf = {}
                dic_list = {}
                for i in range(0,members):
                    i = i + 1
                    numbers = self.dbconfig.get_numbers(i)
                    dic_list = self.print_input_value()
                    db_conf = self.set_default_value(dic_list,numbers)
                    #生成sql文件和目录
                    self.make_sqldir(db_conf['sql_file'])
                    self.dbconfig.set_db_value(db_conf)
                    if self.dbconfig.recive_db('case',i):
                        print "配置 %s 生成成功! " % numbers

            path = self.dbconfig.save_db()
            print "数据库保存配置成功!路径如下:当前目录:%s, 文件保存在:%s" % (os.getcwd(),path)
            print "==========================================================================="
            print "本次配置如下..............................................................."
            with open(path) as f:
                print f.read()
        else:
            print "%s 配置已存在,请移除!" % self.filename


    #生成sqldir文件
    def make_sqldir(self,file_path):
        dir_name = os.path.dirname(file_path)
        if os.path.exists(dir_name):
            pass
        else:
            os.makedirs(dir_name)
        with open(file_path,'w') as f:
            f.write("")
        f.close()



    #默认导出数据表
    def set_default_value(self,dic_list,numbers):
        args = {
                'sn':'%s' % numbers,
                'database_name':  dic_list['db_name'],
                'title':'%s' % dic_list['title'],
                'subject':"%s_%s" % (time.strftime("%Y-%m-%d"),dic_list['title']),
                'jira_case':dic_list['jira_case'],
                'sql_file':"data/sql_dir/%s_%s_%s.sql" % (time.strftime("%Y-%m-%d"),dic_list['jira_case'],dic_list['title']),
                'filename':"%s_%s.xlsx" % (dic_list['jira_case'],dic_list['title']),
                'excel_data_dir':'data/export_dir/',
                'zip_password' : 'jr123456'
                }
        return args

    #打印输入函数
    def print_input_value(self):
        try :
            db_name = raw_input("请输入数据库名称 \033[1;31;40m 名称必须与lib/db_conf.ini下的配置中的db_name必须一致\033[0m......").strip()
            while True:
                if  len(db_name.strip()) == 0:
                    db_name = raw_input("\033[1;31;40m 请输入数据库名称,与lib/db_conf.ini下的配置中的db_name必须一致\033[0m......").strip()
                else:
                    break

            jira_case = raw_input("请输入本次提案号\033[1;31;40m (例如:TMS_3322)\033[0m,请统一使用大写字母和下划线表示......").strip()
            while True:
                if  len(db_name.strip()) == 0:
                    jira_case = raw_input("请输入本次提案号 \033[1;31;40m (例如:TMS_3322)\033[0m,请统一使用大写字母和下划线表示......").strip()
                else:
                    break

            title = raw_input("请输入本次导出生成的文件名 \033[1;31;40m(例如:小班数据)\033[0m,请保持文件名唯一,为了区分方便......").strip()
            while True:
                if  len(db_name.strip()) == 0:
                    title = raw_input("请输入本次导出生成的文件名 \033[1;31;40m(例如:小班数据)\033[0m,请保持文件名唯一,为了区分方便......").strip()
                else:
                    break


            return {
            'db_name':db_name,
            'jira_case':jira_case,
            'title':title,
            }
        except KeyboardInterrupt:
            sys.exit(1)