定时收集TCM数据并生成Excel报表并上传
流程图：
流程说明:
1：后台定时执行，如每天20：30自动执行任务。
点击查看代码
def task_back():
'''每天定时后台执行任务'''
#scheduler = BlockingScheduler()#阻塞, 会一直阻塞当前线程
global scheduler
scheduler = 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_day
datetime_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=True
thread.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:
pass
finally:
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)
return
lst = collect_rawdata_frame.getCsvData(local_csv_path)
del lst[0]#delete title
for 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 table
station_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_list
fr_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()获取结果集中的所有记录
#获取所有是去重sn
cursor.execute(f'select distinct SN from tcm_rawdata where station_id == "{station_id}"') #by station_id
all_sn = []
for item in cursor.fetchall():
all_sn.append(item[0])
#获取所有去重fail sn
cursor.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_id
fr_dic['FR'] = f'{fr*100:.2f}%'
fr_dic['FR_STR'] = fr_str
fr_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只遍历一遍
continue
previous_sn.append(item[4])
station = item[1][:-6]#去掉-L-01
date_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_info
break
cursor.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] error
pics=[]#顺序存储图片路径
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 table
datestr = 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 = dic
break
xyd_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_mode
except 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)#加载现有excel
new_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.active
ws.title = sheet_name # type: ignore
image_num = len(image_paths)
cell_address_list = []
ws.column_dimensions[cell_address[0]].width = 20 # type: ignore
for 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: ignore
for 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)#原始图片尺寸3200
img.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: ignore
ws.add_image(img, cell_address_list[index]) # type: ignore
msg = 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)
return
url = 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)
浙公网安备 33010602011771号