# -*- 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) '''