offline_alarm.py 976 B

123456789101112131415161718
  1. import numpy as np
  2. import pandas as pd
  3. import datetime,time
  4. from sqlalchemy import create_engine
  5. from sqlalchemy.orm import sessionmaker
  6. from urllib import parse
  7. def offline_alarm(db_iotp_engine, db_qxcas_engine, ignore_sns):
  8. sql = "select t2.sn, t2.status_time as last_time, t2.offline_time_hour from " + \
  9. "(SELECT t.*,t1.devcode,t1.update_time,t1.status_time,TIMESTAMPDIFF(hour, status_time, now()) as offline_time_hour from operation_sn t LEFT JOIN " + \
  10. "ff_battery_status t1 ON(t.sn = t1.devcode)) t2 where t2.offline_time_hour > 72 order by t2.offline_time_hour"
  11. df_alarm_lastday = pd.read_sql(sql, db_iotp_engine)
  12. df_alarm_lastday = df_alarm_lastday[['sn', 'last_time']]
  13. df_alarm_lastday = df_alarm_lastday[~df_alarm_lastday['sn'].isin(ignore_sns)]
  14. df_alarm_lastday['level'] = 1
  15. df_alarm_lastday['add_time'] = datetime.datetime.now()
  16. df_alarm_lastday.to_sql("offline_info", db_qxcas_engine,if_exists="append", index=False)