# encoding: utf-8 import os import random import re import time from flask import Blueprint, json import requests from py2neo import Graph, Node, Relationship, NodeMatcher from clickhouse_driver import Client from app.model.base import dbconn, dbconn2, graph from bs4 import BeautifulSoup # from database.neo import graph bp = Blueprint('importdata', __name__, url_prefix='/importdata') @bp.route('/data_xd3') def data_xd3(): category3 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3' ) for val3 in category3: data1 = dbconn.query( sql='SELECT id, `name`, children FROM xd_cate where `name` = \'' + val3['name'] + '\'' ) if len(data1) == 0: continue data2 = { 'cate_id': int(val3['id']), 'xd_id': int(data1[0]['id']) } dbconn.insert(table='illness_xd_cate', data=data2) return "1" @bp.route('/data_symptoms2') def data_symptoms2(): category3 = dbconn.query( sql='SELECT id, `name`, lab_category_id FROM lab_clinical where id > 0 ' ) for val3 in category3: sid = get_symptoms(val3['name']) data1 = dbconn.query( sql='SELECT id, `cate_id`,symptoms_id FROM illness_symptoms where symptoms_id = ' + str( sid) + ' AND cate_id = ' + str(val3['lab_category_id']) ) if len(data1) > 0: continue data2 = { 'cate_id': val3['lab_category_id'], 'symptoms_id': sid } dbconn.insert(table='illness_symptoms', data=data2) # data1 = dbconn.query( # sql='SELECT id, `cate_id`,symptoms_id FROM illness_symptoms where symptoms_id = ' + sid + ' AND cate_id = ' # ) ##### # if len(name) > 1: # print(val3['id']) # dbconn.delete(table='symptoms', condition={'id': val3['id']}) # dbconn.delete(table='illness_symptoms', condition={'symptoms_id': val3['id']}) #### # data1 = dbconn.query( # sql='SELECT id, `cate_id`,symptoms_id FROM illness_symptoms where symptoms_id = ' + str(val3['id']) # ) # for val_n in name: # sid = get_symptoms(val_n) # # for val1 in data1: # data2 = { # 'cate_id': val1['cate_id'], # 'symptoms_id': sid # } # dbconn.insert(table='illness_symptoms', data=data2) return "1" @bp.route('/data_ch') def data_ch(): old_id = '0' i = 1 size = 100 while i <= 10: page = i * size sql_str = 'SELECT rm.id, rm.orgin_id, rp.chief_complaint, rp.treatment, rp.doctors_advice, rms.symptom, rms.subject, re.diagnosis_summary' \ ' FROM ch_report_main as rm ' \ ' left join ch_report_evalute as re on rm.orgin_id=re.report_main_orgin_id ' \ ' left join ch_report_patient as rp on rm.orgin_id=rp.report_main_orgin_id ' \ ' left join ch_report_customer as rc on rm.orgin_id=rc.report_main_orgin_id' \ ' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \ ' where rm.valid_status = 1 and rm.visit_status = 1 and id >' + old_id + \ ' limit ' + str(page) + ', ' + str(size) rows = dbconn2.query( sql=sql_str ) i += 1 for val1 in rows: data1 = { 'name': val1['diagnosis_summary'], 'chief': val1['chief_complaint'], 'doctors_advice': val1['doctors_advice'], 'doctors_advice_data': '', 'subject': val1['subject'], 'treatment': val1['treatment'], 'symptom': val1['symptom'], 'old_id': val1['id'], 'illness_data': '' } sql_str2 = "SELECT `type`, keyword FROM report_keyword where report_main_orgin_id = '" + val1[ 'orgin_id'] + "'" rows2 = dbconn2.query( sql=sql_str2 ) # 1 主诉 2 诊断报告 3 影像报告 4 分析结果 5 病情诊断 6 治疗方案 7 医嘱 for val2 in rows2: if val2['type'] == 7: data1['doctors_advice_data'] += val2['keyword'] + ',' if val2['type'] == 5: data1['illness_data'] += val2['keyword'] + ',' # 主诉-病症 if val2['type'] == 1: data2 = { 'illness_id': val2['id'], 'name': val2['keyword'] } dbconn.insert(table='ch_symptoms', data=data2) # 主诉-病症 if val2['type'] == 6: data2 = { 'illness_id': val2['id'], 'name': val2['keyword'] } dbconn.insert(table='ch_treatment', data=data2) dbconn.insert(table='ch_illness', data=data1) return '1' @bp.route('/rep_xd_cate') def rep_xd_cate(): category3 = dbconn.query( sql='SELECT id, `name` FROM xd_cate where id > 0 and is_set = 0' ) for val3 in category3: time.sleep(random.randint(1, 5)) url = 'https://his.betacat.co/api/pet/v1/medical/book/query' # url = 'https://medical.bk-pet.cn/common/getRedisData' headers = { "Authorization": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiYmIyMzY2ZDAtZmQyOS0xMWVlLWE1NGItYzdkNzU2NWY0MzI2IiwiY3JlYXRlZEF0IjoiMjAyNC0wNC0xOCAxMDozMjo0MiIsImVtcGxveWVlTm8iOiIyMzI1NTc5NTIxNzgzMjc1NTIiLCJleHBpcmVkQXQiOiIyMDI0LTA1LTE4IDEwOjMyOjQyIiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI0LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5biC5Zy6LeadjuiSmeiSmSIsInBlcm1pc3Npb25MaXN0IjpbXSwicGhvbmUiOiIxMzc3Njg1MDIwMSIsInNob3BObyI6IjIwMjMwMjA2MTUxOTA2MTY0MDAxMDAwMSIsInNob3BOb3MiOlsiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIl0sInNob3BSb2xlSURzIjpbNzAsNzBdLCJzaG9wUm9sZUxpc3QiOlt7IklEIjo3MCwiVGl0bGUiOiLnrqHnkIblkZgifV0sInNob3BUaXRsZSI6Iui_heW-t-WuouacjSJ9.UCVLZnjOOvoeRn6H6KV2K6sxbzZNQUwPCoM1OtFaYUI", "Cookie": "loginToken=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiYmIyMzY2ZDAtZmQyOS0xMWVlLWE1NGItYzdkNzU2NWY0MzI2IiwiY3JlYXRlZEF0IjoiMjAyNC0wNC0xOCAxMDozMjo0MiIsImVtcGxveWVlTm8iOiIyMzI1NTc5NTIxNzgzMjc1NTIiLCJleHBpcmVkQXQiOiIyMDI0LTA1LTE4IDEwOjMyOjQyIiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI0LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5biC5Zy6LeadjuiSmeiSmSIsInBlcm1pc3Npb25MaXN0IjpbXSwicGhvbmUiOiIxMzc3Njg1MDIwMSIsInNob3BObyI6IjIwMjMwMjA2MTUxOTA2MTY0MDAxMDAwMSIsInNob3BOb3MiOlsiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIl0sInNob3BSb2xlSURzIjpbNzAsNzBdLCJzaG9wUm9sZUxpc3QiOlt7IklEIjo3MCwiVGl0bGUiOiLnrqHnkIblkZgifV0sInNob3BUaXRsZSI6Iui_heW-t-WuouacjSJ9.UCVLZnjOOvoeRn6H6KV2K6sxbzZNQUwPCoM1OtFaYUI", "content-type": "application/json", "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:123.0) Gecko/20100101 Firefox/123.0" } params = {"list": [val3['name']]} result = requests.request('POST', url=url, headers=headers, json=params) rows = json.loads(result.text) cond = {'id': val3['id']} data_end = {'is_set': 1} dbconn.update(table='xd_cate', data=data_end, condition=cond) if len(rows['data']['list']) == 0: continue for row in rows['data']['list'][0]['titles']: data2 = { 'xd_cate_id': val3['id'], 'title': row['title'], 'content': row['content'] } dbconn.insert(table='xd_cate_info', data=data2) return '1' @bp.route('/data_xd_cate', methods=['POST', 'GET']) def data_xd_cate(): cate_data = json.loads('', encoding='utf-8') data_set_cate(cate_data['data']['list'], 0, 1) return '2' def data_set_cate(child, pid, children): for val in child: data3 = { 'pid': pid, 'name': val['title'], 'children': children } cateid = dbconn.insert(table='xd_cate', data=data3) if len(child) == 0: continue else: data_set_cate(val['child'], cateid, children + 1) @bp.route('/data_keyword_clinical', methods=['POST', 'GET']) def data_keyword_clinical(): sql_str = 'SELECT `name`, keyword FROM symptom where children = 3' rows = dbconn.query( sql=sql_str ) for val in rows: keyword = val['keyword'].split('|') for val2 in keyword: data3 = { 'keyword': val2, 'clinical': val['name'] } dbconn.insert(table='keyword_clinical', data=data3) return '2' @bp.route('/neo_treatment_medicinal') def neo_treatment_medicinal(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) sql_str = 'SELECT tm.medicinal_id, tm.treatment_id, me.name ' \ ' FROM treatment_medicinal as tm ' \ ' left join medicinal as me on tm.medicinal_id = me.id ' \ ' where tm.id > 0' rows = dbconn.query( sql=sql_str ) for val in rows: node_treatment = node_matcher.match('治疗方案', id=val['treatment_id'], type=2).first() if node_treatment is None: continue node_medicinal = node_matcher.match('药品', id=val['medicinal_id'], type=2).first() if node_medicinal is None: node_medicinal = Node('药品', id=val['medicinal_id'], name=val['name'], type=2) link3 = Relationship(node_treatment, '适用药品', node_medicinal) graph.create(link3) return '1' @bp.route('/neo_illness_treatment') def neo_illness_treatment(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) sql_str = 'SELECT il.cate_id, il.treatment_id, tr.name, tr.medicinal_data ' \ ' FROM illness_treatment as il ' \ ' left join treatment as tr on il.treatment_id = tr.id ' \ ' where il.id > 0' rows = dbconn.query( sql=sql_str ) for val in rows: node_cate3 = node_matcher.match('疾病(三)', id=val['cate_id']).first() if node_cate3 is None: continue node_symptoms = node_matcher.match('治疗方案', id=val['treatment_id'], type=2).first() if node_symptoms is None: node_symptoms = Node('治疗方案', id=val['treatment_id'], name=val['name'], medicinal_data=val['medicinal_data'], type=2) link3 = Relationship(node_cate3, '治疗', node_symptoms) graph.create(link3) return '1' @bp.route('/neo_illness_symptoms') def neo_illness_symptoms(): # graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) sql_str = 'SELECT il.cate_id, il.symptoms_id, sy.name ' \ ' FROM illness_symptoms as il ' \ ' left join symptoms as sy on il.symptoms_id = sy.id ' \ ' where il.id > 0' rows = dbconn.query( sql=sql_str ) for val in rows: node_cate3 = node_matcher.match('疾病(三)', id=val['cate_id']).first() if node_cate3 is None: continue node_symptoms = node_matcher.match('病症', id=val['symptoms_id'], type=2).first() if node_symptoms is None: node_symptoms = Node('病症', id=val['symptoms_id'], name=val['name'], type=2) link3 = Relationship(node_cate3, '病症', node_symptoms) link2 = Relationship(node_symptoms, '疾病', node_cate3) graph.create(link2) graph.create(link3) return '1' @bp.route('/data_trmed') def data_trmed(): data = dbconn.query( sql='SELECT id, cate_id, `illness_name`,drug_name FROM illness_durg where id > 0' ) for val in data: tid = get_treatment(val['illness_name'], val['drug_name']) data3 = { 'cate_id': val['cate_id'], 'treatment_id': tid } dbconn.insert(table='illness_treatment', data=data3) res = val['drug_name'].split('、') for val2 in res: mid = get_medicinal(val2) get_treatment_medicinal(tid, mid) # data2 = { # 'treatment_id': tid, # 'medicinal_id': mid # } # dbconn.insert(table='treatment_medicinal', data=data2) return '1' @bp.route('/data_symptoms') def data_symptoms(): data = dbconn.query( sql='SELECT id, cate_id, `data` FROM illness_data where id > 0' ) for val in data: res = val['data'].split('、') for val2 in res: sid = get_symptoms(val2) data2 = { 'cate_id': val['cate_id'], 'symptoms_id': sid } dbconn.insert(table='illness_symptoms', data=data2) @bp.route('/data_drugkey') def data_drugkey(): category3 = dbconn.query( sql='SELECT id, `name` FROM medicinal where id > 0' ) for val3 in category3: drug = dbconn.query( sql='SELECT id, `drug_name` FROM drug where id > 0 and drug_name like \'%' + val3['name'] + '%\'' ) if len(drug) > 0: drug_keyword = '' for val4 in drug: drug_keyword += val4['drug_name'] + ',' drug_keyword = drug_keyword.strip(',') data_end = {'drug_name': drug_keyword} cond = {'id': val3['id']} dbconn.update(table='medicinal', data=data_end, condition=cond) return '1' @bp.route('/data_keywords') def data_keywords(): category3 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3' ) for val3 in category3: # drug_stock = dbconn.query( # sql='SELECT id, useto_keyword FROM drug_stock where useto like \'%' + val3['name'] + '%\'' # ) # # if len(drug_stock) > 0: # for val4 in drug_stock: # if val4['useto_keyword'] == '': # useto_keyword = val3['name'] # else: # useto_keyword =val4['useto_keyword']+','+val3['name'] # # data_end = {'useto_keyword': useto_keyword} # cond = {'id': val4['id']} # dbconn.update(table='drug_stock', data=data_end, condition=cond) # drug_stock2 = dbconn.query( # sql='SELECT id, indication_keyword FROM drug_stock where indication like \'%' + val3['name'] + '%\'' # ) # # # print(len(drug_stock)) # if len(drug_stock2) > 0: # for val4 in drug_stock2: # if val4['indication_keyword'] == '': # indication_keyword = val3['name'] # else: # indication_keyword = val4['indication_keyword'] + ',' + val3['name'] # # data_end = {'indication_keyword': indication_keyword} # cond = {'id': val4['id']} # dbconn.update(table='drug_stock', data=data_end, condition=cond) # drug_stock = dbconn.query( # sql='SELECT id, useto_keyword FROM vp_codex where useto like \'%' + val3['name'] + '%\'' # ) # # if len(drug_stock) > 0: # for val4 in drug_stock: # if val4['useto_keyword'] == '': # useto_keyword = val3['name'] # else: # useto_keyword =val4['useto_keyword']+','+val3['name'] # # data_end = {'useto_keyword': useto_keyword} # cond = {'id': val4['id']} # dbconn.update(table='vp_codex', data=data_end, condition=cond) drug_stock = dbconn.query( sql='SELECT id, keyword FROM disease where disease_name like \'%' + val3['name'] + '%\'' ) if len(drug_stock) > 0: for val4 in drug_stock: if val4['keyword'] == '': keyword = val3['name'] else: keyword = val4['keyword'] + ',' + val3['name'] data_end = {'keyword': keyword} cond = {'id': val4['id']} dbconn.update(table='disease', data=data_end, condition=cond) return '1' @bp.route('/rep_treatment') def rep_treatment(): category3 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy, third_id FROM lab_category where id > 0 and children = 3' ) for val3 in category3: url = 'http://www.aipets.com.cn/Home/DisReportlc?id=' + str(val3['third_id']) result = requests.get(url=url) soup = BeautifulSoup(result.text, 'html.parser') rows = soup.find_all(string=re.compile("临床症状")) for row in rows: if len(row.parent.select('font')) > 0: illness = replace_self(row.parent.select('font')[0].string) cate = row.parent.select('font')[1].string data2 = { 'cate_id': val3['id'], 'third_id': val3['third_id'], 'illness': illness, 'cate': cate } dbconn.insert(table='illness_cate', data=data2) return '1' @bp.route('/rep_treatment2') def rep_treatment2(): category3 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy, third_id FROM lab_category where id > 0 and children = 3' ) for val3 in category3: # url = 'http://www.aipets.com.cn/Home/DisReportlc?id=' + val3['third_id'] url = 'http://www.aipets.com.cn/Home/DisReportFa?id=' + str(val3['third_id']) # url = 'http://www.aipets.com.cn/Home/DisReportFa?id=3' result = requests.get(url=url) soup = BeautifulSoup(result.text, 'html.parser') # row = soup.find(string=re.compile("以上内容均为建议,所需处方药物,必须在执业兽医指导下使用。非处方药物,也最好在宠物医生指导下使用。")) rows = soup.select('.txtdls') # data = row.parent.next_sibling.text # illness = replace_self(row.parent.select('font')[0].string) for row in rows: if len(row.select('font')) > 0: continue if '临床上常用的治疗原则和可能的药物包括' in row.text: continue if '以上内容均为建议,所需处方药物' in row.text: continue if '治疗原则及药物使用' in row.text: continue if '黄家雨' in row.text: continue if '吴柏青' in row.text: continue data2 = { 'cate_id': val3['id'], 'third_id': val3['third_id'], 'data': row.text, } dbconn.insert(table='illness_end', data=data2) # rows = soup.find_all(class_="txtdls") # ill_data = soup.find(string="通常有以下病症:").parent.select('font')[1].string # # data = { # 'cate_id': val3['id'], # 'third_id': val3['third_id'], # 'name': val3['name'], # 'data': ill_data # } # dbconn.insert(table='illness_data', data=data) # # rows = soup.find_all(string="治疗。可能会用到的药物有") # # rows = soup.select("p > font") # # a = rows.parent.name # # print(a) # for row in rows: # # print(row.parent.select('font')[0]) # illness_name = replace_self(row.parent.select('font')[0].string) # durg_name = row.parent.select('font')[1].string # # data2 = { # 'cate_id': val3['id'], # 'third_id': val3['third_id'], # 'illness_name': illness_name, # 'drug_name': durg_name # } # dbconn.insert(table='illness_durg', data=data2) return '1' @bp.route('/b') def test2(): # # category3 = dbconn.query( # sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy, third_id FROM lab_category where id > 0 and children = 3' # ) # # for val3 in category3: # # url = 'http://www.aipets.com.cn/Home/DisReportlc?id=' + val3['third_id'] # url = 'https://www.aipets.com.cn/Home/DrugZstpDetTab?k=' + val['drug_name'] # url = 'https://www.aipets.com.cn/Home/DrugZuoyongDet?k=' + val['disease_name'] # update_pet_species(url) # print(soup.find_all("table","report-table")) # for val in soup.find_all("report-table"): # print(val) # drug = dbconn.query( # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' # ) # # for key, val in enumerate(drug): # res = json.loads(val['res']) # for key2, val2 in enumerate(res['data'][0]['children']): # disease_name = val2['name'] # disease_id = get_disease(disease_name) # # for key3, val3 in enumerate(val2['children']): # data = { # 'drug_id': val['id'], # 'disease_id': disease_id, # 'usage': val3['name'] # } # dbconn.insert(table='drug_disease', data=data) # drug_name = val['drug_name'] # url = 'https://www.aipets.com.cn/Home/GetDrugZstpsAm?drugname=' + drug_name # response = requests.get(url) # apiData = dbconn.query( # sql='SELECT res FROM api where param = "'+drug_name+'"' # ) # res = json.loads(response.text) # data = { # 'url': url, # 'param': drug_name, # 'res': response.text # } # disease_id = dbconn.insert(table='api', data=data) # for key2, val2 in enumerate(res['data'][0]['children']): # data = { # 'disease_name': val2['name'] # } # disease_id = dbconn.insert(table='disease', data=data) # # for key3, val3 in enumerate(val2['children']): # data = { # 'disease_name': val2['name'] # } # disease_id = dbconn.insert(table='disease', data=data) # print(disease_id) # data = { # 'drug_name': 1 # } # dbconn.insert(table='disease', data=data) return "1" @bp.route('/neo_biochemical') def neo_biochemical(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) rows = dbconn.query( sql='SELECT id, abbreviation, `name`, _range, lab_category_id, biochemical_id FROM lab_category_biochemical where id > 0' ) for val in rows: node_cate3 = node_matcher.match('疾病(三)', id=val['lab_category_id']).first() if node_cate3 is None: continue if val['_range'] == 'high': cn_standard = '高' else: cn_standard = '低' name = val['name'] + '(' + cn_standard + ')' node_biochemical = node_matcher.match('检查项', id=val['biochemical_id']).first() if node_biochemical is None: node_biochemical = Node("检查项", id=val['biochemical_id'], name=name, cn_name=val['name'], abbreviation=val['abbreviation'], standard=val['_range'], category_id=val['lab_category_id']) link3 = Relationship(node_cate3, '检查', node_biochemical) graph.create(link3) return '1' @bp.route('/neo_prestext') def neo_prestext(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) pres = dbconn.query( sql='SELECT langtext, prescription_id FROM lab_category_prescription where id > 0 group by prescription_id' ) for val in pres: node_pres = node_matcher.match('治疗原则', id=val['prescription_id']).first() if node_pres is None: continue text = json.loads(val['langtext']) node_text = Node('') link3 = Relationship(node_pres, '治疗', node_text) graph.create(link3) return '1' @bp.route('/neo_pres') def neo_pres(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) pres = dbconn.query( sql='SELECT id, `name`, lab_category_id, langtext, prescription_id FROM lab_category_prescription where id > 0' ) for val in pres: node_cate3 = node_matcher.match('疾病(三)', id=val['lab_category_id']).first() if node_cate3 is None: continue node_pres = node_matcher.match('治疗原则', id=val['prescription_id']).first() if node_pres is None: node_pres = Node("治疗原则", id=val['prescription_id'], name=val['name'], category_id=val['lab_category_id']) link3 = Relationship(node_cate3, '治疗', node_pres) graph.create(link3) return '1' @bp.route('/neo_cate2') def neo_cate2(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) category3 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3' ) for val3 in category3: # node_cate3 = Node("疾病(三)", id=val3['id'], name=val3['name'], parent_id=val3['parent_id']) node_cate3 = node_matcher.match('疾病(三)', id=val3['id']).first() if node_cate3 is None: node_cate3 = Node("疾病(三)", id=val3['id'], name=val3['name'], parent_id=val3['parent_id']) # description = replace_self(val3['description']) # if description is not None: # node_description = Node("描述", name=description) # link3 = Relationship(node_cate3, '描述', node_description) # graph.create(link3) # # examintions = replace_self(val3['examintions']) # if examintions is not None: # node_examintions = Node("建议检测项目", name=examintions) # link4 = Relationship(node_cate3, '建议检测项目', node_examintions) # graph.create(link4) # # remarks = replace_self(val3['remarks']) # if remarks is not None: # node_remarks = Node("医嘱", name=remarks) # link5 = Relationship(node_cate3, '医嘱', node_remarks) # graph.create(link5) prognosis = replace_self(val3['prognosis']) if prognosis is not None: node_prognosis = Node("预后", name=prognosis) link6 = Relationship(node_cate3, '预后', node_prognosis) graph.create(link6) adjuvanttherapy = replace_self(val3['adjuvanttherapy']) if adjuvanttherapy is not None: dict_adj = json.loads(adjuvanttherapy) str_adj = '' for adj in dict_adj: str_adj += adj['servicename'] + '。' node_adjuvanttherapy = Node("辅助疗法", name=str_adj) link7 = Relationship(node_cate3, '辅助疗法', node_adjuvanttherapy) graph.create(link7) return '1' @bp.route('/neo_cate') def neo_cate(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' category = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 1' ) category2 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 2' ) category3 = dbconn.query( sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3' ) for val in category: node_cate = Node("疾病(一)", id=val['id'], name=val['name'], parent_id=val['parent_id']) for val2 in category2: if val2['parent_id'] == val['id']: node_cate2 = Node("疾病(二)", id=val2['id'], name=val2['name'], parent_id=val2['parent_id']) link1 = Relationship(node_cate, '分类', node_cate2) graph.create(link1) for val3 in category3: if val3['parent_id'] == val2['id']: node_cate3 = Node("疾病(三)", id=val3['id'], name=val3['name'], parent_id=val3['parent_id']) link2 = Relationship(node_cate2, '分类', node_cate3) graph.create(link2) description = replace_self(val3['description']) if description is not None: node_description = Node("描述", name=description) link3 = Relationship(node_cate3, '描述', node_description) graph.create(link3) examintions = replace_self(val3['examintions']) if examintions is not None: node_examintions = Node("建议检测项目", name=examintions) link4 = Relationship(node_cate3, '建议检测项目', node_examintions) graph.create(link4) remarks = replace_self(val3['remarks']) if remarks is not None: node_remarks = Node("医嘱", name=remarks) link5 = Relationship(node_cate3, '医嘱', node_remarks) graph.create(link5) prognosis = replace_self(val3['prognosis']) if prognosis is not None: node_prognosis = Node("预后", name=prognosis) link6 = Relationship(node_cate3, '预后', node_prognosis) graph.create(link6) adjuvanttherapy = replace_self(val3['adjuvanttherapy']) if prognosis is not None: node_adjuvanttherapy = Node("辅助疗法", name=adjuvanttherapy) link7 = Relationship(node_cate3, '辅助疗法', node_adjuvanttherapy) graph.create(link7) return "1" @bp.route('/data_presdrug') def data_presdrug(): pres = dbconn.query( sql='SELECT langtext, prescription_id FROM lab_category_prescription where id > 0 group by prescription_id' ) for val in pres: dict1 = json.loads(val['langtext']) for val2 in dict1: for val3 in val2['drugs']: drug_id = get_drug(val3['drugname']) cond_b = {'prescription_id': val['prescription_id'], 'drug_id': drug_id, 'method': val2['method'], 'methodname': val2['methodname']} dbconn.insert(table='prescription_drug', data=cond_b) return '1' @bp.route('/data_biocid') def data_biocid(): # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' clinical = dbconn.query( sql='SELECT id, `name`, lab_category_id, `_range` FROM lab_category_biochemical where id > 0' ) for val in clinical: clinical_id = get_biochemical(val['name'], val['_range']) data_end = {'biochemical_id': clinical_id} cond = {'id': val['id']} dbconn.update(table='lab_category_biochemical', data=data_end, condition=cond) return '1' @bp.route('/data_presid') def data_presid(): # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' clinical = dbconn.query( sql='SELECT id, `name`, lab_category_id FROM lab_category_prescription where id > 0' ) for val in clinical: clinical_id = get_prescription(val['name']) data_end = {'prescription_id': clinical_id} cond = {'id': val['id']} dbconn.update(table='lab_category_prescription', data=data_end, condition=cond) return '1' @bp.route('/data_clinicalid') def data_clinicalid(): # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' clinical = dbconn.query( sql='SELECT id, `name`, `value`, `qz`, lab_category_id FROM lab_clinical where id > 0' ) for val in clinical: clinical_id = get_clinical(val['name']) data_end = {'clinical_id': clinical_id} cond = {'id': val['id']} dbconn.update(table='lab_clinical', data=data_end, condition=cond) return '1' @bp.route('/neo_clinical') def neo_clinical(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) # clinical_id = dbconn.query( # sql='SELECT id, `name`, `value`, `qz`, lab_category_id FROM lab_clinical where id > 0' # ) # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' clinical = dbconn.query( sql='SELECT id, `name`, `value`, `qz`, lab_category_id, clinical_id FROM lab_clinical where id > 0' ) category = dbconn.query( sql='SELECT id, parent_id, `name` FROM lab_category where id > 0 and children = 3' ) for val in clinical: # clinical_id = get_clinical(val['name']) node_clinical = node_matcher.match('病症', id=val['clinical_id']).first() if node_clinical is None: node_clinical = Node("病症", id=val['clinical_id'], name=val['name']) for val2 in category: if val2['id'] == val['lab_category_id']: node_cate = node_matcher.match('疾病(三)', id=val2['id']).first() if node_cate is None: node_cate = Node("疾病(三)", id=val2['id'], name=val2['name'], parent_id=val2['parent_id']) # node_clinical = Node("病症", id=val['id'], name=val['name'], value=val['value'], qz=val['qz'], category_id=val['lab_category_id']) link2 = Relationship(node_clinical, '疾病', node_cate) link3 = Relationship(node_cate, '病症', node_clinical) graph.create(link2) graph.create(link3) return '1' @bp.route('/neo_drug') def neo_drug(): graph = Graph("", auth=("neo4j", "11111111")) node_matcher = NodeMatcher(graph) # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943' drug = dbconn.query( sql='SELECT d.id, d.drug_name FROM drug as d where d.id > 0 ' ) # print(drug) drug_disease = dbconn.query( sql='SELECT d.drug_id, d.disease_id, d.usage, d.pet_species FROM drug_disease as d where d.id > 0' ) disease = dbconn.query( sql='SELECT d.id, d.disease_name FROM disease as d where d.id > 0' ) disease_data = {} for val in disease: disease_data[val['id']] = val['disease_name'] for val2 in drug: node_drug = Node("药品", id=val2['id'], name=val2['drug_name']) for val3 in drug_disease: if val2['id'] == val3['drug_id']: node_disease = node_matcher.match('适应症', id=val3['disease_id']).first() if node_disease is None: node_disease = Node("适应症", id=val3['disease_id'], name=disease_data[val3['disease_id']]) node_pet_species = Node("品种", drug_id=val3['drug_id'], disease_id=val3['disease_id'], name=val3['pet_species']) # link1 = Relationship(node_drug, '品种', node_pet_species) link2 = Relationship(node_disease, '品种', node_pet_species) node_usage = Node("用法用量", drug_id=val3['drug_id'], disease_id=val3['disease_id'], name=val3['usage']) link3 = Relationship(node_pet_species, '用法用量', node_usage) link4 = Relationship(node_drug, '适应症', node_disease) link1 = Relationship(node_disease, '用药', node_drug) graph.create(link1) graph.create(link2) graph.create(link3) graph.create(link4) # node_indication = Node("适应症", name=indication) # node_useto = Node("用途", name=useto) # node_usage = Node("用法用量", name=usage) # node_basis = Node("成分", name=basis) # node1_unreactions = Node("不良反应", name=unreactions) # # link1 = Relationship(node_gen_name, '适应症', node_indication) # graph.create(link1) # # link2 = Relationship(node_gen_name, '用途', node_useto) # graph.create(link2) # # link3 = Relationship(node_gen_name, '用法用量', node_usage) # graph.create(link3) # # link4 = Relationship(node_gen_name, '成分', node_basis) # graph.create(link4) # # link5 = Relationship(node_gen_name, '不良反应', node1_unreactions) # graph.create(link5) return "1" @bp.route('/c') def test(): # test_graph = Graph("", auth=("neo4j", "11111111")) # node_matcher = NodeMatcher(test_graph) # # case = node_matcher.match('人', name='小绿').first() # 获得name为XXX刑事判决文书的“判决文书”类 节点 # people = node_matcher.match('人', name='小红').first() # 获得name为“XXX”的“被告人”类节点 # relation = Relationship(case, '爱', people) # 创建判决文书和被告人间的关系,关系词为“被告人” # test_graph.create(relation) # 将创建传递到图上 # 建立节点 # test_node_1 = Node("人", id=2) # test_node_2 = Node("人", id=1) # test_graph.create(test_node_1) # test_graph.create(test_node_2) # test_node_1.add_label("teacher") # 建立关系 # node_1_call_node_2 = Relationship(test_node_1, '喜欢', test_node_2) # # node_1_call_node_2['count'] = 1 # node_2_call_node_1 = Relationship(test_node_2, '讨厌', test_node_1) # # node_2_call_node_1['count'] = 2 # test_graph.create(node_1_call_node_2) # test_graph.create(node_2_call_node_1) # return "1" # 建立与ClickHouse数据库的连接 conn = Client( host='cc-2zejbg2y0c0m15o20o.ads.rds.aliyuncs.com', port='3306', user='root_house', password='dbc_root888', database='dbc_drug' ) # sqlStr = 'select * from drug_dh limit 10' sqlStr = 'select drug_name,eng_name,drug_info,uad,useto,note,preparation,adrs,cate,spec,withdrawal_time from vp_codex' result = conn.execute(sqlStr) # print(result) # for row in result: # # id, dh_name, dh_info, state, file, state_info, createtime = row # # print(id, dh_name, dh_info, state, file, state_info, createtime) # gen_name,indication,useto,usage,unreactions = row # print(gen_name,indication,useto,usage,unreactions) # graph = Graph("", auth=("neo4j", "11111111")) # # nodes = graph.nodes.match() # # for node in nodes: # print(node) # node_1 = Node("Person", name="test_node_3") # node_2 = Node("Person", name="test_node_4") # graph.create(node_1) # graph.create(node_2) for row in result: # id, dh_name, dh_info, state, file, state_info, createtime = row # print(id, dh_name, dh_info, state, file, state_info, createtime) # gen_name,eng_name,indication,useto,note,usage,basis,spec,unreactions = row # # cond_b = { # 'gen_name': gen_name, # 'eng_name': eng_name, # 'indication': indication, # 'useto': useto, # 'note': note, # 'usage': usage, # 'basis': basis, # 'spec': spec, # 'unreactions': unreactions # } # # dbconn.insert(table='drug_stock', data=cond_b) drug_name, eng_name, drug_info, uad, useto, note, preparation, adrs, cate, spec, withdrawal_time = row cond_b = { 'drug_name': drug_name, 'eng_name': eng_name, 'drug_info': drug_info, 'uad': uad, 'useto': useto, 'note': note, 'preparation': preparation, 'adrs': adrs, 'cate': cate, 'spec': spec, 'withdrawal_time': withdrawal_time } dbconn.insert(table='vp_codex', data=cond_b) # node_gen_name = Node("药品", name=gen_name, goods_name=goods_name) # node_indication = Node("适应症", name=indication) # node_useto = Node("用途", name=useto) # node_usage = Node("用法用量", name=usage) # node_basis = Node("成分", name=basis) # node1_unreactions = Node("不良反应", name=unreactions) # # link1 = Relationship(node_gen_name, '适应症', node_indication) # graph.create(link1) # # link2 = Relationship(node_gen_name, '用途', node_useto) # graph.create(link2) # # link3 = Relationship(node_gen_name, '用法用量', node_usage) # graph.create(link3) # # link4 = Relationship(node_gen_name, '成分', node_basis) # graph.create(link4) # # link5 = Relationship(node_gen_name, '不良反应', node1_unreactions) # graph.create(link5) # node_1_call_node_2 = Relationship(node_1, 'CALL', node_2) # node_1_call_node_2['count'] = 1 # 该关系的属性 # node_2_call_node_1 = Relationship(node_2, 'CALL', node_1) # node_2_call_node_1['count'] = 2 # graph.create(node_1_call_node_2) # graph.create(node_2_call_node_1) # matcher = graph.NodeMatcher(graph) # user = matcher.match("Person", name="test_node_1").first() # data1 = graph.run('MATCH (n) RETURN n') # 通过关系获取节点列表 # print(list(graph.match(r_type="CALL"))) # print(data1, type(data1)) # data1.keys() # "[, ] " # print(data1[0]['a']['name']) # user = graph.run("UNWIND range(1, 3) AS n RETURN n, n * n as n_sq").to_table() return "1" def get_drug(name): cond_b = {'drug_name': name} rows = dbconn.fetch_rows( table='drug', fields='id,drug_name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='drug', data=cond_b) else: return rows['id'] def get_disease(name): cond_b = {'disease_name': name} rows = dbconn.fetch_rows( table='disease', fields='id,disease_name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='disease', data=cond_b) else: return rows['id'] def get_drug_disease(val1, val2, val3): cond_b = {'drug_id': val1, 'disease_id': val2, 'usage': val3, 'pet_species': 0} rows = dbconn.fetch_rows( table='drug_disease', fields='id', condition=cond_b, fetchone=True ) if rows is None: return 0 # return dbconn.insert(table='drug_disease', data=cond_b) else: return rows['id'] def get_clinical(name): cond_b = {'clinical_name': name} rows = dbconn.fetch_rows( table='clinical', fields='id,clinical_name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='clinical', data=cond_b) else: return rows['id'] def get_prescription(name): cond_b = {'prescription_name': name} rows = dbconn.fetch_rows( table='prescription', fields='id, prescription_name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='prescription', data=cond_b) else: return rows['id'] def get_biochemical(name, standard): if standard == 'high': cn_standard = '高' else: cn_standard = '低' cond_b = {'biochemical_name': name, 'standard': cn_standard} rows = dbconn.fetch_rows( table='biochemical', fields='id, biochemical_name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='biochemical', data=cond_b) else: return rows['id'] def get_symptoms(name): cond_b = {'name': name} rows = dbconn.fetch_rows( table='symptoms', fields='id, name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='symptoms', data=cond_b) else: return rows['id'] def get_treatment(name, medicinal_data): cond_b = {'name': name} rows = dbconn.fetch_rows( table='treatment', fields='id, name', condition=cond_b, fetchone=True ) if rows is None: cond_b['medicinal_data'] = medicinal_data return dbconn.insert(table='treatment', data=cond_b) else: return rows['id'] def get_medicinal(name): cond_b = {'name': name} rows = dbconn.fetch_rows( table='medicinal', fields='id, name', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='medicinal', data=cond_b) else: return rows['id'] def get_treatment_medicinal(treatment_id, medicinal_id): cond_b = {'treatment_id': treatment_id, 'medicinal_id': medicinal_id} rows = dbconn.fetch_rows( table='treatment_medicinal', fields='id, treatment_id', condition=cond_b, fetchone=True ) if rows is None: return dbconn.insert(table='treatment_medicinal', data=cond_b) else: return rows['id'] def update_pet_species(url): result = requests.get(url=url) res = result.text # print(res) soup = BeautifulSoup(res, 'html.parser') # print(soup.prettify()) drug = soup.select(".txtdls > font")[0].string # print(drug) drug_id = get_drug(drug) print(drug_id) doc = soup.select(".report-table>tbody>tr>td") # lendoc = int(len(doc)/3) # # for i in range(lendoc): # print(doc[i].string) # print(doc[i+1].string) # print(doc[i+2].string) # i = i+3 i = 0 while i < len(doc): disease_id = get_disease(doc[i].string) # print(doc[i].string) dd_id = get_drug_disease(drug_id, disease_id, doc[i + 1].string) # print(doc[i + 1].string) print(dd_id) if dd_id == 0: continue data_end = {'pet_species': doc[i + 2].string} cond = {'id': dd_id} dbconn.update(table='drug_disease', data=data_end, condition=cond) # print(doc[i + 2].string) i = i + 3 return def replace_self(str): return str.replace(" ", "").replace("\t", "").replace("\n", "")