def insert_sql(self, judge, result, Education, Work, person): sql = "" if judge == "director": sql = "insert into " + get_config( 'db', 'table_director' ) + "(did,code,name,position,sex,age,education,position_title,resume,create_time,modify_person) values(" + str( result.did ) + ",'" + result.code + "','" + result.name + "','" + result.position + "','" + result.sex + "','" + result.age + "','" + result.education + "','" + result.position_title + "','" + result.resume + "','" + get_now( ) + "','" + person + "')" if judge == "education": Education.time = change_none(Education.time) Education.university = change_none(Education.university) Education.education = change_none(Education.education) return "insert into " + get_config( 'db', 'table_education' ) + "(did,university,education,time,create_time,modify_person) values(" + str( result.did ) + ",'" + Education.university + "','" + Education.education + "','" + Education.time + "','" + get_now( ) + "','" + person + "')" if judge == "work": Work.time = change_none(Work.time) Work.company = change_none(Work.company) Work.position = change_none(Work.position) sql = "insert into " + get_config( 'db', 'table_work' ) + "(did,company,position,time,create_time,modify_person) values(" + str( result.did ) + ",'" + Work.company + "','" + Work.position + "','" + Work.time + "','" + get_now( ) + "','" + person + "')" return sql
def output_sql(self): table_director = get_config('db', 'table_director') table_education = get_config('db', 'table_education') table_work = get_config('db', 'table_work') path = get_config('excel', 'path') # sql = "select "+table_director+".name, "+table_education+".* from "+table_director+" left join "+table_education+" on "+table_director+".did="+table_education+".did into outfile '"+path+"'" sql = "select " + table_director + ".name, " + table_work + ".* from " + table_director + " left join " + table_work + " on " + table_director + ".did=" + table_work + ".did into outfile '" + path + "'" # sql = "select " + table_director + ".* into outfile '" + path + "'" return sql
def parser_initial(): par_model_path = os.path.join( get_config('ner', 'LTP_DATA_DIR'), 'parser.model') # 依存句法分析模型路径,模型名称为`parser.model` parser = Parser() # 初始化实例 parser.load(par_model_path) # 加载模型 return parser
def segmentor_initial(): cws_model_path = os.path.join(get_config('ner', 'LTP_DATA_DIR'), 'cws.model') # 分词模型路径,模型名称为`cws.model` segmentor = Segmentor() # 初始化实例 segmentor.load(cws_model_path) # 加载模型 # segmentor.load_with_lexicon(cws_model_path, get_config('ner', 'lexicon')) # 加载模型 return segmentor
def count(self, con): # 执行sql语句 try: with con.cursor() as cursor: sql = "select count(*) from " + get_config('db', 'table') cursor.execute(sql) result = cursor.fetchone() except: print("执行count异常") # self.sql_close(con) return result
def read_excel(self, con, id): # 执行sql语句 try: with con.cursor() as cursor: sql = "select * from " + get_config( 'db', 'table') + " where id=" + str(id) cursor.execute(sql) result = cursor.fetchone() except: print("执行read_excel异常") # self.sql_close(con) return result
def insert_table(result, cm, con): #insert_director # judge = "director" # sql = cm.insert_sql(judge,result,None,None,get_config('developer', 'person')) # flag = cm.insert_data(con,result,sql,judge) flag = True #insert_education judge = "education" for s in result.education_list: if flag: sql = cm.insert_sql(judge, result, s, None, get_config('developer', 'person')) flag = cm.insert_data(con, result, sql, judge) #insert_work judge = "work" for s in result.work_list: if flag: sql = cm.insert_sql(judge, result, None, s, get_config('developer', 'person')) flag = cm.insert_data(con, result, sql, judge)
class ConnectMysql(object): # 连接配置信息 config = { 'host': get_config('db', 'host'), 'port': int(get_config('db', 'port')), # MySQL默认端口 'user': get_config('db', 'user'), # mysql默认用户名 'password': get_config('db', 'password'), 'db': get_config('db', 'db'), # 数据库 'charset': get_config('db', 'charset'), 'cursorclass': pymysql.cursors.DictCursor, } #创建连接 def connect(self): con = pymysql.connect(**self.config) return con def read_excel(self, con, id): # 执行sql语句 try: with con.cursor() as cursor: sql = "select * from " + get_config( 'db', 'table') + " where id=" + str(id) cursor.execute(sql) result = cursor.fetchone() except: print("执行read_excel异常") # self.sql_close(con) return result # print(df.head) #读取数据表中数量 def count(self, con): # 执行sql语句 try: with con.cursor() as cursor: sql = "select count(*) from " + get_config('db', 'table') cursor.execute(sql) result = cursor.fetchone() except: print("执行count异常") # self.sql_close(con) return result def insert_data(self, con, result, sql, table): flag = True # 执行sql语句 try: with con.cursor() as cursor: cursor.execute(sql) con.commit() except: print("执行" + table + ":insert_data异常:id:" + str(result.did) + " name:" + result.name + "该条记录出错") con.rollback() # self.sql_close(con) flag = False return flag #关闭数据库连接 def sql_close(self, con): con.close() #获取insert_data_sql语句 def insert_sql(self, judge, result, Education, Work, person): sql = "" if judge == "director": sql = "insert into " + get_config( 'db', 'table_director' ) + "(did,code,name,position,sex,age,education,position_title,resume,create_time,modify_person) values(" + str( result.did ) + ",'" + result.code + "','" + result.name + "','" + result.position + "','" + result.sex + "','" + result.age + "','" + result.education + "','" + result.position_title + "','" + result.resume + "','" + get_now( ) + "','" + person + "')" if judge == "education": Education.time = change_none(Education.time) Education.university = change_none(Education.university) Education.education = change_none(Education.education) return "insert into " + get_config( 'db', 'table_education' ) + "(did,university,education,time,create_time,modify_person) values(" + str( result.did ) + ",'" + Education.university + "','" + Education.education + "','" + Education.time + "','" + get_now( ) + "','" + person + "')" if judge == "work": Work.time = change_none(Work.time) Work.company = change_none(Work.company) Work.position = change_none(Work.position) sql = "insert into " + get_config( 'db', 'table_work' ) + "(did,company,position,time,create_time,modify_person) values(" + str( result.did ) + ",'" + Work.company + "','" + Work.position + "','" + Work.time + "','" + get_now( ) + "','" + person + "')" return sql def output_sql(self): table_director = get_config('db', 'table_director') table_education = get_config('db', 'table_education') table_work = get_config('db', 'table_work') path = get_config('excel', 'path') # sql = "select "+table_director+".name, "+table_education+".* from "+table_director+" left join "+table_education+" on "+table_director+".did="+table_education+".did into outfile '"+path+"'" sql = "select " + table_director + ".name, " + table_work + ".* from " + table_director + " left join " + table_work + " on " + table_director + ".did=" + table_work + ".did into outfile '" + path + "'" # sql = "select " + table_director + ".* into outfile '" + path + "'" return sql def output_excel(self, con, sql): flag = True # 执行sql语句 try: with con.cursor() as cursor: cursor.execute(sql) con.commit() except: print("执行导出excel异常") con.rollback() self.sql_close(con) flag = False return flag
def recognizer_initial(): ner_model_path = os.path.join(get_config('ner', 'LTP_DATA_DIR'), 'ner.model') # 命名实体识别模型路径,模型名称为`pos.model` recognizer = NamedEntityRecognizer() # 初始化实例 recognizer.load(ner_model_path) # 加载模型 return recognizer
def postagger_initial(): pos_model_path = os.path.join(get_config('ner', 'LTP_DATA_DIR'), 'pos.model') # 词性标注模型路径,模型名称为`pos.model` postagger = Postagger() # 初始化实例 postagger.load(pos_model_path) # 加载模型 return postagger