BIDI_EXTRACTION_AI/BiddingKG/dl/scripts/legacy/preprocessing_db.py

# -*- coding: utf-8 -*-
"""遗留入库与批量处理脚本。

按 ARCHITECTURE.md Phase 4 要求，从 ``interface/Preprocessing.py`` 迁出。
类型：LEGACY（非运行时，仅用于历史数据迁移或调试）。

原位置：``interface/Preprocessing.py`` 中以下函数：
    - ``persistenceData`` — 将中间结果保存到数据库（线上不执行）
    - ``persistenceData1`` — 将实体/句子中间结果保存到数据库（线上不执行）
    - ``_handle`` — 批量表格解析的子任务处理函数
    - ``getPredictTable`` — 批量表格预测脚本入口

``interface/Preprocessing.py`` 仍 re-export 以上全部名称，老 import 不受影响。
"""
from __future__ import absolute_import

import json

from bs4 import BeautifulSoup

from BiddingKG.dl.preprocess.table_parser import tableToText

__all__ = [
    "persistenceData",
    "persistenceData1",
    "_handle",
    "getPredictTable",
]


def persistenceData(data):
    '''
    @summary:将中间结果保存到数据库-线上生产的时候不需要执行
    '''
    # Phase 1: PG 连接走 infra/db（原硬编码 host=192.168.2.101, password=postgres 已移除）
    from BiddingKG.dl.infra.db import get_connection
    conn = get_connection("BiddingKG")
    cursor = conn.cursor()
    for item_index in range(len(data)):
        item = data[item_index]
        doc_id = item[0]
        dic = item[1]
        code = dic['code']
        name = dic['name']
        prem = dic['prem']
        if len(code)==0:
            code_insert = ""
        else:
            code_insert = ";".join(code)
        prem_insert = ""
        for item in prem:
            for x in item:
                if isinstance(x, list):
                    if len(x)>0:
                        for x1 in x:
                            prem_insert+="/".join(x1)+","
                    prem_insert+="$"
                else:
                    prem_insert+=str(x)+"$"
            prem_insert+=";"
        sql = " insert into predict_validation(doc_id,code,name,prem) values('"+doc_id+"','"+code_insert+"','"+name+"','"+prem_insert+"')"
        cursor.execute(sql)
    conn.commit()
    conn.close()


def persistenceData1(list_entitys,list_sentences):
    '''
    @summary:将中间结果保存到数据库-线上生产的时候不需要执行
    '''
    # Phase 1: PG 连接走 infra/db
    from BiddingKG.dl.infra.db import get_connection
    conn = get_connection("BiddingKG")
    cursor = conn.cursor()
    for list_entity in list_entitys:
        for entity in list_entity:
            if entity.values is not None:
                sql = " insert into predict_entity(entity_id,entity_text,entity_type,doc_id,sentence_index,begin_index,end_index,label,values) values('"+str(entity.entity_id)+"','"+str(entity.entity_text)+"','"+str(entity.entity_type)+"','"+str(entity.doc_id)+"',"+str(entity.sentence_index)+","+str(entity.begin_index)+","+str(entity.end_index)+","+str(entity.label)+",array"+str(entity.values)+")"
            else:
                sql = " insert into predict_entity(entity_id,entity_text,entity_type,doc_id,sentence_index,begin_index,end_index) values('"+str(entity.entity_id)+"','"+str(entity.entity_text)+"','"+str(entity.entity_type)+"','"+str(entity.doc_id)+"',"+str(entity.sentence_index)+","+str(entity.begin_index)+","+str(entity.end_index)+")"
            cursor.execute(sql)
    for list_sentence in list_sentences:
        for sentence in list_sentence:
            str_tokens = "["
            for item in sentence.tokens:
                str_tokens += "'"
                if item=="'":
                    str_tokens += "''"
                else:
                    str_tokens += item
                str_tokens += "',"
            str_tokens = str_tokens[:-1]+"]"
            sql = " insert into predict_sentences(doc_id,sentence_index,tokens) values('"+sentence.doc_id+"',"+str(sentence.sentence_index)+",array"+str_tokens+")"
            cursor.execute(sql)
    conn.commit()
    conn.close()


def _handle(item,result_queue):
    dochtml = item["dochtml"]
    docid = item["docid"]
    list_innerTable = tableToText(BeautifulSoup(dochtml,"lxml"))
    flag = False
    if list_innerTable:
        flag = True
        for table in list_innerTable:
            result_queue.put({"docid":docid,"json_table":json.dumps(table,ensure_ascii=False)})


def getPredictTable():
    filename = "D:\Workspace2016\DataExport\data\websouce_doc.csv"
    import pandas as pd
    import json
    from BiddingKG.dl.common.MultiHandler import MultiHandler,Queue
    df = pd.read_csv(filename)
    df_data = {"json_table":[],"docid":[]}
    _count = 0
    _sum = len(df["docid"])
    task_queue = Queue()
    result_queue = Queue()
    _index = 0
    for dochtml,docid in zip(df["dochtmlcon"],df["docid"]):
        task_queue.put({"docid":docid,"dochtml":dochtml,"json_table":None})
        _index += 1

    mh = MultiHandler(task_queue=task_queue,task_handler=_handle,result_queue=result_queue,process_count=5,thread_count=1)
    mh.run()
    while True:
        try:
            item = result_queue.get(block=True,timeout=1)
            df_data["docid"].append(item["docid"])
            df_data["json_table"].append(item["json_table"])
        except Exception as e:
            print(e)
            break
    df_1 = pd.DataFrame(df_data)
    df_1.to_csv("../form/websource_67000_table.csv",columns=["docid","json_table"])