{"id":1180,"date":"2022-07-08T11:07:05","date_gmt":"2022-07-08T03:07:05","guid":{"rendered":"http:\/\/175.24.184.226\/?p=1180"},"modified":"2022-07-08T11:07:05","modified_gmt":"2022-07-08T03:07:05","slug":"python-%e6%95%b0%e6%8d%ae%e5%ba%93%e8%a1%a8%e5%af%bc%e5%87%baexcel%e3%80%81txt","status":"publish","type":"post","link":"\/?p=1180","title":{"rendered":"python \u6570\u636e\u5e93\u8868\u5bfc\u51faexcel\u3001txt"},"content":{"rendered":"
"""\n@author:***\n@date:2022-6-29 10:55:22\n@function:\n\n\/ftp\/..\n"""\nimport base64\nimport io\nimport os\nimport shutil\nimport pymysql\nfrom PIL import Image\nfrom loguru import logger\nimport traceback\nfrom openpyxl import Workbook\nimport psycopg2\nimport psycopg2.extras\nfrom config import *\n# \u8bbe\u7f6e\u65e5\u5fd7\u6587\u4ef6\u5927\u5c0f\nlogger.add("ftp_upload_utils.log", rotation="100 MB", backtrace=True, diagnose=True)\n\ndef compress_image_bs4(b64, mb=23, k=0.9):\n    """\u4e0d\u6539\u53d8\u56fe\u7247\u5c3a\u5bf8\u538b\u7f29\u5230\u6307\u5b9a\u5927\u5c0f\n    :param outfile: \u538b\u7f29\u6587\u4ef6\u4fdd\u5b58\u5730\u5740\n    :param mb: \u538b\u7f29\u76ee\u6807\uff0cKB\n    :param step: \u6bcf\u6b21\u8c03\u6574\u7684\u538b\u7f29\u6bd4\u7387\n    :param quality: \u521d\u59cb\u538b\u7f29\u6bd4\u7387\n    :return: \u538b\u7f29\u6587\u4ef6\u5730\u5740\uff0c\u538b\u7f29\u6587\u4ef6\u5927\u5c0f\n    """\n    f = base64.b64decode(b64)\n    with io.BytesIO(f) as im:\n        o_size = len(im.getvalue()) \/\/ 1024\n        if o_size <= mb:\n            return b64\n        im_out = im\n        while o_size > mb:\n            img = Image.open(im_out)\n            x, y = img.size\n            out = img.resize((int(x * k), int(y * k)), Image.ANTIALIAS)\n            im_out.close()\n            im_out = io.BytesIO()\n            if out.mode == "RGBA":\n                out.save(im_out, 'png')\n            elif out.mode == "RGB":\n                out.save(im_out, 'jpg')\n            else:\n                return ""\n            o_size = len(im_out.getvalue()) \/\/ 1024\n        b64 = base64.b64encode(im_out.getvalue())\n        im_out.close()\n        return str(b64, encoding='utf8')\n\nclass DataBaseCon:\n    """\n    \u6570\u636e\u5e93\u64cd\u4f5c\n    """\n    def __init__(self, table_name, base_info):\n        self.table_name = table_name\n        self.base_info = base_info\n    def connectMysql(self):\n        """\n        Mysql\u6570\u636e\u5e93\u8fde\u63a5\n        """\n        for _ in range(10):\n            try:\n\n                conn = pymysql.connect(**self.base_info)\n                # \u8fd4\u56de\u5b57\u5178\u683c\u5f0f\u7684\u6570\u636e\n                cursor = conn.cursor(pymysql.cursors.DictCursor)\n                return conn, cursor\n            except Exception as err:\n                logger.exception("\u6570\u636e\u5e93\u8fde\u63a5\u5931\u8d25,  %s" % err)\n                return None, None\n\n    def connectPostgreSQL(self):\n        """\n        PG\u5e93\u8fde\u63a5\n        """\n        for _ in range(10):\n            try:\n                conn = psycopg2.connect(**self.base_info)\n                cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)\n                return conn, cursor\n            except Exception as err:\n                logger.exception("\u6570\u636e\u5e93\u8fde\u63a5\u5931\u8d25,  %s" % err)\n        return None, None\n\nclass Upload:\n    def __init__(self, table_name, base_info, base_type):\n        self.table_name = table_name\n        self.base_info = base_info\n        self.out_path = "\/ftp\/industry_chain_data\/"\n        # self.out_path = ".\/"\n        self.base_type = base_type\n    def get_data(self):\n        """\n        \u83b7\u53d6\u6570\u636e\n        :param table_name: \u8868\u540d\n        :param base_info: \u6570\u636e\u5e93\u4fe1\u606f\n        :return:\n        """\n        fields = ""\n        all_data = ""\n        for _ in range(10):\n            try:\n                if self.base_type == "mysql":\n                    conn, cursor = DataBaseCon(self.table_name, self.base_info).connectMysql()\n                elif self.base_type == "pgsql":\n                    conn, cursor = DataBaseCon(self.table_name, self.base_info).connectPostgreSQL()\n                else:\n                    raise TypeError("\u5f53\u524d\u6570\u636e\u5e93\u7c7b\u578b\u65e0\u6cd5\u89e3\u6790")\n                sql = 'select * from %s order by id' % self.table_name\n                cursor.execute(sql)\n                fields = [field[0] for field in cursor.description]\n                all_data = cursor.fetchall()\n                if all_data:\n                    break\n            except Exception as err:\n                logger.error('traceback.format_exc():\\n%s' % traceback.format_exc())\n                logger.error(err)\n            finally:\n                try:\n                    cursor.close()\n                    conn.close()\n                except:\n                    pass\n        return fields, all_data\n    def get_file_name(self):\n        if self.base_type == "mysql":\n            file_name = self.table_name\n        elif self.base_type == "pgsql":\n            file_name = self.table_name.split('.')[1]\n        else:\n            file_name = ''\n        return file_name\n    def to_excel(self):\n        """\n        \u5c06\u6570\u636e\u5bfc\u51fa\u5230excel\n        :return:\n        """\n        logger.info("{}\uff1a\u6b63\u5728\u8bfb\u53d6\u6570\u636e\uff0c\u51c6\u5907\u5bfc\u51faExcel...".format(self.table_name))\n        fields, all_data = self.get_data()\n        # \u5b9e\u4f8b\u5316\n        wb = Workbook()\n        # \u521b\u5efa\u8868\uff08sheet\uff09\n        sheet = wb.worksheets[0]\n\n        # \u5199\u5165\u6807\u9898\n        row = 1\n        for col, field in enumerate(fields):\n            sheet.cell(row=row, column=col+1, value=field)\n\n        # \u5199\u5165\u6570\u636e\n        length = len(all_data)\n        for data in all_data:\n            for col, field in enumerate(data):\n                try:\n                    # \u5927\u4e8eexcel\u5b57\u7b26\u957f\u5ea6\u7684base64\u683c\u5f0flogo,\u8fdb\u884c\u526a\u88c1\u538b\u7f29\n                    cell_message = data[field]\n                    if len(cell_message) > 32767:\n                        cell_message = compress_image_bs4(b64=cell_message)\n                    sheet.cell(row+1, col+1, cell_message)\n                except Exception as e:\n                    sheet.cell(row + 1, col + 1, '')\n                    logger.error(e)\n            logger.info("\u8868{0}".format(self.table_name)+"\u5bfc\u51fa\u8fdb\u5ea6\uff1a%.2f" % (row*100\/length) + "%")\n            row += 1\n        # \u4fdd\u5b58\u6570\u636e\n        file_name = self.get_file_name()\n        path = self.out_path+file_name\n        logger.info("\u6b63\u5728\u4fdd\u5b58\u81f3{} ...".format(path))\n        # \u5982\u679c\u5b58\u5728\u76ee\u5f55\u5219\u5220\u9664\uff0c\u5e76\u521b\u5efa\u65b0\u6587\u4ef6\u5939\n        if os.path.exists(path):\n            shutil.rmtree(path,True)\n        os.mkdir(path)\n        # \u5c06\u6570\u636e\u4fdd\u5b58\u81f3\u76f8\u5e94\u6587\u4ef6\u5939\u4e2d\n        wb.save("%s.xlsx" % (path+"\/"+file_name))\n        logger.info("{}\uff1a\u4fdd\u5b58\u5b8c\u6bd5".format(file_name))\n\n    def to_txt(self):\n        """\n        \u5c06\u6570\u636e\u5bfc\u51fa\u5230.txt\u6587\u4ef6\n        :return:\n        """\n        logger.info("{}\uff1a\u6b63\u5728\u8bfb\u53d6\u6570\u636e\uff0c\u51c6\u5907\u5bfc\u51faTXT\u6587\u672c...".format(self.table_name))\n        fields, all_data = self.get_data()\n        file_name = self.get_file_name()\n        path = self.out_path + file_name\n        # \u5982\u679c\u5b58\u5728\u76ee\u5f55\u5219\u5220\u9664\uff0c\u5e76\u521b\u5efa\u65b0\u6587\u4ef6\u5939\n        if os.path.exists(path):\n            shutil.rmtree(path, True)\n        os.mkdir(path)\n        fp = open("%s.txt" % (path+"\/"+file_name), "wb+")\n        # \u5199\u5165\u6807\u9898\n        for col, field in enumerate(fields):\n            line = '\\t'.join([str(val) for val in field]) + "\\n"  # \u683c\u5f0f\uff1aA\\tB\\tC\\n\n            fp.write(line.encode('utf8'))  # \u4ee5utf8\u5199\u5165\n        # \u5199\u5165\u6570\u636e\n        length = len(all_data)\n        for row, data in enumerate(all_data):\n            # pass\n            line = '\\t'.join([str(val) for val in list(data.values())]) + "\\n" # \u683c\u5f0f\uff1aA\\tB\\tC\\n\n            fp.write(line.encode('utf8') ) #\u4ee5utf8\u5199\u5165\n            logger.info("\u8868{0}".format(self.table_name) + "\u5bfc\u51fa\u8fdb\u5ea6\uff1a%.2f" % (row * 100 \/ length) + "%")\n        fp.close()\n\nif __name__ == '__main__':\n    # Upload(table_name="51job_jiangsu_all", base_info=MY_SQL_LOCAL_LXC, base_type="mysql").to_excel()\n    # Upload(table_name="school_base_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_excel()\n    # Upload(table_name="school_detail_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_excel()\n    # Upload(table_name="school_intro_detail_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_excel()\n    # Upload(table_name="school_professional_detail_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_excel()\n    # Upload(table_name="school_professional_intro_detail_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_excel()\n    # Upload(table_name="industry_chain.industry_info", base_info=PG_SQL_LOCAL, base_type="pgsql").to_excel()\n    # Upload(table_name="industry_chain.industry_info", base_info=PG_SQL_LOCAL, base_type="pgsql").to_txt()\n    # Upload(table_name="school_professional_detail_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_txt()\n    Upload(table_name="school_professional_intro_detail_info", base_info=MY_SQL_LOCAL_SCHOOL, base_type="mysql").to_txt()<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
""" @author:*** @date:2022-6-29 10:55:22 @function: \/   \u2026 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0},"categories":[48],"tags":[],"_links":{"self":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts\/1180"}],"collection":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1180"}],"version-history":[{"count":1,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/1180\/revisions"}],"predecessor-version":[{"id":1181,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/1180\/revisions\/1181"}],"wp:attachment":[{"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1180"},{"taxonomy":"post_tag","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}