pyhton 导出数据库数据到excel文件，上传到远程数据并缓存地址

一时失言乱红尘 2021-12-11 15:45 230阅读 0赞

class Updload:
    	    @classmethod
    	    def output(cls, items):
    	        '''
    	        数据导出
    	        :param items:
    	        :return:
    	        '''
    	        import xlwt
    	        team_id = items.get('team_id')
    	        function = items.get('function')
    	        export_time = items.get('export_time')
    	        model = items.get('model')
    	        sql = items.get('sql')
    	        prefix = items.get('prefix')
    	        params = items.get('params')
    	        count = items.get('count')
    	        sheet_name = items.get('sheet_name')
    	        if not (team_id and function and export_time):
    	            return False
    	        fields = params.values()
    	        params = params.keys()
    	        check_select = sql.find('select')
    	        if check_select > 0:
    	            sql = sql.format(params)
    	            info = model.find(sql)
    	        else:
    	            info = model.select(sql)
    	        if not count:
    	            check_select = sql.find('select')
    	            if check_select > 0:
    	                count = model.find_count(sql.format('count(1)'))
    	            else:
    	                count = model.count(sql)
    	        root_dir = os.path.abspath(os.path.join(os.getcwd(), ".."))
    	        dir_name = cls.get_key(team_id + function + str(export_time))
    	        path_image = '{0}/static/upload/excel/{1}/'.format(root_dir, dir_name)
    	        if not os.path.exists(path_image):
    	            os.makedirs(path_image)
    	        workbook = xlwt.Workbook(encoding='utf-8')
    	        sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
    	        for field in range(0, len(fields)):
    	            sheet.write(0, field, fields[field])
    	        for row in range(1, count + 1):
    	            for col in range(0, len(fields)):
    	                sheet.write(row, col, u'%s' % info[row - 1].get(params[col]))
    	        excel_path = path_image + '/' + prefix
    	        workbook.save(excel_path)
    	        cls.zip_excel(excel_path, dir_name, path_image, team_id, function, sql)
    	
    	    @classmethod
    	    def zip_excel(cls, excel_path, dir_name, path_image, team_id, function, sql):
    	        '''
    	        :return:
    	        '''
    	
    	        def get_key(string):
    	            return hashlib.md5(string).hexdigest()
    	
    	        import zipfile
    	        z_path = path_image + dir_name + '.zip'
    	        with zipfile.ZipFile(z_path, "w") as zFile:
    	            zFile.write(r"{0}".format(excel_path), 'zip/{0}.xlsx'.format(function))
    	        if os.path.isfile(z_path):
    	            result = PublicManage.upload_zip_to_qn(team_id, '{0}.zip'.format(dir_name), z_path)
    	            if result:
    	                cache_db = nosql_db["export_cache"]
    	                params = {
    	                    '_id': team_id
    	                }
    	                key_id = get_key(sql)
    	                cache_result = cache_db.find_one(params)
    	                if cache_result:
    	                    cache_db.update(
    	                        params, {'$set': {
    	                            key_id: result
    	                        }}
    	                    )
    	                else:
    	                    params[key_id] = result
    	                    cache_db.insert(params)
    	            else:
    	                return False
    	            try:
    	                # 上传完毕删除本地数据
    	                os.remove(z_path)
    	                os.remove(excel_path)
    	                import shutil
    	                # 删除图片本地的目录
    	                shutil.rmtree(path_image)
    	            except:
    	                pass