#!/usr/bin/env python3 import sys import os from mysql.connector import MySQLConnection, Error from dbconfig import read_db_config from collections import defaultdict def insertData(dati): print("dati: ",dati) if dati != "null" and dati is not None: query = "INSERT IGNORE INTO ELABDATANESA(UnitName, ToolNameID, NodeNum, EventTimestamp, dataJSON) " \ "VALUES(%s,%s,%s,%s,%s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, dati) conn.commit() except Error as e: print('Error:', e) finally: os.system("cd /usr/local/matlab_func/; ./run_Tilt_lnx.sh /usr/local/MATLAB/MATLAB_Runtime/v93/ "+dati[0][0]+" "+dati[0][1]+"") cursor.close() conn.close() def getDataFromCsv(pathFile): try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor(dictionary=True) with open(pathFile, 'r') as file: data = file.readlines() data = [row.rstrip() for row in data] #data.pop(0) #rimuove header dati = [] i = 0 unit = "" tool = "" #query = "SELECT tool_id, nodetype_id, nt.type as nodetype_name, num FROM nodes as n join nodetypes as nt on nt.id=n.nodetype_id join tools as t on t.id=n.tool_id join units as u on u.id=t.unit_id where u.name=%s and t.name=%s" #cursor.execute(query, [unit, tool]) #resultNodi = cursor.fetchall() #resultNodi_dict = {item['nodetype_id']: item for item in resultNodi} #print(resultNodi_dict) for row in data: row = row.split(",") #print(row) #print("-------") if i == 0: serial_number = row[1] if serial_number[0] == "0": serial_number = serial_number[1:] #rimuove primo char (se 0) del id nesa query = "SELECT unit_name, tool_name FROM nesa_tools WHERE serial_number='"+serial_number+"'" cursor.execute(query) result = cursor.fetchall() if(len(result) > 0): unit = result[0]["unit_name"] tool = result[0]["tool_name"] query = "SELECT tool_id, nodetype_id, nt.type as nodetype_name, num FROM nodes as n join nodetypes as nt on nt.id=n.nodetype_id join tools as t on t.id=n.tool_id join units as u on u.id=t.unit_id where u.name=%s and t.name=%s" cursor.execute(query, [unit, tool]) resultNodi = cursor.fetchall() resultNodi_dict = {item['nodetype_id']: item for item in resultNodi} #print(result[0][0]) #print(result[0][1]) if(unit != "" and tool != ""): date = row[7]+"-"+row[6]+"-"+row[5]+" "+row[2]+":"+row[3]+":"+row[4] row = row[:-1] chunks = [row[i:i+3] for i in range(8, len(row), 3)]# Start from index 8 and split into chunks of 3 battery = -1 solarPanel = -1 grouped = defaultdict(list) for chunk in chunks: key = chunk[0] # First value as the key grouped[key].append(chunk[1:]) # Store the rest of the values #print(grouped) if("108" in grouped): battery = grouped["108"][0][1] if("158" in grouped): solarPanel = grouped["158"][0][1] if("1" in grouped): nodetype_id_to_find = 4 res = resultNodi_dict.get(nodetype_id_to_find) # Returns None if not found if(res is not None): node_num = res["num"] tempAvg = 0 tempMin = 0 tempMax = 0 for item in grouped["1"]: key, value = item if key == '2': tempAvg = value elif key == '3': tempMin = value elif key == '4': tempMax = value #print("therm: ", tempAvg, tempMin, tempMax, " nodenum: ", node_num) dataJSON = '{ "battery":"'+battery+'", "solarPanel":"'+solarPanel+'", "tempAvg":"'+tempAvg+'", "tempMin":"'+tempMin+'", "tempMax":"'+tempMax+'"}' dati.append((unit, tool, node_num, date, dataJSON)) if("2" in grouped): nodetype_id_to_find = 35 res = resultNodi_dict.get(nodetype_id_to_find) # Returns None if not found if(res is not None): node_num = res["num"] humidityAvg = 0 humidityMin = 0 humidityMax = 0 for item in grouped["2"]: key, value = item if key == '2': humidityAvg = value elif key == '3': humidityMin = value elif key == '4': humidityMax = value #print("humidity: ", humidityAvg, humidityMin, humidityMax, " nodenum: ", node_num) dataJSON = '{ "battery":"'+battery+'", "solarPanel":"'+solarPanel+'", "humAvg":"'+humidityAvg+'", "humMin":"'+humidityMin+'", "humMax":"'+humidityMax+'"}' dati.append((unit, tool, node_num, date, dataJSON)) if("3" in grouped): nodetype_id_to_find = 69 res = resultNodi_dict.get(nodetype_id_to_find) # Returns None if not found if(res is not None): node_num = res["num"] pyraAvg = 0 pyraMin = 0 pyraMax = 0 for item in grouped["3"]: key, value = item if key == '2': pyraAvg = value elif key == '3': pyraMin = value elif key == '4': pyraMax = value #print("pyra: ", pyroAvg, pyroMin, pyroMax, " nodenum: ", node_num) dataJSON = '{ "battery":"'+battery+'", "solarPanel":"'+solarPanel+'", "pyraAvg":"'+pyraAvg+'", "pyraMin":"'+pyraMin+'", "pyraMax":"'+pyraMax+'"}' dati.append((unit, tool, node_num, date, dataJSON)) if("4" in grouped): nodetype_id_to_find = 55 res = resultNodi_dict.get(nodetype_id_to_find) # Returns None if not found if(res is not None): node_num = res["num"] windDirAvg = 0 windDirMin = 0 windDirMax = 0 windSpeedAvg = 0 windSpeedMin = 0 windSpeedMax = 0 for item in grouped["4"]: key, value = item if key == '2': windDirAvg = value elif key == '9': windDirMin = value elif key == '8': windDirMax = value for item in grouped["9"]: key, value = item if key == '2': windSpeedAvg = value elif key == '3': windSpeedMin = value elif key == '4': windSpeedMax = value #print("windDir: ", windDirAvg, windDirMin, windDirMax, "-windSpeed: ", windSpeedAvg, windSpeedMin, windSpeedMax, " nodenum: ", node_num) dataJSON = '{ "battery":"'+battery+'", "solarPanel":"'+solarPanel+'", "windDirAvg":"'+windDirAvg+'", "windDirMin":"'+windDirMin+'", "windDirMax":"'+windDirMax+'", "windSpeedAvg":"'+windSpeedAvg+'", "windSpeedMin":"'+windSpeedMin+'", "windSpeedMax":"'+windSpeedMax+'"}' dati.append((unit, tool, node_num, date, dataJSON)) if("10" in grouped): nodetype_id_to_find = 27 res = resultNodi_dict.get(nodetype_id_to_find) # Returns None if not found if(res is not None): node_num = res["num"] rain = 0 for item in grouped["10"]: key, value = item rain = value #print("rain: ", rain, " nodenum: ", node_num) dataJSON = '{ "battery":"'+battery+'", "solarPanel":"'+solarPanel+'", "rain":"'+rain+'"}' dati.append((unit, tool, node_num, date, dataJSON)) if("2013" in grouped): nodetype_id_to_find = 3 res = resultNodi_dict.get(nodetype_id_to_find) # Returns None if not found if(res is not None): node_num = res["num"] pressureAvg = 0 pressureMin = 0 pressureMax = 0 for item in grouped["2013"]: key, value = item if key == '2': pressureAvg = value elif key == '3': pressureMin = value elif key == '4': pressureMax = value #print("pressure: ", pressureAvg, pressureMin, pressureMax, " nodenum: ", node_num) dataJSON = '{ "battery":"'+battery+'", "solarPanel":"'+solarPanel+'", "pressureAvg":"'+pressureAvg+'", "pressureMin":"'+pressureMin+'", "pressureMax":"'+pressureMax+'"}' dati.append((unit, tool, node_num, date, dataJSON)) else: break i+=1 print(dati) return dati except Error as e: print("Error: ", e) def main(): insertData(getDataFromCsv(sys.argv[1])) if __name__ == '__main__': main()