目的:判断旧装置更换时在线情况
方法:通过一天所上传电流数据来对比;
1、按系统来抓取;
2、先在系统db库里将1832开头的装置编号与kid号获取到,列个表,然后拿着这个表在223里匹配投运时间,再将投运时间反推两天,再在his表里以kid和时间来匹配电流数据。最后加上判断。
一、SQL
1、筛选
二、SQL报错:刚开始怀疑是由于频繁连数据库导致访问遭拒绝,参考此报告后发现是SQL写错了。
File "G:/station/.idea/装置通讯断开情况.py", line 42, in <module>
cur.execute(check_sql3)
File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
pymssql.ProgrammingError: (102, b"Incorrect syntax near '4030000000001'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
三、脚本
-- coding:UTF-8 -- import os import pymssql import cx_Oracle import arrow #导时间戳的模块 import xlwt DEVICE = [] KID = [] TIME = [] os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8' conn = pymssql.connect(host=('****'), user='**', password='***', database='TAS3.2_DB_XA') cur = conn.cursor() check_sql1 = "SELECT liXHQDA21_DeviceID,sFactoryNum FROM XHQDA21_Device where sFactoryNum LIKE '1832%'" #DB库中拿装置编号与KID号 cur.execute(check_sql1) A = cur.fetchall() print(A) for B in A: c = B[1] G = B[0] DEVICE.append(c) KID.append(G) print(DEVICE) for F in DEVICE: print(F) # 链接orcal库,获取投运时间 tns = cx_Oracle.makedsn('***********, 'orcl') db = cx_Oracle.connect('*****', '***********', tns) cur = db.cursor() check_sql2 = "SELECT TIME_TOU FROM APP_DEVICE WHERE DEVICE_CODE = %s "%F #总用户数量 cur.execute(check_sql2) TIME_TOU = cur.fetchall() Datetime = TIME_TOU[0][0] TIME.append(Datetime) TIME_duan1 = arrow.get(Datetime).timestamp*1000 - 86400*2 #将datetime格式变为时间戳 TIME_duan2 = arrow.get(Datetime).timestamp * 1000 - 86400 * 3 # print(TIME_duan) 抓取电流数据数量 conn1 = pymssql.connect(host=('****'), user='**', password='*****', database='TAS3.2_HISXA') cur1 = conn1.cursor() for kid in KID: Kd = str(kid) KiD = 'dev'+Kd print(KiD) check_sql3 = "SELECT field_26 FROM " +KiD+ " WHERE TasTimeStamp >= " + str(TIME_duan2) + " and TasTimeStamp <= " + str(TIME_duan1) #DB库中拿装置编号与KID号 cur1.execute(check_sql3) p = cur1.fetchall() P = len(p) print(P) def save_excel(fin_result, tag_name, file_name): book = Workbook(encoding='utf-8') tmp = book.add_sheet('陕西加密装置电池电压装置断开情况明细') times = len(fin_result) + 1 for i in range(times): # i代表的是行,i+1代表的是行首信息 if i == 0: for tag_name_i in tag_name: tmp.write(i, tag_name.index(tag_name_i), tag_name_i) else: for tag_list in range(len(tag_name)): tmp.write(i, tag_list, str(fin_result[i - 1][tag_list])) book.save(r'C:\Users\Administrator\Desktop\%s.xls' % file_name) conn.close()
注:脚本可以实现目的,但依然存在很大不足
1、运行速度较慢
2、只能一个系统一个系统的抓,不能实现自动切换抓取
3、数据未存入excel