流程图:
流程说明:
1:后台定时执行,如每天20:30自动执行任务。
点击查看代码
def task_back():'''每天定时后台执行任务'''#scheduler = BlockingScheduler()#阻塞, 会一直阻塞当前线程global schedulerscheduler = BackgroundScheduler(timezone=pytz.timezone('Asia/Shanghai'), job_defaults={'coalesce':True, 'misfire_grace_time':60})#后台 60s#args=('back',)传递位置参数。 kwargs = {'type':'back'}字典传递关键字参数scheduler.add_job(collect_upload,'cron',args=('back',),hour=param['hour'],minute=param['minute'])#,day_of_week='1-6'#scheduler.add_job(collect_upload,'interval',seconds=10)#周期执行#print('task_back start')msg = f"定时收集上传任务将在每天{param['hour']}:{param['minute']}执行..."window.after(0,updata_gui,msg)logger_helper.logger.info(msg)scheduler.start()# print('task_back started')# time.sleep(60)#确保程序不要终止# scheduler.shutdown()# print('task_back finish')def task_back_finish():'''关闭任务执行器'''scheduler.remove_all_jobs()scheduler.shutdown(wait=False)
2:界面上手动选择日期并执行,可方便随时查看数据。
点击查看代码
excuse_button = tk.Button(window, text='执行', width=10, height=1, command=excuse)
excuse_button.grid(row=0, column=1, sticky=tk.E)
def excuse():global select_daydatetime_day = date_entry.get_date()select_day = datetime(datetime_day.year,datetime_day.month,datetime_day.day)#collect_upload('manual')thread = threading.Thread(target=collect_upload,args=('manual',))#lambda e:self.task_loop()thread.daemon=Truethread.start()#不是监听循环型
3:任务表记录所有工站的执行方式(FTP,WEB),IP,文件路径,文件命名方式。(在设备电脑的IIS中优先设置FTP站点,当此站点不通时再设置WEB站点,并挂载只读虚拟目录)。
点击查看代码
global csvParam
csvParam = project_param.ParamAccess.getCsvData('collect_rawdata_ip.csv')#获得所有工站的通讯方式数据字典
def get_all_remote_file():'''根据collect_rawdata_ip.csv表格中的ftp服务器列表,下载文件到本地'''for item in csvParam:try:if item['method'] == 'ftp':local_tcm_file = get_remote_file(item['station_id'],item['ip'],item['user'],item['password'],item['remote_folder'])get_ftp_fail_picture(item['station_id'],item['ip'],item['user'],item['password'],item['remote_folder'],local_tcm_file)#Fail图片是根据工站的TCM总表获取的elif item['method'] == 'web':local_tcm_file = get_web_file(item['station_id'],item['ip'],item['user'],item['password'],item['remote_folder'])get_web_fail_picture(item['station_id'],item['ip'],item['user'],item['password'],item['remote_folder'],local_tcm_file)except Exception as ex:#空,或一般异常,特定异常msg = f"get remote file fail: {ex}"window.after(0,updata_gui,msg)logger_helper.logger.error(msg, exc_info=True)else:passfinally:pass # type: ignore#上个注释可以忽略编译类型检测,当定义None时;运行时异常 ftp为没有quit方法;try中的局部遍历不能作用到finally块中
4:通过最大扭矩过滤所有吹钉数据,根据sn_config过滤所有测试数据。
5:下载图片分两种方式:直接从共享文件夹中下载指定图片;从共享文件夹中下载指定压缩包,再从压缩包中解压出指定图片。
6:将合并并写入数据库的tcm_rawdata报表。
点击查看代码
local_db = f'Device{os.sep}Config{os.sep}collect_tcm.db'
def insert_tcm_rawdata(local_csv_path):'''读取合并的TCM表,插入到数据库tcm_rawdata表中,插入前先清空'''try:#local_csv_path = r"C:\lab\NP IO Monitor\projects\python_work\tool_mac 250510\Device\Log\TCM\20250805\Sumtable_20250801.csv"file_exist = os.path.exists(local_csv_path)if not file_exist:msg = f"{local_csv_path} do not exist, insert_tcm_rawdata fail"module_logger.info(msg)returnlst = collect_rawdata_frame.getCsvData(local_csv_path)del lst[0]#delete titlefor item in lst:if item[-1] == '':del item[-1] #delete ''#tmp = 1/0 此异常在此函数中捕捉,不会影响其余函数运行(可能被跳过)。就近捕捉原则。除了那些特定功能函数异常可以冒泡。#连接到SQlite数据库,如果不存在,会自动创建with sqlite3.connect(local_db) as conn:#, conn.cursor() as cursor: #从左到右依次进入,从右到左依次退出各个上下文管理器。也可以嵌套 #'sqlite3.Cursor' object does not support the context manager protocol cursor = conn.cursor()cursor.execute('delete from tcm_rawdata') #clear table#插入#cursor.executemany('insert into tcm_rawdata values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', lst)#某些数据插入失败,会导致整个列表都插入失败for item in lst:try:cursor.execute('insert into tcm_rawdata values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', item)except Exception as ex:#空,或一般异常,特定异常module_logger.error(f'insert a row to rawdata of db error',exc_info=True)#插入一条数据失败。再插入下一条。不会因为一个工站的TCM总表格式不符,导致生成不了excel表格。#提交事务 对于增加、修改、删除操作,使用事务,如果相应操作失败,可以使用rollback方法回滚到操作之前的状态conn.commit()cursor.close()module_logger.info(f'insert {local_csv_path} to tcm_rawdata of db success')except Exception as ex:#空,或一般异常,特定异常module_logger.error(f'occur exception: {ex}', exc_info=True)
7:根据by screw的tcm_rawdata报表,在数据库中生成by_unit的rawdata报表(与excel表中一样的格式)。
点击查看代码
def insert_rawdata():'''根据tcm_rawdata在数据库的rawdata表中插入数据, 并by station_id、by sn 去重,有一个tcm fail就是fail'''try:with sqlite3.connect(local_db) as conn:#conn = sqlite3.connect(local_db)cursor = conn.cursor()cursor.execute('delete from rawdata') #clear tablestation_id_lst = get_station_id_lst()cursor.execute(f'select station_id,[HSG Vendor],[Screw Vendor] from station_info')station_infos = cursor.fetchall() global fr_listfr_list= []#记录所有工站的不良率for station_id in station_id_lst:cursor.execute(f'select time, station_id, sn_config, [Pass/Fail], SN from tcm_rawdata where station_id == "{station_id}"')# where [sn_config] != NULL or [sn_config] !="" 在过滤设备TCM总表时,已经过滤了rawdata = cursor.fetchall() #获取查询结果集中的下一条记录,元组;fetchmany(size)获取指定数量的记录,元组列表;fetchall()获取结果集中的所有记录#获取所有是去重sncursor.execute(f'select distinct SN from tcm_rawdata where station_id == "{station_id}"') #by station_idall_sn = []for item in cursor.fetchall():all_sn.append(item[0])#获取所有去重fail sncursor.execute(f'select distinct SN from tcm_rawdata where [Pass/Fail]=="Fail" and station_id == "{station_id}"')fail_sn = []for item in cursor.fetchall():fail_sn.append(item[0])fr = len(fail_sn)/len(all_sn)fr_str = f'{len(fail_sn)}F/{len(all_sn)}T'fr_dic={}fr_dic['station_id'] = station_idfr_dic['FR'] = f'{fr*100:.2f}%'fr_dic['FR_STR'] = fr_strfr_list.append(fr_dic)#再判断fail_sn中重打pass的情况#找到此station_id打的最大螺丝位置数。螺丝没打完TCM fail。螺丝打完了,遍历此sn每个位置的结果,将结果组合成字符串,若此组合字符串没有包含pass,此螺丝true fail。跳到判断下一个螺丝 #by sn记录rawdata,方便统计yield by sn#修改下station_info的spec列的内容#获得tcm_rawdata的所有Pass SN和Fail SN。若item['SN']在哪类,result就是哪个。遍历一个item后记录其sn,若下一次遍历item还是上次sn,就跳过。previous_sn = []for item in rawdata:if item[4] in previous_sn:#by sn去重,同一个sn只遍历一遍continueprevious_sn.append(item[4])station = item[1][:-6]#去掉-L-01date_str = item[0].split(' ')[0]if item[4] in fail_sn:result = 'TCMFAIL'else:result = "PASS"for station_info in station_infos:#获取[HSG Vendor],[Screw Vendor]if station_info[0] == item[1]:select_staion_info = station_infobreakcursor.execute(f'insert into Rawdata (SN, Config, Station, [Station ID], EndTime,[OK(1)/NG(0)], Date,[HSG Vendor],[Screw Vendor]) values ("{item[4]}", "{item[2]}", "{station}", "{item[1]}", "{item[0]}", "{result}", "{date_str}","{select_staion_info[1]}","{select_staion_info[2]}" )') #关闭游标cursor.close()#提交事务 对于增加、修改、删除操作,使用事务,如果相应操作失败,可以使用rollback方法回滚到操作之前的状态conn.commit()module_logger.info(f'insert data from tcm_rawdata and station_into to rawdata of db success')except Exception as ex:#空,或一般异常,特定异常module_logger.error(f'occur exception: {ex}', exc_info=True)
8:根据数据库的station_info报表和tcm_rawdata报表,在数据库中生成与excel中一样格式的TCM_Failure_Breakdown报表
点击查看代码
def insert_TCM_Failure_Breakdown():'''查询tcm_rawdata的fail螺丝,找到fail螺丝的fail项次一起插入'''try:with sqlite3.connect(local_db) as conn:conn.row_factory = sqlite3.Row #返回字典形式->字典列表cursor = conn.cursor()cursor.execute('select * from tcm_rawdata where [Pass/Fail] == "Fail"')rows = cursor.fetchall() #获取查询结果集中的下一条记录,元组;fetchmany(size)获取指定数量的记录,元组列表;fetchall()获取结果集中的所有记录row_list = []#装载查询的字典列表for row in rows:row_list.append(dict(row))fail_mode =[]#用于装fail螺丝的fail项次for i in range(len(row_list)):fail_mode.append('')for index, item in enumerate(row_list):fail_info =''for i, (key,value) in enumerate(item.items()): #for key,value in item.items(): if i>8 and i<26: #TCM总表发生变化,这个要跟着变 if value == 'Fail':fail_info += key+os.linesep#将分号换成换行if len(fail_info)>0:fail_info.rstrip() #参数[chars],若省略,默认移除l首r尾空白符(空格、换行、制表等) fail_mode[index] = fail_info #fail_info=''; fail_info[:-1] is ''; fail_info[0] errorpics=[]#顺序存储图片路径for index, item in enumerate(row_list): filename = item['filename']if 'HA230' in item['station_id'] or 'HA350' in item['station_id'] or 'FA150' in item['station_id']:picture_name = filename[:-4] +'_Fail.png'else:picture_name = filename[:-3]+'png'picture_path = f'{collect_rawdata_frame.local_folder}/picture/{picture_name}'pics.append(picture_path)cursor.execute('delete from [TCM Failure Breakdown]') #clear tabledatestr = datetime.strftime(collect_rawdata_frame.select_day,'%Y-%m-%d')for index, item in enumerate(row_list): for dic in fr_list:if dic['station_id'] ==item['station_id']:fr_dic = dicbreakxyd_result = 'OK' if item['xyd_Pass/Fail']=='Pass' else 'NG'tcm_result = 'OK' if item['Pass/Fail']=='Pass' else 'NG'cursor.execute(f'insert into [TCM Failure Breakdown] (Build, Config,Date, Station, [Failure Mode],[Fail SN + Fail location], FR, FR_STR,[XYD OK/NG],[TCM OK/NG],Pic) values ("{collect_rawdata_frame.param['build']}","{item['sn_config']}","{datestr}","{item['station_id']}","{fail_mode[index]}","{item['SN']}{os.linesep}#{item['screw position']}", "{fr_dic['FR']}","{fr_dic['FR_STR']}", "{xyd_result}","{tcm_result}","{pics[index]}")')#关闭游标cursor.close()#提交事务 对于增加、修改、删除操作,使用事务,如果相应操作失败,可以使用rollback方法回滚到操作之前的状态conn.commit()module_logger.info(f'insert TCM_Failure_Breakdown of db success')#return fail_modeexcept Exception as ex:#空,或一般异常,特定异常module_logger.error(f'occur exception: {ex}', exc_info=True)
9:导出数据库的TCM_Failure_Breakdown报表和Rawdata报表到excel文件。
点击查看代码
def export_excel():'''从数据库中将[TCM Failure Breakdown]到处到excel中'''with sqlite3.connect(local_db) as conn:query = 'select * from [TCM Failure Breakdown]'df = pd.read_sql_query(query, conn)df.to_excel(excel_path,index=False,engine='openpyxl')module_logger.info(f'export TCM_Failure_Breakdown of db to excel success')def export_rawdata_to_excel():#export_to_new_sheet'''导出数据库rawdata表中的数据到excel的rawdata工作表中'''with sqlite3.connect(local_db) as conn:cursor = conn.cursor()cursor.execute(f'select * from Rawdata')rows = cursor.fetchall()column_names = [desc[0] for desc in cursor.description]workbook = load_workbook(excel_path)#加载现有excelnew_sheet = workbook.create_sheet(title="Rawdata")#创建新工作表new_sheet.append(column_names)#写入列名for row in rows:new_sheet.append(row)#写入数据行workbook.save(excel_path)cursor.close()module_logger.info(f'export rawdata of db to excel success')
10:插入FAIL图片到excel的TCM_Failure_Breakdown报表的指定单元格。
点击查看代码
def insert_image_to_excel(file_path,sheet_name,cell_address,image_paths:list):'''创建工作簿或加载现有,[获取单元格内容],清空单元格内容,插入单元格图片。遍历的'''try:#wb = Workbook()wb = load_workbook(file_path)ws = wb.activews.title = sheet_name # type: ignoreimage_num = len(image_paths)cell_address_list = [] ws.column_dimensions[cell_address[0]].width = 20 # type: ignorefor i in range(2,2+image_num):cell_address_list.append(cell_address[0]+str(i))#['Q2','Q3',,ws.row_dimensions[i].height=80 # type: ignorefor index, image_path in enumerate(image_paths):try:if os.path.exists(image_path[0]):ws[cell_address_list[index]]=None # type: ignore #cleer cell content#插入图片到指定单元格img= Image(image_path[0])img.width=int(img.width/20)#原始图片尺寸3200img.height=int(img.height/20)#原始图片尺寸1800#从9往后的单元格尺寸没有变化?#ws.row_dimensions[int(cell_address_list[index][1])].height=80 # type: ignore#ws.column_dimensions[cell_address_list[index][0]].width = 20 # type: ignorews.add_image(img, cell_address_list[index]) # type: ignoremsg = f'{image_path[0]},插入图片到表格成功'module_logger.info(msg)else:msg = f'{image_path[0]} 不存在,插入图片到表格失败'module_logger.info(msg)except Exception as e:msg = f'{image_path[0]},插入图片到表格失败: {e}'module_logger.error(msg, exc_info=True)#保存excel文件wb.save(file_path)except Exception as e:msg = f'插入图片到表格失败: {e}'module_logger.error(msg, exc_info=True)
11:通过指定SN上传合并表,所有FAIL图片,和生成的excel表。若压缩文件超过80M,就分两个文件上传。
点击查看代码
def upload(file_path, sn):'''上传文件到MES'''file_exist = os.path.exists(file_path)if not file_exist:msg = f"{file_path} do not exist, upload fail"window.after(0,updata_gui,msg)logger_helper.logger.info(msg)returnurl = f'{param['url']}?p=fgsn&c=ADD_PICTURE&test_station_name={param['test_station_name']}&station_id={param['station_id']}&sn={sn}'msg = f"request MES, up {file_path}: {url}"window.after(0,updata_gui,msg)logger_helper.logger.info(msg)mes_return = http_mes.HttpMes.add_picture(url, file_path)if mes_return[0]==0:msg = f"upload {file_path} success; {mes_return[1]}"window.after(0,updata_gui,msg)logger_helper.logger.info(msg)else:msg = f"upload {file_path} fail; {mes_return[1]}"window.after(0,updata_gui,msg)logger_helper.logger.info(msg)