以前曾经使用pb写过一个指标检查，发现遇到过10万的问题数据就会性能很慢，使用python后发现导出300万以上轻轻松松，数据时代来临了，自动清洗，自动报告成为可能
sql下的txt格式
服务机构类型为空或代码不在代码表 SELECT AAB034,kB01.* FROM kB01 WHERE akb022 IS NULL OR akb022 NOT IN (SELECT AAA102 FROM AA10 WHERE AAA100='AKB022') 服务等级类型为空或代码不在代码表 SELECT AAB034,kB01.* FROM kB01 WHERE AKA101 IS NULL OR AKA101 NOT IN (SELECT AAA102 FROM AA10 WHERE AAA100='AKA101') 服务当事人id类型为空 SELECT AAB034,kB01.* FROM kB01 WHERE aaz010 IS NULL 服务机构类别为空或代码不在代码表 SELECT AAB034,kB01.* FROM kB01 WHERE BKB010 IS NULL OR BKB010 NOT IN (SELECT AAA102 FROM AA10 WHERE AAA100='BKB010') 服务机构所属行政区划为空或代码不在代码表 SELECT AAB034,kB01.* FROM kB01 WHERE AAB034 IS NULL OR AAB034 NOT IN (SELECT AAA102 FROM AA10 WHERE AAA100='AAB034')一、dos批处理调用；
for %%a in (.\sql\*.txt) do F:\内蒙\py\pyout.py %%a
二、pyout.py；读取检查sql，调用sql执行引擎
#-*- coding:gbk -*- import datetime import time import os import sys import xlwt import openpyxl def txt2xls(filename): print('exec sql ... ') f = open(filename,encoding='gbk') filename=filename[6:] os.system("mkdir xls\\"+filename[0:-4]) x=0 while True: line = f.readline() line=line.replace('"','')[0:-1] if not line: break i=line.split('\t') item=i[1] if len(item)==0: continue #print(i[0]) #print(i[1]) #print(item) cmd='F:\\内蒙\\py\\pyoratsv.py "F:\\内蒙\\py\\xls\\'+filename[0:-4]+"\\"+i[0]+'.txt" "'+i[1]+'"' print(cmd) cont = os.popen(cmd).read() print(cont) x += 1 print(x) f.close() if __name__ == "__main__": filename = sys.argv[1] txt2xls(filename)三、pyoratsv.py；sql执行引擎
#-*- coding:utf-8 -*- import sys import csv import cx_Oracle import codecs import os,io import datetime import time os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.ZHS16GBK' os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK' os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8' os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' os.environ['PATH']='E:\instantclientwin32_jb51\instantclient_11_2;%path%' os.environ['TNS_ADMIN']= 'E:\instantclientwin32_jb51\instantclient_11_2' os.environ['ORACLE_BASE'] = 'E:\instantclientwin32_jb51\instantclient_11_2' os.environ['ORACLE_HOME'] = 'E:\instantclientwin32_jb51\instantclient_11_2' os.chdir(r'F:\pyconsole\tool') #sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='gb18030') fn=sys.argv[1] sql=sys.argv[2] conn=cx_Oracle.connect("","","/orcl") curs=conn.cursor() dt=str(datetime.datetime.now()).replace(':','').replace('-','').replace(' ','')[0:6] csv_file_dest= fn #"test"+dt+".tsv" outputFile=open(csv_file_dest,'w', newline='',encoding='utf-8') output=csv.writer(outputFile,delimiter='\t',quotechar='"') #sql="select * from AC01_TB55" curs.execute(sql) cols=[] for col in curs.description: cols.append(col[0]) output.writerow(cols) for row_data in curs: output.writerow(row_data) outputFile.close()四、生成汇总报告：这个比较费劲
汉字文件名折腾了我几小时才搞定
dir_file_path.encode('utf-8')
len(open(filename,'rb').readlines())
结果集判断一下文件解串，不然老提示越界，其实是个非校验结果文件影响到了
if len(datas)<2:continue
# -*- coding: utf-8 -*- # coding: utf-8 import chardet import os import openpyxl import datetime def get_file_path(root_path,file_list,dir_list): #获取该目录下所有的文件名称和目录名称 dir_or_files = os.listdir(root_path) for dir_file in dir_or_files: #获取目录或者文件的路径 dir_file_path = os.path.join(root_path,dir_file) #判断该路径为文件还是路径 if os.path.isdir(dir_file_path): dir_list.append(dir_file_path) #递归获取所有文件和目录的路径 get_file_path(dir_file_path,file_list,dir_list) else: #print(dir_file_path) #print(type(dir_file_path)) #print(chardet.detect(dir_file_path.encode())) file_list.append(dir_file_path.encode('utf-8')) def linecount_1( filename): return len(open(filename,'rb').readlines()) def linecount_2( filename): count = -1 #print(filename.decode('utf-8')) for count, line in enumerate(open(filename)): pass return count+1 if __name__ == "__main__": #根目录路径 os.chdir(r'F:\内蒙\py') root_path = r"F:\内蒙\py\行业check\主数据" #用来存放所有的文件路径 file_list = [] #用来存放所有的目录路径 dir_list = [] get_file_path(root_path,file_list,dir_list) #print(file_list) #print(dir_list) dt=str(datetime.datetime.now()).replace(':','').replace('-','').replace(' ','')[0:14] xlsname="report"+dt+'.xlsx' os.system("copy report.xlsx "+xlsname) xls=openpyxl.Workbook() wb = openpyxl.load_workbook(xlsname) sheet = wb.get_sheet_by_name('report') row_data=5 for fn in file_list: fnname=str(fn,'utf-8') fnname=fnname fnlines=linecount_1(str(fn,'utf-8')) pos=len(fnname)-fnname[::-1].find(root_path[::-1])+1 datas1=fnname[pos:] datas=datas1.split('\\') if len(datas)<2:continue print(datas) #print(len(datas)) print(datas[0]) #print(type(datas[0])) print(datas[1]) #print(datas[1]) sheet.cell(row=row_data, column=2, value=datas[0]) sheet.cell(row=row_data, column=3, value=datas[1][::-1][4:][::-1]) sheet.cell(row=row_data, column=4, value=datas[1]) sheet.cell(row=row_data, column=5, value=fnlines) row_data=row_data+1 wb.save(xlsname) print("保存成功")
