def update_type_cluster(self, form_type):
db = DBHelper(self.db)
conn = db.getConnection()
cursor = conn.cursor()
sql = "select * from {}".format(self.table)
cursor.execute(sql)
results = cursor.fetchall()
update_sql_pre = "update {} set `type_cluster` = '{}' where id = '{}'"
for result in results:
if form_type == 'fdu':
update_sql = update_sql_pre.format(self.table, result['type'],
result['id'])
else:
if '论文' in result['type'] or result['type'] == '博士后报告' or result['type'] == '':
result['type'] = 'XL'
elif '古籍' in result['type'] or result['type'] == ['善本']:
result['type'] = 'AB'
elif result['type'] == '期刊':
result['type'] = 'SE'
else:
result['type'] = 'BK'
update_sql = update_sql_pre.format(self.table, result['type'],
result['id'])
print(update_sql)
cursor.execute(update_sql)
conn.commit()
cursor.close()
conn.close()
def go(self, table, duty_table, scholar_table):
db = DBHelper(self.db)
conn = db.getConnection()
cursor = conn.cursor()
search_sql = "select sid from {}".format(table)
cursor.execute(search_sql)
sids = cursor.fetchall()
print(len(sids))
for sid_dic in sids:
sid = sid_dic['sid']
search_duty_sql = "select author from {} where sid = '{}'".format(
duty_table, sid)
cursor.execute(search_duty_sql)
authors = cursor.fetchall()
is_relation = False
for author_dic in authors:
author = author_dic['author']
search_relation_sql = "select count(*) as count from {} where name = '{}'".format(
scholar_table, author)
cursor.execute(search_relation_sql)
count_dic = cursor.fetchone()
count = count_dic['count']
# print(count)
if count > 0:
is_relation = True
break
if not is_relation:
delete_sql = "delete from {} where sid = '{}'".format(
table, sid)
cursor.execute(delete_sql)
conn.commit()
print("delete sid = " + sid)
def __init__(self, db, table, source, unique_index, field_index,
field_value_index):
self.table = table
self.source = source
self.unique_index = unique_index
self.field_index = field_index
self.field_value_index = field_value_index
db = DBHelper(db)
self.conn = db.getConnection()
self.cur = self.conn.cursor()
def update_isbn(self, keys):
db = DBHelper(self.db)
conn = db.getConnection()
cursor = conn.cursor()
sql = "select * from {}".format(self.table)
cursor.execute(sql)
results = cursor.fetchall()
update_keys = keys
update_sql_pre = "update {} {} where id = '{}'"
for result in results:
u_str = 'set '
for key in update_keys:
f_v = result[key].replace('-', '')
f_v = pymysql.escape_string(f_v)
u_str += ("`" + key + "`" + '=' + "'" + f_v + "'" + ',')
update_sql = update_sql_pre.format(self.table, u_str.strip(","),
result['id'])
print(update_sql)
cursor.execute(update_sql)
conn.commit()
def go(self, keys):
db = DBHelper(self.db)
conn = db.getConnection()
cursor = conn.cursor()
sql = "select * from {}".format(self.table)
cursor.execute(sql)
results = cursor.fetchall()
update_keys = keys
update_sql_pre = "update {} {} where id = '{}'"
for result in results:
u_str = 'set '
for key in update_keys:
f_v = Tra2Sim(result[key]).strip("'") if result[key] else ''
f_v = pymysql.escape_string(f_v)
u_str += ("`" + key + "`" + '=' + "'" + f_v + "'" + ',')
# value_str += ("'" + pymysql.escape_string(f_v) + "'" + ',')
update_sql = update_sql_pre.format(self.table, u_str.strip(","),
result['id'])
# print(update_sql)
# # update_sql="update {} {} values {}".format(self.table,)
# # 有问题待优化
# for k, v in result.items():
# # if k not in ('id', 'title_py','title_for','title_foreign'):
# if k in update_keys:
# v = Tra2Sim(v).strip("'")
# update_sql = "update {} set {} = '{}' where id = '{}'".format(
# self.table, k, pymysql.escape_string(v), result['id'])
# cursor.execute(update_sql)
# print(update_sql)
# conn.commit()
print(update_sql)
cursor.execute(update_sql)
conn.commit()
cursor.close()
conn.close()
def __init__(self, db):
self.db = db
h = DBHelper(self.db)
self.conn = h.getConnection()
self.cursor = self.conn.cursor()
def designate_by_scholar(self, scholar_table, duty_table, data_table,
duty_out_table):
db = DBHelper(self.db)
conn = db.getConnection()
cursor = conn.cursor()
scholar_sql = "select id,name from {}".format(scholar_table)
cursor.execute(scholar_sql)
scholars = cursor.fetchall()
# 删除指派表
delete_sql = "delete from {}".format(duty_out_table)
cursor.execute(delete_sql)
conn.commit()
for scholar in scholars:
id = scholar['id']
name = scholar['name']
duty_sql = "insert into {} (sid,author,duty) select sid,author,duty from {} where sid in (select sid from {}) and author ='{}'".format(
duty_out_table, duty_table, data_table, name)
cursor.execute(duty_sql)
conn.commit()
# 找到没有指派过的
not_designate_sql = "select sid from {} where sid not in (select sid from {})".format(
data_table, duty_out_table)
cursor.execute(not_designate_sql)
sids = cursor.fetchall()
print(len(sids))
for sid_dict in sids:
sid = sid_dict['sid']
detail_one = "select * from {} where sid='{}'".format(
data_table, sid)
cursor.execute(detail_one)
detail = cursor.fetchone()
description = detail['description'] if detail['description'] else ''
description_plus = detail['description_plus'] if detail[
'description_plus'] else ''
title_cn = detail['title_cn'] if detail['title_cn'] else ''
series = detail['series'] if detail['series'] else ''
e200 = detail['e200'] if detail['e200'] else ''
c200 = detail['c200'] if detail['c200'] else ''
i200 = detail['i200'] if detail['i200'] else ''
f200 = detail['f200'] if detail['f200'] else ''
g200 = detail['g200'] if detail['g200'] else ''
subject_plus = detail['subject_plus'] if detail[
'subject_plus'] else ''
for scholar in scholars:
id = scholar['id']
name = scholar['name']
if name in description_plus:
description_sql = "insert into {} (sid, author, duty, description_plus) values ('{}','{}','{}','{}')".format(
duty_out_table, sid, name, '',
pymysql.escape_string(description_plus))
cursor.execute(description_sql)
conn.commit()
elif name in (title_cn + series + e200 + c200 + i200 + f200 +
g200 + subject_plus):
print(sid,
title_cn + series + e200 + c200 + i200 + f200 + g200)
description_sql = "insert into {} (sid, author, duty, description_plus) values ('{}','{}','{}','{}')".format(
duty_out_table, sid, name, '',
pymysql.escape_string(title_cn + series + e200 + c200 +
i200 + f200 + g200 +
subject_plus))
cursor.execute(description_sql)
conn.commit()
# print(decription)
# print(sid)
# 找到没有指派过的
not_designate_sql = "select sid from {} where sid not in (select sid from {})".format(
data_table, duty_out_table)
cursor.execute(not_designate_sql)
sids = cursor.fetchall()
print(len(sids))
for sid_dict in sids:
sid = sid_dict['sid']
print(sid)
def __init__(self, table):
self.table = table
# self.list = list
db = DBHelper('fddx13')
self.conn = db.getConnection()
self.cur = self.conn.cursor()