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
