123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204 |
- # -*- coding: UTF-8 -*-
- '''
- @Project :jh5sonar_newest
- @File :push_status.py
- @Author :Fang
- @Date :2024/8/14
- '''
- import configparser
- import json
- import os
- import time
- from datetime import datetime
- import pymysql
- # 数据格式化 fields 字段名,result 结果集
- def format_data(fields, result):
- # 字段数组 ['id', 'name', 'password']
- field = []
- for i in fields:
- field.append(i[0])
- # 返回的数组集合 形式[{'id': 1, 'name': 'admin', 'password': '123456'}]
- res = []
- for iter in result:
- line_data = {}
- for index in range(0, len(field)):
- line_data[field[index]] = iter[index]
- res.append(line_data)
- return res
- # ping声呐
- def get_ping_result(ip_list):
- # ip_list: 列表:['192.168.1.51','192.168.1.52']
- # 返回值;0或1
- for ip in ip_list:
- # ping左目,-c:尝试次数,-w:等待时间(秒)
- result = os.popen('ping -c 1 -w 1 ' + ip)
- # ping通后,会有标志性字符串:"1 received",返回子串起始位置,否则返回-1
- r1 = result.read().find('1 received')
- # ping不通,返回0
- if r1 < 0:
- return str(0)
- # 都能ping通,返回1
- return str(1)
- def get_message_from_db(config):
- message = {}
- sections = config.sections()
- print(path + "/push.conf")
- print(sections)
- if 'device_info' in sections:
- try:
- message['serial_number'] = config.get("device_info", "serial_number")
- message['device_name'] = config.get("device_info", "device_name")
- message['project_name'] = config.get("device_info", "project_name")
- except Exception as e:
- print(e)
- if 'db' in sections:
- try:
- db_host = config.get("db", "db_host")
- db_user = config.get("db", "db_user")
- db_pass = config.get("db", "db_pass")
- db_name = config.get("db", "db_name")
- # 打开数据库连接
- db = pymysql.connect(host=db_host, user=db_user, password=db_pass, database=db_name)
- print(db)
- except Exception as e:
- print(e)
- if 'data_rows' in sections:
- try:
- table_name = config.get("data_rows", "table_name")
- date_column_name = config.get("data_rows", "date_column_name")
- # 使用cursor()方法获取操作游标
- cursor = db.cursor()
- # SQL 查询语句
- # date = time.strftime('%Y-%m-%d')
- # sql = "SELECT count(*) FROM " + table_name + " WHERE DATE_FORMAT(" + date_column_name + " ,'%Y-%m-%d') = '" + date + "';"
- sql = "SELECT count(*) FROM " + table_name + " WHERE " + date_column_name + " >= ( NOW( ) - INTERVAL 24 HOUR );"
- try:
- # 执行SQL语句
- cursor.execute(sql)
- # 获取所有记录列表
- results = cursor.fetchall()
- message['data_rows'] = results[0][0]
- except:
- message['data_rows'] = 0
- print("Error: unable to fetch data")
- except Exception as e:
- print(e)
- if 'ping' in sections:
- try:
- ip_list = config.get("ping", "ip_list")
- ip_list = ip_list.split(",")
- ping_result = get_ping_result(ip_list)
- message['ping'] = ping_result
- except Exception as e:
- print(e)
- if 'latest_data' in sections:
- try:
- table_name = config.get("latest_data", "table_name")
- column_name_list = config.get("latest_data", "column_name_list")
- column_order_by = config.get("latest_data", "order_by")
- # 使用cursor()方法获取操作游标
- cursor = db.cursor()
- # SQL 查询语句
- date = time.strftime('%Y-%m-%d')
- sql = "SELECT " + column_name_list + " FROM " + table_name + " order by " + column_order_by + " desc limit 1;"
- try:
- # 执行SQL语句
- cursor.execute(sql)
- result = cursor.fetchall()
- fields = cursor.description
- res = format_data(fields, result)
- message['latest_data'] = res[0]
- except:
- print("Error: unable to fetch data")
- except Exception as e:
- print(e)
- if message['ping'] == '1':
- message['last_offline_time'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
- message_json = json.dumps(message, default=str)
- return message_json
- def save_message(db, message_json):
- cursor = db.cursor()
- message_json = json.loads(message_json)
- up_date = time.strftime('%Y-%m-%d')
- serial_number = message_json['serial_number']
- device_name = message_json['device_name']
- project_name = message_json['project_name']
- data_rows = message_json['data_rows']
- latest_data = json.dumps(message_json['latest_data'])
- device_status = message_json['ping']
- if message_json['ping'] == '1':
- # SQL 插入语句
- sql = "REPLACE INTO device_status (project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status) VALUES ('%s','%s','%s', '%s', %s, '%s', %s)" % (
- project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status)
- elif message_json['ping'] == '0':
- sql = f"SELECT last_offline_time FROM device_status " \
- f"WHERE project_name='{project_name}' and device_name='{device_name}' ORDER BY date_time DESC LIMIT 1;"
- cursor.execute(sql)
- result = cursor.fetchall()
- offline_time = result[0][0]
- if offline_time ==None:
- last_offline_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
- else:
- last_offline_time = offline_time
- sql = "REPLACE INTO device_status (project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status,last_offline_time) VALUES ('%s','%s','%s', '%s', %s, '%s', '%s', '%s')" % (
- project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status,last_offline_time)
- print(sql)
- try:
- # 执行sql语句
- cursor.execute(sql)
- # 提交到数据库执行
- db.commit()
- except Exception as e:
- print(e)
- # 关闭数据库连接
- db.close()
- if __name__ == '__main__':
- # db_host = "101.43.128.27"
- # db_user = "sencott"
- # db_pass = "sencott"
- db_host = "127.0.0.1"
- db_user = "root"
- db_pass = "123456"
- db_name = "sencott_device_adminer_db"
- # 打开数据库连接
- db = pymysql.connect(host=db_host, user=db_user, password=db_pass, database=db_name)
- path = os.path.abspath(os.path.dirname(__file__))
- config = configparser.ConfigParser()
- config.read(path + "/push.conf", encoding="utf-8")
- print(path + "/push.conf")
- print(config.sections())
- message_json = get_message_from_db(config)
- save_message(db, message_json)
- '''
- headers = {'Content-Type': 'application/json'}
- r = requests.post("http://localhost/sencott_device_adminer/saveMessage.php", data=message_json, headers=headers)
- print(r.text)
- '''
|