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

最新推荐文章于 2022-03-27 14:49:05 发布

Morgan Wang

最新推荐文章于 2022-03-27 14:49:05 发布

阅读量306

点赞数

CC 4.0 BY-SA版权

分类专栏： python 文章标签： python xlwt

本文链接：https://blog.youkuaiyun.com/TianPingXian/article/details/95194420

python 专栏收录该内容

82 篇文章

订阅专栏

本文介绍了如何使用Python从数据库提取数据，然后利用xlwt库将数据写入Excel文件。接着，详细阐述了如何将生成的Excel文件上传到远程服务器，并缓存文件地址以供后续使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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