knowledge/tests/read_write_excel.py

import json
from socket import timeout

import pandas as pd
import random

import requests

from db.session import get_db
from service.kg_edge_service import KGEdgeService
from service.kg_node_service import KGNodeService
from tests.DeepseekUtil import chat
from utils.agent import call_chat_api,get_conversation_id

# 读取指定路径的xlsx文件
def read_write_excel(file_path):
    nodeService = KGNodeService(next(get_db()))
    edgeService = KGEdgeService(next(get_db()))
    app_id = "256fd853-60b0-4357-b11b-8114b4e90ae0"
    df = pd.read_excel(file_path)
    for index, row in df.iterrows():
        try:
            disease_name = str(row[0])
            node = nodeService.get_node_by_name_category(disease_name, '疾病')
            if node is None:
                df.at[index, '主诉和现病史'] = '未找到该疾病'
                continue
            edges = edgeService.get_edges_by_nodes(src_id=node['id'], category='疾病相关症状')
            if edges is None or len(edges) == 0:
                df.at[index, '疾病相关症状'] = '未找到疾病相关症状'
                continue
            else:
                symptoms = [edge['dest_node']['name'] for edge in edges]
                df.at[index, '疾病相关症状'] = ' '.join(symptoms)
  
            question = f'你是一个全科医生，帮忙按照开病历的格式，生成“{disease_name}”的主诉和现病史。\n要求：\n1、主诉和现病史需要包含“{disease_name}”的主要症状。\n2、内容简短，不要有除了“主诉”和“现病史”以外多余的信息。\n以json的格式输出，格式如下：\n{{\"主诉\": \"面部红色丘疹、脓疱伴瘙痒3天\", \"现病史\": \"患者3天前面部出现红色丘疹，逐渐发展为脓疱，伴轻度瘙痒，无发热。自行外用“红霉素软膏”效果不佳，脓疱增多，遂就诊。既往体健，无类似病史，无药物过敏史。\"}}'
            answer = chat(question).strip("\n```json")
            json_data = json.loads(answer)
            df.at[index, '主诉和现病史'] = answer
            url = f'http://173.18.12.205:8001/knowledge/disease/recommend?chief={json_data['主诉']}&present_illness={json_data['现病史']}'
            response = requests.request("GET", url,timeout=60)
            data = json.loads(response.text)['data']
            df.at[index, '主诉和现病史中的症状'] = data['症状']
        
            if any(disease_name in d['name'] for d in data['可能诊断']):
                df.at[index, '疾病是否命中'] = '是'
            else:
                df.at[index, '疾病是否命中'] = '否'
            df.at[index, '可能诊断'] = data['可能诊断']
        except Exception as e:
            print(f"处理Excel文件时发生错误: {e}")
    # 保存修改后的文件
    df.to_excel(file_path, index=False)


   

if __name__ == "__main__":
    # 指定xlsx文件路径
    file_path = "C:\\Users\\17664\\Desktop\\疾病列表.xlsx"
    read_write_excel(file_path)