Hi,
I have a program which collects data from MQTT broker and stores into SQL Database. When Connection to SQL DB is lost then it stores into local SQLite DB.
This program has to run 24x7 but it stops suddenly after 1-2 Hours without any Errors.
I have a program which collects data from MQTT broker and stores into SQL Database. When Connection to SQL DB is lost then it stores into local SQLite DB.
This program has to run 24x7 but it stops suddenly after 1-2 Hours without any Errors.
import paho.mqtt.client as mqtt import time import pyodbc import datetime import sqlite3 # ------------- Global Variable Declaration ---------------- LastTime1 = 0 LastTime2 = 0 LastTime3 = 0 LastTime4 = 0 LastTime5 = 0 DeltaT1 = 5 DeltaT2 = 10 DeltaT3 = 20 DeltaT4 = 5 DeltaT5 = 300 Days = 180 w = 3 Clients = 9 Data = [[0 for x in range(w)] for y in range(Clients+1)] for y in range (1,Clients+1): Data[y][0] = "Client"+str(y) Data[y][1] = False Data[y][2] = 0 # ----------------------------- MQTT callbacks ------------------------------- def on_connect(client, userdata, flags, rc): print('Connected with result code {0}'.format(rc)) for y in range (1,Clients+1): client.subscribe(Data[y][0]+"/#") def on_message(client, userdata, msg): global Data myTopic = str(msg.topic) myTopic=myTopic[myTopic.index("/")+1:len(myTopic)] myText=str(msg.payload) myText=myText[myText.index("'")+1:len(myText)-1] cursor1 = connection.cursor() for y in range (1,Clients+1): if msg.topic == Data[y][0]+"/TimeData": Data[y][1]=True # Connection Data[y][2]=time.time() # Last Message if myTopic == 'TimeData': print("------------------------------------------------ Time Data ---------------------------------------------------------------------\n") myText1=myText.split(",") print(myText) try: # Database cursor1.execute("INSERT INTO Production_DE.dbo.TimeData (CTime, Unit, CycleCount,Ur, CycleTime, ClientId, PTopic, IpAdress, Perc, DeltaTUpl, DeltaPh, Reason) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6], myText1[7], myText1[8], myText1[9], myText1[10], myText1[11])) connection.commit() print("Database - XDB01 OK") except: print('------------ >>>> Error Database - XDB01') try: #OpenDb2 cursor2.execute("INSERT INTO TimeData (CTime, Unit, CycleCount,Ur, CycleTime, ClientId, PTopic, IpAdress, Perc, DeltaTUpl, DeltaPh, Reason) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6], myText1[7], myText1[8], myText1[9], myText1[10], myText1[11])) connection2.commit() print("Database Buffer ok") #closeDb2 except: print("Error Buffer Database") if myTopic == 'MachineData': print("------------------------------------------------------ Machine Data -------------------------------------------------------------------\n") myText1=myText.split(",") try: # Database 1 2 3 4 5 6 7 8 9 10 11 cursor1.execute("INSERT INTO Production_DE.dbo.MDaten (CTime,MText,Ip,Ml,Temp1,Temp2,Temp3,Temp4,Temp5,Temp6,Temp7,Text1,Value1, DeltaTUpl, DeltaPulse) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6], myText1[7], myText1[8], myText1[9], myText1[10], myText1[11],myText1[12],myText1[13],myText1[14])) connection.commit() print("Database - XDB01 OK") except: print('------------ >>>> Error Database XDB01') try: #OpenDb2 cursor2.execute("INSERT INTO MachineData (CTime,Unit,Ip,Ur,Temp1,Temp2,Temp3,Temp4,Temp5,Temp6,Temp7,Text1,Value1, DeltaTUpl, DeltaPulse) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6], myText1[7], myText1[8], myText1[9], myText1[10], myText1[11],myText1[12],myText1[13],myText1[14])) connection2.commit() print("Database Buffer ok") #CloseDb2 except: print("Error Buffer Database") if myTopic == 'PlcData': print("-------------------------------------------- Plc Data --------------------------------------------------------------\n") myText1=myText.split(",") print(myText) try: # Database cursor1.execute("INSERT INTO Production_DE.dbo.PlcDaten (PTime,MText,Ip,Ml,Prod,Status1,Status2) VALUES (?,?,?,?,?,?,?)", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6])) connection.commit() print("Database - XDB01 OK") except: print('------------ >>>> Error Database XDB01') try: #OpenDb2 cursor2.execute("INSERT INTO PlcData (CTime,Unit,IpAdress,Ur,Prod,Status1,Status2) VALUES (?,?,?,?,?,?,?)", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6])) connection2.commit() print("Database Buffer OK") #CloseDb2 except: print("Error Buffer Database") if myTopic == 'QualityData': print("----------------------------------------------- Quality Data -----------------------------------------------------------------\n") myText1=myText.split(",") print(myText) try: # Database ## if True: cursor1.execute("""INSERT INTO Production_DE.dbo.QualityData ([QTime],[UnitId],[Status_HS],[WT_bestückt],[WT_vorh],[WT_IO],[WT_NIO],[WT_NIO_Station],[WT_Fehlercode],[WT_Nummer],[WT_Teile_vorh],[WT_falscher_Typ],[Lauf_Nummer] ,[M03_Stückzahl],[M03_Nietzeit],[M03_Rohmass],[M03_Fertigmass],[M03_Nietdruck],[M03_Motordrehzahl],[M04_Stückzahl],[M04_Nietzeit],[M04_Rohmass],[M04_Fertigmass] ,[M04_Nietdruck],[M04_Motordrehzahl],[M05_Stückzahl],[M05_Nietzeit],[M05_Rohmass],[M05_Fertigmass],[M05_Nietdruck],[M05_Motordrehzahl],[M06_Stückzahl],[M06_Nietzeit] ,[M06_Rohmass],[M06_Fertigmass],[M06_Nietdruck],[M06_Motordrehzahl],[M08_Schweissbuckel_1],[M08_Schweissbuckel_2],[M08_Schweissbuckel_3],[M08_Schweissbuckel_4] ,[M08_Schweissbuckel_5],[M08_Schweissbuckel_6],[M08_Schweissbuckel_7],[M08_Schweissbuckel_8],[M08_Schweissbuckel_9],[M08_Schweissbuckel_10],[M08_Schweissbuckel_11] ,[M08_Schweissbuckel_12],[M08_Schweissbuckel_13],[M08_Schweissbuckel_14],[M08_Schweissbuckel_15],[M08_Schweissbuckel_16],[M08_Nietkopf_DM_1],[M08_Nietkopf_DM_2] ,[M08_Nietkopf_DM_3],[M08_Nietkopf_DM_4],[M08_Ebenheit],[M08_Aufnahmebohr_1],[M08_Aufnahmebohr_2],[M08_Aufnahmebohr_3],[M08_Aufnahmebohr_4],[M08_Gesamtbreite_1] ,[M08_Gesamtbreite_2],[M08_Tiefe_Kalotte_1],[M08_Tiefe_Kalotte_2],[M08_Tiefe_Kalotte_3],[M08_Tiefe_Kalotte_4], [M08_Abstand_Bohrung_1], [M08_Abstand_Bohrung_2] ,[Produkt_Nummer], [Fehler_Code]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6], myText1[7], myText1[8], myText1[9], myText1[10], myText1[11], myText1[12] , myText1[13], myText1[14], myText1[15], myText1[16], myText1[17], myText1[18], myText1[19], myText1[20], myText1[21], myText1[22], myText1[23], myText1[24], myText1[25] , myText1[26], myText1[27], myText1[28], myText1[29], myText1[30], myText1[31], myText1[32], myText1[33], myText1[34], myText1[35], myText1[36], myText1[37], myText1[38] , myText1[39], myText1[40], myText1[41], myText1[42], myText1[43], myText1[44], myText1[45], myText1[46], myText1[47], myText1[48], myText1[49], myText1[50], myText1[51] , myText1[52], myText1[53], myText1[54], myText1[55], myText1[56], myText1[57], myText1[58], myText1[59], myText1[60], myText1[61], myText1[62], myText1[63], myText1[64] , myText1[65], myText1[66], myText1[67], myText1[68], myText1[69], myText1[70], myText1[71])) connection.commit() print("----------------------------------------Done-----------------------------------------------") print("Database - XDB01 OK") except: print('------------ >>>> Error Database XDB01') try: #OpenDb2 cursor2.execute("""INSERT INTO QualityData ([QTime],[UnitId],[Status_HS],[WT_bestückt],[WT_vorh],[WT_IO],[WT_NIO],[WT_NIO_Station],[WT_Fehlercode],[WT_Nummer],[WT_Teile_vorh],[WT_falscher_Typ],[Lauf_Nummer] ,[M03_Stückzahl],[M03_Nietzeit],[M03_Rohmass],[M03_Fertigmass],[M03_Nietdruck],[M03_Motordrehzahl],[M04_Stückzahl],[M04_Nietzeit],[M04_Rohmass],[M04_Fertigmass] ,[M04_Nietdruck],[M04_Motordrehzahl],[M05_Stückzahl],[M05_Nietzeit],[M05_Rohmass],[M05_Fertigmass],[M05_Nietdruck],[M05_Motordrehzahl],[M06_Stückzahl],[M06_Nietzeit] ,[M06_Rohmass],[M06_Fertigmass],[M06_Nietdruck],[M06_Motordrehzahl],[M08_Schweissbuckel_1],[M08_Schweissbuckel_2],[M08_Schweissbuckel_3],[M08_Schweissbuckel_4] ,[M08_Schweissbuckel_5],[M08_Schweissbuckel_6],[M08_Schweissbuckel_7],[M08_Schweissbuckel_8],[M08_Schweissbuckel_9],[M08_Schweissbuckel_10],[M08_Schweissbuckel_11] ,[M08_Schweissbuckel_12],[M08_Schweissbuckel_13],[M08_Schweissbuckel_14],[M08_Schweissbuckel_15],[M08_Schweissbuckel_16],[M08_Nietkopf_DM_1],[M08_Nietkopf_DM_2] ,[M08_Nietkopf_DM_3],[M08_Nietkopf_DM_4],[M08_Ebenheit],[M08_Aufnahmebohr_1],[M08_Aufnahmebohr_2],[M08_Aufnahmebohr_3],[M08_Aufnahmebohr_4],[M08_Gesamtbreite_1] ,[M08_Gesamtbreite_2],[M08_Tiefe_Kalotte_1],[M08_Tiefe_Kalotte_2],[M08_Tiefe_Kalotte_3],[M08_Tiefe_Kalotte_4], [M08_Abstand_Bohrung_1], [M08_Abstand_Bohrung_2] ,[Produkt_Nummer], [Fehler_Code]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (myText1[0], myText1[1], myText1[2], myText1[3], myText1[4], myText1[5], myText1[6], myText1[7], myText1[8], myText1[9], myText1[10], myText1[11], myText1[12] , myText1[13], myText1[14], myText1[15], myText1[16], myText1[17], myText1[18], myText1[19], myText1[20], myText1[21], myText1[22], myText1[23], myText1[24], myText1[25] , myText1[26], myText1[27], myText1[28], myText1[29], myText1[30], myText1[31], myText1[32], myText1[33], myText1[34], myText1[35], myText1[36], myText1[37], myText1[38] , myText1[39], myText1[40], myText1[41], myText1[42], myText1[43], myText1[44], myText1[45], myText1[46], myText1[47], myText1[48], myText1[49], myText1[50], myText1[51] , myText1[52], myText1[53], myText1[54], myText1[55], myText1[56], myText1[57], myText1[58], myText1[59], myText1[60], myText1[61], myText1[62], myText1[63], myText1[64] , myText1[65], myText1[66], myText1[67], myText1[68], myText1[69], myText1[70], myText1[71])) connection2.commit() print("Database Buffer OK") #CloseDb2 except: print("Error Buffer Database") # ------------------------ MQTT connection ------------------------ client = mqtt.Client(Client_id = 'Server') client.on_connect = on_connect # Specify on_connect callback client.on_message = on_message # Specify on_message callback try: print("Connecting to MQTT") client.connect('localhost', 1883, 60) except: print("Error connecting to MQTT") client.loop_start() connection = pyodbc.connect('Driver={SQL Server};' 'Server=XDB01;' 'Database=Production_DE;' 'Trusted_Connection=yes;') cursor1 = connection.cursor() connection2 = sqlite3.connect("C:/Users/xabreoli/Documents/PythonFiles/PythonProg/Production_DE.db") cursor2 = connection2.cursor() # --------------------------------------------- Hauptschleife ----------------------------------- while True: # -------------------- Send Server Online and Time to Clients -------------------- if (time.time()-LastTime1)>=DeltaT1: client.publish('Server/Connection', 'Online') LastTime1=time.time() # -------------------- Check connection to Clients -------------------- if (time.time()-LastTime2)>=DeltaT2: for y in range (1,Clients+1): if time.time() - Data[y][2] > 65: Data[y][1] = False # Connection print(Data[y][0]+" Connection : "+str(Data[y][1])) LastTime2=time.time()