项目背景:帮一个医疗系统处理本地mysql数据,整理成对应表格显示,图片分类到对应文件夹
import time
import datetime
import pandas as pd
import requests
from tqdm import tqdm
from utils import DBHelper
from utils.DataExChange import DataExChange
from utils.EmailHelper import EmailHelper
from utils.ExcelType import ExcelType
import shutil
import os
import zipfile
def zip_folder(folder_path, output_path):
with zipfile.ZipFile(output_path, "w", zipfile.ZIP_DEFLATED) as zipf:
for root, dirs, files in os.walk(folder_path):
for file in files:
file_path = os.path.join(root, file)
arcname = os.path.relpath(file_path, folder_path)
zipf.write(file_path, arcname)
print('压缩成功')
def makedirs_file(path):
if not os.path.exists(path):
os.makedirs(path)
def copy_file(target, original):
makedirs_file(target)
# 遍历源文件夹中的所有文件和子文件夹
for root, dirs, files in os.walk(original):
for file in files:
# 检查文件是否为图片文件(这里假设只复制扩展名为 .jpg, .png, .jpeg 的文件)
if file.lower().endswith(('.jpg', '.png', '.jpeg')):
source_path = os.path.join(root, file)
destination_path = os.path.join(target, file)
# 复制文件到目标文件夹
shutil.copy2(source_path, destination_path)
# shutil.copy2(original, target)
def export_image(data, picturePath):
for info in tqdm(data, desc='正在导出图片进度', unit='file'):
original = picturePath + info[5] + '\\' + info[5]
dt = datetime.datetime.strptime(info[4], "%Y/%m/%d %H:%M:%S")
formatted_dt = dt.strftime("%Y/%m/%d %H:%M:%S").replace("/", "").replace(' ', '').replace(':', '')
copy_file(rootPath + info[6] + '_' + formatted_dt, original)
time.sleep(0.01)
def export_excel(data, table_name, tab_header):
df = pd.DataFrame(data, columns=tab_header)
df.to_excel(rootPath + table_name, index=False)
# print(data)
print(f"{table_name}表导出成功")
userList = []
# Press the green button in the gutter to run the script.
def query_all_userinfo():
db = DBHelper.Connector().get_connection()
print('开始查询个人信息表')
cursor = db.cursor()
sql = "select a.user_id,a.user_No,a.user_name,a.user_gener,a.user_birth,a.user_mobile,a.user_regtime,a.user_address,a.user_nation,a.user_bloodtype,b.check_id from user_tbl a left join checkresult_tbl b ON b.user_id=a.user_id where b.check_id is not null"
pulseList = []
faceList = []
tongueList = []
identifyList = []
try:
cursor.execute(sql)
result = cursor.fetchall()
columns = [column[0] for column in cursor.description]
for row in tqdm(result, desc='正在处理数据进度', unit='item'):
# 用户信息
user_info = DataExChange.userInfo(row, columns)
userList.append(user_info)
checkId = row[columns.index('check_id')]
# 脉象信息
pulse_info = DataExChange.pulseInfo(row, columns, checkId)
pulseList.append(pulse_info)
if checkId is not None:
faceResult = DBHelper.query_docface(checkId)
colorResult = DBHelper.query_tfcolor(checkId)
else:
faceResult = None
colorResult = None
# 面色
face_info = DataExChange.faceInfo(row, columns, faceResult, colorResult)
faceList.append(face_info)
# 舌像
tongue_info = DataExChange.tongueInfo(row, columns, checkId, colorResult)
tongueList.append(tongue_info)
# # 四诊合参体质辨识
# identify_info = ExcelType.TAB_HEAD_IDENTIFY.copy()
# identify_info['用户编号'] = row[1]
# identify_info['姓名'] = row[2]
# identify_info['性别'] = '男' if row[3] == 0 else '女'
# identify_info['出生日期'] = row[4].strftime("%Y-%m-%d")
# identify_info['联系电话'] = row[5]
# identifyList.append(identify_info)
except Exception as ex:
print("数据查询异常 {}".format(ex))
db.rollback() # 回滚
db.close() # 关闭数据库连接
print("开始导表....")
if len(userList) > 0:
export_excel(userList, '个人完整信息.xlsx', ExcelType.TAB_HEAD_USER_INFO)
if len(pulseList) > 0:
export_excel(pulseList, '脉象采集.xlsx', ExcelType.TAB_HEAD_PULSE_CONDITION)
if len(faceList) > 0:
export_excel(faceList, '面色采集.xlsx', ExcelType.TAB_HEAD_FACE_COLOR)
if len(tongueList) > 0:
export_excel(tongueList, '舌像采集.xlsx', ExcelType.TAB_HEAD_TONGUE)
#
# if len(identifyList) > 0:
# export_excel(identifyList, '四诊合参体质辨识.xlsx')
# time.sleep(0.2)
# export_image(userList)
# 根据part_id查询色值
def get_color_by_partId(list, partId):
for item in list:
if (item[0] == partId):
return item
def print_splash(width, height, title):
horizontal_border = '+' + '-' * (width - 2) + '+'
empty_line = '|' + ' ' * (width - 2) + '|'
print(horizontal_border)
for i in range(height - 2):
if i == (height - 3) // 2:
center_title = f'{title.center(width - 2)}'
print(f'|{center_title}|')
else:
print(empty_line)
print(horizontal_border)
def print_step2():
data = str(input("请输入原始图片存放目录(如:E:\SCDATA\SCTCMDATA):"))
try:
if data.lower().__contains__('sctcmdata'):
picturePath = data + '\\'
export_image(userList, picturePath)
step = 2
else:
print("输入路径有误,请重新输入")
except Exception as ex:
print("输入有误,请重新输入 {}".format(ex))
finally:
print("------")
if __name__ == '__main__':
step = 0
while True:
print_splash(80, 5, "欢迎来到中医四诊信息平台!\n Step 1:导出表格\n Step 2:导出图片")
tip = str(input("确定要导出所有数据吗Y/N:"))
if tip.lower() == 'n':
print("退出")
break
elif tip.lower() == 'y':
exportPath = str(input("请输入表格存放目录(如:D:\四诊导出数据):"))
fileName = datetime.datetime.now().strftime("%Y-%m-%d")
rootPath = exportPath + '\\' + fileName + '\\'
data = str(input("请输入原始图片存放目录(如:E:\SCDATA\SCTCMDATA):"))
picturePath = data + '\\'
print("导出路径:" + rootPath)
# rootPath = 'C:\\四诊导出数据\\' + datetime.datetime.now().strftime("%Y-%m-%d") + '\\'
makedirs_file(rootPath)
query_all_userinfo()
export_image(userList, picturePath)
zip_folder(rootPath, exportPath + '\\' + fileName + ".zip")
# EmailHelper.send(exportPath + '\\' + fileName+".zip", fileName+".zip")
break
else:
print("请输入Y/N")
# 打包exe 命令
```
pip install pyinstaller
```
```
pyinstaller --onefile export_tools.py
```
```
pyinstaller --onefile --add-data "D:\Program Files\python;." export_tools.py
双击exe文件
整理好的数据