Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python stops without errors
#1
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()
Reply
#2
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()
Reply
#3
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!
Reply
#4
Also, you did make sure to run all your tests after refactoring, right? ;).
Reply
#5
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?
Feel like you're not getting the answers you want? Checkout the help/rules for things like what to include/not include in a post, how to use code tags, how to ask smart questions, and more.

Pro-tip - there's an inverse correlation between the number of lines of code posted and my enthusiasm for helping with a question :)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Rmarkdown opened by python code - errors Rav013 0 398 Apr-27-2021, 03:13 PM
Last Post: Rav013
  Python Request Errors PythonNoob1998 7 684 Jan-07-2021, 05:18 PM
Last Post: buran
  IDLE stops responding upon saving tompi1 2 537 Oct-01-2020, 05:44 PM
Last Post: Larz60+
  Python timer script stops before should ozstar 3 871 May-04-2020, 12:55 AM
Last Post: ozstar
  Pip Syntax Errors in CMD: Windows 10 and Python 3.8.1 jamesphopper 2 2,453 Feb-08-2020, 07:21 PM
Last Post: jamesphopper
  Can the comments produce errors in python? newbieAuggie2019 9 1,285 Nov-26-2019, 12:19 AM
Last Post: micseydel
  Running into errors when installing pip and pip3 on python 2.7 and python 3.6 tej7gandhi 1 1,441 May-05-2019, 10:37 PM
Last Post: snippsat
  First for loop stops after first iteration Divanova94 10 3,906 May-01-2019, 04:27 PM
Last Post: buran
  Python Thread stops execution neethuvp 1 1,312 Feb-18-2019, 06:36 PM
Last Post: micseydel
  randint stops changing values in a loop Naito 4 1,456 Jan-30-2019, 08:15 AM
Last Post: perfringo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020