这个其实只有参考意义——因为都写死了……
大家参考着学下,写了五天。
主程序d1.py
# coding: utf-8
from fun import *
from tkinter import *
from mail_get import *
import threading
import os
import time
import pyperclip
from sql import *
#import email
#import poplib
def show_list_l1(data):
get_char=',\n'.join(data)
l1.insert('end', get_char+'\n')
def show_list(data):
get_char=',\n'.join(data)
l2.insert('end', get_char+'\n')
'''def get_mail(get_mail_number):
p=poplib.POP3_SSL('mail.inspur.com')
try:
p.user('sdmcceoms@inspur.com')
p.pass_('Eh%8(cy2x6')
except:
print('fail')
pop3_list=p.list()
#获取前50封邮件
max_list=len(pop3_list[1])
print(max_list)
# print(pop3_list)
for i in range(max_list-get_mail_number,max_list):
mail_data= p.retr(i)
mail_data=mail_data[1]
mail_data_str=''
for i in range(0,len(mail_data)):
mail_data_str=mail_data_str+str(mail_data[i])+'/n'
data=get_data(mail_data_str)
show_list(data)
def get_20_email():
get_mail(20)
def get_40_email():
get_mail(50)
'''
def get_num_email(num):
l2.delete(0.0,'end')
num=int(num)
end_row=int(get_mail_len())
for i in range(end_row-1,end_row-num-1,-1):
try:
file1=open('F:\\mail_log\\'+str(i)+'.txt', 'r',encoding='GB2312')
data=file1.read()
file1.close()
line=data.split('\n')
l2.insert('end',line[0]+'\n')
l2.insert('end',line[2]+'\n')
l1.insert('end',data)
char_row=get_data(data)
l2.insert('end',"获得"+str(len(char_row))+"个工单号")
show_list(char_row)
num_row=get_num(data)
l2.insert('end',"获得"+str(len(num_row))+"个流水号")
show_list(num_row)
l2.insert('end','\n')
except Exception as e:
l2.insert('end',str(e)+'\n')
def get_20_email():
get_num_email(20)
def get_40_email():
get_num_email(40)
'''
def get_20_email():
l2.delete(0.0,'end')
index=get_index()
for i in range(index-1,index-20-1,-1):
try:
try:
file1=open('F:\\mail_log\\'+str(i)+'.txt', 'r',encoding='GB2312')
data=file1.read()
except:
file1=open('F:\\mail_log\\'+str(i)+'.txt', 'r',encoding='utf-8',errors='replace')
data=file1.read()
print('F:\\mail_log\\'+str(i)+'.txt')
line=data.split('\n')
l2.insert('end',line[0]+'\n')
l2.insert('end',line[2]+'\n')
print(data)
try:
l1.insert('end',data)
except:
l1.insert('end','未知格式\n\n\n\n')
char_row=get_data(data)
l1.insert('end',"获得"+str(len(char_row))+"个工单号")
show_list_l1(char_row)
num_row=get_num(data)
l1.insert('end',"获得"+str(len(char_row))+"个流水号")
show_list_l1(num_row)
l1.insert('end','\n')
l2.insert('end',"存在UTF-8表情包bug")
char_row=get_data(data)
l2.insert('end',"获得"+str(len(char_row))+"个工单号")
show_list(char_row)
num_row=get_num(data)
l2.insert('end',"获得"+str(len(num_row))+"个流水号")
show_list(num_row)
l2.insert('end','\n')
#l2.insert('end',"正常获取")
file1.close()
except Exception as e:
l2.insert('end',str(i)+str(e)+"\n\n")
'''
'''
def get_40_email():
l2.delete(0.0,'end')
index=get_index()
for i in range(index-1,index-40-1,-1):
try:
file1=open('F:\\mail_log\\'+str(i)+'.txt', 'r',encoding='utf-8',errors='replace')
data=file1.read()
print('F:\\mail_log\\'+str(i)+'.txt')
line=data.split('\n')
l2.insert('end',line[0]+'\n')
l2.insert('end',line[2]+'\n')
print(data)
try:
l1.insert('end',data)
except:
l1.insert('end','未知格式\n\n\n\n')
char_row=get_data(data)
l1.insert('end',"获得"+str(len(char_row))+"个工单号")
show_list_l1(char_row)
num_row=get_num(data)
l1.insert('end',"获得"+str(len(char_row))+"个流水号")
show_list_l1(num_row)
l1.insert('end','\n')
char_row=get_data(data)
l2.insert('end',"获得"+str(len(char_row))+"个工单号")
show_list(char_row)
num_row=get_num(data)
l2.insert('end',"获得"+str(len(num_row))+"个流水号")
show_list(num_row)
l2.insert('end','\n')
#l2.insert('end',"正常获取")
file1.close()
except Exception as e:
l2.insert('end',str(i)+str(e)+"\n\n")'''
def del_file():
f = open('F:\log.txt', 'w+',encoding='utf-8')
f.close()
def ins():
print("你点击了按钮")
l2.delete(0.0, 'end')
var = l1.get(0.0, 'end')
data1 = get_data(var)
show_list(data1)
l2.insert('end','\n******\n')
data2 = get_num(var)
show_list(data2)
#显示SQL语句
l3.delete(0.0,'end')
l3.insert('end',"--select语句,全清问题解决\n")
l3.insert('end',select_char(data1))
l3.insert('end','\n\n')
l3.insert('end',select_num(data2))
l3.insert('end','\n\n')
l3.insert('end',"--update语句,多次执行解决问题\n")
l3.insert('end',update_char(data1))
l3.insert('end','\n\n')
l3.insert('end',update_num(data2))
l3.insert('end','\n\n')
l3.insert('end',update_num_main(data2))
l3.insert('end','\n\n')
l3.insert('end',UUID_fill())
l3.insert('end','\n\n')
l3.insert('end',UUID_fill())
l3.insert('end','\n\n')
l3.insert('end',UUID_fill())
l3.insert('end','\n\n')
def into_file():
l2.delete(0.0,'end')
ins()
data2=l2.get(0.0,'end')
f = open('F:\log.txt', 'w',encoding='utf-8')
f.write(data2)
f.close()
def read_log():
f = open('F:\log.txt', 'a+',encoding='utf-8')
f.seek(0,0)
data=f.read()
l1.insert('end',data)
ins()
f.close()
def get_paste():
history_row=""
i=0
while 1:
i=i+1
time.sleep(1)
get_paste=pyperclip.paste()
if get_paste!=history_row:
print(get_paste)
print('数据更新')
l1.insert('end',get_paste+' \n')
history_row=get_paste
'''def get_mail_return():
try:
symbol,index=get_mail()
l2.insert('end',symbol+'\n')
except Exception as e:
l2.insert('end',str(e)+'\n')
def get_mail_loop():
while 1:
try:
get_mail_return()
finally:
time.sleep(100)
'''
def ins_insert():
#ins()
read_log()
into_file()
def mail_input_looplist():
mail_input_loop()
l1.insert('end',"\n获取全部邮件\n")
def mail_newlist():
data=mail_new()
l1.insert('end',"已经最新,最新为"+str(data)+'\n')
t = threading.Thread(target=get_paste)
t.setDaemon(True)
t.start()
t1=threading.Thread(target=mail_input_looplist)
t1.setDaemon(True)
t1.start()
def pas():
global t
t3 = threading.Thread(target=get_paste)
t3.setDaemon(True)
t3.start()
win = Tk()
win.title('文本整理 @青碧凝霜')
win.geometry='600x600'
Label(win, text='待修改文本').grid(row=0, sticky=W)
l1 = Text(win,width='80', height='18', highlightcolor='blue')
l1.grid(row=0, column=1, sticky=W,columnspan=10)
Label(win, text='处理后文本',width='10',height='10').grid(row=1, sticky=W)
l2 = Text(win,width='80', height='18', highlightcolor='blue')
l2.grid(row=1, column=1, sticky=W,columnspan=10)
Label(win, text='生成的sql',width='10',height='10').grid(row=3, sticky=W)
Button(win, text='更新', width=20, command=ins).grid(row=10, column=1, sticky=W)
#Button(win, text='写入日志', width=15, command=into_file).grid(row=10, column=2, sticky=W)
Button(win, text='更新并写入日志',width=20, command=ins_insert).grid(row=10, column=2, sticky=W)
Button(win, text='读取日志', width=20,command=read_log).grid(row=10, column=3, sticky=W)
Button(win, text='删除日志', width=20, command=del_file).grid(row=10, column=4, sticky=W)
l3 = Text(win,width='80', height='18', highlightcolor='blue')
l3.grid(row=3, column=1, sticky=W,columnspan=10)
Button(win, text='读取20封邮件', width=20, command=get_20_email).grid(row=11, column=1, sticky=W)
Button(win, text='读取40封邮件', width=20,command=get_40_email).grid(row=11, column=2, sticky=W)
Button(win, text='读取最新一封邮件', width=20, command=mail_newlist).grid(row=11, column=3, sticky=W)
Button(win, text='剪贴板轮询(正常的话会多开进程)', width=20, command=pas).grid(row=11, column=4, sticky=W)
win.mainloop()
子程序
fun.py
# 循环获取字符串子程序
#这个数据是list,所有数据格式都有逗号和分号
def get_data(v1):
var=v1
var = var.upper()
var = var + ' '
var=var.replace('——','-')
var=var.replace('-','-')
data_list=[]
find_var=0
end_var=0
for i in range(0, 200):
i=i
find_var = var.find('SD')
if find_var == -1:
break
else:
for j in range(find_var, len(var)):
if (var[j] == 'S' or var[j] == 'D' or var[j] == '0' or var[j] == '1' or var[j] == '2' or var[
j] == '3' or var[j] == '4' or var[j] == '5' or var[j] == '6' or var[j] == '7' or var[j] == '8' or
var[j] == '9' or var[j] == '-' or var[j] == ' ' or var[j] == ' ' or var[j] == ' '):
end_var = j
else:
end_var = j
break
if var[j]== 'S' and j>find_var+10:
break
if (end_var-find_var)>=8:
var_=var[find_var:end_var]
var_ = var_.replace(' ', '')
var_='\'' + var_ + '\''
sym=1
for k in range(len(data_list)):
if var_==data_list[k]:
sym=0
if sym==1:
data_list.append(var_)
var = var[end_var:len(var)]
return data_list
def get_num(var):
var = var.upper()
#var=var.replace(' ','')
var = var + ' '
var=var.replace('——','-')
var=var.replace('-','-')
var_ = ''
var__ = []
for i in range(0, len(var)):
if (var[i] == '-' or var[i] == '1' or var[i] == '2' or var[i] == '3' or var[i] == '4' or var[i] == '5' or var[
i] == '6' or var[i] == '7' or var[i] == '8' or var[i] == '9' or var[i] == '0'):
var_ = var_ + var[i]
else:
var_ = var_ + ' '
#print(var_)
#print(var_.find(" "))
while var_.find(' ') != -1:
var_ = var_.replace(' ', ' ')
var_ = var_.split(' ')
for i in range(0, len(var_)):
if var_[i] != '' and len(var_[i]) > 6 and len(var_[i])<=11:
tmp=var_[i]
get_sym=1
for j in range(0,len(var_[i])):
if tmp[j]=="-" and j>2:
get_sym=0
if tmp[0]!="-" and len(tmp)==11:
get_sym=0
tmp='\''+tmp+'\''
for k in range(len(var__)):
if tmp==var__[k]:
get_sym=0
if get_sym==1:
var__.append(tmp)
#print(var__)
return var__
子程序mail.py
import email
import poplib
from email.parser import Parser
from email.header import decode_header
from email.utils import parseaddr
import time
import re
from html import unescape
root="f:\\mail_log\\"
def html_to_plain_text(html):
text = re.sub('<head.*?>.*?</head>', '', html, flags=re.M | re.S | re.I)
text = re.sub('<a\s.*?>', ' HYPERLINK ', text, flags=re.M | re.S | re.I)
text = re.sub('<.*?>', '', text, flags=re.M | re.S)
text = re.sub(r'(\s*\n)+', '\n', text, flags=re.M | re.S)
return unescape(text)
#user ="1973453723@qq.com"
#password ='sucuqlgrzteccjhj'
#pop3server ='pop.qq.com'
#user ="sdmcceoms@inspur.com"
#password ='Eh%8(cy2x6'
#pop3server ='mail.inspur.com'
#p=poplib.POP3_SSL(pop3server)
#p.user(user)
#p.pass_(password)
#获取前三行的宏,其中关于HEAD的不太清楚,但是不重要……
def decode_str(s):
value, charset = decode_header(s)[0]
if charset:
value = value.decode(charset)
return value
def get_head(msg):
value_char=''
for header in ['From', 'To', 'Subject']:
value = msg.get(header, '')
if value:
if header=='Subject':
value = decode_str(value)
else:
hdr, addr = parseaddr(value)
name = decode_str(hdr)
value = u'%s <%s>' % (name, addr)
#print(value)
value_char=value_char+value+'\n'
return value_char+'\n'
def get_file(msg):
data_char=''
for part in msg.walk():
part_charset=part.get_content_charset()
print(part_charset)
part_type=part.get_content_type()
#print(part_type)
if part_type=="text/plain" or part_type=='text/html':
data=part.get_payload(decode=True)
data=data.decode(part_charset,errors="replace")
data=html_to_plain_text(data)
data_char=data_char+'\n'+data
return data_char+'\n'
def mail_input(start_row=90,end_row=100):
user ="sdmcceomXXXXcom"
password ='Eh%8(XXXx6'
pop3server ='mail.XXXXXom'
p=poplib.POP3_SSL(pop3server)
p.user(user)
p.pass_(password)
for i in range(start_row,end_row+1):
mail=p.retr(i)[1]
mail=b'\n'.join(mail)
msg=email.message_from_bytes(mail)
print(get_head(msg))
print(get_file(msg))
file=open(root+str(i)+".txt",'a+',encoding="gb2312",errors="replace")
file.write(get_head(msg)+'\n')
file.write(get_file(msg)+'\n')
file.close()
p.close()
def get_mail_len():
root_log="f:\\mail_log.txt"
try:
file=open(root_log,'r',encoding='gb2312')
file.seek(0,0)
start_row=file.read()
file.close()
except:
user ="sdmpurXXX.com"
password ='Ehx6'
pop3server ='maiXXXXXXcom'
p=poplib.POP3_SSL(pop3server)
p.user(user)
p.pass_(password)
start_row=len(p.list()[1])-100
file=open(root_log,'w',encoding='gb2312')
file.write(str(start_row))
file.close()
return start_row
def flash_end_row(end_row):
root_log="f:\\mail_log.txt"
file=open(root_log,'w',encoding='gb2312')
file.write(str(end_row))
file.close()
def mail_input_loop():
while 1:
user ="sdms@ir.XXXXXXcom"
password ='2xXXXX6'
pop3server ='maiXXXXX.com'
p=poplib.POP3_SSL(pop3server)
p.user(user)
p.pass_(password)
start_row=int(get_mail_len())+1
end_row=len(p.list()[1])
mail_input(start_row,end_row)
flash_end_row(end_row)
p.close()
time.sleep(200)
print('所有邮件全部最新')
def mail_new():
user ="sdmcceoms@inspur.com"
password ='Eh%8(cy2x6'
pop3server ='mail.inspur.com'
p=poplib.POP3_SSL(pop3server)
p.user(user)
p.pass_(password)
start_row=int(get_mail_len())+1
end_row=len(p.list()[1])
mail_input(start_row,end_row)
flash_end_row(end_row)
p.close()
return int(get_mail_len())+1
#mail_input_loop()
#mail_input()
print(get_mail_len())
sql.py
import uuid
def update_char(data,set=0):
sql1='''insert into ZHJK_YXCL(serialno,EVENT_TIME,subuuid,clr_optr,subject)
select ALARM_REDO_SERIALNO.nextval,sysdate,subuuid,'AUTOMATIC',SUBJECT from active_alarm where SERIALNO in (
select ALARM_SERIAL from synthe_fault_bill_alarm where bill_id in ('''
sql2=''')
)
and subuuid is not NULL;\n'''
get_char=','.join(data)
if get_char=='':
get_char="''"
#l3.delete(0.0,"end")
#l3.insert(0.0,sql1+get_char+sql2)
#l3.insert('end','\n')
var=sql1+get_char+sql2
if set==1:
var=var.replace("\n",'')
return(var)
def update_num_main(data,set=0):
sql1='''insert into ZHJK_YXCL(serialno,EVENT_TIME,subuuid,clr_optr,subject)
select ALARM_REDO_SERIALNO.nextval,sysdate,subuuid,'AUTOMATIC',SUBJECT from active_alarm where
SERIALNO in ('''
sql2=''')
and subuuid is not NULL;\n'''
get_char=','.join(data)
if get_char=='':
get_char="''"
#l3.delete(0.0,"end")
#l3.insert(0.0,sql1+get_char+sql2)
#l3.insert('end','\n')
var=sql1+get_char+sql2
if set==1:
var=var.replace("\n",'')
return(var)
def update_num(data,set=0):
sql1='''insert into ZHJK_YXCL(serialno,EVENT_TIME,subuuid,clr_optr,subject)
select ALARM_REDO_SERIALNO.nextval,sysdate,subuuid,'AUTOMATIC',SUBJECT from active_alarm where
SERIALNO in (
select ID from RELATED_ALARM_RELATION where PARENTID in ('''
sql2='''))
and subuuid is not NULL;\n'''
get_char=','.join(data)
if get_char=='':
get_char="''"
#l3.delete(0.0,"end")
#l3.insert(0.0,sql1+get_char+sql2)
#l3.insert('end','\n')
var=sql1+get_char+sql2
if set==1:
var=var.replace("\n",'')
return(var)
def select_num(data,set=0):
sql1='''select a.serialno, a.subuuid, a.TICKETID, a.al_source, a.ticketid, b.dict_name as subject, c.dict_name as city_name, d.dict_name as country,e.dict_name as vendorname,
f.object_name as object_class,a.occur_time,a.alcol_time,a.insert_time,a.clear_time,a.clrinsert_time,a.clr_optr,a.ems_name,a.dn,a.alarm_node,
a.title,a.source_type,a.room_name,a.alarm_text
from active_alarm a
left join (select dict_id,dict_name from synthe_std_config where group_no=0) b on a.subject=b.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=33) c on a.area=c.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=35) d on a.country=d.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=24) e on a.vendorid=e.dict_id
left join (select object_class,object_name from synthe_object_class ) f on a.object_class=f.object_class
where a.SERIALNO in ('''
sql2=''');\n'''
get_char=','.join(data)
if get_char=='':
get_char="''"
#l3.delete(0.0,"end")
#l3.insert(0.0,sql1+get_char+sql2)
#l3.insert('end','\n')
var=sql1+get_char+sql2
if set==1:
var=var.replace("\n",'')
return(var)
def select_char(data,set=0):
sql1='''select a.ems_name,a.serialno,a.subuuid, h.dict_name as alarm_type,a.al_source,a.ticketid,b.dict_name as subject,c.dict_name as city_name,d.dict_name as country,e.dict_name as vendorname,f.object_name as object_class,a.occur_time,a.alcol_time,a.insert_time,a.clear_time,a.clrinsert_time,a.clr_optr,a.ems_name,a.dn,a.alarm_node,a.title,a.source_type,a.room_name,a.alarm_text
from active_alarm a
left join (select dict_id,dict_name from synthe_std_config where group_no=0) b on a.subject=b.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=33) c on a.area=c.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=35) d on a.country=d.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=24) e on a.vendorid=e.dict_id
left join (select dict_id,dict_name from synthe_std_config where group_no=52) h on a.alarm_type=h.dict_id
left join (select object_class,object_name from synthe_object_class ) f on a.object_class=f.object_class
where a.serialno in (
select alarm_serial from synthe_fault_bill_alarm where bill_id in ('''
sql2=''')
);\n'''
get_char=','.join(data)
if get_char=='':
get_char="''"
#l3.delete(0.0,"end")
#l3.insert(0.0,sql1+get_char+sql2)
#l3.insert('end','\n')
var=sql1+get_char+sql2
if set==1:
var=var.replace("\n",'')
return(var)
def UUID_fill(data_char=''):
sql1='''UPDATE active_alarm SET subuuid="'''
sql2='''" WHERE serialno='''
sql3=''' and subuuid is NULL;\n'''
if data_char=='':
data_char="''"
#l3.delete(0.0,"end")
#l3.insert(0.0,sql1+get_char+sql2)
#l3.insert('end','\n')
var=sql1+str(uuid.uuid4())+sql2+data_char+sql3
return var
OK了,大家参考着玩