def insertNewDataToS_data(data_list):
db, cursor = connectDatabase()
count = 0
for data in data_list:
# 18个字段
sql = """insert into s_data_1003 (`sid`,`cid`,`year`,`vol`,`encryptlevel`,`language`,`docmedia`,`doi`,`mtitle`,
`authors`,`authorunit`,`keyword`,`abstracts`,`pages`,`bepage`,`filename`,`filepath`,`filesize`) values
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
my_value = (data['sid'], data['cid'], data['year'], data['vol'],
data['encryptlevel'], data['language'], data['docmedia'],
data['doi'], data['mtitle'], data['authors'],
data['authorunit'], data['keyword'], data['abstracts'],
data['pages'], data['bepage'], data['filename'],
data['filepath'], data['filesize'])
# print(sql)
try:
cursor.execute(sql, my_value)
db.commit()
count += 1
except Exception as e:
print(e)
db.rollback()
print(count)
cursor.close()
db.close()
def b():
db, cursor = connectDatabase()
for cid in range(4, 20):
s_data_num = 's_data_' + str(int(cid) + 1000)
sql = 'select id,docid from '+s_data_num
try:
cursor.execute(sql)
results = cursor.fetchall()
for r in results:
# print(r[0])
id = r[0]
old_docid = str(r[1])
if old_docid[:4] == '1001':
docid = (int(cid) + 1000) * 10000000000 + int(id)
# docid = int('1001000' + str(id))
sql2 = 'update ' + s_data_num + ' set docid = %d where id = %d' % (docid, id)
# print(sql2)
try:
cursor.execute(sql2)
db.commit()
except Exception as e:
print(e)
db.rollback()
except Exception as e:
print(e)
cursor.close()
db.close()
def getSidFromS_sourceByMeetingname(meeting_name): db, cursor = connectDatabase() sql = 'select s_id from s_source where sourcename="%s"' % meeting_name # print(sql) try: cursor.execute(sql) s_id = cursor.fetchone() except Exception as e: print(e) cursor.close() db.close() return s_id[0]
def getSourcenameFromEisc_data(): db, cursor = connectDatabase() sql = 'select sourcename from s_source' source_list = [] try: cursor.execute(sql) results = cursor.fetchall() for row in results: source_list.append(row[0]) except Exception as e: print(e) cursor.close() db.close() return source_list
def insertS_sourceByJournal(journal_list, source_list):
db, cursor = connectDatabase()
for j in journal_list:
if j['journalname'] not in source_list:
try:
sql = 'insert into s_source (`collectiontunit`,`issn`,`sourcename`,`doctype`) VALUES ("%s","%s","%s","%s")' % \
('电子一所文献中心', j['issn'], j['journalname'], 'J')
# print(sql)
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
cursor.close()
db.close()
def insertS_sourceByMeeting(meeting_list, source_list):
db, cursor = connectDatabase()
for m in meeting_list:
if m['Meetingname'] not in source_list:
try:
sql = 'insert into s_source (`collectiontunit`,`isbn`,`sourcename`,`pubdate`,`confaddress`,`confdate`,`doctype`) \
VALUES ("%s","%s","%s","%s","%s","%s","%s")' % \
('电子一所文献中心', m['ISBN'], m['Meetingname'], m['publicationDate'], m['MeetingAddress'],
m['MeetingDate'], 'C')
# print(sql)
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
cursor.close()
db.close()
def a():
db, cursor = connectDatabase()
new_excel_file = 'Z:/ALLPDF/SPIE-FULLTXT/2018004/SPIE会议.new.xls'
table_list = excelTableByIndex(new_excel_file)
print(len(table_list))
for t in table_list:
# print(t['filename'])
confdate = t['MeetingDate']
sourcename = t['MeetingName']
sql = 'update s_source set pubdate="%s" where sourcename="%s"' % ('0000-00-00', sourcename)
# print(sql)
try:
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
cursor.close()
db.close()
def insertErrorData(data_list):
db, cursor = connectDatabase()
count = 0
sql = 'select filename from s_data_1003'
filename_list = []
try:
cursor.execute(sql)
results = cursor.fetchall()
for r in results:
# print(r)
filename_list.append(r[0])
for data in data_list:
if data['filename'] not in filename_list:
# 18个字段
sql = """insert into s_data_1003 (`sid`,`cid`,`year`,`vol`,`encryptlevel`,`language`,`docmedia`,`doi`,`mtitle`,
`authors`,`authorunit`,`keyword`,`abstracts`,`pages`,`bepage`,`filename`,`filepath`,`filesize`) values
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
my_value = (data['sid'], data['cid'], data['year'],
data['vol'], data['encryptlevel'],
data['language'], data['docmedia'], data['doi'],
data['mtitle'], data['authors'],
data['authorunit'], data['keyword'],
data['abstracts'], data['pages'], data['bepage'],
data['filename'], data['filepath'],
data['filesize'])
# print(sql)
try:
cursor.execute(sql, my_value)
db.commit()
count += 1
except Exception as e:
print(e)
db.rollback()
except Exception as e:
print(e)
print(count)
cursor.close()
db.close()
def changeDocidById():
db, cursor = connectDatabase()
sql = 'select id from s_data_1003 where docid=0'
try:
cursor.execute(sql)
results = cursor.fetchall()
for r in results:
# print(r[0])
id = r[0]
docid = int('1001000' + str(id))
sql2 = 'update s_data_1003 set docid = %d where id = %d' % (docid,
id)
# print(sql2)
try:
cursor.execute(sql2)
db.commit()
except Exception as e:
print(e)
db.rollback()
except Exception as e:
print(e)
cursor.close()
db.close()