pyqt5_站点管理_WaterClient_1

主界面:

*程序中有错误,过段时间再回头修改.

from PyQt5.QtWidgets import QApplication,QWidget,QStatusBar,QTabWidget,QVBoxLayout from PyQt5.QtGui import QIcon,QPalette,QBrush,QPixmap from PyQt5.QtCore import QTimer import sys,os,time,datetime,smtplib,pickle,pymssql import index_windows,stcd_windows,db_windows,email_windows,img from email import encoders from email.header import Header from email.utils import parseaddr,formataddr from email.mime.text import MIMEText #定义主程序界面: class mainwindows(QWidget):     def __init__(self):         super().__init__()         self.initUI()     def initUI(self):         #设置标题和大小         self.qicon1 = QIcon(':/IMG/1.png')         self.qicon2 = QIcon(':/IMG/2.png')         self.qicon3 = QIcon(':/IMG/3.png')         self.qicon4 = QIcon(':/IMG/4.png')         self.windows_icon_timer_num = 1         self.windows_icon_timer = QTimer(self)         self.windows_icon_timer.timeout.connect(self.windows_icon_timer_changed)         self.windows_icon_timer.start(500)         #背景图片         palette = QPalette()         palette.setBrush(QPalette.Background,QBrush(QPixmap(':/IMG/window_bg.png')))         self.setPalette(palette)         self.resize(800,600)         self.setWindowTitle('检测软件')         #定义tabwidget:1,首页 2,站点配置,3,数据库配置,4,邮件配置,5,其他配置         #1,首页         self.index_window = index_windows.index_window()         #2,站点配置         self.stcd_window = stcd_windows.stcd_window()         #3,数据库配置         self.db_window = db_windows.db_window()         #4,邮件配置         self.email_window = email_windows.email_window()         #5,其他配置         self.ather_window = QWidget()         #定义tabwidget         self.tabwidget = QTabWidget()         self.tabwidget.insertTab(0,self.index_window,QIcon(':/IMG/index.png'),'首页')         self.tabwidget.insertTab(1,self.stcd_window,QIcon(':/IMG/stcd.png'),'站点')         self.tabwidget.insertTab(2,self.db_window,QIcon(':/IMG/database.png'),'数据库')         self.tabwidget.insertTab(3,self.email_window,QIcon(':/IMG/mail.png'),'邮件')         self.tabwidget.insertTab(4,self.ather_window,QIcon(':/IMG/information.png'),'杂项')         # 设置状态栏         self.statusBar = QStatusBar(self)         #布局管理         vbox = QVBoxLayout()         vbox.addWidget(self.tabwidget)         vbox.addWidget(self.statusBar)         self.setLayout(vbox)         self.show()         self.auto_start_timer = QTimer(self)         self.auto_start_timer.timeout.connect(self.auto_start)         self.auto_start_timer.start(60000)     def windows_icon_timer_changed(self):         if self.windows_icon_timer_num == 1:             self.setWindowIcon(self.qicon1)             self.windows_icon_timer_num = 2         elif self.windows_icon_timer_num == 2:             self.setWindowIcon(self.qicon2)             self.windows_icon_timer_num = 3         elif self.windows_icon_timer_num == 3:             self.setWindowIcon(self.qicon3)             self.windows_icon_timer_num = 4         elif self.windows_icon_timer_num == 4:             self.setWindowIcon(self.qicon4)             self.windows_icon_timer_num = 1         else:             self.windows_icon_timer_num = 1     def get_time(time_flag='', d=0, H=0, M=0):         time_now = datetime.datetime.today()         try:             if time_flag == 'YMD':                 time_d = time_now + datetime.timedelta(days=d)                 return str(time_d).split()[0]                 # 2017-03-10             elif time_flag == 'DHM':                 time_M = time_now + datetime.timedelta(minutes=M)                 time_H = time_M + datetime.timedelta(hours=H)                 time_D = time_H + datetime.timedelta(days=d)                 return str(time_D).split('.')[0]                 # 2017-03-10 14:00:39             elif time_flag == 'NOW':                 time_1 = str(time_now).split()[0]                 hour = time.localtime().tm_hour                 return time_1 + ' ' + str(hour) + ':03:00'             elif time_flag == 'HOUR':                 time_H = time_now + datetime.timedelta(hours=-1)                 time_1 = str(time_H).split()[0]                 hour = str(time_H).split()[1].split(':')[0]                 return time_1 + ' ' + hour + ':03:00'             else:                 return str(time_now).split('.')[0]                 # 2017-03-10 14:00:39         except Exception as error:             return '2017-1-1 00:00:00'             # 2017-03-10 14:00:39     def write_log(self,message):         self.index_window.index_textedit.append(self.get_time() + ':  %s\n'%message)     def path_exists(self,file_path):         # return 2:文件不存在         # return 0:不是文件夹,且有内容         # return 1:是文件夹         try:             if os.path.exists(file_path):                 if os.path.isfile(file_path):                     if os.path.getsize(file_path) > 0:                         return 0                     else:                         os.remove(file_path)                         return 2                 else:                     return 1             else:                 return 2         except Exception as error:             self.write_log(repr(error))     def frist_runsql(self,DBconfig_path):         # return 1:链接数据库错误         # 说明:第一次在服务器上运行时候,初始化服务器数据信息         self.write_log('--------------------第一次初始化数据库信息--------------------')         # 用于链接数据库,查询数据库内容,return 0:success   1,2,3,4...error         try:             conn = pymssql.connect(**self.db_window.dir_db)         except Exception as error:             self.write_log('第一次初始化过程中,连接数据库错误:\n' + repr(error))             self.statusBar.showMessage('数据库链接错误.')             return 1         try:             cur = conn.cursor()             # 得到数据库游标             cur.execute('SELECT Name FROM Master..SysDatabases;')             # 得到所有数据库名称             row = cur.fetchone()             error_status = 0             db_list = []             # 标记错误次数             while row:                 if row[0] == 'water' or row[0] == 'STDDB_V380':                     self.write_log('数据库中存在%s数据库.' % row[0])                     db_list.append(row[0])                     error_status = error_status + 2                 else:                     self.write_log('扫描到的其他数据库名称:' + row[0])                 row = cur.fetchone()             if len(db_list) == 0:                 return 0             try:                 file_water = open(DBconfig_path, 'wb')                 pickle.dump(db_list, file_water)             except Exception as error:                 self.write_log('初始化过程中,%s数据库处理部分,错误信息:\n' % row[0] + repr(error))                 error_status = error_status + 5             finally:                 try:                     file_water.close()                 except Exception as error:                     self.write_log('关闭%s文件出错:\n' % row[0] + repr(error))         except Exception as error:             self.write_log('初始化数据过程中,存在的问题:\n' + repr(error))         finally:             try:                 cur.close()                 conn.close()                 self.write_log('----------------------初始化数据信息完成----------------------')                 return error_status             except Exception as error:                 self.write_log('初始化数据库过程中,关闭数据库出错:' + repr(error))                 return error_status + 7     def runsql(self,sql, DBname, TABLE_NAME):         # return 1:数据库链接错误         # return 2:         try:             conn = pymssql.connect(**self.db_window.dir_db, database=DBname)         except pymssql.InterfaceError as intererror:             # 链接不了数据库             # print('链接不上数据库')             # 记录连接日志             log = '程序无法连接数据库!'             self.write_log(DBname + log + repr(intererror))             with open('.//DATA//error.log', 'a') as error_rite:                 error_rite.write('执行数据库查询,链接数据库失败.')             return (1, [])         except Exception as error:             self.write_log(DBname + repr(error))             with open('.//DATA//error.log', 'a') as error_rite:                 error_rite.write('数据库执行错误%s' % repr(error), )                 return (1, [])         try:             self.statusBar.showMessage('数据库链接正常.')             # print('================连接数据库==========================')             cur = conn.cursor()             # 得到数据库游标             cur.execute(sql)             # 得到所有数据库名称             row = cur.fetchone()             try:                 row_list = []                 while row:                     try:                         row_list.append((str(row[0]).strip(), str(row[1]), row[2]))                     except Exception as error_1:                         self.write_log('%s行处理数据结果过程中,错误:\n' % TABLE_NAME + repr(error_1))                     finally:                         row = cur.fetchone()             except Exception as error:                 self.write_log('数据库列表过程中,错误:\n' + repr(error))         except Exception as error:             # print('error--------1--------' + repr(error))             self.write_log('%s执行错误' % TABLE_NAME + repr(error))         finally:             try:                 cur.close()                 conn.close()                 return (0, row_list)             except Exception as error:                 self.write_log(TABLE_NAME + ':关闭数据库出错!' + repr(error))             finally:                 self.write_log(TABLE_NAME + '-->数据脚本执行完成!')     def run_sql(self):         # 说明:执行数据库查询脚本         try:             # return 0 :ok             # return 1 :error             info_list_path = './/DATA//info.pik'             info_list_path_num = self.path_exists(info_list_path)             pickle_infolist_num = 0             if info_list_path_num == 1:                 os.remove(info_list_path)                 pickle_infolist_num = self.pickle_infolist()             elif info_list_path_num == 2:                 pickle_infolist_num = self.pickle_infolist()             if pickle_infolist_num == 1:                 return 1         except Exception as error:             self.write_log('执行数据库查询,最初阶段出错!\n' + repr(error))         try:             config_path = './/DATA//DBconfig.pik'             config_file = open(config_path, 'rb')             pickle_load = pickle.load(config_file)             i = 0             row_list = []             while i < len(pickle_load):                 if pickle_load[i] == 'water':                     sql = 'SELECT [meterId], [meterDt],[rainAmount] FROM [water].[dbo].[rain] ' \                           'WHERE meterDt<\'' + self.get_time('NOW') + '\' AND meterDt>=\'' + self.get_time('HOUR') + '\';'                     status, row_list_rain = self.runsql(sql, 'water', 'rain')                     if status == 0:                         row_list = row_list + row_list_rain                     sql = 'SELECT [meterId],[meterDt],[waterLv] FROM [water].[dbo].[waterLv] ' \                           'WHERE meterDt<\'' + self.get_time('NOW') + '\' AND meterDt>=\'' + self.get_time('HOUR') + '\';'                     status, row_list_waterlv = self.runsql(sql, 'water', 'waterLv')                     if status == 0:                         row_list = row_list + row_list_waterlv                 elif pickle_load[i] == 'STDDB_V380':                     sql = 'SELECT [STCD],[TM],[DRP] FROM [STDDB_V380].[dbo].[ST_PPTN_R] ' \                           'WHERE TM<\'' + self.get_time('NOW') + '\' AND TM>=\'' + self.get_time('HOUR') + '\';'                     status, row_list_pptn = self.runsql(sql, 'STDDB_V380', 'ST_PPTN_R')                     if status == 0:                         row_list = row_list + row_list_pptn                     sql = 'SELECT [STCD],[TM],[Z] FROM [STDDB_V380].[dbo].[ST_RIVER_R]' \                           'WHERE TM<\'' + self.get_time('NOW') + '\' AND TM>=\'' + self.get_time('HOUR') + '\';'                     status, row_list_river = self.runsql(sql, 'STDDB_V380', 'ST_RIVER_R')                     if status == 0:                         row_list = row_list + row_list_river                     sql = 'SELECT [STCD],[TM],[RZ] FROM [STDDB_V380].[dbo].[ST_RSVR_R]' \                           'WHERE TM<\'' + self.get_time('NOW') + '\' AND TM>=\'' + self.get_time('HOUR') + '\';'                     status, row_list_rsvr = self.runsql(sql, 'STDDB_V380', 'ST_RSVR_R')                     if status == 0:                         row_list = row_list + row_list_rsvr                 i = i + 1         except Exception as error:             self.write_log('数据库查询总体上报错:\n' + repr(error))         try:             info_file = open('.//DATA//info.pik', 'rb')             info_list = pickle.load(info_file)         except Exception as error:             self.write_log('得站点信息列表错误,info.pik已经存在:\n' + repr(error))         try:             now_hour = time.localtime().tm_hour             info_list[0].append(str(now_hour - 1) + '时 至 ' + str(now_hour) + '时')             i = 1             while i < len(info_list):                 info_list[i][0]                 m = 0                 flag = 0                 while m < len(row_list):                     if str(info_list[i][0][0]) == str(row_list[m][0]):                         flag = 1                         del row_list[m]                         m = m - 1                     m = m + 1                 if flag == 1:                     info_list[i].append(1)                 else:                     info_list[i].append(0)                 i = i + 1         except Exception as error:             print(repr(error))             return 1         try:             info_list_file = open('.//DATA//info.pik', 'wb')             pickle.dump(info_list, info_list_file)             return 0         except Exception as error:             self.write_log('重新压入数据错误:' + repr(error))             return 1         finally:             info_list_file.close()     def send_email(self):         # 发送邮件         def _format_addr(s):             name, addr = parseaddr(s)             return formataddr((Header(name, 'utf-8').encode(), addr))         self.write_log('准备发送邮件')         file = open('.//DATA//info.pik', 'rb')         info_list = pickle.load(file)         msg_txt = '<html><body><table border="1" cellspacing="0" style="color:#FFF; text-align:left; ' \                   'font-size:14px; font-weight:bold; margin-left:1px; width:1200px; height:200px;text-align:center">'         i = 0         while i < len(info_list):             n = 0             m = 0             tr = '<tr>'             while n < len(info_list[i]):                 if i == 0 or n == 0:                     if m == 0:                         tr = tr + '<td bgcolor="224071">%d</td><td bgcolor="224071">' % i + str(                             info_list[i][n]) + '</td>'                         m = m + 1                     else:                         tr = tr + '<td bgcolor="224071">' + str(info_list[i][n]) + '</td>'                 else:                     if info_list[i][n] == 0:                         tr = tr + '<td bgcolor="e91e6f">无数据</td>'                     else:                         tr = tr + '<td bgcolor="1ea27e">有数据</td>'                 n = n + 1             tr = tr + '</tr>'             m = 0             msg_txt = msg_txt + tr             i = i + 1         msg_txt = msg_txt + '</table></body></html>'         try:             msg = MIMEText(msg_txt, 'plain', 'utf-8')             from_addr = self.email_window.dir_email['user']             password = self.email_window.dir_email['password']             to_addrs = ''             for line in self.email_window.list_users:                 to_addrs = to_addrs + line + ','             stmp_server = self.email_window.dir_email['stmp']             msg['From'] = _format_addr('%s<%s>' % (self.email_window.dir_email['user'],from_addr))             msg['To'] = ','.join(to_addrs)             msg['Subject'] = Header('%s%s邮件' % (self.email_window.dir_email['name'],self.get_time(), 'utf-8').encode())             server = smtplib.SMTP(stmp_server, 25)             server.set_debuglevel(1)             server.login(from_addr, password)             server.sendmail(from_addr, [to_addrs], msg.as_string())             os.remove('.//DATA//info.pik')             server.quit()             return 0         except Exception as error:             self.write_log('邮件发送失败!\n' + repr(error))             return 1     def auto_start(self):         if time.localtime().tm_min != 3:             return         self.write_log('------------------程序开始------------------')         try:             path_list = ['.//log', './/DATA']             for dir_path in path_list:                 dir_path_num = self.path_exists(dir_path)                 if dir_path_num == 1:                     pass                 elif dir_path_num == 0:                     os.remove(dir_path)                     os.mkdir(dir_path)                 else:                     os.mkdir(dir_path)             config_file = './/DATA//DBconfig.pik'             config_file_num = self.path_exists(config_file)             first_runsql_num = 9             if config_file_num == 0:                 first_runsql_num = 2             elif config_file_num == 1:                 os.remove(config_file)                 first_runsql_num = self.frist_runsql(config_file)             else:                 first_runsql_num = self.frist_runsql(config_file)             if first_runsql_num == 2 or first_runsql_num == 4:                 pass             elif first_runsql_num > 4:                 os.remove('.//DATA//DBconfig.pik')                 self.write_log('DBconfig.pik有问题.程序终止运行.')                 return             elif first_runsql_num == 0:                 self.write_log('数据库中不存在想要的数据库.')                 return             else:                 self.write_log('DBconfig.pik有问题.程序终止运行.')                 return         except Exception as error:             self.write_log('执行错误')             return         try:             run_sql_flag = self.run_sql()             if run_sql_flag == 1:                 return             if time.localtime().tm_hour == 8:                 if time.localtime().tm_min == 3:                     i = 0                     while i < 4:                         flag = self.send_email()                         if flag == 0:                             i = 4                             break                         i = i + 1                         time.sleep(300)         except Exception as error:             self.write_log('最后一段时间错误:\n' + repr(error)) if __name__ == '__main__':     if not os.path.exists('.//DATA'):         os.mkdir('.//DATA')     app = QApplication(sys.argv)     mainwin = mainwindows()     sys.exit(app.exec_())