importdata.py

# encoding: utf-8
import os
import random
import re
import time
from flask import Blueprint, json
import requests
# from clickhouse_driver import Client
from app.model.base import dbconn, dbconn2
from bs4 import BeautifulSoup

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('/data_trmed')
def data_trmed():
    data = dbconn.query(
        sql='SELECT id, `name`, `medicinal_data` FROM treatment where id > 0'
    )

    for val in data:
        res = val['medicinal_data'].split('、')
        
        drug_keyword = ''
        
        for val2 in res:
            if val2 == "":
                continue
            
            drug = dbconn.query(
                sql='SELECT id, `name` FROM drug where id > 0 and `name` like \'%' + val2 + '%\''
            )

            if len(drug) > 0:
                
                for val4 in drug:
                    data3 = {
                        'treatment_id': val['id'],
                        'drug_id': val4['id']
                    }
                    dbconn.insert(table='treatment_drug', data=data3)
                    drug_keyword += val4['name'] + ','

                
        drug_keyword = drug_keyword.strip(',')
        data_end = {'drug_data': drug_keyword}
        cond = {'id': val['id']}
        
        dbconn.update(table='treatment', data=data_end, condition=cond)
    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('/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('/c')
def test():

    # 建立与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)


    #
    # 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()
    # "[<Record a=(_214:人 {name: '\u5c0f\u660e'})>, <Record a=(_254:人 {age: 22, name: '\u5c0f\u7ea2'})>] <class 'list'>"
    # 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", "")