Posts: 2
Threads: 1
Joined: Feb 2020
Feb-04-2020, 06:47 AM
(This post was last modified: Feb-04-2020, 12:01 PM by Larz60+.)
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.
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()
Posts: 2
Threads: 1
Joined: Feb 2020
I have 9 Raspberry Pi connected to 9 machines physically (via cables). These Raspberry Pi collects data from these machines and send it to MQTT broker (mosquitto-Server) running on different PC (Lets call it Server PC). The script which I posted above runs on Server PC. I run this script with the help of Python IDLE. Also, the script was running fine some days ago but I changed the following things: Previously there were 4 on_message callbacks which I made it to 1. A Little sophisticated way to check the Connection to the Clients (by sending Server/Connection). The script above runs on Python shell till 1-2 Hours but suddenly it stops without Errors.
# Import libraries.....
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.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()
# --------------------------------------------- Main loop -----------------------------------
while True:
# -------------------- Send Server Online and Time to Clients --------------------
if (time.time()-LastTime1)>=DeltaT1:
client.publish('Server/Connection', 'Online')
client.publish('Server/Time', str(time.time()))
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()
# -------------------- Restore buffered Data --------------------
if (time.time()-LastTime4)>=DeltaT4:
## try:
if True:
cursor2.execute("select * from TimeData")
rows = cursor2.fetchall()
print("Buffer Database - TimeData has "+str(len(rows))+" rows to restore")
if len(rows)>0:
print("Restoring TimeData")
sqlcmd="INSERT INTO Production_DE.dbo.TimeData (CTime, Unit, CycleCount,Ur, CycleTime, ClientId, PTopic, IpAdress, Perc) VALUES (?,?,?,?,?,?,?,?,?)"
cursor1.executemany(sqlcmd,rows)
connection.commit()
cursor2.execute("delete from TimeData")
connection2.commit()
cursor2.execute("Select * from MachineData")
rows = cursor2.fetchall()
print("Buffer Database - MachineData has "+str(len(rows))+" rows to restore")
if len(rows)>0:
print("Restoring MachineData")
sqlcmd="INSERT INTO Production_DE.dbo.MDaten (CTime,MText,Ip,Ml,Temp1,Temp2,Temp3,Temp4,Temp5,Temp6,Temp7,Text1,Value1, DeltaTUpl, DeltaPulse) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cursor1.executemany(sqlcmd,rows)
connection.commit()
cursor2.execute("delete from MachineData")
connection2.commit()
cursor2.execute("Select * from PlcData")
rows = cursor2.fetchall()
print("Buffer Database - PlcData has "+str(len(rows))+" rows to restore")
if len(rows)>0:
print("Restoring PlcData")
sqlcmd="INSERT INTO Production_DE.dbo.PlcDaten (PTime,MText,Ip,Ml,Prod,Status1,Status2) VALUES (?,?,?,?,?,?,?)"
cursor1.executemany(sqlcmd,rows)
connection.commit()
cursor2.execute("delete from PlcData")
connection2.commit()
cursor2.execute("Select * from QualityData")
rows = cursor2.fetchall()
print("Buffer Database - QualityData has "+str(len(rows))+" rows to restore")
if len(rows)>0:
print("Restoring QualityData")
sqlcmd=("""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]
,[QData3], [QData4], [QData5], [Produkt_Nummer], [Fehler_Code], [QData8],[QData9],[QData10] )
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""")
cursor1.executemany(sqlcmd,rows)
connection.commit()
cursor2.execute("delete from QualityData")
connection2.commit()
## except:
## ("Waiting to connect to the XDB01 Database")
LastTime4=time.time()
Posts: 1,835
Threads: 2
Joined: Apr 2017
Feb-04-2020, 12:51 PM
(This post was last modified: Feb-04-2020, 12:51 PM by ndc85430.)
There's a lot of code there and I think you'll be hard pressed to get someone to debug it for you.
Perhaps a few things to try are:
- More logging to see what the application is doing at the time it gets stuck
- Introducing timeouts on the calls to the external systems (I'd hope the APIs you're using to talk to those allow you to set timeouts!).
You really want to gather more data to help diagnose the problem. If you haven't read it, Nygard's Release It! is a must read to learn about these kinds of problems and patterns to solve them.
Edit: in the above, I interpreted you saying "it stops suddenly" as "it hangs", as opposed to "it crashes". Timeouts should help with the former, though not with the latter. In any case, more data is required!
Posts: 1,835
Threads: 2
Joined: Apr 2017
Also, you did make sure to run all your tests after refactoring, right? ;).
Posts: 2,342
Threads: 62
Joined: Sep 2016
I normally don't read the code when it's this long, but anytime you see except: that's a problem. You should never blanket-catch and then swallow exceptions like that. Ideally, you should catch specific exceptions you expect, and if you truly need a blanket-catch, then you should at least log that things happened.
Did you write this code yourself, or did someone else?
|
