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() self.lockSet = 0 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)) columns.append(str(0)) 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`,`number` FROM DeviceAddressTable where `deviceName`=%s", deviceName) #self.cur.execute() output = self.cur.fetchall() #get the mysql response deviceAddr = '' for record in output: columns = list() for entry in record: columns.append(str(entry)) deviceAddr = columns 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 GSMPrefix(self,deviceName): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT `deviceIP`,`number` FROM DeviceAddressTable where `deviceName`=%s", deviceName) output = self.cur.fetchall() #get the mysql response deviceAddr = '' for record in output: columns = list() for entry in record: columns.append(str(entry)) GSMListPrefix = columns return GSMListPrefix 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 def insertTaskIn2(self, fromDevice, toDevice, taskNo): if self.connectionCreated == 1: try: #I used here a transaction since I want the mysql to execute a few commands and tell me was it successful rather than to execute some and there happens a mistake and one part is updated and the other isn't newQuery = "START TRANSACTION; INSERT INTO `TaskTable` (`taskNo`, `from`, `to`, `status`) VALUES ('" + str(taskNo) + "', '" + str(fromDevice) + "', '" + str(toDevice) +"', '0'); SELECT @taskID := LAST_INSERT_ID(); INSERT INTO `TempTaskTable` (`taskID`, `taskNo`, `from`, `to`) VALUES (@taskID, '" + str(taskNo) + "', '" + str(fromDevice) + "', '"+ str(toDevice) + "'); COMMIT;" successful = self.cur.execute(newQuery) output = self.cur.fetchone() #without closing the cursos we get a MySQL error, the mistake is an internal mistak of the MySQLdb python library # self.cur.close() # self.cur = self.datBaseConn.cursor() while self.cur.nextset() is not None: pass newQuery1 = 'SELECT taskID FROM `TempTaskTable` ORDER BY taskID DESC LIMIT 1'; successful1 = self.cur.execute(newQuery1) record = self.cur.fetchone() columns = list() for entry in record: columns.append(str(entry)) columns.append(str(taskNo)) columns.append(str(fromDevice)) columns.append(str(toDevice)) columns.append(str(0)) self.tasksList.append(columns) return 1 if debugMode == 1: print output except MySQLdb.Error, e: error = str(e) if debugMode == 1: print str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key return 3 else: return 0 def searchTaskList(self, fromDevice, toDevice): if self.connectionCreated == 1: for item in self.tasksList: taskID = item[0] taskNo = item[1] callFrom = item[2] callTo = item [3] if callFrom == fromDevice and callTo == toDevice: return 1 #that task was found return 2 #that task wasn't found else: return 0 def lockMutex(self, seconds): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT IS_FREE_LOCK('SoftwareStarted')") output = self.cur.fetchone() if output[0] == 1: #resource is free which means software is not started yet successful = self.cur.execute("SELECT GET_LOCK('SoftwareStarted', %i)"%(int(seconds))) output1 = self.cur.fetchone() if output1[0] == 1: #I got the lock now self.lockSet = 1 return 1 elif output1[0] == 0: return 7 #if the attempt timed out (for example, because another client has previously locked the name) else: return 6 # if an error occurred (such as running out of memory or the thread was killed with elif output[0] ==0: return 4 #software is already running and somebody has allocated the mutex else: #means some not so good bug return 5 # if an error occurs (such as an incorrect argument). 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 def releaseMutex(self): if self.connectionCreated == 1: try: if self.lockSet == 1: successful = self.cur.execute("SELECT IS_FREE_LOCK('SoftwareStarted')") output = self.cur.fetchone() if output[0] == 1: #the lock seems to be free self.lockSet = 0 return 4 elif output[0] == 0: #try to release the lock successful = self.cur.execute("SELECT RELEASE_LOCK('SoftwareStarted')") output1 = self.cur.fetchone() if output1[0] == 1: #the lock was released successfully return 1 elif output1[0] == 0: # if the lock was not established by this thread (in which case the lock is not released) return 5 else: # and NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released. return 6 else: #some serious problem #and NULL if an error occurs (such as an incorrect argument). return 5 else: return 7 #the lock wasn't set 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