笔记：主要掌握根据字典内容同时替换多个字符串

import json import re import os import pandas as pd from sqlalchemy import create_engine # 根据字典同时替换多个字符串 class make_xlat: def __init__(self, *args, **kwargs): self.adict = dict(*args, **kwargs) self.rx = self.make_rx() def make_rx(self): return re.compile('|'.join(map(re.escape, self.adict))) def one_xlat(self, match): return str(self.adict[match.group(0)]) def __call__(self, text): return self.rx.sub(self.one_xlat, text) def get_file_path(rootdir): for (dirpath, dirnames, filenames) in os.walk(rootdir): pathname = [os.path.join(dirpath, filename) for filename in filenames] return pathname def write2sql(df, tableName): con = create_engine('mysql+pymysql://user:password@host:port/database?charset=utf8') df.index = range(1, len(df) + 1) df.to_sql(tableName, con, schema='database', if_exists='append', index=True, index_label='id') # chunksize=10000 def get_data(filename_list): data_li = [] for filename in filename_list: print('filename==>', filename) with open(filename, 'r+', encoding='utf-8') as f: content = f.read().split('=')[1] datas = json.loads(re.match(r'.*data: (.*),font:', content).group(1)) # print('datas==>', datas) if datas: font = json.loads(re.match(r'.*font:(.*)}', content).group(1)) adict = {font_map['code']: font_map['value'] for font_map in font['FontMapping']} print('adic===>', adict) translate = make_xlat(adict) expenses = ['sumasset', 'fixedasset', 'monetaryfund', 'accountrec', 'inventory', 'sumliab', 'accountpay', 'advancereceive', 'sumshequity', 'cashanddepositcbank', 'loanadvances', 'saleablefasset', 'borrowfromcbank', 'acceptdeposit', 'sellbuybackfasset', 'settlementprovision', 'borrowfund', 'agenttradesecurity', 'premiumrec', 'stborrow', 'premiumadvance'] tbs = ['accountrec_tb', 'inventory_tb', 'monetaryfund_tb', 'accountpay_tb', 'advancereceive_tb', 'sumshequity_tb', 'tsatz', 'tdetz', 'cashanddepositcbank_tb', 'loanadvances_tb', 'saleablefasset_tb', 'borrowfromcbank_tb', 'acceptdeposit_tb', 'sellbuybackfasset_tb', 'settlementprovision_tb', 'borrowfund_tb', 'agenttradesecurity_tb', 'premiumrec_tb', 'stborrow_tb', 'premiumadvance_tb', 'ld', 'zcfzl'] for data in datas: expenses_dic = {} expenses_dic['gp_code'] = filename.split('.')[0].split('\\')[-1] expenses_dic['reportdate'] = data['reportdate'] expenses_dic['noticedate'] = data['noticedate'] for expense in expenses: if data[expense] == '-': expenses_dic[expense] = data[expense] else: # print(translate(data[expense])) expenses_dic[expense] = round(float(translate(data[expense])) / 100000000, 2) # 单位亿 for tb in tbs: if data[tb] == '-' or '无穷大' in data[tb] or '非数字' in data[tb]: expenses_dic[tb] = data[tb] else: expenses_dic[tb] = round(float(translate(data[tb])) * 100, 2) # 百分比 print('expenses_dic=====>', expenses_dic) data_li.append(expenses_dic) df = pd.DataFrame(data_li) return df if __name__ == '__main__': rootdir = 'dir' filename_list = get_file_path(rootdir) df = get_data(filename_list) print('=========================df') print(df) # write2sql(df, 'profit_tb') # write2sql(df, 'xjll_tb')