97 lines
5.2 KiB
Python
Executable File
97 lines
5.2 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
import sys
|
|
import os
|
|
from mysql.connector import MySQLConnection, Error
|
|
from dbconfig import read_db_config
|
|
from datetime import datetime
|
|
|
|
def insertData(dati):
|
|
#print(dati)
|
|
if dati != "null":
|
|
query = "INSERT 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:
|
|
folder_path, file_with_extension = os.path.split(pathFile)
|
|
file_name, _ = os.path.splitext(file_with_extension)#toolname
|
|
serial_number = file_name.split("_")[0]
|
|
query = "SELECT unit_name, tool_name FROM vulink_tools WHERE serial_number=%s"
|
|
query_node_depth = "SELECT depth FROM ase_lar.nodes as n left join tools as t on n.tool_id=t.id left join units as u on u.id=t.unit_id where u.name=%s and t.name=%s and n.nodetype_id=2"
|
|
db_config = read_db_config()
|
|
conn = MySQLConnection(**db_config)
|
|
cursor = conn.cursor()
|
|
cursor.execute(query, [serial_number])
|
|
result = cursor.fetchall()
|
|
unit = result[0][0]
|
|
tool = result[0][1]
|
|
cursor.execute(query_node_depth, [unit, tool])
|
|
resultNode = cursor.fetchall()
|
|
node_depth = float(resultNode[0][0]) #node piezo depth
|
|
with open(pathFile, 'r', encoding='ISO-8859-1') as file:
|
|
data = file.readlines()
|
|
data = [row.rstrip() for row in data]
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
data.pop(0) #rimuove header
|
|
for row in data:
|
|
row = row.split(",")
|
|
date_time = datetime.strptime(row[1], '%Y/%m/%d %H:%M').strftime('%Y-%m-%d %H:%M')
|
|
date_time = date_time.split(" ")
|
|
date = date_time[0]
|
|
time = date_time[1]
|
|
temperature_unit = float(row[2])
|
|
battery_perc = float(row[3])
|
|
pressure_baro = float(row[4])*1000#(kPa) da fare *1000 per Pa in elab->pressure
|
|
conductivity = float(row[6])
|
|
ph = float(row[11])
|
|
temperature_piezo = float(row[14])
|
|
pressure = float(row[16])*1000
|
|
depth = (node_depth * -1) + float(row[17])#da sommare alla quota del nodo (quota del nodo fare *-1)
|
|
queryInsRaw = "INSERT IGNORE INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, Val0) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
queryInsElab = "INSERT IGNORE INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, pressure) VALUES(%s,%s,%s,%s,%s,%s)"
|
|
cursor.execute(queryInsRaw, [unit, tool, 1, date, time, battery_perc, temperature_unit, pressure_baro])
|
|
cursor.execute(queryInsElab, [unit, tool, 1, date, time, pressure_baro])
|
|
queryInsRaw = "INSERT IGNORE INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, Val0) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
queryInsElab = "INSERT IGNORE INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, XShift) VALUES(%s,%s,%s,%s,%s,%s)"
|
|
cursor.execute(queryInsRaw, [unit, tool, 2, date, time, battery_perc, temperature_unit, conductivity])
|
|
cursor.execute(queryInsElab, [unit, tool, 2, date, time, conductivity])
|
|
queryInsRaw = "INSERT IGNORE INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, Val0) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
queryInsElab = "INSERT IGNORE INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, XShift) VALUES(%s,%s,%s,%s,%s,%s)"
|
|
cursor.execute(queryInsRaw, [unit, tool, 3, date, time, battery_perc, temperature_unit, ph])
|
|
cursor.execute(queryInsElab, [unit, tool, 3, date, time, ph])
|
|
queryInsRaw = "INSERT IGNORE INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, Val0, Val1, Val2) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
queryInsElab = "INSERT IGNORE INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, T_node, water_level, pressure) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
|
|
cursor.execute(queryInsRaw, [unit, tool, 4, date, time, battery_perc, temperature_unit, temperature_piezo, depth, pressure])
|
|
cursor.execute(queryInsElab, [unit, tool, 4, date, time, temperature_piezo, depth, pressure])
|
|
conn.commit()
|
|
except Error as e:
|
|
print('Error:', e)
|
|
def main():
|
|
getDataFromCsv(sys.argv[1])
|
|
if __name__ == '__main__':
|
|
main()
|