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()