import MySQLdb import string class DBMySQLConnection: def __init__(self, username, password, host, dbname): #initialize at the start all the user parameters self.usern = username self.passw = password self.host = host self.db = dbname self.connectionCreated = 0 self.tasksList = list() global debugMode debugMode = 0 def connectDB(self): try: #try the connection self.datBaseConn=MySQLdb.connect(self.host,self.usern, self.passw,self.db) self.datBaseConn.paramstyle = 'format' self.cur = self.datBaseConn.cursor() #make the cursor, used for sending queries self.connectionCreated = 1 #use it as an indicator that the connection was created return 1 except MySQLdb.Error, e: #if we have an error then try to catch it error=str(e) if error[1:5] == '1045': #wrong username or password return 0 elif error[1:5] == '2002': #can't connect to mysql, mysql shutdown or wrong host return 2 else: if debugMode == 1: print error return 3 def closeDBConn(self): #close the connection to the database here if self.connectionCreated == 1: try: #close the cursor and then the connection to the DB self.cur.close() self.datBaseConn.close() return 1 except MySQLdb.Error, e: #in case of an error if debugMode == 1: error = str(e) print error return 3 else: #I never really had a connection return 0 def anyTasksToDo(self): #see are there any jobs to be executed and make a list out of it if self.connectionCreated == 1: try: self.cur.execute("SELECT * FROM TempTaskTable") output = self.cur.fetchall() #get the mysql response #parse the output from the mysql by creating a list #with lists where each attribue(column) gets independent #element of the list for record in output: columns = list() for entry in record: columns.append(str(entry)) self.tasksList.append(columns) return 1 except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 else: return 0 def cleanTasksList(self): if self.connectionCreated == 1: del self.tasksList[:] return 1 else: return 0 def removeTaskFromList(self, taskID): #remove only one task from the task list if self.connectionCreated == 1: for index in range(len(self.tasksList)): item = self.tasksList[index] if item[0] == str(taskID): #self.tasksList.remove(index) #print 'found it' del self.tasksList[index] return 1 #deleted taskID return 2 #didn't find that taskID else: return 0 def deviceAddress(self,deviceName): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT `deviceIP` FROM DeviceAddress where `deviceName`=%s", deviceName) #self.cur.execute() output = self.cur.fetchall() #get the mysql response #parse the output from the mysql by creating a list #with lists where each attribue(column) gets independent #element of the list deviceAddr = '' for record in output: columns = list() for entry in record: deviceAddr = str(entry) return deviceAddr except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 #some error happened else: return 0 #I am not connected def updateTaskResult(self, taskID, status): if self.connectionCreated == 1: try: successful = self.cur.execute("UPDATE TaskTable SET status=%i WHERE taskID=%i"%(int(status), int(taskID))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 0: return 1 #update successful else: return 4 #taskID doesn't exist except MySQLdb.Error, e: if debugMode == 1: print str(e) return 3 else: return 0 def updatePingResult(self, taskNo, sipServer, sipGate, sipLoc, gsmBox1, gsmBox2): if self.connectionCreated == 1: try: successful = self.cur.execute("UPDATE PingResultTable SET sipServer=%i, sipGate=%i, sipLoc=%i, gsmBox1=%i, gsmBox2=%i WHERE taskNo=%i"%(int(sipServer), int(sipGate), int(sipLoc), int(gsmBox1), int(gsmBox2), int(taskNo))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 0: return 1 #ping table updated else: return 4 #the taskNo didn't exist except MySQLdb.Error, e: if debugMode == 1: print str(e) return 3 else: return 0 def deleteTempTask(self, taskID): if self.connectionCreated == 1: try: successful = self.cur.execute("DELETE FROM TempTaskTable WHERE taskID=%i"%(int(taskID))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 1: return 1 #deleted it else: return 4 #that taskID didn't exist or something else except MySQLdb.Error, e: if debugMode == 1: print str(e) return 3 else: return 0 def addResult(self, taskID, result): if self.connectionCreated == 1: try: successful = self.cur.execute("INSERT INTO ResultTable(taskID, result) VALUES ('%i', '%i')"%(int(taskID), int(result))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 1: return 1 #successfully added the result else: return 4 #hmmm except MySQLdb.Error, e: error = str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key if debugMode == 1: print str(e) return 3 else: return 0